public LogSequenceNumber ReadMostRecentLsn(string serverName, string databaseName)
{
if (string.IsNullOrEmpty(serverName))
{
throw new ArgumentNullException(nameof(serverName));
}
if (string.IsNullOrEmpty(databaseName))
{
throw new ArgumentNullException(nameof(databaseName));
}
int serverId = ReadCaptureSystemCached(serverName);
int databaseId = ReadCaptureDatabaseCached(databaseName);
string sql = @"select top 1 CurrentLsn from CaptureEvent where CaptureSystemId = @CaptureSystemId and CaptureDatabaseId = @CaptureDatabaseId order by CaptureTime desc;";
SqlParameter serverParameter = new SqlParameter("CaptureSystemId", serverId);
SqlParameter databaseParameter = new SqlParameter("CaptureDatabaseId", databaseId);
object lastKnownLsn = ExecuteSqlScalar(sql, serverParameter, databaseParameter);
if (lastKnownLsn == null)
{
return(null);
}
LogSequenceNumber lsn = new LogSequenceNumber((decimal)lastKnownLsn);
return(lsn);
}
public static void CaptureDbccLogInfo(string sourceInstanceName, string sourceDatabaseName, string targetInstanceName, string targetDatabaseName)
{
using (CaptureDal captureDal = new CaptureDal(targetInstanceName, targetDatabaseName))
{
Logger.Write(LogLevel.Informational, "Reading most recent LSN.");
LogSequenceNumber lsn = captureDal.ReadMostRecentLsn(sourceInstanceName, sourceDatabaseName);
List <DbccLogInfoItem> dbccLogInfo;
using (LogStatsDal sourceSystemDal = new LogStatsDal(sourceInstanceName, sourceDatabaseName))
{
Logger.Write(LogLevel.Informational, "Reading LOGINFO.");
dbccLogInfo = sourceSystemDal.ReadDbccLogInfo(lsn);
}
Logger.Write(LogLevel.Informational, "Writing captured log information.");
captureDal.SaveDbccLoginfoCapture(dbccLogInfo);
}
}
public int CreateCaptureEvent(string serverName, string databaseName, DateTime captureTime, LogSequenceNumber currentLsn)
{
string sql = @"insert CaptureEvent (CaptureSystemId, CaptureDatabaseId, CaptureTime, CurrentLsn) values (@CaptureSystemId, @CaptureDatabaseId, @CaptureTime, @CurrentLsn); select cast(scope_identity() as int) ID;";
SqlParameter systemParameter = new SqlParameter("CaptureSystemId", ReadCaptureSystemCached(serverName));
SqlParameter databaseParameter = new SqlParameter("CaptureDatabaseId", ReadCaptureDatabaseCached(databaseName));
SqlParameter timeParameter = new SqlParameter("CaptureTime", captureTime);
timeParameter.SqlDbType = SqlDbType.DateTime2;
SqlParameter lsnParameter = new SqlParameter("CurrentLsn", currentLsn.ToDecimal());
int id = (int)ExecuteSqlScalar(sql, systemParameter, databaseParameter, timeParameter, lsnParameter);
return(id);
}
public List <DbccLogInfoItem> ReadDbccLogInfo(LogSequenceNumber lastKnownLsn, bool useLiteVersion = false, bool forceDbccLoginfo = false)
{
string sql;
SqlParameter[] parameters = null;
if (forceDbccLoginfo || _sqlVersion < _minDmvLogInfoVersion)
{
if (useLiteVersion)
{
sql = LogFileVisualizerResources.DbccLoginfoLite;
}
else
{
sql = LogFileVisualizerResources.DbccLoginfo;
SqlParameter lsnParameter = new SqlParameter("lastKnownLsn", GetParameterValue(lastKnownLsn?.ToString(LsnStringType.DecimalSeparated)));
parameters = new SqlParameter[1] {
lsnParameter
};
}
}
else
{
// Version >= 2016 SP2 and forceDbccLoginfo is false
sql = @"set deadlock_priority low;
select cast(file_id as int) FileId,
cast(vlf_size_mb * 1024 * 1024 as bigint) FileSize,
vlf_begin_offset StartOffset,
cast(vlf_sequence_number as int) FSeqNo,
vlf_active Active,
vlf_status Status,
cast(vlf_parity as tinyint) Parity,
vlf_create_lsn CreateLSN,
@@SERVERNAME ServerName,
db_name() DatabaseName,
sysdatetime() CaptureTime,
cast(null as nvarchar(50)) CurrentLsnHex
from sys.dm_db_log_info(null);";
}
using (DataTable table = ExecuteSqlOneResultset(sql, parameters))
{
List <DbccLogInfoItem> list = new List <DbccLogInfoItem>();
foreach (DataRow row in table.Rows)
{
DbccLogInfoItem item = new DbccLogInfoItem();
item.FileId = GetObjectValue <int>(row, "FileId");
item.FileSize = GetObjectValue <long>(row, "FileSize");
item.StartOffset = GetObjectValue <long>(row, "StartOffset");
item.VirtualLogFileNumber = GetObjectValue <int>(row, "FSeqNo");
item.Status = GetObjectValue <int>(row, "Status");
item.Parity = GetObjectValue <byte>(row, "Parity");
object createLsnObject = row["CreateLSN"];
Type createLsnType = createLsnObject.GetType();
if (createLsnType == typeof(decimal))
{
item.CreateLsn = GetObjectValue <decimal>(createLsnObject);
}
else if (createLsnType == typeof(string))
{
string createLsnTypeHexSeparated = GetObjectValue <string>(createLsnObject);
LogSequenceNumber createLsn = new LogSequenceNumber(createLsnTypeHexSeparated, LsnStringType.HexidecimalSeparated);
item.CreateLsn = createLsn.ToDecimal();
}
string recoveryUnitIdColumnName = "RecoveryUnitId";
if (table.Columns.Contains(recoveryUnitIdColumnName))
{
item.RecoveryUnitId = GetObjectValue <int>(row, recoveryUnitIdColumnName);
}
item.ServerName = GetObjectValue <string>(row, "ServerName");
item.DatabaseName = GetObjectValue <string>(row, "DatabaseName");
item.CaptureTime = GetObjectValue <DateTime>(row, "CaptureTime");
if (useLiteVersion == false)
{
item.LastKnownLsn = new LogSequenceNumber(GetObjectValue <string>(row, "CurrentLsnHex"), LsnStringType.HexidecimalSeparated);
}
list.Add(item);
}
return(list);
}
}
