dbcc shrinkdatabase

• Mkumari

  Hall of Fame

  Points: 3498

  More actions

  April 8, 2002 at 10:10 am

  #157858

  Hi all,

  we have a db of morethan 2 terra bytes which has image datatype.Recently we have purged aome of the tables thinking that it may free some space and according to our estimation it should come to 60GB.But after deleting the rows from the tables we haven't seen any free space released.I have run both DBCC shrink database and shrink file,still we didn't get the desired free space.does any one can throw some lite on this issue.

  Thanks

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738160

  More actions

  April 8, 2002 at 10:17 am

  #426790

  There are issues in how the virtual log files are created for the logs. Search the scripts area for a shrink procedure that should help there. Not sure about the db.

  Steve Jones

  steve@dkranch.net

• Antares686

  SSC Guru

  Points: 125444

  More actions

  April 8, 2002 at 10:37 am

  #426793

  So far this has never failed me and cleans up a number of issues with databases when adjusting size (especially drastic changes). Note: large databases take a while, generally.

  --Truncate Log File

  BACKUP LOG DbName

  WITH TRUNCATE_ONLY

  GO

  --Force Log File to Shrink the script Steve is referring to.

  [DbName]..sp_force_shrink_log

  GO

  --Rebuild All Indexes in Database see script below.

  sp_RebuildIndexes [DbName]

  GO

  --Update stats information

  [DbName]..sp_updatestats

  --Update Datebase Usage to keep running smooth

  DBCC UPDATEUSAGE (DbName)

  GO

  --Shrink Database to Last Extent in File

  DBCC SHRINKDATABASE (DbName)

  GO

  --Update Datebase Usage to keep running smooth

  DBCC UPDATEUSAGE (DbName)

  GO

  ----------------sp_RebuildIndexes

  CREATE PROCEDURE sp_RebuildIndexes

  @DBName as varchar(500)

  AS

  DECLARE @SQLState varchar(5000)

  SET @SQLState = '

  DECLARE TableCursor CURSOR FOR SELECT ''' + @DBName + '.'' + TABLE_SCHEMA + ''.'' + table_name FROM [' + @DBName + '].information_schema.tables WHERE table_type = ''base table''

  DECLARE @TableName varchar(255)

  OPEN TableCursor

  FETCH NEXT FROM TableCursor INTO @TableName

  WHILE @@FETCH_STATUS = 0

  BEGIN

  SELECT ''Reindexing '' + @TableName as DOING

  DBCC DBREINDEX(@TableName,'' '',90)

  FETCH NEXT FROM TableCursor INTO @TableName

  END

  CLOSE TableCursor

  DEALLOCATE TableCursor'

  EXEC (@SQLSTate)

  "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

• Mkumari

  Hall of Fame

  Points: 3498

  More actions

  April 10, 2002 at 2:11 pm

  #426984

  Wondering if you delte the Rows from a table which has image data type,does it frees the space occupied by the image too.As per my understanding table holds a pinter for the image.