Shrinking Large DB Logs
-
Hello All,
I have a general question that I thought I'd take to the pros.
I have a couple database instances whose user database log files (.ldf) tends to grow over 2+ GB. I have a script to shrink the log which works great. The script I run consists of the following command:
BACKUP LOG DBNAME WITH TRUNCATE_ONLY
DBCC SHRINKFILE ('LOGNAME', 1)
When I look at the TaskPad view in Enterprise Manager, I can see how much of that log is actually being used. If I run the above command and shrink a let say 2GB log file down to 1MB, am I setting myself up for a possible problem later down the line? What type of information gets collected in the .ldf files? Will this impact anything if I need to restore a backup?
Thanks,
Ronnie
-
It shouldn’t hurt anything to shrink the database down since it only truncates off data that has already been written to the database. However, if this is something that you perform frequently it could impact performance in the feature. The constant changing of file sizes can cause fragmentation on the hard disk.
I suspect you are not backing up your transaction log files or not frequently. Usually performing transaction log backups on a regular schedule will keep the log file down to size. On my DBs I backup the log at least twice daily on the low use systems. The high data entry systems I backup every 2 hours.
If you don’t care about backing up transaction logs you can set the database to Simple recovery mode. That should keep the size down as well. But then forget about doing recovery to point-in-time.
What gets collected in .ldf files? Most all of the transactions (update, insert…) except for a few like the select statement. It keeps track of open transactions and rolls back failed transactions. Contains who made the change and when. They are kind of important.
-
Using these commands could set you up for a major problem.
Without knowing the particulars of your system I'd say that generally a 2GB log file isn't unreasonable at all.
Until you gain a full understanding of what the issues are in using the TRUNCATE_ONLY option I would strongly recommend against using it.
Make sure that you have a good full backup of your databases right now and start reading Books Online. Focus on understanding what the transaction log is and why it is important, then move into understanding the Recovery Models and creating a workable backup/recovery strategy.
A forum like this isn't the appropriate place to give a full and detailed crash course on these subjects, but they have been covered in many books and in Books Online which comes free with SQL Server.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
I missed the part about the TRUNCATE_ONLY. Your right...that could cause problems.
-
Thanks guys.
So basically, I need to leave the logs alone? I'll research that information that was suggested. The purpose of my question to the forum was to hopefully get some real world experience on how this should be handled. I have other database instances with log files 7 or 8GB. Also, I do backup the transaction log every 30 minutes.
Do either of you two shrink your log file? Also, if TRUNCATE_ONLY is the problem, should I be using a different command?
Ronnie
-
I VERY seldom shrink the transaction logs. Generally the log files achieve what I look at as a certain equalibrium (assuming you are doing regular log backups). The file size grows to the size that is needed to support the activity (including maintenance) and will generally grow slowly as the data files grow. Shrinking the log files will almost always lead to unnecessary overhead as SQL Server has to allocate space (sometimes rapidly).
About the only time I need to shrink a log file is when log backups aren't performed on an appropriate schedule (or not at all) and the file has gotten too large due to negligence (not mine of course
)/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
I also hardly ever shrink a log file unless it grows out of control for some reason.
-
Thanks guys. I guess I've been doing it all wrong. I guess one of my primary reasons for doing so was to preserve disk space. Since the log file was only using in some case 25% of the log size, I just thought it was wasted space.
I also do regular intervals backups (every 10 - 30 mins depending on the DB activity) of the transaction logs. If that is the log backup you are referring to, then I'm covered there and if I'm understanding what you're telling me is those backups should take care of the unused space concerns.
Ronnie
-
That's right. It's normal to have unused space in the log file. So having a 2 gig file doesn't necessarily mean that 2 gigs of the file are used. When you backup the trans log it removes what data it can but the file itself would stay at 2 gigs, just with more free space in it.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply