Thursday, October 18, 2012

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
reconfigure with override
update msdb.dbo.sysjobs
set [enabled] = 0
where [name] = ‘Full Database Backups’
exec sp_configure ‘allow updates’,0
reconfigure with override
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!

Wednesday, October 10, 2012

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!...


Saturday, June 30, 2012

Locked Out Of SQL Server

Has there been a time when you needed to get into a SQL Server to perform a sysadmin function?...

This same situation happened to me the other day.  For whatever reason the DBA team got locked out of a SQL Server in the production environment.  The DBAs could RDP to the server as our domain group belonged to the local Administrators group but we could not Windows Authenticate into SQL Server.  Of course here you would say..."just sign in using the SA login".  We were saying the same thing... however, none of us knew the password as this SQL Server was legacy.  So what to do... after researching this is what we came up with...

I went ahead and put the SQL Server into Single User Mode.  This is how you do this...

Go to your Start menu -> All Programs -> Microsoft SQL Server 200x -> Configuration Tools -> SQL Server Configuration Manager.  Once you are here, on the left side click on SQL Server Services and on the right side select SQL Server (MSSQLServer) and right-click and select Properties and click on the Advanced tab.  Once you are here look down the list and find Startup Parameters.  In the very beginning of the parameters put -m; (enter it just as you see it with the semi colon right after the m (m represents Single User Mode).  After clicking 'Apply' the following message box appears:
Click OK and then stop the SQL Server Agent service and then the SQL Server service.  When you restart SQL Server make sure you do NOT.. I repeat DO NOT start the SQL Server Agent service as this will obtain the Single User connection and you will not be able to connect via SSMS.  Just start the SQL Server service and connect via SSMS.

You are now in SQL Server as a sysadmin.  Connect to your SQL Server through SSMS and expand your server tree.  Select Security and then Logins... here you will be able to make changes to the accounts and grant the correct \ necessary permissions.  

Now that you have made the changes to your security in SQL Server just follow the steps in reverse order to take SQL Server out of Single User Mode (by removing the -m in the Startup Parameters) and restart the SQL Server and SQL Server Agent services.

I hope this helps...

Until next time...

Crazed DBA

  © Blogger template 'A Click Apart' by 2008

Back to TOP