sqlserver的日常检查脚本归结

核心提示：print ' 'print '---'print '20. 查看所有数据库大小、恢复模式等信息'print '---'SELECT substring (DatabaseName,0,12) as

print '                             '

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

print '20. 查看所有数据库大小、恢复模式等信息'

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

SELECT substring (DatabaseName,0,12) as DatabaseName,DataSize,LogSize,DataSize+LogSize AS TotalSize, Collation, RecoveryType,AutoClose,AutoShrink

FROM 

(SELECT DBID,

CASE Sum(size*8/1024) 

WHEN 0 THEN 1 

ELSE Sum(size*8/1024) 

END AS DataSize

FROM master..sysaltfiles

WHERE GroupID <> 0

GROUP BY DBID) q1

INNER JOIN

(SELECT DBID,

CASE Sum(size*8/1024) 

WHEN 0 THEN 1 

ELSE Sum(size*8/1024)

END AS LogSize

FROM master..sysaltfiles

WHERE GroupID = 0

GROUP BY DBID) q2 

ON q1.DBID = q2.DBID

INNER JOIN

(SELECT DBID, [name] AS DatabaseName,

CONVERT(varchar(100),DATABASEPROPERTYEX([Name], 'Collation')) AS Collation,

CONVERT(varchar(100),DATABASEPROPERTYEX([Name], 'Recovery')) AS RecoveryType,

CASE CONVERT(varchar(10),DATABASEPROPERTYEX([Name], 'IsAutoClose'))

WHEN 0 THEN '-'

WHEN 1 THEN 'Yes'

END  AS AutoClose,

CASE CONVERT(varchar(10),DATABASEPROPERTYEX([Name], 'IsAutoShrink'))

WHEN 0 THEN '-'

WHEN 1 THEN 'Yes'

END AS AutoShrink

FROM master.dbo.sysdatabases) q3

ON q1.DBID = q3.dbid

ORDER BY DatabaseName 

print '                             '

print '                             '

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

print '21. 查看数据库群集信息'

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

PRINT ' **** Cluster Information ****'

PRINT ' '

PRINT ' The following is information on the cluster you are connected'

PRINT ' '

PRINT '... Name of all nodes used and are part of this failover cluster'

SELECT * FROM sys.dm_os_cluster_nodes 

PRINT ' '

PRINT '... Node which is the active '

SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') 

PRINT ' '

PRINT '... Drive letters that are part of the resourse group which contain the data and log files'

SELECT * FROM sys.dm_io_cluster_shared_drives

go

print '                             '