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)
 Log Backup Error		  New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

jung1975
Aged Yak Warrior

USA
503 Posts
Posted - 12/22/2006 :  13:06:14  Show Profile  Reply with Quote
"1. Disable the job that performs the reindex (perhaps you are using maintenance plan, so it's called optimizations)"

I did unchecked "Reorganized data and index pages" in Optmixations tab in Database Maintence Plan ( SQL 2000). This will stop rebuilding index right?

how about "check database integrity" on integrity tab? Should I uncheck this one as well to keep the transaction log size in control?

I can see it ran DBCC check DB command last night.. and the transaction log file size get huge again this morning..
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35007 Posts
Posted - 12/22/2006 :  13:13:11  Show Profile  Visit tkizer's Homepage  Reply with Quote
Well you can uncheck that option, but you also could have just disabled the job.

You definitely need the integrity checks. That's the one that checks for database corruption. I use this instead:
http://weblogs.sqlteam.com/tarad/archive/2004/06/16/1606.aspx

DBCC CHECKDB will not cause the transaction log to grow. You've got some other process causing it.

List out the jobs that are enabled for us and we'll see if any of those can cause it.

Tara Kizer
Go to Top of Page

jung1975
Aged Yak Warrior

USA
503 Posts
Posted - 12/22/2006 :  13:27:02  Show Profile  Reply with Quote
"Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'Ndhdsk13' as 'aa23\sqladmin' (trusted)
Starting maintenance plan 'App DB Maintenance Plan' on 12/22/2006 1:00:01 AM
[1] Database AppRAI: Index Rebuild (leaving 10%% free space)...

Rebuilding indexes for table 'Account'
Rebuilding indexes for table 'AcctClass'
Rebuilding indexes for table 'AcctHist'
Rebuilding indexes for table 'Address'
Rebuilding indexes for table 'Alloc""

I can see the above process ran everynight in the job log file( optimazation)...Rebuilding index..right?

Since I took off the Reorganized index option, it won't trying to rebuild the index every night correct?


thie was what it used to be:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID D07D3DDC-E37C-4BD2-8FA1-AD020FA264A7 -Rpt "D:\MSSQL\MSSQL\LOG\App DB Maintenance Plan0.txt" -DelTxtRpt 2WEEKS -WriteHistory -RebldIdx 10 -RmUnusedSpace 50 10 '


After I took the reorganized index option from the maintenence plan:

EXECUTE master.dbo.xp_sqlmaint N'-PlanID D07D3DDC-E37C-4BD2-8FA1-AD020FA264A7 -Rpt "D:\MSSQL\MSSQL\LOG\App DB Maintenance Plan0.txt" -DelTxtRpt 2WEEKS -WriteHistory -RmUnusedSpace 50 10 '

-RebldIdx part is gone now..:)
Edited by - jung1975 on 12/22/2006 13:28:22
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35007 Posts
Posted - 12/22/2006 :  13:55:58  Show Profile  Visit tkizer's Homepage  Reply with Quote
I don't use maintenance plans, so I'm not very familiar with them. But yes it would seem that it shouldn't run the reindex again since the RebldIdx is gone.

You still have the shrink option set in your plan though, but that's not what is causing the tlog expansion to occur.

Hopefully you'll see the tlog not expand after tonight's jobs run. If it works out, then remove the shrink portion to avoid performance issues.

Tara Kizer
Go to Top of Page

jung1975
Aged Yak Warrior

USA
503 Posts
Posted - 12/22/2006 :  15:11:16  Show Profile  Reply with Quote
Tara, thank you so much for all of your comments..
Happy holiday~~~
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 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.08 seconds. Powered By: Snitz Forums 2000