March 22, 2016 at 6:13 am
Hi all,
My backup maintenence plan is failing due to locking/blocking. I've set up to rebuild the indexes in the db every night and it is occasionally getting blocked by active connnections (users leaving app open after work)
This is the query blocking the index rebuild:
SELECT 0 AS Comp_Code, ' All' AS Comp_Name FROM dbo.tblCompanies UNION SELECT Comp_Code, Comp_Name FROM dbo.tblCompanies WHERE Archived = 0 ORDER BY 2 END
and this is the blocked index rebuild
ALTER INDEX [Code] ON [dbo].[tblCompanies] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
Is there a solution or do I have to go back to reorganise index?
Using Sql Server 2005 Standard Edition
Regards
March 22, 2016 at 6:48 am
You could write something that will loop through active connections and kill any that haven't done anything for longer than a particular period of time. Make sure you test thoroughly before doing anything like that in live.
John
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply