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 |
|
CanadaDBA
583 Posts |
 Posted - 2005-07-18 : 15:24:27
|
| I want to restore a database on my local. I get the following. I have 4.5GB available space. Can I somehow force the Restore command to restore the backup or discard the tranny log?Server: Msg 3257, Level 16, State 1, Line 8There is insufficient free space on disk volume 'C:\' to create the database. The database requires 13241352192 additional free bytes, while only 4512319488 bytes are available.Server: Msg 3013, Level 16, State 1, Line 8RESTORE DATABASE is terminating abnormally.Thanks,Canada DBA |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-07-18 : 15:34:29
|
quote:
big tranny log
I find this highly amusing, but I won't explain why.  The only way to restore that backup is to free up the space it needs, or restore it to another server, truncate and shrink the transaction log, then back it up again and restore that backup to your computer. |
 |
|
|
CanadaDBA
583 Posts |
Posted - 2005-07-19 : 08:06:03
|
I think I used wrong terms!  quote:
Originally posted by robvolk
quote:
big tranny log
I find this highly amusing, but I won't explain why. The only way to restore that backup is to free up the space it needs, or restore it to another server, truncate and shrink the transaction log, then back it up again and restore that backup to your computer.
Canada DBA |
|
|
|
CanadaDBA
583 Posts |
Posted - 2005-07-19 : 08:24:46
|
| The backup comes from production. The data file size is 894MB and the Transaction log size is 15619MB. My local harddrive has 4.5GB free. 1. If I need to restore the DB on my local, do I need to have the transaction log, too? How can cut that portion from the BAK file? Or restrict the backup to not backup the transaction log part? 2. The File Growth is by percent and I think I have to change it to by MegaBytes. Right?3. And totally, is it good idea to set the transaction log to a fix size. I mean to restrict the file growth. I know this is not a recommended idea but if for example all my production's daily transactions for the DB doesn't go more than 2GB, then should I restrict the transaction logs grow to 2GB or 4GB or what?Canada DBA |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-07-19 : 10:09:36
|
| I would definitely change the expansion to be a given size in MB, rather than percentage. I can't be sure, but I have the feeling that we have had dramatic slowdown of some of our multi-GB DBs when they have gone to expand and SQL has grabbed extra diskspace to add to the DB files based on 10% - its just a huge amount of space to grab & initialise in one go. 100MB at a go seems more reasonable to me.Do you need a transaction log of 15GB? You could try shrinking it and seeing how big it grows back - but if it needs 15GB there is little point shrinking it - it will just take CPU & disk effort to grow back again, and there is a risk that the subsequent file will be more fragmented.I definitely would NOT set the transaction log to a fixed size - if it ever gets to be full everything will just stop ...If you are in a position to "shrink" the database, do a backup after that and restore that on your new server - that should be as small as is possible.Otherwise you might be able to detach the database, copy JUST the MDF file to the other server, and then reattach the original, and then do a single-file-attach on the new server. (Make sure you have a backup before you detach)Kristen |
|
|
|
|
|
|
|
|
Restore a BAK file with big tranny log