Back up database: Insufficient space

[twifosp]

Someone is trying to transfer a database to me via backing it up and then I am restoring it. I am not the person who performed the back-up. The .bak file is only 58 megs and they say they have truncated all tables ect.

When I try and back up the database, I get an error message:

Microsoft SQL-DMO (ODBC SQLState: 42000)

There is insufficient free space on the disk volume...

The message has more but it is basically saying I need 48 gigs free space and I only have 35. I only have 35 gigs free, yes, but why should a 58 meg .bak file require 48 gigs of free space?

 

[MichaelF81]

Try shrinking th database before the update. I believe it does it on the fly so it might be trying to use too much disk space.

DBCC SHRINKDATABASE
    ( database_name [ , target_percent ]
        [ , { NOTRUNCATE | TRUNCATEONLY } ]
    )

example

DBCC SHRINKDATABASE (MyDatabaseName, 10)
GO

"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[twifosp]

I don't think I understand how to do that on a database that isn't created yet. The database hasn't been created yet, I am trying to restore it from a back up file.

 

[SQLBill]

Oh, restore. Your post said you were trying to do a BACKUP.

When I try and back up the database, I get an error message:

One more thing...you say

they say they have truncated all tables

That just empties the data, but does NOT change the size of the database files.

Let's say the database was 30 GB and the log file was 20 GB. You truncate all the tables and the database file is still 30 GB. It's just an empty 30 GB. Same with the log file, if you just truncate it the space is still held by SQL Server. You need to SHRINK the database to return the free space back to the operating system.


-SQLBill

Posting advice: FAQ481-4875

 

[twifosp]

Bill thanks for the reply. I understand that, but the .bak file is only 58 megs. How on Earth can it restore 48 gigs out of a 58 meg file.

Also the database is not restored yet. How do I shrink it when it is not restored or attached?

 

[MichaelF81]

You can try to attach the files and shrink it from there




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[twifosp]

I don't have the space for that. I could make space, but it doesn't seem like a 58 meg file could take up my 38 gigs free.

 

[MichaelF81]

in databases there is a lot of "white space", or empty space. If you look at a char field, select the data and look at how much extra spaces there are. Those spaces take up just as much space as any letter or number. Take a 500mb .MDF file, zip it and watch it become like 250kb or 1mb.




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[twifosp]

Ok, is there a way to shrink the .bak file before trying to restore it?

 

[SQLSister]

No you need to shrink the database before the backup is made.

Questions about posting. See faq183-874

 

[MichaelF81]

you can buy software for like $9 [link =

http://www.amleth.com/dbhelper/

]

http://www.amleth.com/dbhelper/

[/url]




"Adults are just obsolete children and the hell with them." - Dr. Seuss

 

[SQLBill]

Let's say you create your database to have a 10 GB data file and a 5 GB log file. Now you have a 15 GB database with no data. You put 1 GB of data in it. Now you have 1 GB of data and 14 GB of free space. When you back it up, the backup will be 1 GB (or so), but there's a 'command' in there that says "I need 15 GB of space".

So, the database that got backed up was 48 GB in SIZE, not in DATA. It needs to restore that size regardless of whether it is USED space. But it only backs up the USED space.

Do you have a database already? Open it up in Enterprise Manager. Drill down, select and right click on the database. Select View>TaskPad. Scroll down in the right pane and you will see a bar graph for DATA and LOG file sizes. The bars are split into two colors. From the right is the amount of actual data in the file. Where the second color is shows the amount of FREE space. This free space does not get backed up, but the backup file knows that it is 'required' for restore. This is why all databases should be shrunk (DBCC SHRINKDATABASE or DBCC SHRINKFILE) before doing a backup.

-SQLBill

Posting advice: FAQ481-4875