sqlserver的日常检查脚本归结

核心提示：FROM sysfiles f JOIN sysfilegroups g ON f.groupid = g.groupid' SELECT * FROM #output drop TABLE #outputprint ' 'print &#

FROM sysfiles f 

JOIN sysfilegroups g 

ON f.groupid = g.groupid' 

SELECT * FROM #output 

drop TABLE #output

print '                             '

print '                             '

print '-------------------------------------------------------'

print '28.Buffer Pool缓冲池里面修改过的页总数大小'

print '-------------------------------------------------------'

SELECT count(*) * 8/1024 as cached_pages_mb,

convert(varchar(5),convert(decimal(5,2),(100-1.0*(select count(*) from sys.dm_os_buffer_descriptors b 

where b.database_id=a.database_id and is_modified=0)/count(*)*100.0)))+'%'modified_percentage,

CASE database_id WHEN 32767 THEN 'ResourceDb'

ELSE db_name(database_id)

END AS Database_name

FROM sys.dm_os_buffer_descriptors a

GROUP BY db_name(database_id),database_id

ORDER BY cached_pages_mb DESC;

/*

如果一个数据库的大部分（超过80%）是修改过的，那么这个数据库写操作非常多。

反之如果这个比例接近0，那么该数据库的活动几乎是只读的。读写的比例对磁盘的安排是很重要的。

*/

print '                             '

print '                             '

print '-------------------------------------------------------'

print '29.查看 tempdb 大小和增长参数'

print '-------------------------------------------------------'

SELECT 

    name AS FileName, 

    size*1.0/128 AS FileSizeinMB,

    CASE max_size 

        WHEN 0 THEN 'Autogrowth is off.'

        WHEN -1 THEN 'Autogrowth is on.'

        ELSE 'Log file will grow to a maximum size of 2 TB.'

    END,

    growth AS 'GrowthValue',

    'GrowthIncrement' = 

        CASE

            WHEN growth = 0 THEN 'Size is fixed and will not grow.'

            WHEN growth > 0 AND is_percent_growth = 0 

                THEN 'Growth value is in 8-KB pages.'

            ELSE 'Growth value is a percentage.'

        END

FROM tempdb.sys.database_files;

GO

print '                             '