Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL database shrink takes over many hours Expand / Collapse
Author
Message
Posted Friday, July 18, 2014 11:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 20, 2014 6:46 PM
Points: 8, Visits: 18
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.

Post #1594175
Posted Friday, July 18, 2014 11:54 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:32 PM
Points: 21,755, Visits: 15,459
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1594183
Posted Friday, July 18, 2014 12:01 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:38 PM
Points: 3,931, Visits: 7,160
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"
Post #1594185
Posted Friday, July 18, 2014 1:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 20, 2014 6:46 PM
Points: 8, Visits: 18
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?
Post #1594206
Posted Friday, July 18, 2014 1:25 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:38 PM
Points: 3,931, Visits: 7,160
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"
Post #1594214
Posted Friday, July 18, 2014 1:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, July 20, 2014 6:46 PM
Points: 8, Visits: 18
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?
Post #1594215
Posted Friday, July 18, 2014 1:33 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:38 PM
Points: 3,931, Visits: 7,160
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"
Post #1594216
Posted Friday, July 18, 2014 1:52 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:32 PM
Points: 21,755, Visits: 15,459
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1594219
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse