February 22, 2006 at 8:58 am
I am trying to get my head around many of the issues related to maintaining some of our larger databases.
Our largest database is roughly 10 GB. Our largest table in the database has 15,783,725 rows with a size of 9.8 GB and an index size of 2.8 GB according to the taskpad view. It has one clustered index and 13 indexes. The clustered index has a prefix of PK_*** Four of the indexes have a prefix of IX_*** and the remaining nine indexes have a prefix of _WA_Sys_***.
I have been reading a lot about reindexing, checkdb, shrinkdb, etc.
First question, my current rough idea for our new maintenance plan looks like this:
Daily:
1) checkdb
2) backup db
3) backup tlog (tlog backups will run throughout the day as well)
Weekly:
1) checkdb
2) backupdb
3) shrinkfile db
4) backup tlog
5) shrinkfile tlog
6) file system defrag
7) reindex (not an indexdefrag)
I keep reading that shrinking the database files is wasted I/O but, we are renting SAN space, so we need to be as efficient as possible with disk space. We plan to shrink the db before the tlog backup so that we can reclaim the tlog space created during shrinking the db.
I have read that putting your indexes into separate filegroups can reduce fragmentation and thus improve performance. Given the size of our indexes, this seems to make sense to me. Should I move all indexes, clustered and nonclustered to the separate filegroup?
Also, someone previous to me had created a second db file for this database, but they are both in the same file group. Should I leave that as is, or do I need to do something about the second file?
Is there anything I am missing? I think I have caught all the main points for performing regular maintenance. I have already collected, created and modified a set of stored procs to do most of these tasks, so it will just be a matter of scripting a few jobs.
Thank you,
Kevin
February 22, 2006 at 3:25 pm
only nonclustered indexes can be moved to a separate filegroup. your clustered index stays with the table.
If you're looking to get rid of that other file, you will have to dbcc shrinkfile with the emptyfile option. Then you alter database to drop the file if you want to.
the _WA_Sys_***. are statistics.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply