Информация о SQL сервере:
|
-- Имена сервера и экземпляра Select @@SERVERNAME as [Server\Instance]; -- версия SQL Server Select @@VERSION as SQLServerVersion; -- экземпляр SQL Server Select @@ServiceName AS ServiceInstance; -- Текущая БД (БД, в контексте которой выполняется запрос) Select DB_NAME() AS CurrentDB_Name; |
Время работы с последнего старта:
|
SELECT @@Servername AS ServerName , create_date AS ServerStarted , DATEDIFF(s, create_date, GETDATE()) / 86400.0 AS DaysRunning , DATEDIFF(s, create_date, GETDATE()) AS SecondsRunnig FROM sys.databases WHERE name = 'tempdb'; |
Общая информация об активных подключениях:
|
SELECT @@Servername AS Server , DB_NAME(database_id) AS DatabaseName , COUNT(database_id) AS Connections , Login_name AS LoginName , MIN(Login_Time) AS Login_Time , MIN(COALESCE(last_request_end_time, last_request_start_time)) AS Last_Batch FROM sys.dm_exec_sessions WHERE database_id > 0 AND DB_NAME(database_id) NOT IN ( 'master', 'msdb' ) GROUP BY database_id , login_name ORDER BY DatabaseName; |
Дата последних бэкапов:
|
SELECT @@Servername AS ServerName , d.Name AS DBName , MAX(b.backup_finish_date) AS LastBackupCompleted FROM sys.databases d LEFT OUTER JOIN msdb..backupset b ON b.database_name = d.name AND b.[type] = 'D' GROUP BY d.Name ORDER BY d.Name; |
Каталоги последних бэкапов:
|
SELECT @@Servername AS ServerName , d.Name AS DBName , b.Backup_finish_date , bmf.Physical_Device_name FROM sys.databases d INNER JOIN msdb..backupset b ON b.database_name = d.name AND b.[type] = 'D' INNER JOIN msdb.dbo.backupmediafamily bmf ON b.media_set_id = bmf.media_set_id ORDER BY d.NAME , b.Backup_finish_date DESC; |
Список всех баз:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
|
/*1*/ EXEC sp_helpdb; /*2*/ EXEC sp_Databases; /*3*/ SELECT @@SERVERNAME AS Server , name AS DBName , recovery_model_Desc AS RecoveryModel , Compatibility_level AS CompatiblityLevel , create_date , state_desc FROM sys.databases ORDER BY Name; /*4*/ SELECT @@SERVERNAME AS Server , d.name AS DBName , create_date , compatibility_level , m.physical_name AS FileName FROM sys.databases d JOIN sys.master_files m ON d.database_id = m.database_id WHERE m.[type] = 0 -- data files only ORDER BY d.name; |
Размеры баз:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
|
with fs as ( select database_id, type, size * 8.0 / 1024 size from sys.master_files ) select name, (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB, (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB from sys.databases db /*После того, как стало известно о размере баз данных, можно посмотреть сколько место фактически используется.*/ SELECT SUM(unallocated_extent_page_count) AS [free pages], (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB] FROM sys.dm_db_file_space_usage; |
Расположение файлов базы данных:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
|
Use [ИмяБазы] /*1*/ EXEC sp_Helpfile; /*2*/ SELECT @@Servername AS Server , DB_NAME() AS DB_Name , File_id , Type_desc , Name , LEFT(Physical_Name, 1) AS Drive , Physical_Name , RIGHT(physical_name, 3) AS Ext , Size , Growth FROM sys.database_files ORDER BY File_id; |
Размеры таблиц:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42
|
Use [ИмяБазы] SELECT a3.name AS [schemaname], a2.name AS [tablename], a1.rows as row_count, (a1.reserved + ISNULL(a4.reserved,0))* 8 AS [reserved], a1.data * 8 AS [data], (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS [index_size], (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS [unused] FROM (SELECT ps.object_id, SUM ( CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END ) AS [rows], SUM (ps.reserved_page_count) AS reserved, SUM ( CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END ) AS data, SUM (ps.used_page_count) AS used FROM sys.dm_db_partition_stats ps GROUP BY ps.object_id) AS a1 LEFT OUTER JOIN (SELECT it.parent_id, SUM(ps.reserved_page_count) AS reserved, SUM(ps.used_page_count) AS used FROM sys.dm_db_partition_stats ps INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id) WHERE it.internal_type IN (202,204) GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id) INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id ) INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id) WHERE a2.type <> N'S' and a2.type <> N'IT' ORDER BY reserved DESC |
…
ДАЛЕЕ