收集SQL Server数据库巡检脚本

[bean_sql]

网上找了一些，先贴出来，
请大神继续补充。

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


--查看某数据库下每个数据对象的大小 
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更改为要收缩的数据库名称

[woof_1128]

好东西值得收藏

[angelgxw]

学习了，不错不错

[ycj80]

收藏....

[cainiao_w]

xingliuming

[skyclin]

向各大位大神学习一下

[jAmEs_]

[wolf0532]

[u010092411]

学习学习

[annierwu]

59楼的实用些

[唐诗三百首]

@叶子兄台,分享一下sp_who3的代码吖?

[migeking]

DECLARE @spid INT DECLARE @blk INT DECLARE @count INT DECLARE @index INT DECLARE @lock TINYINT SET @lock=0 CREATE TABLE #temp_who_lock ( id INT IDENTITY(1, 1), spid INT, blk INT ) --if @@error<>0 return @@error INSERT INTO #temp_who_lock (spid, blk) SELECT 0, blocked FROM (SELECT * FROM master..sysprocesses WHERE blocked > 0)a WHERE NOT EXISTS(SELECT * FROM master..sysprocesses WHERE a.blocked = spid AND blocked > 0) UNION SELECT spid, blocked FROM master..sysprocesses WHERE blocked > 0 --if @@error<>0 return @@error SELECT @count = Count(*), @index = 1 FROM #temp_who_lock --select @count,@index --if @@error<>0 return @@error IF @count = 0 BEGIN SELECT '没有阻塞和死锁信息' --return 0 END WHILE @index <= @count BEGIN IF EXISTS(SELECT 1 FROM #temp_who_lock a WHERE id > @index AND EXISTS(SELECT 1 FROM #temp_who_lock WHERE id <= @index AND a.blk = spid)) BEGIN SET @lock=1 SELECT @spid = spid, @blk = blk FROM #temp_who_lock WHERE id = @index SELECT '引起数据库死锁的是: ' + Cast(@spid AS VARCHAR(10)) + '进程号,其执行的SQL语法如下' ; SELECT @spid, @blk DBCC inputbuffer(@spid) DBCC inputbuffer(@blk) END SET @index=@index + 1 END IF @lock = 0 BEGIN SET @index=1 WHILE @index <= @count BEGIN SELECT @spid = spid, @blk = blk FROM #temp_who_lock WHERE id = @index IF @spid = 0 SELECT '引起阻塞的是:' + Cast(@blk AS VARCHAR(10)) + '进程号,其执行的SQL语法如下' ELSE SELECT '进程号SPID：' + Cast(@spid AS VARCHAR(10)) + '被' + '进程号SPID：' + Cast(@blk AS VARCHAR(10)) + '阻塞,其当前进程执行的SQL语法如下' PRINT ( LTRIM(@spid) + ''+ LTRIM(@blk)); if(@spid <> 0) BEGIN DBCC inputbuffer(@spid) -- END DBCC inputbuffer(@blk) --引起阻塞语句 SET @index=@index + 1 END END DROP TABLE #temp_who_lock --return 0 -----查询出来的进程用 KILL +进程ID --KILL 98 -- 死锁回顾与处理

[lao_bulls]

不错，我顶呀。。。mark!

[夜予]

翻下页

[铁歌]

--总耗CPU最多的前个SQL: SELECT TOP 20 total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数], qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)], last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)], SUBSTRING(qt.text,qs.statement_start_offset/2+1, (CASE WHEN qs.statement_end_offset = -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) AS [使用CPU的语法], qt.text [完整语法], qt.dbid, dbname=db_name(qt.dbid), qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName FROM sys.dm_exec_query_stats qs WITH(nolock) CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE execution_count>1 ORDER BY total_worker_time DESC --平均耗CPU最多的前个SQL: SELECT TOP 20 total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数], qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)], last_execution_time AS [最后一次执行时间],min_worker_time /1000 AS [最小执行时间(ms)], max_worker_time /1000 AS [最大执行时间(ms)], SUBSTRING(qt.text,qs.statement_start_offset/2+1, (CASE WHEN qs.statement_end_offset = -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1) AS [使用CPU的语法], qt.text [完整语法], qt.dbid, dbname=db_name(qt.dbid), qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName FROM sys.dm_exec_query_stats qs WITH(nolock) CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE execution_count>1 ORDER BY (qs.total_worker_time/qs.execution_count/1000) DESC

[铁歌]

--查询空间使用

if object_id('tempdb..#tpData') is not null drop table #tpData;
create table #tpData(name varchar(100)
 ,row varchar(100)
 ,reserved varchar(100)
 ,data varchar(100)
 ,index_size varchar(100)
 ,unused varchar(100)) 
 
declare @name varchar(100) 
declare cur cursor  for 
    select name from sysobjects where xtype='u' order by name 
open cur 
fetch next from cur into @name 
while @@fetch_status=0 
begin 
    insert into #tpData 
    exec sp_spaceused   @name 
    print @name 
 
    fetch next from cur into @name 
end 
close cur 
deallocate cur 
 
select name
 ,convert(int,row) as row
 ,convert(int,replace(reserved,'KB','')) as reserved
 ,convert(int,replace(data,'KB','')) as data
 ,convert(int,replace(index_size,'KB','')) as index_size
 ,convert(int,replace(unused,'KB','')) as unused 
from #tpData

[铁歌]

CREATE PROCEDURE sp_who3 ( @SessionID INT = NULL ) AS BEGIN SELECT SPID = er.session_id ,STATUS = ses.STATUS ,[Login] = ses.login_name ,Host = ses.host_name ,BlkBy = er.blocking_session_id ,DBName = DB_Name(er.database_id) ,CommandType = er.command ,SQLStatement = st.text ,ObjectName = OBJECT_NAME(st.objectid) ,ElapsedMS = er.total_elapsed_time ,CPUTime = er.cpu_time ,IOReads = er.logical_reads + er.reads ,IOWrites = er.writes ,LastWaitType = er.last_wait_type ,StartTime = er.start_time ,Protocol = con.net_transport ,ConnectionWrites = con.num_writes ,ConnectionReads = con.num_reads ,ClientAddress = con.client_net_address ,Authentication = con.auth_scheme FROM sys.dm_exec_requests er OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id WHERE er.session_id > 50 AND @SessionID IS NULL OR er.session_id = @SessionID ORDER BY er.blocking_session_id DESC ,er.session_id END GO

[lmy0403]

学习了！刚刚有用到！！！！

[xiongshao1943]

mark!留着以后备用

[ryan_hb]

thk!!!!

[四眼小兵]

彩笔，还不懂