Search This Blog

Get Current SQL Server Details:

Get SQL Server Version
SQL Server Start Time


>SELECT SERVERPROPERTY('ServerName') AS [SQLServer],
@@microsoftversion/0x01000000 AS [MajorVersion],
SERVERPROPERTY('ProductVersion') AS [VersionBuild],
SERVERPROPERTY('ProductLevel') AS [Product],
SERVERPROPERTY ('Edition') AS [Edition],
SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsWindowsAuthOnly],
SERVERPROPERTY('IsClustered') AS [IsClustered],
[cpu_count] AS [CPUs],
[physical_memory_in_bytes]/1048576 AS [RAM (MB)],
cpu_count AS [Logical CPUs],
physical_memory_in_bytes / 1048576 AS 'mem_MB'
,virtual_memory_in_bytes / 1048576 AS 'virtual_mem_MB'
,max_workers_count
,os_error_mode
,os_priority_class
,(case hyperthread_ratio
when 1 then
'1' 
else
(cpu_count / hyperthread_ratio) 
end)AS [Physical CPUs]
FROM sys.dm_os_sys_info OPTION (RECOMPILE);

OUTPUT:
SQLServer MajorVersion VersionBuild Product Edition IsWindowsAuthOnly IsClustered CPUs RAM (MB) Logical CPUs mem_MB virtual_mem_MB max_workers_count os_error_mode os_priority_class Physical CPUs
AU2K8DC5SD 10 10.50.2500.0 SP1 Standard Edition (64-bit) 0 0 2 8191 2 8191 8388607 512 5 32 1

Method 1 – “SELECT @@VERSION”

Simply open up a new query and run the following:
1SELECT @@VERSION
The output will look something like:
Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
 Feb 10 2012 19:39:15 
 Copyright (c) Microsoft Corporation
 Express Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1)


Method 2 – “xp_msver”

I like this one as it provides quite a lot of useful information about your SQL Server installation and server configuration
1EXEC xp_msver
Sample output from my SQL Server 2012 install…
determine sql server version number

Method 3 – “SELECT SERVERPROPERTY”

You run SELECT SERVERPROPERTY against your server and pass in some parameters to get back the information you want.
1SELECT
2SERVERPROPERTY('productversion') AS ProductVersion
3, SERVERPROPERTY ('productlevel') AS ProductLevel
4, SERVERPROPERTY ('edition') AS ProductEdition
Which produces the following output…
ProductVersion       ProductLevel         ProductEdition
-------------------- -------------------- ------------------------------
11.0.2100.60         RTM                  Express Edition (64-bit)

(1 row(s) affected)

Method 4 – Use Management Studio (Client Tools)

You can access the Help menu in Management Studio and goto “About” to ascertain which SQL Server versions of the client tools you have installed on your workstation or SQL Server.


GET OS Information:

EXEC xp_cmdshell 'SET PROCESSOR'
or
exec master..xp_cmdshell 'systeminfo'
or
select right(@@version, 44)



SQL Server Uptime from SSMS: SQL Server Start Time


Today there was a problem on one of my production server, where I was investigating and identified nothing which can let me know the problem. Everything was fine but the server is running extremely slowly. When I was sure that this is not a database or application problem I asked help from my server expert guys where they have identified that the server is not restarted since October 2012 and this is the problem. They asked me to get the approval to restart the server. I got that and the server restarted.

After restart it was working fine. I asked the server expert what happened after restart then he told me that after every 90 days the servers must be restarted to get the optimum performance from the server.

So I decided to create a script which will let me know that from how many days the server is up and running.


DECLARE @p_dateTime AS DATETIME;
DECLARE @p_SQL_Query NVARCHAR(100);
DECLARE @Table TABLE (AllData NVARCHAR(MAX));
DECLARE @p_ServerName NVARCHAR(50);
SET @p_SQL_Query = 'exec xp_cmdshell "net statistics server"';
SELECT @p_ServerName = CAST(SERVERPROPERTY('machinename') AS VARCHAR);
INSERT INTO @Table
EXEC (@p_SQL_Query);
SELECT @p_dateTime = SUBSTRING(AllData,17,LEN(AllData)) FROM @Table WHERE AllData LIKE 'Statistics since%';
SELECT 'The Server ['+@p_ServerName+'] is up from the date :'+ CAST(@p_dateTime AS VARCHAR)+' which is almost '+CAST(DATEDIFF(day,@p_dateTime,getdate()) AS VARCHAR) +' Days !!'

Output:

The Server [MAHALI RAJESH] is up from the date :Feb 22 2014 10:18 AM which is almost 1 Days !!