June 29, 2009 at 4:07 pm
I've inherited a system on which the maintenance plan is failing. I doubt it's complete in ages. The database itself is only 3 GB but the transaction log file is 800 GB. The maintenance plan is set to run once a week and it deletes backup files older than 4 weeks. It's backing up to the same disk that the databases are on which makes no sense of course and that disk is only 1 TB. With an 800 GB trn file, clearly you could only have one backup of it at any given point in time.
How do I shrink this beast and move on with my life? I've tried DBCC shrinkdatabase and DBCC shrinkfile with no success. Then I changed to simple logging and backed up, and then the damn thing grew in size! So that was an epic fail. My background is .NET/SQL Server development. I am not DBA but we don't have one (because we like pain and suffering apparently) so I am the closest thing they have.
Any advice? Thx.
June 29, 2009 at 4:26 pm
here's a good article on managing transaction logs
http://www.sqlservercentral.com/articles/64582/
this should work (try to do at a quite time):
just for info run dbcc sqlperf(logspace) which will tell you how much of the log is actually used
put the database in simple recovery mode (alter database dbname set recovery simple)
shrink the log file the quick way
dbcc shrinkfile(logicalfilename,truncateonly)
If that does not get much space back, run it again
If still no joy perhaps something is preventing it shrinking, try dbcc opentran in the database to see if a query is still active
put database back in full recovery mode
take a full database backup
manually grow log to a sensible size (1 GB or so), immediately set up transaction log backups
monitor for a while to get a feel of how often to do log backups and a size for the tran log that will accommodate normal use.
---------------------------------------------------------------------
June 29, 2009 at 5:01 pm
I'd like to make sure I understand what I am supposed to be shooting for here. My understanding is that you take backups every so often, and then you run differentials more frequently. To restore you would restore your backup and then your transaction log differentials right?
I think I need to alter the maintenance plan to something more sensical. The DB itself is only 3 GB so to do one a day is no big deal. Then I would just need log files for each 24 hours. How do I shrink them every 24 hours and start over?
June 29, 2009 at 6:04 pm
That worked by the way. I put it in simple, then ran shrinkfile, then put it back to full. It's 24 MB now.
June 29, 2009 at 7:27 pm
Be sure that you are now running transaction log backups, and that you took a full backup after the shrink and change from simple.
June 29, 2009 at 8:08 pm
Review the article I link to in my signature to get a better understanding on how to manage your transaction logs.
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
June 30, 2009 at 4:08 am
tnk (6/29/2009)
I'd like to make sure I understand what I am supposed to be shooting for here. My understanding is that you take backups every so often, and then you run differentials more frequently. To restore you would restore your backup and then your transaction log differentials right?I think I need to alter the maintenance plan to something more sensical. The DB itself is only 3 GB so to do one a day is no big deal. Then I would just need log files for each 24 hours. How do I shrink them every 24 hours and start over?
tnk, If you have been landed with this job its definitely worth reading the article thats been mentioned if you have not already. It will answer your questions. Feel free to come back for clarification if you have doubts\questions. Also look up 'backup database' in books OnLine (BOL)
Be careful of the term differential, its a different type of backup which in SQL is not the same as a transaction log backup. With a 3Gb database a differential would be unnecessary. Take a full backup nightly and transaction log backups through the day. Exactly how often depends on the amount of update activity in your database and the maximum amount of data you can risk losing (1 hours worth?, then take a log backup hourly). You seem to have plenty of space so keep at least a couple of days worth on disk and have copies offsite as well.
To recover to a point in time you then restore the latest full backup followed by the transaction log backups in order.
DO NOT keep shrinking the log file, its not necessary. When the log is backed up the space within it that has been backed up is marked for reuse, thus backups control the growth of the log file.
At 24Mb your log file is probably undersized now, manually grow it in one chunk to a sensible size, and set the growth factor to a MB value of about 50MB.
---------------------------------------------------------------------
July 1, 2009 at 6:45 pm
As George mentioned DO NOT keep shrinking the log file, its probably undersized now.
The reason for the large transaction log size is because they have not been backed up. Once the tran logs get backedup regularly you woudn't have this issue. Make sure they are backed up once every 15 minutes atleast, this is also an important aspect of database recovery.
Thanks,
Amol
Amol Naik
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply