October 4, 2004 at 4:51 am
I have an issue at a client site that is driving me nuts.
Once a week (not always at the same time)a table in our db seems to corrupt, only around 2000 rows in the table. The users report a timeout issue from the front end and running a select statement on the table from qa to return all rows takes 20 minutes to return results.
The client has other db's on the same sql server which they report work fine, i have no remote access to the db so have to do all my support over the phone with their IT dept.
Tried running dbcc checktable and it ran for 2 hours before i finally stopped it, a workaround for the issue is to backup the db in its existing state then restore it. That cures the issue, until next time.
Any advice would be greatly appreciated.
Thanks
Nick
October 4, 2004 at 1:05 pm
How big is the database? I've had a problem with queries locking up my database (182+ GB) and things being really slow. I couldn't run DBCC SHOWCONFIG at all. Finally rebuilt the indexes (DBCC DBREINDEX) and now everything is working again. Maybe you need to give that a try.
In your case DBCC DBREINDEX will probably be better than DBCC INDEXDEFRAG.
-SQLBill
October 4, 2004 at 1:14 pm
Can you check if there are any open transaction creating issues. Try running DBCC opentran.
October 5, 2004 at 4:48 am
Thanks for you advice,
db is very small only 500mb
October 5, 2004 at 8:31 am
Now's the time to start gathering statistics if you haven't already.
What time(s) of day does this happen?
Is it always the same time?
Is is every day? If not, is it the same day(s) of the week (ex. every Monday and Wednesday)
What else is happening at that time?
Is there any system jobs occuring?
Is the sysadmin/network admin performing any work such as backups?
Is there antivirus programs running at that time?
Finally, search on this site for information about using Profiler and set up a SQL Server Profiler trace to capture what's going on.
-SQLBill
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply