SQL Sever SERVERPROPERTY – The Unused Power

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.

SQL Server SERVERPROPERTY

/* 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:

  1. Output results to a flat file OR
  2. 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 |

Follow me on Twitter

Avatar

About Arsalan Khan

People know me as 'Khan' ... nope not Shah Rukh's movie but Star Trek II - Wrath of Khan and since than 'Khan' it has been. Born and raised in Dubai before moving to US for my masters. Been blessed with solid 8+ yrs with SQL (and counting) and currently working for the biggest publishing house as Sr. Database Administrator. I have had an opportunity to speak about tips & tricks to write efficient tsql for Quest International Users Group (PeopleSoft). Love playing TT, badminton and thoroughly enjoy watching cricket when not occupied with my loving daughter. Expertise in DR, Performance Tuning, Troubleshooting and Problem Solving. With that being said I have finally decided to roll my sleeves up and give back to the community bit by bit. Finally.. as my wonderful wife puts it ... "if you don't have a smile, I will give you one of mine ~ Rabia Khan" ~Cheers

View all posts by Arsalan Khan →

Leave a Reply

Your email address will not be published. Required fields are marked *