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