Useful SQL Query (Part 3) – Remove database log from SQL

There are certain needs when you want to remove the database logs. Below SQL query will help you in that.

USE [master]

GO
ALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAIT

DBCC SHRINKFILE(TestDbLog, 1)

ALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAIT

GO

Advertisements

4 Responses to Useful SQL Query (Part 3) – Remove database log from SQL

  1. stephen says:

    Please don’t do this its very bad practise.

    • Amir Nazim says:

      Hi Stephen,

      Thanks for feedback, can you give me some more details ? why it is not a best practice ? I know it has impacts if we misuse this but there are certain needs when you want to remove the database logs in such cases the sql helps a lot.

      • stephen says:

        Sorry for the delay I wrote you a lengthy reply but some password issue creot in and I could not post

        There is a difference between shrinking log files and shrinking tables – you need to understand the VLF concept. to understand the problem see Kimberley Tripp’s blog articles for example she is one of the more reliable SQL bloggers.

        If a log needs to be of a certain size then it will quickly grow back t that size and that growth creates performance issues and you enter up with excessive VLF files which further reduces performance. The key s to size the log correctly in the first place. You may need to do a one off shrink to clean up after someone else but it should not be a regular task.

        Where it may make sense is in a dev system after you have refreshed from live but will have only a fraction of the transaction volumes.

        (On a separate note- when deleting from a SQL table remember that will write to the transaction log, so be careful how many records you delete at a time or you will throttle SQL and then you will have a major issue with your transaction log size)

        More important for Ax is to stay on top of the useless InventSumLogtts size, and while your at it Also while you are at it check out the size of the smmTransLog,)

        If you do any significant deletions then also consider defragmentation

  2. tommyskaue says:

    Remember that when you set the recovery mode from Full/Bulk to Simple, you always break the database recovery log chain. This makes it impossible to restore log backups after that point in time unless you don’t have a new full database backup created after setting recovery back to full/bulk again.

    Read more here:
    https://technet.microsoft.com/en-us/library/ms178052(v=sql.105).aspx

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: