sqlserver的日常检查脚本归结

核心提示：print ' 'print ' 'print ' 'print '--'print &

print '                             '

print '                             '

print '                             '

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

print '14.检查SQL Server的执行缓存和数据缓存占用'

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

print '*********************************'

use master

go

dbcc memorystatus 

go

print '*********************************'

print '                             '

print '                             '

print '                             '

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

print '15.所有数据库备份情况'

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

print '*********************************'

-- sql server 2000/2005 version

use master

go

set nocount on

go

declare @counter smallint

declare @dbname varchar(100)

declare @db_bkpdate varchar(100)

declare @status varchar(20)

declare @svr_name varchar(100)

declare @media_set_id varchar(20)

declare @filepath varchar(1000)

declare @filestatus int

declare @fileavailable varchar(20)

declare @backupsize float

select @counter=max(dbid) from master..sysdatabases

create table #backup_details 

(

servername varchar(100),

databasename varchar(100),

bkpdate varchar(20) null,

backupsize_in_mb varchar(20),

status varchar(20),

filepath varchar(1000),

fileavailable varchar(200)

)

select @svr_name = cast(serverproperty('servername')as sysname)

while @counter > 0

begin

/* need to re-initialize all variables*/

select @dbname = null , @db_bkpdate = null ,

@media_set_id = null , @backupsize = null ,

@filepath = null , @filestatus = null , 

@fileavailable = null , @status = null , @backupsize = null

select @dbname = name from master..sysdatabases where dbid = @counter

select @db_bkpdate = max(backup_start_date) from msdb..backupset where database_name = @dbname and type='d'

select @media_set_id = media_set_id from msdb..backupset where backup_start_date = ( select max(backup_start_date) from msdb..backupset where database_name = @dbname and type='d')