Allocating huge space during restore.

Question

Allocating huge space during restore.

Saravanan T

SSCrazy

Points: 2506
More actions
April 19, 2010 at 5:03 am

#231865

Hi,
My database backup size is 2 GB. I have a free space of 13 GB. During restore it is not restoring because it allocating around 16 GB and throwing error like insufficient free space.. What could be the reason???
Regards,
Saravanan

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

joeroshan SSChampion Points: 10377 More actions · Answer 1

Original database must have contained unallocated space. Even though the data is only 2GB it will try to create the data and log file with original size specifications.

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Grant Fritchey SSC Guru Points: 398693 More actions · Answer 2

Well, add it up. You have 2gb of data plus 13gb of free space:

2+13 = 15

Which means that it needs to allocate 15gb, give or take, to recreate the existing database.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Saravanan T SSCrazy Points: 2506 More actions · Answer 3

should i shrink before taking backup????

Regards,
Saravanan

joeroshan SSChampion Points: 10377 More actions · Answer 4

Just see why you have that much unallocated space in files. Normally this much free space comes after some cleanups only (if it is data file). If it is not going to grow that soon, you can shrink the database.

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Grant Fritchey SSC Guru Points: 398693 More actions · Answer 5

I agree. Is that the size of the database you want? If so.... there you go. If not, yeah, you might want to shrink the files. Regardless, once you backup a database, the backup is the complete database, every part of the definition that makes up that database, including the defined file sizes. It's not just data.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Perry Whittle SSC Guru Points: 233858 More actions · Answer 6

either perform a

RESTORE FILELISTONLY FROM DISK = 'diskpath\filename.bak'

or use the following against the original database

Use yourdb

exec sys.sp_spaceused

this will give you an idea of the space used\required

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉