question about shrinkdatabase

Question

question about shrinkdatabase

jay-h

SSCoach

Points: 18816
More actions
May 16, 2005 at 8:29 am

#93004

I am running a number of databases in full recovery mode on a clustered server.
I would like to truncate the log, then shrink the db from an automatically invoked procedure, but when I try running:
dbcc shrinkdatabase (dbasename)
from the QA command line, it takes a very long time (actually I terminated it after 15 minutes, it seemed to be going nowhere) but if I shrink the same database from EM it takes only a couple of seconds.
What is responsible for this difference in performance?

Jay

...
-- FORTRAN manual for Xerox Computers --

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

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

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 1

Run profiler against the server and see what dbcc is being issued by em, maybe the sql is different than what you are using. If so use the sql em issues into a sql job.

Greg Milner SSCrazy Points: 2425 More actions · Answer 2

Unless you have some specific need to run the database in full recovery mode, don't. Use bulk logged. This will really cut down un unnecessary db growth.

Also, see http://codetempest.com/node/10 for a stored proc (and the code came from this site originally) that lets you dynamically shrink your log file.

G. Milner

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

The log file probably grew for a pretty good reason. If you shrink it and it decides to grow again, especially if the growth size is small or is a small percentage, you will frag your hard-disk.

gdmilner is correct... unless you are doing regular backups of the database and the logfile, you're gonna get some growth with the recovery mode set to full. Unless you database is absolutely mission critical and backups must be recoverable to the second the thing crashes, you don't need full recovery mode. Depending on you needs, the other 2 recovery modes will likely suit your database.

Really, once you change the recovery mode and start doing backups on a regular basis, don't schedule anything to shrink your database. It'll get pretty darned slow due to being fragged.

--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)

cpeck523 Old Hand Points: 360 More actions · Answer 4

It looks like your main focus might be to shrink the log file. If that is the case you can use this:

dbcc shrinkfile (2)

This is best done right after a dbcc checkdb