关于查看MSSQL 数据库 用户每个表 占用的空间大小

2019-07-23 06:21栏目:编程学习

最近做项目需要查看数据用户表的大小,包括记录条数和占用的磁盘空间数目。在网上找了很久其中查看MSSQL数据库每个表占用的空间大小 相对还可以。
不过它的2、3中方法返回的数据比较多,有些是我们不关心的数据,我在AdventureWorks2012数据上做的测试。其中第二种方法代码如下:

日常数据维护中容量规划是每个DBA的基础工作之一,也是非常重要的工作。在生产环境中一些比较重要的业务数据库会用前期容量规划不足,或出现意外的爆发式的数据增长,直至盛满整个磁盘空间,系统会无法使用,最终业务受到影响。这时候,如果没有及时的磁盘预警的情况下会更糟糕,最终带来经济损失。那么,为了避免这种情况发生,我们可以做到提前预警,主动搜集相关数据文件的大小,例如数据库、数据文件、备份文件大小,以及主要业务数据表大小等,定期追踪并已报告形式给到存储团队,提醒容量的变化。详细的代码如下 :

复制代码 代码如下:

1.检查数据库文件大小

View Code
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tablespaceinfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
create table tablespaceinfo --创建结果存储表
(nameinfo varchar(50) ,
rowsinfo int , reserved varchar(20) ,
datainfo varchar(20) ,
index_size varchar(20) ,
unused varchar(20) )
delete from tablespaceinfo --清空数据表
declare @tablename varchar(255) --表名称
declare @cmdsql varchar(500)
DECLARE Info_cursor CURSOR FOR
select o.name
from dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsTable') = 1
and o.name not like N'#%%' order by o.name
OPEN Info_cursor
FETCH NEXT FROM Info_cursor
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
if exists (select * from dbo.sysobjects where id = object_id(@tablename) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
execute sp_executesql
N'insert into tablespaceinfo exec sp_spaceused @tbname',
N'@tbname varchar(255)',
@tbname = @tablename
FETCH NEXT FROM Info_cursor
INTO @tablename
END
CLOSE Info_cursor
DEALLOCATE Info_cursor
GO
--itlearner注:显示数据库信息
sp_spaceused @updateusage = 'TRUE'
--itlearner注:显示表信息
select *
from tablespaceinfo
order by cast(left(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) as int) desc

SELECT  convert(varchar(10),getdate(),120) as 'Date',d.name as 'Database Name',sum(CONVERT(decimal(9,3),convert(float,l.size) * (8192.0/1024.0/1024.)/1024.))  as 'Size(GB)' 

运行效果如图: 威尼斯人app 1 
很显然这个返回结果是错误的。但是它提供了一种思路,修改后的SQL语句如下:

FROM sys.master_files  l

复制代码 代码如下:

LEFT JOIN sys.databases d  ON  l.database_id=d.database_id

View Code
IF NOT EXISTS ( SELECT  *
                FROM    sys.tables
                WHERE   name = 'tablespaceinfo' )
    BEGIN
        CREATE TABLE tablespaceinfo --创建结果存储表
            (
              Table_Name VARCHAR(50) ,
              Rows_Count INT ,
              reserved INT ,
              datainfo INT ,
              index_size INT ,
              unused INT
            )
    END
DELETE  FROM tablespaceinfo
 --清空数据表
CREATE TABLE #temp --创建结果存储表
    (
      nameinfo VARCHAR(50) ,
      rowsinfo INT ,
      reserved VARCHAR(20) ,
      datainfo VARCHAR(20) ,
威尼斯人app,      index_size VARCHAR(20) ,
      unused VARCHAR(20)
    )
DECLARE @tablename VARCHAR(255)
 --表名称
DECLARE @cmdsql NVARCHAR(500)
DECLARE Info_cursor CURSOR
FOR
    SELECT  '[' TABLE_SCHEMA '].[' TABLE_NAME ']' AS Table_Name
    FROM    [INFORMATION_SCHEMA].[TABLES]
    WHERE   TABLE_TYPE = 'BASE TABLE'
            AND TABLE_NAME <> 'tablespaceinfo'
OPEN Info_cursor
FETCH NEXT FROM Info_cursor
INTO @tablename
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @cmdsql = 'insert into #temp exec sp_spaceused ''' @tablename
            ''''
        EXECUTE sp_executesql @cmdsql
        FETCH NEXT FROM Info_cursor
INTO @tablename
    END
CLOSE Info_cursor
DEALLOCATE Info_cursor
GO
--itlearner注:显示数据库信息
--sp_spaceused @updateusage = 'TRUE'
--itlearner注:显示表信息
UPDATE  #temp
SET     reserved = REPLACE(reserved, 'KB', '') ,
        datainfo = REPLACE(datainfo, 'KB', '') ,
        index_size = REPLACE(index_size, 'KB', '') ,
        unused = REPLACE(unused, 'KB', '')
INSERT  INTO dbo.tablespaceinfo
        SELECT  nameinfo ,
                CAST(rowsinfo AS INT) ,
                CAST(reserved AS INT) ,
                CAST(datainfo AS INT) ,
                CAST(index_size AS INT) ,
                CAST(unused AS INT)
        FROM    #temp
DROP TABLE #temp
SELECT  Table_Name ,
        Rows_Count ,
        CASE WHEN reserved > 1024
             THEN CAST(reserved / 1024 AS VARCHAR(10)) 'Mb'
             ELSE CAST(reserved AS VARCHAR(10)) 'KB'
        END AS Data_And_Index_Reserved ,
        CASE WHEN datainfo > 1024
             THEN CAST(datainfo / 1024 AS VARCHAR(10)) 'Mb'
             ELSE CAST(datainfo AS VARCHAR(10)) 'KB'
        END AS Used ,
        CASE WHEN Index_size > 1024
             THEN CAST(index_size / 1024 AS VARCHAR(10)) 'Mb'
             ELSE CAST(index_size AS VARCHAR(10)) 'KB'
        END AS index_size ,
        CASE WHEN unused > 1024 THEN CAST(unused / 1024 AS VARCHAR(10)) 'Mb'
             ELSE CAST(unused AS VARCHAR(10)) 'KB'
        END AS unused
FROM    dbo.tablespaceinfo
ORDER BY reserved DESC

 WHERE d.database_id>4--排除系统数据库

运行结果如图: 威尼斯人app 2
同时他的第三种方法返回的数据太多,很多是我们不怎么关心的,原SQL语句如下:

 group by d.name

复制代码 代码如下:

order by  'Size(GB)' desc

View Code

 

SELECT  OBJECT_NAME(id) tablename ,
 * reserved / 1024 reserved ,
        RTRIM(8 * dpages / 1024) 'Mb' used ,
 * ( reserved - dpages ) / 1024 unused ,
 * dpages / 1024 - rows / 1024 * minlen / 1024 free ,
        rows
FROM    sysindexes
WHERE   indid = 1
ORDER BY reserved DESC

--或者...
---MDF LDF
SELECT DISTINCT
--a.database_id,
a.name AS dbName,
a.compatibility_level,
CONVERT(VARCHAR(25), a.create_date, 106) AS dbCreateDate,
CONVERT(VARCHAR(25), CAST(CAST(SUM(b.size * 8.0 / 1024.0/1024.) AS NUMERIC(10,2)) AS money), 1) AS 'Size(GB)',
DATABASEPROPERTYEX(a.name, 'Recovery') AS recoveryMode,
DATABASEPROPERTYEX(a.name, 'Status') AS databaseStatus,
CASE DATABASEPROPERTYEX(a.name, 'IsAutoCreateStatistics') WHEN 1 THEN 'TRUE' ELSE 'FALSE' END AS autoCreateStatsEnabled,
CASE DATABASEPROPERTYEX(a.name, 'IsAutoUpdateStatistics') WHEN 1 THEN 'TRUE' ELSE 'FALSE' END AS autoUpdateStatsEnabled,
DATABASEPROPERTYEX(a.name, 'UserAccess') AS userAccess,
DATABASEPROPERTYEX(a.name, 'Updateability') AS Updateability,
CASE DATABASEPROPERTYEX(a.name, 'IsTornPageDetectionEnabled') WHEN 1 THEN 'TRUE' ELSE 'FALSE' END AS tornPageDetectionEnabled,
CASE DATABASEPROPERTYEX(a.name, 'IsRecursiveTriggersEnabled') WHEN 1 THEN 'TRUE' ELSE 'FALSE' END AS recursiveTriggersEnabled,
CASE DATABASEPROPERTYEX(a.name, 'IsInStandBy') WHEN 1 THEN 'TRUE' ELSE 'FALSE' END AS isDBStandBy
FROM
master.sys.databases a INNER JOIN master.sys.master_files b ON a.database_id = b.database_id

运行结果如图: 威尼斯人app 3
这里面包含一些索引信息,其实我们只关心表占用磁盘信息,修改后的SQL语句如下:

where a.database_id>4
GROUP BY
a.database_id,
a.name,
a.compatibility_level,
CONVERT(VARCHAR(25), a.create_date, 106)
ORDER BY 1

复制代码 代码如下:

 

View Code
SELECT  OBJECT_NAME(id) tablename ,
        CASE WHEN reserved * 8 > 1024 THEN RTRIM(8 * reserved / 1024) 'MB'
             ELSE RTRIM(reserved * 8) 'KB'
        END DataReserve ,
        CASE WHEN dpages * 8 > 1024 THEN RTRIM(8 * dpages / 1024) 'MB'
             ELSE RTRIM(dpages * 8) 'KB'
        END Used ,
        CASE WHEN 8 * ( reserved - dpages ) > 1024
             THEN RTRIM(8 * ( reserved - dpages ) / 1024) 'MB'
             ELSE RTRIM(8 * ( reserved - dpages )) 'KB'
        END unused ,
        CASE WHEN ( 8 * dpages / 1024 - rows / 1024 * minlen / 1024 ) > 1024
             THEN RTRIM(( 8 * dpages / 1024 - rows / 1024 * minlen / 1024 )
                        / 1024) 'MB'
             ELSE RTRIM(( 8 * dpages / 1024 - rows / 1024 * minlen / 1024 ))
                  'KB'
        END FREE ,
        rows AS Rows_Count
FROM    sys.sysindexes
WHERE   indid = 1
        AND status = 2066 -- status='18'
ORDER BY reserved DESC

2.检查数据库备份文件大小  

运行结果如下: 威尼斯人app 4
有不对的地方欢迎大家拍砖!

SELECT   getdate()  as,

b.server_name, Round(SUM(convert(float,b.backup_size) /1024.0/1024.0/1024.0),2) AS 'backup_size_GB', 

Round(SUM(convert(float,b.compressed_backup_size)/1024.0/1024.0/1024.0),2) AS 'compressed_backup_size_GB' FROM msdb..backupset b 

where  b.database_name not in ('model','master','msdb','')

--and b.type='D'

AND backup_start_date>getdate()-1 

GROUP BY b.server_name

3.检查表空间大小

SELECT OBJECT_NAME(id) tablename ,
CASE WHEN reserved * 8 > 1024 THEN RTRIM(8 * reserved / 1024) 'MB'
ELSE RTRIM(reserved * 8) 'KB'
END DataReserve ,
CASE WHEN dpages * 8 > 1024 THEN RTRIM(8 * dpages / 1024) 'MB'
ELSE RTRIM(dpages * 8) 'KB'
END Used ,
CASE WHEN 8 * ( reserved - dpages ) > 1024
THEN RTRIM(8 * ( reserved - dpages ) / 1024) 'MB'
ELSE RTRIM(8 * ( reserved - dpages )) 'KB'
END unused ,
CASE WHEN ( 8 * dpages / 1024 - rows / 1024 * minlen / 1024 ) > 1024
THEN RTRIM(( 8 * dpages / 1024 - rows / 1024 * minlen / 1024 )
/ 1024) 'MB'
ELSE RTRIM(( 8 * dpages / 1024 - rows / 1024 * minlen / 1024 ))

  • 'KB'
    END FREE ,
    rows AS Rows_Count
    FROM sys.sysindexes
    WHERE indid = 1
    AND status = 2066 -- status='18'
    ORDER BY reserved DESC

 

4.检查表索引大小

--特别提醒:此查询较慢,如果是生产环境请选择非业务时间执行

IF OBJECT_ID('tempdb..#Indexdata', 'U') IS NOT NULL
DROP TABLE #Indexdata
DECLARE
@SizeofIndex BIGINT, @IndexID INT,
@NameOfIndex nvarchar(200),@TypeOfIndex nvarchar(50),
@ObjectID INT,@IsPrimaryKey INT,
@FGroup VARCHAR(20)

create table #Indexdata (name nvarchar(50),
IndexID int, IndexName nvarchar(200),
SizeOfIndex int, IndexType nvarchar(50),
IsPrimaryKey INT,FGroup VARCHAR(20))
DECLARE Indexloop CURSOR FOR
SELECT idx.object_id, idx.index_id, idx.name, idx.type_desc
,idx.is_primary_key,fg.name
FROM sys.indexes idx
join sys.objects so
on idx.object_id = so.object_id JOIN sys.filegroups fg
ON idx.data_space_id = fg.data_space_id
where idx.type_desc != 'Heap'
and so.type_desc not in ('INTERNAL_TABLE','SYSTEM_TABLE')
AND idx.name in(

select
i.name

FROM sys.dm_db_index_usage_stats AS ius
JOIN sys.indexes AS i ON i.index_id = ius.index_id
AND i.object_id = ius.object_id
WHERE ius.database_id = DB_ID() --and i.name like '%ClusteredIndex%'
--and OBJECT_NAME(i.object_id) like'

版权声明:本文由威尼斯人app发布于编程学习,转载请注明出处:关于查看MSSQL 数据库 用户每个表 占用的空间大小