How To Disable Multiple SQL Server Agent Jobs!

Hello and welcome back to the trenches of supporting SQL Server in your environments….

Today I thought I would share with you my experience with disabling multiple jobs in SQL Server 2008.  Here is the situation….

The other night I participated in a maintenance window that was to update drivers on our SAN.  So the pre-maintenance work I had to do before shutting down the cluster was to disable all the jobs in each instance.  Now, I am not sure if your systems are like mine… but I am talking about more than 100 jobs on each instance.  Now there is no way I am going to disable these jobs manually.

Now thinking about my days of SQL Server 6.5, 7.0 and 2000 I used to run the following in the MSDB database.

exec sp_configure ‘allow updates’,1
go
reconfigure with override
go

update msdb.dbo.sysjobs
set [enabled] = 0
where [name] = ‘Full Database Backups’
go

exec sp_configure ‘allow updates’,0
go
reconfigure with override
go

So I thought I would go with what I know for SQL Server 2008… BIG MISTAKE!  When I executed the T-SQL code above for this job it did work!  The problem came when I went to enable the job.  To enable the job I ran the same T-SQL above but change [enabled] = 1 and the job “appeared” to have been enabled.  The job listing in SQL Server Agent went from the color grey (disabled) to the color blue (enabled) and I thought everything was good.  Until the schedule for this job came and gone and the job did not run.  So I was like… uummmm…whats going on?

So I did some research and it turns out when you access (by that I mean update) the system tables directly specifically the msdb.dbo.sysjobs table some functionality does not get re-enabled.. e.g. Job schedules.  To fix this issue, you need to manually disable the job and jobs schedule manually and then re-enable both manually.  If you need to do this for the number of jobs that I have… it will take you a long time and chance for missing a job.

So now I was stuck… I had over a hundred jobs to fix and a short amount of time to do it in.  So I went online, used BOL and I came across the stored procedure sp_update_job stored procedure.  This procedure will enable the job and reset (reactivate) the schedule on the job.  So I wrote a T-SQL script that creates a T-SQL statement for every job I have in msdb.dbo.sysjobs that calls the sp_update_job procedure.  To make sure this works, I went ahead and disabled the jobs again through this method so that when I re-enable the jobs again through this stored procedure the issues will be resolved.  Here is the T-SQL script that creates the statements to first disable the jobs:

select ‘exec msdb.dbo.sp_update_job @job_name = ”’ + name + ”’, @enabled = 0′
from msdb.dbo.sysjobs
order by name

Here is the T-SQL script that creates the statements to enable the jobs:
select ‘exec msdb.dbo.sp_update_job @job_name = ”’ + name + ”’, @enabled = 1′
from msdb.dbo.sysjobs
order by name

Take the result set from the query above… copy, paste and execute the T-SQL statements that were generated and your jobs will be enabled and their schedules will be reset.

You are now back in business!… I hope this helps… if you have any suggestions or comments please leave them here!

CrazedDBA

Error 952 Database is in transition

Hello and welcome back...

So today I came across an issue with one of my SQL Server 2005 databases.  I started receiving automated emails stating that one of the reporting databases was set to 'Offline'.  I logged into the server and opened up SSMS and the database appeared to be online however, when I tried to open the database I received the following error message:
      Database 'SalesHistory' is in transition. Try the statement later.
      Error: 952, Severity: 16, State: 1

I attempted to execute the ALTER DATABASE statement to bring the database online however, I received an error stating the database was not available.  So I did some research and there is a bug in SQL Server 2005 SSMS that puts an exclusive lock on the database from the last spid connecting to the database.  This bug prevents any other connections from accessing the database and makes it appear to SQL Server the database is offline.  From the research I did, the solution was to find the host name that had the SSMS connection open and close it.

To do this I ran a sp_who2 active and noticed spid 72 with the command 'ALTER DATABASE' and I executed dbcc inputbuffer(72) to view the full statement
       Alter Database SalesHistory Set Offline
       With Rollback Immediate

With this information I was able to track down the client computer and I had the user close their SSMS and the SalesHistory database was now available.

I hope this helps!...

CrazedDBA