среда, 27 июня 2012 г.

Размер всех баз данных MS SQL сервера

   Использование sp_spaceused на экземпляре MS SQL Server-е, когда на сервере находится довольно много баз данных не очень удобно, и получается не информативно, да и иногда хочется получить результат одной строкой, чтобы, например, результат записать в таблицу.

Ниже предлагаю скрипт, которые позволяет получить данные в таком виде, текст скрипта основан все на той же процедуре sp_spaceused.

declare @t table --
(dbname varchar(50),
size dec(15,2)
,datafile_size dec(15,2),
log_size dec(15,2),
unnlocatespace dec(15,2),
reserved dec(15,2),
data dec(15,2),
index_size dec(15,2),
unused dec(15,2))
declare @db_name varchar(60)
declare @sql varchar(max)
declare cur cursor for

select name,* from sys.databases where database_id>4 and state_desc='online'
 -- .
open cur
FETCH NEXT FROM cur into @db_name
while @@fetch_status=0
begin





set @sql='USE ['+@db_name+'];
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
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

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
select database_name = db_name(),
database_size = (@dbsize+@logsize)
* 8192 / 1048576,@dbsize* 8192 / 1048576 ''datafile_ size (Mb)'',@logsize* 8192 / 1048576 ''log_size (Mb)'',
''unallocated space'' =Convert(dec(20,2),(case when @dbsize >= @reservedpages then
(convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages))
* 8192 / 1048576 else 0 end)),
reserved =( @reservedpages * 8192 / 1024)/1024,
data = (@pages * 8192 / 1024)/1024,
index_size = (((@usedpages - @pages) * 8192 / 1024) )/1024,
unused = ((@reservedpages - @usedpages) * 8192 / 1024)/1024'


insert into @t exec( @sql )
--print @sql
FETCH NEXT FROM cur into @db_name
end



close
cur
deallocate cur;
select * from @t order by dbname

Результат выводится по всем базам данных, за исключением системных, что можно поменять в запросе(select name from sys.databases where database_id>4).
Пользуйтесь:)


4 комментария :

  1. Все это можно просмотреть проще: Management Studio> menu View > Object Explorer Details. Теперь стоя на узле дерева можно видеть всю необходимую информацию, в частность по всем базам данных. Если соответствующих колонок нет в списке (а по умолчанию размеры баз не показываются), то их можно добавить (клик правой кнопки мышки на шапке таблицы). При желании все это можно экспортировать в эксель

    ОтветитьУдалить
    Ответы
    1. Вы указали просмотр графической информации в студии ms sql 2012 . Я указал, как один из способов получения данной информации с возможностью дальнейшего его анализа(вставлять в таблицы, в отчеты и т.д)

      Удалить
    2. Для вывода графической информации используется запрос, который легко получить с помощью профайлера и затем тоже его можно использовать :)

      Удалить
    3. используйте, если не ошибаюсь, там процедуры(часть кода которго здесь и используется), которые несут много, инфы которой не надо

      Удалить