October 20, 2008 at 1:25 pm
I'm trying to restore a database onto another server and keep getting this message.
Msg 3257, Level 16, State 1, Line 8
There is insufficient free space on disk volume 'E:\' to create the database. The database requires 41464823808 additional free bytes, while only 15647244288 bytes are available.
Msg 3119, Level 16, State 4, Line 8
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 8
RESTORE DATABASE is terminating abnormally
------------
The database is less than 2Gb, and there is >14Gb available on the drive I need to restore to. Anyone seen this before?
Help and Thanks,
October 20, 2008 at 2:49 pm
How large is the transaction log on the system where you took the backup? Not only do you need space for the database (*.mdf), but you need space for the log (*.ldf).
My guess is that the transaction log is much larger than 14GB.
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
October 20, 2008 at 2:55 pm
Hi Jeffrey - you're very right. The log file is 38Gb - who/how/what can I do to shrink/truncate this beast?
THANK YOU
October 20, 2008 at 3:09 pm
Okay, the transaction log on the source system needs to be managed. To start, what is the recovery model for that database? And, how often are you backing up the transaction log?
If you are not backing up the transaction log (which is my guess), then you need to decide whether or not you need to. How often you back up the transaction log should be determined by how much data loss the business can sustain. In a disaster scenario - to recover the database you would have to restore the database backup and all transaction log backups up to the point of the disaster. If you don't need to recover to a point in time, then you can change the recovery model to simple. When the database is in the simple recovery model, you cannot take transaction log backups at all - so, you only have to worry about restoring from the latest backup.
Once you have determined what the recovery model should be and how often you need to backup the transaction log, then you setup and build the maintenance plan. Only after that has been done can you do anything about the size of the transaction log itself. At this point, you would shrink the transaction log using DBCC SHRINKFILE and monitor the size of the file.
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
October 20, 2008 at 3:18 pm
Hi Jeffrey,
It is set for 'FULL' but when I asked the user about backing up the Transaction log - there was a sad, blank look. So, the transaction logs are not backed up at all.
This is a secure, Sarbanes-Oxley project, so I am hands-off and will have to pass instructions on to the user. Probably not a candidate for Simple mode.
Sounds like the thing to do is make a database backup, and a transaction log backup, then we can address the log file size. Shrink file is not giving enough relief.
Thank you
October 20, 2008 at 3:37 pm
Yeah, doesn't sound like simple is going to work. And yes, you need to truncate the transaction log before it will shrink. When you perform a transaction log backup - the transactions that have been committed to the database will be truncated in the log file. Once that is done, you should be able to shrink the file - although it may take several backups before you are able to fully shrink the file.
I would highly recommend working with the user to setup a maintenance plan that backs up the transaction log at least every hour, if not more often.
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply