Wednesday, February 1, 2012

Do you use backup log with truncate only in your instance

In some cases developers are using the statement BACKUP LOG <databasename> WITH TRUNCATE_ONLY in their scripts. And this script is going to be scheduled in our production environment. If the database has not been set on simple and we make backups from the log in between for our DR. Then those backup logs are useless.

How can we determine if the log isn't being backup in a proper manner.

Here is the script I am using:


DECLARE @currentfilename nvarchar(max)
DECLARE @logvalue        nvarchar(1000)
DECLARE @oldfilename     nvarchar(max)
DECLARE @oldlogId        smallint
DECLARE @olderfilename     nvarchar(max)
DECLARE @olderlogId        smallint
DECLARE @endpos          smallint
DECLARE @startpos        smallint
DECLARE @diffpos         smallint

SELECT @currentfilename=CAST(value AS nvarchar(max))
  FROM fn_trace_getinfo(default)
 WHERE property = 2 -- Get the current trace file name

SET @endpos   = CHARINDEX('.trc',@currentfilename)
SET @startpos = CHARINDEX('log_',@currentfilename)
SET @diffpos  = @endpos-@startpos


SET @logvalue=SUBSTRING(SUBSTRING(@currentfilename,@startpos,@diffpos),1,10)
SET @oldlogId=SUBSTRING(SUBSTRING(@currentfilename,@startpos,@diffpos),5,4) - 1
SET @olderlogID=SUBSTRING(SUBSTRING(@currentfilename,@startpos,@diffpos),5,4) - 2

SET @oldfilename = 'log_'
                 + CAST(@oldlogId AS varchar(1000))
SET @olderfilename = 'log_'
                 + CAST(@olderlogId AS varchar(1000))

SET @oldfilename = REPLACE(@currentfilename,@logvalue,@oldfilename)
SET @olderfilename = REPLACE(@currentfilename,@logvalue,@olderfilename)

SELECT *
   FROM fn_trace_gettable(@currentfilename,DEFAULT) AS Trace
 WHERE Trace.ObjectType <> '21587' -- Ignoring the statistics
   AND TextData is not null
   AND (TextData like '%no_log%'
    OR TextData like 'DUMP%'
    OR TextData like '%truncate%')
   AND TextData not like 'SELECT%'

--SELECT *
--  FROM fn_trace_gettable(@oldfilename,DEFAULT) AS Trace
-- WHERE Trace.ObjectType <> '21587' -- Ignoring the statistics
--   AND TextData is not null
--   AND (TextData like '%no_log%'
--    OR TextData like 'DUMP%'
--    OR TextData like '%truncate%')
--   AND TextData not like 'SELECT%'
--
--SELECT *
--  FROM fn_trace_gettable(@olderfilename,DEFAULT) AS Trace
-- WHERE Trace.ObjectType <> '21587' -- Ignoring the statistics
--   AND (TextData like '%no_log%'
--    OR TextData like 'DUMP%'
--    OR TextData like '%truncate%')
--   AND TextData not like 'SELECT%'
--

No comments:

Post a Comment