SqlServer数据库自动备份和清理磁盘备份策略及巡检脚本
2018年4月份数据库排行榜趋势图
众所周知，it行业数据非常昂贵，毫不夸张的说可以是无价之宝，数据灾备是重中之重，一旦数据发生丢失，哭都来不及。所以作为一名DBA、运维人员、实施人员亦或是测试人员必须要学会如何备份以及清理备份文件，自动清理备份可避免手工人为清理，及备份文件数量太多后，造成数据库服务器磁盘io空间不足，最后导致服务器宕机。
如我们可以每天0点为指定数据库做一个完整的备份，每周未在0点去清理上一周的备份文件，这样可以避免磁盘容量无穷的增长。
一、备份数据库
使用了SQL－管理－维护计划－维护计划向导
随后下一步，可为计划任务拟定一个名字，如syydbnameautobackup。建议以项目名称+数据库名称+自动备份来命名。
接下来配置计划任务策略。点击“更改”。
之后，选择备份数据库，有完整，差异和日志，建议选择完整备份，这样数据恢复更彻底。
之后选择要备份的数据库，当前可以同时备份多个库，也可以备份所有的数据库。当然大多数情况下，因服务器磁盘空间有限，只备份自己所需的数据库即可。
备份文件bak的存储路径我们也要设置一下，这会以时间为单位对文件进行存储。
最后一步，我们可以第一时间执行一个备份计划，这时在对应的文件夹里升成了备份的文件。
二、当数据库完全备份后，由于随着每天的业务数据量增长故数据库会不断增大。需要去清理备份文件。清理方式有手工去清理也可自动清理。当然数据库自己能完成的事情没必要人工去做了。
选择刚才我们的备份计划，然后添加一个子计划：
选择对备份任务进行清理，周期可以根据自己的实际情况去灵活设置。
**********************************************************************************
SqlServer数据库巡检脚本：
1.查看所有数据库名称及大小
select sp_helpdb
2.查看数据库实例名
select 'Instance:'+ltrim(@@servicename)
3.数据库的磁盘空间呢使用信息
exec sp_spaceused
4.日志文件大小及使用情况
dbcc sqlperf(logspace)
5.表的磁盘空间使用信息
exec sp_spaceused 'tablename'
6.获取磁盘读写情况
select
@@total_read [读取磁盘次数],
@@total_write [写入磁盘次数],
@@total_errors [磁盘写入错误数],
getdate() [当前时间]
7.获取I/O工作情况
select @@io_busy,
@@timeticks [每个时钟周期对应的微秒数],
@@io_busy*@@timeticks [I/O操作毫秒数],
getdate() [当前时间]
8.查看CPU活动及工作情况
select
@@cpu_busy,
@@timeticks [每个时钟周期对应的微秒数],
@@cpu_busy*cast(@@timeticks as float)/1000 [CPU工作时间(秒)],
@@idle*cast(@@timeticks as float)/1000 [CPU空闲时间(秒)],
getdate() [当前时间]
9.检查锁与等待
exec sp_lock
10.检查死锁
exec sp_who_lock --自己写个存储过程即可
/*
create procedure sp_who_lock
as
begin
declare @spid int,@bl int,
@intTransactionCountOnEntry int,
@intRowcount int,
@intCountProperties int,
@intCounter int
create table #tmp_lock_who (id int identity(1,1),spid smallint,bl smallint)
IF @@ERROR<>0 RETURN @@ERROR
insert into #tmp_lock_who(spid,bl) select 0 ,blocked
from (select * from sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sysprocesses where blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where blocked>0
IF @@ERROR<>0 RETURN @@ERROR
-- 找到临时表的记录数
select @intCountProperties = Count(*),@intCounter = 1
from #tmp_lock_who
IF @@ERROR<>0 RETURN @@ERROR
if @intCountProperties=0
select '现在没有阻塞和死锁信息' as message
-- 循环开始
while @intCounter <= @intCountProperties
begin
-- 取第一条记录
select @spid = spid,@bl = bl
from #tmp_lock_who where id = @intCounter
begin
if @spid =0
select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
else
select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
end
-- 循环指针下移
set @intCounter = @intCounter + 1
end
drop table #tmp_lock_who
return 0
end
*/
11.用户和进程信息
exec sp_who
exec sp_who2
12.查看所有数据库用户所属的角色信息
exec sp_helpsrvrolemember
13.查看远端数据库用户登录信息
exec sp_helpremotelogin
14.查询文件组和文件
select
df.[name],df.physical_name,df.[size],df.growth,
f.[name][filegroup],f.is_default
from sys.database_files df join sys.filegroups f
on df.data_space_id = f.data_space_id
15.得到最耗时的前10条T-SQL语句
;with maco as
(
select top 10
plan_handle,
sum(total_worker_time) as total_worker_time ,
sum(execution_count) as execution_count ,
count(1) as sql_count
from sys.dm_exec_query_stats group by plan_handle
order by sum(total_worker_time) desc
)
select t.text ,
a.total_worker_time ,
a.execution_count ,
a.sql_count
from maco a
cross apply sys.dm_exec_sql_text(plan_handle) t
16. 查看SQL Server的实际内存占用
select * from sysperfinfo where counter_name like '%Memory%'
17.sqlserver重建索引
--1.先查询下索引 把结果拷贝出来 需要七八秒时间
DBCC showcontig('表名')
--2.重建索引 大概要15秒左右
DBCC DBREINDEX('表名')
--3.再查询下索引 把结果拷贝出来
DBCC showcontig('表名')