Replication (Publisher Log File)

September 23, 2011 Leave a comment
  • With transactional replication you have a log reader that reads the log file on the pulisher.  Replication only gives you eyes into the distribution database (assuming the log reader has already put all transactions into the distribution database.  Here is a script you can run on the publisher to see how many transactions are in the publishers log file waiting to be picked up by the Log Reader.  This runs on the publisher (Good candidate to be set up as a reporting services report to be emailed to the DBA’s hourly)
declare @DatabaseName varchar(100)
SET @DatabaseName = '<PublisherDatabaseNameHere>'

    instance_name as DatabasName,
    cntr_value as Num_Pending_Trans,
        when cntr_value > 10000 
            then 'ALERT' 
        else ' ' 
    End as Status
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Repl. Pending Xacts'
and instance_name IN (@DatabaseName)
Categories: replication

Get Last Backup Date

August 10, 2011 1 comment

Here is a handy script I created that gives you the last backup date for all databases on a SQL instance.  This can be incorporated into powershell to get the last backup date from all servers in your enterprise

DECLARE @DBName varchar(100)
CREATE TABLE #DatabaseBackupDate
DatabaseName varchar(100),
BackupDate datetime,
DaysOld int
DECLARE c_Databases CURSOR FOR select distinct name from master.dbo.sysdatabases where name not in ('tempdb') order by name
open c_Databases
fetch c_Databases into @DBName
while (@@FETCH_STATUS = 0)
INSERT INTO #DatabaseBackupDate (DatabaseName) values(@DBName)

UPDATE #DatabaseBackupDate set BackupDate = backup_finish_date, DaysOld = DATEDIFF(day, backup_finish_date, getdate())
FROM msdb..backupset INNER JOIN #DatabaseBackupDate on msdb..backupset.database_name = #DatabaseBackupDate.DatabaseName
backup_set_id = (
SELECT MAX(backup_set_id)
FROM msdb..backupset
WHERE database_name = @DBName AND
[type] IN ('D','I') ) --Reports just Full or Differential, not log backups
fetch c_Databases into @DBName
select * from #DatabaseBackupDate
drop table #DatabaseBackupDate
close c_Databases
deallocate c_Databases
Categories: backup recovery

Welcome to my blog

I have just created my first blog for my love for the SQL Server technology.  I will be posting various SQL related topics, scripts, and links to useful information regarding SQL Server technology

Categories: Uncategorized