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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 DBCC checkDB

Author  Topic 

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-12-22 : 10:32:56

Will DBCC checkDb command genrate a big teansaction log?
I've shirked the transaction log file ( one time)and start hourly transaction log backup yeasterday.. it shriked down to 1GB.
But this morning it bounced back to 10gb again.. DBCC checkDB ran yesterday night. I now DBCC reindex can generate a big transaction log file.. how about DBCC checkDB process?



Kristen
Test

22859 Posts

Posted - 2006-12-22 : 11:03:52
I'd be surprised if DBCC CHECKDB generates ANY log. I would expect it to only Read the data (unless you are using "Allow data loss" of some similar setting).

Anything else going on that is hammering the log? Big query? Index maintenance (as you said) or maybe UPDATE STATS?

Kristen
Go to Top of Page

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-12-22 : 13:14:28
I can see the maintenance plan ran DBCC check DB command last night.. and the size of transaction log file got huge again this morning..

The"Check database integrity" option is chekced on integrity tab in the database maintrnance ...Is this the one that runs DBCC checkDb command?

I did unchecked "Reorganized data and index pages" on optimazations table in database maintenance plan( this is the one that runs DBCC reindex command right?)

Alos, I have checked Remove unused space from datbase files. Shrink database when it grows beyond 50mb
amount of free space to remain after shrink 10% of the data space..




Go to Top of Page

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2006-12-22 : 15:36:31
The shrink of the database is what is doing it to you, then. When you move data from the end of a file (so you can shrink), the move is logged. If you are not hurting for diskspace, I would suggest not shrinking the database(s).
Go to Top of Page

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-12-22 : 16:26:59
thanks..
ware you saying shrink datafile is causing the expanding of log file out fo control? I thought it;s causing by rebuilding index every night...


Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-12-23 : 02:44:43
Don't use SHRINK. It will just increase the inefficiency of your database (apart from any impact on your logs)

Kristen
Go to Top of Page
   

- Advertisement -