DBCC SHRINKDATABASE (N'MyDB', 0) is taking long time

Question

DBCC SHRINKDATABASE (N'MyDB', 0) is taking long time

vaithi.saran846

SSC Eights!

Points: 992
More actions
June 15, 2012 at 3:45 am

#263008

Hi,
I have sql server 2005 and my database mdf file size is 93GB and ldf file size is 5GB. The shrink is configured as a job. Below are the two t-sql statement i am executing in the two steps:
First step: BACKUP TRAN CDR WITH TRUNCATE_ONLY
Second step:DBCC SHRINKDATABASE (N'Mydb', 0)
if i see the job history, first step has taken only 3 seconds, but the second step is keep on running for 14 hours. So i stopped the job.
Is there any way reduce the running time for second step?
Please help!...........

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Your trying to shrink 98GB down to 0GB and SQL has to compact the files in the background and as it moves pages around it is going to take time.

First off, does the database have any free space? If not then why shrink?

Do you really want to shirnk the whole DB or just the transaction log file?

Do you have jobs setup to rebuild all indexes across the DB once the Shrink has finished?

vaithi.saran846 SSC Eights! Points: 992 More actions · Answer 2

Hi

Below are the clarification for you..

First off, does the database have any free space? If not then why shrink?

Yes. We have created and dropped the large(30GB) table.

Do you really want to shirnk the whole DB or just the transaction log file?

I want to shrink both(database and log file)

Do you have jobs setup to rebuild all indexes across the DB once the Shrink has finished?

Yes. But rebuild will be running once in a week.

Is above info is enough to analysis.

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

Well if you know you have 30GB free, then specify a size of 63GB as SQL will try to get it down to 0 or as close to it as you can which will increase load.

Use individual commands to shrink files one by one.

Do it in smaller chunks, say 5GB at a time.

Ensure you rebuild AFTER the shrink, shrinking the databases has a very very bad performance issue in that it fragments your indexes and causes bad performance issues until you rebuild?

What is the biggest index you have? Multiply that value by 2.2 (approx space needed for index rebuild), if the outcome is >= 30GB then dont bother shrinking as it will only grow the file again by that value after you have shrunk it by 30GB

Suresh B. SSC-Insane Points: 22986 More actions · Answer 4

vaithi.saran846 (6/15/2012)
if i see the job history, first step has taken only 3 seconds, but the second step is keep on running for 14 hours.

I guess, something is blocking the shrinkdb command.

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 5

vaithi.saran846 (6/15/2012)
Hi,
I have sql server 2005 and my database mdf file size is 93GB and ldf file size is 5GB. The shrink is configured as a job. Below are the two t-sql statement i am executing in the two steps:
First step: BACKUP TRAN CDR WITH TRUNCATE_ONLY
Second step:DBCC SHRINKDATABASE (N'Mydb', 0)
if i see the job history, first step has taken only 3 seconds, but the second step is keep on running for 14 hours. So i stopped the job.
Is there any way reduce the running time for second step?
Please help!...........

Why are you shrinking the database as a scheduled job? Do you realize what a mess of the indexes shrinking the MDF file creates?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)