Binaryworld - Handling database file growth ... [ SQL Server -> Server Operations ]

Scripts to automatically get notified if a database grew due to the autogrowth option being turned on.

--***********************************************************************
--* Script used to check whether or not any databases grew   *
--* because of auto-growth being enabled. The script creates   *
--* a bunch of procedures etc to keep things dynamic. *
--* *
--* I am aware that cursors are slower, and that truncating the   *
--* table every time is more resource intensive, but I can't be   *
--* bothered to spend more time into doing this faster. Feel free *
--*   to alter the script if you like. If you tweak it somewhat,   *
--*   I'd love to see what you did with it :)          *
--* *
--*   (c) 2002 - Peter Schmitz *
--* *
--*   Use the source, Luke! *
--***********************************************************************

--***********************************************************************
--*            Creating the Alert.              *
--***********************************************************************

DECLARE @FreeAlertNumber int,
   @SQLString nvarchar(200)

-- Check for a free alert-ID. We ignore all numbers < 50,000
-- because those are reserved by SQL Server.
-- If no alerts over 50,000 exist, the result stored would be NULL.
-- If that's the case, we'll use
-- 50,001

SELECT @FreeAlertNumber = (ISNULL(((SELECT MAX(error)
FROM master..sysmessages WHERE error > 50000) + 1), 50001))

-- Create new alert with the free Alertnumber, severity 10
-- (Informational). Language = English, and the 'true
-- value specifies the alert will be written to the Windows Eventlog.

EXEC sp_configure N'allow updates', 1
RECONFIGURE WITH OVERRIDE

SELECT @SQLString = 'EXEC sp_addmessage ' +
CAST(@FreeAlertNumber AS nvarchar(11)) + ', 10, ' +
'''Database %d grew with %s MB.''' + ', ' + '''English''' +
', ' + '''true'''
EXEC sp_executesql @SQLString

EXEC sp_configure N'allow updates', 0
RECONFIGURE WITH OVERRIDE
go

--***********************************************************************
--*            Creating the table.              *
--***********************************************************************

-- Create a table to hold the database's ids, filenameids and sizes.
-- I chose to create this table in the msdb database. Change this
-- if you want.

Use msdb
go

CREATE TABLE [dbo].[tbl_DatabaseSizes](
[dbid] [smallint] NOT NULL ,
[fileid] [smallint] NOT NULL ,
[filesize] [int] NOT NULL
)
go

ALTER TABLE [dbo].[tbl_DatabaseSizes] WITH NOCHECK ADD
CONSTRAINT [PK_tbl_DatabaseSizes] PRIMARY KEY CLUSTERED
(
[dbid],
[fileid]
)
GO

--***********************************************************************
--*       Creating a procedure to fill the table.         *
--***********************************************************************

USE msdb
go

CREATE PROCEDURE usp_FillDatabaseSizeTable AS

SET NOCOUNT ON

-- Clear the table

DELETE FROM msdb..tbl_DatabaseSizes

-- Fill the table with values. We just loop through
-- sysdatabases to retrieve all database ids.
-- Then, we loop through sysfiles to retrieve the fileids and
-- the sizes of these files.

DECLARE @db_name varchar(255),
@SQLString nvarchar(4000)

DECLARE cur_Databasenames CURSOR FOR

-- Get all databasenames
SELECT name FROM master..sysdatabases ORDER BY dbid

OPEN cur_Databasenames
FETCH NEXT FROM cur_Databasenames INTO @db_name

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @SQLString =
'DECLARE cur_FileIDs CURSOR FOR

SELECT fileid, size FROM ' + @db_name + '..sysfiles

DECLARE @fileid smallint,
@filesize int

OPEN cur_FileIDs
FETCH NEXT FROM cur_FileIDs INTO @fileid, @filesize

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO msdb..tbl_DatabaseSizes
SELECT db_id(''' + @db_name + '''), @fileid, @filesize
FETCH NEXT FROM cur_FileIDs INTO @fileid, @filesize
END
CLOSE cur_FileIDs
DEALLOCATE cur_FileIDs'

EXEC sp_executesql @SQLString

FETCH NEXT FROM cur_Databasenames INTO @db_name
END

Close cur_Databasenames
DEALLOCATE cur_Databasenames

SET NOCOUNT OFF

go

--***********************************************************************
--*     Creating a procedure to check if autogrowth occurred.    *
--***********************************************************************

-- Create the stored procedure that will verify whether or not the datafiles
-- grew. We check this by comparing the current size against the earlier
-- saved size. If there's a difference, the database grew (or was
-- shrinked). We will add the difference to the earlier created table if
-- any differences exist.

USE msdb
go

CREATE PROCEDURE usp_CheckFileGrowth AS

SET NOCOUNT ON

DECLARE @db_name varchar(255),
@size int,
@sizediff int,
@SQLString nvarchar(4000)

DECLARE cur_Databasenames CURSOR FOR

-- All databases
SELECT name FROM master..sysdatabases ORDER BY dbid

OPEN cur_Databasenames

-- Find the alert number for our earlier defined error message
DECLARE @AlertNumber int
SELECT @AlertNumber = (SELECT error FROM master..sysmessages
WHERE description = 'Database %s grew with %d MB.')

FETCH NEXT FROM cur_Databasenames INTO @db_name

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQLString =
'DECLARE cur_FileIDs CURSOR FOR

SELECT fileid, size FROM ' + @db_name + '..sysfiles

DECLARE @fileid smallint,
@filesize int,
@filesize_old int,
@sizediff int

OPEN cur_FileIDs
FETCH NEXT FROM cur_FileIDs INTO @fileid, @filesize

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @filesize_old =
(SELECT filesize FROM msdb..tbl_DatabaseSizes
WHERE dbid = db_id(''' + @db_name + ''')
AND fileid = @fileid)

SELECT @sizediff = CAST(((@filesize - @filesize_old) * 8) / 1024 As Varchar)

IF @filesize_old <> @filesize
BEGIN
RAISERROR (' + CAST(@Alertnumber As varchar)+ ',10,1, ''' + @db_name + ''',@sizediff)
UPDATE msdb..tbl_DatabaseSizes
SET filesize = (filesize + (@filesize - @filesize_old))
WHERE dbid = db_id(''' + @db_name + ''')
AND fileid = @fileid
END
FETCH NEXT FROM cur_FileIDs INTO @fileid, @filesize
END
CLOSE cur_FileIDs
DEALLOCATE cur_FileIDs'

EXEC sp_executesql @SQLString
FETCH NEXT FROM cur_Databasenames INTO @db_name
END

Close cur_Databasenames
DEALLOCATE cur_Databasenames

-- Update the table for tomorrow.
EXEC usp_FillDatabaseSizeTable

SET NOCOUNT OFF
go

--***********************************************************************
--*   Creating a job that runs usp_CheckFileGrowth daily       *
--* Code is scripted from a job I created. If you prefer, you *
--*   of course could choose to run usp_FillDatabaseSizeTable as a  *
--* second step in the job.
--***********************************************************************

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

-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Check Database Sizes')
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 ''Check Database Sizes''
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'Check Database Sizes'
  SELECT @JobID = NULL
END

BEGIN

-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT
, @job_name = N'Check Database Sizes', @owner_login_name = N'sa'
, @description = N'No description available.', @category_name = N'Database Maintenance'
, @enabled = 1, @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'Run usp_CheckFileGrowth'
, @command = N'EXEC usp_CheckFileGrowth', @database_name = N'msdb'
, @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'Check for autogrowth daily', @enabled = 1
, @freq_type = 4, @active_start_date = 20021114, @active_start_time = 0
, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0
, @freq_relative_interval = 0, @freq_recurrence_factor = 0
, @active_end_date = 99991231, @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: