MSSQL запросы

Ссылки
http://habrahabr.ru/post/162497/

1. Отключение всех коннектов от БД
USE master;
GO
ALTER DATABASE имя_базы
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE имя_базы
SET MULTI_USER;
GO

2. Перевод БД в offline
ALTER DATABASE имя_базы SET OFFLINE;
GO

3. Урезание файла лога транзакций
USE имя_базы;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE имя_базы
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (logical_name_db, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE имя_базы
SET RECOVERY FULL;
GO

4. Посмотреть размеры файлов транзакций и % их использования
DBCC SQLPERF(LOGSPACE);
GO

5. Перенос файлов БД в другое место:
http://technet.microsoft.com/en-us/library/ms345483.aspx

6. Скорость дисков
SELECT
    --virtual file latency
    [ReadLatency] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
    [WriteLatency] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
    [Latency] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
    --avg bytes per IOP
    [AvgBPerRead] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
    [AvgBPerWrite] =
        CASE WHEN [io_stall_write_ms] = 0
            THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
    [AvgBPerTransfer] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE
                (([num_of_bytes_read] + [num_of_bytes_written]) /
                ([num_of_reads] + [num_of_writes])) END,
    LEFT ([mf].[physical_name], 2) AS [Drive],
    DB_NAME ([vfs].[database_id]) AS [DB],
    --[vfs].*,
    [mf].[physical_name]
FROM
    sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
    ON [vfs].[database_id] = [mf].[database_id]
    AND [vfs].[file_id] = [mf].[file_id]
-- WHERE [vfs].[file_id] = 2 -- log files
-- ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
ORDER BY [WriteLatency] DESC;
GO

7. Где размещаются файлы БД
use имя_базы
   go
   sp_helpfile
   go

8. Восстановление БД с указанием нового места размещения файлов БД.

RESTORE DATABASE имя_базы
   FROM DISK = 'E:\файл_бекапа.bak'
   WITH REPLACE,
   MOVE 'логическое_имя_файла_данных' TO 'F:\каталог\файл.mdf',
   MOVE 'логическое_имя_файла_транзакций' TO 'F:\каталог\файл_log.ldf';
GO

8.2 Восстановление БД на тот же сервер, если уже база существует. Тут при восстановлении создается БД имя_новой_бд

USE master;
GO
RESTORE FILELISTONLY
   FROM DISK = 'E:\файл_бекапа.bak'
RESTORE DATABASE имя_новой_бд
  FROM DISK = 'E:\файл_бекапа.bak'
   WITH RECOVERY,
   MOVE 'логическое_имя_файла_данных' TO 'F:\каталог\файл.mdf',
   MOVE 'логическое_имя_файла_транзакций' TO 'F:\каталог\файл_log.ldf';
GO

8.3 Восстановление БД с нескольких файлов (полного и дифференциальных бекапов и лога транзакций)
RESTORE DATABASE имя_базы FROM DISK = 'E:\файл_бекапа.bak' WITH NORECOVERY,
STATS = 10
GO
RESTORE DATABASE имя_базы FROM DISK = 'E:\файл_бекапа2.bak' WITH NORECOVERY,
STATS = 10
GO
RESTORE LOG имя_базы FROM DISK = 'E:\файл_лога_транз.trn' WITH RECOVERY,
STATS = 10
GO

где STATS = 10, для показа прогресса восстановления


9. User, group, or role 'name_user' already exists in the current database. (Microsoft SQL Server, Error: 15023)

Если пользователь уже есть, то
USE DB_NAME
GO
EXEC sp_change_users_login "update_one", "USER_NAME", "LOGIN_NAME"
GO

более подробно здесь
http://www.sqlserver-expert.com/2013/02/user-group-or-role-already-exists-in.html


10. Бекап БД SQL Express
http://support.microsoft.com/kb/2019698/ru
http://support.microsoft.com/kb/2019698/en-us

11. Бекап из командной строки
http://www.alta.ru/instructions/backup_mssql.php
http://www.sover.pro/blogs/8-Retrieving-data-from-Microsoft-SQL-Server-with-PowerShell.html

12. Если БД в статусе "Восстановление"

restore database MyDB with recovery

13. Восстановление БД из бекапа с полным бекапом и разностным:

restore database DBNAME from disk = 'c:\DBNAME.bak' with NORECOVERY
restore database DBNAME from disk = 'c:\DBNAME.df1' with NORECOVERY
restore database DBNAME with RECOVERY 

14. Размер таблиц в БД

select  t.name as TableName, Min(t.create_date) as CreateDate, ds.name as FileGroupName, SUM(u.total_pages) * 8 / 1024 as SizeMB 

from sys.tables as t

inner join sys.partitions as p on t.object_id = p.object_id

inner join sys.allocation_units as u on p.partition_id = u.container_id

inner join sys.data_spaces as ds on u.data_space_id = ds.data_space_id

group by t.name, ds.name

order by SizeMB desc

14. Посмотреть настройки зеркалирования

SELECT
name,
role_desc AS Role,
state_desc AS State,
connection_auth_desc AS ConnAuth,
encryption_algorithm_desc AS Algorithm
FROM sys.database_mirroring_endpoints;

15. Сборник ссылок по свойствам БД

https://habrahabr.ru/post/241079/

16. Вывести список БД и расположение файлов

select 
    d.name as 'database',
    mdf.physical_name as 'mdf_file',
    ldf.physical_name as 'log_file'
from sys.databases d
inner join sys.master_files mdf on 
    d.database_id = mdf.database_id and mdf.[type] = 0
inner join sys.master_files ldf on 
    d.database_id = ldf.database_id and ldf.[type] = 1

17. SPID

http://sqlcom.ru/dba-tools/sql-server-last-query-per-spid/

DBCC INPUTBUFFER(61)
GO

18. Информация о пользователях и подключениях

http://sqlcom.ru/helpful-and-interesting/connections-and-users/

select dc.session_id as [SPID],
dc.client_net_address as [IP клиента],
sp.hostname as [Имя PC клиента],
dc.local_net_address as [IP подключения к серверу],
dc.net_transport as [Протокол подключения],
dc.local_tcp_port as [Порт],
sp.[program_name] as [Имя программы],
sp.loginame
FROM sys.dm_exec_connections dc
inner join master.sys.sysprocesses sp
on dc.session_id=sp.spid
where sp.spid > 50

19. Посмотреть сообщения по eventid

select * from sys.messages where message_id =11102;

20. Запросы в tempdb

https://www.sql.ru/forum/917399/mssql-aktivno-ispolzuet-tempdb

SELECT
  tsu.internal_page_count/128 AS internal_size_current_MB,
  tsu.user_page_count/128 AS user_size_current_MB,  
  tsu.internal_alloc_page_count/128 AS internal_alloc_MB,
  tsu.user_alloc_page_count/128 AS user_alloc_MB,    
  r.session_id,
  r.request_id,
  r.start_time,
  s.[host_name],
  s.program_name,
  s.login_name,
 DB_NAME(r.database_id) as db_name,
  CAST('<?text --'+CHAR(13)+CHAR(10)+substring(qt.text, (r.statement_start_offset/2)+1 , case r.statement_end_offset when -1 then 2147483647 else ((r.statement_end_offset - r.statement_start_offset)/2) + 1 end ) + '--?>' as XML) as sql_text,
  CAST(qp.query_plan AS XML) AS query_plan,
  r.granted_query_memory,
  r.cpu_time,
  r.reads,
  r.writes,
  r.logical_reads,
  r.statement_start_offset,
  r.statement_end_offset  
FROM sys.dm_exec_requests as r
  INNER JOIN
  (  
    SELECT
      tsu.session_id, 
      tsu.request_id,
      SUM(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) internal_page_count,
      SUM(tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) user_page_count,
      SUM(tsu.internal_objects_alloc_page_count) internal_alloc_page_count,
      SUM(tsu.user_objects_alloc_page_count) user_alloc_page_count  
    FROM sys.dm_db_task_space_usage tsu
    WHERE tsu.internal_objects_alloc_page_count > 0 or tsu.user_objects_alloc_page_count > 0
    GROUP BY tsu.session_id, tsu.request_id
  ) tsu ON r.session_id = tsu.session_id and r.request_id = tsu.request_id
  outer apply sys.dm_exec_sql_text(r.sql_handle) qt
  outer apply sys.dm_exec_text_query_plan(r.plan_handle, r.statement_start_offset, r.statement_end_offset) qp
  LEFT JOIN sys.dm_exec_sessions as s ON s.session_id = r.session_id
WHERE r.status IN ('running', 'runnable', 'suspended')
  and r.session_id <> @@SPID

21. Переместить данные из файла в другие файлы filegroup
DBCC SHRINKFILE (e2, EMPTYFILE); GO

Затем файл можно удалить
ALTER DATABASE name_db REMOVE FILE e2;

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-ver15


https://support.citrix.com/article/CTX226229

22. Shrink log transaction AlwaysOn

ou can try:

1. Database on all servers in Availability Group should be in Synchronized state.
2. Move used pages to start of the transaction log, before you shrink it.
3. Sometimes available free space of log is 99%, but SQL Server can't release unused space. Try to reboot each server in Availability Group in turn.
4. Sometimes you need to bakup and shrink transaction log 2 times before MS SQL Server released free space (Cannot shrink log file (DB_Log) because the logical log file located at the end of the file is in use.).

--Set current database inside job step or script
    --Check for Execute on Primary Only
    if (SELECT role
        FROM sys.dm_hadr_availability_replica_states AS a
        JOIN sys.availability_replicas AS b
            ON b.replica_id = a.replica_id
    WHERE b.replica_server_name = @@SERVERNAME) = 1
    BEGIN
        -- Use [test_db] -- Not working for MS SQL 2014, just comment this line and set current database inside job step or script
        -- 1) Bakup Trn
        BACKUP LOG [test_db] TO  DISK = N'D:\MSSQL\Backup\test_db.trn' WITH NOFORMAT, INIT,  NAME = N' Trn Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
        -- 2) Move used pages
        DBCC SHRINKFILE (N'test_db_log' , 3000, NOTRUNCATE)
        -- 3) SHRINKFILE Log
        DBCC SHRINKFILE (N'test_db_log' , 3000)
    END

23. Shrink msdb

https://habr.com/ru/post/273633/

Вот этим запросом можно вернуть список объектов и их размер:

USE msdb
GO

SELECT TOP(10)
      o.[object_id]
    , obj = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
    , o.[type]
    , i.total_rows
    , i.total_size
FROM sys.objects o
JOIN (
    SELECT
          i.[object_id]
        , total_size = CAST(SUM(a.total_pages) * 8. / 1024 AS DECIMAL(18,2))
        , total_rows = SUM(CASE WHEN i.index_id IN (0, 1) AND a.[type] = 1 THEN p.[rows] END)
    FROM sys.indexes i
    JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
    JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
    WHERE i.is_disabled = 0
        AND i.is_hypothetical = 0
    GROUP BY i.[object_id]
) i ON o.[object_id] = i.[object_id]
WHERE o.[type] IN ('V', 'U', 'S')
ORDER BY i.total_size DESC

https://www.sqlrx.com/large-msdb-database-from-sysmaintplan_logdetail-table/

Очистить таблицу sysmaintplan_log

USE msdb

GO

ALTER TABLE [dbo].[sysmaintplan_log] DROP CONSTRAINT [FK_sysmaintplan_log_subplan_id];

GO

ALTER TABLE [dbo].[sysmaintplan_logdetail] DROP CONSTRAINT [FK_sysmaintplan_log_detail_task_id];

GO

TRUNCATE TABLE msdb.dbo.sysmaintplan_logdetail;

GO

TRUNCATE TABLE msdb.dbo.sysmaintplan_log;

GO

ALTER TABLE [dbo].[sysmaintplan_log] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_subplan_id] FOREIGN KEY([subplan_id])

REFERENCES [dbo].[sysmaintplan_subplans] ([subplan_id]);

GO

ALTER TABLE [dbo].[sysmaintplan_logdetail] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_detail_task_id] FOREIGN KEY([task_detail_id])

REFERENCES [dbo].[sysmaintplan_log] ([task_detail_id]) ON DELETE CASCADE;

GO

23. Сессии активные

select
    P.spid
,   right(convert(varchar, 
            dateadd(ms, datediff(ms, P.last_batch, getdate()), '1900-01-01'), 
            121), 12) as 'batch_duration'
,   P.program_name
,   P.hostname
,   P.loginame
from master.dbo.sysprocesses P
where P.spid > 50
and      P.status not in ('background', 'sleeping')
and      P.cmd not in ('AWAITING COMMAND'
                    ,'MIRROR HANDLER'
                    ,'LAZY WRITER'
                    ,'CHECKPOINT SLEEP'
                    ,'RA MANAGER')
order by batch_duration desc

 https://stackoverflow.com/questions/941763/list-the-queries-running-on-sql-server/941851

23. Объем запросов

SELECT session_id, num_writes, st.text AS statement_text
FROM sys.dm_exec_connections AS ec
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS st
ORDER BY num_writes DESC

 https://stackoverflow.com/questions/57498828/dm-exec-connections-does-not-show-some-connections

https://dba.stackexchange.com/questions/756/find-out-what-queries-are-causing-the-biggest-amount-of-network-traffic

24. Обрезка лога в AlwausOn

// This Shrinks your Log Files to a 100 MB File //
DECLARE @dbname sysname
DECLARE @sqlstmt varchar(max)
SET @dbname = 'db1'
SET @sqlstmt = 'use [' + @dbname + '];DBCC SHRINKFILE(2, 100);'
IF sys.fn_hadr_is_primary_replica ( @dbname ) = 1 
BEGIN 
PRINT 'Shrinking file'
EXEC (@sqlstmt)
END

25. Выводит информацию по каждой БД: recovery model, размеры файлов и сколько используется, когда бекап был.

https://stackoverflow.com/questions/18014392/select-sql-server-database-size

IF OBJECT_ID('tempdb.dbo.#space') IS NOT NULL
    DROP TABLE #space

CREATE TABLE #space (
      database_id INT PRIMARY KEY
    , data_used_size DECIMAL(18,2)
    , log_used_size DECIMAL(18,2)
)

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF((
    SELECT '
    USE [' + d.name + ']
    INSERT INTO #space (database_id, data_used_size, log_used_size)
    SELECT
          DB_ID()
        , SUM(CASE WHEN [type] = 0 THEN space_used END)
        , SUM(CASE WHEN [type] = 1 THEN space_used END)
    FROM (
        SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024)
        FROM sys.database_files s
        GROUP BY s.[type]
    ) t;'
    FROM sys.databases d
    WHERE d.[state] = 0
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

EXEC sys.sp_executesql @SQL

SELECT
      d.database_id
    , d.name
    , d.state_desc
    , d.recovery_model_desc
    , t.total_size
    , t.data_size
    , s.data_used_size
    , t.log_size
    , s.log_used_size
    , bu.full_last_date
    , bu.full_size
    , bu.log_last_date
    , bu.log_size
FROM (
    SELECT
          database_id
        , log_size = CAST(SUM(CASE WHEN [type] = 1 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
        , data_size = CAST(SUM(CASE WHEN [type] = 0 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
        , total_size = CAST(SUM(size) * 8. / 1024 AS DECIMAL(18,2))
    FROM sys.master_files
    GROUP BY database_id
) t
JOIN sys.databases d ON d.database_id = t.database_id
LEFT JOIN #space s ON d.database_id = s.database_id
LEFT JOIN (
    SELECT
          database_name
        , full_last_date = MAX(CASE WHEN [type] = 'D' THEN backup_finish_date END)
        , full_size = MAX(CASE WHEN [type] = 'D' THEN backup_size END)
        , log_last_date = MAX(CASE WHEN [type] = 'L' THEN backup_finish_date END)
        , log_size = MAX(CASE WHEN [type] = 'L' THEN backup_size END)
    FROM (
        SELECT
              s.database_name
            , s.[type]
            , s.backup_finish_date
            , backup_size =
                        CAST(CASE WHEN s.backup_size = s.compressed_backup_size
                                    THEN s.backup_size
                                    ELSE s.compressed_backup_size
                        END / 1048576.0 AS DECIMAL(18,2))
            , RowNum = ROW_NUMBER() OVER (PARTITION BY s.database_name, s.[type] ORDER BY s.backup_finish_date DESC)
        FROM msdb.dbo.backupset s
        WHERE s.[type] IN ('D', 'L')
    ) f
    WHERE f.RowNum = 1
    GROUP BY f.database_name
) bu ON d.name = bu.database_name
ORDER BY t.total_size DESC

26. "Умная" дефрагментация

https://github.com/microsoft/tigertoolbox/tree/master/AdaptiveIndexDefrag