Backup log WITH NO_LOG - change recovery model to SIMPLE?

Hi,
I use FULL recovery model, SQL 2005. Is it possible this type of
backup ""change"" my recovery model to SIMPLE. I noticed when I
executed this:
BACKUP DATABSE db_name
TO DISK = 'path'
BACKUP LOG db_name WITH NO_LOG
DBCC SHRINKFILE ('db_name_log', truncateonly)
Now transact log grow very, very, very slow (this is symptom simple
model). But when executed this (different order):
BACKUP LOG db_name WITH NO_LOG
DBCC SHRINKFILE ('db_name_log', truncateonly)
BACKUP DATABSE db_name
TO DISK = 'path'
transact log grow normally
Would somebody explain me this, and tell me first statement change
(theoretically) my model to SIMPLE?
--
RegardsHi
First , read this article
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
<anxcomp@.gmail.com> wrote in message
news:1187684709.945537.84260@.g4g2000hsf.googlegroups.com...
> Hi,
> I use FULL recovery model, SQL 2005. Is it possible this type of
> backup ""change"" my recovery model to SIMPLE. I noticed when I
> executed this:
> BACKUP DATABSE db_name
> TO DISK = 'path'
> BACKUP LOG db_name WITH NO_LOG
> DBCC SHRINKFILE ('db_name_log', truncateonly)
>
> Now transact log grow very, very, very slow (this is symptom simple
> model). But when executed this (different order):
> BACKUP LOG db_name WITH NO_LOG
> DBCC SHRINKFILE ('db_name_log', truncateonly)
> BACKUP DATABSE db_name
> TO DISK = 'path'
> transact log grow normally
> Would somebody explain me this, and tell me first statement change
> (theoretically) my model to SIMPLE?
> --
> Regards
>|||This is expected. If you are in full mode and empty the log without actually doing a backup (which
is what TRUNCATE_ONLY and NO_LOG does), then subsequent real log backups would be useless. SQL
Server know this and in this situation, the database acts as if it is simple recovery (log is
auto-truncated).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<anxcomp@.gmail.com> wrote in message news:1187684709.945537.84260@.g4g2000hsf.googlegroups.com...
> Hi,
> I use FULL recovery model, SQL 2005. Is it possible this type of
> backup ""change"" my recovery model to SIMPLE. I noticed when I
> executed this:
> BACKUP DATABSE db_name
> TO DISK = 'path'
> BACKUP LOG db_name WITH NO_LOG
> DBCC SHRINKFILE ('db_name_log', truncateonly)
>
> Now transact log grow very, very, very slow (this is symptom simple
> model). But when executed this (different order):
> BACKUP LOG db_name WITH NO_LOG
> DBCC SHRINKFILE ('db_name_log', truncateonly)
> BACKUP DATABSE db_name
> TO DISK = 'path'
> transact log grow normally
> Would somebody explain me this, and tell me first statement change
> (theoretically) my model to SIMPLE?
> --
> Regards
>|||On Aug 21, 2:52 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> This is expected. If you are in full mode and empty the log without actually doing a backup (which
> is what TRUNCATE_ONLY and NO_LOG does), then subsequent real log backups would be useless. SQL
> Server know this and in this situation, the database acts as if it is simple recovery (log is
> auto-truncated).
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>
> <anxc...@.gmail.com> wrote in messagenews:1187684709.945537.84260@.g4g2000hsf.googlegroups.com...
> > Hi,
> > I use FULL recovery model, SQL 2005. Is it possible this type of
> > backup ""change"" my recovery model to SIMPLE. I noticed when I
> > executed this:
> > BACKUP DATABSE db_name
> > TO DISK = 'path'
> > BACKUP LOG db_name WITH NO_LOG
> > DBCC SHRINKFILE ('db_name_log', truncateonly)
> > Now transact log grow very, very, very slow (this is symptom simple
> > model). But when executed this (different order):
> > BACKUP LOG db_name WITH NO_LOG
> > DBCC SHRINKFILE ('db_name_log', truncateonly)
> > BACKUP DATABSE db_name
> > TO DISK = 'path'
> > transact log grow normally
> > Would somebody explain me this, and tell me first statement change
> > (theoretically) my model to SIMPLE?
> > --
> > Regards- Hide quoted text -
> - Show quoted text -
to change recovery model from full to simple use alter database
command for that database
alter database dbname set recovery=simple
http://msdn2.microsoft.com/en-us/library/aa275464(SQL.80).aspx
setting to simple recovery helps to minimize log file growth ,but note
you will not be able to do point in time recovery incase of failures
Thanks
VS|||On 21 Sie, 11:52, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> This is expected. If you are in full mode and empty the log without actually doing a backup (which
> is what TRUNCATE_ONLY and NO_LOG does), then subsequent real log backups would be useless. SQL
> Server know this and in this situation, the database acts as if it is simple recovery (log is
> auto-truncated).
So it is realy true, it change to SIMPLE mode, thanks.
Tibor I read your article ' Why you want to be restrictive with
shrink of database files', I understand I shouldn't use both
SHRINKFILE and SHRINKDATABASE command.
So, would you help me create good backup plan, which ENSURE me that I
shouldn't USE SHRINK* commands, please.
Main principles:
1.LOG can't grow so match (most important)
2 I can lost information max fifteen minutes back
3. I'd like use only full recovery model not SIMPLE
If you like it can be for example graphical plan - "Maintenance Plans"
on SQL 2005
Thank you
--
Regards|||> So it is realy true, it change to SIMPLE mode, thanks.
No, it doesn't change recovery model to simple. It puts the database in a state where it behaves the
same as in simple model. Important distinction.
> 1.LOG can't grow so match (most important)
OK. You handle this by doing frequent log backups since a lot backup will empty the ldf file(s).
> 2 I can lost information max fifteen minutes back
So you should do log backups at least in 15 minutes intervals. Perhaps every 10 minutes...
> 3. I'd like use only full recovery model not SIMPLE
So just keep the database in full recovery.
Above is very basic. Do a database (full) backup perhaps every day. And do a log backup every 10
minutes. This is easy thing to setup with the maint wizard.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<anxcomp@.gmail.com> wrote in message news:1187727440.811711.299920@.a39g2000hsc.googlegroups.com...
> On 21 Sie, 11:52, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> This is expected. If you are in full mode and empty the log without actually doing a backup
>> (which
>> is what TRUNCATE_ONLY and NO_LOG does), then subsequent real log backups would be useless. SQL
>> Server know this and in this situation, the database acts as if it is simple recovery (log is
>> auto-truncated).
> So it is realy true, it change to SIMPLE mode, thanks.
> Tibor I read your article ' Why you want to be restrictive with
> shrink of database files', I understand I shouldn't use both
> SHRINKFILE and SHRINKDATABASE command.
> So, would you help me create good backup plan, which ENSURE me that I
> shouldn't USE SHRINK* commands, please.
> Main principles:
> 1.LOG can't grow so match (most important)
> 2 I can lost information max fifteen minutes back
> 3. I'd like use only full recovery model not SIMPLE
> If you like it can be for example graphical plan - "Maintenance Plans"
> on SQL 2005
> Thank you
> --
> Regards
>|||> No, it doesn't change recovery model to simple. It puts the database in a state where it behaves the
> same as in simple model. Important distinction.
OK, I understand now.
> Above is very basic. Do a database (full) backup perhaps every day. And do a log backup every 10
> minutes. This is easy thing to setup with the maint wizard.
I've done this use Wizard and now I'm watching what happen with log :)
Thank you Tibor for all advices and other people too :)
--
Regards