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

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

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

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

4、 sp_spaceused、查询数据库服务器各数据库日志文件的大小及利用率DBCC SQLPERF(LOGSPACE)5、 统计数据库中每张表的大小create table tmp( name varchar(50), rowscount int, reserved varchar(50), data varchar(50), index_size varchar(50), unused varchar(50); insert tmp(name, rowscount, reserved, data, index_size, unused) exec sp_MSforeachtable comm

5、and1=sp_spaceused ?;select * from tmp where name tmp order by namedrop table tmp ;或者SELECT a.name, b.rowsFROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.idWHERE (a.type = u) AND (b.indid IN (0, 1)ORDER BY b.rows DESC6、检查数据库完整性 dbcc checkdb(Portal) dbcc checkdb(Portal) with tablock7、数据库重命

6、名、修改恢复模式、修改用户模式 -数据库重命名 ALTER DATABASE WC MODIFY NAME = test -设置数据库为完整恢复模式alter database testset recovery full-只允许一个用户访问数据库 alter database test set single_user with rollback after 10 seconds -指定多少秒后回滚事务 -只有sysadmin,dbcreator,db_owner角色的成员可以访问数据库 alter database wc set restricted_user with rollback im

7、mediate -立即回滚事务 -多用户模式 alter database wc set multi_user with no_wait -不等待立即改变，如不能立即完成，那么会导致执行错误 8、扩展数据库：增加文件组、增加文件、修改文件大小、修改文件的逻辑名称 -添加文件组ALTER DATABASE testADD FILEGROUP WC_FG8-添加数据文件ALTER DATABASE testADD FILE( NAME = WC_FG8, FILENAME = D:WC_FG8.ndf, SIZE = 1mb, MAXSIZE = 10mb, FILEGROWTH = 1mb)TO

8、 FILEGROUP WC_FG8 -添加日志文件ALTER DATABASE testADD LOG FILE( NAME = WC_LOG3, FILENAME = D:WC_FG3.LDF, SIZE = 1MB, MAXSIZE = 10MB, FILEGROWTH = 100KB) -修改数据文件的大小,增长大小,最大大小ALTER DATABASE testMODIFY FILE( NAME = WC_FG8, SIZE = 2MB, -必须大于之前的大小,否则报错 MAXSIZE= 8MB, FILEGROWTH = 10%) -修改数据文件或日志文件的逻辑名称ALTER DAT

9、ABASE testMODIFY FILE( NAME = WC_LOG3, NEWNAME = WC_FG33) 9、移动文件 -由于在SQL Server中文件组、文件不能离线,所以必须把整个数据库设置为离线checkpointgo ALTER DATABASE WCSET OFFLINEgo -修改文件名称ALTER DATABASE WCMODIFY FILE( NAME = WC_fg8, FILENAME = D:WCWC_FG8.NDF)go -把原来的文件复制到新的位置：D:WCWC_FG8.NDF-设置数据库在线ALTER DATABASE WCSET ONLINE10、设置

10、默认文件组、只读文件组 -设置默认文件组ALTER DATABASE WCMODIFY FILEGROUP WC_FG8 DEFAULT -设为只读文件组-如果文件已经是某个属性，不能再次设置相同属性ALTER DATABASE WCMODIFY FILEGROUP WC_FG8 READ_WRITE11、收缩数据库、收缩文件 -收缩数据库 DBCC SHRINKDATABASE(test, -要收缩的数据库名称或数据库ID 10 -收缩后，数据库文件中空间空间占用的百分比 ) DBCC SHRINKDATABASE(test, -要收缩的数据库名称或数据库ID 10, -收缩后，数据库文件中

11、空闲空间占用的百分比 NOTRUNCATE -在收缩时，通过数据移动来腾出自由空间 ) DBCC SHRINKDATABASE(test, -要收缩的数据库名称或数据库ID 10, -收缩后，数据库文件中空间空间占用的百分比 TRUNCATEONLY -在收缩时，只是把文件尾部的空闲空间释放 ) -收缩文件 DBCC SHRINKFILE(wc_fg8, -要收缩的数据文件逻辑名称 7 -要收缩的目标大小，以MB为单位 ) DBCC SHRINKFILE(wc_fg8, -要收缩的数据文件逻辑名称 EMPTYFILE -清空文件，清空文件后，才可以删除文件 ) 12、删除文件、删除文件组1.要

12、删除文件，必须要先把文件上的数据删除，或者移动到其他文件或文件组上 -删除数据后，必须要清空文件的内容DBCC SHRINKFILE(WC_FG8,EMPTYFILE) -删除文件，同时也在文件系统底层删除了文件ALTER DATABASE testREMOVE FILE WC_FG82.要删除文件组，必须先删除所有文件-最后删除文件组ALTER DATABASE testREMOVE FILEGROUP WC_FG8 13、重新组织索引 ALTER INDEX idx_temp_lock_id ON dbo.temp_lock REORGANIZE WITH ( LOB_COMPACTION

13、 = ON ) use testgo select DBCC INDEXDEFRAG(+db_name()+,+o.name+,+i.name + ); -,db_name(), -o.name, -i.name, -i.* from sysindexes iinner join sysobjects o on i.id = o.idwhere o.xtype = U and i.indid 0 and charindex(WA_Sys,i.name) = 014、重新生成索引 ALTER INDEX idx_temp_lock_id ON dbo.temp_lock REBUILD PART

14、ITION = ALLWITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF ) 15、更新统计信息 -更新表中某个的统计信息 update statistics temp_lock(_WA_Sys_00000001_07020F21) update statistics temp_lock(_WA_Sys_00000001_07020F21) with sample 50 perce

15、nt update statistics temp_lock(_WA_Sys_00000001_07020F21) with resample, -使用最近的采样速率更新每个统计信息 norecompute -查询优化器将完成此统计信息更新并禁用将来的更新 -更新索引的统计信息 update statistics temp_lock(idx_temp_lock_id) with fullscan -更新表的所有统计信息 update statistics txt with all16、执行SQL Server代理作业 exec msdb.dbo.sp_start_job job_name =Njob_update_sql;