3

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.

  • Is there some kind of Job running some query for it? – Racer SQL Sep 14 '15 at 16:14
  • Why FULL recovery for ASPState? ASPState is only used for current session state data that's only good for currently active sessions so one generally uses the SIMPLE recovery model for this database. – Dan Guzman Sep 15 '15 at 1:15
4

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;
|improve this answer|||||
  • Thanks for the information. I'll create a backup plan for them now in SQL Server, looks like from your query there that the DB backups are being done by a system process. – J Hays Sep 14 '15 at 18:57
  • Glad to help! Note: If the "LoginName" from my snippet matches the account your SQL Agent is running under then chances are these mystery backups are being done in one of the scheduled and enabled jobs (and not some mystery person or process). There are lots of scripts via Google to parse through job information using SQL to hunt something down if there are too many to review manually in SSMS. – Todd Harmon Sep 14 '15 at 19:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.