time 
设为首页】【收藏本站
当前位置: 主页 > 数据库 > SQL Server > SQL 基础 > 查明SQL Server表和模式空间使用情况

查明SQL Server表和模式空间使用情况

时间:2008-04-09 14:30 点击:922次 字体:[ ]




SQL Server数据库的管理员常常会头疼硬盘可用空间太少,往往需要编写很多查询来查明哪些模式占用了大量的硬盘空间,以便能够定期清理这些表和模式。本文将为大家阐述如何查询系统目录来确定磁盘空间的使用情况,可以帮助SQL Server数据库管理员识别占用最大空间的表和模式,以便将老旧的数据归档,将不需要的数据清除。本文所列脚本适用于SQL Server 2005SQL Server 2008 CTP5。

  步骤1:如图一所示,登录到SQL Server实例。

   查明SQL Server表和模式空间使用情况_www.fengfly.com

  步骤2:确定你想要获得空间使用信息的数据库,导航到该数据库,如下所示(见图二)

  Use AdventureWorks

  go

   查明SQL Server表和模式空间使用情况_www.fengfly.com

  步骤3.1:将下面的代码添加到查询窗口,并执行(见图三)。      
begin try
SE
LECT
 (
row_number() over(order by a3.name, a2.name))%2 as l1,
 a3.name AS [schemaname],
 a2.name AS [tablename],
 a1.rows as row_count,
 (a1.reserved +
ISNULL(a4.reserved,0))* 8 AS reserved,
 a1.
data * 8 AS data,
 (
CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN
   (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
 (
CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN
   (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused
F
ROM
 (SELECT
  ps.
object_id,
  SUM (
   CASE
    WHEN (ps.index_id < 2) THEN row_count
    ELSE 0
   END
   ) AS [rows],
  SUM (ps.reserved_
page_count) AS reserved,
  SUM (
   CASE
     WHEN (ps.index_id < 2) THEN
   (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
     ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
   END
   ) AS data,
  SUM (ps.used_page_count) AS used
 FROM sys.dm_db_
partition_stats ps
 GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
 (SELECT
  it.parent_id,
  SUM(ps.reserved_page_count) AS reserved,
  SUM(ps.used_page_count) AS used
  FROM sys.dm_db_partition_stats ps
  INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
  WHERE it.internal_type IN (202,204)
  GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.
schema_id = a3.schema_id)
WHERE a2.type <> 'S' and a2.type <> 'IT'
O
RDER BY a3.name, a2.name
end try
begin catch
select
 -100 as l1
, 1 as schemaname
,       ERROR_NUM
BER() as tablename
,       ERROR_SEVERITY() as row_count
,       ERROR_ST
ATE() as reserved
,       ERROR_MESSAGE() as data
,       1 as index_size
,   1 as unused
end catch


本文地址 : http://www.fengfly.com/plus/view-166462-1.html
标签: 空间 模式 使用 as Sum 情况
------分隔线----------------------------
最新评论 查看所有评论
发表评论 查看所有评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
验证码: