August 25, 2014 at 2:14 pm
Hello,
I have a vendor database (Solarwinds) that has over 2000 indexes. Some of the nights the reindexing job runs, it executes successfully. But most of the time, the job fails.
When I check the logs, the reindexing is trying to reindex an index while the table still has a lock on it. The previous index may still be rebuilding and has a lock on the table. When the code tries to rebuild the next index, the job is failing.
Therefore, I need to script a stored procedure that will loop through all 2000 indexes doing the following:
1) Gets the name of the index
2) Get the table associated with the index
3) Check that there is not a lock on the table
4) Reindex that index
5) Proceed to the next index
I can write a script that will loop through sys.indexes where type = 1 or 2.
However, what is the code to see if a table is available or if it still has a lock on?
Error Message:
Executed as user: Transaction (Process ID 67) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001] (Error 1205). The step failed.
Things will work out. Get back up, change some parameters and recode.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply