SQLServerWiki

“The Only Thing That Is Constant Is Change”

Avoid auto-growth and automatically grow files every month based on previous month growth pattern.

Posted by database-wiki on April 22, 2013

Scenario:

======

—  Your Data file growth increments need to be tuned based on trends of database growth.  Tuning the file growth increments can reduce database engine overhead by limiting the frequency of file growth.  Database performance is being impacted if the database engine is increasing the file size during data insertion.

—  Your Data file growth increments have not been adjusted in a very long time.  We have grown more than double in size (possibly triple) since data file growth increments have been adjusted.

—  Your Data file growth increments should be adjusted so that data file sizes are not extended more than once every other week.

—  Data file growth controlled by either percentage of growth or by a specific size in Mb.   Is it preferred to grow data files by specific size? Yes it is

Resolution:

========

— I have used the logic of determining the data file and log file auto growth using SQL Server default trace.

— One good thing about SQL Server default trace is that we can determine when the data file and log file has grown using event class. EventClass = 92 is Data File Auto-growth including indexes And EventClass = 93 is Log File Auto-growth.

— We can also use this trace to determine who had done what in the SQL Server instance if we enough .trc files to trace back to the date the event has happened. Example, if anybody had dropped an object or database etc. J

  1. First, I have created a logging table DataAndLogFileGrowthLog.

USE [Utility]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DataAndLogFileGrowthLog]’) AND type in (N’U’))

BEGIN

CREATE TABLE [dbo].[DataAndLogFileGrowthLog](

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[Spid] [varchar](5) NOT NULL,

[DatabaseID] [bigint] NOT NULL,

[DatabaseName] [varchar](255) NOT NULL,

[FileId] [bigint] NOT NULL,

[FileName] [varchar](255) NOT NULL,

[PhysicalName] [varchar](1000) NOT NULL,

[Drive] [varchar](10) NOT NULL,

[FreeSpaceMB] [bigint] NOT NULL,

[ApplicationName] [varchar](500) NULL,

[EventClass] [bigint] NOT NULL,

[GrowthMB] [bigint] NOT NULL,

[GrowthCount] [bigint] NOT NULL,

[GrowthDurationMS] [bigint] NOT NULL,

[GrowthDate] [date] NOT NULL,

[GrowthStartTime] [time](7) NOT NULL,

[GrowthEndTime] [time](7) NOT NULL,

[IsLogFile] [bit] NULL,

[AlterAndReport] [bit] NULL,

[CanGrow] [bit] NULL,

[AlterStatement] [varchar](1000) NULL,

PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

END

GO

SET ANSI_PADDING OFF

GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_DataAndLogFileGrowthLog_IsLogFile]’) AND type = ‘D’)

BEGIN

ALTER TABLE [dbo].[DataAndLogFileGrowthLog] ADD  CONSTRAINT DF_DataAndLogFileGrowthLog_IsLogFile DEFAULT ((0)) FOR [IsLogFile]

END

GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_DataAndLogFileGrowthLog_AlterAndReport]’) AND type = ‘D’)

BEGIN

ALTER TABLE [dbo].[DataAndLogFileGrowthLog] ADD  CONSTRAINT DF_DataAndLogFileGrowthLog_AlterAndReport DEFAULT ((0)) FOR [AlterAndReport]

END

GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_DataAndLogFileGrowthLog_CanGrow]’) AND type = ‘D’)

BEGIN

ALTER TABLE [dbo].[DataAndLogFileGrowthLog] ADD  CONSTRAINT DF_DataAndLogFileGrowthLog_CanGrow DEFAULT ((0)) FOR [CanGrow]

END

GO

2. DataAndLogFileGrowthLogging stored procedure to scan the default SQL trace on daily basis and to log the details in DataAndLogFileGrowthLog table.

USE [Utility]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF EXISTS (SELECT 1     FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspPub_DataAndLogFileGrowthLogging]’) AND type IN (N’P’,N’PC’))

DROP PROCEDURE [dbo].[uspPub_DataAndLogFileGrowthLogging]

GO

CREATE PROCEDURE [dbo].[uspPub_DataAndLogFileGrowthLogging]

AS

IF OBJECT_ID(‘tempdb..#FreeSpaceDrives’, ‘u’) IS NOT NULL

BEGIN

DROP TABLE #FreeSpaceDrives;

END

CREATE TABLE #FreeSpaceDrives (DRIVE CHAR(1) PRIMARY KEY , FREESPACE BIGINT NOT NULL )

INSERT INTO #FreeSpaceDrives

EXECUTE master.dbo.xp_fixeddrives;

DECLARE @filename NVARCHAR(1000);

DECLARE @ReportHTML NVARCHAR(MAX);

DECLARE @Subject NVARCHAR(250);

DECLARE @12MonthsAgo DATETIME2(7) = DATEADD(dd, 0, DATEDIFF(dd, 0, dateadd(mm, – 12, getutcdate())));

SET @12MonthsAgo = dateadd(dd, – (datepart(dd, @12MonthsAgo) – 1), @12MonthsAgo);

— Get the name of the current default trace

SELECT @filename = CAST(value AS NVARCHAR(1000))

FROM::fn_trace_getinfo(DEFAULT)

WHERE traceid = 1

AND property = 2;

INSERT INTO [utility].[dbo].[DataAndLogFileGrowthLog] (

[Spid]

,[DatabaseID]

,[DatabaseName]

,[FileId]

,[FileName]

,[PhysicalName]

,[Drive]

,[FreeSpaceMB]

,[ApplicationName]

,[EventClass]

,[GrowthMB]

,[GrowthCount]

,[GrowthDurationMS]

,[GrowthDate]

,[GrowthStartTime]

,[GrowthEndTime]

,[IsLogFile]

)

SELECT ftg.SPID

,ftg.DatabaseID

,ftg.DatabaseName

,m.file_id

,ftg.FileName

,m.physical_name

,substring(m.physical_name, 1, 1) AS Drive

,b.FreeSpace AS ‘FreeSpaceMB’

,ftg.ApplicationName

,EventClass

,SUM(ftg.IntegerData * 8) / 1024.0 AS ‘Growth(MB)’

,COUNT(*) AS GrowthCount

,SUM(ftg.duration / 1000) AS ‘GrowthDuration(MS)’

,convert(VARCHAR(10), DATEADD(d, – 1, GETDATE()), 120) AS GrowthDate

,convert(CHAR(5), StartTime, 108) AS GrowthStartTime

,convert(CHAR(5), EndTime, 108) AS GrowthEndTime

,CASE

WHEN EventClass = 93

THEN 1

ELSE 0

END AS ‘ISLogFile?’

FROM::fn_trace_gettable(@filename, DEFAULT) AS ftg

INNER JOIN sys.master_files m ON ftg.FileName = m.NAME

INNER JOIN #FreeSpaceDrives AS b ON substring(m.physical_name, 1, 1) = b.[DRIVE]

WHERE EventClass IN (92 , 93 ) — Data And Log File Auto-grow

AND StartTime > DATEADD(dy, – 1, GETDATE())

AND DatabaseName NOT IN (

SELECT NAME = D.NAME

FROM sys.databases D

WHERE (

D.database_id BETWEEN 1

AND 3 – exclude master, model, and msdb

OR D.NAME LIKE ‘ReportServer%’ – exclude Report Server

OR D.is_distributor = 1 – exclude Replication

)

AND D.source_database_id IS NULL — not a snapshot

AND D.state_desc = N’ONLINE’ — is online

AND D.user_access_desc = N’MULTI_USER’ — open for all users

AND D.is_read_only = 0

)

GROUP BY ftg.SPID

,ftg.DatabaseID

,ftg.DatabaseName

,m.file_id

,ftg.FileName

,m.physical_name

,Drive

,b.FREESPACE

,ftg.ApplicationName

,EventClass

,ftg.StartTime

,ftg.EndTime

ORDER BY databasename DESC

— Clean up

IF OBJECT_ID(‘tempdb..#FreeSpaceDrives’, ‘u’) IS NOT NULL

BEGIN

DROP TABLE #FreeSpaceDrives;

END

IF Day(dateadd(D, 1, GETUTCDATE())) = 1

BEGIN

UPDATE DGL

SET CanGrow = 1

,AlterAndReport = 1

,AlterStatement = ‘ALTER DATABASE ‘ + Databasename + ‘ MODIFY FILE (NAME=”’ + FileName + ”’,SIZE=’ + CAST(Qry.GrowthMB AS VARCHAR(50)) + ‘MB);’

FROM (

SELECT MAX(ID) AS ID

,Sum([GrowthMB]) AS GrowthMB

FROM [DataAndLogFileGrowthLog](NoLock)

GROUP BY Databasename

,FileId

,[Filename]

HAVING Sum([FreeSpaceMB]) >= Sum([GrowthMB])

) AS Qry

INNER JOIN [DataAndLogFileGrowthLog] (nolock) DGL ON Qry.ID = DGL.ID

— Delete data more than 1 year old.

DELETE FROM [utility].[dbo].[DataAndLogFileGrowthLog]  WHERE growthdate < @12MonthsAgo

END

GO

3. DataAndLogFileResize stored procedure to do the alter on last day of every month and send an the email.

USE [Utility]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

GO

IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspPub_DataAndLogFileResize]’) AND type IN (N’P’,N’PC’))

DROP PROCEDURE [dbo].[uspPub_DataAndLogFileResize]

GO

CREATE PROCEDURE [dbo].[uspPub_DataAndLogFileResize]

AS

BEGIN TRY

BEGIN

DECLARE @Sql NVARCHAR(4000)

DECLARE @RowCnt INT = 0

DECLARE @pointer INT = 0

SELECT @RowCnt = MAX(ID)

FROM dbo.DataAndLogFileGrowthLog(NOLOCK)

WHERE AlterAndReport = 1

AND Month(Growthdate) = Month(getutcdate())

AND Year(Growthdate) = Year(getutcdate())

WHILE @pointer < @RowCnt

BEGIN

SELECT @Sql = AlterStatement

FROM dbo.DataAndLogFileGrowthLog

WHERE AlterAndReport = 1

AND Month(Growthdate) = Month(getutcdate())

AND Year(Growthdate) = Year(getutcdate())

AND ID = @pointer

EXECUTE sp_executesql @Sql

SET @pointer = @pointer + 1

END

DECLARE @xmlHeaderdata XML

DECLARE  @xmlHeaderdata1 Varchar(max)

—          Select Top 1  @tableVar  ‘<p><br>’ + cast(ID as varchar(10)) +  Description + ‘</br></p>’ from NH_RPT..DimEventType

Select  @xmlHeaderdata1=

( SELECT

CAST(DatabaseName as Varchar(400)) as [td],

CAST([FileName]as Varchar(400))  as [td],

CAST(PhysicalName as Varchar(400)) as [td],

CAST(FreeSpaceMB as Varchar(400))  as [td],

CAST( GrowthMB as Varchar(400))           as [td],

CAST(GrowthCount as Varchar(400)) as [td],

CAST(GrowthDate as Varchar(400)) as [td],

CAST(IsLogFile as Varchar(400))   as [td],

CAST(AlterStatement as Varchar(400)) as [td]

FROM Utility.dbo.DataAndLogFileGrowthLog

–WHERE AlterAndReport = 1

—          AND Month(Growthdate) = Month(getutcdate())

—          AND Year(Growthdate) = Year(getutcdate())

For XML raw(‘tr’), Elements

)

–bgcolor=”lightblue”

SET @xmlHeaderdata1='<table border=”3″>

<tr>

<td>DatabaseName</td><td>FileName</td><td>PhysicalName</td><td>FreeSpaceMB</td><td>GrowthMB</td><td>GrowthCount</td>

<td>GrowthDate</td><td>IsLogFile</td><td>AlterStatement</td>

</tr>

‘ + @xmlHeaderdata1 + ‘</table>’

Print @xmlHeaderdata1

EXEC msdb..sp_send_dbmail

@profile_name = ‘DBMail’,

@recipients = ‘abc@gmail.com;def@gmail.com’,

@body_format = ‘HTML’,

@subject=’FileSize Has Been Altered For Following DataFiles and LogFiles.’,

@body= @xmlHeaderdata1

END

END TRY

BEGIN CATCH

EXEC uspPvt_RethrowError;

RETURN – 1;

END CATCH

GO

4. AlterDataAndLogFileGrowth will be the job that will take care of this activity. Initially I have disable the step 2 running the alter store procedure DataAndLogFileResize to monitor and check the logic for some time. Later you can enable the same.

USE [msdb]

GO

IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N’Utility AlterDataAndLogFileGrowth’)

EXEC msdb.dbo.sp_delete_job @job_name=N’Utility AlterDataAndLogFileGrowth’, @delete_unused_schedule=1

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’Utility AlterDataAndLogFileGrowth’,

@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’sqldba’, @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’DataAndLogFileGrowthLog’,

@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 [utility].[dbo].[uspPub_DataAndLogFileGrowthLogging]’,

@database_name=N’Utility’,

@flags=0

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

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’DataAndLogFileResize’,

@step_id=2,

@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 [Utility].[dbo].[uspPub_DataAndLogFileResize]’,

@database_name=N’Utility’,

@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_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

Email Alert after resizing the data and Log files:

DatabaseName FileName PhysicalName FreeSpaceMB GrowthMB GrowthCount GrowthDate IsLogFile AlterStatement
NH NH_Data1 D:\MSSQL10\MSSQL\DATA\NH_Data1.mdf 456000 100 15 2001-12-12 0 Alter   Database NH MODIFY FILE (NAME=’ NH_Data1’,SIZE=’1500’ MB);
NH NH_Index1 D:\MSSQL10\MSSQL\DATA\NH_Index1.ndf 456000 100 21 2001-12-12 0 Alter   Database NH MODIFY FILE (NAME=’ NH_Index1’,SIZE=’2100’ MB);
NH NH_Index2 D:\MSSQL10\MSSQL\DATA\NH_Index2.ndf 456000 100 11 2001-12-12 0 Alter   Database NH MODIFY FILE (NAME=’ NH_Index2’,SIZE=’1100’ MB);
NH NH_Data2 D:\MSSQL10\MSSQL\DATA\NH_Data2.ndf 456000 100 10 2001-12-12 0 Alter   Database NH MODIFY FILE (NAME=’ NH_Data2’,SIZE=’1000’ MB);
NH NH_Data3 D:\MSSQL10\MSSQL\DATA\NH_Data3.ndf 456000 100 13 2001-12-12 0 Alter   Database NH MODIFY FILE (NAME=’ NH_Data3’,SIZE=’1300’ MB);
NH NH_Dly_1 D:\MSSQL10\MSSQL\DATA\ NH_Dly_1.ndf 456000 100 16 2001-12-12 0 Alter   Database NH MODIFY FILE (NAME=’ NH_Dly_1’,SIZE=’1600’ MB);
NH NH_Mntr_1 D:\MSSQL10\MSSQL\DATA\NH_Mntr_1.ndf 456000 100 19 2001-12-12 0 Alter   Database NH MODIFY FILE (NAME=’ NH_Mntr_1’,SIZE=’1900’ MB);
NH NH_Dly_2 D:\MSSQL10\MSSQL\DATA\NH_Dly_2.ndf 456000 100 13 2001-12-12 0 Alter   Database NH MODIFY FILE (NAME=’ NH_Dly_2’,SIZE=’1300’ MB);
NH_RPT NH_Log1 D:\MSSQL10\MSSQL\DATA\NH_Log1.ldf 456000 100 25 2001-12-12 1 Alter   Database NH MODIFY FILE (NAME=’ NH_Log1’,SIZE=’2500’ MB);
NH_RPT NH_Log2 D:\MSSQL10\MSSQL\DATA\NH_Log2.ldf 456000 100 30 2001-12-12 1 Alter   Database NH MODIFY FILE (NAME=’ NH_Log2’,SIZE=’3000’ MB);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: