SQL Database Size

Hi John,

Hmmm – you are already doing transaction log backups, and normally that’s all that is required. I wouldn’t expect the LDF to be as large as that given the backup method you describe. (Our LDF is 20 GB and MDF is 56 GB – and we do transaction log backups every 30 minutes.) Is the LDF continually growing or has it remained at approximately 167 GB for quite some time? I’m wondering if perhaps there was a period of time when transaction log backups weren’t being done and it grew during that period…

As a general rule, it is not a good idea to shrink database files. That can actually create more issues than it solves. However, in this particular case, I can see why you’d want to shrink the 167 GB LDF!  One method I’ve seen is to temporarily put the database in simple recovery mode, then in SSMS, right-click the database, go to Tasks, Shrink, and choose the File option. From there, you can select the LDF and it should shrink down. Then, go back to full recovery mode (assuming it was set to that in the first place). It’s not the only way to do it, but tends to be the easiest as a one-time fix.  (This link mentions this idea, as well as some other possible ways to get the LDF shrunk down: http://timradney.com/2011/08/31/shrinking-transaction-logs/.) If you do that, I would do it during a maintenance window just to be safe and only after you’ve performed a full backup. That way, you have a way to fairly easily recover if something bad happens (unlikely, but always best to be prepared when performing maintenance like this).

But, unless you have a disk space emergency, it may be best to find out why the LDF is growing so large before shrinking. I hope this is helpful.

Thanks,

David

Does it seem normal to have an Impresario mdf file of 50GB and a ldf file of 167GB?  A full backup of the database results in a 13GB bak file.  It seems like there’s a lot of wasted space in the database.  Currently we are doing a daily full backup with transactional backups every 15min.  Are there reindexing or shrink utilities in SQL 2008 R2 that can assist?

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Hi John,

Hmmm – you are already doing transaction log backups, and normally that’s all that is required. I wouldn’t expect the LDF to be as large as that given the backup method you describe. (Our LDF is 20 GB and MDF is 56 GB – and we do transaction log backups every 30 minutes.) Is the LDF continually growing or has it remained at approximately 167 GB for quite some time? I’m wondering if perhaps there was a period of time when transaction log backups weren’t being done and it grew during that period…

As a general rule, it is not a good idea to shrink database files. That can actually create more issues than it solves. However, in this particular case, I can see why you’d want to shrink the 167 GB LDF!  One method I’ve seen is to temporarily put the database in simple recovery mode, then in SSMS, right-click the database, go to Tasks, Shrink, and choose the File option. From there, you can select the LDF and it should shrink down. Then, go back to full recovery mode (assuming it was set to that in the first place). It’s not the only way to do it, but tends to be the easiest as a one-time fix.  (This link mentions this idea, as well as some other possible ways to get the LDF shrunk down: http://timradney.com/2011/08/31/shrinking-transaction-logs/.) If you do that, I would do it during a maintenance window just to be safe and only after you’ve performed a full backup. That way, you have a way to fairly easily recover if something bad happens (unlikely, but always best to be prepared when performing maintenance like this).

But, unless you have a disk space emergency, it may be best to find out why the LDF is growing so large before shrinking. I hope this is helpful.

Thanks,

David

Does it seem normal to have an Impresario mdf file of 50GB and a ldf file of 167GB?  A full backup of the database results in a 13GB bak file.  It seems like there’s a lot of wasted space in the database.  Currently we are doing a daily full backup with transactional backups every 15min.  Are there reindexing or shrink utilities in SQL 2008 R2 that can assist?

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!