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