среда, июля 25, 2007

sysfiles и файлы базы данных

в продолжении работы над утилиткой контроля за удаленными инстансами sql server-ов выкладываю скрипт, результаты которого также собираюсь отображать в виде репорта о состоянии sql server/// скрипт выдает отчет о файлах баз данных, их характеристики, текущий размер, а также (пока в виде нераспарсенной строки результата dir ) свободное место на дисках, где собственно и лежат файлы базы данных...

сам скрипт:



if isnull(object_id('tempdb..#files_info'), 0) > 0
drop table #files_info

create table #files_info ( database_name varchar(100) null default (db_name()),
critery int,
disk_letter varchar(10) null,
device varchar(10) null,
growth_expression varchar(100) null,
data_size_expression varchar(100) null,
data_size int null,
logic_file_name varchar(100) null,
filename varchar(100) null,
free_disk_space varchar(100) null default('')
)

declare @databases cursor ,
@d_name varchar(100),
@sql_script nvarchar(4000)

set @databases= cursor local forward_only for
select name
from master..sysdatabases
where sid <> 0x01


open @databases
fetch next from @databases into @d_name

while (@@fetch_status = 0 )
begin
set @sql_script =
'
insert into #files_info
select ''' + @d_name + ''' database_name, *
from (
select 0 critery,
substring(filename, 0, 3) disk_letter,
case
when (status & 0x40) = 0 then ''data file''
else ''log file''
end device,
case
when (status & 1048576) = 0 then cast( (growth * 8 /1024) as varchar(10)) + '' Mb ''
else cast(growth as varchar(10)) + '' % ''
end growth_expression,
''~'' + cast((size * 8 / 1024) as varchar(10)) + '' Mb '' data_size_expression,
(size * 8 / 1024) data_size,
name logic_file_name,
filename,
'''' free_disk_space
from ' + @d_name + '..sysfiles (nolock)
union
select 1 critery,
substring(filename, 0, 3) disk_letter,
'''' device,
'''' growth_expression,
''~'' + cast((sum(size) * 8 / 1024 /1024) as varchar(10)) + '' Gb '' data_size_expression,
(sum(size) * 8 / 1024) data_size,
'''' logic_file_name,
'''' filename,
'''' free_disk_space
from ' + @d_name + '..sysfiles (nolock)
group by substring(filename, 0, 3)
union
select 2 critery,
''total:'' disk_letter,
'''' device,
'''' growth_expression,
''~'' + cast((sum(size) * 8 / 1024 /1024) as varchar(10)) + '' Gb '' data_size_expression,
(sum(size) * 8 / 1024 ) data_size,
'''' logic_file_name,
'''' filename,
'''' free_disk_space
from ' + @d_name + '..sysfiles (nolock)
) innerselect
order by critery, disk_letter, device


'
execute sp_executesql @sql_script

fetch next from @databases into @d_name
end

-- свободное место на дисках
declare @disk_letters cursor,
@disk_letter varchar(10)

set @disk_letters = cursor local forward_only for
select distinct disk_letter
from #files_info
where critery = '1'
open @disk_letters
fetch next from @disk_letters into @disk_letter

while (@@fetch_status = 0)
begin
create table #output (text nvarchar(1000))


set @sql_script = '
execute master..xp_cmdshell ''dir ' + @disk_letter + ' /A:D ''
'
insert into #output
execute sp_executesql @sql_script

declare @free_text varchar(100)
set @free_text = ''

select top 1 @free_text = text
from #output
where text like '%bytes free%'
drop table #output

update #files_info
set free_disk_space = @free_text
where critery = 1
and disk_letter = @disk_letter

fetch next from @disk_letters into @disk_letter
end

close @disk_letters
deallocate @disk_letters

select '" + name + @"' connection_string_name, *
from #files_info
union all
select '" + name + @"' connection_string_name,
'all databases' database_name,
3 critery,
'total:' disk_letter,
'' device,
'' growth_expression,
'~' + cast(sum(data_size) as varchar(10)) + ' Gb ' data_size_expression,
sum(data_size) data_size,
'' logic_file_name,
'' filename,
'' free_disk_space
from #files_info
where critery = 2



результат приблизительно такой:



Комментариев нет: