August 28, 2003 at 9:18 am
I got in a rather heated discussion on this one - I needed to restore a db on laptop I couldn't for lack of hard drive space. So for a couple hours I cleared & zipped a lot of stuff to make room for the 3GB db. Once it was restored, I changed the recovery model from bulk-log to simple, and shrank the db down to 180MB!!! This made room to restore another db in similar shape.
I say shrink before backing up. The supervisor says no, backup first because shrinking can corrupt the DB. Never happened to me, but I'm willing to take this advice & backup first. Then I say shrink, do an integrity check, and backup again. Perhaps perform a checkpoint to clear the log. Anyone have advice/opinions on this?
The point is I shouldn't need 3GB to restore a 180MB db. Also, is there a way to restore a db that's mostly empty space on a drive that can fit the db only once it's shrunk?
Data: Easy to spill, hard to clean up!
August 28, 2003 at 9:28 am
I guess your supervisor just want to prevent database curruption by shrinking database 'in case' even thoght I haven't seen that too.
You may have to find out why database is in 3GB size but there is only 180MB data. You may allocate too much spaces for it.
August 28, 2003 at 10:58 am
What is the size of the transaction log space? When you backup the database it looks at the total size of the database space. This includes the data files, log files, index files, and unallocated space.
So a 10 GB database could actually be 5 GB of data, 2 GB of log and 3 GB of unallocated space.
If log backups aren't done and full backups aren't done often, it's possible for the transaction log space to be larger than the data space. And if the backups are done, but the log space isn't shrunk you can have lots of unallocated space that you are backing up.
-SQLBill
August 28, 2003 at 12:32 pm
Why does a 3 gig DB shrink to 180mb, was it the logs ?? if it was you could have considered stopping the db, copying the mdf, then attach it on the new machine.
I always do a backup before, shrinks or reorgs. I haven't seen any problems, but anything is possible, and I always want my backup.
KlK, MCSE
KlK
August 29, 2003 at 2:49 am
use DBCC SHRINKFILE with TRUNCATEONLY option,
what it does is it shrinks the files to the extent possible or to the actual size of the data available and then frees the rest of the space to the Operating system.
One more suggestion, always Truncate log, before shrinking the database files and then take a backup of the Database, simply because on truncation of the database files, all the uncommited transactions gets commited and gets written to the file and then gets cleared. So, then shrinking become that much easier.
August 31, 2003 at 11:56 pm
The DBs were huge mostly due to the log files. They were probably never shrunk & bulk-log recovery model leads to large log files when bulk operations are performed.
I was peeved because the DBs should have been much smaller, then I would have had to spend so much time & effort clearing space for them. We decided to set a job to backup, then shrink, then backup again. The first backup protects against corruption from shrinking & the second backup is the smaller size. We run a query to test the db after shrinking. If it fails (I doubt it ever will), the db restores from the first backup.
Data: Easy to spill, hard to clean up!
September 2, 2003 at 7:03 am
I still have the question: is it possible restore a db from backup where the db would only fit on the drive once it's shrunk?
Data: Easy to spill, hard to clean up!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply