Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Процедура SP_SPACEUSED  [new]
RedMouse
Member

Откуда:
Сообщений: 2
Доброго времени суток! Возникла следующая проблема. Сервер MSSQL 2005. Для того, чтобы получить размер для всей базы данных целиком, процедура SP_SPACEUSED должна запускаться без параметров. В этом случае она возвращает два выходных набора. Таким образом, нельзя использовать конструкцию insert into #TAB_TEMP exec sp_spaceused. Каким образом, тогда можно сохранить полученные результаты? Возможно есть динамические представления, которыми можно получить аналогичные данные? (Я не могу найти. Реализация с помощью dataset не подходит, информация должна обрабатыватся на сервере)
2 фев 12, 17:16    [12021180]     Ответить | Цитировать Сообщить модератору
 Re: Процедура SP_SPACEUSED  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Ненужное выпиливайте сами.

if object_id('tempdb..#t') is not null
    drop table #t
  
create table #t(
    name            nvarchar(128) 
    , rows          char(11) 
    , reserved      varchar(18) 
    , data          varchar(18) 
    , index_size    varchar(18) 
    , unused        varchar(18) 
)

exec sp_msforeachtable 'insert into #t exec sp_spaceused ''?'''

alter table #t add reserved_kb  bigint  null
alter table #t add rows_int     bigint  null
alter table #t add filegroup	sysname null
--alter table #t add dataspace    sysname null

go

declare
    @fg             sysname
    , @tn_template  sysname
    
update #t set rows_int = cast(rows as bigint)
update #t set reserved_kb = 
    case 
        when charindex('KB',reserved,  1) > 0 then cast(replace(reserved, 'KB', '') as bigint) 
        when charindex('MB',reserved,  1) > 0 then cast(replace(reserved, 'MB', '') as bigint) * 1024
    end

update t set
  t.filegroup = d.name
from        #t              t   
inner join  sys.indexes     i   on 
    object_id(t.name) = i.object_id
    and i.index_id < 2
inner join  sys.data_spaces d on
    d.data_space_id = i.data_space_id    
    
select 
    * 
from #t     t
where
    (
        nullif(@fg, '') is null
        or t.filegroup = @fg
    )
    and (
        @tn_template is null
        or t.name like @tn_template
    )        
    
order by t.reserved_kb desc

select
    isnull( a.filegroup, b.groupname)
    , a.reserved_kb
    , group_size_kb = cast(b.group_size as bigint) * 1024
    , b.percent_used
from (
    select 
        tag             = 1
        , filegroup     
        , reserved_kb   = sum(reserved_kb)
    from #t     t
    group by t.filegroup
    union all select
        tag             = 0
        , 'TOTAL'
        , reserved_kb   = sum(reserved_kb)
    from  #t      
) a
full join (
    select
        b.groupname
        , a.*
        , percent_used      = cast( space_used * 100.00 / group_size as decimal(5, 2) )
        , percent_unused    = cast( space_unused * 100.00 / group_size as decimal (5, 2) )
    from (
        select
            a.data_space_id
            , group_size    = sum( [fl_size] )
            , space_used    = sum( [fl_used] ) 
            , space_unused  = sum( [fl_unused] )
        from (
            select
                a.data_space_id
	            , [fl_size]	= 
		            convert(int,round((b.size*1.000)/128.000,0))
	            , [fl_used]	=
		            convert(int,round(fileproperty(b.name,'SpaceUsed')/128.000,0))
	            , [fl_unused]	=
		            convert(int,round((b.size-fileproperty(b.name,'SpaceUsed'))/128.000,0))    
            from        sys.filegroups  a
            inner join  sys.sysfiles    b   on b.groupid = a.data_space_id
        ) a
        group by a.data_space_id
    ) a
    inner join sys.sysfilegroups b on a.data_space_id = b.groupid
) b on a.filegroup = b.groupname   
order by isnull(Tag, 1) desc, reserved_kb desc
go

;with c as (
    select
        a.*
        , percent_used = cast ( a.[used, GB] / a.[total, GB] * 100.0 as decimal (38, 2) )
    from (
        select 
              fg.name
              , [used, GB] = cast ( (sum(au.total_pages * 1.) * 8 * 1024) / 1024 / 1024 / 1024 as decimal(38, 2) )
              , [total, GB] = (
                select
                    cast ( sum ( f.size*1.000 /128.000 ) / 1024 as decimal (38, 2) )
                from sys.sysfiles    f   
                where
                    f.groupid = fg.data_space_id      
              )
        from        sys.filegroups          fg
        inner join  sys.allocation_units    au on fg.data_space_id = au.data_space_id 
        group by 
              fg.data_space_id
              , fg.name
    ) a
)    
select
    a.name
    , [used, GB]
    , [total, GB]
    , percent_used
from (
    select
        *
        , tag = 0
    from c    
    union all
    select 
        * 
    from (
        select
            name = 'TOTAL'
            , [used, GB] = sum(x.[used, GB])
            , [total, GB] = sum(x.[total, GB])
            , percent_used = cast ( sum(x.[used, GB]) / sum (x.[total, GB]) * 100.0 as decimal (38, 2) )
            , tag = 1
        from c x
    ) b
) a    
order by tag, [total, GB] desc
go


Сообщение было отредактировано: 2 фев 12, 17:18
2 фев 12, 17:18    [12021208]     Ответить | Цитировать Сообщить модератору
 Re: Процедура SP_SPACEUSED  [new]
Eagle_84
Member

Откуда: Москва
Сообщений: 1535
а что именно нужно? только размер базы (данные и лог) или что-то еще?
2 фев 12, 17:18    [12021210]     Ответить | Цитировать Сообщить модератору
 Re: Процедура SP_SPACEUSED  [new]
Andraptor
Member

Откуда: Ukraine, Kharkiv
Сообщений: 291
SELECT name as [FileName]
,size*8/1024 as FileSizeInMB
,FILEPROPERTY(name, 'SpaceUsed')*8/1024 as UsedSpaceInMB
,size*8/1024 - FILEPROPERTY(name, 'SpaceUsed')*8/1024 AS AvailableSpaceInMB 
,round(convert(real,(size*8/1024 - FILEPROPERTY(name, 'SpaceUsed')*8/1024))/(size*8/1024)*100,0) as AvailableSpaceInPerc
FROM sys.database_files
UNION ALL
SELECT 'ВСЕГО'
,sum(size*8/1024) as FileSizeInMB
,sum(FILEPROPERTY(name, 'SpaceUsed')*8/1024) as UsedSpaceInMB
,sum(size*8/1024 - FILEPROPERTY(name, 'SpaceUsed')*8/1024) AS AvailableSpaceInMB 
,round(convert(real,sum(size*8/1024 - FILEPROPERTY(name, 'SpaceUsed')*8/1024))/sum(size*8/1024)*100,0) as AvailableSpaceInPerc
FROM sys.database_files;
2 фев 12, 18:41    [12021958]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Процедура SP_SPACEUSED  [new]
Anatoliy
Member

Откуда: Kiev
Сообщений: 100
Просто укоротить системную процедуру sp_spaceused

CREATE procedure dbo.SystemInfo_Spaceused --- this is shorten copy of sys.sp_spaceused

AS

DECLARE @dbsize bigint
		,@pages	bigint			-- Working variable for size calc.
		,@logsize bigint
		,@reservedpages  bigint
		,@usedpages  bigint

SET NOCOUNT ON
	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-Docid is not considered "data", but is part of "index_size"
					WHEN it.internal_type IN (202,204) 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')
RETURN(0) -- sp_spaceused
23 окт 13, 14:08    [15020628]     Ответить | Цитировать Сообщить модератору
 Re: Процедура SP_SPACEUSED  [new]
Anatoliy
Member

Откуда: Kiev
Сообщений: 100
Вообще, если честно запутался. Сама процедура sp_spaceused выполняется на всех базах. Причем под логинами, под которыми запускается приложение.
Ну я взял исходный текст sp_spaceused и укоротил ее под себя (см. предыдущий пост). Запустил на тестовой базе. Все ОК. Запускаю на рабочей базе - ругается:
"The SELECT permission was denied on the object 'sysfiles', database 'mssqlsystemresource', schema 'sys'."
И ведь, что странно, исходная процедура sp_spaceused также обращается к "from dbo.sysfiles". Но выполняется под тем же логином, что и моя процедура.
В общем пришлось идти обходным путем. Мне то, собственно необходим размер базы получить на клиенте. Вот я и создал процедуру - обертку:

CREATE PROCEDURE [dbo].[SystemInfo_Spaceused]
AS 
	EXEC sp_spaceused
GO 

А уже ее вызываю с клиента и считываю значение с помощью System.Data.SqlClient.SqlDataReader
Как то так....
24 окт 13, 09:29    [15024177]     Ответить | Цитировать Сообщить модератору
 Re: Процедура SP_SPACEUSED  [new]
Glory
Member

Откуда:
Сообщений: 104751
Anatoliy
исходная процедура sp_spaceused также обращается к "from dbo.sysfiles".

Только вот sp_spaceused - системная процедура, а не пользовательская
24 окт 13, 09:33    [15024194]     Ответить | Цитировать Сообщить модератору
 Re: Процедура SP_SPACEUSED  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32169
Anatoliy
Мне то, собственно необходим размер базы получить на клиенте. Вот я и создал процедуру - обертку:
Почему бы просто не вызвать системную sp_spaceused из клиента??? Зачем обёртка?
24 окт 13, 09:40    [15024218]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить