|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
in full recovery mode but log keeps self-truncating
We have a dev database we keep in simple recovery mode. We restored a fresh
copy of the database from production, and it was set down to simple mode. The log file was only 500kb. We set it up to full recovery mode. We're hammering the server with deletes. The log file expanded to 1gb. We can watch the space_used rise to about 300gb - then it truncates back down again, as if it were still in simple. We set the mode using the SSMS. It shows as full. But the log file refuses to grow! There is no process pulling off backups - AFAIK! Can anybody enlighten me what is going on here? Thanks. Josh Sorry, space used rises to about 300mb, inside the 1gb, and then it truncates.
SQL Server 2005 sp2 plus hotfixes. Josh Show quoteHide quote "JRStern" wrote: > We have a dev database we keep in simple recovery mode. We restored a fresh > copy of the database from production, and it was set down to simple mode. > The log file was only 500kb. We set it up to full recovery mode. We're > hammering the server with deletes. The log file expanded to 1gb. We can > watch the space_used rise to about 300gb - then it truncates back down again, > as if it were still in simple. We set the mode using the SSMS. It shows as > full. But the log file refuses to grow! There is no process pulling off > backups - AFAIK! > > Can anybody enlighten me what is going on here? > > Thanks. > > Josh > Josh,
From your narrative, you restored, set to SIMPLE, then set to FULL. You did not mention doing a database backup after the set to FULL recovery model. If you have not backed up the database since setting it to FULL recovery, the transaction log is meaningless in terms of recovery. If I understand correctly, SQL Server will truncate periodically since there is not starting point (the full backup) to which the log can be applied. Once you have done a full backup you should see the normal behaviour. RLF Show quoteHide quote "JRStern" <JRSt***@discussions.microsoft.com> wrote in message news:456BEDF1-8CB7-4F41-8122-7C44B86A3F28@microsoft.com... > Sorry, space used rises to about 300mb, inside the 1gb, and then it > truncates. > > SQL Server 2005 sp2 plus hotfixes. > > Josh > > > "JRStern" wrote: > >> We have a dev database we keep in simple recovery mode. We restored a >> fresh >> copy of the database from production, and it was set down to simple mode. >> The log file was only 500kb. We set it up to full recovery mode. We're >> hammering the server with deletes. The log file expanded to 1gb. We can >> watch the space_used rise to about 300gb - then it truncates back down >> again, >> as if it were still in simple. We set the mode using the SSMS. It shows >> as >> full. But the log file refuses to grow! There is no process pulling off >> backups - AFAIK! >> >> Can anybody enlighten me what is going on here? >> >> Thanks. >> >> Josh >> "Russell Fields" wrote: You know, that was suggested here, but nobody here was really aware that was > Josh, > Once you have done a full backup you should see the normal behaviour. a necessary step. Dang, so the bad move was setting it to simple, since the restored database was already in full, we have the backup already, and doing another backup (or restore!) will take hours. Thanks. Josh Have you backed up the database? Kalen just wrote a good piece about this
yesterday: http://sqlblog.com/blogs/kalen_delaney/archive/2008/11/30/when-is-full-recov ery-not-really-full-recovery.aspx On 12/2/08 5:21 PM, in article 4F4CAFD9-35BF-4163-8DDE-35E36F4EE***@microsoft.com, "JRStern" <JRSt***@discussions.microsoft.com> wrote: Show quoteHide quote > We have a dev database we keep in simple recovery mode. We restored a fresh > copy of the database from production, and it was set down to simple mode. > The log file was only 500kb. We set it up to full recovery mode. We're > hammering the server with deletes. The log file expanded to 1gb. We can > watch the space_used rise to about 300gb - then it truncates back down again, > as if it were still in simple. We set the mode using the SSMS. It shows as > full. But the log file refuses to grow! There is no process pulling off > backups - AFAIK! > > Can anybody enlighten me what is going on here? > > Thanks. > > Josh > Yep, well, swell, that's exactly where we are!
Is this new behavior in 2005? I could have sworn that back in the day, you could set it to full, and the log started growing immediately. Yes? Thanks. Josh Show quoteHide quote "Aaron Bertrand [SQL Server MVP]" wrote: > Have you backed up the database? Kalen just wrote a good piece about this > yesterday: > > http://sqlblog.com/blogs/kalen_delaney/archive/2008/11/30/when-is-full-recovery-not-really-full-recovery.aspx > > > > > On 12/2/08 5:21 PM, in article > 4F4CAFD9-35BF-4163-8DDE-35E36F4EE***@microsoft.com, "JRStern" > <JRSt***@discussions.microsoft.com> wrote: > > > We have a dev database we keep in simple recovery mode. We restored a fresh > > copy of the database from production, and it was set down to simple mode. > > The log file was only 500kb. We set it up to full recovery mode. We're > > hammering the server with deletes. The log file expanded to 1gb. We can > > watch the space_used rise to about 300gb - then it truncates back down again, > > as if it were still in simple. We set the mode using the SSMS. It shows as > > full. But the log file refuses to grow! There is no process pulling off > > backups - AFAIK! > > > > Can anybody enlighten me what is going on here? > > > > Thanks. > > > > Josh > > > > I don't remember behavior like that, sorry. That doesn't mean it was never
like that, but you can't take assumptions from one version to the next anyway... Show quoteHide quote > Yep, well, swell, that's exactly where we are! > > Is this new behavior in 2005? > > I could have sworn that back in the day, you could set it to full, and the > log started growing immediately. Yes? > > Thanks. > > Josh I believe it has been this way at least since Recovery Models were
introduced in SQL Server 2000. Show quoteHide quote "JRStern" <JRSt***@discussions.microsoft.com> wrote in message news:0510FD66-11DF-4A43-895B-200CB485C5AE@microsoft.com... > Yep, well, swell, that's exactly where we are! > > Is this new behavior in 2005? > > I could have sworn that back in the day, you could set it to full, and the > log started growing immediately. Yes? > > Thanks. > > Josh > > > > "Aaron Bertrand [SQL Server MVP]" wrote: > >> Have you backed up the database? Kalen just wrote a good piece about >> this >> yesterday: >> >> http://sqlblog.com/blogs/kalen_delaney/archive/2008/11/30/when-is-full-recovery-not-really-full-recovery.aspx >> >> >> >> >> On 12/2/08 5:21 PM, in article >> 4F4CAFD9-35BF-4163-8DDE-35E36F4EE***@microsoft.com, "JRStern" >> <JRSt***@discussions.microsoft.com> wrote: >> >> > We have a dev database we keep in simple recovery mode. We restored a >> > fresh >> > copy of the database from production, and it was set down to simple >> > mode. >> > The log file was only 500kb. We set it up to full recovery mode. >> > We're >> > hammering the server with deletes. The log file expanded to 1gb. We >> > can >> > watch the space_used rise to about 300gb - then it truncates back down >> > again, >> > as if it were still in simple. We set the mode using the SSMS. It >> > shows as >> > full. But the log file refuses to grow! There is no process pulling >> > off >> > backups - AFAIK! >> > >> > Can anybody enlighten me what is going on here? >> > >> > Thanks. >> > >> > Josh >> > >> >> > Is this new behavior in 2005? Since the new architecture (7.0).-- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "JRStern" <JRSt***@discussions.microsoft.com> wrote in message news:0510FD66-11DF-4A43-895B-200CB485C5AE@microsoft.com... > Yep, well, swell, that's exactly where we are! > > Is this new behavior in 2005? > > I could have sworn that back in the day, you could set it to full, > and the > log started growing immediately. Yes? > > Thanks. > > Josh > > > > "Aaron Bertrand [SQL Server MVP]" wrote: > >> Have you backed up the database? Kalen just wrote a good piece >> about this >> yesterday: >> >> http://sqlblog.com/blogs/kalen_delaney/archive/2008/11/30/when-is-full-recovery-not-really-full-recovery.aspx >> >> >> >> >> On 12/2/08 5:21 PM, in article >> 4F4CAFD9-35BF-4163-8DDE-35E36F4EE***@microsoft.com, "JRStern" >> <JRSt***@discussions.microsoft.com> wrote: >> >> > We have a dev database we keep in simple recovery mode. We >> > restored a fresh >> > copy of the database from production, and it was set down to >> > simple mode. >> > The log file was only 500kb. We set it up to full recovery mode. >> > We're >> > hammering the server with deletes. The log file expanded to 1gb. >> > We can >> > watch the space_used rise to about 300gb - then it truncates back >> > down again, >> > as if it were still in simple. We set the mode using the SSMS. >> > It shows as >> > full. But the log file refuses to grow! There is no process >> > pulling off >> > backups - AFAIK! >> > >> > Can anybody enlighten me what is going on here? >> > >> > Thanks. >> > >> > Josh >> > >> >> That's about the last time I had any real admin responsibilities for SQL
Server, good ol' 6.5 days. Time to update the notes ... .... though some local admins were not really aware of it, either, with perhaps less excuse. Learning is a lifetime job - and as Aaron pointed out, ya never know about the new (!) versions! Thanks. Josh Show quoteHide quote "Tibor Karaszi" wrote: > > Is this new behavior in 2005? > > Since the new architecture (7.0). > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > "JRStern" <JRSt***@discussions.microsoft.com> wrote in message > news:0510FD66-11DF-4A43-895B-200CB485C5AE@microsoft.com... > > Yep, well, swell, that's exactly where we are! > > > > Is this new behavior in 2005? > > > > I could have sworn that back in the day, you could set it to full, > > and the > > log started growing immediately. Yes? > > > > Thanks. > > > > Josh > > > > > > > > "Aaron Bertrand [SQL Server MVP]" wrote: > > > >> Have you backed up the database? Kalen just wrote a good piece > >> about this > >> yesterday: > >> > >> http://sqlblog.com/blogs/kalen_delaney/archive/2008/11/30/when-is-full-recovery-not-really-full-recovery.aspx > >> > >> > >> > >> > >> On 12/2/08 5:21 PM, in article > >> 4F4CAFD9-35BF-4163-8DDE-35E36F4EE***@microsoft.com, "JRStern" > >> <JRSt***@discussions.microsoft.com> wrote: > >> > >> > We have a dev database we keep in simple recovery mode. We > >> > restored a fresh > >> > copy of the database from production, and it was set down to > >> > simple mode. > >> > The log file was only 500kb. We set it up to full recovery mode. > >> > We're > >> > hammering the server with deletes. The log file expanded to 1gb. > >> > We can > >> > watch the space_used rise to about 300gb - then it truncates back > >> > down again, > >> > as if it were still in simple. We set the mode using the SSMS. > >> > It shows as > >> > full. But the log file refuses to grow! There is no process > >> > pulling off > >> > backups - AFAIK! > >> > > >> > Can anybody enlighten me what is going on here? > >> > > >> > Thanks. > >> > > >> > Josh > >> > > >> > >> > >  
Other interesting topics
Server Disk Space
Max server memory being ignored Re-attaching database Consolidate datafiles to one drive SQL 2k5-32bit >SQL 2k5-64bit Cumulative package 2 for SQL 2008 Maintenence Cleanup Task does not delete backup files How to do bulk update in SQL Server 2005. Taking database offline taking long time Re: Odd Identity Behavior in Enterprise Manager |
|||||||||||||||||||||||
