Friday, December 4, 2015

Check Transaction Log space used in all databases

Super fast way to check the transaction log usage for all of your databases.  Pretty short and sweet, but it gives you a fast look at how much of your transaction logs are being used, per database. 

    /* quick check on log file usage */
       instance_name [Database],
       [LOG File(s) Size (KB)] [LogFileSizeKB],
       [Log File(s) Used Size (KB)] [LogFileSpaceUsedKB],
       [Percent Log Used] [%LogInUse]
       SELECT FROM sys.dm_os_performance_counters
       WHERE counter_name IN
         ('Log File(s) Size (KB)','Log File(s) Used Size (KB)','Percent Log Used')
       AND instance_name != '_Total'
     ) source pivot (
          FOR counter_name IN   
          ([LOG File(s) Size (KB)],[Log File(s) Used Size (KB)],[Percent Log Used])
     ) p2

This is the output from one of my instances:

Pretty cool.  You can add data file sizes in there, too, or you can capture several other statistics like Lock Waits/sec, Lock Requests/sec, the number of active transactions.  Two of my favorites are the Log Growths and Log Shrinks. Monitoring and being aware of your log growths/shrinks is huge!  Run this, you will see those counters and more:

     SELECT object_name, counter_name, instance_name, cntr_value, cntr_type
     FROM sys.dm_os_performance_counters;

Definitely take a look at this piece from MSDN regarding the other available counters:

This is more information about using sys.dm_os_performance_counters to monitor system activity on a whole:

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:  

Saturday, October 31, 2015

SQL Server Error Msg: Divide by zero error encountered.

I was reviewing my posts today and recognized that I don't have near enough SQL Server error messages w/resolutions up here.  My bad.  I am going to begin building a reference for some of the more common (and more frustrating) errors that I'm sure you've all seen.  

First up is the Divide by Zero error.  This error is caused by performing a division where the denominator is a zero.  Here's a very quick example:

    -- sample data in @numbers 
    DECLARE @numbers TABLE (Numerator INT, Denominator INT)
    INSERT @numbers (Numerator,Denominator)
    VALUES (100,10),
             (22,0);  -- note the 0 denominator

    -- perform your division
         Numerator / Denominator [Result]

You'll receive this error:

    (4 row(s) affected)
    Msg 8134, Level 16, State 1, Line 8
    Divide by zero error encountered.

We can workaround the problem using this CASE statement.

    -- workaround CASE
        CASE WHEN Denominator = 0 THEN 0 ELSE Numerator / Denominator END [Result]

No more error.  This is your result, where that 0 is now returned without the error:


We can also use NULLIF for the workaround, rather than the CASE statement, like this:

    -- workaround NULLIF
         Numerator / NULLIF(Denominator,0) [Result]

Slightly different return:


NULLIF returns a NULL value if the two specified expressions are equal. More details here:

I'll be back with more SQL Server Error messages soon.  Until then, feel free to post any errors you're struggling with, and I'll help you out.

Wednesday, August 19, 2015

What port is SQL Server listening on?

I was going through some of my older posts today, and I found a response to one that I had never seen before.  You'll see it at the bottom of this page:

My post is just a quick tip for finding the port that SQL Server is listening on, using xp_regread. As my reader says, xp_regread is undocumented and unsupported... I have used the xp's many times over the years, but the point is valid. They're unsupported and could even be gone in the next build.  If you can work around them, you probably should.

And I have! One of my favorite DMVs is sys.dm_exec_connections. You can use it to monitor your active connections and all kinds of fun stuff, as I've sampled hereIn this particular case, we can use sys.dm_exec_connections to return the port number that SQL Server is listening on. 

       SELECT local_tcp_port
        FROM sys.dm_exec_connections
        WHERE session_id = @@SPID;

Your output will resemble this:

Super easy, documented... and no longer unsupported.  :-) 

Take a look at both of these for more details regarding the DMVs: