June 4, 2010 at 9:43 am
This is a question about how to best reorg and maintain a Sql Server 2005 database given some environment constraints. I just inherited support for a 150G database that was installed as part of a vendor application, and since it's a data collection app, the database is bound to continue to grow at a fast pace. In the past I've always had multiple drives available to lay out database and log files separately, but this server has one drive with 600+G available and that's where everything sits right now. The vendor has told the user of the app to simply let the database autogrow unchecked, but this is not realistic given that when the space runs out, that's all there is. I have convinced them to start doing routine purges of older data and after one of these purges ran, was able to shrink it and gain back 7G free space, but this was just a stop gap measure.
Any suggestions on how best to reorg this database and subsequently maintain it, from anyone facing a similar situation with limited resources?
June 4, 2010 at 10:50 am
Shrinking causes fragmentation. Leave the space available for future growth.
June 4, 2010 at 11:02 am
After a purge, rebuild the clustered index of the table purged. Leave the free space within the database, SQL can and will reuse the available space in the data file
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply