Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
wynng
Starting Member
8 Posts |
 Posted - 2006-05-22 : 10:28:27
|
| One of our databases currently has it's indexes rebuilt as part of a weekly maintenance job. It used DBCC DBREINDEX which works ok, the only problem is that the transaction log swells to about 6GB. There is a microsoft article [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx[/url] that compares DBREINDEX and INDEXDEFRAG for this purpose. As we are able to run this job on a regular basis and a large proportion of our data is static (i.e. from week to week only a small amount of it will change) it seems like INDEXDEFRAG would be the correct choice. I tried this however and the log file was huge and the job failed due to disk space! Has anyone had any experience with this or any recommendations on what to look at? Thanks! |
|
|
wynng
Starting Member
8 Posts |
Posted - 2006-05-22 : 10:30:32
|
| The server is SQL Server 2000, the database is about 8GB with approx 300 tables. It is set to full recovery. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-22 : 10:57:07
|
| We only use DEFRAG (rather than REINDEX) for our tables - except that we use REINDEX if the table has very few rows - I forget exactly, but something like < 1,000 rows.DEFRAG can be interrupted, so you could just run it for a set amount of time and you would still have the benefits on the tables that it had processed (but they would need the statistics rebuilding after the DEFRAG).We only Defrag tables that have fragmentation above a threshold (using DBCC SHOWCONTIG).You could additionally only defrag certain tables each night, so that the whole job was spread over the week. And you could increase the frequency of Log Backups during that time to reduce the strain on the log file (its got to be backed up at some time, so more often wont increase the total disk space required, just the number of TLog backup files).Kristen |
|
|
|
wynng
Starting Member
8 Posts |
Posted - 2006-05-23 : 15:47:33
|
| Thanks Kristen. I'll try a couple of those suggestions. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
DBREINDEX vs INDEXDEFRAG