Posted by Erland Sommarskog on 03/11/06 01:24

traceable1 (tracykc@gmail.com) writes:
> I run this job on the weekends in the evening, but we have several
> other load jobs going on at the same time.

OK, that makes sense.

> Of course I would not want a user to get this message, but like Joe
> mentioned, it would be great if it could have low priority, but
> continue to run.

Well, on SQL 2005 you could achieve this with the new TRY-CATCH handling.
Of course, the DEFRAG operation would be aborted, but the procedure
could continue to execute. On SQL 2000, however, a deadlock terminates
the batch, so you need cover up from somewhere outside SQL Server.

> What I think I'll try is Erland's recommendation to update my proc so I
> can have it restart where it left off. Since the DBREINDEX requires
> exclusive access, this will probably be my best bet. But it seems to
> me that the DEFRAG also requires exclusive access, since the job is
> running the DEFRAG when it's killed.

I think the likelyhood of getting deadlocks with DBREINDEX is small,
since it takes out one big fat table lock to keep everyone else out.

INDEXDEFRAG, on the other hand, is an online operation. Obviously, it
must still lock the pages it's currently working with. I'm not too
well versed with the internals of DBCC INDEXDEFRAG, but I could envision
that you get a deadlock, because the defragmenation collides with a
different process.

Again, this may work better in SQL 2005, where the online rebuild
makes use of the new snapshot technology. This permits most of the
defrag to be done without any blocking effect on users. (There is a
slight impact in the beginning and in the end.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]