SQL database shrink takes over many hours

Question

Post reply

SQL database shrink takes over many hours

Craig G

Old Hand

Points: 305
More actions
July 18, 2014 at 11:35 am

#291174

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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

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[/url]
Learn Extended Events

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 2

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; Theyll drag you down to their level and beat you with experience

Craig G Old Hand Points: 305 More actions · Answer 3

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 SSC-Forever Points: 44486 More actions · Answer 4

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; Theyll drag you down to their level and beat you with experience

Craig G Old Hand Points: 305 More actions · Answer 5

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 SSC-Forever Points: 44486 More actions · Answer 6

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; Theyll drag you down to their level and beat you with experience

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

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[/url]
Learn Extended Events