SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 DBCC checkDB
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jung1975
Aged Yak Warrior

USA
503 Posts

Posted - 12/22/2006 :  10:32:56  Show Profile  Reply with Quote

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

United Kingdom
22415 Posts

Posted - 12/22/2006 :  11:03:52  Show Profile  Reply with Quote
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

USA
503 Posts

Posted - 12/22/2006 :  13:14:28  Show Profile  Reply with Quote
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 - 12/22/2006 :  15:36:31  Show Profile  Reply with Quote
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

USA
503 Posts

Posted - 12/22/2006 :  16:26:59  Show Profile  Reply with Quote
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

United Kingdom
22415 Posts

Posted - 12/23/2006 :  02:44:43  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000