监控数据库运行情况（三）

[sql] view plaincopyprint?
--1.查看数据库版本信息   
select @@version  
--2.查看所有数据库名称及大小   
select sp_helpdb  
--3.查看数据库所在机器的操作系统参数   
exec master..xp_msver  
--4.查看数据库启动的参数   
exec sp_configure  
--5.查看数据库启动时间   
select convert(varchar(30),login_time,120)  
from master..sysprocesses where spid=1  
--6.查看数据库服务器名   
select 'Server Name:'+ltrim(@@servername)  
--7.查看数据库实例名   
select 'Instance:'+ltrim(@@servicename)   
--8.数据库的磁盘空间呢使用信息   
exec sp_spaceused  
--9.日志文件大小及使用情况   
dbcc sqlperf(logspace)  
--10.表的磁盘空间使用信息   
exec sp_spaceused 'tablename'  
--11.获取磁盘读写情况   
select   
@@total_read [读取磁盘次数],  
@@total_write [写入磁盘次数],  
@@total_errors [磁盘写入错误数],  
getdate() [当前时间]  
--12.获取I/O工作情况   
select @@io_busy,  
@@timeticks [每个时钟周期对应的微秒数],  
@@io_busy*@@timeticks [I/O操作毫秒数],  
getdate() [当前时间]  
--13.查看CPU活动及工作情况   
select  
@@cpu_busy,  
@@timeticks [每个时钟周期对应的微秒数],  
@@cpu_busy*cast(@@timeticks as float)/1000 [CPU工作时间(秒)],  
@@idle*cast(@@timeticks as float)/1000 [CPU空闲时间(秒)],  
getdate() [当前时间]  
--14.检查锁与等待   
exec sp_lock  
--15.检查死锁   
exec sp_who_lock --自己写个存储过程即可   
/*  
create procedure sp_who_lock  
as  
begin  
    declare @spid int,@bl int,  
    @intTransactionCountOnEntry int,  
    @intRowcount int,  
    @intCountProperties int,  
    @intCounter int  
    create table #tmp_lock_who (id int identity(1,1),spid smallint,bl smallint)  
    IF @@ERROR<>0 RETURN @@ERROR  
    insert into #tmp_lock_who(spid,bl) select 0 ,blocked  
    from (select * from sysprocesses where blocked>0 ) a   
    where not exists(select * from (select * from sysprocesses where blocked>0 ) b   
    where a.blocked=spid)  
    union select spid,blocked from sysprocesses where blocked>0  
    IF @@ERROR<>0 RETURN @@ERROR  
        -- 找到临时表的记录数   
        select @intCountProperties = Count(*),@intCounter = 1  
        from #tmp_lock_who  
    IF @@ERROR<>0 RETURN @@ERROR  
    if @intCountProperties=0  
    select '现在没有阻塞和死锁信息' as message  
    -- 循环开始   
    while @intCounter <= @intCountProperties  
    begin  
    -- 取第一条记录   
    select @spid = spid,@bl = bl  
    from #tmp_lock_who where id = @intCounter   
    begin  
    if @spid =0   
        select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'  
    else  
        select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'  
    DBCC INPUTBUFFER (@bl )  
    end  
    -- 循环指针下移   
    set @intCounter = @intCounter + 1  
    end  
    drop table #tmp_lock_who  
    return 0  
end  
*/  
  
  
--16.用户和进程信息   
exec sp_who  
exec sp_who2  
  
  
--17.活动用户和进程的信息   
exec sp_who 'active'  
  
  
--18.查看进程中正在执行的SQL   
dbcc inputbuffer(进程号)  
exec sp_who3  
  
  
--19.查看所有数据库用户登录信息   
exec sp_helplogins   
  
  
--20.查看所有数据库用户所属的角色信息   
exec sp_helpsrvrolemember  
  
  
--21.查看链接服务器   
exec sp_helplinkedsrvlogin  
  
  
--22.查看远端数据库用户登录信息   
exec sp_helpremotelogin  
   
--23.获取网络数据包统计信息   
select   
@@pack_received [输入数据包数量],  
@@pack_sent [输出数据包数量],  
@@packet_errors [错误包数量],  
getdate() [当前时间]  
  
  
--24.检查数据库中的所有对象的分配和机构完整性是否存在错误   
dbcc checkdb  
  
  
--25.查询文件组和文件   
select   
    df.[name],df.physical_name,df.[size],df.growth,   
    f.[name][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)