Best Practices for Large DB

[louis nguyen]

Hi All,

My question is what are the best practices for administering large
DBs. (My coworker is the DB administrator. I'm more of the
developer. But slowly being sucked in.) My main concern is that we
have some DBs that take approx 3 hrs a night just to rebuild the
indexes. I know that with MSSQL 2000, I can use partitioned views to
break out the table(s) into smaller databases and tables. But we also
have an older server that runs MSSQL 7. Lastly how do you handle
drive space issues? Do you spread out the DB across multiple MDF
files on different drives? Thanks in advance.

[Sjaak van Esdonk]

lo************@hotmail.com (louis nguyen) wrote in message news:<b0*************************@posting.google.c om>...

Hi All,

My question is what are the best practices for administering large
DBs. (My coworker is the DB administrator. I'm more of the
developer. But slowly being sucked in.) My main concern is that we
have some DBs that take approx 3 hrs a night just to rebuild the
indexes. I know that with MSSQL 2000, I can use partitioned views to
break out the table(s) into smaller databases and tables. But we also
have an older server that runs MSSQL 7. Lastly how do you handle
drive space issues? Do you spread out the DB across multiple MDF
files on different drives? Thanks in advance.

Large databases can be spread using multiple filegroups. Each
filegroup can be placed on a different drive. The extension for the
..mdf file on the new filegroup(s) wil be .ndf. So on the original
filegroup you will have for example pubs.mdf and on the new filegroup
you will have pubs.ndf. What you can do is add a new filegroup (on a
new drive) and (re)build all of the indexes on that new filegroup.
This way your tables and indexes will be spread on different drives,
which also speeds up inserts on the database.