分析数据库占用大空间的记录

--分析目前数据库中表数据最大的情况
select 'union select '''+name+''' as name,count(*) as num from '+name From sysobjects where type ='U'


--复制查询
--select 'select * From '+name from 
select name,num from (
select 'MStracer_tokens' as name,count(*) as num from MStracer_tokens
union select 'MStracer_history' as name,count(*) as num from MStracer_history
union select 'MSarticles' as name,count(*) as num from MSarticles
union select 'MSreplication_monitordata' as name,count(*) as num from MSreplication_monitordata
union select 'MSsync_states' as name,count(*) as num from MSsync_states
union select 'MSsubscriptions' as name,count(*) as num from MSsubscriptions
union select 'MSmerge_subscriptions' as name,count(*) as num from MSmerge_subscriptions
union select 'MSrepl_transactions' as name,count(*) as num from MSrepl_transactions
union select 'MSrepl_commands' as name,count(*) as num from MSrepl_commands
union select 'MSrepl_originators' as name,count(*) as num from MSrepl_originators
union select 'MSsubscriber_info' as name,count(*) as num from MSsubscriber_info
union select 'MSsubscriber_schedule' as name,count(*) as num from MSsubscriber_schedule
union select 'MSsnapshot_history' as name,count(*) as num from MSsnapshot_history
union select 'MSlogreader_history' as name,count(*) as num from MSlogreader_history
union select 'MSdistribution_history' as name,count(*) as num from MSdistribution_history
union select 'MSmerge_sessions' as name,count(*) as num from MSmerge_sessions
union select 'UIProperties' as name,count(*) as num from UIProperties
union select 'MSmerge_articlehistory' as name,count(*) as num from MSmerge_articlehistory
union select 'MSrepl_errors' as name,count(*) as num from MSrepl_errors
union select 'MSmerge_history' as name,count(*) as num from MSmerge_history
union select 'MSmerge_identity_range_allocations' as name,count(*) as num from MSmerge_identity_range_allocations
union select 'MSsnapshot_agents' as name,count(*) as num from MSsnapshot_agents
union select 'MSlogreader_agents' as name,count(*) as num from MSlogreader_agents
union select 'MSdistribution_agents' as name,count(*) as num from MSdistribution_agents
union select 'MSmerge_agents' as name,count(*) as num from MSmerge_agents
union select 'MSrepl_identity_range' as name,count(*) as num from MSrepl_identity_range
union select 'MSpublication_access' as name,count(*) as num from MSpublication_access
union select 'MSqreader_agents' as name,count(*) as num from MSqreader_agents
union select 'MSqreader_history' as name,count(*) as num from MSqreader_history
union select 'MSrepl_backup_lsns' as name,count(*) as num from MSrepl_backup_lsns
union select 'MSpublicationthresholds' as name,count(*) as num from MSpublicationthresholds
union select 'IHpublishers' as name,count(*) as num from IHpublishers
union select 'IHpublishertables' as name,count(*) as num from IHpublishertables
union select 'IHarticles' as name,count(*) as num from IHarticles
union select 'IHpublishercolumns' as name,count(*) as num from IHpublishercolumns
union select 'IHcolumns' as name,count(*) as num from IHcolumns
union select 'IHindextypes' as name,count(*) as num from IHindextypes
union select 'IHpublisherindexes' as name,count(*) as num from IHpublisherindexes
union select 'IHpublishercolumnindexes' as name,count(*) as num from IHpublishercolumnindexes
union select 'IHpublications' as name,count(*) as num from IHpublications
union select 'IHconstrainttypes' as name,count(*) as num from IHconstrainttypes
union select 'IHpublisherconstraints' as name,count(*) as num from IHpublisherconstraints
union select 'IHpublishercolumnconstraints' as name,count(*) as num from IHpublishercolumnconstraints
union select 'IHsubscriptions' as name,count(*) as num from IHsubscriptions
union select 'sysschemaarticles' as name,count(*) as num from sysschemaarticles
union select 'MScached_peer_lsns' as name,count(*) as num from MScached_peer_lsns
union select 'MSrepl_version' as name,count(*) as num from MSrepl_version
union select 'MSpublisher_databases' as name,count(*) as num from MSpublisher_databases
union select 'MSpublications' as name,count(*) as num from MSpublications
) as a  where num >0


--查看表中数据
select * From IHconstrainttypes
select * From IHindextypes
select * From MSdistribution_history
select * From MSpublicationthresholds
select * From MSrepl_backup_lsns
select * From MSrepl_commands
select * From MSrepl_transactions
select * From MSrepl_version
select * From MSreplication_monitordata
select * From MSsync_states
select * From MStracer_history
select * From MStracer_tokens


--查看数据表所占空间
exec sp_spaceused 'MStracer_tokens',true
exec sp_spaceused 'MStracer_history',true
exec sp_spaceused 'MSarticles',true
exec sp_spaceused 'MSreplication_monitordata',true
exec sp_spaceused 'MSsync_states',true
exec sp_spaceused 'MSsubscriptions',true
exec sp_spaceused 'MSmerge_subscriptions',true
exec sp_spaceused 'MSrepl_transactions',true
exec sp_spaceused 'MSrepl_commands',true
exec sp_spaceused 'MSrepl_originators',true
exec sp_spaceused 'MSsubscriber_info',true
exec sp_spaceused 'MSsubscriber_schedule',true
exec sp_spaceused 'MSsnapshot_history',true
exec sp_spaceused 'MSlogreader_history',true
exec sp_spaceused 'MSdistribution_history',true
exec sp_spaceused 'MSmerge_sessions',true
exec sp_spaceused 'UIProperties',true
exec sp_spaceused 'MSmerge_articlehistory',true
exec sp_spaceused 'MSrepl_errors',true
exec sp_spaceused 'MSmerge_history',true
exec sp_spaceused 'MSmerge_identity_range_allocations',true
exec sp_spaceused 'MSsnapshot_agents',true
exec sp_spaceused 'MSlogreader_agents',true
exec sp_spaceused 'MSdistribution_agents',true
exec sp_spaceused 'MSmerge_agents',true
exec sp_spaceused 'MSrepl_identity_range',true
exec sp_spaceused 'MSpublication_access',true
exec sp_spaceused 'MSqreader_agents',true
exec sp_spaceused 'MSqreader_history',true
exec sp_spaceused 'MSrepl_backup_lsns',true
exec sp_spaceused 'MSpublicationthresholds',true
exec sp_spaceused 'IHpublishers',true
exec sp_spaceused 'IHpublishertables',true
exec sp_spaceused 'IHarticles',true
exec sp_spaceused 'IHpublishercolumns',true
exec sp_spaceused 'IHcolumns',true
exec sp_spaceused 'IHindextypes',true
exec sp_spaceused 'IHpublisherindexes',true
exec sp_spaceused 'IHpublishercolumnindexes',true
exec sp_spaceused 'IHpublications',true
exec sp_spaceused 'IHconstrainttypes',true
exec sp_spaceused 'IHpublisherconstraints',true
exec sp_spaceused 'IHpublishercolumnconstraints',true
exec sp_spaceused 'IHsubscriptions',true
exec sp_spaceused 'sysschemaarticles',true
exec sp_spaceused 'MScached_peer_lsns',true
exec sp_spaceused 'MSrepl_version',true
exec sp_spaceused 'MSpublisher_databases',true
exec sp_spaceused 'MSpublications',true