May 6, 2002 at 3:18 pm
Hello,
Does anyone know how much disk space is actually needed for SQL Server to perform a restore? It is obvious that space is needed for the restored database. However, it appears that more space is needed.I had a 6 GB database that needed to be restored. I had at least this much free disk space on my server. When I attempted to run my RESTORE statement, SQL Server said I needed more space. So I just began randomly shrinking databases until enough space was available.
Thank you,
Melanie
May 6, 2002 at 8:13 pm
See if this looks like it may be your issue. http://support.microsoft.com/default.aspx?scid=kb;en-us;Q232196
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 7, 2002 at 2:21 pm
Hello,
Thank you for your reply. I took a look at the KB article and that does not describe my problem.
I have a database that is 6.9 GB. The backup file for this database is about 6.5 GB. I want to restore this backup onto another server.
I have the backup file on the destination server. The disk space available is at least 6.9 GB (which is the size the database should be when restored).
What's happening is that when I'm in QA and run a restore command, I receive an error that more disk space is needed.
I would like to find out if anyone knows exactly how much space is needed by SQL Server to successfully run a restore command. From my scenario above, it appears that I have enough disk space. So when I encounter this error, I have to go through the server and shink / truncate databases.
Thank you for your follow up.
Melanie
May 7, 2002 at 4:25 pm
Seems interesting. Are you sure the .bak file will expand back to 6.9g? Maybe it was backed up prior to the db being shrunk and that accounts for the problem?
Andy
May 8, 2002 at 6:52 am
I agree with Andy. Testing all my file backups I never have any trouble with size growing beyond what the last backup image was. Did you shrink then do the backup?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 8, 2002 at 8:16 am
Hello,
Thank you for your response. The database isn't shrunken before the backup is performed.
The next time I perform a restore, I'll take note as to exactly how much space I have and, if I receive the error, how much more space is needed.
Thanks for your help.
May 8, 2002 at 8:45 am
Hi there
Yep, ive had very similar problems, the key is that yes, the files are restored to their original backup size, but, ive had the same issue recently where i had to free around 100mb MORE than what id expect to be free'd. This was a tad strange but was too flat out to follow up on it. We are running win2k as with 64k ALU.
Sorry, cant help as yet, but thought id throw in my experiences...
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
May 8, 2002 at 11:02 am
Probably a little variance due to how space gets allocated on disk maybe?
Andy
May 8, 2002 at 3:18 pm
Even if the allocation units of the drive have some slack left over it seems odd that you would need that much extra space to do a restore based on the file. I was thinking maybe something is going on with tempdb or it's log when extracting the backup... Will need to test a restore with Profiler running to see what all is happening.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 24, 2002 at 2:38 pm
I had a similar experience where on an NT 4.0 server with 500 MB free on the HD I tried to restore a 35 MB backup of a DB with a size of about 100 MB and it caused a error for insufficient space. I don't exactly know how much space is needed but it seems to me that the restore needs more space than the actual DB size.
May 27, 2002 at 5:30 am
A few things to remember...
a) Space may be needed to process the transaction log, as well as database.
SQL Server will always scan the transaction log and to back out any canges due to incomplete transactions at the end of the restore process. This requires a temporary use of disk space for the log.
Also, this process could result in database file pages being released or allocated, tasks that will be performed by SQL Server at the time of the restore using whatever machine constraints (spare memory, etc) apply at that time. It could be that a successful restore performed when the machine is busy will result in a different database file to a successful restore when the machine is quiet.
b) During the restore process, SQL Server creates and deletes .ckp files in the /BACKUP directory. These will also use some disk space.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply