Monitorowanie przyrostu wielkości tabel [Space growth]

Nieodłącznym elementem pracy z bazami danych jest monitorowanie przyrostu wielkości tabel. Wykrycie za w czasu nagłego przyrostu danych może nas uchronić od wielu nieprzyjemnych incydentów. Konieczna będzie analiza tabel i ich zasileń, jakimi danymi są zasilane, czy został otwarty nowe źródło, jak również czy przypadkiem nie mamy dubli w wyniku jakiegoś błędu. Najważniejsze jest odpowiedzenia na pytanie „dlaczego i skąd mamy tyle nowych danych?”.

Dodatkowo może okazać się, że mamy błędnie dobrane typy danych, które nie ułatwiają tematu wielkości tabel i zajmowanej przestrzeni dyskowej.

Zacznijmy zatem od stworzenia w pierwszym kroku obiektu do przechowywania danych:

USE master;

CREATE TABLE [dbo].[TableSpaceGrowth] (
	[id] INT IDENTITY(1,1) NOT NULL,
	[databasename] NVARCHAR(256) NULL,
	[table_schema] NVARCHAR(50) NULL,
	[table_name] NVARCHAR(256) NULL,
	[table_rows] INT NULL,
	[reserved_space] INT NULL,
	[data_size] INT NULL,
	[index_size] INT NULL,
	[unused_space] INT NULL,
	[insert_date] DATETIME NULL
	); 

Kolejnym krokiem będzie utworzenie procedury do zbierania danych o przyrostach:

USE [master]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[SP_MONITORING_TABLE_SIZE_GROWTH] @databasename NVARCHAR(50)
AS
BEGIN
	SET NOCOUNT ON

----------------------------
--- DECLARE VARIABLES ---
----------------------------

DECLARE
	@last_id INT,
	@next_id INT,
	@table_schema NVARCHAR(50),
	@table_name NVARCHAR(256),
	@command NVARCHAR(4000)


IF (SELECT OBJECT_ID('tempdb..#spaceused_stg')) IS NOT NULL
BEGIN
	DROP TABLE #spaceused_stg
END

DECLARE @table_list TABLE(
	id INT IDENTITY(1,1) PRIMARY KEY
	, table_schema NVARCHAR(50)
	, table_name NVARCHAR(256)
	)


CREATE TABLE #spaceused_stg (
	databasename NVARCHAR(256)
	, table_schema NVARCHAR(50)
	, table_name NVARCHAR(256)
	, table_rows INT
	, reserved_space NVARCHAR(60)
	, data_size NVARCHAR(60)
	, index_size NVARCHAR(60)
	, unused_space NVARCHAR(60)
)


SET @command = 
	'USE ' + @databasename + '
	 SELECT table_schema, table_name
	FROM
		information_schema.tables 
	WHERE table_schema + ''.'' + table_name IN (''dbo.SALES'',''dbo.INVOICE'', ''dbo.ORDERS'')'


INSERT INTO @table_list(table_schema, table_name) EXEC (@command)

----------------------------
--- DATA EXTRACT STAGE ---
----------------------------

SELECT
	@next_id = 1,
	@last_id = (SELECT MAX(id) FROM @table_list)
 
WHILE @next_id < @last_id + 1
BEGIN


	SELECT 
		@table_schema = table_schema
		, @table_name = table_name
	FROM
		@table_list
	WHERE
		id = @next_id
	

	SELECT @command = 'USE ' + @databasename + ' EXEC sp_spaceused ''[' + @table_schema + '].[' + @table_name + ']''' 

	INSERT INTO #spaceused_stg (table_name, table_rows, reserved_space, data_size, index_size, unused_space)
	EXEC (@command)

	SELECT @next_id = @next_id + 1

END

----------------------------
--- DATA TRANSFORM STAGE ---
----------------------------

UPDATE 
	#spaceused_stg 
SET 
	databasename = @databasename
	, table_schema = @table_schema
	, data_size = SUBSTRING(data_size, 1, (LEN(data_size)-3))
	, reserved_space = SUBSTRING(reserved_space, 1, (LEN(reserved_space)-3))
	, index_size = SUBSTRING(index_size, 1, (LEN(index_size)-3))
	, unused_space = SUBSTRING(unused_space, 1, (LEN(unused_space)-3))


----------------------------
--- DATA TRANSFORM LOAD ---
----------------------------

INSERT INTO [master].[dbo].[TableSpaceGrowth] (
	databasename, table_schema, table_name, table_rows, reserved_space, data_size, index_size, unused_space, insert_date)
SELECT 
	@databasename
	, table_schema
	, table_name
	, table_rows
	, reserved_space
	, data_size
	, index_size
	, unused_space
	, GETDATE() 
FROM #spaceused_stg

END

GO

Ostatnim krokiem będzie dodanie zadania (job) z cyklicznych uruchomieniem naszej procedury, oczywiści można stworzyć wszystko w kreatorze, jak również można za pomocą zamieszonego poniżej skryptu:

USE [msdb]
GO


BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Table Size Monitoring', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'local\SqlJobOwner', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Check SALES_DB database', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'EXEC [dbo].[SP_MONITORING_TABLE_SIZE_GROWTH] ''SALES_DB''', 
		@database_name=N'master', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Check SALES_DB database', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20231002, 
		@active_end_date=99991231, 
		@active_start_time=80000, 
		@active_end_time=235959, 
		@schedule_uid=N'a1f23722-c0c9-453c-8652-edb296ddbc74'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO