A practical SQL Server daily check script

SQL Server Daily Check script

print '----------------------------'

print ' 0.sqlserver All information '
print '----------------------------'
print '
print ' ********************************* '

--step 1:setting NULLs and quoted identifiers to on and checking the version of SQL Server
Go
SET ANSI_NULLS on
Go
SET QUOTED_IDENTIFIER ON
Go

IF EXISTS (SELECT * from dbo.sysobjects WHERE id = object_id (n ' prodver ') and OBJECTPROPERTY (ID, n ' isusertable ') = 1)
drop table ProdVer
CREATE table ProdVer ([index] int, Name nvarchar (m), Internal_value int, charcater_value nvarchar (50))
INSERT INTO ProdVer exec xp_msver ' productversion '
if (select substring (charcater_value,1,1) from ProdVer)!=8
Begin

---Step 2:this code would be used if the instance was not SQL Server 2000

Declare @image_path nvarchar (100)
Declare @startup_type int
Declare @startuptype nvarchar (100)
Declare @start_username nvarchar (100)
Declare @instance_name nvarchar (100)
Declare @system_instance_name nvarchar (100)
Declare @log_directory nvarchar (100)
Declare @key nvarchar (1000)
Declare @registry_key nvarchar (100)
Declare @registry_key1 nvarchar (300)
Declare @registry_key2 nvarchar (300)
Declare @IpAddress nvarchar (20)
Declare @domain nvarchar (50)
Declare @cluster int
Declare @instance_name1 nvarchar (100)
--Step 3:reading registry keys for Ip,binaries,startup type, Startup username, errorlogs location and domain.
SET @instance_name = COALESCE (convert (nvarchar), serverproperty (' InstanceName '), ' MSSQLServer ');
If @instance_name!= ' MSSQLServer '
Set @instance_name = @instance_name

Set @instance_name1 = COALESCE (convert (nvarchar), serverproperty (' InstanceName '), ' MSSQLServer ');
If @instance_name1!= ' MSSQLServer '
Set @instance_name1 = ' mssql$ ' + @instance_name1
EXEC master.dbo.xp_regread n ' HKEY_LOCAL_MACHINE ', n ' software\microsoft\microsoft SQL server\instance names\sql ', @ instance_name, @system_instance_name output;

Set @key =n ' system\currentcontrolset\services\ ' + @instance_name1;
SET @registry_key = N ' software\microsoft\microsoft SQL server\ ' + @system_instance_name + ' \mssqlserver\parameters ';
If @registry_key is NULL
Set @instance_name =coalesce (convert (nvarchar), serverproperty (' InstanceName '), ' MSSQLServer ');
EXEC master.dbo.xp_regread n ' HKEY_LOCAL_MACHINE ', n ' software\microsoft\microsoft SQL server\instance names\sql ', @ instance_name, @system_instance_name output;

SET @registry_key = N ' software\microsoft\microsoft SQL server\ ' + @system_instance_name + ' \mssqlserver\parameters ';
SET @registry_key1 = N ' software\microsoft\microsoft SQL server\ ' + @system_instance_name + ' \mssqlserver\ Supersocketnetlib\tcp\ip1 ';
SET @registry_key2 = N ' system\controlset001\services\tcpip\parameters\ ';

EXEC master.dbo.xp_regread ' HKEY_LOCAL_MACHINE ', @key, @value_name = ' ImagePath ', @value = @image_path OUTPUT
EXEC master.dbo.xp_regread ' HKEY_LOCAL_MACHINE ', @key, @value_name = ' Start ', @value = @startup_type OUTPUT
EXEC master.dbo.xp_regread ' HKEY_LOCAL_MACHINE ', @key, @value_name = ' objectname ', @value = @start_username OUTPUT
EXEC master.dbo.xp_regread ' HKEY_LOCAL_MACHINE ', @registry_key, @value_name = ' SQLArg1 ', @value = @log_directory OUTPUT
EXEC master.dbo.xp_regread ' HKEY_LOCAL_MACHINE ', @registry_key1, @value_name = ' IPAddress ', @value = @IpAddress OUTPUT
EXEC master.dbo.xp_regread ' HKEY_LOCAL_MACHINE ', @registry_key2, @value_name = ' Domain ', @value = @domain OUTPUT

Set @startuptype = (select ' Start up Mode ' =
Case
When @startup_type =2 then ' AUTOMATIC '
When @startup_type =3 then ' MANUAL '
When @startup_type =4 then ' Disabled '
End)

--step 4:getting The cluster node names if the server is in cluster. Else this value would be NULL.

declare @Out nvarchar (400)
SELECT @Out = COALESCE (@Out + ', ') + nodename
From Sys.dm_os_cluster_nodes

--Step 5:printing Server Details

SELECT
@domain as ' domain ',
Serverproperty (' Computernamephysicalnetbios ') as ' machinename ',
Cpu_count as ' Cpucount ',
(physical_memory_in_bytes/1048576) as ' physicalmemorymb ',
@Ipaddress as ' ip_address ',
@instance_name1 as ' instancename ',
@image_path as ' Binariespath ',
@log_directory as ' errorlogslocation ',
@start_username as ' Startupuser ',
@Startuptype as ' Startuptype ',
Serverproperty (' ProductLevel ') as ' servicepack ',
Serverproperty (' edition ') as ' edition ',
Serverproperty (' productversion ') as ' Version ',
SERVERPROPERTY (' collation ') as ' collation ',
Serverproperty (' isclustered ') as ' isclustered ',
@out as ' clusternodes ',
Serverproperty (' isfulltextinstalled ') as ' Isfulltext '
From Sys.dm_os_sys_info