About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Wednesday, January 2, 2013

vwLogInfoSummary - show Virtual Log Files per database


In the last post on Virtual Log Files, a procedure was given, PopLogInfo, to populate table LogInfo. 

Here, a view is presented to make best use of that table, to see how many VLFs a given database has.


IF object_id('dbo.vwLogInfoSummary') Is Not Null
      DROP VIEW dbo.vwLogInfoSummary
GO

CREATE VIEW vwLogInfoSummary
AS
/*    DATE        AUTHOR            REMARKS    
      12/15/12    PPaiva            Initial creation.
     
      SELECT *
      FROM LogInfo
     
      SELECT *
      FROM vwLogInfoSummary
      ORDER BY NumVLFs desc

*/
SELECT      Server,
                  DB,
                  FileID,
                  Status,
                  StatusDesc,
                  COUNT(*) NumVLFs,
                  InsertDate
      FROM LogInfo
      WHERE StatusDesc = 'Reusable'
      GROUP BY Server, DB, FileID, Status, StatusDesc, InsertDate


No comments:

Post a Comment