实用的sql server日常检查脚本

实用的sql server日常检查脚本2014-03-14sqlserver 日常检查脚本

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（50）,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 will be used if the instance is 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（100）, serverproperty（"InstanceName"））,"MSSQLSERVER"）;
If @instance_name！="MSSQLSERVER"
Set @instance_name=@instance_name

Set @instance_name1= coalesce（convert（nvarchar（100）, serverproperty（"InstanceName"））,"MSSQLSERVER"）;
If @instance_name1！="MSSQLSERVER"
Set @instance_name1="MSSQL$"+@instance_name1
EXEC master.dbo.xp_regread N"HKEY_LOCAL_MACHINE", N"SoftwareMicrosoftMicrosoft SQL ServerInstance NamesSQL", @instance_name, @system_instance_name output;

Set @key=N"SYSTEMCurrentControlSetServices" +@instance_name1;
SET @registry_key = N"SoftwareMicrosoftMicrosoft SQL Server" + @system_instance_name + "MSSQLServerParameters";
If @registry_key is NULL
set @instance_name=coalesce（convert（nvarchar（100）, serverproperty（"InstanceName"））,"MSSQLSERVER"）;
EXEC master.dbo.xp_regread N"HKEY_LOCAL_MACHINE", N"SoftwareMicrosoftMicrosoft SQL ServerInstance NamesSQL", @instance_name, @system_instance_name output;

SET @registry_key = N"SoftwareMicrosoftMicrosoft SQL Server" + @system_instance_name + "MSSQLServerParameters";
SET @registry_key1 = N"SoftwareMicrosoftMicrosoft SQL Server" + @system_instance_name + "MSSQLServersupersocketnetlibTCPIP1";
SET @registry_key2 = N"SYSTEMControlSet001ServicesTcpipParameters";

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 on cluster .else this value will 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