September 8, 2009 at 12:45 pm
Hi,
I have a job that rebuilds index on a database. The job first takes the db in single user mode , rebuilds the indexes and then gets it back to multiple user mode. There are scenarios when the job fails and then the db remains in the single user mode before some one changes it to multiple user mode the next day.
What would be impact of removing the step where the db is taken to single user mode apart from the long run time ?
Is there a way that the db can be taken to single user mode if the run time of the job is more than lets say 120 mins?
Thanks.
September 8, 2009 at 2:26 pm
look at ONLINE option for index rebuild, see if it can be done on your particular table. Beyond that, you should be able to set up an Agent job having 3 steps. Step one put DB in single user, Step two rebuild indexes, Step three back to multi-user. Insure that step three executes regardless of the success of failure of step two.
The probability of survival is inversely proportional to the angle of arrival.
September 8, 2009 at 2:54 pm
Even I would follow the steps given by sturner. Else use DBCC INDEXDEFRAG which is online but I would prefer DBCC DBREINDEX.
SQL DBA.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply