DBA Query: Instance Information

As part of my weekly routine I like to take a holistic view of the production servers I am responsible for.  After backups and maintenance plans, the next most important role of the DBA is to keep an eye on the overall health of the production databases.

I have several queries that I use to provide me with a summary view of the databases.

Today’s post will be a query that provides a nice view at the instance level.  Of course there are always more points to pull, but this is what I culled the query down to.  You may want to add more, or delete something.

Rather than running this query for every instance, all you need to do is set up your central management servers (providing you have at least one SQL2008 or later server).  This will allow you to run the query and get the result set for each server.  For more info on central management servers see BOL.

So here’s my query for instance reporting…  Enjoy.

Instance Properties
  1. DECLARE    @xp_msver table(    id                    int
  2.                             , name                sysname
  3.                             , internal_value    int
  4.                             , character_value    nvarchar(4000) );
  6. INSERT INTO @xp_msver                    
  7.     EXEC    master.dbo.xp_msver ProcessorCount;
  8. INSERT INTO @xp_msver                    
  9.     EXEC    master.dbo.xp_msver PhysicalMemory;
  11. SELECT    CONVERT(int, CONVERT(char, current_timestamp, 112)) AS CaptureDate
  12.         , SERVERPROPERTY (‘ServerName’) AS [ServerName]
  13.         , SERVERPROPERTY (‘ComputerNamePhysicalNetBIOS’) AS [ComputerNamePhysicalNetBIOS]
  14.         , SERVERPROPERTY (‘MachineName’) AS [MachineName]
  15.         , SERVERPROPERTY (‘InstanceName’) AS [InstanceName]
  16.         , SERVERPROPERTY (‘Edition’) AS [SQL Server Edition]
  17.         , SERVERPROPERTY (‘ProductVersion’) AS [ProductVersion]
  18.         , SERVERPROPERTY (‘ProductLevel’) AS [ProductLevel]
  19.         , SERVERPROPERTY (‘IsClustered’) AS [IsClustered]
  20.         , SERVERPROPERTY (‘IsIntegratedSecurityOnly’) AS [IsIntegratedSecurityOnly]
  21.         , SERVERPROPERTY (‘IsSingleUser’) AS [IsSingleUser]
  22.         , SERVERPROPERTY (‘LicenseType’) AS [LicenseType]
  23.         , SERVERPROPERTY (‘NumLicenses’) AS [NumLicenses]
  24.         , SERVERPROPERTY (‘ProcessID’) AS [ProcessID]
  25.         , CAST(xp1.ProcessorCount AS int) AS ProcessorCount
  26.         , CAST(xp2.PhysicalMemory AS int) AS PhysicalMemory
  27.         , dt.DefaultTraceEnabled
  28.         –, df.DefaultTracePath
  29.         , rde.RemoteDACEnabled
  30.         , (    SELECT    create_date
  31.             FROM    sys.databases
  32.             WHERE    database_id = 2) AS SQLServerStartDate
  33.         , (    SELECT    DATEDIFF(day, create_date, current_timestamp)
  34.             FROM    sys.databases
  35.             WHERE    database_id = 2) As DaysSinceSQLServerStart
  36. FROM    (SELECT    character_value AS ProcessorCount FROM @xp_msver WHERE name = ‘ProcessorCount’) xp1
  37.         CROSS JOIN
  38.          (SELECT    internal_value AS PhysicalMemory FROM @xp_msver WHERE name = ‘PhysicalMemory’) xp2
  39.         CROSS JOIN
  40.          (SELECT value_in_use AS DefaultTraceEnabled FROM sys.configurations WHERE name = ‘default trace enabled’) dt
  41.         CROSS JOIN
  42.         (SELECT value_in_use AS RemoteDACEnabled FROM sys.configurations WHERE name = ‘remote admin connections’) rde
  43.         –CROSS JOIN
  44.         –(SELECT NULL AS DefaultTracePath) df
  45.         –(SELECT VALUE AS DefaultTracePath FROM fn_trace_getinfo(0) WHERE property = 2) df

Stay curious… keep learning.


Leave a Reply

You must be logged in to post a comment.

Using Gravatars in the comments - get your own and be recognized!

XHTML: These are some of the tags you can use: <a href=""> <b> <blockquote> <code> <em> <i> <strike> <strong>