QL Server数据库巡检脚本

set  nocount  on    --不显示提示信息

select  '当前时间是：' + convert ( varchar ,getdate(),120)+ '    执行sql server维护'

 

-----------------------------------------------"

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

print  '--------------查看配置信息---------------'

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

 

--查看数据库服务器名和实例名

print  '数据库服务器名:' + convert ( varchar (30),@@servername)

--print '实例名:'+convert(varchar(30),@@server

 

--获取当前数据库的版本

select  '检查当前数据库的版本'

select  @@version

 

--获取数据库所在机器操作系统参数

select  '数据库所在机器操作系统参数'

exec   master..xp_msver

 

--获取数据库启动参数

select  '数据库启动参数'

exec  sp_configure

 

--查看数据库启动时间

select  '数据库启动时间'

select  convert ( varchar (30),login_time,120)  from  master..sysprocesses  where  spid=1

 

 

--查看所有数据库用户登录信息

select  '所有数据库用户登录信息' 

exec  sp_helplogins 

 

 

 

 

-------查看数据库信息-----------------

--查看所有数据库名称

select  '数据库的名称'

exec  sp_helpdb

 

1.查看数据库服务器名和实例名 

print  'Server Name...............: '  +  convert ( varchar (30),@@SERVERNAME) 

print  'Instance..................: '  +  convert ( varchar (30),@@SERVICENAME) 

 

2.查看所有数据库名称及大小 

sp_helpdb 

 

3.重命名数据库用的SQL 

sp_renamedb  'old_dbname' ,  'new_dbname' 

  

4.查看所有数据库用户所属的角色信息 

sp_helpsrvrolemember 

修复迁移服务器时孤立用户时,可以用的fix_orphan_user脚本或者LoneUser过程 

更改某个数据对象的用户属主 

sp_changeobjectowner [@objectname =]  'object' , [@newowner =]  'owner' 

注意: 更改对象名的任一部分都可能破坏脚本和存储过程。 

把一台服务器上的数据库用户登录信息备份出来可以用add_login_to_aserver脚本 

 

5. 查看链接服务器 

sp_helplinkedsrvlogin 

  

6.查看远端数据库用户登录信息 

sp_helpremotelogin

  

7.查看某数据库下某个数据对象的大小 

sp_spaceused @objname 

还可以用sp_toptables过程看最大的N(默认为50)个表 

  

8.查看某数据库下某个数据对象的索引信息 

sp_helpindex @objname 

还可以用SP_NChelpindex过程查看更详细的索引情况 

SP_NChelpindex @objname 

  

9.查看数据库里所有的存储过程和函数 

use @database_name 

sp_stored_procedures 

查看存储过程和函数的源代码 

sp_helptext  '@procedure_name' 

  

10.查看数据库里用户和进程的信息 

sp_who 

查看SQL Server数据库里的活动用户和进程的信息 

sp_who  'active' 

查看SQL Server数据库里的锁的情况 

sp_lock 

进程号1 --50是SQL Server系统内部用的,进程号大于50的才是用户的连接进程. 

spid是进程编号,dbid是数据库编号,objid是数据对象编号 

查看进程正在执行的SQL语句 

dbcc inputbuffer () 

 

推荐大家用经过改进后的sp_who3过程可以直接看到进程运行的SQL语句 

sp_who3 

 

检查死锁用sp_who_lock过程 

sp_who_lock 

  

11.收缩数据库日志文件的方法 

收缩简单恢复模式数据库日志，收缩后@database_name_log的大小单位为M 

backup log @database_name  with  no_log 

dbcc shrinkfile (@database_name_log, 5) 

  

12.分析SQL Server SQL 语句的方法: 

set  statistics  time  { on  |  off } 

set  statistics  io { on  |  off } 

图形方式显示查询执行计划 

在查询分析器->查询->显示估计的评估计划(D)-Ctrl-L 或者点击工具栏里的图形 

文本方式显示查询执行计划 

set  showplan_all { on  |  off } 

set  showplan_text {  on  |  off  } 

set  statistics  profile {  on  |  off  } 

 

--查看某数据库下每个数据对象的大小 

print  '查看数据库下数据对象的大小'            --添加@objname

exec  sp_spaceused

 

print  '查看和收缩数据库日志文件的方法' 

print  '所有数据库日志文件大小'

dbcc sqlperf(logspace)

 

 

-----------查看进程--------------------

select  '数据库里用户和进程的信息'

exec  sp_who

 

select   'SQL Server数据库里的活动用户和进程的信息'   --活动的时候才

exec  sp_who  'active'

 

select   'SQL Server数据库里的锁的情况'

exec  sp_lock

 

 

----------数据库备份-------------------

----------如果有多个数据库，请复制，并将数据库名称改为相应的名称------

print  '--------数据库AdventureWorks完整备份--------------'

BACKUP  DATABASE  [AdventureWorks]  TO   DISK = N 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak'  WITH  NOFORMAT, NOINIT,   NAME  = N 'AdventureWorks-完整 数据库 备份' , SKIP, NOREWIND, NOUNLOAD,  STATS = 10

 

print   '------------数据库AdventureWorks事务日志备份----------'

BACKUP LOG [AdventureWorks]  TO   DISK = N 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak'  WITH  NOFORMAT, NOINIT,   NAME  = N 'AdventureWorks-事务日志  备份' , SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

 

-----------收缩数据库-----------------

----------如果有多个数据库，请复制，并将数据库名称改为相应的名称------

 

 

print  '收缩数据库文件'

DBCC SHRINKDATABASE([AdventureWorks])    --AdventureWorks更改为要收缩的数据库名称