January 12, 2016 at 9:28 am
I am trying to figure out a way to tell what LSN is associated with the creation of a database snapshot.
sys.master_files seems useless and sys.database_files (in the snapshot ) is useless too.
On MSSQL Server Internals it says:
... The snapshot reflects when the CREATE DATABASE command is issued - that is, when the creation commences.
SQL server checkpoints the source database and records a synchronization LSN in the source database.
Is that checkpoint LSN the one that I should care about? I see no other LSN reported by fn_dblog.
Is that LSN metadata queryable by any other means?
* Noel
January 12, 2016 at 9:49 am
Interesting question. If I create a snapshot in a clean db/log, I get only 3 log records. They are the
LOP_BEGIN_CKPT
LOP_XACT_CKPT
LOP_END_CKPT
I'm guessing it's one of these, likely the end, but I'll check with other people.
May I ask why you want to know? Is there something you're trying to pin down in terms of timing?
January 12, 2016 at 11:00 am
FWIW: I do get those three records too.
We are trying to create a few ways to apply delayed transactions to some remote servers.
Database snapshots are "fast" to create and easy to query (we have business use cases to protect against)
Knowing the LSN number at which the snapshot was created is just a part of the puzzle.
* Noel
January 12, 2016 at 2:12 pm
I was told it was in the boot page for the snapshot (or db)
DBCC TRACEON(3604);
DBCC PAGE(0,1,9,3);
January 13, 2016 at 9:07 am
This might be easier to consume. Wasn't aware of it myself until Argenis Fernandez mentioned it
DBCC DBTABLE
January 14, 2016 at 8:14 am
As a feedback and for completeness we ended up using:
DBCC DBINFO(DATABASENAME) WITH TABLERESULTS;
Thank you very much for pointing us in the right direction
* Noel
January 14, 2016 at 9:38 am
Cheers. If it's an interesting solution, maybe you want to write about it? 😉
Send in a short piece on how this is helpful to you or how you use it?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply