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

Откуда:
Сообщений: 8
Здравствуйте. Подскажите пожалуйста.

Есть 4 сервера, в каждом по 30-40 БД. Само собой, базы расширяются, и следить за всеми через ПКМ->свойства - очень удручает.
Логи режутся, но хочется, чтобы и доступное место было как можно меньше.

Был написан скриптец с курсором (не кидайтесь тапками, я не знаю как это сделать лучше) и использованием sp_spaceused.
DECLARE @db_name NVARCHAR(50)

DECLARE @sql NVARCHAR(100)

DECLARE CUR CURSOR
FOR SELECT NAME
    FROM   sys.databases
    WHERE  name LIKE 'A%' --нужные базы начинаются на А (можно конечно поставить условие database_id > 4, но мне нужны именно базы на А)
		 AND state_desc = 'ONLINE'

OPEN CUR

FETCH NEXT FROM CUR INTO @db_name

WHILE @@fetch_status = 0
    BEGIN
	   SELECT @sql = 'USE ['+@db_name+'];'
	   SELECT @sql = @sql+'EXEC sp_spaceused'
	   EXECUTE (@sql)
	   FETCH NEXT FROM CUR INTO @db_name
    END

CLOSE CUR

DEALLOCATE CUR 


Но пока этот скрипт выполняется (для 10 не таких больших БД ~ 10 секунд) можно ежа родить. А уж если запускать через "Зарегистрированные серверы", там можно тушить свет и ложиться спать. Ну и плюс выводит курсор это все не в очень удобной форме.

Как еще можно сделать скриптец для всех баз на сервере, чтобы выводил это "Доступное место"? Size в sys.master_files дает не то(
17 окт 17, 09:29    [20874710]     Ответить | Цитировать Сообщить модератору
 Re: Узнать доступное место во всех БД  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30768
getred
Как еще можно сделать скриптец для всех баз на сервере, чтобы выводил это "Доступное место"?
Посмотрите текст sp_spaceused
17 окт 17, 09:51    [20874792]     Ответить | Цитировать Сообщить модератору
 Re: Узнать доступное место во всех БД  [new]
getred
Member

Откуда:
Сообщений: 8
alexeyvg, Посмотрел, спасибо. Однако все равно не понимаю, как связать его со всеми базами(
17 окт 17, 10:07    [20874857]     Ответить | Цитировать Сообщить модератору
 Re: Узнать доступное место во всех БД  [new]
aleks222
Guest
set @str = (SELECT 'EXEC ' + NAME + '..sp_spaceused;' 
                FROM   sys.databases
                WHERE  name LIKE 'A%' --нужные базы начинаются на А (можно конечно поставить условие database_id > 4, но мне нужны именно базы на А)
		             AND state_desc = 'ONLINE'
                for xml path('')
           );

exec(@str);
17 окт 17, 10:17    [20874916]     Ответить | Цитировать Сообщить модератору
 Re: Узнать доступное место во всех БД  [new]
MadMaxus
Member

Откуда:
Сообщений: 4
В разрезе файлов, файловых групп, дисков:
+

Функция dbo.ListToTable_All - аналог STRING_SPLIT (начиная с SQL 2016). Варианты реализаций недавно обсуждались на этом форуме.

declare
	@server_names varchar(8000) = '***,***'

if object_id('tempdb.dbo.#server_names', 'U') is not null
	drop table #server_names;
create table #server_names(
	server_name varchar(100)
);
insert #server_names
select '[' + item + ']'
from dbo.ListToTable_All(@server_names + ',', ',');


declare 
	@sql_command_1 varchar(max),
	@sql_command_2 varchar(max);

if object_id('tempdb..#database_files') is not null
	drop table #database_files;
create table #database_files (
	server_name varchar(100),
	db_id int,
	[db_name] varchar(100),
	type_desc varchar(20),
	FileGroupName nvarchar(200),
	FileName nvarchar(200),
	volume_mount_point nvarchar(300),
	logical_volume_name nvarchar(300),
	physical_name nvarchar(300),
	AvailableSpace_File_GB float,
	Size_File_GB float,
	AvailableSpace_Disk_GB float,
	Size_Disk_GB float,
	SpaceUsed_File_GB float,
	SpaceUsed_File float,
	SpaceUsed_Disk float,
	next_growth_gb float
);


if object_id('tempdb.dbo.#databases', 'U') is not null
	drop table #databases;
create table #databases(db_id varchar(10), db_name varchar(100));


declare @server_name varchar(100);
declare cursor_server cursor for 
	select server_name
	from #server_names;
	
open cursor_server;
fetch next from cursor_server into @server_name;

while @@fetch_status = 0
begin;
	truncate table #databases;
	set @sql_command_1 = '
		insert #databases(db_id, db_name)
		select cast(t1.database_id as varchar(10)) as db_id, t1.name as db_name
		from ' + @server_name + '.master.sys.databases t1
		where t1.state = 0;'
	exec (@sql_command_1);


	declare @db_id varchar(10), @db_name varchar(100);
	declare cursor_db cursor for 
		select t1.db_id, t1.db_name
		from #databases t1;

	open cursor_db;
	fetch next from cursor_db into @db_id, @db_name;

	while @@fetch_status = 0
	begin;
		set @sql_command_1 = '
			use [' + @db_name + '];

			select 
				''' + @server_name + ''' as server_name,
				''' + cast(@db_id as varchar(10)) + ''' as db_id,
				''' + @db_name + ''' [db_name],
				f1.type_desc,
				isnull(fg2.name, ''##  '' + f1.name) as FileGroupName, 
				f1.name as [FileName], 
				f3.volume_mount_point,
				f3.logical_volume_name,
				f1.physical_name, 
				f1.size / 131072.0 - CAST(FILEPROPERTY(f1.name, ''SpaceUsed'') as float) / 131072 as AvailableSpace_File_GB, 
				f1.size / 131072. as Size_File_GB, 
				cast(f3.available_bytes / 1073741824. as float) as AvailableSpace_Disk_GB,
				cast(f3.total_bytes / 1073741824. as float) as Size_Disk_GB,
				cast(fileproperty(f1.name, ''SpaceUsed'') as float) / 131072 as SpaceUsed_File_GB, 
				cast(fileproperty(f1.name, ''SpaceUsed'') as float) / f1.size as SpaceUsed_File, 
				cast(fileproperty(f1.name, ''SpaceUsed'') as float) / f3.total_bytes * 1024 * 8 as SpaceUsed_Disk,
				case
					when f1.is_percent_growth = 1 then f1.size / 131072. * (f1.growth / 100.)
					else f1.growth / 131072.
				end as next_growth_gb
			from sys.database_files f1
			left join sys.sysfiles f2 on f2.fileid = f1.file_id
			left join sys.sysfilegroups fg1 on fg1.groupid = f2.groupid
			left join sys.filegroups fg2 on fg2.name = fg1.groupname
			cross apply sys.dm_os_volume_stats(' + @db_id + ', f1.file_id) f3;';

		
		set @sql_command_2 = '
		exec (''' + replace(@sql_command_1, '''', '''''') + ''')' + iif(@server_name = '[' + @@servername + ']', '', ' at ' + @server_name);

		insert #database_files
		exec(@sql_command_2);


		fetch next from cursor_db into @db_id, @db_name;
	end;

	close cursor_db;
	deallocate cursor_db;

	fetch next from cursor_server into @server_name;
end;

close cursor_server;
deallocate cursor_server;




-- Детализированные данные
select *
from #database_files t1
order by
	t1.server_name, 
	t1.db_id,
	t1.type_desc;


-- Переполненные файловые группы
select 
	t1.server_name,
	t1.db_id,
	t1.db_name, 
	t1.type_desc,
	t1.FileGroupName, 
	sum(t1.SpaceUsed_File_GB) as SpaceUsed_File_GB,
	sum(t1.AvailableSpace_Disk_GB) as AvailableSpace_Disk_GB,
	sum(t1.AvailableSpace_File_GB) as AvailableSpace_File_GB,
	sum(t1.SpaceUsed_File_GB) / sum(t1.AvailableSpace_Disk_GB + t1.AvailableSpace_File_GB + t1.SpaceUsed_File_GB) as SpaceUsed_Disk
from #database_files t1
group by
	t1.server_name,
	t1.db_id,
	t1.db_name, 
	t1.type_desc,
	t1.FileGroupName
order by
	SpaceUsed_Disk desc
	

-- Переполненные диски
select
	t1.server_name,
	t1.volume_mount_point,
	t1.logical_volume_name,
	t1.Size_Disk_GB,
	sum(t1.SpaceUsed_File_GB) as SpaceUsed_File_GB,
	sum(t1.SpaceUsed_File_GB) / t1.Size_Disk_GB as SpaceUsed_Disk
from #database_files t1
group by 
	t1.server_name,
	t1.volume_mount_point,
	t1.logical_volume_name,
	t1.Size_Disk_GB
order by 
	t1.server_name,
	SpaceUsed_Disk desc;
17 окт 17, 10:23    [20874949]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить