4步追蹤導致事務日誌膨脹的SQL命令

4步追蹤導致事務日誌膨脹的SQL命令

[@more@]

-- 1. create LogSize and BufferSQL table
create table LogSize
(
ID int identity(1, 1),
DatabaseName varchar(100),
LogSize decimal(9, 2),
LogSpaceUsed decimal(9, 2),
Status tinyint,
Date datetime DEFAULT (getdate())
)

create table BufferSQL
(
ID int,
EventType varchar(50),
Parameters varchar(100),
EventInfo varchar(150),
Date datetime DEFAULT (getdate())
)

-- 2. create sp_GetBufferSQL procedure
alter PROCEDURE sp_GetBufferSQL(@ID int)
AS
BEGIN
declare @spid int,
@intCountProperties int,
@intCounter int

CREATE TABLE #tmp_process ( id int identity(1,1), spid smallint )
IF @@ERROR<>0 RETURN @@ERROR

CREATE TABLE #tmp_SQL
(
EventType varchar(50),
Parameters varchar(100),
EventInfo varchar(150)
)
IF @@ERROR<>0 RETURN @@ERROR

INSERT INTO #tmp_process( spid )
SELECT spid FROM sysprocesses WHERE dbid = DB_ID('DBName')
IF @@ERROR<>0 RETURN @@ERROR

SELECT @intCountProperties = Count(*), @intCounter = 1
from #tmp_process
IF @@ERROR<>0 RETURN @@ERROR

WHILE @intCounter <= @intCountProperties
BEGIN
select @spid = spid
from #tmp_process where id = @intCounter

INSERT INTO #tmp_SQL(EventType, Parameters, EventInfo)
EXECUTE sp_executesql N'DBCC INPUTBUFFER ( @v_spid )', int', @v_spid = @spid

INSERT INTO MemberData.dbo.BufferSQL(ID, EventType, Parameters, EventInfo, Date)
SELECT @ID, EventType, Parameters, EventInfo, getdate()
FROM #tmp_SQL

SET @intCounter = @intCounter + 1
END
DROP TABLE #tmp_process
DROP TABLE #tmp_SQL
RETURN 0
END

-- 3. creaet job to collect LogSize

BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'TraceLogSize')
IF (@JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''TraceLogSize'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'TraceLogSize'
SELECT @JobID = NULL
END

BEGIN

-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'TraceLogSize', @owner_login_name = N'MSDOMAIN1LWANG2', @description = N'TraceLogSize', @category_name = N'[Uncategorized (Local)]', @enabled = 0, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'Get LogSize', @command = N'insert into MemberData.dbo.LogSize(DatabaseName, LogSize, LogSpaceUsed, Status)
execute('dbcc sqlperf(logspace) with no_infomsgs'')', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'every 15 minutes', @enabled = 1, @freq_type = 4, @active_start_date = 20091213, @active_start_time = 0, @freq_interval = 1, @freq_subday_type = 4, @freq_subday_interval = 15, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 20091230, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

-- 4. create trigger to collect buffer SQL
CREATE TRIGGER [tr_LogSize_insert]
ON [dbo].[LogSize]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ID int

IF EXISTS(select 1 from MemberData.dbo.LogSize WHERE DatabaseName = 'DBName' and LogSpaceUsed > 75)
BEGIN
SELECT @ID = ID from inserted
EXEC sp_GetBufferSQL @ID
END

END

5. Check buffer SQL
select
a.ID, a.DatabaseName, a.LogSize, a.LogSpaceUsed,
a.Date, b.EventInfo, b.Parameters
from MemberData.dbo.LogSize a
inner join MemberData.dbo.BufferSQL b
on a.ID = b.ID
and b.EventType <> 'No Event'