Shrink DB

• Rajo

  SSC Enthusiast

  Points: 145

  More actions

  January 24, 2011 at 10:16 pm

  #245850

  Friends,

  Thanks in advance.

  In sql2k5, one db size is 150gb, after remove some records and backup file size is around 30GB, but in db shows 100gb.

  I feel the backup file size may correct because, records cleared almost 110GB but in DB still it shows as 100GB

  i tried to shrink the db but it's not getting shrink.

  Please suggest if any

  Thanks

  Rajo

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  January 24, 2011 at 10:33 pm

  #1277607

  Unless you are having disk space issues requiring you to make your database(s) small, don't shrink them. They will only have to grow later as additional data is added to the database.

• Rajo

  SSC Enthusiast

  Points: 145

  More actions

  January 24, 2011 at 10:45 pm

  #1277613

  Hi Lynn,

  thanks for ur reply.

  yes, i am having the space issue, thats y i truncated some records.

  Now the backup file size(30gb) becomes as expected. but the DB (.mdf file) size still 110GB.

  rajo

• Meet George Jetson

  SSChampion

  Points: 11638

  More actions

  January 25, 2011 at 2:42 pm

  #1278203

  What is the unused space within the db

  select [FileSizeMB] = convert(numeric(10,2)

  , round(a.size/128.,2))

  , [UsedSpaceMB] = convert(numeric(10,2)

  , round(fileproperty( a.name,'SpaceUsed')/128.,2))

  , [UnusedSpaceMB] = convert(numeric(10,2)

  , round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))

  , [DBFileName] = a.name

  from sysfiles a

  Chris Powell

  George: You're kidding.
  Elroy: Nope.
  George: Then lie to me and say you're kidding.

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  January 25, 2011 at 4:48 pm

  #1278245

  chris.s.powell (1/25/2011)

  ---

  What is the unused space within the db

  select [FileSizeMB] = convert(numeric(10,2)

  , round(a.size/128.,2))

  , [UsedSpaceMB] = convert(numeric(10,2)

  , round(fileproperty( a.name,'SpaceUsed')/128.,2))

  , [UnusedSpaceMB] = convert(numeric(10,2)

  , round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))

  , [DBFileName] = a.name

  from sysfiles a

  Just as a word of warning from BOL 2005

  sysfiles (Transact-SQL)

  This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead.

  In other words use sys.database_files

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]