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-07-16 : 09:31:23
|
| Paul writes "Log grows to 12 gig during db maint plan. Background:As part of a db maint plan the integrity check runs Sunday am. Settings include include indexes and attempt to repair minor problems. In addition the reorganize data and index pages is checked with change free space percentage to 10% (default). Remove unused space checked as well. The database size is 12 gig. The logs are on a separate partition from the data files with max space of (coincidentally) 12 gigs. During the Sunday am process the log grows to over 12 gigs and the job fails because it runs out of room on the partition. Environment:SQL 2000, SP2Windows 2000 serverQuestion:What causes the log file to grow so large? The dbcc or the reorganize? Is there anything I can do to modify the job to shrink the t-log at some point to prevent the log from filling the partition?" |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-07-16 : 11:27:22
|
| Your database is in "Full" recovery mode. This means that all bulk operations (and index rebuild is considered a bulk operation) are fully logged. You have a couple of options to keep your log from growing.1. Switch your database into "Bulk-Logged" recovery mode before you run your index rebuild. And then change it back while you are done. In "Bulk-Logged" mode, index rebuilds will not cause the log to grow. BOL has more details on recovery modes. 2. Code reindex operation manually, using the T-SQL commands (as opposed to using the maintenance plan). Make sure that as your code loops through the tables in your database, it checks % of logspace used (DBCC SQLPERF (LOGSPACE)) and runs log backup if necessary.Edited by - izaltsman on 07/16/2002 11:29:36 |
 |
|
|
|
|
|
dbcc makes huge t-log