求教~~ SQL 数据库空间问题

Question

sql数据库select数据

Answer 1

我用这个脚本来检查数据库文件信息的，2008R2上运行通过，低版本未测试：
--check DB files informations
SELECT  DB_NAME(database_id) DBName ,
        name [FILENAME] ,
        physical_name ,
        type_desc ,
        CAST(size AS VARCHAR(10)) + 'MB' AS currentsize ,
        CASE WHEN max_size = -1 THEN 'unlimited'
             ELSE CONVERT(VARCHAR(20), max_size / 1024)
        END [LimitSIZE(MB)] ,
        CASE WHEN is_percent_growth = 0
             THEN CAST(growth AS VARCHAR(10)) + 'MB'
             ELSE CAST(growth AS VARCHAR(10)) + '%'
        END [GrowTH]
FROM    sys.master_files

Answer 2

我现在就是搞不懂为什么每个表大小加起来不等于 sp_helpdb或者sp_spaceused.
关键是差的太多了，sp_helpdb 是2T, 所有表加起来只有700GB.....

Answer 3

你可以对比一下它们的源码，机制是不一样的：SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
create procedure sys.sp_spaceused --- 2003/05/19 14:00
@objname nvarchar(776) = null, -- The object we want size on.
@updateusage varchar(5) = false -- Param. for specifying that
-- usage info. should be updated.
asdeclare @id int -- The object id that takes up space
,@type character(2) -- The object type.
,@pages bigint -- Working variable for size calc.
,@dbname sysname
,@dbsize bigint
,@logsize bigint
,@reservedpages  bigint
,@usedpages  bigint
,@rowCount bigint/*
**  Check to see if user wants usages updated.
*/if @updateusage is not null
begin
select @updateusage=lower(@updateusage) if @updateusage not in ('true','false')
begin
raiserror(15143,-1,-1,@updateusage)
return(1)
end
end
/*
**  Check to see that the objname is local.
*/
if @objname IS NOT NULL
begin select @dbname = parsename(@objname, 3) if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end if @dbname is null
select @dbname = db_name() /*
**  Try to find the object.
*/
SELECT @id = object_id, @type = type FROM sys.objects WHERE object_id = object_id(@objname) -- Translate @id to internal-table for queue
IF @type = 'SQ'
SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 --ITT_ServiceQueue /*
**  Does the object exist?
*/
if @id is null
begin
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
end -- Is it a table, view or queue?
IF @type NOT IN ('U ','S ','V ','SQ','IT')
begin
raiserror(15234,-1,-1)
return (1)
end
end/*
**  Update usages if user specified to do so.
*/if @updateusage = 'true'
begin
if @objname is null
dbcc updateusage(0) with no_infomsgs
else
dbcc updateusage(0,@objname) with no_infomsgs
print ' '
endset nocount on/*
**  If @id is null, then we want summary data.
*/
if @id is null
begin
select @dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
, @logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))
from dbo.sysfiles select @reservedpages = sum(a.total_pages),
@usedpages = sum(a.used_pages),
@pages = sum(
CASE
-- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"
When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
)
from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id /* unallocated space could not be negative */
select 
database_name = db_name(),
database_size = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize)) 
* 8192 / 1048576,15,2) + ' MB'),
'unallocated space' = ltrim(str((case when @dbsize >= @reservedpages then
(convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages)) 
* 8192 / 1048576 else 0 end),15,2) + ' MB') /*
**  Now calculate the summary data.
**  reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(data_pages) + sum(text_used)
** index: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
select
reserved = ltrim(str(@reservedpages * 8192 / 1024.,15,0) + ' KB'),
data = ltrim(str(@pages * 8192 / 1024.,15,0) + ' KB'),
index_size = ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0) + ' KB'),
unused = ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0) + ' KB')
end/*
**  We want a particular object.
*/
else
begin
/*
** Now calculate the summary data. 
*  Note that LOB Data and Row-overflow Data are counted as Data Pages.
*/
SELECT 
@reservedpages = SUM (reserved_page_count),
@usedpages = SUM (used_page_count),
@pages = SUM (
CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
),
@rowCount = SUM (
CASE
WHEN (index_id < 2) THEN row_count
ELSE 0
END
)
FROM sys.dm_db_partition_stats
WHERE object_id = @id; /*
** Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table
*/
IF (SELECT count(*) FROM sys.internal_tables WHERE parent_id = @id AND internal_type IN (202,204,211,212,213,214,215,216)) > 0 
BEGIN
/*
**  Now calculate the summary data. Row counts in these internal tables don't 
**  contribute towards row count of original table.
*/
SELECT 
@reservedpages = @reservedpages + sum(reserved_page_count),
@usedpages = @usedpages + sum(used_page_count)
FROM sys.dm_db_partition_stats p, sys.internal_tables it
WHERE it.parent_id = @id AND it.internal_type IN (202,204,211,212,213,214,215,216) AND p.object_id = it.object_id;
END SELECT 
name = OBJECT_NAME (@id),
rows = convert (char(11), @rowCount),
reserved = LTRIM (STR (@reservedpages * 8, 15, 0) + ' KB'),
data = LTRIM (STR (@pages * 8, 15, 0) + ' KB'),
index_size = LTRIM (STR ((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0) + ' KB'),
unused = LTRIM (STR ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')end
return (0) -- sp_spaceused
GO

Answer 4

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
create procedure sys.sp_helpdb  -- 1995/12/20 15:34 #12755
@dbname sysname = NULL -- database name
asdeclare @exec_stmt nvarchar(625)
declare @showdev bit
declare @name           sysname
declare @cmd nvarchar(285) -- (26 + 258) + 1 extra
declare @dbdesc varchar(600) /* the total description for the db */
declare @propdesc varchar(40)set nocount on/* Create temp table before any DMP to enure dynamic
**  Since we examine the status bits in sysdatabase and turn them
**  into english, we need a temporary table to build the descriptions.
*/
create table #spdbdesc
(
dbname sysname,
owner sysname null,
created nvarchar(11),
dbid smallint,
dbdesc nvarchar(600) null,
dbsize nvarchar(13) null,
cmptlevel tinyint
)
/*
**  If no database name given, get 'em all.
*/
if @dbname is null
select @showdev = 0
else select @showdev = 1/*
**  See if the database exists
*/
if not exists (select * from master.dbo.sysdatabases
where (@dbname is null or name = @dbname))
begin
raiserror(15010,-1,-1,@dbname)
  return (1)
end
/*
**  Initialize #spdbdesc from sysdatabases
*/
insert into #spdbdesc (dbname, owner, created, dbid, cmptlevel)
select name, isnull(suser_sname(sid),'~~UNKNOWN~~'), convert(nvarchar(11), crdate),
dbid, cmptlevel from master.dbo.sysdatabases
where (@dbname is null or name = @dbname)
declare ms_crs_c1 cursor global for
select db_name (dbid) from #spdbdesc
open ms_crs_c1
fetch ms_crs_c1 into @name
while @@fetch_status >= 0
begin
if (has_dbaccess(@name) <> 1)
begin
  delete #spdbdesc where current of ms_crs_c1
  raiserror(15622,-1,-1, @name)
end
else
begin
/* Insert row for each database */
select @exec_stmt = 
    'update #spdbdesc
/*
** 8 KB pages is 128 per MB. If we ever change page size, this
** will be variable by DB or file or filegroup in some manner 
** unforseeable now so just hard code it.
*/
set dbsize = (select str(sum(convert(dec(17,2),size)) / 128,10,2)
+ N'' MB'' from '
  + quotename(@name, N'[') 
  + N'.dbo.sysfiles) 
  WHERE current of ms_crs_c1' execute (@exec_stmt)
end
fetch ms_crs_c1 into @name
end
deallocate ms_crs_c1/*
**  Now for each dbid in #spdbdesc, build the database status
**  description.
*/
declare @curdbid smallint /* the one we're currently working on */
/*
**  Set @curdbid to the first dbid.
*/
select @curdbid = min(dbid) from #spdbdesc
while @curdbid IS NOT NULL
begin
set @name = db_name(@curdbid) -- These properties always available
SELECT @dbdesc = 'Status=' + convert(sysname,DatabasePropertyEx(@name,'Status'))
SELECT @dbdesc = @dbdesc + ', Updateability=' + convert(sysname,DatabasePropertyEx(@name,'Updateability'))
SELECT @dbdesc = @dbdesc + ', UserAccess=' + convert(sysname,DatabasePropertyEx(@name,'UserAccess'))
SELECT @dbdesc = @dbdesc + ', Recovery=' + convert(sysname,DatabasePropertyEx(@name,'Recovery'))
SELECT @dbdesc = @dbdesc + ', Version=' + convert(sysname,DatabasePropertyEx(@name,'Version')) -- These props only available if db not shutdown
IF DatabaseProperty(@name, 'IsShutdown') = 0
BEGIN
SELECT @dbdesc = @dbdesc + ', Collation=' + convert(sysname,DatabasePropertyEx(@name,'Collation'))
SELECT @dbdesc = @dbdesc + ', SQLSortOrder=' + convert(sysname,DatabasePropertyEx(@name,'SQLSortOrder'))
END -- These are the boolean properties
IF DatabasePropertyEx(@name,'IsAutoClose') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoClose'
IF DatabasePropertyEx(@name,'IsAutoShrink') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoShrink'
IF DatabasePropertyEx(@name,'IsInStandby') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsInStandby'
IF DatabasePropertyEx(@name,'IsTornPageDetectionEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsTornPageDetectionEnabled'
IF DatabasePropertyEx(@name,'IsAnsiNullDefault') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiNullDefault'
IF DatabasePropertyEx(@name,'IsAnsiNullsEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiNullsEnabled'
IF DatabasePropertyEx(@name,'IsAnsiPaddingEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiPaddingEnabled'
IF DatabasePropertyEx(@name,'IsAnsiWarningsEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAnsiWarningsEnabled'
IF DatabasePropertyEx(@name,'IsArithmeticAbortEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsArithmeticAbortEnabled'
IF DatabasePropertyEx(@name,'IsAutoCreateStatistics') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoCreateStatistics'
IF DatabasePropertyEx(@name,'IsAutoUpdateStatistics') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsAutoUpdateStatistics'
IF DatabasePropertyEx(@name,'IsCloseCursorsOnCommitEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsCloseCursorsOnCommitEnabled'
IF DatabasePropertyEx(@name,'IsFullTextEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsFullTextEnabled'
IF DatabasePropertyEx(@name,'IsLocalCursorsDefault') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsLocalCursorsDefault'
IF DatabasePropertyEx(@name,'IsNullConcat') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsNullConcat'
IF DatabasePropertyEx(@name,'IsNumericRoundAbortEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsNumericRoundAbortEnabled'
IF DatabasePropertyEx(@name,'IsQuotedIdentifiersEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsQuotedIdentifiersEnabled'
IF DatabasePropertyEx(@name,'IsRecursiveTriggersEnabled') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsRecursiveTriggersEnabled'
IF DatabasePropertyEx(@name,'IsMergePublished') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsMergePublished'
IF DatabasePropertyEx(@name,'IsPublished') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsPublished'
IF DatabasePropertyEx(@name,'IsSubscribed') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsSubscribed'
IF DatabasePropertyEx(@name,'IsSyncWithBackup') = 1
SELECT @dbdesc = @dbdesc + ', ' + 'IsSyncWithBackup' update #spdbdesc set dbdesc = @dbdesc where dbid = @curdbid /*
**  Now get the next, if any dbid.
*/
select @curdbid = min(dbid) from #spdbdesc where dbid > @curdbid
end/*
**  Now #spdbdesc is complete so we can print out the db info
*/
select name = dbname,
db_size = dbsize,
owner = owner,
dbid = dbid,
created = created,
status = dbdesc,
    compatibility_level = cmptlevel
from  #spdbdesc
order by dbname/*
**  If we are looking at one database, show its file allocation.
*/
if @showdev = 1 and has_dbaccess(@dbname) = 1
begin
print N' '
select @cmd = N'use ' +  quotename(@dbname) + N' exec sys.sp_helpfile'
exec (@cmd)end
return (0) -- sp_helpdb
GO

Answer 5

本帖最后由 wufeng4552 于 2014-04-18 15:32:45 编辑

  

Answer 6

有些统计方便是有区别的
比如一个表占用多个Extend，一个Extend有8个Page，很可能只使用了两三个Page，其他五六个Page是空闲的
那么有的空间统计为8个Page，有的空间统计为两三个Page
这就是区别

Answer 7

sp_helpdb 
sp_spaceused都是按照页来统计

Answer 8

但是我sp_helpdb里显示的log类型的数据库只有一个，也就50G左右吧，其他都是data类型的啊，
我即使是吧log数据库剔除掉和sp_spaceused比也差很多啊，

Answer 9

我的是SQL 2005，没有这2个视图好像。
郁闷啊

Answer 10

本帖最后由 wufeng4552 于 2014-04-21 15:17:56 编辑

  

Answer 11

现在sp_helpdb和sp_spaceuesd 基本差不多， 但是我用sp_spaceused 导出所有表之后，把所有表reserved加起来就不等于单个的sp_spaceued，而且相差1000GB之多。
当前数据库中执行
sp_spaced 
返回的结果中
unallocated space
在你返回的reserved 总和+unallocated space是不是和
sp_helpdb差不多？
如果是 可以参照我说的
4）SP_helpdb 返回的大小包含未分配的空间
或者
sp_spaced 不加参数的情况下
返回的
reserved是不是与你每个表的reserved之和差不多 前者稍大（包含系统页面的使用）

Answer 12

当前数据库中执行
sp_spaced 
返回的结果中
unallocated space
在你返回的reserved 总和+unallocated space是不是和
sp_helpdb差不多？
如果是 可以参照我说的
4）SP_helpdb 返回的大小包含未分配的空间
或者
sp_spaced 不加参数的情况下
返回的
reserved是不是与你每个表的reserved之和差不多 前者稍大（包含系统页面的使用）
相差1000G之多
确定没有计算错？

Answer 13

sp_spaceused 
加上
@updateusage = N'TRUE'这个选项之后
重新计算下
看看结果如何数据库级和对象级都加上这个选项

Answer 14

select sum(dbps.reserved_page_count)*8 
from sys.dm_db_partition_stats dbps
inner join sys.objects o
on dbps.object_id=o.object_id
这个返回多少

Answer 15

数据库现在不在我旁边，我要去别的地方才能运行出结果，如果还有其他的check语句，要不一起写出来，我一起去把结果捞出来看问题在哪，呵呵，谢谢！
select sum(dbps.reserved_page_count)*8 
from sys.dm_db_partition_stats dbps
inner join sys.objects o
on dbps.object_id=o.object_id
这个返回多少