Menu
We have a SQL Server 2000 database that holds most of our teams databases running on a relatively old server.
Lately we have been having some weird slow down issues on some of our database calls in various applications. (So I know it isn't application specific)
It was mentioned to me that we should look into compacting some of our databases. What are some general rules of thumb for doing this?
Is compacting a database just considered to be general maintenance? Is there a magic number of databases, tables, or records that might lead to this slow down and thus be helped be a compaction?
Number of actively used databases: 6
Average size of database: 20MB with the exception of Fogbugz, that is about 11000MB
923
Shrinking the database to a minimum required and releasing most of the unused space is possible. This may be a positive move as the larger freed space will improve performance. But shrinking may affect your database performance significantly, if not properly done.
Recommendations. A shrink operation is most effective after an operation that creates a large amount of unused storage space, such as a large DELETE statement, truncate table, or a drop table operation. Most databases require some free space to be available for regular day-to-day operations.
Shrinking data files recovers space by moving pages of data from the end of the file to unoccupied space closer to the front of the file. When enough free space is created at the end of the file, data pages at end of the file can deallocated and returned to the file system.
You don't compact a SQL Server database (you "SHRINK" it) unless you really need to. It's not MS Access and won't reclaim unused space (well, it can, but it masks a more important problem)
More likely, you have a index/statistic problem:
For starters with that database size (small), I'd suggest you look
64
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
