USE master
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_loginfo'), 'IsProcedure') = 1
BEGIN
    DROP PROCEDURE dbo.sp_loginfo
    IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_loginfo'), 'IsProcedure') = 1
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_loginfo >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.sp_loginfo >>>'
END
go
CREATE PROCEDURE sp_loginfo
--WITH ENCRYPTION
AS
/********************************************************************************/
/*      Created By :    Umachandar Jayachandran (UC)                            */
/*      Created On :    28 June 2000                                            */
/*      Description:    This stored procedure can be used to obtain information */
/*                      about the transaction log of the current database.      */
/*                      The SP generates the following reports:                 */
/*                      1) Virtual log files that comprise the transaction log  */
/*                      2) Number of virtual log files in each physical log file*/
/*                         and their approximate sizes in MB                    */
/*                      3) Virtual log files that are in use by the active      */
/*                         portion of the transaction log                       */
/*                      4) Size of the active portion of the transaction log.   */
/********************************************************************************/
/*      Resources  :    https://umachandar.com/resources.htm                 */
/********************************************************************************/
CREATE TABLE #loginfo ( 
        FileId int , FileSize decimal( 28 , 0 ) ,
        StartOffset decimal( 28 , 0 ) , FSeqNo decimal( 28 , 0 ) , 
        Status tinyint , Parity tinyint , StartTime datetime , CreateLSN varchar( 20 )
)
/* 
        FileId - Corresponds to the fileid column of sp_helpfile output. 
        FileSize - size of virtual log file within the physical file 
        StartOffSet , StartTime , CreateLSN , Parity - internal information 
        Status - can be used to find which virtual file is in use 
*/
-- Check for SQL70 & upwards. If it is SQL70, the last column in the output is StartTime
-- else it is CreateLSN.
IF CHARINDEX( '7.00' , @@VERSION ) > 0
        INSERT INTO #loginfo ( 
                FileId , FileSize , StartOffset , FSeqNo , 
                Status , Parity , StartTime 
        ) 
        EXEC( 'DBCC LOGINFO WITH TABLERESULTS' ) 
ELSE
        INSERT INTO #loginfo ( 
                FileId , FileSize , StartOffset , FSeqNo , 
                Status , Parity , CreateLSN
        ) 
        EXEC( 'DBCC LOGINFO WITH TABLERESULTS' ) 
        
-- Report of file , virtual log files in sequence 
SELECT l.FileId ,
       CAST( FILE_NAME( l.FileId ) AS varchar( 30 ) ) AS LogicalFileName,
       FileSize / POWER( 1024. , 2 ) AS "Virtual File Size in MB" ,
       l.FSeqNo
FROM #loginfo AS l
UNION ALL
SELECT NULL , '<< Transaction Log Usage >>' , 0 , NULL
ORDER BY l.FSeqNo

-- To find #virtual log files & size in each physical file 
SELECT l.FileId ,
       CAST( FILE_NAME( l.FileId ) AS varchar( 30 ) ) AS LogicalFileName,
       COUNT( l.StartOffSet ) AS #VirtualFiles ,
       SUM( l.FileSize ) / POWER( 1024. , 2 ) AS "Size in MB"
FROM #loginfo AS l
GROUP BY l.FileId
ORDER BY l.FileId 

-- To determine which virtual log files are in use
SELECT l.FileId , 
       CAST( FILE_NAME( l.FileId ) AS varchar( 30 ) ) AS LogicalFileName, 
       FileSize / POWER( 1024. , 2 ) AS "Virtual File Size in MB" , 
       l.FSeqNo 
FROM #loginfo AS l 
WHERE Status > 0 
UNION ALL
SELECT NULL , '<< Active Log Usage >>' , 0 , NULL
ORDER BY FSeqNo

-- To find approx. size in MB of the virtual log file(s) in use
-- i.e. active portion of transaction log
SELECT SUM( l.FileSize ) / POWER( 1024. , 2 ) AS "Size in MB (Active)" 
FROM #loginfo AS l 
WHERE Status > 0 
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_loginfo'), 'IsProcedure') = 1
BEGIN
    GRANT EXECUTE ON dbo.sp_loginfo To Public
    PRINT '<<< CREATED PROCEDURE dbo.sp_loginfo >>>'
END
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_loginfo >>>'
go

This page was last updated on May 01, 2006 04:28 PM.