Skip to content

Instantly share code, notes, and snippets.

@AndyMeps

AndyMeps/create_log_file_usage_table.sql

Last active October 19, 2015 16:23
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save AndyMeps/59dae14f9f60f51d77c2 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save AndyMeps/59dae14f9f60f51d77c2 to your computer and use it in GitHub Desktop.
Download ZIP
Logs database log file sizes to a table in the [master] schema for diagnostic / analytic purposes.
Raw
create_log_file_usage_table.sql
CREATE TABLE [master].[dbo].[LogFileUsage] (
[Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL
,[DatabaseName] [nvarchar](100) NOT NULL
,[LogSize] [real] NOT NULL
,[PercentageUsed] [real] NOT NULL
,[LogDateTime] [datetime] NOT NULL DEFAULT GETDATE()
)
Raw
log_file_agent_job_t-sql.sql
DECLARE @temp TABLE (
DatabaseName NVARCHAR(100)
,LogSize REAL
,PercentageUsed REAL
,Status INT
)
INSERT INTO @temp (DatabaseName, LogSize, PercentageUsed, Status)
EXEC('DBCC SQLPERF(LOGSPACE)')
INSERT INTO [master].[dbo].[LogFileUsage]
(DatabaseName
,LogSize
,PercentageUsed)
SELECT DatabaseName, LogSize, PercentageUsed FROM @temp
Raw
select_log_file_history.sql
SELECT
Id
,DatabaseName
,LogSize
,(LogSize / 100) * PercentageUsed AS [PercentUsedInMB]
,PercentageUsed
,LogDateTime
FROM
[master].[dbo].[LogFileUsage]
WHERE
DatabaseName = '%'
ORDER BY
LogDateTime DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment