SQL Server 2000 DB Optimization running for long time

Question

SQL Server 2000 DB Optimization running for long time

Shankar Nandakumar

Valued Member

Points: 62
More actions
January 31, 2005 at 10:13 pm

#85366

Good day,
One of our clients is having a problem - The SQL Server 2000 DB Optimization Job is running continously for 8 hours+ before being cancelled, in the last 3 days.
Size of the MDF files - 7 GB
Size of Transaction Log - 6 GB
Available HDD space - 45 GB for the Backup Drive/ 25 GB for the DATA Drive
DB Maintenance Schedule is as follows:
Transaction Log back up - 12 AM (Everyday)
Optimizations job - 1 AM (Everyday)
DB Backup job - 2 AM (Everyday)
We found the Optimizations job is running for 8 hours+ and the DB Backup job also running continously for 8 hours+, which normally takes 30 mins to complete. In the last 3 days we cancelled both the job, as it was affecting the performance slowing down the DB access. Since the jobs are cancelled there is no failure message in the job/maintenance history and the backup report for the optimizations shows indexing complete around 50%.
We ran a manual DB backup that took 40 mins as normal. Potentially the Optimaztions job is affecting the Back up, which starts while the former is in progress.
Tried making the DB simple, Shrink it but still the Optimizations is running too long. Normal time is 30 mins.
Would greatly appreciate an early response for this weird problem which we can't figure out.
Thanks in advance,
Shankar

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Vladan SSC-Insane Points: 22072 More actions · Answer 1

Hello Shankar,

I don't know precisely what your optimization job does - we have several such jobs (indexdefrag, indexrebuild, integrity check). Anyway, most probably you are speaking about the sqlmaint optimization plan with indexrebuild... IMHO the problem is in the concurrent backup; what we do is : close all traffic, disable all backup jobs, set restricted user mode and simple recovery model, run sdqlmaint and then reset everything back. This job runs only once every 2 weeks though, the daily job only performs DBCC INDEXDEFRAG, without limiting the use of DB.

This may be impossible if you need to have the DB accessible all the time, but I would at least try to make sure that no backups are started during optimization.

HTH, Vladan

gbn SSCertifiable Points: 6429 More actions · Answer 2

I've seen instances where rebuilding the statistics can take some time.

I used to sample 100% during optimisations (to try and head off stats rebuild during working hours) but removed then left it to default and the backup windows dropped dramataically

vinival Grasshopper Points: 14 More actions · Answer 3

Hi Shankar, perhaps it's the maxsize of log file? (sp_helpdb dbname)

Shankar Nandakumar Valued Member Points: 62 More actions · Answer 4

Thanks for all your reply and suggestions.

The restart of the Windows Server (which restarted SQL Server) had fixed the problem (proves its a MS product ). Optimizations is back to normal at 36 mins. Optimizations job is one of the job created as part of the SQL DB Maintenance plan. It is the complete reindex of all the tables.

Cheers,

Shankar

sa24 SSCrazy Eights Points: 8027 More actions · Answer 5

It is possible that there is an application which would continously access a database, hence the optimization job would wait to be granted an exclusive lock. When you rebooted windows, the connection might have been killed. If this happens again, try to query sysprocesses or run sp_who and see what the job is waiting on and you can then try to kill the spid(if reqd.) which is blocking the job.