ITPUB论坛-中国专业的IT技术社区

 找回密码
 注册
查看: 192807|回复: 153

[精华] MSSQL DBA 精华总结[处理对象名称中特殊字符]

[复制链接]
论坛徽章:
14
迷宫蛋
日期:2012-03-13 09:50:292014年新春福章
日期:2014-02-18 16:44:08优秀写手
日期:2013-12-18 09:29:15迷宫蛋
日期:2012-06-13 15:28:34咸鸭蛋
日期:2012-06-11 16:11:11灰彻蛋
日期:2012-05-31 10:18:54双黄蛋
日期:2012-05-28 17:45:17茶鸡蛋
日期:2012-05-28 17:27:32蜘蛛蛋
日期:2012-05-28 16:54:09迷宫蛋
日期:2012-05-17 19:00:48
跳转到指定楼层
1#
发表于 2012-3-5 16:28 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
本帖最后由 Cherish_j_wang 于 2012-8-16 22:12 编辑

MSSQL DBA 常用脚本总结
之如何快速而准确获取数据库信息
前沿
作为MSSQL DBA来讲,相信大家在平时的工作中也有很多总结,在此和大家分享一些有用的脚本。也希望大家多多的补充。

背景
作为MSSQL DBA来说,需要做的一个很重要的工作就是数据库的监控和监控处理。其中,一类比较重要的监控就是Disk Monitor。监控磁盘空间的使用状况,当达到某一个设定的阀值时,会发出监控邮件,这个时候需要DBA及时处理,以免数据文件或者日志文件的增长导致了磁盘空间用尽,从而导致磁盘写爆或者宕机的可能。
这个时候,一系列重要的问题就摆在了DBA的面前:

  • 空间紧张的Driver上有哪些数据库?
  • 这些数据库的数据文件和日志文件有多大?
  • 数据文件或者日志文件到底有多少空间可供收缩?
  • 整个数据库实例的文件大小情况怎样?

如何快速而准确的获取到这些信息是我们本文的目的。
在此,希望和大家对工作总结的分享,希望对大家有用

脚本
在上面的背景下,话不多说,直接上脚本,也可以直接下载附件。
database_Statistics_V2.sql (4.34 KB, 下载次数: 1949)
  1. USE master
  2. GO
  3. SET NOCOUNT ON
  4. --===================public variables
  5. declare
  6.         @db_name        sysname
  7.         ,@drive                sysname
  8. ;
  9. select
  10.         @db_name        = NULL        --NULL: all databases
  11.         ,@drive                = 'D'        --NULL:all drives
  12. ;
  13. --================end public variables

  14. --===================private variables
  15. declare
  16.         @sql nvarchar(max)
  17.         ,@exec_sql nvarchar(max)
  18.         ,@database_name sysname
  19.         ,@reservedpages int
  20. ;
  21. select
  22.         @sql = N'
  23. select @reservedpages = sum(a.total_pages)
  24. from sys.partitions p WITH(NOLOCK)
  25.         inner join sys.allocation_units a WITH(NOLOCK)
  26.                 on p.partition_id = a.container_id'
  27.         ,@database_name = N''
  28.         ,@exec_sql = N''
  29.         ,@reservedpages = 0
  30. ;
  31. --================end private variables

  32. --================for reserved pages saving
  33. declare
  34.         @tb_database_reservedpages table
  35. (
  36.         id int identity(1,1) not null PRIMARY key
  37.         ,database_id int null
  38.         ,database_name sysname null
  39.         ,reservedpages int null
  40. );

  41. DECLARE
  42.         @tb_dbSize TABLE
  43. (
  44. id int identity(1,1) not null PRIMARY key
  45. ,size int null
  46. ,name sysname null
  47. ,database_id int null
  48. ,type bit null
  49. ,on_drive char(1) null
  50. ,f_name sysname null
  51. );

  52. --size = 8K
  53. INSERT INTO @tb_dbSize
  54. SELECT
  55.         F.size
  56.         ,DB.name
  57.         ,F.database_id
  58.         ,F.type
  59.         ,left(F.physical_name,1)
  60.         ,F.name AS f_name
  61. FROM sys.master_files AS F WITH (NOLOCK)
  62.         INNER JOIN sys.databases AS DB WITH (NOLOCK)
  63.         ON F.database_id = DB.database_id

  64. --==============================================all database reservedpages generation
  65. declare cur_database cursor local static forward_only read_only
  66. for
  67. select name
  68. from sys.databases with(nolock)
  69. --==============you can filter the system database or not
  70. --where name not in(
  71. --'master'
  72. --,'tempdb'
  73. --,'model'
  74. --,'msdb'
  75. --,'distribution'
  76. --);

  77. open cur_database
  78. fetch next from cur_database into @database_name

  79. while(@@FETCH_STATUS = 0)
  80. begin
  81.         SET
  82.                 @exec_sql = N'USE ' + QUOTENAME(@database_name) + ';'
  83.                 + @sql;

  84.         exec sys.sp_executesql @exec_sql
  85.                                                         ,N'@reservedpages int output'
  86.                                                         ,@reservedpages = @reservedpages output
  87.         
  88.         insert into @tb_database_reservedpages
  89.         select db_id(@database_name),@database_name,@reservedpages;

  90.         fetch next from cur_database into @database_name
  91. end;

  92. close cur_database
  93. deallocate cur_database

  94. --select * from @tb_database_reservedpages
  95. --============================================end all database reservedpages generation

  96. --====================all database size statistics
  97. ;WITH dbSize--type = 0
  98. AS
  99. (
  100. SELECT
  101.         database_name = name,
  102.         database_id,
  103.         on_drive,
  104.         fileSize = sum(size)--*8./1024.
  105. FROM @tb_dbSize
  106. WHERE type = 0
  107. GROUP BY name,database_id,on_drive
  108. )
  109. ,logSize--type = 1
  110. AS
  111. (
  112. SELECT
  113.         database_name = name,
  114.         database_id,
  115.         on_drive,
  116.         fileSize = sum(size)--*8./1024.
  117. FROM @tb_dbSize
  118. WHERE type = 1
  119. GROUP BY name,database_id,on_drive
  120. )
  121. ,Size
  122. AS
  123. (
  124.         SELECT
  125.                 database_name = name,
  126.                 database_id,
  127.                 dbSize = sum(size)--*8./1024.
  128.         FROM @tb_dbSize
  129.         GROUP BY name,database_id
  130. )
  131. SELECT
  132.                 server_name = @@SERVERNAME
  133.                 ,A.database_name
  134.                 ,[dbSize(MB)] = S.dbSize * 8./1024.
  135.                 ,[dataSize(MB)] = A.fileSize * 8./1024.
  136.                 ,[unallocatedSize(MB)] = ltrim(str((case
  137.                                                                                                 when A.fileSize/*@dbsize*/ >= rvp.reservedpages/*@reservedpages*/ then  
  138.                                                                                                                                 (convert (dec (15,2),A.fileSize/*@dbsize*/) - convert (dec (15,2),rvp.reservedpages/*@reservedpages*/))   
  139.                                                                                                                                 * 8192 / 1048576
  140.                                                                                                 else 0
  141.                                                                                         end),15,2) + ' MB')
  142.                 ,data_Drive = A.on_drive
  143.                 ,[logSize(MB)] = B.fileSize * 8./1024.
  144.                 ,log_Drive = B.on_drive
  145. FROM dbSize AS A
  146.         INNER JOIN logSize AS B
  147.         ON A.database_id = B.database_id
  148.         INNER JOIN Size AS S
  149.         ON A.database_id = S.database_id
  150.         INNER JOIN @tb_database_reservedpages AS rvp
  151.         ON A.database_id = rvp.database_id
  152. WHERE A.database_name = ISNULL(@db_name,A.database_name)
  153.         AND (A.on_drive = ISNULL(@drive,A.on_drive)
  154.   OR B.on_drive= ISNULL(@drive,B.on_drive)
  155.   )ORDER BY S.dbSize DESC         
  156. --=================end all database size statistics

  157. --====================instance summary
  158. ;WITH DATA
  159. AS
  160. (select
  161.         Size = cast(sum(size)*8./1024./1024. AS decimal(9,2))
  162.         ,type
  163. FROM @tb_dbSize
  164. GROUP BY type
  165. )

  166. SELECT
  167.         server_name = @@SERVERNAME
  168.         ,[0] + [1] AS [dbSize(GB)]
  169.         ,[0] AS [dataSize(GB)]
  170.         ,[1] AS [logSize(GB)]
  171. FROM (
  172.                 SELECT *
  173.                 FROM DATA
  174. ) AS A
  175. PIVOT
  176. (
  177.         sum(size)
  178.         FOR type in([0],[1])
  179. ) AS B

  180. --=================end instance summary
复制代码


说明
1.        参数@db_name:如果有值表示查看特定数据库信息;如果为null表示查看所有数据库信息
2.        参数@drive:如果有值表示查看特定磁盘驱动器的信息;如果为null表示查看所有磁盘的信息。
3.        本文的目的是快速而准确的定位问题所在,当然我们也可以使用RDP,从Windows窗口查看,但效率上无法相比。
4.        解决Disk Monitor不是本文讨论的范畴(其实也就是在定位问题后,收缩数据库文件)

论坛徽章:
25
2012新春纪念徽章
日期:2012-02-07 09:59:35奥运会纪念徽章:举重
日期:2012-07-06 21:53:49ITPUB 11周年纪念徽章
日期:2012-10-09 18:14:48奥运会纪念徽章:蹦床
日期:2012-10-26 23:07:332013年新春福章
日期:2013-02-25 14:51:24复活蛋
日期:2013-04-20 09:27:50ITPUB社区千里马徽章
日期:2013-06-09 10:15:34雪佛兰
日期:2013-12-15 18:10:27马上有对象
日期:2015-01-08 23:11:00双鱼座
日期:2016-01-01 11:27:08
2#
发表于 2012-3-5 21:43 | 只看该作者

使用道具 举报

回复
论坛徽章:
14
迷宫蛋
日期:2012-03-13 09:50:292014年新春福章
日期:2014-02-18 16:44:08优秀写手
日期:2013-12-18 09:29:15迷宫蛋
日期:2012-06-13 15:28:34咸鸭蛋
日期:2012-06-11 16:11:11灰彻蛋
日期:2012-05-31 10:18:54双黄蛋
日期:2012-05-28 17:45:17茶鸡蛋
日期:2012-05-28 17:27:32蜘蛛蛋
日期:2012-05-28 16:54:09迷宫蛋
日期:2012-05-17 19:00:48
3#
 楼主| 发表于 2012-3-5 21:49 | 只看该作者
只是工作的总结,希望对有遇到相同问题的同胞有帮助。

使用道具 举报

回复
论坛徽章:
25
ITPUB伯乐
日期:2012-05-22 15:05:35慢羊羊
日期:2015-05-08 15:34:02喜羊羊
日期:2015-05-14 12:15:30美羊羊
日期:2015-07-02 22:22:59秀才
日期:2015-08-24 09:51:48天枰座
日期:2015-11-05 16:53:35双鱼座
日期:2015-12-05 19:22:56巨蟹座
日期:2015-12-20 18:46:10摩羯座
日期:2016-05-19 09:54:17ITPUB15周年纪念
日期:2016-10-13 13:15:34
4#
发表于 2012-3-5 22:21 | 只看该作者
支持。。。。

使用道具 举报

回复
论坛徽章:
25
ITPUB伯乐
日期:2012-05-22 15:05:35慢羊羊
日期:2015-05-08 15:34:02喜羊羊
日期:2015-05-14 12:15:30美羊羊
日期:2015-07-02 22:22:59秀才
日期:2015-08-24 09:51:48天枰座
日期:2015-11-05 16:53:35双鱼座
日期:2015-12-05 19:22:56巨蟹座
日期:2015-12-20 18:46:10摩羯座
日期:2016-05-19 09:54:17ITPUB15周年纪念
日期:2016-10-13 13:15:34
5#
发表于 2012-3-5 22:21 | 只看该作者
还有更多的脚本吗?能一起发出来吗,我加为精华。
供大家学习,谢谢

使用道具 举报

回复
论坛徽章:
5
ITPUB十周年纪念徽章
日期:2011-11-01 16:26:59蜘蛛蛋
日期:2011-11-03 10:25:372012新春纪念徽章
日期:2012-01-04 11:58:18蛋疼蛋
日期:2012-02-06 10:11:07紫蛋头
日期:2012-02-18 21:18:11
6#
发表于 2012-3-5 22:55 | 只看该作者
支持技术分享

使用道具 举报

回复
论坛徽章:
14
迷宫蛋
日期:2012-03-13 09:50:292014年新春福章
日期:2014-02-18 16:44:08优秀写手
日期:2013-12-18 09:29:15迷宫蛋
日期:2012-06-13 15:28:34咸鸭蛋
日期:2012-06-11 16:11:11灰彻蛋
日期:2012-05-31 10:18:54双黄蛋
日期:2012-05-28 17:45:17茶鸡蛋
日期:2012-05-28 17:27:32蜘蛛蛋
日期:2012-05-28 16:54:09迷宫蛋
日期:2012-05-17 19:00:48
7#
 楼主| 发表于 2012-3-6 19:57 | 只看该作者
hwtong 发表于 2012-3-5 22:21
还有更多的脚本吗?能一起发出来吗,我加为精华。
供大家学习,谢谢

哈哈,有很多啦。。。。先把这个加精华了,我再Share~~~

使用道具 举报

回复
论坛徽章:
14
迷宫蛋
日期:2012-03-13 09:50:292014年新春福章
日期:2014-02-18 16:44:08优秀写手
日期:2013-12-18 09:29:15迷宫蛋
日期:2012-06-13 15:28:34咸鸭蛋
日期:2012-06-11 16:11:11灰彻蛋
日期:2012-05-31 10:18:54双黄蛋
日期:2012-05-28 17:45:17茶鸡蛋
日期:2012-05-28 17:27:32蜘蛛蛋
日期:2012-05-28 16:54:09迷宫蛋
日期:2012-05-17 19:00:48
8#
 楼主| 发表于 2012-3-6 20:01 | 只看该作者
各位实在对不住啦,今天测试的时候发现一个小Bug,代码的第165行,应该修改为:
  1. AND (A.on_drive = ISNULL(@drive,A.on_drive)
  2.                 OR B.on_drive= ISNULL(@drive,B.on_drive)
  3.                 )
复制代码

使用道具 举报

回复
论坛徽章:
14
迷宫蛋
日期:2012-03-13 09:50:292014年新春福章
日期:2014-02-18 16:44:08优秀写手
日期:2013-12-18 09:29:15迷宫蛋
日期:2012-06-13 15:28:34咸鸭蛋
日期:2012-06-11 16:11:11灰彻蛋
日期:2012-05-31 10:18:54双黄蛋
日期:2012-05-28 17:45:17茶鸡蛋
日期:2012-05-28 17:27:32蜘蛛蛋
日期:2012-05-28 16:54:09迷宫蛋
日期:2012-05-17 19:00:48
9#
 楼主| 发表于 2012-3-6 20:06 | 只看该作者
Bug已经在帖子上更新掉了,如果大家发现还有问题,我在继续修改。~·

使用道具 举报

回复
招聘 : 数据库管理员
认证徽章
论坛徽章:
83
IT宝贝
日期:2013-11-15 18:40:242015年新春福章
日期:2015-03-06 11:57:31美羊羊
日期:2015-03-04 14:48:58马上加薪
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有车
日期:2014-02-19 11:55:14马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11
10#
发表于 2012-3-7 09:25 | 只看该作者
呵呵,支持原创哦,版主以及加精华了,本帖将推荐上明天的itpub门户社区推荐位置,本帖还可参与本月的精华中的精华评选哦,lz将有机会获赠2012中国数据库技术大会的门票

本月精华活动链接:/thread-1585008-1-1.html
欢迎lz分享更多精彩内容

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表
http://www.vxiaotou.com