Why is this happening?

Question

Post reply

Why is this happening?

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 1

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 · Answer 2

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 · Answer 3

kknudson

SSCarpal Tunnel

Points: 4986

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 · Answer 4

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