August 2, 2002 at 12:13 pm
Hi all,
That is my situation:
I’ve a store procedure that starting month running well (taking close to 30 minutes to finish), but during the month this procedure are improving the time to complete, taking more than 2 hours to complete and some time it fail.
I’ve tried every thing that I know to figured out this problem, like run ckeckdb, checkcatalog, reindex and update statistics every sunday without success. This problem are driving me crazy and for the last troubleshoot I created a new DB and transferred all in the problematic DB to new DB by DTS, data fist, store procedures and recreation of index after. For my surprise it’s work and the system start to run well again.
For the database maintenance routine, I do ckeckdb, checkcatalog, reindex and update statistics on all database in all server, every Sunday.
I’d like know if there are another thing to do than recreate this DB every new month and if some know why this ‘idiot’ troubleshot are working ??
Ps.: I didn’t stop the SQL or reboot server... I’m just create the database.
August 2, 2002 at 12:25 pm
How often does the stored procedure run and is it a particularly complex one?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
August 2, 2002 at 12:32 pm
The procedure runs 2 or 3 times most at the end of the month.
Its very complex, but I've the same procedure in another servers and this didn't show the problem.
August 2, 2002 at 2:29 pm
Tough to say. Are teh servers the same hardware? Could be that. Could be fragmented. Might want to check with dbcc showcontig and rebuild clustered indexes.
Steve Jones
August 7, 2002 at 5:50 am
The hardware is the same, including memory and configuration.
About the tip I’ve some doubt:
To rebuild a clustered index I can do with dbcc dbreindex or I need to dorp and create the index?
At the result of dbcc showcontig if I have a difference of 00.10% at Avg. Page Density (full), comparing the same table between 2 different DB, is it a problem or it don’t make any difference?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply