数据库日常维护脚本.doc

/*完整备份作业*/-完整备份,每周一次USE MasterGOdeclare str varchar(100)set str=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=10GO/*差异备份作业*/-截断日志USE MasterGOBACKUP LOG Demo WITH NO_LOGGO-收缩日志文件USE DemoGODBCC SHRINKFILE (NDemo_log,0,TRUNCATEONLY)GO-差异备份,每天一次USE MasterGOdeclare str varchar(100)set str=D:DBtextjgjDBABakDiffBak+replace(replace(replace(convert(varchar,getdate(),20),-,), ,),:,)+.diffBACKUP DATABASE Demo TO DISK=strWITH DIFFERENTIAL,RETAINDAYS=8,NOFORMAT,NOINIT,NAME=NDemo差异备份,SKIP,NOREWIND,NOUNLOAD,STATS=10GO/*日志备份作业*/-日志备份,每小时一次USE DemoGOdeclare str varchar(100)set str=D:DBtextjgjDBABaklogbak+replace(replace(replace(convert(varchar,getdate(),20),-,), ,),:,)+.trnBACKUP LOG Demo TO DISK=strWITH RETAINDAYS=3,NOFORMAT,NOINIT,NAME=NDemo日志备份,SKIP,NOREWIND,NOUNLOAD,STATS=10GO-删除过期的备份文件,每天两次declare str varchar(100),dir varchar(100),fileName varchar(30)set dir=del D:DBtextjgjDBABakset filename=left(replace(replace(replace(convert(varchar,getdate()-15,20),-,), ,),:,),8)set str=dir+fullbak+filename+*.bakexec xp_cmdshell strset filename=left(replace(replace(replace(convert(varchar,getdate()-8,20),-,), ,),:,),8)set str=dir+diffbak+filename+*.diffexec xp_cmdshell strset filename=left(replace(replace(replace(convert(varchar,getdate()-8,20),-,), ,),:,),8)set str=dir+logbak+filename+*.trnexec xp_cmdshell str- 如何删除 SQL2005 过期的数据库备份文件呢？ 在 SQL2005 数据库中，不可以自动删除过期的备份文件，所以借用第三方插件完成此功能。 - 方式一：通过 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 /Q path /d -5 - 错误 : 用指定的搜索标准没有找到文件。 2 、通过计划任务调用批处理脚本如图所示,图太长请看这里 3 、当然也可以用 SQLAgent 调用 CMDEXEC 完成批处理作业。 方式二：如果在 SQL2008 中因为默认安装 Powershell 1.0 程序，故可以用 Powershell 编写脚本来完成定时删除过期文件。 太多内容，请看文章/claro/archive/2009/08/18/4458417.aspx。-最好备份日志，以后可通过日志恢复数据。以下为日志处理方法一般不建议做第4,6两步第4步不安全,有可能损坏数据库或丢失数据第6步如果日志达到上限,则以后的数据库处理会失败,在清理日志后才能恢复.-*/-下面的所有库名都指你要处理的数据库的库名1.清空日志DUMP TRANSACTION 库名 WITH NO_LOG2.截断事务日志：BACKUP LOG 库名 WITH NO_LOG3.收缩数据库文件(如果不压缩,数据库的文件不会减小企业管理器-右键你要压缩的数据库-所有任务-收缩数据库-收缩文件-选择日志文件-在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了-选择数据文件-在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了也可以用SQL语句来完成 -收缩数据库DBCC SHRINKDATABASE(库名)-收缩指定数据文件,1是文件号,可以通过这个语句查询到:select * from sysfilesDBCC SHRINKFILE(1)4.为了最大化的缩小日志文件(如果是sql 7.0,这步只能在查询分析器中进行)a.分离数据库:企业管理器-服务器-数据库-右键-分离数据库b.在我的电脑中删除LOG文件c.附加数据库:企业管理器-服务器-数据库-右键-附加数据库此法将生成新的LOG，大小只有500多K或用代码： 下面的示例分离 pubs，然后将 pubs 中的一个文件附加到当前服务器。a.分离EXEC sp_detach_db dbname = 库名b.删除日志文件c.再附加EXEC sp_attach_single_file_db dbname = 库名, physname = c:Program FilesMicrosoft SQL ServerMSSQLData库名.mdf5.为了以后能自动收缩,做如下设置:企业管理器-服务器-右键数据库-属性-选项-选择自动收缩-SQL语句设置方式:EXEC sp_dboption 库名, autoshrink, TRUE6.如果想以后不让它日志增长得太大企业管理器-服务器-右键数据库-属性-事务日志-将文件增长限制为xM(x是你允许的最大数据文件大小)-SQL语句的设置方式:alter database 库名 modify file(name=逻辑文件名,maxsize=20)-查看表的索引信息exec sp_helpindex tb-结合sys.indexes和sys.index_columns,sys.objects,sys.columns查询索引所属的表或视图的信息select as 表名, as 索引名, as 列名, i.type_desc as 类型描述, is_primary_key as 主键约束, is_unique_constraint as 唯一约束, is_disabled as 禁用from sys.objects o inner join sys.indexes ion i.object_id=o.object_idinner join sys.index_columns icon ic.index_id=i.index_id and ic.object_id=i.object_idinner join sys.columns con ic.column_id=c.column_id and ic.object_id=c.object_idgo-查询索引的键和列信息select as 表名, as 索引名, as 字段编号,from sysindexes i inner join sysobjects o on i.id=o.idinner join sysindexkeys k on o.id=k.id and i.indid=k.indidinner join syscolumns c on c.id=i.id and k.colid=c.colidwhere =表名-查询索引操作的信息select * from sys.dm_db_index_usage_stats-查询指定表的统计信息(sys.stats和sysobjects联合查询)select ,-表名 ,-统计信息的名称 auto_created,-统计信息是否由查询处理器自动创建 user_created-统计信息是否由用户显示创建from sys.statsinner join sysobjects oon s.object_id=o.idwhere =表名go-查看统计信息中列的信息select ,-表名 ,-统计信息的名称 sc.stats_column_id, -列名from sys.stats_columns scinner join sysobjects oon sc.object_id=o.idinner join sys.stats son sc.stats_id=s.stats_id and sc.object_id=s.object_idinner join sys.columns con sc.column_id=c.column_id and sc.object_id=c.object_idwhere =表名-查看统计信息的明细信息dbcc show_statistics-查看索引自动创建的统计信息exec sp_autostats 对象名-关闭自动生成统计信息的数据库选项alter datebase 数据库名 set auto_create_statistics off-创建统计信息create statistics 统计信息名称 on 表名(列名)with fullscan sample numberpercent|rows norecomputego解释一下上面的参数：fullscan:指定对表或视图中所有的行收集统计信息sample numberpercent|rows:指定随机抽样应读取的数据行数或者百分比 sample选项不能与fullscan选项同时使用norecompute:指定数据库引擎不自动重新计算统计信息-计算随机抽样统计信息create statistics 统计信息名称 on 表名(列名)with sample 5 percent-创建统计信息，按5%计算随机抽样统计信息go-创建统计信息exec sp_createstats-参数自己去查下帮助，在这里不一一列举-修改统计信息update statistics 表名|视图名 索引名|统计信息名,索引名|统计信息名,.with fullscan sample numberpercent|rows norecompute-参数与create statistics 语句相似，下面介绍几种常用应用1.更新指定表的所有统计信息update statistics 表名2.更新指定表的单个索引的统计信息update statistics 表名 索引名3.对表进行全面扫描，更新统计信息update statistics 表名(列名) with fullscan-获取磁盘读写情况select total_read as 读取磁盘的次数, total_write as 写入磁盘的次数, total_error as 磁盘写入错误数, getdate() as 当前时间-获取数据库文件的I/O统计信息select * from fn_virtualfilestats(null,null)-两个参数database_id-指定数据库编号，如果为null,则为所有数据库实例返回I/O统计信息file_id -文件的编号,如果为null,则为所有文件返回信息-获取I/O工作情况select id_busy，-SQL自上次启动以来的用于执行输入和输出操作的时间 timeticks, -每个时钟周期对应的微秒数 id_busy*timeticks as I/O 操作毫秒数, getdate() as 当前时间-查看SQL SEVER CPU活动,工作情况select cpu_busy,-自上次启动以来的工作时间 timeticks, -每个时钟周期对应的微秒数 cpu_busy*cast(timeticks as float)/1000 as cpu工作时间(秒), idie*cast(timeticks as float)/1000 as CPU空闲时间(秒) getdate() as 当前时间-获取网络数据包统计信息select getdate() as 当前时间, pack_received as输入数据包数量, pack_sent as 输出数据包数量, packet_error as 错误包数量-启动AWEsp_configure show advanced options,1reconfiguregosp_configure awe enable,1-启动AWE选项，用于支持超过4G内存 具体用法见笔记三go sp_configure show advanced options,0reconfigurego-指定游标集中的行数sp_configure show advanced options,1reconfiguregosp_configure cursor threshold-指定游标集中的行数，超过此行数，将异步生成游标键集go sp_configure show advanced options,0reconfigurego/*配置选项 show advanced options 已从 0 更改为 1。请运行 RECONFIGURE 语句进行安装。name minimum maximum config_value run_value- - - - -cursor threshold -1 2147483647 -1 -1配置选项 show advanced options 已从 1 更改为 0。请运行 RECONFIGURE 语句进行安装。*/-指定全文索引列的默认语言值sp_configure show advanced options,1reconfiguregosp_configure default full-text language-2052代表简体中文，具体的查询联机丛书go sp_configure show advanced options,0reconfigurego/*name minimum maximum config_value run_value- - - - -default full-text language 0 2147483647 2052 2052*/-控制是否让触发器返回结果集sp_configure show advanced options,1reconfiguregosp_configure disallow results from triggers,1-1代表ongo sp_configure disallow results from triggers,0-0代表offgo sp_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_configure show advanced options,1reconfiguregosp_configure locks-要设置的话在后面加,数字go sp_configure show advanced options,0reconfigurego/*name minimum maximum config_value run_value- - - - -locks 5000 2147483647 0 0*/-设置SQL进程可使用的工作线程数sp_configure show advanced options,1reconfiguregosp_configure max worker threads-要设置的话在后面加,数字go sp_configure show advanced options,0reconfigurego/*name minimum maximum config_value run_value- - - - -max worker threads 128 32767 0 0*/-指定一个查询在超时前等待所需资源的时间sp_configure query wait,数字go-指定在SQL SERVER超时之前远程操作可以持续的时间sp_configure remote query timeout,数字go-是否允许运