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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-06-19 : 12:24:16
|
| karthik writes "The worst thing I found out today is that a reindex job for a couple of tables(high volume) in 2000 fills around 5 GB of transaction log entries but the same job running in 7.0 server with the same database and tables, fills only a few MB in the transaction log. I used Log explorer and found out that in 2000 he makes lot of log entries like Format page, modify rows etc,.No user connections during the testing phase.Can anyone explain this..." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-19 : 12:45:00
|
| It's probably related to the recovery model on your SQL 2000 database. If it's set to full, it will log just about everything than can be logged. SQL 7.0 doesn't quite do this. Try changing the recovery model to another setting (read Books Online first to determine which one you should use) |
 |
|
|
karthikp
Starting Member
1 Post |
Posted - 2002-06-19 : 13:25:20
|
| Yes the recovery model is 'Full' as the data is highly sensitive and transaction volume is high. I can't set simple recovery or bulk for production databases. Any other solution for this...? |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-06-19 : 16:31:39
|
| First of all, with Bulk-Logged mode you get pretty much the same type of recoverability that you used to have in SQL7. So if it worked OK for you then, I don't see why you can't use Bulk-Logged now (remember -- you can switch to Bulk-Logged just before you run the reindex, and switch back to Full recovery mode as soon as you are done). Anyways... The only other thing you can do is to monitor log utilization during your reindex (by running DBCC SQLPERF (logspace) as you go from one table to the next), and once the used portion of your log hits a certain percentage, you could issue a BACKUP LOG statement and proceed with reindex once the backup completes. |
|
|
|
|
|
|
Reindex job fills up the Transaction Log in SQL Server 2000.