shrinking db

• dell1

  SSC-Addicted

  Points: 432

  More actions

  June 3, 2004 at 9:48 am

  #163674

  I need to backup and restore this db another server, and its' max sixe can be 28gb

  database size = 30133mb

  datafile1 = 100mb (Primary filegroup)

  datafile2 = 7379mb

  datafile3 = 14068mb

  datafile4 = 3526mb

  transaction log = 5063mb

  Tried rightclicking database and goto all tasks and then shrink db via enterprise manager, this succeeded but size still the same. Via query analyser I tried:

  DBCC SHRINKFILE (4, 7000)

  GO

  Tried this for each of the 5 file_id's. This didn't reduce any of the sizes either. Books on line says try truncating transaction log first via backing it up, prior to using this in query analyser. However, transaction is dimmed out when I goto backup wizard, presume this is to do with the setup.

  Any suggestions???

• sa24

  SSCrazy Eights

  Points: 8027

  More actions

  June 3, 2004 at 10:30 am

  #508861

  Try executing sp_spaceused for each table and check if any of the tables have lot of free space in it. You can then rebuild your index to get rid of the free space on the table.

• Adam Tice

  SSC Journeyman

  Points: 87

  More actions

  June 4, 2004 at 5:01 am

  #508984

  You should be able to decrease the size of your transaction log significantly by using QA to execute:

  BACKUP LOG <db name> WITH TRUNCATE_ONLY

  then

  DBCC SHRINKFILE (<file name>, <size (mb)&gt

  Adam

   

   

   

• Charlie-120533

  SSCrazy

  Points: 2187

  More actions

  June 4, 2004 at 9:22 am

  #509040

  You might want to go to the database and right click and select properties.  From there, select OPTIONS and change the recovery model to SIMPLE.  Then run the dbcc shrinkdb.  Don't forget to reset the recovery back to FULL.

   

  (You might try to perform a complete backup prior to the dbcc shrinkdb.)

  

• iLoveSQL

  SSCrazy

  Points: 2888

  More actions

  June 4, 2004 at 10:38 am

  #509055

  First of all, please feel free to correct me if I am wrong.

  Most of time, DBCC SHRINKFILE works but time to time, doesn't work and both Adam and Charlie method works and have been working for me almost 99%. However, very rarly, even that doesn't work and the reason is that.

  First, we need to run backup log so that we can truncate all the unnessary logs in that log file. Better yet,just running log backup should be good enough if you are working on production database. After that, use the shrinkfile with NOTRUNCATE option and it will free the file space to be retained in the files. After that running the shinrkfile again with truncate_only option and will release the actual space.

  Here is the query that I use to generate script to shrink file. Hope this helps

  ------------------------------------------

  USE <DatabaseName>

  SET NOCOUNT ON

  GO

  select 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'

  GO

  Select 'DBCC SHRINKFILE(' + rtrim(convert(char(30),name))+ ',' + '1,NOTRUNCATE)' + char(13) + 'GO'  from sysfiles

  GO

  Select 'DBCC SHRINKFILE(' + rtrim(convert(char(30),name))+ ',' + '1,TRUNCATEONLY)' + char(13) + 'GO'  from sysfiles

  GO