1

A colleague of mine reported that an hour after starting a restore the operation failed because the server ran out of disk space. He mentioned that since we are using filestream the size isn't included in the backup size.

Does this sound right? I thought the first thing sql server did up on restoring was to check the amount of disk necessary [including filestream objects] then reserve the space on disk, start initializing the allocated disk space and finally write out the data. I didn't think it was possible to run out of disk space mid-restore.

Does any of this sound right? Does anyone know of a reason the restore would fail halfway through?

|improve this question
1

A colleague of mine reported that an hour after starting a restore the operation failed because the server ran out of disk space.

What was the specific error you received?

Does this [info about FILESTREAM enabled database restores] sound right?

I don't believe FILESTREAM enabled databases have different restore settings than a typical database... a quick internet search doesn't return results to the contrary.

Questions for you:

Have you checked the size of the database (link to TSQL) and compared it to how much disk space is available on the destination server's drives (another link)?

Or use this code (msdn source):
SELECT * FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id);

Are you using the WITH MOVE option to ensure the database files are allocated to specific drive(s) / mount-points which have enough free space?

I didn't think it was possible to run out of disk space mid-restore.

It's certainly possible...

Are you actively adding any databases (or random files that shouldn't be there) to the destination drive(s)?

Is your destination drive C:?

Also, look into the use of WITH RESTART for your restores...

|improve this answer
  • Holy smokes - all very good information. All I have to work on is "it error'd out. I think it is because you are using filestream." - Not much more. :) Next time it happens I will refer to this post. – John Hennesey Dec 29 '16 at 18:24
  • @JohnHennesey - Did you ever get to the bottom of this issue? – SQL_Underworld Feb 6 '17 at 15:02
  • I did not- I went under the belief that empty pages were the cause. – John Hennesey Feb 7 '17 at 19:29
1

I have not tested it, but I would think the filestream file should be included in the size.

For example, you can use all backup and recovery models with FILESTREAM data, and the FILESTREAM data is backed up with the structured data in the database.

Source

In addition, it sounds like you have emtpy pages.

Note that the backup size does not include empty pages, but when you actually perform the restore, the data and log files will be over 200 GB, because it has to restore exactly what the source system had (including a 200+ GB log file, regardless of how full it was).

Your example may not be 200+GB, but it was in the case for this person when their backup was pretty small.

Source

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.