Wednesday, February 17, 2016

"The syspolicy_purge_history SQL Server Agent job may fail in SQL Server 2008"

... and in SQL Server 2012. Doubt I've found a v2012 bug, but I've definitely found the same failure there. My customer's syspolicy_purge_history job failed last night with this error:

A job step received an error at line 1 in a PowerShell script. The corresponding line is '(Get-Item SQLSERVER:\SQLPolicy\CLUSNOD3\VISION).EraseSystemHealthPhantomRecords()'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Failed to connect to server .
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)


I'll bet the server wasn't found because 'CLUSNOD3\VISION' does not exist. This occurred on SQLPROD\VISION, where 'CLUSNOD3' is the name of one of the cluster nodes. See Step #6 in the 1st method of correction suggested by MSFT in this Kb:     https://support.microsoft.com/en-us/kb/955726

Easy fix. Just "replace the computer node name by using the virtual server name for the cluster instance".  Ok.  But how do you know for sure what the virtual server name is? Run this on the instance where the failure occurred:

     SELECT SERVERPROPERTY('MachineName')     

The 'MachineName' property returns the computer name on which the SQL Server instance is running. If it is a clustered instance, it returns the name of the virtual server.  My result is 'SQLPROD'. 

Exactly as outlined in the 955726 Kb, I edited step #3 of the sysolicy_purge_history job, replacing 'CLUSNODE3' with 'SQLPROD', like this:

   (Get-Item SQLSERVER:\SQLPolicy\SQLPROD\VISION).EraseSystemHealthPhantomRecords()

The job now completes without error.

Not sure how the incorrect servername got in there, but that's another issue entirely... The syspolicy_purge_history Agent job was introduced with v2008 with Policy Based Management.  Take a look at these for details regarding syspolicy_purge_history, and Policy Based Management in general:

   https://msdn.microsoft.com/en-us/library/dd795279.aspx
    https://msdn.microsoft.com/en-us/library/bb510667(v=sql.105).aspx





Wednesday, January 20, 2016

Determine space used by each SQL Server table

A customer asked me today, 'how much space are all my tables taking?'.  Great question! It is very helpful to know the size of your tables;  not just the rowcount, but the physical space each table is using.  This is a quick query that you can use for just that.  I am JOINing sys.tables, sys.indexes and a couple more catalog views, and returning back the record count and the size of the data and index pages for each of your tables.

WITH spaceUsed
AS (
       SELECT
        o.object_id,
        s.name [SchemaName],
        o.name [TableName],
        CASE MAX(i.index_id) WHEN 1 THEN 'Clustered' ELSE 'Heap' END [IndexType],
        SUM(p.rows) [RecordCount],
        SUM(a.total_pages) [DataPages]
    FROM
        sys.tables o INNER JOIN sys.indexes i
          ON i.object_id = o.object_id INNER JOIN sys.partitions p
            ON p.object_id = o.object_id
            AND p.index_id = i.index_id INNER JOIN sys.allocation_units a
              ON a.container_id = p.partition_id INNER JOIN sys.schemas s
               ON s.schema_id = o.schema_id
    WHERE
        o.type = 'U'
        AND i.index_id IN(0,1)
    GROUP BY
        s.name,
        o.name,
        o.object_id
    ),
IndexPages 
AS (
      SELECT
         o.object_id,
         o.name [TableName],
         SUM(a.total_pages) [IndexPages]
      FROM
         sys.objects o INNER JOIN sys.indexes i
           ON i.object_id = o.object_id INNER JOIN sys.partitions p
              ON p.object_id = o.object_id
AND p.index_id = i.index_id INNER JOIN sys.allocation_units a
                ON a.container_id = p.partition_id
      WHERE
         i.index_id <> 0
      GROUP BY
         o.name,
         o.object_id
   )

     -- return details
     SELECT
         s.SchemaName,
         s.TableName,
         s.IndexType,
         s.RecordCount,
         s.DataPages,
         (s.DataPages * 8) [DataPageSizeKB],
         ISNULL(i.IndexPages, 0) IndexPages,
         (ISNULL(i.IndexPages, 0) * 8) [IndexPageSizeKB]
     FROM
         spaceUsed s LEFT JOIN IndexPages i
           ON s.object_id = i.object_id
     ORDER BY
         s.SchemaName,
         s.TableName

This is the output from AdventureWorks2012 on my own instance:












Nothing too complex. There's probably several other details you could include as well. Take a look at this reference to the each of the catalog views I used, as well as many more:

Wednesday, January 6, 2016

Can I get the index creation date from SQL Server?

um.... No.  Well, not for ALL of them, anyway.  The sys.indexes catalog view does not include a creation date for the indexes, and sys.objects only stores data for indexes associated with primary key and unique constraints.  So... we do not have a create date for indexes that are not associated with primary or unique constraints.

Take a look at the two queries below;  the 1st one joins sys.objects and sys.indexes, but the date returned is of the table creation, not the index. It may be close, but it's not guaranteed to be the date the index was created.  The 2nd query, however, just takes a look into sys.objects for the create date (crdate) of the index that SQL Server creates for us automatically when any PRIMARY KEY or UNIQUE constraint is created. This one is definitely IS the create date for the indexes associated with each constraint.

-- #1. Not the index create date
USE AdventureWorks2012;
SELECT
    i.name [IndexName],
    o.name [TableName],
    o.create_date [Created] -- this is the table, not the index
FROM
    sys.indexes i INNER JOIN sys.objects o
      ON i.object_id = o.object_id
WHERE
    o.name IN ('BusinessEntityContact','UnitMeasure')

Results:



     






-- #2. crdate for primary/unique constraint indexes
SELECT
    name [IndexName],
    crdate [Created]
FROM
    sys.sysobjects
WHERE
    xtype IN('pk','uq') -- all you need to find pk/uq constraints
    AND name IN
('PK_BusinessEntityContact_BusinessEntityID_PersonID_ContactTypeID','PK_UnitMeasure_UnitMeasureCode')

Result:
    







The AND portion of the WHERE clause in query #2 is only there because I wanted to give you back the same constraints returned in query #1.  If you take a look at the create dates for both, you will see they are definitely different than the date the tables were created.

So... can we get the created date for ALL indexes?  Sure, but not with out-of-the-box SQL Server. You could use a DDL event trigger to audit object creation/modifications. Possibly the topic of a later post....

Until then, please take a look at this reference to sys.indexes.  

    https://msdn.microsoft.com/en-IN/library/ms173760.aspx

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 */
    SELECT
       instance_name [Database],
       [LOG File(s) Size (KB)] [LogFileSizeKB],
       [Log File(s) Used Size (KB)] [LogFileSpaceUsedKB],
       [Percent Log Used] [%LogInUse]
    FROM
    (
       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 (
          MAX(cntr_value)
          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:     https://msdn.microsoft.com/en-us/library/ms187743(v=sql.110).aspx

This is more information about using sys.dm_os_performance_counters to monitor system activity on a whole:   https://technet.microsoft.com/en-us/library/ms190382(v=sql.110).aspx

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
    (
    SELECT
       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)
    FROM
       master.sys.databases sd INNER JOIN msdb.dbo.[restorehistory] rh
         ON sd.[name] = rh.[destination_database_name]    )

    -- bring back the details
    SELECT
       [Database],
       RestoreDate,
       RestoredTo,
       RestoredBy
    FROM
       LastRestore
    WHERE 
       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;
DECLARE
     @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 sj.name FROM msdb..sysjobs sj
     WHERE sj.name 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)
     BEGIN
       IF (@@FETCH_STATUS <> -2)
       BEGIN
        SELECT @sql='EXEC dbo.sp_update_job @job_name = N'''+@job+''',@enabled = 0;'
        EXECUTE sp_executesql @sql
        SELECT @success = @success + 1
       END

       NextRecord:
       FETCH NEXT FROM DisableJobCursor
       INTO @Job
     END

     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:

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

       DECLARE DisableJobCursor CURSOR FOR
       SELECT sj.name FROM msdb..sysjobs sj INNER JOIN msdb..syscategories sc
         ON sj.category_id = sc.category_id
       WHERE sc.name = @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 sj.name 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 
     0xC0014062."

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 */
     SELECT
            mp.name [MaintenancePlan],
            mp.[description] [Description],
            mp.[owner] [Owner],
            sp.subplan_name [Subplan],
            sp.subplan_description [SubplanDescription],
            sj.name [Job],
            sj.[description] [JobDescription],
            sj.[enabled] [IsEnabled]
     FROM
            msdb..sysmaintplan_plans mp INNER JOIN msdb..sysmaintplan_subplans sp
              ON mp.id = 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 */ 
     SELECT a.name
     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 b.name <> '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)
     DECLARE JobCursor CURSOR
     FOR
     SELECT a.name
     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 b.name <> 'sa'

     OPEN JobCursor
     FETCH NEXT FROM JobCursor INTO @jobname
     WHILE (@@FETCH_STATUS <> -1)
     BEGIN
          EXEC msdb..sp_update_job @job_name = @jobname, @owner_login_name = 'sa'
          FETCH NEXT FROM JobCursor INTO @jobname
     END
     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.