i wrote a custom db maintenance procedure to reindex various tables in my
database. depending on the parameter passed to the procedure, a group of
tables get reindexed. There is a scheduled job that runs daily that calls
this maintenance procedure. im having an issue where this job is failing
about 25% of the time. i know that the issue ocurrs while attempting to
reindex some of the largest tables in the database because i'm logging the
reindexing. the failure causes the server to completely freeze and we are
forced to hard reboot.

the script looks like this:

DBCC DBREINDEX ('myDB.dbo.myTableA')
INSERT INTO tLog (log_type, log_text) values (2,'Done reindexing
myTableA')

DBCC DBREINDEX ('myDB.dbo.myTableB')
INSERT INTO tLog (log_type, log_text) values (2,'Done reindexing
myTableB')

DBCC DBREINDEX ('myDB.dbo.myTableC')
INSERT INTO tLog (log_type, log_text) values (2,'Done reindexing
myTableC')

Not really a DTS issue. Try server for a start. You may also want to try and
find an error, have you check the the "SQL Server Logs"?

--
Darren Green
http://www.sqldts.com
http://www.sqlis.com

"JT" <jt (AT) nospam (DOT) com> wrote

Reindexing a large table can max out the processors for some period of time
and this is normal. It is unlikely your system needs to be rebooted but
more like it simply needs to finish what it is doing first. If you have
multiple processors you might want to set the MAXDOP at the server level to
one less than the total before you reindex the large tables. If you have
Hyper Threaded processors you might want to set the MAXDOP to only the
number of physical processors.

--
Andrew J. Kelly SQL MVP


"JT" <jt (AT) nospam (DOT) com> wrote