sqlserver的日常检查脚本归结

核心提示：SET @registry_key2 = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\';SET @registry_key4 = N'SYSTEM\CurrentControlSet\Control\Session Manager\

SET @registry_key2 = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\';

SET @registry_key4 = N'SYSTEM\CurrentControlSet\Control\Session Manager\Environment'

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='SQLPath',@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_key2,@value_name='Domain',@value=@domain OUTPUT

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key4,@value_name='NUMBER_OF_PROCESSORS',@value=@CPU OUTPUT                        

END

IF @instance_name='MSSQLSERVER'

BEGIN

SET @key=N'SYSTEM\CurrentControlSet\Services\' +@instance_name;

SET @registry_key = N'Software\Microsoft\MSSQLSERVER\MSSQLServer\Parameters';

SET @registry_key1 = N'Software\Microsoft\MSSQLSERVER\Setup';

SET @registry_key2 = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\';

SET @registry_key4 = N'SYSTEM\CurrentControlSet\Control\Session Manager\Environment'

                                            　

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='SQLPath',@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

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key4,@value_name='NUMBER_OF_PROCESSORS',@value=@CPU OUTPUT                        

END

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 10 : Using ipconfig and xp_msver to get physical memory and IP

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'tmp') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)