Detaching a database and CHECKPOINT

Hi,
can someone confirm that SQL Server 2000 (SP3a) ALWAYS does a CHECKPOINT
upon detaching a database, so that the MDF/NDF files are "self-supporting"?
That would mean I can delete/rename the LDF file and have it recreated by
SQL
Server upon attach.
Of course I can checkpoint manually, but I'd expect the detach procedure to
that automatically for me (after prohibiting any new txns).
I'm pretty sure for SQL Server 2005 I have read such a statement in either
the official BOL or an article (pointers appreciated, though) but for SQL2K
I have have not found such information...
Thanx,
HansAs far as I know, once a database is cleanly detached using sp_detach_db,
then it can be attached without a log file. It has always worked for me.
There's an undocumented trace flag 3502, when turned on, it adds an entry to
the SQL Server error log, everytime a checkpoint occurs in any database. I
used this trace flag and found that a check point occured everytime I tried
to detach a database.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Hans Dingemans" <hans_dingemans@.hotmail.com> wrote in message
news:%23V$CtDihGHA.896@.TK2MSFTNGP02.phx.gbl...
Hi,
can someone confirm that SQL Server 2000 (SP3a) ALWAYS does a CHECKPOINT
upon detaching a database, so that the MDF/NDF files are "self-supporting"?
That would mean I can delete/rename the LDF file and have it recreated by
SQL
Server upon attach.
Of course I can checkpoint manually, but I'd expect the detach procedure to
that automatically for me (after prohibiting any new txns).
I'm pretty sure for SQL Server 2005 I have read such a statement in either
the official BOL or an article (pointers appreciated, though) but for SQL2K
I have have not found such information...
Thanx,
Hans|||>>> trace flag 3502
Ah, that's the kind of information I was looking for. This indeed helps a
lot. Thanx!
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%239otSzjhGHA.1276@.TK2MSFTNGP03.phx.gbl...
> As far as I know, once a database is cleanly detached using sp_detach_db,
> then it can be attached without a log file. It has always worked for me.
> There's an undocumented trace flag 3502, when turned on, it adds an entry
> to
> the SQL Server error log, everytime a checkpoint occurs in any database. I
> used this trace flag and found that a check point occured everytime I
> tried
> to detach a database.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Hans Dingemans" <hans_dingemans@.hotmail.com> wrote in message
> news:%23V$CtDihGHA.896@.TK2MSFTNGP02.phx.gbl...
> Hi,
> can someone confirm that SQL Server 2000 (SP3a) ALWAYS does a CHECKPOINT
> upon detaching a database, so that the MDF/NDF files are
> "self-supporting"?
> That would mean I can delete/rename the LDF file and have it recreated by
> SQL
> Server upon attach.
> Of course I can checkpoint manually, but I'd expect the detach procedure
> to
> that automatically for me (after prohibiting any new txns).
> I'm pretty sure for SQL Server 2005 I have read such a statement in either
> the official BOL or an article (pointers appreciated, though) but for
> SQL2K
> I have have not found such information...
> Thanx,
> Hans
>
>