sqlserver的日常检查脚本归结

serverproperty ('Machinename') as 'Machine',                      

serverproperty ('Instancename') as 'InstanceName',                      

(SELECT 'file_type' =                      

CASE                      

WHEN s.groupid <> 0 THEN 'data'                      

WHEN s.groupid = 0 THEN 'log'                      

END) AS 'fileType'                      

, d.dbid as 'DBID'                      

, d.name AS 'DBName'                      

, s.name AS 'LogicalFileName'                      

, s.filename AS 'PhysicalFileName'                      

, (s.size * 8 / 1024) AS 'FileSizeMB' -- file size in MB                      

,d.cmptlevel as 'CompatibilityLevel'                      

, DATABASEPROPERTYEX (d.name,'Recovery') as 'RecoveryModel'                      

, DATABASEPROPERTYEX (d.name,'Status') as 'DatabaseStatus' ,                   　

(SELECT 'is_replication' =                      

CASE                      

WHEN d.category = 1 THEN 'Published'                      

WHEN d.category = 2 THEN 'subscribed'                      

WHEN d.category = 4 THEN 'Merge published'

WHEN d.category = 8 THEN 'merge subscribed'

Else 'NO replication'

 END) AS 'Is_replication',

 @Mirroring as 'MirroringState'

FROM                      

sysdatabases d INNER JOIN sysaltfiles s                      

ON                      

d.dbid=s.dbid                      

ORDER BY