fn_log | LSN

select

       CONVERT(

              BIGINT

              , Convert(

                     varbinary(MAX)

                     , '0x' + SUBSTRING([CURRENT LSN], 1, CHARINDEX(':', [CURRENT LSN]) - 1)

                     , 1

              )

       ) * 1000000000000000 +

convert(

       BIGINT

       , Convert(

              varbinary(MAX)

              , '0x' + SUBSTRING([CURRENT LSN]

                     , CHARINDEX(':', [CURRENT LSN]) + 1

                     , CHARINDEX(':', [CURRENT LSN], CHARINDEX(':', [CURRENT LSN]) + 1) - CHARINDEX(':', [CURRENT LSN]) - 1)

              , 1

       ))* 100000 +

CONVERT(

       INT

       , convert(

              varbinary(MAX)

              , '0x' + SUBSTRING(

                     [CURRENT LSN]

                     , CHARINDEX(':', [CURRENT LSN], CHARINDEX(':', [CURRENT LSN]) + 1) + 1

                     , len([CURRENT LSN]) - CHARINDEX(':', [CURRENT LSN], CHARINDEX(':', [CURRENT LSN]) + 1))                       

              , 1)

       ) LSN_NUM, [CURRENT LSN], Description

from ::fn_dblog(default, default)


CREATE FUNCTION [fn_ifNeedFullBackup] (

    @DBName sysname)

RETURNS BIT

AS

BEGIN

    DECLARE @IsReallyFull BIT;

    DECLARE @LastLogBackupLSN NUMERIC (25,0);

    DECLARE @RecoveryModel TINYINT;

 

    SELECT

        @LastLogBackupLSN = [last_log_backup_lsn]

    FROM

        sys.database_recovery_status

    WHERE

        [database_id] = DB_ID (@DBName);

 

    SELECT

        @RecoveryModel = [recovery_model]

    FROM

        sys.databases

    WHERE

        [database_id] = DB_ID (@DBName);

 

    IF (@RecoveryModel = 1 AND @LastLogBackupLSN IS NOT NULL)

        SELECT @IsReallyFull = 1

    ELSE

        SELECT @IsReallyFull = 0;

 

    RETURN (@IsReallyFull);

END;

GO

Comments