DEFINITIONS

Definitions More Info.
Definition ID775
TitleSQL
CategoryNOTES
DefinitionRestart Warning sp
Definition Description-- Log tablosu oluştur
CREATE TABLE DBA_SQLRestartlog (
ID INT IDENTITY (1,1),
SERVERNAME VARCHAR(100) DEFAULT ('SUPPORTDB-192.168.1.113'),
STARTTIME DATETIME,
IsMailed BIT DEFAULT (0)
)

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_InserT_SQLRestartlog]
AS
INSERT INTO [DPAudit].[dbo].[SQLRestartLog] (StartTime)
VALUES(GETDATE())
-- Mail, Server Is Restarted
DECLARE @IP VARCHAR(100)
, @svrName VARCHAR(255)
, @sql VARCHAR(400)
, @tableHTML NVARCHAR(MAX)
, @subject1 VARCHAR(200)
, @recipients CHAR(100)
, @subject CHAR(250)
, @body CHAR (250)

--by default it will take the current server name, we can then set the server name as well
SET @svrName = @@SERVERNAME

-- Mail title and server name to print in mail content
SELECT @IP= ( SELECT TOP(1) local_net_address
FROM sys.dm_exec_connections
WHERE local_net_address IS NOT NULL )
SELECT @subject1=@IP+'_'+@svrName+' SUPPORTDB!!!'
SELECT @tableHTML = N''+
N'

'+@IP+'_'+@@SERVERNAME+''+
N''+
N''+
N''+
N''+
CAST ( ( SELECT
td = O.[ID], '',
td = O.[ServerName], '',
td = O.[StartTime], '',
td = O.[IsMailed], ''
FROM [DPAudit].[dbo].[DBA_SQLRestartlog] O
FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX)) +
N'
IDServerNameStartTime
'+
N'
'+
N'' +
N'

';
EXEC msdb.dbo.sp_send_dbmail
@recipients='[email protected]'
--@recipients='[email protected]',
,@subject = @subject1
, @body = '**** SQL SERVER RESTARTED !!!!! *****'
, @body_format = 'HTML';

UPDATE [DPAudit].[dbo].[SQLRestartLog]
SET Ismailed = 1
WHERE Ismailed = 0


GO
EXEC sp_procoption N'[master].[dbo].[usp_InserT_SQLRestartlog]', 'startup', '1'
GO
RecordBycunay
Record Date08-02-2017 07:54:54
Düzenle
Kopyala
Sil