July 21, 2009 at 1:17 am
Cathy DePaolo (7/20/2009)
Just to close the loop on this, I backed up the log and then ran DBCC SHRINKFILE with a target size of 12GB. I was able to successfully shrink the log to 12.4 GB.I would have liked to follow Vegard's recommendation and modify the file size manually using ALTER DATABASE, but I am not allowed to restart the server. We have a scheduled reboot monthly, so I will try to coordinate this and see if I can get the number of VLF's down as well as the size of the database. After backing up the log and running the shrinkfile command 4 times, I was unable to reduce the number of VLF's any lower than 723 files.
Thank you to everyone that posted!
Cathy
Actually, you do not neeed to restart the server to run ALTER DATABASE on a user database. In this particular case, the ALTER DATABASE command does exactly the same thing as when you bring up database properties in Management Studio, go to the Files sheet and simply type in a new value in the "Initial size" field for the relevant file(s) and click OK to commit your changes. Keep in mind, though, that you can only increase the file size in this manner. To shrink the file, you still need to use the DBCC SHRINKFILE command.
As for your 723 VLF's - well, that's what happens over time when the poor database has to autogrow a whole bunch of times and it only gets a few megs each time: You end up with an awful lot of very small VLF's. Like I said, if you back up the log and then shrink the .ldf as small as possible (ie. no target size for the SHRINKFILE, just cut it to the bone), you should be able to get rid of most (almost all) of these. Note: You may need to run both backup and shrink a few times before really getting the file size down to or near the minimum size. This is because, as mentioned in an earlier post, a VLF can only be truncated if it contains no log records for any active transactions. For this reason, you may find that shrinking works best during off-peak hours when there are fewer active transactions "locking" your VLF's.
So, to sum things up, like I said earlier, what you want to do is to shrink the log file as small as possible and then manually grow it back up to an appropriate size. The shrink, if done correctly, will drop most of your undersized VLF's and the grow will create new ones of more appropriate sizes, leaving you with a better tuned and better performing database, even if you don't change the total log file size at all. 🙂
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
July 21, 2009 at 1:52 am
Does this mean you are recommending a shrink every night then?
No, not at all. If you have the reindex job once a week or a month then it would make sense to shrink the transaction log. But not when you have it every night.
I'm not sure I could manage to take that on in the evenings and I know this should not be a scheduled job. However, if this is what is truly required, I will figure out a way to make this work. Just to sum up, every evening I should reindex, shrink the log, and then perform a full backup of the database. Is this the full recommendation?
I have a weekly\monthly reindex jobs, so i go for the shrink of the transaction log. But, in your case as you have a nightly task and i don't recommend shrinking the log.
July 21, 2009 at 2:12 am
I generally reorganize / rebuild indexes once a week, but I still don't shrink the log and I don't see any good reasons to do so. After all, we already know it's just gonna have to grow again next week, so why would we want to shrink it?
Yes, you can temporarily free up some disk space by shrinking the log file, but you can't really use that space for anything since your log file's gonna need it again next week. So, it's really not free space at all and for SQL server it would be better to just leave it with the log file even if it doesn't get used for a few days.
In my opinion, shrinking should not be part of any periodical maintenance plans. Period.
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
July 21, 2009 at 5:45 am
Vegard Hagen (7/21/2009)
Cathy DePaolo (7/20/2009)
Just to close the loop on this, I backed up the log and then ran DBCC SHRINKFILE with a target size of 12GB. I was able to successfully shrink the log to 12.4 GB.I would have liked to follow Vegard's recommendation and modify the file size manually using ALTER DATABASE, but I am not allowed to restart the server. We have a scheduled reboot monthly, so I will try to coordinate this and see if I can get the number of VLF's down as well as the size of the database. After backing up the log and running the shrinkfile command 4 times, I was unable to reduce the number of VLF's any lower than 723 files.
Thank you to everyone that posted!
Cathy
Actually, you do not neeed to restart the server to run ALTER DATABASE on a user database. In this particular case, the ALTER DATABASE command does exactly the same thing as when you bring up database properties in Management Studio, go to the Files sheet and simply type in a new value in the "Initial size" field for the relevant file(s) and click OK to commit your changes. Keep in mind, though, that you can only increase the file size in this manner. To shrink the file, you still need to use the DBCC SHRINKFILE command.
As for your 723 VLF's - well, that's what happens over time when the poor database has to autogrow a whole bunch of times and it only gets a few megs each time: You end up with an awful lot of very small VLF's. Like I said, if you back up the log and then shrink the .trn as small as possible (ie. no target size for the SHRINKFILE, just cut it to the bone), you should be able to get rid of most (almost all) of these. Note: You may need to run both backup and shrink a few times before really getting the file size down to or near the minimum size. This is because, as mentioned in an earlier post, a VLF can only be truncated if it contains no log records for any active transactions. For this reason, you may find that shrinking works best during off-peak hours when there are fewer active transactions "locking" your VLF's.
So, to sum things up, like I said earlier, what you want to do is to shrink the log file as small as possible and then manually grow it back up to an appropriate size. The shrink, if done correctly, will drop most of your undersized VLF's and the grow will create new ones of more appropriate sizes, leaving you with a better tuned and better performing database, even if you don't change the total log file size at all. 🙂
Ah, I see. I scrolled too quickly in BOL and saw "Step 3: restart SQL Server" and thought that applied to all ALTER DATABASE statements. Just goes to show that working (or trying to work) on a database when you are bone tired is NEVER a good idea. At least I erred on the side of caution 🙂
I will run the backups/shrinks tonight without a target size and see if I can get those VLF's down to around the 50 file mark. Thanks Vegard!
July 21, 2009 at 9:26 am
Cathy,
There's also a Forcibly Shrink the log script here [/url]on the site that will run a few transactions and shrink the log. It repeats this to clear out VLFs and get back to the first one.
If you need it, it has worked well for many people in the past.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 21, 2009 at 9:37 am
Steve Jones - Editor (7/21/2009)
Cathy,There's also a Forcibly Shrink the log script here [/url]on the site that will run a few transactions and shrink the log. It repeats this to clear out VLFs and get back to the first one.
If you need it, it has worked well for many people in the past.
VERY nice. Thanks Steve. I'll check it out a little more thoroughly and go through a test run on our test server first of course, but this will be MUCH nicer than manually backing up and shrinking over and over and over and...well, you get the picture 😛
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply