SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL database shrink takes over many hours


SQL database shrink takes over many hours

Author
Message
Craig G
Craig G
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 20
we truncated tables and we found over 300 gb unused or free space. So we went to shrink the database in sql management studio . But when I ran queries to check the shrink progress , its very slow but first 20 percent went through faster. But now its moving at 1% per hour , any thoughts on what could cause this to be too slow? also status is showing as "suspended".

I do not want to kill the process , because that will take rollback time even more.
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32653 Visits: 18558
Are you trying to shrink out all 300GB of space?


Next question...

Why shrink? This fragments indexes severely and will likely cause the database to need to grow when you defrag the indexes.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6702 Visits: 7394
Another note: If SQL 2008 or lower, the shrink process is single-threaded = takes forever.

Despite the obvious fragmentation you're going to cause, you could try shrinking at a smaller chunk, like 1GB at a time...

There's some good power shell scripts out there to automate the entire thing:
http://www.mssqltips.com/sqlservertip/3178/incrementally-shrinking-a-large-sql-server-data-file-using-powershell/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20140605

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
Craig G
Craig G
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 20
thanks for response. But the shrink database is already executing now from sql management studio. So please suggest an option that i can do now.

1. Can i kill the process ? what command should I use? will this cause any issues? where should i run the command from ?

2. I can let it to run for 24 hours , as you its single threaded , probably it will complete smoothly?
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6702 Visits: 7394
Killing it won't do any harm whatsoever. It shouldn't take long at all to rollback and it's only doing small chunks behind the scenes

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
Craig G
Craig G
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 20
Can it run for 24 hours? I am a newbie. The process is already running now. So I need answers for what can be done now as opposed what should have been done before shrinking.

we truncated tables and shrunk same size in another environment and it went through faster and completed.

in this database , its taking too long , can this be run for 24 hours and it will not cause any issue?

should we kill it now ? what is the command to kill it from the query window? I am running shrink database menu option in sql 2008 management studio?
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6702 Visits: 7394
Can what run? The actual shrink process or the rollback?

The shrink process can take days depending on how much you're attempting to shrink at a time...the rollback should be quick, but as with anything regarding SQL server, "it depends"

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32653 Visits: 18558
So you ran shrink from the GUI instead of from a command?

Do you know how to "kill" a command? Same command to kill the shrink.

Have you checked for any blocking?

Did you run this during a maintenance window or during production hours?

And yes you can kill it.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search