| Author |
Topic |
|
Tracey
Starting Member
40 Posts |
 Posted - 2004-06-22 : 22:44:20
|
| Hello Everyone,The Check Database Integrity Job(Excluding indexes) of a Database(30 GB, SQL SERVER 7.0-sp4) runing long time. It is scheduled to run every night, but last 2 days its keep RUNNING forever( more than 8 hrs)(results in few internal problem), i have to manually stop the job.I have checked the TempDB space needed using DBCC Checkdb with Estimate Only. There is Enough Space in TempDB !Would be any other issues that could make the job run forever!Maybe Disconnect All users before run this job?Any suggestion/help much appreciated!RegardsTracey |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-06-22 : 22:50:30
|
| do you have any other maintenance jobs running at the same time?-ec |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-22 : 23:30:19
|
| What options do you have checked for this?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Tracey
Starting Member
40 Posts |
Posted - 2004-06-22 : 23:50:13
|
| There is no other job running during this time(there is one which runs after 2hrs)Option i have checked:Check Database Integrity,Exclude indexesTa |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-23 : 00:07:22
|
| Open the job that schedules the maintenance, go to the step, then the advanced tab. Have it write to an output file. When you run it, you will have an output file you can copy and inspect to see where you are at in the process and what is taking so long.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Tracey
Starting Member
40 Posts |
Posted - 2004-06-23 : 00:14:41
|
| Ok will try that,also i have looked the Database Maint Plan history and before this problem..it only took 45 mins to runs this job :(dunno where the glitch is?Thanks |
|
|
|
Tracey
Starting Member
40 Posts |
Posted - 2004-06-23 : 20:19:03
|
| Ok,The job run and failed this time:Error 2501:[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find table named '(Object ID 905547176)'. Check sysobjects.[Microsoft][ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC printed error messages, contact your system administrator.Found this in MKB, will follow this:http://support.microsoft.com/default.aspx?scid=kb;en-us;288745Thanks for your help guys,Tracey |
|
|
|
Tracey
Starting Member
40 Posts |
Posted - 2004-06-23 : 20:32:58
|
| When i checked the SQL SERVER Log, i found this :DBCC CHECKDB (xxx, noindex) executed by NT AUTHORITY\SYSTEM found 0 errors and repaired 0 errors.But actually the job was Failed with the error in my previous post?Is this normal???Tracey |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-23 : 23:05:01
|
| And what does SELECT OBJECT_ID('905547176') give you?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Tracey
Starting Member
40 Posts |
Posted - 2004-06-24 : 00:21:39
|
| Nothing, there is no object under this ID.Have Checked that already.Will be there any corruption??Maybe i will run DBCC checkdb with Repair option!Tracey |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-24 : 00:23:28
|
| Have you ran DBCC CHECKDB with all error messages??MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Tracey
Starting Member
40 Posts |
Posted - 2004-06-24 : 00:39:42
|
| The job was part of Maintenance plan(Integrity).I never ran DBCC checkdb, but planned to do it soon(when users are off, so i can change the DB to single user and run DBCC with Repair option)Tracey |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-24 : 00:40:42
|
| I wouldn't run DBCC with repair option without running it with all info messages first. It's a good way to find yourself in a disaster recovery mode. Just my two cents of course.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Tracey
Starting Member
40 Posts |
Posted - 2004-06-24 : 00:46:10
|
| You are Right Derrick,I will run first with ALL MESSAGES mode and see if there is any error.I was confused with the SQL SERVER message where it said the job was success and no error.Tracey |
|
|
|
Tracey
Starting Member
40 Posts |
Posted - 2004-06-24 : 00:48:06
|
| just Wondering,Is it good to run dbcc checkdb with ALL_errormsgs while users are connected?ThanksTracey |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-24 : 01:19:45
|
| If you watch your CPU really careful. I prefer to only do it at night. Necessity has forced my hand a few times though.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Tracey
Starting Member
40 Posts |
Posted - 2004-06-24 : 21:49:05
|
| OkkieeDokiee,I changed the DB to single user mode and ran the job and everything was good.Thank you guys for your help! |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-06-25 : 03:48:12
|
quote:
Originally posted by Tracey
OkkieeDokiee,I changed the DB to single user mode and ran the job and everything was good.Thank you guys for your help!
Are you still seeing the job run 8+ hours?-ec |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-25 : 13:41:35
|
| Much easier not to use the maintenance plans for DBA routines. Here's a stored procedure that I wrote for DBCC CHECKDB that checks the integrity of all databases:http://weblogs.sqlteam.com/tarad/archive/2004/06/16/1606.aspxMaintenance plans are slower when you select the options to log it.Tara |
|
|
|
|
Maintenance Job running Forever