September 6, 2009 at 11:23 am
I recently deleted few millions of records from a 1.6TB database and started shrinking it to gain some space but its been 3 days my shrink job is still running, is that an issue or is there any other way to fast shrink the db.
September 6, 2009 at 1:41 pm
If you've only deleted a few million records from a database that size, why shrink the database?
You're probably only going to have to see it expand again as records are added. This can lead to performance issues due to fragmentation. Unless you have absolutey critical space issues, rather than shrink - you may want to consider adding enough space for the lifetime of the system, or the next 2 years - whichever is shorter, during your next downtime.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
September 6, 2009 at 2:01 pm
I have deleted so many records for which i am expecting atleast 100gb space back and i am interested to know the reason why it takes so longer.
September 6, 2009 at 3:41 pm
Do you realize that 100GB is less than 10% of your 1.6TB database? Do you also realize that you want at least 10% (and probably more like 20%) space available in the data file for normal operations?
Have you considered how much space you need in the data file for the next 6 months growth, plus available space for reindexing operations? That is where I would estimate 20% available - but without knowing your operations, I cannot say with any certainty.
In this situation - saving 100GB of disk space is useless. You are going to use that space up again in a very short amount of time - so why bother with shrinking the data file? All it is going to do is cause you a lot of problems, destroy your indexes which are going to have to be rebuilt after the shrink, take up resources better used for query processing and when you do reindex - going to just grow the file again using up the space you just saved.
If you are running out of disk space - buy more. It's a lot less expensive than the approach you are using now.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
September 7, 2009 at 2:03 am
Tara (9/6/2009)
I have deleted so many records for which i am expecting atleast 100gb space back and i am interested to know the reason why it takes so longer.
depends on whether you shrink the file(s) or the database and if you decided to reorganise the data as well.
shrinking a database is pretty much a last resort, because it can affect performance drastically and in most cases is only a temporary solution.
without knowing the database and the number of table\indexes etc and usage. it is not easy to predict why.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 7, 2009 at 8:54 am
Have you had a look in activity monitor to see if there are any locks blocking the shrink job?
I say this whilst agreeing with the previous posters about shrinking in the first place - you could end up with a large and fragmented database file.
September 7, 2009 at 11:14 am
Do you have problems with disk space ? If not you only need to execute some index rebuilds and nothing more.
If you have disk space problem .... threre are no perfect solution.
September 8, 2009 at 7:24 am
The main reason for the shrink is i had to delete un used records of nearly a billion and so want to check how much space i have saved and its not shrinking donno the exact reason.
If i backup the database does it back the empty space , can i expect to see my backup file smaller than the one i had before deleting the records?
September 8, 2009 at 7:41 am
The backup should be smaller - it won't back up the empty space
September 8, 2009 at 7:48 am
Tara,
Has the shrink finished now and how long did it take
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 8, 2009 at 8:20 am
Not yet, its been 4 days but still doing the shrink with no gain of space till now but i have to stop it and start backing up the database just to check if my backup file will be smaller than before.
September 8, 2009 at 8:39 am
Tara (9/8/2009)
Not yet, its been 4 days but still doing the shrink with no gain of space till now but i have to stop it and start backing up the database just to check if my backup file will be smaller than before.
I would leave it, hopefully this isnt a production system. How did you kick off the shrink, using tsql, using the GUI or via a scheduled job.
You are aware that if you kill a process, it can take the same time or longer it was taking so far to rollback. bear in mind as well that if you do this, you will need to run dbcc checkdb on this database and rebuild all your indexes, when you can.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 8, 2009 at 10:58 am
Actually i tried all 3 ways and also killed but it didnt roll back, just killed the session.
September 8, 2009 at 11:28 am
if you want to find out how unused space there is in a data file, just use dbcc showfilestats
1 Extent = 64KB
so the unused space (that you deleted)
(TotalExtents *64) - (UsedExtents * 64) = UnUsed Data Size in KB
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply