SQL Server Backup – How is My Database Being Backed Up?

backupsql servertransaction-log

I'm using SQL Server 2008 R2. Someone else set everything up. I encountered an error today: Autogrow of file 'ASPState_log' in database 'ASPState' was cancelled by user or timed out after 1748 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.

This was a very large transaction log (much bigger than the table) trying to grow and as a result it shutdown our website. The recovery model is set to Full but the logs are not being backed up and truncated. But somehow the database is being backed up nightly as there is a timestamp under Last DB Backup. However, under Management there are no maintenance plans…so I'm not sure how the DB is getting backed up nightly unless there is an outside task or server doing it (which I couldn't find).

Should I do a full backup including logs and then truncate them? And where can I find how I am currently being backed up? Thanks, my first question.

Best Answer

In general you should have full backups set on one schedule, and transaction log backups set on another (more frequent) schedule, so yes do both of these as soon as you can.

The issue you will run into is if another process is performing backups without "COPYONLY", as this will through off sequencing (making a full point in time restore impossible unless you can track down the location of the mystery backups).

The code snippet below will search the default trace for all BACKUP activity and should allow you to track things down.

SET NOCOUNT ON;
--this version reads all trace files
declare @path  nvarchar(100)
set @path = (select top 1 [path] from sys.traces where is_default = 1)
set @path = (select reverse(right(reverse(@path), (Len(@path) - (PATIndex('%[_]%', reverse(@path)))))) + '.trc')


SELECT  STE.name AS EventClassName,
       ST.StartTime ,
        ST.LoginName ,
        ST.HostName ,
        ST.ApplicationName ,
        ST.TextData
FROM    sys.fn_trace_gettable((@path), DEFAULT) ST
INNER JOIN sys.trace_events STE ON ST.EventClass = STE.trace_event_id
WHERE   TextData LIKE '%backup%' AND SPID <> @@SPID
ORDER BY StartTime DESC;
Related Question