Wednesday, November 25, 2015

Who restored those databases?!

Working an upgrade with a customers right now, from SQL Server v2005 to v2012. The bulk of the work is done, but I am still rebuilding an ETL project for them. The new VS solution is in place, I've upgraded 50+ packages successfully, and now I just need to build the Agent job that will kick off the ETL each day. Everything is progressing very well, but... today I was surprised!  

I get into SSMS, SQL Server Agent\Jobs, and find a TON of jobs there that I know nothing about! The only thing I can tell for sure is that they are SSRS subscriptions. You know, those crazy job names like this:

That tells me ReportServer, but remember, this is a new box.  Not production-ready yet. I didn't even know there was a ReportServer database until now. Hence, my surprise. This post is just a quick collect on the last database restore details from msdb..restorehistory, if exists.

    /* get the most recent restore details from your databases */
    WITH LastRestore AS
       sd.[name] [Database],
       rh.restore_date [RestoreDate],
       rh.destination_database_name [RestoredTo],
       rh.user_name [RestoredBy],
     RowNum = ROW_NUMBER() OVER (PARTITION BY sd.Name ORDER BY rh.[restore_date] DESC)
       master.sys.databases sd INNER JOIN msdb.dbo.[restorehistory] rh
         ON sd.[name] = rh.[destination_database_name]    )

    -- bring back the details
       RowNum = 1
       AND RestoreDate IS NOT NULL

This pic is from my customer's instance..though I've blacked out RestoredBy.  :-)

And this one is from my own instance, just so you can see the full output:

I've actually posted this before here, but it was for another purpose, and I didn't include in the output who actually did the deed. Pretty helpful to know who's doing what.  We all know, it happens.  

See this for more information on msdb..restorehistory:

Tuesday, November 24, 2015

Disable (or enable) all SQL Server Agent jobs

I was doing some heavy lifting for a customer recently... it was after hours, and I needed to disable a few Agent jobs beforehand, to be sure I didn't run into anything. This post is the cursor I used to first disable the jobs, and then re-enable them afterward. Very simple, you can see it's just based on an IN list:

/* Use this to disable multiple SQL Server Agent jobs in one action. */
USE msdb;
     @job VARCHAR(128),
     @success INT,
     @error INT,
     @sql NVARCHAR(255)

     /* declare cursor to disable sysjobs based on the IN list */
     DECLARE DisableJobCursor CURSOR FOR
     SELECT FROM msdb..sysjobs sj
     WHERE NOT IN('AFewJobs','YouMayWant','ToIgnore') -- change as needed
     AND enabled = 1

     SELECT @success = 0
     SELECT @error = 0

     OPEN DisableJobCursor
     FETCH NEXT FROM DisableJobCursor
     INTO @job

     WHILE (@@FETCH_STATUS <> -1)
       IF (@@FETCH_STATUS <> -2)
        SELECT @sql='EXEC dbo.sp_update_job @job_name = N'''+@job+''',@enabled = 0;'
        EXECUTE sp_executesql @sql
        SELECT @success = @success + 1

       FETCH NEXT FROM DisableJobCursor
       INTO @Job

     CLOSE DisableJobCursor
     DEALLOCATE DisableJobCursor

     SELECT 'Disabled Jobs = ' + CONVERT(VARCHAR(10),@success)
     SELECT 'Failures = ' + CONVERT(VARCHAR(10),@error)

If you're working with a lot of jobs, that IN list can become a pain. In that case we could go in more collectively by using the job category. For example, let's say we want to disable all jobs in the 'Database Maintenance' category.  Just use @category in your cursor declaration, instead of the IN list, like this:

       @job VARCHAR(128),
       @jobcategory  VARCHAR(25) = 'Database Maintenance',
       @sql VARCHAR(255)

       DECLARE DisableJobCursor CURSOR FOR
       SELECT FROM msdb..sysjobs sj INNER JOIN msdb..syscategories sc
         ON sj.category_id = sc.category_id
       WHERE = @jobcategory
       AND enabled = 1  

Another option, though a bit reckless, is just to go in for all enabled jobs.  This approach let's you disable/enable en masse:

       DECLARE DisableJobCursor CURSOR FOR
       SELECT FROM msdb..sysjobs sj
       WHERE sj.enabled = 1

You want to be careful with that last approach... who knows how many jobs you have?!

I would be remiss if I don't caution you about cursor usage. In most cases, I am pretty anti-cursor, but... that's not what this post is about.  I will give you an excellent reference for cursor examples, performance considerations, and alternatives. Don't ignore.  This is an excellent read.

Query SQL Server Maintenance Plan details

I recently inherited a couple of servers, and am in the process of assessing them, and cleaning things up.  Today I was viewing job logging, and found this in the output of a failed job:

     Could not load package "\Maintenance Plans\RumbaKillListCSV" because of error 

RumbaKillList?  Maintenance plans?  I didn't know about either of these things!  As I said, I just inherited these servers... it looks like I've got a lot more digging to do.  For starters, I wrote this to quickly list details regarding any maintenance plans that may exist.

     /* quick details regarding any maintenance plans you've inherited */
            mp.[description] [Description],
            mp.[owner] [Owner],
            sp.subplan_name [Subplan],
            sp.subplan_description [SubplanDescription],
            sj.[description] [JobDescription],
            sj.[enabled] [IsEnabled]
            msdb..sysmaintplan_plans mp INNER JOIN msdb..sysmaintplan_subplans sp
              ON = sp.plan_id INNER JOIN msdb..sysjobs sj
                ON sp.job_id = sj.job_id

Not much, just a quick look to see what plans are there.  See that 'IsEnabled' -- this is just something to let you know whether or not the maintenance plan's corresponding Agent job is enabled.

Here's the output for just this plan:

You can expand upon the output, if you'd like.  Take a look at this from MSFT, for more detail  on these and the other Maintenance Plan tables:

Wednesday, November 18, 2015

Update job owner for all SQL Server Agent jobs

I am helping a customer cleanup their SQL Server inventory, and remove a few risks that exist. In this case, they have more than 40 SQL Server Agent jobs that are owned by the domain login of their old DBA, who no longer works at the firm. They can't remove that login from the DC yet because the jobs that it owns will fail to run.

What login should be used as the Agent job owner?  There are many opinions here, but my rule is simple;  do not define SQL Server Agent job ownership with a domain login. What happens if when they remove the credentials, or even if DNS becomes inaccessible, and the login cannot be resolved? 

Use SA, or another SQL login that is created explicitly for Agent job ownership. In this tip I will show you how to identify all non-SA owned Agent jobs, and then update ownership to SA

FIRST, you run this SELECT to get a list of the jobs that you are going to effect. It's just a safety check, and will help you change back, if necessary. 

     /*  output a list of the jobs you'll update */ 
     FROM msdb..sysjobs a JOIN sys.server_principals b
       ON a.owner_sid = b.sid
     WHERE a.enabled = 1  -- just looking for enabled jobs
     AND <> 'sa'

Now we can alter the ownership for each to SA using a cursor, like this:

     /*  update job owner for all SQL Server Agent jobs */
     DECLARE @jobname VARCHAR(1000)
     FROM msdb..sysjobs a JOIN sys.server_principals b
       ON a.owner_sid = b.sid
     WHERE a.enabled = 1  -- just looking for enabled jobs
     AND <> 'sa'

     OPEN JobCursor
     FETCH NEXT FROM JobCursor INTO @jobname
     WHILE (@@FETCH_STATUS <> -1)
          EXEC msdb..sp_update_job @job_name = @jobname, @owner_login_name = 'sa'
          FETCH NEXT FROM JobCursor INTO @jobname
     CLOSE JobCursor
     DEALLOCATE JobCursor

Very simple, but we do have to ask another question. Should we use the SA account for job ownership?  I'll come back with another post tomorrow regarding SQL Server Agent's fixed database roles. Specifically, SQLAgentOperatorRole. We will use it to establish SQL Server Agent job ownership, using a non-SA account.

Back soon.  

Tuesday, November 17, 2015


I decided to expand upon my SERVERPROPERTY script this morning... there are a lot of other properties beyond just the name of the instance.  :-)  I put this one together quickly, should work well if you're looking at configuration or other settings, and you'd like to see all server properties at once. 

     /* Use SERVERPROPERTY to collect details regarding the server instance. */
     DECLARE @serverproperty TABLE (property sysname)
     INSERT INTO @serverproperty(property)

     -- pull them back out
            SERVERPROPERTY(Property) Value

Here's a piece of the output from one of my own instances:

Take a look at this for more SERVERPROPERTY details from MSFT: