Shrinking dataFiles

• sree-226516

  SSCrazy

  Points: 2590

  More actions

  July 15, 2005 at 7:49 am

  #110145

  Hi,

  I am trying to shrink data files in one of the database.I scheduled sql server agent job to shrink files.But its taking for ever to shrink datafiles.I wrote followin code.

  DBCC Shrinkfile (datafile1,1848)

  go

  DBCC Shrinkfile (datafile1,1700)

  go

   

  DBCC Shrinkfile (datafile1,1600)

  go

  DBCC Shrinkfile (datafile1,1500)

  go

  DBCC Shrinkfile (datafile1,1458)

  go

   

  whats wrong with my code?could anybody please help me why its taking so long to shrink.

  Thanks,

  Sree

   

  whats

   

   

  Thanks,
  SR

• July 18, 2005 at 8:00 am

  This was removed by the editor as SPAM

• PurpleLady

  SSCertifiable

  Points: 7819

  More actions

  July 18, 2005 at 12:08 pm

  #575285

  Try adding TRUNCATEONLY - this should release empty space without moving the data. Depending on how fragmented the data is, that can affect the elapsed time.

   We schedule this on a weekly basis when there is no activity on the server as part of our maintenance plan.

  Hope this helps.

• M. Senthil Kumar

  Newbie

  Points: 1

  More actions

  July 18, 2005 at 11:12 pm

  #575387

  Hi

  You need to take backup before doing the shrinking

• ann ramos

  Right there with Babe

  Points: 791

  More actions

  July 19, 2005 at 5:38 pm

  #575679

  yeah. i think you need to run backup first before you can do a shrink.  i used the following code for our daily backup/shrink/etc:

  BACKUP LOG SERVICECENTERMAPPED WITH TRUNCATE_ONLY

  DBCC SHRINKDATABASE (N'ServiceCentermapped', 10)

  it shrinks the log file to a smaller size.

  hth.

  ann