19th September 2015 SQL Saturday Denmark Questions.

What editions is replication in?

As per https://msdn.microsoft.com/en-us/library/cc645993.aspx#Replication replication is supported in Standard Edition, this does not include Oracle publishing or Peer to Peer transactional replication.

How can I read the transaction log?

To read the current logs:

SELECT * FROM fn_dblog (NULL, NULL)

The parameters to fn_dblog are StartLSN and EndLSN in 3 part decimal format

To read from a log backup:

SELECT * FROM
fn_dump_dblog (
NULL, NULL, N’DISK’, 1, N’D:\LogBackups\Justdave_Log1.bak’,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);

The parameters are:

  • Start LSN starting with 0x
  • End LSN starting with 0x
  • Device Type ‘DISK’ or ‘TAPE’ deffault DISK
  • Seqnum – used to indentify a backup within a backup device, usually 1
  • Filename of log backup on disk
  • the name of a backup file in the default backup directory for the SQL Server instance
  • The remaining parameters can be used to specify multiple media families in a media set used for a log backup i.e. the filenames of the remaining stripes if more than 1 stripe was used for the log backup

Warning: As per http://www.sqlskills.com/blogs/paul/using-fn_dblog-fn_dump_dblog-and-restoring-with-stopbeforemark-to-an-lsn/

There is a bug where using these functions to read log information will result in a new hidden SQLOS scheduler being created and up to three threads, which will not go away (and will not be reused) until a server restart. This is fixed in 2012 SP2 and 2014.

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s