-- Script to handle file growth
-- Replace string <operator_name> with the operator name
--Replace string <dbname> with the database name where the 'usp_dba_monitor_file_growth'
-- stored procedure will be created
--
--
-- add message
	EXEC sp_addmessage @msgnum = 60000, @severity = 16, 
    	@msgtext = N'File %s has changed size from %d to %d KB.',
    	@lang = 'us_english'
	go
	
-- create alert
	IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Database file has changed size'))
 	---- Delete the alert with the same name.
  	EXECUTE msdb.dbo.sp_delete_alert @name = N'Database file has changed size' 
	BEGIN 
	EXECUTE msdb.dbo.sp_add_alert @name = N'Database file has changed size', @message_id = 60000,
	@severity = 0, @enabled = 1, @delay_between_responses = 0, @include_event_description_in = 5, 
	@category_name = N'[Uncategorized]'

	EXECUTE msdb.dbo.sp_add_notification @alert_name = N'Database file has changed size', 
	@operator_name = N'<operator_name>', @notification_method = 1
	END
	go

-- create table to hold database_id, file_id and size
	if exists (select * from sysobjects where id = object_id(N'[dbo].[dba_file_info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
	drop table [dbo].[dba_file_info]
	go

	CREATE TABLE [dbo].[dba_file_info] (
	[dbid] [int] NOT NULL ,
	[fileid] [int] NOT NULL ,
	[size] [int] NULL ,
	CONSTRAINT [pk_dba_file_info] PRIMARY KEY  CLUSTERED 
	(
		[dbid],
		[fileid]
	)  
	)
	go


-- create stored procedure 

if exists (select * from sysobjects where id = object_id(N'[dbo].[usp_dba_monitor_file_growth]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_dba_monitor_file_growth]
GO


create procedure usp_dba_monitor_file_growth as
BEGIN 
	set nocount on

	declare @dbid int
	declare @dbname sysname
	declare @cmd varchar(8000)
	declare @fileid smallint
	declare @filename sysname
	declare @current_size int
	declare @stored_size int
	declare @current_size_KB int
	declare @stored_size_KB int
	declare @msg varchar(600)
	declare @status smallint

	set @status = 0		-- successful status

	declare db_cursor cursor for
	select name, dbid
	from master.dbo.sysdatabases
	order by dbid

	open db_cursor

	fetch next from db_cursor into @dbname, @dbid

	while @@fetch_status = 0
	begin
		select @msg = 'database: ' + @dbname 
		print ' '
		print @msg

		select @cmd = 'declare file_cursor cursor for
			select fileid, size, name
			from ' 
			 + quotename(@dbname, '[') + '.dbo.sysfiles'
		execute (@cmd)
	
		open file_cursor

		fetch next from file_cursor into @fileid, @current_size, @filename

		while @@fetch_status = 0
		begin
			-- compare the current size against the stored file size
			select @stored_size = size 
			from dba_file_info
			where dbid = @dbid and
			fileid = @fileid

			if @@ROWCOUNT = 0	-- no record found
			begin
				select @msg = 'No record found for database ' 
					+ @dbname + ' file ' + @filename
				print @msg
				
				insert into dba_file_info
				values (@dbid, @fileid, @current_size)
			end
			else
			begin
				if @current_size <> @stored_size	-- values are different
				begin
					select @msg = 'File ' + RTRIM(@filename) + ' size has been changed'
					print @msg

					set @current_size_KB = @current_size * 8
					set @stored_size_KB =  @stored_size * 8
					RAISERROR(60000, 16, 1, @filename, @stored_size_KB, @current_size_KB) with log
					
										-- update stored size value
					update dba_file_info
					set size = @current_size
					where dbid = @dbid and fileid = @fileid

					if @@ERROR <> 0
					BEGIN
						print 'Error updating dba_file_info table'
						set @status = 1
					END

				end
				else
				begin
					select @msg = 'File ' +  RTRIM(@filename) + ' size has NOT been changed'
					print @msg
				end
			end
			
			fetch next from file_cursor into @fileid, @current_size, @filename
		end
		close file_cursor
		deallocate file_cursor

		fetch next from db_cursor into @dbname, @dbid
	end
	
	close db_cursor
	deallocate db_cursor

	if @status <> 0
		return 1
	return 0
END

go

-- create a job to execute the usp_dba_monitor_file_growth procedure



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'#DBA - Monitor File Growth')       
  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 ''#DBA - Monitor File Growth'' 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'#DBA - Monitor File Growth' 
    SELECT @JobID = NULL
  END 

BEGIN 

  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'#DBA - Monitor File Growth', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', @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'step 1', @command = N'exec usp_dba_monitor_file_growth', @database_name = N'<dbname>', @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'Monitor File Growth', @enabled = 1, @freq_type = 4, @active_start_date = 19991223, @active_start_time = 230000, @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: