Advertisements
This script are used to collect SpServerDiagnostics results on target server, when AG is not working.
Additional SP:
Sp1
USE [DBAdata]
GO
/****** Object: StoredProcedure [dbo].[usp_SpServerDiagnostics_new] Script Date: 15-04-2017 14:29:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
CREATE TABLE [dbo].[SpServerDiagnostics](
[create_time] [datetime] NULL,
[component_type] [sysname] NOT NULL,
[component_name] [sysname] NOT NULL,
[state] [int] NULL,
[state_desc] [sysname] NOT NULL,
[data] [nvarchar](max) NULL
)
*/
--DROP PROC [dbo].[usp_SpServerDiagnostics]
alter proc [dbo].[usp_SpServerDiagnostics_new]
(@Server_name varchar(50))
--with Encryption
as
begin
EXEC('Exec ['+@Server_name+'].MASTER.DBO.usp_SpServerDiagnostics')
END
GO
Sp2
USE [DBAdata]
GO
/****** Object: StoredProcedure [dbo].[usp_SpServerDiagnostics] Script Date: 15-04-2017 14:29:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
CREATE TABLE [dbo].[SpServerDiagnostics](
[create_time] [datetime] NULL,
[component_type] [sysname] NOT NULL,
[component_name] [sysname] NOT NULL,
[state] [int] NULL,
[state_desc] [sysname] NOT NULL,
[data] [nvarchar](max) NULL
)
*/
--DROP PROC [dbo].[usp_SpServerDiagnostics]
alter proc [dbo].[usp_SpServerDiagnostics]
--with Encryption
as
begin
DECLARE @server_name SYSNAME
DECLARE @DESC SYSNAME
DECLARE @text varchar(8000)
DECLARE @minrow int
DECLARE @maxrow int
--TRUNCATE TABLE SpServerDiagnostics
declare @Diagnostics table (id int primary key identity,
servername varchar(100),Description varchar(100))
insert into @Diagnostics
select Servername , Description from dbadata.dbo.dba_all_servers
-- usp_SpServerDiagnostics Object created only target server
WHERE Description in(
'sss'
)
-- select * from dbadata.dbo.dba_all_servers where ha like 'a%'
SELECT @minrow = MIN(id)FROM @Diagnostics
SELECT @maxrow = MAX(id) FROM @Diagnostics
while (@minrow <=@maxrow)
begin
BEGIN TRY
select @Server_name=Servername ,
@Desc=Description from @Diagnostics where ID = @minrow
EXEC('Exec ['+@Server_name+'].MASTER.DBO.usp_SpServerDiagnostics')
end try
BEGIN CATCH
SELECT @SERVER_NAME,ERROR_NUMBER() AS ErrorNumber,ERROR_MESSAGE() AS ErrorMessage;
insert into tbl_Error_handling
SELECT @SERVER_NAME,'AlwaysON_DIAGNOSTICS',[Error_Line] = ERROR_LINE(),[Error_Number] = ERROR_NUMBER(),
[Error_Severity] = ERROR_SEVERITY(),[Error_State] = ERROR_STATE(),
[Error_Message] = ERROR_MESSAGE(),GETDATE()
END CATCH
set @minrow =@minrow +1
end
END
GO
SP3 On target sevrver
use master
go
CREATE TABLE SpServerDiagnosticsResult
(
create_time DateTime,
component_type sysname,
component_name sysname,
state int,
state_desc sysname,
data nvarchar(max)
);
go
-- select * from SpServerDiagnosticsResult
create proc usp_SpServerDiagnostics
as
begin
--truncate table SpServerDiagnosticsResult
INSERT INTO SpServerDiagnosticsResult
EXEC master.dbo.sp_server_diagnostics
delete from SpServerDiagnosticsResult where create_time > getdate()-10
end
