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.
- DECLARE @xp_msver table( id int
- , name sysname
- , internal_value int
- , character_value nvarchar(4000) );
- INSERT INTO @xp_msver
- EXEC master.dbo.xp_msver ProcessorCount;
- INSERT INTO @xp_msver
- EXEC master.dbo.xp_msver PhysicalMemory;
- SELECT CONVERT(int, CONVERT(char, current_timestamp, 112)) AS CaptureDate
- , SERVERPROPERTY (‘ServerName’) AS [ServerName]
- , SERVERPROPERTY (‘ComputerNamePhysicalNetBIOS’) AS [ComputerNamePhysicalNetBIOS]
- , SERVERPROPERTY (‘MachineName’) AS [MachineName]
- , SERVERPROPERTY (‘InstanceName’) AS [InstanceName]
- , SERVERPROPERTY (‘Edition’) AS [SQL Server Edition]
- , SERVERPROPERTY (‘ProductVersion’) AS [ProductVersion]
- , SERVERPROPERTY (‘ProductLevel’) AS [ProductLevel]
- , SERVERPROPERTY (‘IsClustered’) AS [IsClustered]
- , SERVERPROPERTY (‘IsIntegratedSecurityOnly’) AS [IsIntegratedSecurityOnly]
- , SERVERPROPERTY (‘IsSingleUser’) AS [IsSingleUser]
- , SERVERPROPERTY (‘LicenseType’) AS [LicenseType]
- , SERVERPROPERTY (‘NumLicenses’) AS [NumLicenses]
- , SERVERPROPERTY (‘ProcessID’) AS [ProcessID]
- , CAST(xp1.ProcessorCount AS int) AS ProcessorCount
- , CAST(xp2.PhysicalMemory AS int) AS PhysicalMemory
- , dt.DefaultTraceEnabled
- –, df.DefaultTracePath
- , rde.RemoteDACEnabled
- , ( SELECT create_date
- FROM sys.databases
- WHERE database_id = 2) AS SQLServerStartDate
- , ( SELECT DATEDIFF(day, create_date, current_timestamp)
- FROM sys.databases
- WHERE database_id = 2) As DaysSinceSQLServerStart
- FROM (SELECT character_value AS ProcessorCount FROM @xp_msver WHERE name = ‘ProcessorCount’) xp1
- CROSS JOIN
- (SELECT internal_value AS PhysicalMemory FROM @xp_msver WHERE name = ‘PhysicalMemory’) xp2
- CROSS JOIN
- (SELECT value_in_use AS DefaultTraceEnabled FROM sys.configurations WHERE name = ‘default trace enabled’) dt
- CROSS JOIN
- (SELECT value_in_use AS RemoteDACEnabled FROM sys.configurations WHERE name = ‘remote admin connections’) rde
- –CROSS JOIN
- –(SELECT NULL AS DefaultTracePath) df
- –(SELECT VALUE AS DefaultTracePath FROM fn_trace_getinfo(0) WHERE property = 2) df
Stay curious… keep learning.
Leave a Reply
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>