Shrink SQL DB

Epicor ERP 9
1

Epicor 9.05.701 SQL 2008

We are running out of drive space that has our .mdf and .ldf files and want to shrink some of them down. What is the proper procedure to do this? I know using the ‘shrink’ command is frowned upon because it can harm performance.

Any input is greatly appreciated.

2

Abe,
If I needed to shrink anything, I would be only wanting to shrink the log file, leave the DB alone shrinking can cause index/file fragmentation, which will affect performance.

Firstly I would be wanting to ask myself a couple of questions:

  1. How much free space are in the Database file and Log files
  2. What might be causing any bloat, is there anything I can do about it?
  3. What is my recovery model set to on the database.

If you don’t have much free space trying to shrink the file is probably not worth the effort. If you do have heaps of free space (in your log) then by all means you can shrink it, but you need to be cautious about the log file growth settings. Leaving it at default can cause a situation where the system will pause while the log file grows.

Trying to work out what is causing the bloat can be tricky, but start at the known things and work from there. We know that in versions of SQL 2008 and later the rebuilding indexes will increase the log file growth, so if you have a
maintenance plan (SQL built or rolled your own) setup to rebuild your indexes, this might be one cause. Here is a nice link to this issue. There is also a good issue in SQL 2012 where even in Simple mode the log will grow ridiculously large SQL 2012 CU7 resolved this.

Any processes that create, update or delete large amounts of data will increase the log size, sorry I can’t be specific, but every environment is different, and only you will know which parts of the system you use the most. Once again using the reports can help, although there are a myriad of really useful tools from the SQL world that can help, including using SQL profiler to see what is going on.

Recovery model. This is twofold. Dependent on your recovery model depends on how granular you can go with recovery of your database. The flip side is your log will grow, in actual fact it will grow even in the mode the logs the least (Simple), So a big, long running transaction will increase your log file regardless, but when the transaction is complete the entries in the log file are marked for reuse by the next transactions. This does help to reduce the log size, but as mentioned before, a big transaction could increase the size. With the other two models it is not until you perform a transaction log backup that the log entries will be marked for reuse. So backing up your transaction log regularly is important also.

Here is a good description of the three models, Please note that it looks like Microsoft have pulled the descriptions for earlier versions of SQL so this posts to SQL 2012.

So what’s the answer? Firstly if you have a good idea on what sort of size the log file is on a regular type system, then set it to that size value initially to prevent log file growth transactions occurring, and create a regular log transaction backup process to backup the logs so they space can be reused. This should help keep the log under control.

With regards to monitoring the free space in your database and log files, look at the reports in SQL Server Management Studio. There should be one for disk space in the reports menu item for each database. Alternatively you can use the stored procedure sp_spaceused to return the results, but I like the pictures myself. They make is a whole lot easier to explain to management when you do need to go cap in hand for more storage.

To shrink the log file, BACKUP your database first as a precaution.

  1. Set your database to simple recovery model
  2. Open up the Shrink->Shrink Files option from the context menu on the database.
  3. Change the File Type option to select Log instead of data.
  4. Select the reorganize pages before releasing unused space and select a size to shrink to (it will not shrink the log greater that the amount of unused space)
  5. Set the recovery model back to full and perform a full backup.

You can also perform this with TSQL and even Powershell, and there are plenty examples of this around in the internet. What I really want impress on people is shrinking the LOG file is Ok, but leave the Data file alone. Of course if it is a non-producton database, go for it.

Shrinking the transaction log will interrupt any maintenance processes for backing up the log file until you have performed a full database backup. This allows SQL Server to have a new starting point to restore transactions logs from.

There is a good thread here on Epicor maintenance and has some very useful information for keeping things under control space wise.

Apologies for the long post. I hope the information helps

4 Likes
3

Nice write up Simon. I never thought of switching to Simple mode for a log
shrink! I think we’ll try that next maintenance cycle.

4

Thank you Simon! No apology is needed, great info!