SQL Server数据库巡检脚本(二)
][filegroup],f.is_default
from sys.database_files df join sys.filegroups f
on df.data_space_id = f.data_space_id
--26.查看数据库中所有表的条数
select b.name as tablename ,
a.rowcnt as datacount
from sysindexes a ,
sysobjects b
where a.id = b.id
and a.indid < 2
and objectproperty(b.id, 'IsMSShipped') = 0
--27.得到最耗时的前10条T-SQL语句
;with maco as
(
select top 10
plan_handle,
sum(total_worker_time) as total_worker_time ,
sum(execution_count) as execution_count ,
count(1) as sql_count
from sys.dm_exec_query_stats group by plan_handle
order by sum(total_worker_time) desc
)
select t.text ,
a.total_worker_time ,
a.execution_count ,
a.sql_count
from maco a
cross apply sys.dm_exec_sql_text(plan_handle) t
--28. 查看SQL Server的实际内存占用
select * from sysperfinfo where counter_name like '%Memory%'
--29.显示所有数据库的日志空间信息
dbcc sqlperf(logspace)
--30.收缩数据库
dbcc shrinkdatabase(databaseName)