use distribution go select distinct @@SERVERNAME as Distributor , db_name(db_id()) as Distributor_DB , srv.srvname publication_server , a.publisher_db , p.publication publication_name , a.article , a.destination_object , p.retention , ss.srvname subscription_server , s.subscriber_db from MSArticles a join MSpublications p on a.publication_id = p.publication_id join MSsubscriptions s on p.publication_id = s.publication_id join master..sysservers ss on s.subscriber_id = ss.srvid join master..sysservers srv on srv.srvid = p.publisher_id join MSdistribution_agents da on da.publisher_id = p.publisher_id and da.subscriber_id = s.subscriber_id ORDER BY p.retention
Archive for March, 2012
[replication] all articles and server information
Posted by Simon Cho on 03/13/2012
Posted in Common | Tagged: Replication | Leave a Comment »
Size 1 day backup size
Posted by Simon Cho on 03/08/2012
3rd part tool isn’t working well.
ex) Litespeed
SQL server 2000-2008R2 working fine.
Sum(Calculate 1 day backup set including transaction log)
use msdb --select top 10 * from backupfile --select top 10 * from backupmediaset --select top 10 * from backupmediafamily declare @sql nvarchar(4000) if exists(select OBJECT_NAME(id) from syscolumns where name='compressed_backup_size') begin set @sql = ' select top 1 @@servername as servername , case when sum(compressed_backup_size)convert(varchar(8),getdate()-30,112) group by convert(varchar(8), backup_start_date, 112) order by sum(backup_size) desc ' end else begin set @sql = ' select top 1 @@servername as servername, ''N'' compressed_backup , convert(numeric(10,2),sum(backup_size/1024/1024/1024.0)) as [backup_size(GB)] from msdb.dbo.backupset with(nolock) where backup_start_date>convert(varchar(8),getdate()-30,112) group by convert(varchar(8), backup_start_date, 112) order by sum(backup_size) desc ' end exec(@sql)
Posted in Common | Leave a Comment »