这个新的参数@OneResultSet 就跟它的名字一样,将结果变成一个集合.
原来的SP_SpaceUsed将结果分成了两部分. 实际上使用sp_helptext sp_SpaceUsed 查看到的结果里面
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.
@mode varchar(11) = 'ALL', -- Param. for specifying whether to calculate space usage for
-- local data only or remote data archive only or both.
@oneresultset bit = 0 -- Param. for specifying whether to return only one result set /*--------------------------定义---------------------------*/
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
,@remotesql nvarchar(1000)
,@isupdateusage bit
,@errormessage nvarchar(4000)
,@errornumber int
,@errorseverity int
,@errorstate int
,@errorline int;
DECLARE @summary_tmp_table table(
database_name nvarchar(128),
database_size varchar(18),
unallocated_space varchar(18),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18));
DECLARE @detail_tmp_table table(
name nvarchar(128),
rows bigint,
reserved nvarchar(80),
data nvarchar(80),
index_size nvarchar(80),
unused nvarchar(80));
/*
** Check to see if user wants usages updated.
*/
if @updateusage is not null
begin
select @updateusage=lower(@updateusage)
if @updateusage ='true'
begin
set @isupdateusage = 1
end
else
begin
if @updateusage = 'false'
begin
set @isupdateusage = 0
end
else
begin
raiserror(15143,-1,-1,@updateusage)
return(1)
end
end
end
/*
** Validate the @mode parameter.
*/
IF UPPER(@mode) NOT IN('ALL', 'LOCAL_ONLY', 'REMOTE_ONLY')
BEGIN
raiserror (14822, -1, -1, @mode);
return (1)
END
set nocount on
/*
** 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
ELSE
begin
/*
** Now we know the object type is in ('U ','S ','V ','SQ','IT')
** All types of objects always have physical size except for V (views). Only indexed views have actual sizes.
** Since an un-indexed view does not occupy physical storage space, querying its space usage is meaningless.
*/
IF @type = 'V '
begin
IF NOT EXISTS
(SELECT TOP 1 object_id
FROM sys.dm_db_partition_stats
WHERE object_id = @id) -- An un-indexed view DOES NOT have an row in sys.dm_db_partition_stats.
begin
SELECT
@objname AS name,
NULL AS rows,
NULL AS reserved,
NULL AS data,
'0 KB' AS index_size,
'0 KB' AS unused
return (0)
end
end
end
end
/*
** Update usages if user specified to do so.
*/
if @isupdateusage = 1
begin
if @objname is null
dbcc updateusage(0) with no_infomsgs
else
dbcc updateusage(0,@objname) with no_infomsgs
print ' '
end
/*
** If @id is null, then we want summary data.
*/
if @id is null
begin
/*
** Calculate local space usage if mode is LOCAL_ONLY or ALL
*/
IF UPPER(@mode) IN ('ALL', 'LOCAL_ONLY')
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 and semantic index internal tables are not considered "data", but is part of "index_size"
When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0
When a.type <> 1 and p.index_id < 2 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
/*
** Calculate the summary data and insert them into the cache table
** 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)
** note that unallocated space could not be negative
*/
INSERT INTO @summary_tmp_table
SELECT
db_name(),
LTRIM(STR((CONVERT (dec (15,2),@dbsize) + CONVERT (dec (15,2),@logsize)) *
8192 / 1048576,15,2) + ' MB'),
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'),
LTRIM(STR(@reservedpages * 8192 / 1024.,15,0) + ' KB'),
LTRIM(STR(@pages * 8192 / 1024.,15,0) + ' KB'),
LTRIM(STR((@usedpages - @pages) * 8192 / 1024.,15,0) + ' KB'),
LTRIM(STR((@reservedpages - @usedpages) * 8192 / 1024.,15,0) + ' KB')
END
/*
** Include remote results if the user specified no arguments , or remote only.
*/
IF UPPER(@mode) IN ('ALL', 'REMOTE_ONLY')
BEGIN
/*
** If the database is not stretched, it should not have a remote part, thus skipping remote results.
*/
IF NOT EXISTS(SELECT * FROM sys.remote_data_archive_databases)
BEGIN
IF UPPER(@mode) = 'REMOTE_ONLY'
BEGIN
raiserror(14821, 16, 1);
return (1)
END
END
/*
** Since the database is stretched and the remote database should exist, we should include remote results
*/
ELSE
BEGIN
SET @remotesql = CONCAT(
N'sys.sp_spaceused_remote_data_archive ',
@isupdateusage)
BEGIN TRY
INSERT INTO @summary_tmp_table EXECUTE(@remotesql); /*------------------------返回的第一个表--------------------------*/
END TRY
BEGIN CATCH
set @errornumber = ERROR_NUMBER()
set @errorseverity = ERROR_SEVERITY()
set @errorstate = ERROR_STATE()
set @errorline = ERROR_LINE()
set @errormessage = ERROR_MESSAGE()
raiserror(14827, 16, 2, @errornumber, @errorseverity, @errorstate, @errorline, @errormessage)
IF UPPER(@mode) = 'REMOTE_ONLY'
BEGIN
return (1)
END
END CATCH
END
END
IF @oneresultset = 1
BEGIN
SELECT
database_name = db_name(),
database_size = LTRIM (STR ((SUM (CONVERT (dec (15, 2), SUBSTRING(s.database_size, 1, CHARINDEX(' ', s.database_size))))),15,2) + ' MB'),
'unallocated space' = LTRIM (STR ((SUM (CONVERT (dec (15, 2), SUBSTRING(s.unallocated_space, 1, CHARINDEX(' ', s.unallocated_space))))),15,2) + ' MB'),
reserved = LTRIM (STR (SUM (CAST (SUBSTRING(s.reserved, 1, CHARINDEX(' ', s.reserved)) AS bigint))) + ' KB'),
data = LTRIM (STR (SUM( CAST( SUBSTRING(s.data, 1, CHARINDEX(' ', s.data)) AS bigint))) + ' KB'),
index_size = LTRIM (STR (SUM( CAST( SUBSTRING(s.index_size, 1, CHARINDEX(' ', s.index_size)) AS bigint))) + ' KB'),
unused = LTRIM (STR (SUM( CAST( SUBSTRING(s.unused, 1, CHARINDEX(' ', s.unused)) AS bigint))) + ' KB')
FROM @summary_tmp_table AS s
END
ELSE
BEGIN
SELECT
database_name = db_name(),
database_size = LTRIM (STR ((SUM (CONVERT (dec (15, 2), SUBSTRING(s.database_size, 1, CHARINDEX(' ', s.database_size))))),15,2) + ' MB'),
'unallocated space' = LTRIM (STR ((SUM (CONVERT (dec (15, 2), SUBSTRING(s.unallocated_space, 1, CHARINDEX(' ', s.unallocated_space))))),15,2) + ' MB')
FROM @summary_tmp_table AS s
SELECT
reserved = LTRIM (STR (SUM (CAST (SUBSTRING(s.reserved, 1, CHARINDEX(' ', s.reserved)) AS bigint))) + ' KB'),
data = LTRIM (STR (SUM( CAST( SUBSTRING(s.data, 1, CHARINDEX(' ', s.data)) AS bigint))) + ' KB'),
index_size = LTRIM (STR (SUM( CAST( SUBSTRING(s.index_size, 1, CHARINDEX(' ', s.index_size)) AS bigint))) + ' KB'),
unused = LTRIM (STR (SUM( CAST( SUBSTRING(s.unused, 1, CHARINDEX(' ', s.unused)) AS bigint))) + ' KB')
FROM @summary_tmp_table AS s
END
END
/*
** We want a particular object.
*/
ELSE
BEGIN
/*
** Include remote results if the user expects remote space usage.
*/
IF UPPER(@mode) = 'ALL' or UPPER(@mode) = 'REMOTE_ONLY'
BEGIN
/*
** If the object is stretched, we should include remote space usage into the result.
*/
IF EXISTS (SELECT * FROM sys.remote_data_archive_tables WHERE object_id = @id AND remote_table_name IS NOT NULL)
BEGIN
SET @remotesql = CONCAT(
N'sys.sp_spaceused_remote_data_archive ',
@isupdateusage,
N' , ',
@id)
BEGIN TRY
INSERT INTO @detail_tmp_table EXECUTE(@remotesql)
END TRY
BEGIN CATCH
set @errornumber = ERROR_NUMBER()
set @errorseverity = ERROR_SEVERITY()
set @errorstate = ERROR_STATE()
set @errorline = ERROR_LINE()
set @errormessage = ERROR_MESSAGE()
raiserror(14827, 16, 3, @errornumber, @errorseverity, @errorstate, @errorline, @errormessage)
IF UPPER(@mode) = 'REMOTE_ONLY'
BEGIN
return (1)
END
END CATCH
END
/*
** If the object is not stretched, the object shouldn't have a remote part, thus skipping remote results.
*/
ELSE
BEGIN
IF UPPER(@mode) = 'REMOTE_ONLY'
BEGIN
RAISERROR(14821, 16, 2);
RETURN (1)
END
END
END
/*
** Calculate local space usage if mode is LOCAL_ONLY or ALL
*/
IF UPPER(@mode) IN ('ALL', 'LOCAL_ONLY')
BEGIN
/*
** Now calculate the summary data.
* Note that LOB Data and Row-overflow Data are counted as Data Pages for the base table
* For non-clustered indices they are counted towards the index 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 0
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,207,211,212,213,214,215,216,221,222,236)) > 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,207,211,212,213,214,215,216,221,222,236) AND p.object_id = it.object_id;
END
INSERT INTO @detail_tmp_table /*------------------------返回的第二个表--------------------------*/
SELECT
OBJECT_NAME (@id),
CONVERT (char(20), @rowCount),
LTRIM (STR (@reservedpages * 8, 15, 0) + ' KB'),
LTRIM (STR (@pages * 8, 15, 0) + ' KB'),
LTRIM (STR ((CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END) * 8, 15, 0) + ' KB'),
LTRIM (STR ((CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END) * 8, 15, 0) + ' KB')
END
SELECT @objname AS name,
CONVERT (char(20), SUM(rows)) AS rows,
LTRIM (STR (SUM (CAST (SUBSTRING(reserved, 1, CHARINDEX(' ', reserved)) AS bigint))) + ' KB' ) AS reserved,
LTRIM (STR (SUM (CAST (SUBSTRING(data, 1, CHARINDEX(' ', data)) AS bigint))) + ' KB') AS data,
LTRIM (STR (SUM (CAST (SUBSTRING(index_size, 1, CHARINDEX(' ', index_size)) AS bigint))) + ' KB') AS index_size,
LTRIM (STR (SUM (CAST (SUBSTRING(unused, 1, CHARINDEX(' ', unused)) AS bigint))) + ' KB') AS unused
FROM @detail_tmp_table
END
return (0) -- sp_spaceused
新的@OneResultSet使用之后呢?
@oneresultset bit = 0 -- Param. for specifying whether to return only one result set
嵌入使用sp_MSforeachdb,可以一次获取所有数据库的空间使用状况.
还是非常不方便,如果可以在一个resultset就好了. 请看下面的方法:
create table #spaceused
(database_name varchar(100)
,database_size varchar(20)
,unallocated_space varchar(20)
,reserved varchar(20)
,data varchar(20)
,index_size varchar(20)
,unused varchar(20)
)
go
insert into #spaceused
exec sp_MSforeachdb
@command1= '
use [?]
exec sp_spaceused @oneresultset=1'
go
select
database_name [Database Name]
,cast(replace(database_size,'MB','') as decimal(10,2)) [Database Size (MB)]
,cast(replace(unallocated_space,'MB','') as decimal(10,2)) [Free Space (MB)]
,(cast(replace(reserved,'KB','') as int))/1024 [Reserved (MB)]
,(cast(replace(data,'KB','') as int))/1024 [Data Space (MB)]
,(cast(replace(index_size,'KB','') as int))/1024 [Index Space (MB)]
,(cast(replace(unused,'KB','') as int))/1024 [Unused Space (MB)]
from #spaceused
order by [Unused Space (MB)] desc
有了参考,就好办了. 下面是我写的一个批处理,生成可以调整数据库大小修改的DBCC
WITH tt
AS ( SELECT database_name [Database Name] ,
CAST(REPLACE(database_size, 'MB', '') AS DECIMAL(10, 2)) [Database Size (MB)] ,
CAST(REPLACE(unallocated_space, 'MB', '') AS DECIMAL(10,
2)) [Free Space (MB)] ,
( CAST(REPLACE(reserved, 'KB', '') AS INT) ) / 1024 [Reserved (MB)] ,
( CAST(REPLACE(data, 'KB', '') AS INT) ) / 1024 [Data Space (MB)] ,
( CAST(REPLACE(index_size, 'KB', '') AS INT) ) / 1024 [Index Space (MB)] ,
( CAST(REPLACE(unused, 'KB', '') AS INT) ) / 1024 [Unused Space (MB)]
FROM #spaceused
)
SELECT DB_NAME(database_id) [Database Name] ,
'DBCC SHRINKDATABASE(N''' + DB_NAME(database_id) + ''', 30 );' [Shrink Statement]
FROM sys.databases
WHERE database_id IN ( SELECT TOP ( 10 )
DB_ID([Database Name])
FROM tt
ORDER BY [Unused Space (MB)] DESC );
注意收缩数据库的大小哦.