很多公司因为历史原因，需要下线SQLSERVER里的表或者库。在下线前需要排查确定这些表无人访问

下线步骤

1. 整理库表的最后访问时间（一般是最近一个月的）
2. 改掉程序中对这些复制订阅表的从库访问（如果有）
3. 在测试环境sp_rename 这些表，加上指定后缀(_dbadel)
4. 如果有问题回滚sp_rename
5. 保留2周后备份删除

获得表的最后访问时间

IF OBJECT_ID('tempdb.dbo.#tableused', 'U') IS NULL 
BEGIN 
CREATE TABLE #tableused
(
DBName VARCHAR(100) ,
TableName VARCHAR(100) ,
reads BIGINT ,
writes bigint,
last_system_seek DATETIME ,
last_user_seek DATETIME ,
last_user_scan DATETIME ,
last_user_lookup DATETIME ,
last_user_update DATETIME ,
tableCreateTime DATETIME
)
END 
EXEC sp_msforeachdb '
declare @dbname varchar(200);
select @dbname=''?''
if (@dbname not in (''system'') and db_id(@dbname)>4) and @dbname not like ''%dbo%''  --在这里排掉不需要统计的库
begin
execute
(
''
use ''+ @dbname+'';
insert into #tableused
select ''''''+ @dbname+'''''', 
obj.name AS TableName , 
sum(indUsage.user_seeks+indUsage.user_scans+indUsage.user_lookups) as  reads,
sum(indUsage.user_updates) as writes, 
max(indUsage.last_system_seek ) as last_system_seek , 
max(indUsage.last_user_seek)   as last_user_seek,
max(indUsage.last_user_scan)   as last_user_scan,
max(indUsage.last_user_lookup) as last_user_lookup,
max(indUsage.last_user_update  ) as last_user_update,
min(obj.create_date) as create_date
FROM ''+ @dbname + ''.sys.indexes AS ind 
INNER JOIN ''+ @dbname + ''.sys.objects AS obj ON ind.object_id = obj.object_id 
LEFT JOIN ''+ @dbname + ''.sys.dm_db_index_usage_stats indUsage ON ind.object_id = indUsage.object_id AND indUsage.database_id=DB_ID(''''''+ @dbname+'''''')
AND ind.index_id = indUsage.index_id 
WHERE obj.type <> ''''S'''' and is_ms_shipped=0  AND OBJECTPROPERTY(obj.object_id, ''''isusertable'''') = 1 

group by obj.name
''
)
end
'


SELECT *
FROM #tableused


DROP TABLE #tableused

建一个任务，用于每10分钟收集一次线上的访问情况

<tasktype>sql_to_sql</tasktype>

<from_server>group:sqlserver_onlineall</from_server>		<!--数据源，推荐用-->
<to_server>link:db_infostatus</to_server>			<!--目标实例-->

<step>							<!--可以多次定义的任务步骤-->
  <from_sqlstr>
    select login_name,s.host_name,c.client_net_address,COUNT(0) host_count from Sys.dm_exec_requests r with(nolock) 
right outer join Sys.dm_exec_sessions s  with(nolock) on r.session_id = s.session_id right outer join Sys.dm_exec_connections c  with(nolock)
on s.session_id = c.session_id where s.session_id >50 
and login_name not like '%\%'
group by host_name,login_name,client_net_address 
 </from_sqlstr>
      <to_sqlstr>insert into info_sqlserver_process
(addtime,linkname,login_name,host_name,ipstr,thead_counts) values ('{tasktime}','{linkname}','{0}','{1}','{2}',{3})
</to_sqlstr> 
 
</step>

<success></success>
<onerror></onerror>

建一个报表，用来展示收集到的数据

<!--每个报表都可以定义多个page用来展示和多个para用来接收用户输入-->


<page>
 <viewtype>table</viewtype>
  <title>按库汇总</title>
  <connstr>link:db_infostatus</connstr>
  <sqlstr><![CDATA[
select login_name,
sum(thead_counts) as conn_counts
 from info_sqlserver_process 
where login_name not in ('dba')
group by login_name
order by conn_counts desc 

]]>
  </sqlstr>
  <width>420px</width>
</page>

<page>
 <viewtype>table</viewtype>
  <title>按主机汇总</title>
  <connstr>link:db_infostatus</connstr>
  <sqlstr><![CDATA[
select 
concat(host_name,'(',ipstr,')')
 as hoststr,
sum(thead_counts) as conn_counts
 from info_sqlserver_process 
where login_name not in ('dba')
group by
 concat(host_name,'(',ipstr,')')
order by conn_counts desc 

]]>
  </sqlstr>
  <width>420px</width>
</page>


<page>
 <viewtype>table</viewtype>
  <connstr>link:db_infostatus</connstr>
  <sqlstr><![CDATA[
select concat(host_name,'(',ipstr,')') as hoststr,
login_name
 ,
sum(thead_counts) as tcount from info_sqlserver_process 
where login_name not in ('dba')
group by host_name,ipstr,
 login_name


]]>
  </sqlstr>
  <width>720px</width>
</page>