June 18, 2010 at 7:20 am
We have taken over a client whose DBA left and are trying to get the SQL 2005 server back in order. There is one database whose transaction log file has grown to over 800GB and we would like to first back it up and then shrink it to a more manageable size. The database for this log is only about 40GB. There is not enough internal storage on the server so we have connected an external USB 1.5TB drive to use as the temporarily. The normal database backup runs fine to the external drive. When we try to run a transaction log backup with truncate it fails with an error of Operating system error 112(There is not enough space on the disk.) I have watched the external drive and it appears to be writing fine until it get to between 4-5GB and this is when we get the error. The database model is set to full at present.
Any help would be greatly appreciated..
Thanks
Thomas
June 18, 2010 at 1:46 pm
Has this transaction log ever been backed up? Doesn't sound like it - which is why it is as large as it is.
At this point, I would recommend not backing it up at all. If you think about it, I don't see how you would even be able to use the backup to recover anyways. So, the easiest way to shrink the file would be to change the recovery model to simple and start shrinking in small segments until you have it down to a minimum size.
I would recommend shrinking it as small as possible and then regrowing it out to it's appropriate size based upon your new transaction log backup schedule.
Once that is done, switch back to full recovery - take a full backup (restarts the log chain) and start your transaction log backups.
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 18, 2010 at 3:57 pm
What would you suggest we try a the segmental steps for reducing the size since it is so large.
Thanks
June 18, 2010 at 4:19 pm
That would depend on the system resources available. I would start by trying to reduce by 50GB at a time. If that takes too long, go with smaller increments - if it doesn't take a long time then increase the size.
But, it does depend on your system and IO subsystem. You might try with 10GB at first and increase it each time.
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 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply