Wednesday, March 7, 2012

DBCC DBREINDEX Status

I have inherited a SQL SERVER 2000 project from a colleague and we have a monthly SQL SERVER job that reindexes all the indexes in a reporting table (it contains archived records up to 1 year old, averaging close to 15 million rows). This table receives fresh data from another table in a different database every night (close to 30000 rows).

Since there is a monthly job that rebuilds all the indexes, there will be a single day every month when the rebuilding of indexes will clash with insertion of those new rows. I haven't been able to find a specific TSQL construct that I can use in my INSERT stored procedure to see if the table is currently locked for rebuilding indexes before trying to insert the rows. Specifically, I wouldn't insert the rows that night because the table is being reindexed.

Please let me know if there is such a facility in Server 2000 to find out the current indexing status. Also, I wouldn't mind alternate solutions to the above problem.

Thanks in advance!
Your ETL Job could check to see if the re-indexing Job is running, and if so, have the ETL job take a pass for an hour [ WAITFOR DELAY '001:00:00' ], then check again, etc.|||

Sorry to ask the obvious, but how to check if the job is running? Specifically, from within SQL Server and outside SQL Server? I guess at this point in time I am just curious to find out both alternatives.

Thanks for the reply!

|||

With SQL 2005, you could use sp_helpjobactivity.

However, with SQL 2000, it is a bit more trouble. You can check the msdb.dbo.sysjobhistory, column run_status, looking for run_status = 4 (in progress).

|||

Great tip!

OK, here is a more reliability-related question... I have never dealt with the SQL Server Job Agent (believe me, I have complete faith in SQL Server 2005, but this is 2000's job agent), but I am hoping to find out how reliable the Agent is in 2000? I only bring up this question because you mentioned in your previous post that "with SQL Server 2000, it is a bit more trouble." Of course looking at the schema of that table (sysjobhistory), this doesn't seem to be too hard!

Thanks again for the quick replies, Arnie!

|||

My experience with SQL 2000 SQL Agent is that it is very reliable.

However, sometimes the JobHistory table seems to have some latency. If a Job 'should' be running, and the run_status <> 4, I will also cross check a the start_time against the end_time. Keep in mind that an entry is added to the JobHistory table when the Job starts, and if the end_time is NULL, then it has not finished.

|||

Alternatively you could use the stored procedure 'sp_help_job' to determine the job's status.

See here for more info:

http://www.databasejournal.com/features/mssql/article.php/10894_3491201_2

...or here (note that this link refers to SQL Server 7 but the syntax should still apply to SQL Server 2000):

http://doc.ddart.net/mssql/sql70/sp_help_27.htm

Chris

|||Thanks, Chris!

No comments:

Post a Comment