Ramblings Of A Crazed DBA

How To Disable Multiple SQL Server Agent Jobs!

2012-10-18T21:25:00.003-07:00

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

2012-10-10T21:49:00.000-07:00

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

Locked Out Of SQL Server

2012-06-30T21:06:00.001-07:00

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

Use DTExec To Replace DTSRun In SSIS!....

2010-09-15T16:09:00.000-07:00

Hello there!...

Today I was tasked with migrating a SQL Server Agent job from SQL Server 2000 to SQL Server 2008.

This job calls a VB script file and within this file executes the dtsrun.exe command and calls a DTS package. So as I migrated the job to the new server I upgraded the DTS package to SSIS and copied over the VB script file. So within the VB script file I had to modify the command to go from using DTSRun to DTExec with the new parameters. So here is what dtsrun command looked like in SQL Server 2000:
        '*DTS executable and Parameters
        DTSAPPRUN = """C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtsrun.exe"" -S server
               name - E -N DTS package name

Here is what I had to change command to look like for a SSIS package:
        '*SSIS executable and Parameters
        DTSAPPRUN = """C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe"" /sq SSIS
               package name /ser server name"

Now fortunately for me, the DTS package was a simple data import package so it was converted to SSIS without issue. So, after making the changes to the VB script file and saving... I reran the SQL Server Agent job and the VB Script executed and the SSIS package ran without issue. I hope you find this helpful and if you have another way of doing this please post here and let us know!

Until next time...
Crazed DBA

Database Mail Formatting, Invalid Parameter Error!

2010-09-13T15:08:00.000-07:00

Today I wanted to cover a database mail configuration issue I came across while attempting to send an email with an attachment through a SQL Server Agent job.

I have a t-sql script that calls msdb.dbo.sp_send_dbmail stored procedure in a job step that send the results of query as an attachment. When the job runs it fails with the following error message:

Executed as user: Domain\SQLAccount. Error formatting query,
probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.

When I copied the t-sql script from the job step and ran it in a query window the query ran and the email sent with the attachment without issue. So I went to BOL, SQL Server Newsgroups and Google and through a combination of posts from different sources I found the solution to my problem. The issue was the domain account that the SQL Server Agent service is running under did not belong as a user in the msdb database. For some quirky reason, for sp_send_dbmail the login that executes this must be in the msdb database. If you come across this problem give this solution a try and let us know!...

Crazed DBA

Access Blocked To xp_cmdshell

2010-09-10T12:27:00.000-07:00

Hello and welcome back!...

Today I was attempting to execute a stored procedure in SQL Server 2008 R2 that calls xp_cmdshell to get a directory listing and I received the following error message:

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell'

Now there are 2 ways to enable xp_cmdshell in SQL Server 2008 R2 they are:
1. In a query window execute the following t-sql:
exec sp_configure 'xp_cmdshell', '1'
reconfigure with override
go
2. The other way is through SQL Server Management Studio (SSMS). Here are the
steps to enable xp_cmdshell through SSMS:

Step 1 - In Management Studio connect to a component of SQL Server
Step 2 - In Object Explorer, right-click the server, and then click Facets
Step 3 - In the View Facets dialog box, expand the Facet list, and select
Surface Area Configuration
Step 4 - The last in the list is XPCmdShellEnabled and you want to select this to read 'Yes'
and then click OK. Here is a screenshot of this Facet:

Quick Way To Find File Sizes For Your Database!

2010-09-08T17:58:00.000-07:00

Hello and welcome to my blog!...

Here you will find what I like to call "nuggets" that I come across while working with SQL Server and other tools!...

Today I am going to share with you a quick way in T-SQL to determine the file sizes in your database. The script below will work in SQL Server 2000, 2005, 2008 and 2008 R2. You will need to run this in the context of the database you want the file sizes for.

SELECT FILEID,
CONVERT(decimal(12,2),ROUND([size]/128.000,2)) as [FILESIZEINMB] ,
CONVERT(decimal(12,2),ROUND(fileproperty([name],'SpaceUsed')/128.000,2))
as [SPACEUSEDINMB],
CONVERT(decimal(12,2),ROUND(([size]-fileproperty([name], 'SpaceUsed' )) /
128.000,2)) as [FREESPACEINMB],
[name] as [DATABASENAME],
[filename] as [FILENAME]
FROM dbo.sysfiles

Let us know if you know of another way to get file sizes fast!