Анализ информации о дисках и файлах в SQL Server

Анализ полных имен физических файлов с целью сбора информации — одна из задач, которую довольно часто приходится выполнять администраторам баз данных, начиная с просмотра путей для создаваемых файлов задания агента SQL Server до анализа метаданных файла базы данных. Недавно мне пришлось обновлять отчет служб SQL Server Reporting Services, который содержит журнал сбоев задания агента SQL Server для группы администраторов базы данных. Я добавлял функцию, которая вводит выходной файл для любого задания, попадающего в отчет. Это позволяет администратору баз данных выполнять диагностику сбоев без необходимости переходить к этому заданию на экземпляре и получать местонахождение файла, а затем открывать файл в приложении «Блокнот». Один щелчок на гиперссылке выходного файла в новом отчете, и файл открывается автоматически.

По крайней мере, так было задумано.

Я выяснил, что при наличии пробелов в имени файла гиперссылка не формируется. Поэтому требовалось идентифицировать имена файлов с пробелами и преобразовать их в подчеркивания (_). Другая проблема состояла в том, что я хотел реализовать подход с единственным щелчком. Для этого нужно преобразовать каждый файл, сохраненный как локальный путь в UNC.

Чтобы выполнить эти задачи, требовалось определить, какие файлы должны быть затронуты, что в свою очередь привело к аналитическому запросу, приведенному в листинге 1.

На экране показаны результаты тестового выполнения с использованием представления системного каталога sys.database_files. В листинге 2 процесс работает с любым столбцом, хранящим полный путь физического файла, но в качестве примера используется msdb.dbo.sysjobsteps.

Экран. Результаты тестового выполнения

--===========================================================
--ИСПОЛЬЗОВАНИЕ ЗНАЧЕНИЙ ФАЙЛОВ ТЕКУЩЕЙ БАЗЫ ДАННЫХ:
--===========================================================
SELECT physical_name
, LEFT(physical_name, LEN(physical_name) - (CHARINDEX('\',REVERSE(physical_name), 1))) AS the_file_path
, RIGHT(physical_name, CHARINDEX('\',REVERSE(physical_name)) - 1)AS the_file_name
FROM sys.[database_files]

--===========================================================
--ПРИМЕР С ИСПОЛЬЗОВАНИЕМ ЗНАЧЕНИЙ msdb.dbo.sysjobsteps.output_file_name:
--===========================================================
SELECT output_file_name
, LEFT(output_file_name, LEN(output_file_name) - (CHARINDEX('\',REVERSE(output_file_name), 1))) AS the_file_path
, RIGHT(output_file_name, CHARINDEX('\',REVERSE(output_file_name)) - 1)AS the_file_name
FROM msdb.dbo.sysjobsteps
WHERE output_file_name IS NOT NULL
ORDER BY output_file_name