数据库日常维护脚本

1、/* 完整备份作业 */-完整备份，每周一次USE MasterGOdeclare str varchar(100)setstr=D:DBtextjgjDBABakFullBak+replace(replace(replace(convert(varchar,getdate(),20),-,), ,),:,)+.bakBACKUP DATABASE demo TO DISK=strWITH RETAINDAYS=15,NOFORMAT,NOINIT,NAME=NDemo 完整备份，SKIP,NOREWIND,NOUNLOAD,STATS=10*GO差异备份作业 *截断日志USE MasterG

2、OBACKUP LOG Demo WITH NO_LOGGO-收缩日志文件USE DemoGODBCC SHRINKFILE (NDemo_log,0,TRUNCATEONLY)GO-差异备份，每天一次USE MasterGOdeclare str varchar(100)setstr=D:DBtextjgjDBABakDiffBak+replace(replace(replace(convert(varchar,getdate(),20),-,), ,),:,)+.diffBACKUP DATABASE Demo TO DISK=strWITH DIFFERENTIAL,RETAINDAYS

3、=8,NOFORMAT,NOINIT,NAME=NDemo 差异备份,SKIP,NOREWIND,NOUNLOAD,STATS=10GO/* 日志备份作业 */-日志备份，每小时一次USE DemoGOdeclare str varchar(100)setstr=D:DBtextjgjDBABaklogbak+replace(replace(replace(convert(varchar,getdate(),20),-,), ,),:,)+.trnBACKUP LOG Demo TO DISK=strWITH RETAINDAYS=3,NOFORMAT,NOINIT,NAME=NDemo 日志

4、备份，SKIP,NOREWIND,NOUNLOAD,STATS=10GO-删除过期的备份文件，每天两次declare str varchar(100),dir varchar(100),fileName varchar (30)set dir=del D:DBtextjgjDBABak setfilename=left(replace(replace(replace(convert(varchar,getdate()-15, 20),-,), ,),:,),8)set str=dir+fullbak+filename+*.bakexec xp_cmdshell strsetfilename=l

5、eft(replace(replace(replace(convert(varchar,getdate()-8,20),-,), ,),:,),8)set str=dir+diffbak+filename+*.diffexec xp_cmdshell str setfilename=left(replace(replace(replace(convert(varchar, getdate() -8,20), -,), ,),:,),8)set str=dir+logbak+filename+*.trnexec xp_cmdshell str -如何删除SQL2005过期的数据库备份文件呢？在S

6、QL2005数据库中，不可以自动删除过期的备份文件，所以借用第三方插件完成此功能。-方式一：通过Forfiles删除指定目录下过期的备份文件目的：删除目录i: sqldataup中天前的.bak文件：步骤:1、定义FORFILES批处理脚本如下:C: FORFILES / P i: sqldataup / M *. bak / C cmd /C del /Q path / d - 5 如果执行成功则返回当前盘符C: -如果没有需要删除的文件则返回信息错误：用指定的搜索标准没有找到文件。比如： -C:FORFILES /P i:sqldataup /M *.bak /C cmd /C del /

7、Q path /d -5 -错误：用指定的搜索标准没有找到文件。2、通过计划任务调用批处理脚本如图所示，图太长请看这里3、当然也可以用SQLAgent调用CMDEXEC完成批处理作业。方式二：如果在SQL2008中因为默认安装Powershell 1.0程序，故可以用Powershell编写脚本 来完成定时删除过期文件。太多内容，请看文章 HYPERLINK /cl_aro/archive_/2009/08/18/4458417.aspx /cl aro/archive /2009/08/18/4458417.aspx-最好备份日志，以后可通过日志恢复数据。以下为日志处理方法一般不建议做第4,

8、6两步第4步不安全，有可能损坏数据库或丢失数据第6步如果日志达到上限，则以后的数据库处理会失败，在清理日志后才能恢复.一一*/-下面的所有库名都指你要处理的数据库的库名1 .清空日志DUMP TRANSACTION 库名 WITH NO_LOG截断事务日志：BACKUP LOG 库名 WITH NO_LOG3 .收缩数据库文件（如果不压缩，数据库的文件不会减小企业管理器-右键你要压缩的数据库-所有任务-收缩数据库-收缩文件-选择日志文件-在收缩方式里选择收缩至XXM，这里会给出一个允许收缩到的最小M数，直接输入这个 数，确定就可以了-选择数据文件-在收缩方式里选择收缩至XXM,这里会给出一个允

9、许收缩到的最小M数，直接输入这个 数,确定就可以了也可以用SQL语句来完成-收缩数据库DBCC SHRINKDATABASE（库名）-收缩指定数据文件，1是文件号，可以通过这个语句查询到：select * from sysfilesDBCC SHRINKFILE（l）为了最大化的缩小日志文件（如果是sql 7.0,这步只能在查询分析器中进行）分离数据库：企业管理器-服务器-数据库-右键-分离数据库在我的电脑中删除LOG文件附加数据库：企业管理器-服务器-数据库-右键一附加数据库此法将生成新的LOG，大小只有500多K或用代码：下面的示例分离pubs，然后将pubs中的一个文件附加到当前服务器。

10、分离EXEC sp_detach_db dbname =库名删除日志文件再附加EXEC sp_attach_single_file_db dbname = 库名，physname = c:Program FilesMicrosoft SQL ServerMSSQLData库名.mdf5 .为了以后能自动收缩，做如下设置：企业管理器-服务器-右键数据库-属性-选项-选择自动收缩-SQL语句设置方式：EXEC sp_dboption 库名，autoshrink, TRUE6.如果想以后不让它日志增长得太大企业管理器-服务器-右键数据库-属性-事务日志-将文件增长限制为xM（x是你允许的最大数据文件

11、大小） -SQL语句的设置方式：alter database 库名 modify file （name=逻辑文件名，maxsize=20）查看表的索引信息exec sp_helpindex tb -结合 sys.indexes 和 sys.index_columns,sys.objects,sys.columns 查询索引所属的表或视 图的信息select as 表名,name as索引名， as 歹0名,i.type_desc as 类型描述，is_primary_key as 主键约束，is_unique_constraint as 唯一约束，is_disabled as 禁用fromsy

12、s.objects oinner joinsys.indexes ioni.object_id= o.object_idinner joinsys.index_columns iconic.index_id i.index_id and ic.object_id=i.object_idinner joinsys.columns conic.column_id c.column_id and ic.object_id=c.object_idgo-查询索引的键和歹信息select as 表名, as索引名， as字段编号，fromsysindexes i inner join sysobjects

13、 ooni.id=o.idinner joinsysindexkeys kono.id=k.id and i.indid=k.indidinner joinsyscolumns conc.id=i.id and k.colid=c.colidwhere=表名-查询索引操作的信息select * from sys.dm_db_index_usage_stats-查询指定表的统计信息（sys.stats和sysobjects联合查询） select,-表名,-统计信息的名称auto_created,-统计信息是否由查询处理器自动创建user_created-统计信息是否由用户显示创建fromsys

14、.statsinner joinsysobjects oons.object_id= o.idwhere=表名go-查看统计信息中列的信息select,-表名,-统计信息的名称sc.stats_column_id,-列名fromsys.stats_columns scinner joinsysobjects oonsc.object_id= o.idinner joinsys.stats sonsc.stats_id s.stats_id and sc.object_id=s.object_id inner joinsys.columns c onsc.column_id c.column_i

15、d and sc.object_id=c.object_idwhere=表名-查看统计信息的明细信息dbcc show_statistics-查看索引自动创建的统计信息exec sp_autostats 对象名-关闭自动生成统计信息的数据库选项alter datebase 数据库名 set auto_create_statistics off-创建统计信息create statistics 统计信息名称 on 表名（歹。名）withfullscansample numberpercent|rowsnorecomputego解释一下上面的参数：fullscan:指定对表或视图中所有的行收集统计信

16、息sample numberpercent | rows:指定随机抽样应读取的数据行数或者百分比sample选项不能与 fullscan选项同时使用norecompute:指定数据库引擎不自动重新计算统计信息-计算随机抽样统计信息create statistics 统计信息名称 on 表名（歹。名）with sample 5 percent创建统计信息，按5%计算随机抽样统计信息go-创建统计信息exec sp_createstats-参数自己去查下帮助，在这里不一一列举-修改统计信息update statistics 表名 | 视图名索引名|统计信息名，索引名|统计信息名，withfulls

17、cansample numberpercent|rowsnorecompute参数与create statistics语句相似，下面介绍几种常用应用1.更新指定表的所有统计信息 update statistics 表名更新指定表的单个索引的统计信息update statistics 表名索引名对表进行全面扫描，更新统计信息update statistics 表名(歹。名)with fullscan-获取磁盘读写情况selecttotal_read as 读取磁盘的次数，total_write as 写入磁盘的次数，total_error as 磁盘写入错误数，getdate () as 当前时

18、间-获取数据库文件的I/O统计信息select * from fn_virtualfilestats(null,null)-两个参数database_id-指定数据库编号，如果为null,则为所有数据库实例返回I/O统计信息 file_id -文件的编号，如果为null，则为所有文件返回信息-获取I/O工作情况selectid_busy, -SQL自上次启动以来的用于执行输入和输出操作的时间timeticks,-每个时钟周期对应的微秒数id_busy*timeticks as I/O 操作毫秒数，getdate () as 当前时间-查看SQL SEVER CPU活动，工作情况selectcp

19、u_busy,-自上次启动以来的工作时间timeticks,-每个时钟周期对应的微秒数cpu_busy*cast (timeticks as float) /1000 as cpu 工作时间(秒)，idie*cast (timeticks as float) /1000 as CPU 空闲时间(秒) getdate () as 当前时间-获取网络数据包统计信息selectgetdate () as 当前时间,pack_received as 输入数据包数量,pack_sent as 输出数据包数量，packet_error as 错误包数量启动AWEsp_configure show adva

20、nced options,1reconfiguregosp_configure awe enable , 1-启动AWE选项，用于支持超过4G内存 具体用法见笔记三gosp_configure show advanced options,0 reconfigurego-指定游标集中的行数sp_configure show advanced options,1reconfiguregosp_configure cursor threshold-指定游标集中的行数，超过此行数，将异步生成游标键集gosp_configure show advanced options,0reconfigurego/

21、*配置选项show advanced options已从0更改为1。请运行RECONFIGURE语句进行安装。nameminimum maximum config_value run_valuecursor threshold-12147483647-1-1配置选项show advanced options已从1更改为0。请运行RECONFIGURE语句进行安装。*/-指定全文索引列的默认语言值sp_configure show advanced options,1reconfiguregosp_configure default full-text language -2052代表简体中文，

22、具体的查询联机丛书gosp_configure show advanced options,0reconfigurego/*nameminimum maximum config_value run_valuedefault full-text language0214748364720522052*/-控制是否让触发器返回结果集sp_configure show advanced options,1reconfiguregosp_configure disallow results from triggers , 1-1 代表 ongosp_configure disallow results

23、 from triggers , 0-0 代表 offgosp_configure show advanced options,0reconfigurego/*配置选项 disallow results from triggers已从 1 更改为 1。请运彳亍 RECONFIGURE 语 句进行安装。配置选项disallow results from triggers已从 1更改为 0。请运行 RECONFIGURE语句进 行安装。*/-控制最初为创建索引分配的最大内存量sp_configure index create memory, 4096GO设置可用的锁的最大个数sp_configur

24、e show advanced options,1reconfiguregosp_configure locks要设置的话在后面加,数字gosp_configure show advanced options,0reconfigurego/*nameminimum maximum config_value run_valuelocks5000214748364700*/-设置SQL进程可使用的工作线程数sp_configure show advanced options,1reconfiguregosp_configure max worker threads-要设置的话在后面加,数字gosp_configure show advanced options,0reconfigurego/*nameminimummaximumconfig_value run_valuemax worker threads12832767*/-指定一个查询在超时前等待所需资源的时间 sp_configure query wait,数字 go-指定在SQL SERVER超时之前远程操作可以持续的时间 sp_configure remote query timeout,数字go-是否允许运行系统存储过程xp_cmdshell sp_configur