Backup log WITH NO_LOG, change rec mod. to SIMPLE? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Backup log WITH NO_LOG, change rec mod. to SIMPLE?

Author

Topic

anxcomp
Starting Member

41 Posts

Posted - 2007-08-21 : 04:29:17

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,
anxcomp

ratheeshknair
Posting Yak Master

129 Posts

Posted - 2007-08-21 : 08:44:38

In simple recovery model the sql server issues checkpoints and commits the transactions by itself...u cant take a transaction log backup in simple recovery mode.

RKNAIR

ratheeshknair
Posting Yak Master

129 Posts

Posted - 2007-08-21 : 08:51:17

Syntax for shrinking log file
"dbcc shrinkfile(file_name,target_size,truncateonly)"

RKNAIR

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-21 : 22:27:08

Backing up log will not switch db to simple recovery model, and don't shrink log file if server doesn't have disk space issue. That's performance killer.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources