SQL Server 数据库巡检脚本.docx

1. -1.查看数据库版本信息2. selectversion3. -2.查看所有数据库名称及大小4. execsp_helpdb5. -3.查看数据库所在机器的操作系统参数6. execmaster.xp_msver7. -4.查看数据库启动的参数8. execsp_configure9. -5.查看数据库启动时间10. selectconvert(varchar(30),login_time,120)11. frommaster.sysprocesseswherespid=112. -6.查看数据库服务器名13. selectServerName:+ltrim(servername)14. -7.查看数据库实例名15. selectInstance:+ltrim(servicename)16. -8.数据库的磁盘空间呢使用信息17. execsp_spaceused18. -9.日志文件大小及使用情况19. dbccsqlperf(logspace)20. -10.表的磁盘空间使用信息21. execsp_spaceusedtablename22. -11.获取磁盘读写情况23. select24. total_read读取磁盘次数,25. total_write写入磁盘次数,26. total_errors磁盘写入错误数,27. getdate()当前时间28. -12.获取I/O工作情况29. selectio_busy,30. timeticks每个时钟周期对应的微秒数,31. io_busy*timeticksI/O操作毫秒数,32. getdate()当前时间33. -13.查看CPU活动及工作情况34. select35. cpu_busy,36. timeticks每个时钟周期对应的微秒数,37. cpu_busy*cast(timeticksasfloat)/1000CPU工作时间(秒),38. idle*cast(timeticksasfloat)/1000CPU空闲时间(秒),39. getdate()当前时间40. -14.检查锁与等待41. execsp_lock42. -15.检查死锁43. execsp_who_lock-自己写个存储过程即可44. /*45. createproceduresp_who_lock46. as47. begin48. declarespidint,blint,49. intTransactionCountOnEntryint,50. intRowcountint,51. intCountPropertiesint,52. intCounterint53. createtable#tmp_lock_who(idintidentity(1,1),spidsmallint,blsmallint)54. IFERROR0RETURNERROR55. insertinto#tmp_lock_who(spid,bl)select0,blocked56. from(select*fromsys.sysprocesseswhereblocked0)a57. wherenotexists(select*from(select*fromsys.sysprocesseswhereblocked0)b58. wherea.blocked=spid)59. unionselectspid,blockedfromsys.sysprocesseswhereblocked060. IFERROR0RETURNERROR61. -找到临时表的记录数62. selectintCountProperties=Count(*),intCounter=163. from#tmp_lock_who64. IFERROR0RETURNERROR65. ifintCountProperties=066. select现在没有阻塞和死锁信息asmessage67. -循环开始68. whileintCounter50129. ANDSessionIDISNULL130. ORer.session_id=SessionID131. ORDERBYer.blocking_session_idDESC,132. er.session_id133. 134. 135. END136. */137. 138. -19.查看所有数据库用户登录信息139. execsp_helplogins140. 141. -20.查看所有数据库用户所属的角色信息142. execsp_helpsrvrolemember143. 144. -21.查看链接服务器145. execsp_helplinkedsrvlogin146. 147. -22.查看远端数据库用户登录信息148. execsp_helpremotelogin149. 150. -23.获取网络数据包统计信息151. select152. pack_received输入数据包数量,153. pack_sent输出数据包数量,154. packet_errors错误包数量,155. getdate()当前时间156. 157. -24.检查数据库中的所有对象的分配和机构完整性是否存在错误158. dbcccheckdb159. 160. -25.查询文件组和文件161. select162. ,df.physical_name,df.size,df.growth,163. filegroup,f.is_default164. fromsys.database_filesdfjoinsys.filegroupsf165. ondf.data_space_id=f.data_space_id166. 167. -26.查看数据库中所有表的条数168. astablename,169. a.rowcntasdatacount170. fromsysindexesa,171. sysobjectsb172. wherea.id=b.id173. anda.indid2174. andobjectproperty(b.id,IsMSShipped)=0175. 176. -27.得到最耗时的前10条T-SQL语句177. ;withmacoas178. (179. selecttop10180. plan_handle,181. sum(total_worker_time)astotal_worker_time,182. sum(execution_count)asexecution_count,183. count(1)assql_count184. fromsys.dm_exec_query_statsgroupbyplan_handle185. orderbysum(total_worker_time)desc186. )187. selectt.text,188. a.total_worker_time,189. a.execution_count,190. a.sql_count191. frommacoa192. crossapplysys.dm_exec_sql_text(plan_handle)t193. 194. -28.查看SQLServer的实际内存占用195. sel