May 20, 2009 at 3:42 pm
I had to delete 71 records with sensitive data. My boss is paranoid and wants to free up the space that the data was in. He wants to make sure the data is totally gone. I'm now running sp_clean_db_free_space to get rid of the ghost records. It has been running for almost two hours and I'm sure we have a long night ahead of us.
The next step is to free the space from the deleted records. The database is 7GB and has 6197MB free. Should I shrink the database or the files? I would rather not shrink the database because of the performance issues but the boss insists that something should be shrunk. What about the files? Would it achieve the same results as a database shrink?
Also, the database is set to autoshrink on...should I turn it off?
May 20, 2009 at 3:58 pm
New_to_being_DBA (5/20/2009)
I had to delete 71 records with sensitive data. My boss is paranoid and wants to free up the space that the data was in. He wants to make sure the data is totally gone. I'm now running sp_clean_db_free_space to get rid of the ghost records. It has been running for almost two hours and I'm sure we have a long night ahead of us.The next step is to free the space from the deleted records. The database is 7GB and has 6197MB free. Should I shrink the database or the files? I would rather not shrink the database because of the performance issues but the boss insists that something should be shrunk. What about the files? Would it achieve the same results as a database shrink?
Also, the database is set to autoshrink on...should I turn it off?
Aaah bosses, you gotta love 'em. If the data is deleted, its gone. does (s)he want you to go and remove the records from backups as well?
turn autoshrink off, you are wasting resources. If you have auto shrink on not quite sure how you have ended up with a 7GB file with 6Gb free space, so please confirm size of data and log files.
If data file has a lot of free space and you do not think it will ever need that free space then shrink it to a reasonable size with capacity to accommodate reindexes without having to grow. Avoid shrinking the log file unless space on disk is a problem.
In other words if you have to shrink use shrinkfile rather than shrinkdatabase, and specify a sensible size in MB to shrink to.
---------------------------------------------------------------------
May 20, 2009 at 4:06 pm
sorry, I goofed.
database_sizeunallocated space
7725.06 MB 71.88 MB
Database is 7.45GB
mdf is 7.53 GB
ldf is 4.6MB
Boss insists that something should be shrunk. I have pled my case as to WHY it is not needed after only deleting 71 records but..No go! So, should I shrink the file or the database and what is good amount to leave for unused space.
By the way I did ask about the backups being burned. 😛
May 20, 2009 at 4:08 pm
Also, do I need to rebuild indexes AFTER I shrink the files??
May 20, 2009 at 4:15 pm
there is nothing to shrink, you have no free space because of the autoshrink.
Anyway all shrink will do is move extents about, it won;t overwrite data within pages and therefore remove your 'ghost' records the boss thinks are there.
Reindex the table(s) involved instead, that will compress rows within pages and therefore where you have markers for space available for reuse they will get used.
This is presuming the tables is not a heap.
Otherwise just shrink it, boss will be happy, but nothing will happen! You should reindex afterwards
---------------------------------------------------------------------
May 20, 2009 at 4:18 pm
If you do have to waste time by shrinking, grow the data and log files afterwards to values that will accommodate normal database use, turn autoshrink off and leave it like that
---------------------------------------------------------------------
May 20, 2009 at 4:24 pm
You have good solid advice from george in the posts above.
How did your boss get so worried about this? In 20 years plus experience I never had anyone worry this much about rows that were only logically deleted.
(Or files only marked as deleted).
(S)He sounds challenging to work for!
Good Luck!
Tim
.
May 21, 2009 at 3:19 pm
so, what happened in the end?
---------------------------------------------------------------------
May 22, 2009 at 6:23 am
I shrank the log file and it did absolutely nothing. 🙂 The lowest it would let me allocate space was 7720. I did that and the log file went from about 4.56 to 4.55. 😛 But at least I was able to tell my boss that I shrank it...as he wished.
I also turned off autoshrink.
Thanks for all your help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply