HELP! SQL 2000 Maintenance Issues 2

[1Coolhand]

Question:

Just converted from SQL7 to SQL2K. Setup some maintenance plans for the user databases and system databases. 2 plans, 1 does reorg and integrity, the other does indexes. Reorg starts at 1 am, integrity at 2 am and indexes at 3. My questions is two-fold. With this maintenance plan running, can users still access or does the database get dumped to single-user mode? Reason being is as soon as the maintenance plan kicked off at 1:00, we had a boatload of issues and ended up rebooting the server and the user database went through recovery. Also, is this really the best maintenance plan or does someone have something else better to suggest. I was thinking to run these plans on the weekend, say Sunday morning at 1 am as opposed to every day. I'm a little weary of running maintance plans during the week if this is going to be the case.

BTW, this is an 8-way box with 2.6 Xeons, 8GB RAM and about 6TB of Fiber attached storage on RAID 5, RAID 0+1, etc.

Thanks!

 

[JayKusch]

In your MaintPlan check the Integrity section. Make sure the "Attempt to repair any minor problems" check box is UNCHECKED. This may be what is causing your problem.

You probably do have some issues w/ your DB, most likely a table w/ corruption. You should try to run a

DBCC CHECKDB(MyDB)

to verify if this is the case or not.

Thanks

J. Kusch

 

[SQLBill]

What do you mean by 'reorg'? Do you mean the indexes are being rebuilt?

What problems are you having?

Rebuilding the indexes can slow down queries and the TEMPDB can get huge.

-SQLBill

 

[1Coolhand]

Jay, I'm going to run a dbcc checkdb(mydb) with estimateonly to determine if the tempdb size is slowing down this process and will adjust accordingly.

Bill, reorganizing and indexing pages option is what I meant, sorry for the confusion. Attached is what came out of the log from this morning at 1:00am:

Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'IMGSVR' as 'FNC\Administrator' (trusted)
Starting maintenance plan 'DB Maintenance Plan1' on 6/2/2004 1:00:08 AM
[1] Database itiserver: Index Rebuild (leaving 10%% free space)...

Rebuilding indexes for table 'abatable'
Rebuilding indexes for table 'action'
Rebuilding indexes for table 'actionlist'
Rebuilding indexes for table 'actionlistxaction'
.... so forth and so on, then further on down I get:
Rebuilding indexes for table 'folderxkey117'
Rebuilding indexes for table 'folderxkey118'
Rebuilding

That is just a line that says Rebuilding, nothing else, and it just sat there until I rebooted and went through a recovery.

Everything I've come up with states that the tempdb is a huge part of this equation and right now I am getting an event under application viewer that says:

Error: 9002, Severity: 17, State: 6
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.

If you look at this, it may be part of my problem, but the steps to take to fix this elude me.

BTW, the database in question is split into an .mdf and .ndf file with log (.ldf). Total database size is around 208GB.

Any help is greatly appreciated.

 

[JayKusch]

Based on the size of you DB and the index rebuilding that was taking place, I am pretty sure you taxed out the space on the drive that the TempDB resides on.

Instead of using a MaintPlan to reindex your tables, I would create a few jobs that index a few tables at a time. Once the job finishes, you could shrink the TempDB and run the next indexing job.

The reindexing of the table is definately causing issues.

Thanks

J. Kusch

 

[SQLBill]

Here's the issue with rebuilding indexes and it doesn't matter if they are being dropped and rebuilt or just defragged.

When SQL Server rebuilds/defrags indexes, it has to keep track of how they were when it started. If it fails, SQL Server needs to be able to rollback the rebuild/defrag. So it keeps track in the TEMPDB. This causes TEMPDB to G-R-O-W. If there's not enough room or it can't autogrow fast enough, the rebuild/defrag fails and everything rolls back. But it will first 'lock up' and ask you to 'backup the TEMPDB' to free up space.

Solution, do it in small chunks (a few indexes at a time) or give TEMPDB more room.

-SQLBill

 

[1Coolhand]

Okay, everyone is in agreement to do small chunks/tables at a time. Seeing as how I am not too SQL savvy, can you provide a suggestion/procedure for doing small chunks.

Thanks for the input.

 

[SQLBill]

Here's the way I do it...

I made a list of all my indexes and their sizes.
Then I divided them up into 'chunks', leaving large indexes as their own 'chunk'.
Then create jobs to run DBCC INDEXDEFRAG.

Check the BOL (Books OnLine) for the syntax.

-SQLBill

BOL can be found at Start>Programs>Microsoft SQL Server>Books OnLine. It's installed as part of the Client Tools.

 

[1Coolhand]

Okay, I can handle that. As for the other maintenance tasks, what do you reccommend? Integrity check, etc. This will take care of the indexing, just curious about the other options and if I should run them. I already use BE9.1 to back up all of the SQL databases, so I am covered from a backup standpoint.

Thanks for all the advice.