when to use dbcc shrinkdatabase

Question

when to use dbcc shrinkdatabase

yuvipoy

SSChampion

Points: 10469
More actions
March 19, 2014 at 3:48 am

#98876

Hi,
When we need to use dbcc shrinkdatabase once i delete some bulk records or on timely bases?
I am having a database size ofaround 15 Gb.
I am deleting older records on time bases which around 1 GB , on the deleting can dbcc shrinkdatabase ?
dbcc shrinkdatabase is taking more time to finish the operation.
i am just giving
dbcc shrinkdatabase ('Database_name');

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

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

tresiqus SSC Eights! Points: 835 More actions · Answer 1

yuvipoy (3/19/2014)
Hi,
When we need to use dbcc shrinkdatabase once i delete some bulk records or on timely bases?
I am having a database size ofaround 15 Gb.
I am deleting older records on time bases which around 1 GB , on the deleting can dbcc shrinkdatabase ?
dbcc shrinkdatabase is taking more time to finish the operation.
i am just giving
dbcc shrinkdatabase ('Database_name');

Personally, I usually only shrink files, and then only when I know that the file grew because of some maintenance activity I was doing or some other one time, rare event that cause file growth to be much larger than needed.

You say you're purging 1GB from a 15GB database, is the datafile(s) most of the 15GB database size? Or is the log file(s) the largest file?

Sample shrink file command:

USE [DBName]

GO

DBCC SHRINKFILE (N'DBName_log' , 2)

GO

Keep in mind that shrinking the database or files can often be counter-productive.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

yuvipoy (3/19/2014)
I am having a database size ofaround 15 Gb.
I am deleting older records on time bases which around 1 GB , on the deleting can dbcc shrinkdatabase ?

Probably not a good idea. Unless you don't expect to add 1GB of data into that database for months, just leave it. After shrinking you'll have to rebuild all indexes and that could easily leave the DB back at the size it was.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

free_mascot One Orange Chip Points: 27169 More actions · Answer 3

What is the growth of the Database? 15 GB is not huge data and you are purging only 1 GB and that too Database will occupy when insert will happen. I just prefer to leave as it is.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."