Over the years I have seen admins struggle when asked to provide SQL instance level information. They right click here and there and YES I was one of them till I was introduced to SQL Server SERVERPROPERTY. This TSQL is either unused or underused and I intend change that by unleashing a fraction of its power.
Here are is a small list of reasons I have compiled over the years:
- New to the environment
- Do a quick inventory check
- Before applying SPs or CU
- Reports asked by your manager on the fly
BOL online indicates “Returns property information about the server instance“ The list of arguments grow as new versions of SQL server are introduced.
Here are the top “bang for the buck” ServerProperty arguments that will provide the information you need on your fingertips.
/* This is tested against SQL 2005 / 2008 / R2 / 2012 */
SELECT
SERVERPROPERTY('MachineName') AS [MachineName],
SERVERPROPERTY('ServerName') AS [ServerName],
SERVERPROPERTY('InstanceName') AS [Instance],
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [PhysicalNode],
SERVERPROPERTY('Edition') AS [Edition],
SERVERPROPERTY('ProductLevel') AS [ProductLevel],
SERVERPROPERTY('ProductVersion') AS [ProductVersion],
SERVERPROPERTY('ProcessID') AS [ProcessID],
SERVERPROPERTY('IsClustered') AS [IsClustered],
SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled],
SERVERPROPERTY('Collation') AS [Collation]
You can go a step further and setup a job that would do either of the following:
- Output results to a flat file OR
- Insert results into a table
These can be used to store for historic purposes and an additional job can be created to purge n days / months worth of data.
If you have used additional effectively share it with the community!
~ Adios
Khan
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
