639532 9766984 KB 4579624 KB 81640 KB 5105720 KB
1053160 8923136 KB 3562704 KB 155320 KB 5205112 KB
34938 539872 KB 421144 KB 672 KB 118056 KB
32636 418504 KB 392920 KB 2720 KB 22864 KB
64035 253184 KB 217192 KB 12512 KB 23480 KB
也收缩了数据库,也重建了聚集索引,就是没效果,
也执行了下面的语句,
DUMP TRANSACTION [web] WITH NO_LOG
BACKUP LOG [web] WITH NO_LOG
DBCC SHRINKDATABASE ([web],0)
请问这么大的unused正常吗?跟表内的ntext字段有关系吗?应该怎么处理。这么大的数据库磁盘IO都跟不上了
7 个解决方案
#1
sp_spaceused 'object','true'
#2
看看这堆文字是否有用?
*--压缩数据库的通用存储过程
压缩日志及数据库文件大小
因为要对数据库进行分离处理
所以存储过程不能创建在被压缩的数据库中
--邹建 2004.03(引用请保留此信息)--*/
/*--调用示例
exec p_compdb 'test '
--*/
use master --注意,此存储过程要建在master数据库中
go
if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[p_compdb] ') and OBJECTPROPERTY(id, N 'IsProcedure ') = 1)
drop procedure [dbo].[p_compdb]
GO
create proc p_compdb
@dbname sysname, --要压缩的数据库名
@bkdatabase bit=1, --因为分离日志的步骤中,可能会损坏数据库,所以你可以选择是否自动数据库
@bkfname nvarchar(260)= ' ' --备份的文件名,如果不指定,自动备份到默认备份目录,备份文件名为:数据库名+日期时间
as
--1.清空日志
exec( 'DUMP TRANSACTION [ '+@dbname+ '] WITH NO_LOG ')
--2.截断事务日志:
exec( 'BACKUP LOG [ '+@dbname+ '] WITH NO_LOG ')
--3.收缩数据库文件(如果不压缩,数据库的文件不会减小
exec( 'DBCC SHRINKDATABASE([ '+@dbname+ ']) ')
--4.设置自动收缩
exec( 'EXEC sp_dboption ' ' '+@dbname+ ' ' ', ' 'autoshrink ' ', ' 'TRUE ' ' ')
--后面的步骤有一定危险,你可以可以选择是否应该这些步骤
--5.分离数据库
if @bkdatabase=1
begin
if isnull(@bkfname, ' ')= ' '
set @bkfname=@dbname+ '_ '+convert(varchar,getdate(),112)
+replace(convert(varchar,getdate(),108), ': ', ' ')
select 提示信息= '备份数据库到SQL 默认备份目录,备份文件名: '+@bkfname
exec( 'backup database [ '+@dbname+ '] to disk= ' ' '+@bkfname+ ' ' ' ')
end
--进行分离处理
create table #t(fname nvarchar(260),type int)
exec( 'insert into #t select filename,type=status&0x40 from [ '+@dbname+ ']..sysfiles ')
exec( 'sp_detach_db ' ' '+@dbname+ ' ' ' ')
--删除日志文件
declare @fname nvarchar(260),@s varchar(8000)
declare tb cursor local for select fname from #t where type=64
open tb
fetch next from tb into @fname
while @@fetch_status=0
begin
set @s= 'del " '+rtrim(@fname)+ '" '
exec master..xp_cmdshell @s,no_output
fetch next from tb into @fname
end
close tb
deallocate tb
--附加数据库
set @s= ' '
declare tb cursor local for select fname from #t where type=0
open tb
fetch next from tb into @fname
while @@fetch_status=0
begin
set @s=@s+ ', ' ' '+rtrim(@fname)+ ' ' ' '
fetch next from tb into @fname
end
close tb
deallocate tb
exec( 'sp_attach_single_file_db ' ' '+@dbname+ ' ' ' '+@s)
go
------------------------------------------------------------------------------------
也可在企业管理里收缩日志:
--收缩数据库
dbcc shrinkdatabase( '数据库名 ',0,notruncate)
DUMP TRANSACTION [库名] WITH NO_LOG
下例将 UserDB 用户数据库中名为 DataFil1 的文件收缩到 7 MB。
USE UserDB
GO
DBCC SHRINKFILE (DataFil1, 7)
下例将 UserDB 用户数据库中的文件减小,以使 UserDB 中的文件有 10% 的可用空间。
DBCC SHRINKDATABASE (UserDB, 10)
GO
*--压缩数据库的通用存储过程
压缩日志及数据库文件大小
因为要对数据库进行分离处理
所以存储过程不能创建在被压缩的数据库中
--邹建 2004.03(引用请保留此信息)--*/
/*--调用示例
exec p_compdb 'test '
--*/
use master --注意,此存储过程要建在master数据库中
go
if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[p_compdb] ') and OBJECTPROPERTY(id, N 'IsProcedure ') = 1)
drop procedure [dbo].[p_compdb]
GO
create proc p_compdb
@dbname sysname, --要压缩的数据库名
@bkdatabase bit=1, --因为分离日志的步骤中,可能会损坏数据库,所以你可以选择是否自动数据库
@bkfname nvarchar(260)= ' ' --备份的文件名,如果不指定,自动备份到默认备份目录,备份文件名为:数据库名+日期时间
as
--1.清空日志
exec( 'DUMP TRANSACTION [ '+@dbname+ '] WITH NO_LOG ')
--2.截断事务日志:
exec( 'BACKUP LOG [ '+@dbname+ '] WITH NO_LOG ')
--3.收缩数据库文件(如果不压缩,数据库的文件不会减小
exec( 'DBCC SHRINKDATABASE([ '+@dbname+ ']) ')
--4.设置自动收缩
exec( 'EXEC sp_dboption ' ' '+@dbname+ ' ' ', ' 'autoshrink ' ', ' 'TRUE ' ' ')
--后面的步骤有一定危险,你可以可以选择是否应该这些步骤
--5.分离数据库
if @bkdatabase=1
begin
if isnull(@bkfname, ' ')= ' '
set @bkfname=@dbname+ '_ '+convert(varchar,getdate(),112)
+replace(convert(varchar,getdate(),108), ': ', ' ')
select 提示信息= '备份数据库到SQL 默认备份目录,备份文件名: '+@bkfname
exec( 'backup database [ '+@dbname+ '] to disk= ' ' '+@bkfname+ ' ' ' ')
end
--进行分离处理
create table #t(fname nvarchar(260),type int)
exec( 'insert into #t select filename,type=status&0x40 from [ '+@dbname+ ']..sysfiles ')
exec( 'sp_detach_db ' ' '+@dbname+ ' ' ' ')
--删除日志文件
declare @fname nvarchar(260),@s varchar(8000)
declare tb cursor local for select fname from #t where type=64
open tb
fetch next from tb into @fname
while @@fetch_status=0
begin
set @s= 'del " '+rtrim(@fname)+ '" '
exec master..xp_cmdshell @s,no_output
fetch next from tb into @fname
end
close tb
deallocate tb
--附加数据库
set @s= ' '
declare tb cursor local for select fname from #t where type=0
open tb
fetch next from tb into @fname
while @@fetch_status=0
begin
set @s=@s+ ', ' ' '+rtrim(@fname)+ ' ' ' '
fetch next from tb into @fname
end
close tb
deallocate tb
exec( 'sp_attach_single_file_db ' ' '+@dbname+ ' ' ' '+@s)
go
------------------------------------------------------------------------------------
也可在企业管理里收缩日志:
--收缩数据库
dbcc shrinkdatabase( '数据库名 ',0,notruncate)
DUMP TRANSACTION [库名] WITH NO_LOG
下例将 UserDB 用户数据库中名为 DataFil1 的文件收缩到 7 MB。
USE UserDB
GO
DBCC SHRINKFILE (DataFil1, 7)
下例将 UserDB 用户数据库中的文件减小,以使 UserDB 中的文件有 10% 的可用空间。
DBCC SHRINKDATABASE (UserDB, 10)
GO
#3
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.
as
declare @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 ' '
end
set 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
#4
收缩文件
把大字段的单独放到其他服务器
把大字段的单独放到其他服务器
#5
NTEXT字段占据了大量空间
#6
重建索引看看,如果你的数据库非常非常活跃,sqlserver可能会错误的给索引分配额外的空间来应对.重建完后再shrinkfile或者shrinkdatabase
#7
顺便说一下 从这个结果来看 好像并没什么问题
#1
sp_spaceused 'object','true'
#2
看看这堆文字是否有用?
*--压缩数据库的通用存储过程
压缩日志及数据库文件大小
因为要对数据库进行分离处理
所以存储过程不能创建在被压缩的数据库中
--邹建 2004.03(引用请保留此信息)--*/
/*--调用示例
exec p_compdb 'test '
--*/
use master --注意,此存储过程要建在master数据库中
go
if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[p_compdb] ') and OBJECTPROPERTY(id, N 'IsProcedure ') = 1)
drop procedure [dbo].[p_compdb]
GO
create proc p_compdb
@dbname sysname, --要压缩的数据库名
@bkdatabase bit=1, --因为分离日志的步骤中,可能会损坏数据库,所以你可以选择是否自动数据库
@bkfname nvarchar(260)= ' ' --备份的文件名,如果不指定,自动备份到默认备份目录,备份文件名为:数据库名+日期时间
as
--1.清空日志
exec( 'DUMP TRANSACTION [ '+@dbname+ '] WITH NO_LOG ')
--2.截断事务日志:
exec( 'BACKUP LOG [ '+@dbname+ '] WITH NO_LOG ')
--3.收缩数据库文件(如果不压缩,数据库的文件不会减小
exec( 'DBCC SHRINKDATABASE([ '+@dbname+ ']) ')
--4.设置自动收缩
exec( 'EXEC sp_dboption ' ' '+@dbname+ ' ' ', ' 'autoshrink ' ', ' 'TRUE ' ' ')
--后面的步骤有一定危险,你可以可以选择是否应该这些步骤
--5.分离数据库
if @bkdatabase=1
begin
if isnull(@bkfname, ' ')= ' '
set @bkfname=@dbname+ '_ '+convert(varchar,getdate(),112)
+replace(convert(varchar,getdate(),108), ': ', ' ')
select 提示信息= '备份数据库到SQL 默认备份目录,备份文件名: '+@bkfname
exec( 'backup database [ '+@dbname+ '] to disk= ' ' '+@bkfname+ ' ' ' ')
end
--进行分离处理
create table #t(fname nvarchar(260),type int)
exec( 'insert into #t select filename,type=status&0x40 from [ '+@dbname+ ']..sysfiles ')
exec( 'sp_detach_db ' ' '+@dbname+ ' ' ' ')
--删除日志文件
declare @fname nvarchar(260),@s varchar(8000)
declare tb cursor local for select fname from #t where type=64
open tb
fetch next from tb into @fname
while @@fetch_status=0
begin
set @s= 'del " '+rtrim(@fname)+ '" '
exec master..xp_cmdshell @s,no_output
fetch next from tb into @fname
end
close tb
deallocate tb
--附加数据库
set @s= ' '
declare tb cursor local for select fname from #t where type=0
open tb
fetch next from tb into @fname
while @@fetch_status=0
begin
set @s=@s+ ', ' ' '+rtrim(@fname)+ ' ' ' '
fetch next from tb into @fname
end
close tb
deallocate tb
exec( 'sp_attach_single_file_db ' ' '+@dbname+ ' ' ' '+@s)
go
------------------------------------------------------------------------------------
也可在企业管理里收缩日志:
--收缩数据库
dbcc shrinkdatabase( '数据库名 ',0,notruncate)
DUMP TRANSACTION [库名] WITH NO_LOG
下例将 UserDB 用户数据库中名为 DataFil1 的文件收缩到 7 MB。
USE UserDB
GO
DBCC SHRINKFILE (DataFil1, 7)
下例将 UserDB 用户数据库中的文件减小,以使 UserDB 中的文件有 10% 的可用空间。
DBCC SHRINKDATABASE (UserDB, 10)
GO
*--压缩数据库的通用存储过程
压缩日志及数据库文件大小
因为要对数据库进行分离处理
所以存储过程不能创建在被压缩的数据库中
--邹建 2004.03(引用请保留此信息)--*/
/*--调用示例
exec p_compdb 'test '
--*/
use master --注意,此存储过程要建在master数据库中
go
if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[p_compdb] ') and OBJECTPROPERTY(id, N 'IsProcedure ') = 1)
drop procedure [dbo].[p_compdb]
GO
create proc p_compdb
@dbname sysname, --要压缩的数据库名
@bkdatabase bit=1, --因为分离日志的步骤中,可能会损坏数据库,所以你可以选择是否自动数据库
@bkfname nvarchar(260)= ' ' --备份的文件名,如果不指定,自动备份到默认备份目录,备份文件名为:数据库名+日期时间
as
--1.清空日志
exec( 'DUMP TRANSACTION [ '+@dbname+ '] WITH NO_LOG ')
--2.截断事务日志:
exec( 'BACKUP LOG [ '+@dbname+ '] WITH NO_LOG ')
--3.收缩数据库文件(如果不压缩,数据库的文件不会减小
exec( 'DBCC SHRINKDATABASE([ '+@dbname+ ']) ')
--4.设置自动收缩
exec( 'EXEC sp_dboption ' ' '+@dbname+ ' ' ', ' 'autoshrink ' ', ' 'TRUE ' ' ')
--后面的步骤有一定危险,你可以可以选择是否应该这些步骤
--5.分离数据库
if @bkdatabase=1
begin
if isnull(@bkfname, ' ')= ' '
set @bkfname=@dbname+ '_ '+convert(varchar,getdate(),112)
+replace(convert(varchar,getdate(),108), ': ', ' ')
select 提示信息= '备份数据库到SQL 默认备份目录,备份文件名: '+@bkfname
exec( 'backup database [ '+@dbname+ '] to disk= ' ' '+@bkfname+ ' ' ' ')
end
--进行分离处理
create table #t(fname nvarchar(260),type int)
exec( 'insert into #t select filename,type=status&0x40 from [ '+@dbname+ ']..sysfiles ')
exec( 'sp_detach_db ' ' '+@dbname+ ' ' ' ')
--删除日志文件
declare @fname nvarchar(260),@s varchar(8000)
declare tb cursor local for select fname from #t where type=64
open tb
fetch next from tb into @fname
while @@fetch_status=0
begin
set @s= 'del " '+rtrim(@fname)+ '" '
exec master..xp_cmdshell @s,no_output
fetch next from tb into @fname
end
close tb
deallocate tb
--附加数据库
set @s= ' '
declare tb cursor local for select fname from #t where type=0
open tb
fetch next from tb into @fname
while @@fetch_status=0
begin
set @s=@s+ ', ' ' '+rtrim(@fname)+ ' ' ' '
fetch next from tb into @fname
end
close tb
deallocate tb
exec( 'sp_attach_single_file_db ' ' '+@dbname+ ' ' ' '+@s)
go
------------------------------------------------------------------------------------
也可在企业管理里收缩日志:
--收缩数据库
dbcc shrinkdatabase( '数据库名 ',0,notruncate)
DUMP TRANSACTION [库名] WITH NO_LOG
下例将 UserDB 用户数据库中名为 DataFil1 的文件收缩到 7 MB。
USE UserDB
GO
DBCC SHRINKFILE (DataFil1, 7)
下例将 UserDB 用户数据库中的文件减小,以使 UserDB 中的文件有 10% 的可用空间。
DBCC SHRINKDATABASE (UserDB, 10)
GO
#3
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.
as
declare @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 ' '
end
set 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
#4
收缩文件
把大字段的单独放到其他服务器
把大字段的单独放到其他服务器
#5
NTEXT字段占据了大量空间
#6
重建索引看看,如果你的数据库非常非常活跃,sqlserver可能会错误的给索引分配额外的空间来应对.重建完后再shrinkfile或者shrinkdatabase
#7
顺便说一下 从这个结果来看 好像并没什么问题