星期二, 11月 10, 2009

找出SQLServe某個Database裏每個table的使用量

印象中在下用的某個架站機有Log功能,剛才發現DB成長到500MB,但是忘記是哪個table在Log,於是找到Script to analyze table space usage,找出此資料庫中列數最多或使用磁碟容量最多的table,就可以解決。

Update: 強者 evi1[c] 在回應中提供了他的SQL,語法精簡效率超高,我已改用他的版本,特此誌謝。

為避免原站無法連結,備份如下:(版權歸原作者Michael Valentine Jones所有)
-- Script to analyze table space usage using the
-- output from the sp_spaceused stored procedure
-- Works with SQL 7.0, 2000, and 2005

set nocount on

print 'Show Size, Space Used, Unused Space, Type, and Name of all database files'

select
 [FileSizeMB] =
  convert(numeric(10,2),sum(round(a.size/128.,2))),
        [UsedSpaceMB] =
  convert(numeric(10,2),sum(round(fileproperty( a.name,'SpaceUsed')/128.,2))) ,
        [UnusedSpaceMB] =
  convert(numeric(10,2),sum(round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2))) ,
 [Type] =
  case when a.groupid is null then '' when a.groupid = 0 then 'Log' else 'Data' end,
 [DBFileName] = isnull(a.name,'*** Total for all files ***')
from
 sysfiles a
group by
 groupid,
 a.name
 with rollup
having
 a.groupid is null or
 a.name is not null
order by
 case when a.groupid is null then 99 when a.groupid = 0 then 0 else 1 end,
 a.groupid,
 case when a.name is null then 99 else 0 end,
 a.name

create table #TABLE_SPACE_WORK
(
 TABLE_NAME  sysname  not null ,
 TABLE_ROWS  numeric(18,0) not null ,
 RESERVED  varchar(50)  not null ,
 DATA   varchar(50)  not null ,
 INDEX_SIZE  varchar(50)  not null ,
 UNUSED   varchar(50)  not null ,
)

create table #TABLE_SPACE_USED
(
 Seq  int  not null 
 identity(1,1) primary key clustered,
 TABLE_NAME  sysname  not null ,
 TABLE_ROWS  numeric(18,0) not null ,
 RESERVED  varchar(50)  not null ,
 DATA   varchar(50)  not null ,
 INDEX_SIZE  varchar(50)  not null ,
 UNUSED   varchar(50)  not null ,
)

create table #TABLE_SPACE
(
 Seq  int  not null
 identity(1,1) primary key clustered,
 TABLE_NAME  SYSNAME  not null ,
 TABLE_ROWS  int   not null ,
 RESERVED  int   not null ,
 DATA   int   not null ,
 INDEX_SIZE  int   not null ,
 UNUSED   int   not null ,
 USED_MB    numeric(18,4) not null,
 USED_GB    numeric(18,4) not null,
 AVERAGE_BYTES_PER_ROW  numeric(18,5) null,
 AVERAGE_DATA_BYTES_PER_ROW numeric(18,5) null,
 AVERAGE_INDEX_BYTES_PER_ROW numeric(18,5) null,
 AVERAGE_UNUSED_BYTES_PER_ROW numeric(18,5) null,
)

declare @fetch_status int
declare @proc  varchar(200)
select @proc = rtrim(db_name())+'.dbo.sp_spaceused'

declare Cur_Cursor cursor local
for
select
 TABLE_NAME = 
 rtrim(TABLE_SCHEMA)+'.'+rtrim(TABLE_NAME)
from
 INFORMATION_SCHEMA.TABLES 
where
 TABLE_TYPE = 'BASE TABLE'
order by
 1

open Cur_Cursor

declare @TABLE_NAME  varchar(200)

select @fetch_status = 0
while @fetch_status = 0
 begin
 fetch next from Cur_Cursor
 into
  @TABLE_NAME

 select @fetch_status = @@fetch_status

 if @fetch_status <> 0
  begin
  continue
  end

 truncate table #TABLE_SPACE_WORK

 insert into #TABLE_SPACE_WORK
  (
  TABLE_NAME,
  TABLE_ROWS,
  RESERVED,
  DATA,
  INDEX_SIZE,
  UNUSED
  )
 exec @proc @objname = 
  @TABLE_NAME ,@updateusage = 'true'


 -- Needed to work with SQL 7
 update #TABLE_SPACE_WORK
 set
  TABLE_NAME = @TABLE_NAME

 insert into #TABLE_SPACE_USED
  (
  TABLE_NAME,
  TABLE_ROWS,
  RESERVED,
  DATA,
  INDEX_SIZE,
  UNUSED
  )
 select
  TABLE_NAME,
  TABLE_ROWS,
  RESERVED,
  DATA,
  INDEX_SIZE,
  UNUSED
 from
  #TABLE_SPACE_WORK

 end  --While end

close Cur_Cursor

deallocate Cur_Cursor

insert into #TABLE_SPACE
 (
 TABLE_NAME,
 TABLE_ROWS,
 RESERVED,
 DATA,
 INDEX_SIZE,
 UNUSED,
 USED_MB,
 USED_GB,
 AVERAGE_BYTES_PER_ROW,
 AVERAGE_DATA_BYTES_PER_ROW,
 AVERAGE_INDEX_BYTES_PER_ROW,
 AVERAGE_UNUSED_BYTES_PER_ROW

 )
select
 TABLE_NAME,
 TABLE_ROWS,
 RESERVED,
 DATA,
 INDEX_SIZE,
 UNUSED,
 USED_MB   =
  round(convert(numeric(25,10),RESERVED)/
  convert(numeric(25,10),1024),4),
 USED_GB   =
  round(convert(numeric(25,10),RESERVED)/
  convert(numeric(25,10),1024*1024),4),
 AVERAGE_BYTES_PER_ROW =
  case
  when TABLE_ROWS <> 0
  then round(
  (1024.000000*convert(numeric(25,10),RESERVED))/
  convert(numeric(25,10),TABLE_ROWS),5)
  else null
  end,
 AVERAGE_DATA_BYTES_PER_ROW =
  case
  when TABLE_ROWS <> 0
  then round(
  (1024.000000*convert(numeric(25,10),DATA))/
  convert(numeric(25,10),TABLE_ROWS),5)
  else null
  end,
 AVERAGE_INDEX_BYTES_PER_ROW =
  case
  when TABLE_ROWS <> 0
  then round(
  (1024.000000*convert(numeric(25,10),INDEX_SIZE))/
  convert(numeric(25,10),TABLE_ROWS),5)
  else null
  end,
 AVERAGE_UNUSED_BYTES_PER_ROW =
  case
  when TABLE_ROWS <> 0
  then round(
  (1024.000000*convert(numeric(25,10),UNUSED))/
  convert(numeric(25,10),TABLE_ROWS),5)
  else null
  end
from
 (
 select
  TABLE_NAME,
  TABLE_ROWS,
  RESERVED = 
  convert(int,rtrim(replace(RESERVED,'KB',''))),
  DATA  = 
  convert(int,rtrim(replace(DATA,'KB',''))),
  INDEX_SIZE = 
  convert(int,rtrim(replace(INDEX_SIZE,'KB',''))),
  UNUSED  = 
  convert(int,rtrim(replace(UNUSED,'KB','')))
 from
  #TABLE_SPACE_USED aa
 ) a
order by
 TABLE_NAME

print 'Show results in descending order by size in MB'

select * from #TABLE_SPACE order by USED_MB desc
go

drop table #TABLE_SPACE_WORK
drop table #TABLE_SPACE_USED 
drop table #TABLE_SPACE


後記:美麗的MIS小姐又提供SQL資料庫容量檢查(How to check Database reference size?)
再備份如下:(版權歸遺忘技術的角落或其朋友所有)
DECLARE @tblname varchar(50)            --資料表名稱(使用者資料表名稱變數) 
if exists (select * from dbo.sysobjects 
            where id = object_id(N'dbo.#tmpStatics') 
              and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
DROP TABLE #tmpStatics   --暫存資料表

CREATE TABLE #tmpStatics (        --建立暫存資料表 
  name       varchar(50)  NULL ,  --資料表名稱 
  rows       varchar(50)  NULL ,  --資料表現有的資料列數 
  reserved   varchar(50)  NULL ,  --資料庫中的物件所配置的空間大小 
  data       varchar(50)  NULL ,  --資料所用的空間大小 
  index_size varchar(50)  NULL ,  --索引所用的空間大小 
  unused     varchar(50)  NULL )  --保留給資料庫中之物件但尚未使用的空間大小 
DECLARE cur_TableStatics CURSOR FORWARD_ONLY FOR 
    SELECT name FROM sysobjects 
     WHERE xtype='U' ORDER BY name               --取得使用者資料表名稱 
OPEN cur_TableStatics 
FETCH NEXT FROM cur_TableStatics 
INTO @tblname 
  WHILE @@FETCH_STATUS = 0 
  BEGIN  
    INSERT #tmpStatics 
      EXEC sp_spaceused @tblname,@updateusage = N'TRUE'    --顯示資料表的相關磁碟空間資訊 
    FETCH NEXT FROM cur_TableStatics 
    INTO @tblname 
  END 
CLOSE cur_TableStatics 
DEALLOCATE cur_TableStatics

SELECT name,rows, 
       CONVERT(NUMERIC(18,2),SUBSTRING(reserved,1,LEN(reserved)-2))/1024 AS Reserved, -- MB 
       CONVERT(NUMERIC(18,2),SUBSTRING(data,1,LEN(data)-2))/1024 AS Data, 
       CONVERT(NUMERIC(18,2),SUBSTRING(index_size,1,LEN(index_size)-2))/1024 AS Index_Siz, 
       CONVERT(NUMERIC(18,2),SUBSTRING(unused,1,LEN(unused)-2))/1024 AS Unused 
  FROM #tmpStatics 
WHERE rows <> '0' 
ORDER BY Data 
DROP TABLE #tmpStatics

2 則留言:

evi1[c] 提到...

找所有 table 筆數的方式有個很快的方法, 我是拿來對付一個月會生出兩三千萬筆資料的 table。參考一下:

SELECT sysobjects.[name], max(sysindexes.[rows]) AS TableRows,
CAST(
CASE max(sysindexes.[rows])
WHEN 0 THEN -0
ELSE LOG10(max(sysindexes.[rows]))
END
AS NUMERIC(5,2))
AS L10_TableRows
FROM sysindexes INNER JOIN sysobjects ON sysindexes.[id] = sysobjects.[id]
WHERE sysobjects.xtype = 'U'
GROUP BY sysobjects.[name]
ORDER BY max(rows) DESC
GO

鳥毅 提到...

非常感謝 evi1[c] ,您的版本快速精簡,實在是太優秀了。