sqlserver数据库日常维护规范.pdf
1.数据库服务器负载情况维护 查看 CPU，IO，内存使用情况 规则 Windows 系统:打开任务管理器，选择进程选项卡，可以查看 CPU、内存和 IO 使用率。2.数据库运行状态维护 规则 1.实例运行状态 使用 SQL SERVER Management Studio 能连接说明实例运行正常。2.磁盘空间使用情况 直接登陆服务器进行磁盘使用率检查 3.查看错误日志:方法一：可以通过执行该命令来查看错误日志信息：exec xp_readerrorlog 方法二：在 SQL SERVER Management Studio 中连接该 sql server 实例，object 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.数据库备份情况维护 规则 数据库备份情况检查：备份日志显示:成功,说明备份成功。备份日志显示：失败,说明备份失败，需要进一步查找失败原因。每天检查异地备份是否成功。4.数据库对象的维护 规则 1、查询各个磁盘分区的剩余空间：Exec master.dbo.xp_fixeddrives 欢迎下载 2 2、查询数据库的数据文件及日志文件的相关信息（包括文件组、当前文件大小、文件最大值、文件增长设置、文件逻辑名、文件路径等）select*from 数据库名.dbo.sysfiles 转换文件大小单位为 MB：select name,convert(float,size)*(8192.0/1024.0)/1024.from 数据库名.dbo.sysfiles 3、查询当前数据库的磁盘使用情况：Exec 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 command1=sp_spaceused?;select*from tmp where name tmp order by name drop table tmp;或者 SELECT a.name,b.rows FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id=b.id WHERE (a.type=u)AND(b.indid IN(0,1)ORDER BY b.rows DESC 6、检查数据库完整性 dbcc checkdb(Portal)欢迎下载 3 dbcc checkdb(Portal)with tablock 7、数据库重命名、修改恢复模式、修改用户模式 -数据库重命名 ALTER DATABASE WC MODIFY NAME=test -设置数据库为完整恢复模式 alter database test set 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 test ADD FILEGROUP WC_FG8 欢迎下载 4 -添加数据文件 ALTER DATABASE test ADD FILE(NAME=WC_FG8,FILENAME=D:WC_FG8.ndf,SIZE=1mb,MAXSIZE=10mb,FILEGROWTH=1mb)TO FILEGROUP WC_FG8 -添加日志文件 ALTER DATABASE test ADD LOG FILE(NAME=WC_LOG3,FILENAME=D:WC_FG3.LDF,SIZE=1MB,MAXSIZE=10MB,FILEGROWTH=100KB)-修改数据文件的大小,增长大小,最大大小 ALTER DATABASE test MODIFY FILE(NAME=WC_FG8,SIZE=2MB,-必须大于之前的大小,否则报错 MAXSIZE=8MB,FILEGROWTH=10%)-修改数据文件或日志文件的逻辑名称 欢迎下载 5 ALTER DATABASE test MODIFY FILE(NAME=WC_LOG3,NEWNAME=WC_FG33)9、移动文件 -由于在 SQL Server 中文件组、文件不能离线,所以必须把整个数据库设置为离线 checkpoint go ALTER DATABASE WC SET OFFLINE go -修改文件名称 ALTER DATABASE WC MODIFY FILE(NAME=WC_fg8,FILENAME=D:WCWC_FG8.NDF)go -把原来的文件复制到新的位置：D:WCWC_FG8.NDF-设置数据库在线 ALTER DATABASE WC SET ONLINE 10、设置默认文件组、只读文件组 -设置默认文件组 欢迎下载 6 ALTER DATABASE WC MODIFY FILEGROUP WC_FG8 DEFAULT -设为只读文件组-如果文件已经是某个属性，不能再次设置相同属性 ALTER DATABASE WC MODIFY FILEGROUP WC_FG8 READ_WRITE 11、收缩数据库、收缩文件 -收缩数据库 DBCC SHRINKDATABASE(test,-要收缩的数据库名称或数据库 ID 10 -收缩后，数据库文件中空间空间占用的百分比 )DBCC SHRINKDATABASE(test,-要收缩的数据库名称或数据库 ID 10,-收缩后，数据库文件中空闲空间占用的百分比 NOTRUNCATE-在收缩时，通过数据移动来腾出自由空间 )DBCC SHRINKDATABASE(test,-要收缩的数据库名称或数据库 ID 10,-收缩后，数据库文件中空间空间占用的百分比 TRUNCATEONLY-在收缩时，只是把文件尾部的空闲空间释放 )-收缩文件 DBCC SHRINKFILE(wc_fg8,-要收缩的数据文件逻辑名称 7 -要收缩的目标大小，以 MB 为单位 )欢迎下载 7 DBCC SHRINKFILE(wc_fg8,-要收缩的数据文件逻辑名称 EMPTYFILE-清空文件，清空文件后，才可以删除文件 )12、删除文件、删除文件组 1.要删除文件，必须要先把文件上的数据删除，或者移动到其他文件或文件组上 -删除数据后，必须要清空文件的内容 DBCC SHRINKFILE(WC_FG8,EMPTYFILE)-删除文件，同时也在文件系统底层删除了文件 ALTER DATABASE test REMOVE FILE WC_FG8 2.要删除文件组，必须先删除所有文件 -最后删除文件组 ALTER DATABASE test REMOVE FILEGROUP WC_FG8 13、重新组织索引 ALTER INDEX idx_temp_lock_id ON dbo.temp_lock REORGANIZE WITH(LOB_COMPACTION=ON)use test go select DBCC INDEXDEFRAG(+db_name()+,+o.name+,+i.name+);-,db_name(),欢迎下载 8 -o.name,-i.name,-i.*from sysindexes i inner join sysobjects o on i.id=o.id where o.xtype=U and i.indid 0 and charindex(WA_Sys,i.name)=0 14、重新生成索引 ALTER INDEX idx_temp_lock_id ON dbo.temp_lock REBUILD PARTITION=ALL WITH(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 percent update statistics temp_lock(_WA_Sys_00000001_07020F21)欢迎下载 9 with resample,-使用最近的采样速率更新每个统计信息 norecompute -查询优化器将完成此统计信息更新并禁用将来的更新 -更新索引的统计信息 update statistics temp_lock(idx_temp_lock_id)with fullscan -更新表的所有统计信息 update statistics txt with all 16、执行 SQL Server 代理作业 exec msdb.dbo.sp_start_job job_name=Njob_update_sql;
