February 14, 2011 at 3:19 am
Hello
Please see attached first to understand this ?
1. I created a backup file (full backup ) and the file size is
46,731,837 KB ( Comes to about 45 GB )
However the total size of the DB is about 92GB (including unsused space)
2. The backup file size is smaller than the total database size.
3. I have quite a bit of unused space in all files in the DB.
question 1)
Does this mean when SQL server makes a backup it considers only used space ? ( That is good if the answer is "YES" )
question 2)
The new server has very limited space (about 60GB free space ).
So when I retore this backup file will it fi into the 60 GB space or
will it ask for 92GB ( and then terminate the restore process.. )
February 14, 2011 at 4:11 am
mw112009 (2/14/2011)
question 1)Does this mean when SQL server makes a backup it considers only used space ? ( That is good if the answer is "YES" )
Correct.
question 2)
The new server has very limited space (about 60GB free space ).
So when I retore this backup file will it fi into the 60 GB space or
will it ask for 92GB ( and then terminate the restore process.. )
It will need the full 92 GB. it will restore the database to exactly the state it was at time of backup, including file sizes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2011 at 6:39 am
Is there a way to tell SQL sever to create a DB using the used space only ? Or is there a way to force SQL sever to cut down the database size when restoration takes place
( The reason why I say that is we have a space issue in our sever. There is about 60 GB left so can't fit in the entire 92 GB ).
Any help is appreciated.
February 14, 2011 at 6:53 am
No and no. It requires space for the full size of the original database. Restore somewhere that does have space, shrink the DB (DO NOT shrink your production DB), then backup and restore to the limited environment.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2011 at 6:58 am
I did run the DBCC_SHRINKDATABASE command and all that it did was increase the size of the unused space ( Took several hours by the way for this to complete ).
Is there another command that can help us here with the shrinking ?
February 14, 2011 at 6:59 am
I agree with Gail.
Anyway you can have a second drive attached to the server? That way, you can have the database (or part of it) restored there for the cleanup operations that Gail recommends.
By the way, what's your recovery model? If it is the bulk-logged or full recovery models, have you taken log backups regularly?
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
February 14, 2011 at 7:01 am
Shrink database IS the command to remove unused space. Sounds like it may have grown the log. Check recovery model and log backups.
If you ran that on you production server you now need to go and rebuild all of your indexes ASAP.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2011 at 7:07 am
No I did nto run it in the production server.
We do have an intermediate sever ( It is a OLD PC that I loaded the OS and SQL sever )
Is there a way to remove unused space from the file that holds indexes ?
There is quite a bit of unused space in this file ( many GBs ) ?
February 14, 2011 at 7:09 am
You asked me about the Recover Model.
It was set to SIMPLE ( beofore I deleted quite a bit of records from several tables ).
Even now it is in SIMPLE mode
February 14, 2011 at 7:13 am
mw112009 (2/14/2011)
Is there a way to remove unused space from the file that holds indexes ?There is quite a bit of unused space in this file ( many GBs ) ?
DBCC SHRINKDATABASE or DBCC SHRINKFILE
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply