Re: Simple recovery model + checkpoint = no need to save .ldf file?

From: Peter Yeoh (nospam_at_nospam.com)
Date: 08/04/04 

Date: Wed, 4 Aug 2004 09:13:40 +0800

>From Books Online,

'The detached files remain and can be reattached using sp_attach_db or
sp_attach_single_file_db. The files can also be moved to another server and
attached.'

This implies that sp_detach_db rolls back all uncommitted transactions and
writes all changes to the data files before the database is detached.
Hence, you do not need the log file as sp_attach_single_file_db can be used
to attach only the data file. This has been the experience in my case so
far.

Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Get MiniSQLBackup Lite, free!

"jwf" <jwf@discussions.microsoft.com> wrote in message
news:8AA7ECF5-CDDB-45AC-BCBD-0947D941829D@microsoft.com...
> If I have a sql server 2000 database using the simple recovery model and
then issue a checkpoint command right before I sp_detach_db, do I need to
even save the .ldf file at that point?
>
> Seems like in this case the log file is truncated anyway and upon an
attach there would be no recovery needed so later when I want to attach the
database again I could simply use sp_attach_single_file_db (we only use a
single .mdf and single .ldf file).
>
> Other than the obvious drawbacks of the simple vs. full recovery models,
is there any other issue I'm not grasping with this?
>
> From reading BOL it seems like there might be a problem with this. Even
though the log is "truncated", it sounds like there could still be committed
data from the start of a logical log section to the checkpoint LSN that
would be lost?? Or is this not the case for simple recovery mode?
>
> Our application needs are similar to the simplicity of moving around
MS-Access database files, but the functionality of SQL Server/MSDE 2000 when
the database is live.

Relevant Pages

  • Re: Exchange off line, and ESEUTIL error
    ... our Exchange 2003 server crashed. ... Anyway, we tried bouncing the Exchange server, and it just never seemed ... It seems that there are some database issues. ... we think that we know which log file hasn't been ...
    (microsoft.public.exchange.admin)
  • Re: Will this shrinkfile method work?
    ... There is no need to change the recovery model at all. ... If the active VLF is near the end of the log file it may take a little ... while for it to shrink but it will. ... Right click the database, choose TASKS - SHRINK - DATABASE ...
    (microsoft.public.sqlserver.setup)
  • Re: Log file growing very large??
    ... > BULK_LOGGED.Please change the recovery model for the database to ... You could truncate the transaction ... > Now execute the below command to see log file size and usage. ...
    (microsoft.public.sqlserver.msde)
  • Re: Stanby cannot be moved forward
    ... It very much looks like by opening the database you have switched the ... a log file. ... A backup is taken from production server to backup server. ...
    (comp.databases.oracle.server)
  • Re: Please evaluate this approach to shrinking log files
    ... A common cause of unruly log files is that the database is in the FULL ... recovery model but regular transaction log backups are not scheduled. ... IMHO, log file ...
    (comp.databases.ms-sqlserver)
Loading