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

1. 数据库效劳器负载情况维护查看CPU，IO，内存使用情况l 规则Windows系统:翻开任务管理器，选择进程选项卡，可以查看CPU、内存和IO使用率2. 数据库运行状态维护l 规则1. 实例运行状态使用SQL SERVER Management Studio能连接说明实例运行正常2. 磁盘空间使用情况直接登陆效劳器进展磁盘使用率检查3. 查看错误日志:方法一：可以通过执行该命令来查看错误日志信息：e*ec *p_readerrorlog 方法二：在SQL SERVER Management Studio中连接该sql server实例，object E*plorer中查找Management->SQL Server logs->右键选view-"选择你要看的日志信息〔sql server log or sql server and Windows log〕方法三：去安装路径下去找你的log文件，我的默认实例路径如下driver:\SQL Server 2008 R2\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG 4．跟踪事件企业管理里SQL2005\SQL2008在上方菜单—工具—SQL Profiler，按照选择进展跟踪。

3. 数据库备份情况维护l 规则数据库备份情况检查：备份日志显示:成功,说明备份成功备份日志显示：失败,说明备份失败，需要进一步查找失败原因每天检查异地备份是否成功4. 数据库对象的维护l 规则1、查询各个磁盘分区的剩余空间：2、查询数据库的数据文件及日志文件的相关信息〔包括文件组、当前文件大小、文件最大值、文件增长设置、文件逻辑名、文件路径等〕select * from [数据库名].[dbo].[sysfiles]转换文件大小单位为MB：select name, convert(float,size) * (8192.0/1024.0)/1024. from [数据库名].dbo.sysfiles3、查询当前数据库的磁盘使用情况：E*ec sp_spaceused４、查询数据库效劳器各数据库日志文件的大小及利用率DBCC SQLPERF(LOGSPACE)5、统计数据库中每张表的大小create table tmp( name varchar(50), rowscount int, reserved varchar(50), data varchar(50), inde*_size varchar(50), unused varchar(50));insert tmp(name, rowscount, reserved, data, inde*_size, unused) e*ec sp_MSforeachtable mand1="sp_spaceused '"'";select * from tmp where name <> 'tmp' order by namedrop table tmp ;或者SELECT a.name, b.rowsFROM sysobjects AS a INNER JOIN sysinde*es 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、数据库重命名、修改恢复模式、修改用户模式--数据库重命名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 immediate --立即回滚事务--多用户模式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, MA*SIZE = 10mb, FILEGROWTH = 1mb)TO FILEGROUP WC_FG8--添加日志文件ALTER DATABASE testADD LOG FILE( NAME = WC_LOG3, FILENAME = 'D:\WC_FG3.LDF', SIZE = 1MB, MA*SIZE = 10MB, FILEGROWTH = 100KB)--修改数据文件的大小,增长大小,最大大小ALTER DATABASE testMODIFY FILE( NAME = 'WC_FG8', SIZE = 2MB, --必须大于之前的大小,否则报错 MA*SIZE= 8MB, FILEGROWTH = 10%)--修改数据文件或日志文件的逻辑名称ALTER DATABASE testMODIFY FILE( NAME = WC_LOG3, NEWNAME = WC_FG33) 9、移动文件--由于在SQL Server中文件组、文件不能离线,所以必须把整个数据库设置为离线checkpointgoALTER DATABASE WCSET OFFLINEgo--修改文件名称ALTER DATABASE WCMODIFY FILE( NAME = WC_fg8, FILENAME = 'D:\WC\WC_FG8.NDF')go--把原来的文件复制到新的位置：'D:\WC\WC_FG8.NDF'--设置数据库ALTER DATABASE WCSET ONLINE10、设置默认文件组、只读文件组--设置默认文件组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, --收缩后，数据库文件中空闲空间占用的百分比 NOTRUNCATE --在收缩时，通过数据移动来腾出自由空间 ) DBCC SHRINKDATABASE('test', --要收缩的数据库名称或数据库ID 10, --收缩后，数据库文件中空间空间占用的百分比 TRUNCATEONLY --在收缩时，只是把文件尾部的空闲空间释放 ) --收缩文件DBCC SHRINKFILE(wc_fg8, --要收缩的数据文件逻辑名称 7 --要收缩的目标大小，以MB为单位 ) DBCC SHRINKFILE(wc_fg8, --要收缩的数据文件逻辑名称 EMPTYFILE --清空文件，清空文件后，才可以删除文件 )12、删除文件、删除文件组1.要删除文件，必须要先把文件上的数据删除，或者移动到其他文件或文件组上--删除数据后，必须要清空文件的内容DBCC SHRINKFILE(WC_FG8,EMPTYFILE)--删除文件，同时也在文件系统底层删除了文件ALTER DATABASE testREMOVE FILE WC_FG82.要删除文件组，必须先删除所有文件--最后删除文件组ALTER DATABASE testREMOVE FILEGROUP WC_FG8 13、重新组织索引ALTER INDE* [id*_temp_lock_id] ON [dbo].[temp_lock] REORGANIZE WITH ( LOB_PACTION = ON )use testgoselect 'DBCC INDE*DEFRAG('+db_name()+','+o.name+','+i.name + ');' --,db_name(), --o.name, --i.name, --i.*from sysinde*es iinner join sysobjects o on i.id = o.idwhere o.*type = 'U' and i.indid >0 and charinde*('WA_Sys',i.name) = 014、重新生成索引ALTER INDE* [id*_temp_lock_id] ON [dbo].[temp_lock] REBUILD PARTITION = ALLWITH ( PAD_INDE* = OFF, STATISTICS_NOREPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )15、更新统计信息--更新表中*个的统计信息update statis。