sqlserver健康检查报告-范本

-北京东方宏达科技有限公司sqlserver数据库配置及性能检查报告作者:刘东阳创建日期:2014年01月09日最后修改日期:1. 文档控制修改记录日期作者版本修改记录2014-01-09刘东阳1.0Initial分发者公司北京移动审阅记录姓名职位相关文档2. 目录1.文档控制ii修改记录ii分发者ii审阅记录ii相关文档ii2.目录iii3.检查总结4概要4建议44.介绍4目标4检查方法5检查范围55.操作系统5系统版本5用户安全6系统性能6进程6文件系统7网络状态8系统日志9服务启动状态10系统的定时作业10病毒库更新106.数据库配置11数据库基本信息11数据库字符集11操作系统参数12数据库参数12数据库启动时间14数据库用户14数据库数据文件15事务日志16数据库进程17数据库完整性18数据库备份及维护计划21数据库服务及组件21数据库中运行的job217.数据库性能23数据库服务23数据库进程占用系统资源情况23精选资料3. 检查总结概要此次数据库检查包括以下内容： 主机相关配置：CPU、内存操作系统配置：版本，补丁，病毒库操作系统性能数据库配置文件系统可用空间数据库版本数据库参数运行日志和跟踪文件数据库备份日志文件数据文件数据库的组件，补丁数据库各组件运行状态资源占用情况数据库性能建议以下是本次检查发现的一些主要问题和建议的总结。No.问题描述建议建议解决时间1采用数据库镜像技术，备份了生产库，但系统库没有备份。登陆实例的用户信息和相关的一些代理设置没有备份。备份系统库近期23检查方面评价/发现情况主机配置主机配置基本合理，满足当前系统的需要。操作系统性能内存正常，CPU资源正常。数据库配置数据库配置很好数据库性能数据库性能较好4. 介绍目标数据库配置/性能检查是用来：- 评价数据库当前配置及性能情况- 分析数据库应用瓶颈和资源竞争情况- 指出存在的问题，提出解决建议检查方法本次数据库性能检查的工具是： 操作系统工具和命令检查操作系统。 SQL命令检查数据库配置，SQL命令在sqlserver工具查询分析器中运行。检查范围本报告提供的检查和建议主要针对以下方面：1. 主机配置2. 操作系统性能3. 数据库配置4. 数据库性能本报告的提供的检查和建议不涉及：- 具体的性能调整- 应用程序的具体细节5. 操作系统这个部分详细阐述了系统的主要结构。winmsd.exe，systeminfo系统版本用户安全正常 正常。系统性能监控系统性能是否有不稳定的情况发生。从16:30直到巡检结束19:20cpu与内存的最高峰。正常。文件系统正常网络状态 C:Usersadministrator.BJXNHipconfigWindows IP ConfigurationEthernet adapter Local Area Connection 2: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80:a1fc:86d3:8fd8:957e%13 IPv4 Address. . . . . . . . . . . : 132.78.208.80 Subnet Mask . . . . . . . . . . . : 255.255.255.128 Default Gateway . . . . . . . . . :Ethernet adapter Local Area Connection: Connection-specific DNS Suffix . : Link-local IPv6 Address . . . . . : fe80:1b3:9985:9f55:d9a6%12 IPv4 Address. . . . . . . . . . . : 132.78.208.152 Subnet Mask . . . . . . . . . . . : 255.255.255.224 Default Gateway . . . . . . . . . : 132.78.208.158Tunnel adapter isatap.A800EE55-1DE0-42CA-B8AF-764C37EF0D4B: Media State . . . . . . . . . . . : Media disconnected Connection-specific DNS Suffix . :Tunnel adapter Local Area Connection* 11: Connection-specific DNS Suffix . : IPv6 Address. . . . . . . . . . . : 2002:844e:d050:844e:d050 IPv6 Address. . . . . . . . . . . : 2002:844e:d098:844e:d098 Default Gateway . . . . . . . . . : 2002:c058:6301:c058:6301Tunnel adapter isatap.D465440F-528A-4599-AD14-24491F59F06E: Media State . . . . . . . . . . . : Media disconnected Connection-specific DNS Suffix . :C:Usersadministrator.BJXNHping 132.78.208.158 -tPinging 132.78.208.158 with 32 bytes of data:Reply from 132.78.208.158: bytes=32 time=2ms TTL=255Reply from 132.78.208.158: bytes=32 time=1ms TTL=255Reply from 132.78.208.158: bytes=32 time=3ms TTL=255Reply from 132.78.208.158: bytes=32 time=18ms TTL=255Reply from 132.78.208.158: bytes=32 time=2ms TTL=255Reply from 132.78.208.158: bytes=32 time=1ms TTL=255Reply from 132.78.208.158: bytes=32 time=2ms TTL=255Reply from 132.78.208.158: bytes=32 time=2ms TTL=255Reply from 132.78.208.158: bytes=32 time=2ms TTL=255Reply from 132.78.208.158: bytes=32 time=3ms TTL=255Reply from 132.78.208.158: bytes=32 time=2ms TTL=255Reply from 132.78.208.158: bytes=32 time=3ms TTL=255Reply from 132.78.208.158: bytes=32 time=2ms TTL=255Reply from 132.78.208.158: bytes=32 time=2ms TTL=255Reply from 132.78.208.158: bytes=32 time=1ms TTL=255Reply from 132.78.208.158: bytes=32 time=2ms TTL=255Reply from 132.78.208.158: bytes=32 time=1ms TTL=255Reply from 132.78.208.158: bytes=32 time=2ms TTL=255Ping statistics for 132.78.208.158: Packets: Sent = 18, Received = 18, Lost = 0 (0% loss),Approximate round trip times in milli-seconds: Minimum = 1ms, Maximum = 18ms, Average = 2msControl-C 没有丢包，正常。系统日志日志查看，可以查看服务器的各种服务的运行是否报错，系统的运行是否报错，针对单独的错误，我们可以看是否存在问题，系统日志存在打印机驱动报错（可以忽略），系统日志正常。查看应用程序日志，看是否有关键业务，及关键系统报错，如果是一些无关的应用报错可以忽略,应用程序在2014/1/5 2:09:48存在报错。Event ID:1479：The mirroring connection to TCP:/132.78.208.147:5022 has timed out for database VDesktop6000 after 10 seconds without a response. Check the service and network connections.之后没有报错，应该问题处理完成。应用程序日志正常。正常服务启动状态查看服务器的服务启动状态，确认服务器基本服务和应用服务已经启动。正常。系统的定时作业没有任务计划在执行。正常。病毒库更新无正常。6. 数据库配置实例中生产库XenDesktop数据库配置了到147的镜像备份库，如果生产库发生问题可以使用镜像库进行切换恢复。数据库基本信息数据库的当前版本select versionMicrosoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7600: ) (Hypervisor)数据库实例select servernameBJXNH2DB02数据库字符集sp_helpsort/数据库属性里面查看Chinese-PRC, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive操作系统参数exec master.xp_msver1ProductNameNULLMicrosoft SQL Server2ProductVersion65541010.50.1600.13Language1033English (United States)4PlatformNULLNT x645CommentsNULLSQL6CompanyNameNULLMicrosoft Corporation7FileDescriptionNULLSQL Server Windows NT - 64 Bit8FileVersionNULL2009.0100.1600.01 (KJ_RTM).100402-1539 )9InternalNameNULLSQLSERVR10LegalCopyrightNULLMicrosoft Corp. All rights reserved.11LegalTrademarksNULLMicrosoft SQL Server is a registered trademark of Microsoft Corporation.12OriginalFilenameNULLSQLSERVR.EXE13PrivateBuildNULLNULL14SpecialBuild104857601NULL15WindowsVersion4980738626.1 (7600)16ProcessorCount2217ProcessorActiveMaskNULL 318ProcessorType8664NULL19PhysicalMemory40964096 (4294565888)20Product IDNULLNULL数据库参数数据库启动参数exec sp_configure/SELECT * FROM sys.configurations;allow updates0100backup compression default0100clr enabled0100cross db ownership chaining0100default language0999900filestream access level0200max text repl size (B)-121474836476553665536nested triggers0111remote access0111remote admin connections0100remote login timeout (s)021474836472020remote proc trans0100remote query timeout (s)02147483647600600server trigger recursion0111show advanced options0100user options03276700正常数据库启动时间查看数据库运行时间select convert(varchar(30),login_time,120) from master.sysprocesses where spid=12014-01-04 08:43:30正常。数据库用户数据库用户的登录信息exec sp_helplogins#MS_AgentSigningCertificate#0x01060000000000090100000046118475F61285F02F9210FAF708E0B1B357ABF7masterus_englishyes no #MS_PolicyEventProcessingLogin#0x4EAF544D095570419CF280E6C9D103A6masterus_englishyes no #MS_PolicySigningCertificate#0x0106000000000009010000005DB061F0B3BF9B487E202EF4BBCDCEA6299A157AmasterNULL? no #MS_PolicyTsqlExecutionLogin#0x014EA8886B841C4CA1F7ED32489BBF62masterus_englishyes no #MS_SmoExtendedSigningCertificate#0x01060000000000090100000064FC3D29EAB41849908D7F3B6B975941E654941AmasterNULL? no #MS_SQLAuthenticatorCertificate#0x010600000000000901000000B2CD74E94B9C1B6756EA8BA882E64BA8554DA783masterNULL? no #MS_SQLReplicationSigningCertificate#0x010600000000000901000000AC5DA6E535933FFD392B6A536EC661B5F6A39563masterNULL? no #MS_SQLResourceSigningCertificate#0x01060000000000090100000041AB05E0B31EB78666287ED3EB07A4C7E52E4857masterNULL? no BJ-ITA2DB0x60BC724A47710042B34704C8C2ECE7FDmasterus_englishyes no bjxnhBJ-DDCsvc0x0105000000000005150000005E7DB098F01B1F85A8E79D355E040000MASTERus_englishyes no BJXNHBJ-SQLsvc0x0105000000000005150000005E7DB098F01B1F85A8E79D355F040000masterus_english? no BJXNHBJXNH2DDC01$0x0105000000000005150000005E7DB098F01B1F85A8E79D35F6070000masterus_englishyes no BJXNHBJXNH2DDC02$0x0105000000000005150000005E7DB098F01B1F85A8E79D35F7070000masterus_englishyes no NT AUTHORITYSYSTEM0x010100000000000512000000masterus_english? no NT SERVICEMSSQLSERVER0x010600000000000550000000E20F4FE7B15874E48E19026478C2DC9AC307B83Emasterus_english? no NT SERVICESQLSERVERAGENT0x010600000000000550000000DCA88F14B79FD47A992A3D8943F829A726066357masterus_english? no sa0x01masterus_englishyes no 生产数据库用户权限应该使用的是sa用户作为生产用户。正常。数据库数据文件系统文件系统正常数据库大小exec sp_spaceusedXenDesktop2025.69 MB2.86 MB数据库状态exec sp_helpdbmaster 5.25 MBsa1Apr 8 2003Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=661, Collation=Chinese_PRC_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics100model 3.00 MBsa3Apr 8 2003Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=661, Collation=Chinese_PRC_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics100msdb 17.81 MBsa4Apr 2 2010Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=661, Collation=Chinese_PRC_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled100tempdb 9.25 MBsa2Jan 4 2014Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=661, Collation=Chinese_PRC_CI_AS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics100XenDesktop 2025.69 MBsa6Apr 2 2013Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=661, Collation=Latin1_General_CI_AS_KS, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled100正常事务日志记录数据库操作信息，对于基于时间点恢复操作非常重要。日志文件大小dbcc sqlperf(logspace)master1.24218855.660380tempdb1.24218843.396220model0.742187550.526320msdb3.05468836.700770XenDesktop2001.436.1984990数据文件及日志文件管理数据文件管理采用自动增长的方式，无最大限制，自动增长比例是1m. 日志文件管理采用自动增长的方式，每次增长200M，最大值是d盘的空间。正常数据库进程活动进程exec sp_who active10background sa 20background sa 30background sa 40background sa 50background sa 60background sa 70background sa 80sleeping sa 90background sa 100background sa 110background sa 120background sa 130sleeping sa 140sleeping sa 150sleeping sa 160background sa 170background sa 171background sa 172background sa 180sleeping sa 190background sa 200background sa 210background sa 220sleeping sa 230sleeping sa 240background sa 250sleeping sa 260background sa 280sleeping sa 300background sa 310sleeping sa 350background sa 360background sa 600runnable BJ-ITA2DBBJXNH2DB020 VDesktop6000BakSELECT 0240sleeping sa 0 masterTASK MANAGER 0250backgroundsa 0 masterBRKR TASK 0260sleeping sa 0 masterTASK MANAGER 0270sleeping sa 0 masterTASK MANAGER 0290backgroundsa 0 masterBRKR TASK 0300sleeping sa 0 masterTASK MANAGER 0320backgroundsa 0 NULLDB MIRROR 0330sleeping sa 0 masterTASK MANAGER 01270runnable saBJXNH2DB010 VDesktop6000BakSELECT 锁资源使用情况exec sp_lock51600DBSGRANT52600DBSGRANT53600DBSGRANT54600DBSGRANT55600DBSGRANT56600DBSGRANT59600DBSGRANT60600DBSGRANT60111311510750TABISGRANT61600DBSGRANT62600DBSGRANT63600DBSGRANT64600DBSGRANT65600DBSGRANT66600DBSGRANT67600DBSGRANT68600DBSGRANT71600DBSGRANT73600DBSGRANT74600DBSGRANT86400DBSGRANT105400DBSGRANT正常建议：无。数据库完整性检查数据库中所有的对象分配和机构完整性是否存在错误，如果有报错需要及时排查处理dbcc checkdbdbcc checkdbDBCC results for XenDesktop.Service Broker Msg 9675, State 1: Message Types analyzed: 14.Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.Service Broker Msg 9667, State 1: Services analyzed: 3.Service Broker Msg 9668, State 1: Service Queues analyzed: 3.Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.Service Broker Msg 9605, State 1: Conversation Priorities analyzed: 0.DBCC results for sys.sysrscols.There are 1861 rows in 17 pages for object sys.sysrscols.DBCC results for sys.sysrowsets.There are 381 rows in 4 pages for object sys.sysrowsets.DBCC results for sys.sysallocunits.There are 454 rows in 5 pages for object sys.sysallocunits.DBCC results for sys.sysfiles1.There are 2 rows in 1 pages for object sys.sysfiles1.DBCC results for sys.syspriorities.There are 0 rows in 0 pages for object sys.syspriorities.DBCC results for sys.sysfgfrag.There are 2 rows in 1 pages for object sys.sysfgfrag.DBCC results for sys.sysphfg.There are 1 rows in 1 pages for object sys.sysphfg.DBCC results for sys.sysprufiles.There are 2 rows in 1 pages for object sys.sysprufiles.DBCC results for sys.sysftinds.There are 0 rows in 0 pages for object sys.sysftinds.DBCC results for sys.sysowners.There are 24 rows in 1 pages for object sys.sysowners.DBCC results for sys.sysprivs.There are 843 rows in 5 pages for object sys.sysprivs.DBCC results for sys.sysschobjs.There are 1760 rows in 31 pages for object sys.sysschobjs.DBCC results for sys.syscolpars.There are 3630 rows in 63 pages for object sys.syscolpars.DBCC results for sys.sysnsobjs.There are 1 rows in 1 pages for object sys.sysnsobjs.DBCC results for sys.syscerts.There are 0 rows in 0 pages for object sys.syscerts.DBCC results for sys.sysxprops.There are 0 rows in 0 pages for object sys.sysxprops.DBCC results for sys.sysscalartypes.There are 296 rows in 3 pages for object sys.sysscalartypes.DBCC results for sys.systypedsubobjs.There are 0 rows in 0 pages for object sys.systypedsubobjs.DBCC results for sys.sysidxstats.There are 590 rows in 12 pages for object sys.sysidxstats.DBCC results for sys.sysiscols.There are 840 rows in 6 pages for object sys.sysiscols.DBCC results for sys.sysbinobjs.There are 23 rows in 1 pages for object sys.sysbinobjs.DBCC results for sys.sysaudacts.There are 0 rows in 0 pages for object sys.sysaudacts.DBCC results for sys.sysobjvalues.There are 1826 rows in 410 pages for object sys.sysobjvalues.DBCC results for sys.sysclsobjs.There are 23 rows in 1 pages for object sys.sysclsobjs.DBCC results for sys.sysrowsetrefs.There are 0 rows in 0 pages for object sys.sysrowsetrefs.DBCC results for sys.sysremsvcbinds.There are 0 rows in 0 pages for object sys.sysremsvcbinds.DBCC results for sys.sysxmitqueue.There are 0 rows in 0 pages for object sys.sysxmitqueue.DBCC results for sys.sysrts.There are 1 rows in 1 pages for object sys.sysrts.DBCC results for sys.sysconvgroup.There are 0 rows in 0 pages for object sys.sysconvgroup.DBCC results for sys.sysdesend.There are 0 rows in 0 pages for object sys.sysdesend.DBCC results for sys.sysdercv.There are 0 rows in 0 pages for object sys.sysdercv.DBCC results for sys.syssingleobjrefs.There are 565 rows in 4 pages for object sys.syssingleobjrefs.DBCC results for sys.sysmultiobjrefs.There are 5944 rows in 40 pages for object sys.sysmultiobjrefs.DBCC results for sys.sysguidrefs.There are 0 rows in 0 pages for object sys.sysguidrefs.DBCC results for sys.syscompfragments.There are 0 rows in 0 pages for object sys.syscompfragments.DBCC results for sys.sysftstops.There are 0 rows in 0 pages for object sys.sysftstops.DBCC results for sys.sysqnames.There are 97 rows in 1 pages for object sys.sysqnames.DBCC results for sys.sysxmlcomponent.There are 99 rows in 1 pages for object sys.sysxmlcomponent.DBCC results for sys.sysxmlfacet.There are 112 rows in 1 pages for object sys.sysxmlfacet.DBCC results for sys.sysxmlplacement.There are 18 rows in 1 pages for object sys.sysxmlplacement.DBCC results for sys.sysobjkeycrypts.There are 0 rows in 0 pages for object sys.sysobjkeycrypts.DBCC results for sys.sysasymkeys.There are 0 rows in 0 pages for object sys.sysasymkeys.DBCC results for sys.syssqlguides.There are 0 rows in 0 pages for object sys.syssqlguides.DBCC results for sys.sysbinsubobjs.There are 3 rows in 1 pages for object sys.sysbinsubobjs.DBCC results for sys.syssoftobjrefs.There are 3304 rows in 56 pages for object sys.syssoftobjrefs.DBCC results for HostingUnitServiceSchema.HypervisorConnectionAddress.There are 0 rows in 0 pages for object HostingUnitServiceSchema.HypervisorConnectionAddress.DBCC results for ConfigurationSchema.SchemaFeatures.There are 1 rows in 1 pages for object