sql — MitCom.su

-- Имена сервера и экземпляра 
Select @@SERVERNAME as [Server\Instance]; 

-- версия SQL Server 
Select @@VERSION as SQLServerVersion; 

-- экземпляр SQL Server 
Select @@ServiceName AS ServiceInstance;

 -- Текущая БД (БД, в контексте которой выполняется запрос)
Select DB_NAME() AS CurrentDB_Name;

1

2

3

4

5

6

7

8

9

10

11

-- Имена сервера и экземпляра

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';

1

2

3

4

5

6

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';

-- Похожая информация, может быть получена с помощью sp_who 

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;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

-- Похожая информация, может быть получена с помощью sp_who

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

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;

Теперь мы знаем есть ли настроенное резервное копирование. Ну или хотя бы ручной запуск бэкапирования.

Для начала узнаем список всех баз на сервере. Для этого есть несколько способов.

EXEC sp_helpdb; 
или

EXEC sp_Databases; 
или

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; 
или

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

18

19

20

21

22

23

24

25

26

27

28

Для начала узнаем список всех баз на сервере. Для этого есть несколько способов.

EXEC sp_helpdb;

или

EXEC sp_Databases;

или

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;

или

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;

Все способы дают примерно ту же самую информацию, но с разной детализацией.

Размер базы в мегабайтах.

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

Размер базы в мегабайтах.

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;

Если доля неиспользованного места в базе высокая, то это может стать поводом для проведения шринка файлов данных базы. Но совсем не обязательно!

Расположение файлов также можно определить несколькими способами. Вот два из них.

EXEC sp_Helpfile; 

или

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

Расположение файлов также можно определить несколькими способами. Вот два из них.

EXEC sp_Helpfile;

или

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;

Может пригодиться, если планируется изменять конфигурацию хранилища данных и по другим админским вопросам.

Рубрика: sql

Набор скриптов для MS SQL Server