May 4, 2011 at 4:43 pm
This forum has been very helpful to me over the last 2 years, so I thought I'd attempt to return the favor by posting a few things that I've implemented that may be helpful to others.
In the past 2 years, the VP of I.T. and I have had to make some decisions about creating appropriately sized database log files without having any historical data upon which to base our decisions. In retrospect, we generally allocated more disk space than was needed.
Recently, I found the time to create a SQL Agent job that tracks database log file sizes and persists their "high water marks" in my Administration database. The SQL Agent job executes every 1 minute, takes less than a second to execute, updates the rows in the table with the current file utilization and timestamp, and if a new "high water mark" is reached, updates that information, and timestamps the event.
A second SQL Agent job executes every hour. If a log file "high water mark" exceeds 80% utilized, it sends me an alert so I can plan to manually grow the log file at an appropriate time.
First, the table that holds the data in the Administration database:
USE [Administration]
GO
/****** Object: Table [dbo].[Database Log File Metadata] Script Date: 05/04/2011 16:31:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Database Log File Metadata](
[rowId] [int] IDENTITY(1,1) NOT NULL,
[Server Name] [varchar](50) NOT NULL,
[Database Name] [varchar](100) NOT NULL,
[Log Size in MB] [decimal](18, 2) NOT NULL,
[Log Space Used %] [decimal](9, 2) NOT NULL,
[Data Capture Timestamp] [datetime] NOT NULL,
[Log Space Used High Water %] [decimal](9, 2) NOT NULL,
[High Water Timestamp] [datetime] NOT NULL,
CONSTRAINT [PKNCX_DatabaseLogFileMetadata_DatabaseName] PRIMARY KEY NONCLUSTERED
(
[Database Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [Administration]
GO
/****** Object: Index [UCX_DatabaseLogFileMetadata_rowId] Script Date: 05/04/2011 16:32:00 ******/
CREATE UNIQUE CLUSTERED INDEX [UCX_DatabaseLogFileMetadata_rowId] ON [dbo].[Database Log File Metadata]
(
[rowId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Second, the SQL Agent job that inspects and saves the current log file utilization for all databases:
USE Administration
BEGIN TRY
DROP TABLE #DBCC_SQLPERF
END TRY
BEGIN CATCH
END CATCH
CREATE TABLE #DBCC_SQLPERF
(
[DatabaseName] VARCHAR(100)
,[LogSizeInMB] DECIMAL(18,6)
,[LogSpaceUsed%] DECIMAL(9,6)
,[Status] INTEGER
);
INSERT INTO #DBCC_SQLPERF EXEC('DBCC SQLPERF(LOGSPACE)');
DECLARE @now DATETIME;
SET@now = GETDATE();
-- Update data for any database log file record that already exists.
UPDATE Administration..[Database Log File Metadata]
SET
[Log Size In MB] = a.[LogSizeInMB]
,[Log Space Used %] = a.[LogSpaceUsed%]
,[Data Capture Timestamp] = @now
FROM #DBCC_SQLPERF a
WHERE a.[DatabaseName] = [Database Log File Metadata].[Database Name]
-- Update data for any database log file record that already exists and whose "high water mark" has increased.
UPDATE [Database Log File Metadata]
SET
[Log Space Used High Water %] = a.[LogSpaceUsed%]
,[High Water Timestamp]= @now
FROM #DBCC_SQLPERF a
WHERE a.[DatabaseName] = [Database Log File Metadata].[Database Name]
AND a.[LogSpaceUsed%] > [Log Space Used High Water %]
-- Add rows for any database log file records that do not already exist.
INSERT INTO [Database Log File Metadata]
SELECT
@@SERVERNAME
,a.[DatabaseName]
,a.[LogSizeInMB]
,a.[LogSpaceUsed%]
,@now
,a.[LogSpaceUsed%]
,@now
FROM #DBCC_SQLPERF a
WHERE a.[DatabaseName] NOT IN (SELECT [Database Name] FROM [Database Log File Metadata]);
-- Delete a row if its related database no longer exists on this server.
DELETE FROM [Database Log File Metadata]
WHERE [Database Log File Metadata].[Database Name] NOT IN (SELECT [DatabaseName] FROM #DBCC_SQLPERF);
DROP TABLE #DBCC_SQLPERF
Last, the SQL Agent job that checks every hour for a utilization "high water mark" of 80% or higher:
USE Administration
DECLARE @count INT;
SET @count = (SELECT COUNT(*) FROM [Database Log File Metadata] WHERE [Log Space Used High Water %] >= 80);
IF @count <> 0 RAISERROR( 'A database''s log file usage has exceeded 80%', 18, 0 ) WITH LOG
Every morning, I audit our servers. One of the things I do is execute the following query to check on database log file growth:
SELECT * FROM [Administration].[dbo].[Database Log File Metadata]
LC
May 5, 2011 at 8:26 am
Thanks for contributing. That is very nice and helpful.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
May 5, 2011 at 8:35 am
Same info, without jobs.
SELECT
bs.database_name
-- , DATEADD(D , 0 , DATEDIFF(D , 0 , bs.backup_start_date)) AS BackupDate
, CONVERT(VARCHAR(50), DATEADD(s , -1 * DATEPART(s , bs.backup_start_date), bs.backup_start_date), 108) AS BackupDate_minutes
-- , bs.backup_start_date
-- , CONVERT(DECIMAL(18 , 1) , DATEDIFF(s , bs.backup_start_date ,
-- bs.backup_finish_date)
-- / 60.0) AS MinutesForBackup
, SUM(CONVERT(DECIMAL(18 , 3) , bs.backup_size / 1024 / 1024)) AS MB_backup_size
, COUNT(*) As Cnt
, AVG(CONVERT(DECIMAL(18 , 3) , bs.backup_size / 1024 / 1024)) AS Average
, MAX(CONVERT(DECIMAL(18 , 3) , bs.backup_size / 1024 / 1024)) AS Maximum
-- , ROW_NUMBER() OVER ( PARTITION BY bs.database_name ORDER BY bs.backup_start_date ) AS seqFirst
-- , ROW_NUMBER() OVER ( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC ) AS seqLast
-- , bs.[type]
FROM
msdb.dbo.backupset bs
WHERE
bs.[type] = 'L'
-- AND name IS NULL
-- AND bs.backup_start_date >= '2011-04-19'
-- AND bs.backup_size > 1024 * 1024 * 25 --min size in mbs
-- AND DATEPART(n, bs.backup_start_date) = 15
GROUP BY bs.database_name, CONVERT(VARCHAR(50), DATEADD(s , -1 * DATEPART(s , bs.backup_start_date), bs.backup_start_date), 108)
--HAVING AVG(CONVERT(DECIMAL(18 , 3) , bs.backup_size / 1024 / 1024)) > 25
ORDER BY CONVERT(VARCHAR(50), DATEADD(s , -1 * DATEPART(s , bs.backup_start_date), bs.backup_start_date), 108)
--ORDER BY bs.backup_start_date
May 5, 2011 at 8:56 am
crainlee2,
Thanks for contributing, I hope someone finds your scripts very useful.
Have you thought about not just tracking the highest log usage for each database, but keeping a historical record of log sizes? It's pretty much what you do now, just adding a new record to your admin table instead of updating the current database record.
This is good if you like to look at trends or spot potential issues before they reach that 80% mark. To see how much they grow when re-indexing occurrs or to compare usage during different time periods.
perhaps some future improvements when time permits 🙂
May 5, 2011 at 9:01 am
That's why I check the avg / max for each log backup during the day.
It's easy to plan hd space and growth with that.
May 5, 2011 at 10:52 am
calvo (5/5/2011)
crainlee2,Thanks for contributing, I hope someone finds your scripts very useful.
Have you thought about not just tracking the highest log usage for each database, but keeping a historical record of log sizes? It's pretty much what you do now, just adding a new record to your admin table instead of updating the current database record.
This is good if you like to look at trends or spot potential issues before they reach that 80% mark. To see how much they grow when re-indexing occurs or to compare usage during different time periods.
perhaps some future improvements when time permits 🙂
Ninja's_RGR'us (5/5/2011)
That's why I check the avg / max for each log backup during the day.It's easy to plan hd space and growth with that.
Calvo,
I do capture and store historical records of size for the database data files; I take a snapshot shortly after midnight every night. I was thinking about posting that implementation in the future.
That's a good idea for the log files, too. When I've got some time, I might do the same.
Right now, I've been more concerned with peak usage, since that is what might trigger an auto-growth event, and I want to implement that manually. We were completely blind on this subject and had no idea of peak log file usage until I began to track it.
What's been interesting is that in general, we have WAY over-estimated our log file size requirements for most databases. Keeping track of peak usage has been educational and we are going to use the results it's produced for estimations of required log file sizes for our next system release.
We use the TempDB exclusively for online re-indexing operations. I've begun to track file utilization on those files, too. It has also been enlightening. I am thinking about doing a similar post just on tracking TempDB file utilization.
Ninja's_RGR'us,
Tracking the daily average is a very good idea. I may implement that.
LC
May 5, 2011 at 10:57 am
[Jim].[dba].[Murphy] (5/5/2011)
Thanks for contributing. That is very nice and helpful.Jim
You're welcome, Jim. Glad to do it.
LC
May 5, 2011 at 11:10 am
My goal wasn't only the daily average, but more backup drive capacity planning.
Also it shows you the peak utilisations on the db so you can know when something's going / gone wrong just using that :w00t:.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply