September 19, 2005 at 7:19 am
Hi,
I have created a maintenance job that reorganises data and index pages and changes free space to 10%. The job is only working on user databases but appears to be causing problems.
Each day the server pings but I cannot connect to it suggesting that CPU usage is high. About lunchtime however the server is back to normal which suggests that the job is finished.
The job history shows that the job last ran on the 14th even though the job is a daily job and there are no errors in the history or log that I can see.
So, why is the job history not being updated and why is the server hanging each day? One of the databases is 20Gb in size but this should not matter should it? The job starts at 04:00 each morning.
I am assuming that it is this job that is causing the problems but cannot confirm this because I don't know how to.
Thanks
September 20, 2005 at 2:53 am
Couple of things here.
1. What does sql agent job indicate as the next time this job is scheduled to run?
2. Expand the job history and see when it has been running.
3. Check the sql error log to see if the DBCC is running for this database.
4. How did you create the job? Via a maint plan wizard?
5. Check the size of the tran log for this database, my guess is its huge and grows when the reorg has run. Try shrinking it back then run a reorg and see how much it grows by. If you are backing up the tran log then this could also create massive tran log backup files.
Point 5 log growth might be the cause of some noticable slowdown.
Derek
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply