Profiler tracking db growth - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Profiler tracking db growth

Author

Topic

Sarat
Constraint Violating Yak Guru

265 Posts

Posted - 2003-06-06 : 18:43:30

Hi,
I ran profiler with database specific events and data columns in the trace file against production database while I ran defrag job. The events I wanted to record were duration, end time, event class, file name, integer data and the event class was: data file auto grow/shrink, log file auto grow/shrink. I understand that these events are not triggered if the data file is grown explicitly through alter database so is defrag job (DBCC INDEXDEFRAG)actually doing an alter statement?. I didn't get any recordings on my trace even though I know the log file grew due to derfag job.
Thanks,
Sarat.

**To be intoxicated is to feel sophisticated, but not be able to say it.**

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-06-09 : 13:38:34

Use the default template for the trace first to see what commands are running. This will show you if DBCC INDEXDEFRAG is really doing an ALTER.

Tara

Shastryv
Posting Yak Master

145 Posts

Posted - 2003-06-27 : 13:46:05

If you want to know when did you datafile or logfile growth occurred here is script to notify you the details

-- 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.

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:

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources