BI SQL # 180 : SQL Server DBA Scripts : List log file information for all the databases on the server
Hi Geeks,
In this article we are going to cover How to List log file information for all the databases on the server.
In this post we are going to discuss following points:
- Problem Statement of SQL Script:
- Description of SQL Script:
- SQL Script Output Column
- SQL Script Code
- SQL Script Output Screenshot
- User Level to execute
Problem Statement of SQL Script:
How to find the log file information for all Databases on the server?
Description of SQL Script:
This Transact-SQL script uses the DMV sys.dm_os_performance_counters
To list log file information:
Allocated size in MB
used size in MB
free size in MB
percent of size used
count of VLF the log have been grown, shrink and truncated since last service start for all databases, similar to the DBCC SqlPerf(LogSpace) command.
SQL Script Output Column
SQL Script Code
SELECT PVT.DatabaseName ,CONVERT(NUMERIC(38, 1), PVT.[Log File(s) Size (KB)] / 1024.0) AS LogFileSizeMB ,CONVERT(NUMERIC(38, 1), PVT.[Log File(s) Used Size (KB)] / 1024.0) AS LogFileUsedMB ,CONVERT(NUMERIC(38, 1), ( PVT.[Log File(s) Size (KB)] - PVT. [Log File(s) Used Size (KB)] ) / 1024.0) AS LogFileFreeMB ,PVT.[Percent Log Used] AS PercLogUsed ,PVT.[Log Growths] AS LogGrowths ,PVT.[Log Shrinks] AS LogShrinks ,PVT.[Log Truncations] AS LogTrunc FROM ( SELECT RTRIM(SUB.counter_name) AS CounterName ,RTRIM(SUB.instance_name) AS DatabaseName ,SUB.cntr_value AS CounterValue FROM [master].[sys].[dm_os_performance_counters] AS SUB WHERE SUB.[object_name] LIKE 'SQLServer:Databases%' -- To be independed of instance name. AND SUB.counter_name IN ( 'Log File(s) Size (KB)' ,'Log File(s) Used Size (KB)' ,'Percent Log Used' ,'Log Growths' ,'Log Shrinks' ,'Log Truncations' ) ) AS OPC PIVOT(SUM(OPC.CounterValue) FOR OPC.CounterName IN ( [Log File(s) Size (KB)] ,[Log File(s) Used Size (KB)] ,[Percent Log Used] ,[Log Growths] ,[Log Shrinks] ,[Log Truncations] )) AS PVT ORDER BY PVT.DatabaseName;
SQL Script Output Screenshot
User Level to execute
- 300
Hope you will like How to List log file information for all the databases on the server.
If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.
If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :
For More information related to BI World visit my Mentalist Blog
SQL Server Mentalist >> SQL Learning Blog
Business Intelligence Mentalist >> BI World
Infographic Mentalist >> Image worth explaining thousand Words
Microsoft Mentalist >> MVC,ASP.NET, WCF & LinQ
DBA Mentalist >>Advance SQL Server Blog
Microsoft BI Mentalist >> MS BI Development Update
Connect With me on
SQL Server Mentalist 