Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
eevans
Starting Member
48 Posts |
 Posted - 2010-05-19 : 23:01:14
|
| Hello,I receive this message when trying to restore a backup file:“System.Data.SqlClient.SqlError: MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file. (Microsoft.SqlServer.Smo)”After running RESTORE FILELISTONLY FROM DISK, I see that the database itself is less than a gb but that the log file is over 200 gb. Not good.However, I am actually restoring from and to an external hard drive with 500 gb of free space, so disk space shouldn’t be an issue.Can anyone offer any tips on how I might go about restoring this backup? Does it sound like my backup is corrupt? Any advice would be greatly appreciated.Thanks!Eric |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-05-20 : 23:30:42
|
| When you perform the restore, you have to modify the destinations for the data and log files. But, I have to ask why you are restoring to an external drive. Is this a test system or something else?BTW - I would recommend that you start running transaction log backups on the system where that database was backed up. Once you have scheduled regular transaction log backups, you can then shrink the transaction log. You should schedule frequent transaction log backups (every hour is a good start, more often if you can't afford to lose an hours worth of data). The size of the transaction log should be large enough to manage an hours worth of transaction and a bit more - so when you shrink, take that into consideration and don't shrink it all the way. |
 |
|
|
eevans
Starting Member
48 Posts |
Posted - 2010-05-21 : 08:37:50
|
| Jeff,Thanks for your response. It is a test system. I was only trying to restore to the external hard drive to see if I could get past the low disk space error message I was receiving when trying to restore locally. Also, I did modify the destination to the hard drive. I assume the file is corrupt and will probably look into a data recovery service.Also, thanks for the log backup advice. I am actually not in charge of our backups but will be sure to avoid this scenario if I ever am.Thanks!Eric |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-21 : 10:21:11
|
| There is no indication that the backup file is corrupted, the "out of disk space" message is quite clear.You could monitor disk space on the target drive and see if it is dropping towards zero before the error occurs - if so then that is your problem. If the error message comes up pretty much immedaitely - before the restore startsa[and before you can see much int he way of files growing] then SQL has determined there is not enough disk space.Slightly puzzling is the " while attempting to expand the physical file." which implies that restore had started and the file needed to be expanded half way through - my understanding is that SQL will pre-create files of the original size of the database that was backed up - regardless of how much disk space is actually needed - and then start the restore, so the "expand" word is a bit strange."data recovery service"Is the database no longer available? If it is you can just make a fresh backup and go from there."I am actually not in charge of our backups but will be sure to avoid this scenario if I ever am"If I were you I would tell them. They may not know that something has, at some stage, failed - and possibly still is failing!They can always ignore your alert, but there again they may be exceedingly grateful! Point them to this thread if they need some suggests as to cause & sorting it out. |
|
|
|
eevans
Starting Member
48 Posts |
Posted - 2010-05-21 : 14:18:39
|
| We actually got the backup back up by loading it onto an external drive with a free terabyte. It took half a day to complete the restore. The log file created by the restore was 230 gb.Kristen, thanks for your reply. The database was down and the backup file was all we had. Needless to say, our DBA has already realized the need to revise our backup startegy in order prevent future incidents.Thanks again for all of your help!Eric |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-21 : 14:33:02
|
I'm not surprised that it took half a day  After restoring the Data file it would then have had to plough through whatever there was in the log file that was also restored, working out what, if anything, needed to be applied to the database.I suspect, in principle, that it may well have been the case everything was checkpoint'd at the time the backup was taken - so "nothing" needed to be done - but the act of trawling to find out what needed to be done might well have been a huge task.Recommend that you "kill" the log file, and then take a full backup.Killing the log may require that you back it up (depends what version of SQL you are running), but you may be table to just truncate it without backing it up.Either way, when that is done, SHRINK the log file to a reasonable size. Make sure the shrink works (there may be "stuck" transactions at the end of the log file that prevent it shrinking, hopefully it will work first-time for you)Once you've got the LOG file shrunk to a reasonable size, and assuming that there isn't a universe of slack space in the Data file too (if so that needs reorganising and shrinking to something reasonable too), then take a Transaction Backup (just in case anything is lurking in there) and then take a FULL BACKUP. If you need to restore again that FULL BACKUP will only create the now-reduced-and-shrunk size of database, and won't have to apply to 200GB log, and thus will only take minutes to restore, not half-days. |
|
|
|
|
|
|
|
|
Can’t Restore Backup, Huge Log File