Why is this happening?

• Julian Kuiters

  SSCertifiable

  Points: 6585

  More actions

  August 24, 2004 at 6:22 pm

  #520434

  Randy

  Try these simple steps:

  -- Ask SQL Server to update and display file usage

  EXEC sp_spaceused @updateusage= 'TRUE'

  -- Get SQL Server to DEFRAG the data inside the files,

  -- and then remove excess free space

  DBCC SHRINKFILE (Yourdatabasename)

  You should then see less free space in your files.

  The DBCC SHRINKFILE task may take a long time, depending on how big your database is and how badly fragmented the data pages inside the database files are. Probably best to read up about the commands before running them. It doesn't defrag the file in your filesystem! Just the data inside the file.

  There are some limitations of course! If when you CREATE/ALTER the database, you set a starting size of the database, you cannot shrink the files to be smaller than that starting size. So if you create a database with 2GB size, and only put 500MB of data in it, you can't shrink it back down to 500MB.

  Also, your data files may grow if part of your database maintenance plan is to reindex your databases.

  ---

  Julian Kuiters
  juliankuiters.id.au

• RandyB

  SSCommitted

  Points: 1565

  More actions

  August 25, 2004 at 8:21 am

  #520520

  Allen and Julian,

  Great stuff. I am going to drop the clustered index off my biggest two tables and re-create as non-clustered. I will schedule the Optimization job to run this weekend to Reindex and Shrink the database. If the problem isn't fixed with the index modification, I will manually shrink it.

  If that doesn't work...we're getting quotes for SQL2K.

  Thanks alot,

  Randy

• kknudson

  SSCarpal Tunnel

  Points: 4986

  More actions

  August 25, 2004 at 8:49 am

  #520532

  Julian

  I believe the restriction of not being able to shrink below original allocation size went away with 2k.  At least if you explicitly state the shrink to size.

  I will try and do a test over the weekend.

  
  KlK

• Julian Kuiters

  SSCertifiable

  Points: 6585

  More actions

  August 25, 2004 at 6:00 pm

  #520631

  I wish... I've had to deal with a number of databases that had an initial / minimum size specified too high.

  to quote from BOL:

  The target size for data and log files as calculated by DBCC SHRINKDATABASE can never be smaller than the minimum size of a file. The minimum size of a file is the size specified when the file was originally created, or the last explicit size set with a file size changing operation such as ALTER DATABASE with the MODIFY FILE option or DBCC SHRINKFILE. For example, if all the data and log files of mydb were specified to be 10 MB at the time CREATE DATABASE was executed, the minimum size of each file is 10 MB. DBCC SHRINKDATABASE cannot shrink any of the files smaller than 10 MB. If one of the files is explicitly grown to a size of 20 MB by using ALTER DATABASE with the MODIFY FILE option, the new minimum size of the file is 20 MB. To shrink a file to a size smaller than its minimum size, use DBCC SHRINKFILE and specify the new size. Executing DBCC SHRINKFILE changes the minimum file size to the new size specified.

  ---

  Julian Kuiters
  juliankuiters.id.au