sqlserver数据库日常维护规范.docx

1、sqlserver数据库日常维护规范1.数据库服务器负载情况维护査看CPU, IO,内存使用情况规则Windows 系统:打开任务管理器，选择进程选项卡，可以査看CPU、内存和10使用率02.数据库运行状态维护规则1.实例运行状态使用SQLSERVER Management Studio能连接说明实例运行正常。2.磁盘空间使用情况直接登陆服务器进行磁盘使用率检査3査看错误日志：方法一：可以通过执行该命令来査看错误日志信息：execxp_readerrorlog方法二：在 SQL SERVER Management Studio 中连接该 sql server 实例，object Explore

2、r 中査 找 Managemenl-SQL Server logs-右键选 view-选择你要看的日志信息(sql server log or sql server and Windows log)方法三：去安装路径下去找你的log文件，我的默认实例路径如下driver:SQL Server 2008 R2MSSQL10_50.MSSQLSERVERMSSQLLogERRORLOG4跟踪事件企业皆理里SQL2OO5SQL2OO8在上方菜单一工具一SQL Profiler,按照选择进行跟踪。3.数据库备份情况维护规则数据库备份情况检査:备份日志显示：成功，说明备份成功。备份日志显示：失败，说明备

3、份失败，需要进一步査找失败原因。 每天检査异地备份是否成功。4.数据库对彖的维护规则1、査询乞个磁盘分区的剩余空间:Exec master.dbo.xp_fixeddrives2、 査询数据库的数据文件及日志文件的相关信息(包括文件组、当前文件大小、文件 最大值、文件增长设置、文件逻辑名、文件路径等select * from 数据库家dbosysfilcs转换文件大小单位为MB：select name, convert(floai,size) * (8192.0/1024.0)/1024. from 数据库名,dbo.sysfiles3、 査询当前数据库的磁盘使用情况：Exec sp_spac

4、eused4、査询数据商服务器各数据库日志文件的大小及利用率DBCC SQLPERF(LOGSPACE)5、统讣数据库中每张表的大小create table impname varchar(50).rowscount int, reserved varchar(50).data varchar(50).index_size varchar(50).unused varchar(50)insert tmp(namc rowscount. reserved, data. index_size. unused)exec sp_MSforeachtable commandl=”sp_spaceused

5、select * from tmp where name tmp order by name drop table imp :或考SELECTa. name, b.rowsFROMWHEREsysobjecls AS a INNER JOINsysindexes AS b ON aJd = b.id(a.type = V) AND (bJndid IN (0, 1)ORDER BY b.rows DESC6、检査数据库完整性dbcc checkdb(Portal)dbcc checkdb(Portal) with tablock7、数据库重命名、修改恢复模式、修改用户模式-数据库重命需ALTE

6、R DATABASE WCMODIFY NAME = test-设置数据原为完整恢复模式alter database test set recovery full-只允许一个用户访问数据库alter database test set single_userwith rollback after 10 seconds 指泄多少秒后回滚事务只有sysadmin,dbcreatordb_owner角色的成员可以访问数据库alter database wc set restricted userwith rollback immediate立即回滚事务-多用户模式alter database wcs

7、et multi userwith no wait-不等待立即改变，如不能立即完成，那么会导致执行错误8、扩展数据库：增加文件组、增加文件、修改文件大小、修改文件的逻辑名称添加文件组ALTER DATABASE testADD FILEGROUPWC FG8-添加数据文件ALTER DATABASE testADD FILENAME = WC FG&FILENAME = D:WC_FG&ndfSIZE = 1 mb.MAXSIZE = lOmb.FILEGROWTH = 1 mbTOHLEGROUPWC FG8添加日志文件ALTER DATABASE testADD LOG FILENAME

8、= WC-LOG3,FILENAME = DWC FG3.LDF.SIZE= 1MB.MAXSIZE = 10MB.HLEGROWTH = 100KB-修改数据文件的大小，増长大小最大大小ALTER DATABASE testMODIFY FILENAME = WC-FG8；SIZE = 2MB-必须大于之前的大小，否则报错MAXSIZE= 8MBHLEGROWTH = 10%-修改数据文件或日志文件的逻辑名称ALTER DATABASE testMODIFY FILENAME = WC-LOG3.NEWNAME = WC FG339、移动文件-由于在SQLServer中文件组、文件不能离线，

9、所以必须把整个数据库设置为离线check poini goALTER DATABASE WCSETOFFLINE g-修改文件名称ALTER DATABASE WCMODIFY FILENAME = WC_fg &FILENAME = DWCWC FG&NDF go把原来的文件复制到新的位置：D:WCWC_FG&NDF-设置数据库在线ALTER DATABASE WCSETONLINE10、设置默认文件组、只读文件组-设置默认文件组ALTER DATABASE WCMODIFY HLEGROUP WC FG8 DEFAULT-设为只读文件组-如果文件已经是某个属性不能再次设置柑同属性ALTER

10、 DATABASE WCMODIFY HLEGROUP WC FG8 READ WRITE11、收缩数据库、收缩文件-收缩数据原DBCC SHRINKDATABASE(tesl10-要收缩的数据库名称或数拯库ID-收缩后，数据库文件中空间空间占用的百分比DBCC SHRINKDATABASE(乜ST,10.“要收缩的数据库名称或数据库ID收缩后，数据库文件中空闲空间占用的百分比NOTRUNCATE-在收缩时，通过数据移动来腾出自由空间DBCC SHRINKDATABASE(-tesf,-要收缩的数据库名称或数据库ID“收缩后，数据库文件中空间空间占用的百分比10.TRUNCATEONLY 一在

11、收缩时，只是耙文件尾部的空闲空间释放收缩文件DBCC SHRINKFlLE(wcg& -要收缩的数据文件逻辑坍称7 要收缩的目标大小，以MB为单位DBCC SHRINKFILE(wc_fg& 一要收缩的数据文件逻辑名称EMPTYRLE 淸空文件，淸空文件后才可以删除文件12、删除文件、删除文件组1要删除文件，必须要先把文件上的数据删除，或者移动到其他文件或文件组上删除数据后，必须要淸空文件的内容DBCC SHRINKFILE(WC_FG&EMPTYF1LE)-删除文件，同时也在文件系统底层删除了文件ALTER DATABASE testREMOVE HLE WC FG82要删除文件组，必须先删

12、除所有文件-最后删除文件组ALTER DATABASE testREMOVE HLEGROUP WC FG813、重新组织索引ALTER INDEX idxjempjockjd ON dbo.tempjock REORGANIZEWITH ( LOB_COMPACTION = ON ) use test go select DBCC INDEXDEFRAGC+db_name()+7+o.name+7+i name +-.db_name().-o.nanie.Lname.from sysindexes i inner join sysobjecls oon Lid = o.idwhere o.x

13、iypc = Uand Undid 0and charindexCW_Sysi.name) = 014、重新生成索引ALTER INDEX idxjcmpJockJd ON dbo.tempJockREBUILD PARTITION = ALLWITH ( PAD JNDEX =OFESTATISTICS NORECOMPUTE =OFEALLOW ROW LOCKS = ON,ALLOW PAGE LOCKS = ON.ONLINE = OFF.SORTJN_TEMPDB = OFF)15、更新统计信息-更新表中某个的统计倍息update statistics tempJock(_WA_Sy

14、s_0000000 l_07020F21) update statistics tempJock(_WA_Sys_0000000 l_07020F21) with sample 50 percent update statistics tempJock(_WA_Sys_0000000 l_07020F21)with resample. 使用最近的采样速率更新每个统讣信息norecompute -査询优化器将完成此统计信息更新并禁用将来的更新-更新索引的统计信息up date statistics tempjock(idx_tempjockjd) with fullscan-更新表的所有统计信息update statistics txtwith all16、执行SQLServer代理作业 exec msdbdbosp_siartobjob_name =Nob_update_sql;