October 25, 2010 at 11:09 am
Hey all,
I am in charge of doing some database maintenance work for the company I work for. I like to test the maintenance queries on test versions of production databases. One issue I'm running into is that sometimes the transaction log for a database is simply huge. While I know it's not a good idea to truncate a transaction log, it's important for me to do it with test databases so that I can restore them on my local machine without running in to space issues.
Does anyone have any advice on how to go about shrinking the transaction log for a database hosted on a SQL Server 2008 instance? I would also appreciate advice on how to do this for a database on a SQL 2000 instance. I would appreciate any help that anyone can provide.
October 25, 2010 at 12:20 pm
October 25, 2010 at 12:21 pm
October 25, 2010 at 12:31 pm
I agree with Pavan. Usually when the transaction log is too big there's a reason for it and shrink will not reduce its size.
If the recovery model is anything but SIMPLE you need to take a transaction log backup before shrinking will work.
Shrink won't work if there are old noncommitted transactions - you need to get those committed or rolled back before shrinking the log, whatever your recovery model is.
Tom
October 25, 2010 at 12:34 pm
You can shrink sql2000 log files from enterprise manager, don't need to script it. But note that it won't get you anywhere unless you've firsdt got rid of outstanding transactions and done any necessary log backups.
Tom
October 25, 2010 at 12:39 pm
Hi,
first of all in my opinion it is not necessary to shrink the log file periodically because in this case the hard disk would be more and more fragmented. But if you set the size of the log to more than the maximum, the log file would not get fragmented, because the log file is now rotating inside the log file and the sql server defragment this file itself. This point leads into better hard disk performance and you never need to defrag your hard disk, but windows partition 😉 . And the more you backup your database, the more you can save space.
Another and performant option is to set recovery model to simple. This option would be good if you haven't got any full transaction log needed feature like p.e. mirroring, logshipping, ... turned on and you only backup the database not the log. This would be the case if your db is not so big.
I hope this was understandable enough. 🙂
Greets
pitcher
[font="Arial"]Kind regards,
Patrick Fiedler
Consultant
October 25, 2010 at 1:39 pm
Thanks very much for the replies. At this point, I'm not concerned with the administrative decisions that affect transaction log growth (although I'd like to better understand those at some point). Right now, I basically have a database that I need to test a query on. It has a very large transaction log and I'd like to shrink it down as much as possible, so long as it doesn't affect the integrity of the data itself.
After I test the query on the database, I am going to delete the database, so the long-term implications of shrinking the log file are unimportant (this isn't a production database, merely a copy of one).
I appreciate the suggestion on shrinking the log file. As I understand it, the database either has to be in simple recovery mode or had the transaction log file backed up recently. Will backing up the log file using Rt. click, Tasks, Backup, Backup type Transaction Log allow the transaction log to be shrunk by a significant amount?
Thanks.
October 25, 2010 at 3:40 pm
dbcc shrinkfile, it's in books on line
October 25, 2010 at 7:33 pm
First of all, i think you need not shrink the log file IF your db has more active transactions and disk has sufficient space.
When you shrinked the log file and released the free space, and database will automatically increase the log file again for the more active transactions. And this operation will increase the CPU loading.
Second, it's necessary to set-up the regular backup-restore strategy, log backup can truncate the previous log.
October 25, 2010 at 8:16 pm
cjohn5552 (10/25/2010)
Thanks very much for the replies. At this point, I'm not concerned with the administrative decisions that affect transaction log growth (although I'd like to better understand those at some point). Right now, I basically have a database that I need to test a query on. It has a very large transaction log and I'd like to shrink it down as much as possible, so long as it doesn't affect the integrity of the data itself.After I test the query on the database, I am going to delete the database, so the long-term implications of shrinking the log file are unimportant (this isn't a production database, merely a copy of one).
I appreciate the suggestion on shrinking the log file. As I understand it, the database either has to be in simple recovery mode or had the transaction log file backed up recently. Will backing up the log file using Rt. click, Tasks, Backup, Backup type Transaction Log allow the transaction log to be shrunk by a significant amount?
Thanks.
Since this is a test copy - the first thing I would recommend doing is change the recovery model to SIMPLE. Once that is done, you can then shrink the log file (DBCC SHRINKFILE).
However, since you are going to be deleting the database anyways - there is no reason to go through the hassle of shrinking. Having a large transaction log will not have any effect on your query. The only time it will have an issue is if the recovery model is FULL - the transaction log is FULL and it needs to grow. That should not be an issue for you, unless the reason the transaction log is full is because of something you did on this copy.
You can review the article I link to in my signature for a detailed explanation by Gail Shaw on managing the transaction log.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply