Перейти к содержимому

Mssql

Все по MSsql

Как найти в mssql какой запрос больше всего нагружает файловую систему.

Выросли очереди на диски на сервере базы данных mssql. Задача найти запросы топ по чтению с диска.

Нагрузка на диск на чтение

По графику видно что нагрузка выросла. Диски nvme и поменять на более производительные нет возможности

Запрос по топу чтения

WITH DB_Disk_Reads_Stats

AS

(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_physical_reads) AS [physical_reads]

 FROM sys.dm_exec_query_stats AS qs

 CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] 

              FROM sys.dm_exec_plan_attributes(qs.plan_handle)

              WHERE attribute = N'dbid') AS F_DB

 GROUP BY DatabaseID)

SELECT ROW_NUMBER() OVER(ORDER BY [physical_reads] DESC) AS [row_num],

       DatabaseName, [physical_reads], 

       CAST([physical_reads] * 1.0 / SUM([physical_reads]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [Physical_Reads_Percent]

FROM DB_Disk_Reads_Stats

WHERE
… Читать далее

Как сгенерировать скрипт AUTOGROW включить автоматического увеличения всех файлов

DECLARE @Sql NVARCHAR(MAX);
DECLARE @DbName NVARCHAR(128);
 
-- Удаление временных таблиц, если они уже существуют
IF OBJECT_ID('tempdb..#DatabaseFG') IS NOT NULL DROP TABLE tempdb..#DatabaseFG;
IF OBJECT_ID('tempdb..#SysFiles') IS NOT NULL DROP TABLE tempdb..#SysFiles;
IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL DROP TABLE tempdb..#DatabaseList;
 
-- Создание временных таблиц
CREATE TABLE #DatabaseFG (
    DatabaseName NVARCHAR(128),
    FG_name NVARCHAR(128),
    data_space_id INT,
    type NVARCHAR(128),
    is_default INT,
    is_autogrow_all_files INT
);
 
CREATE TABLE #SysFiles (
    DatabaseName NVARCHAR(128),
    FileName NVARCHAR(128),
    FileSizeMB DECIMAL(18, 2),
    SpaceUsedMB DECIMAL(18, 2),
    FreeSpaceMB DECIMAL(18, 2)
);
 
CREATE TABLE #DatabaseList (
    DatabaseName NVARCHAR(128)
);
 
-- Курсор для обхода баз данных
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name
… Читать далее

Как сгенерировать скрипт для создание файловых групп

DECLARE @Sql NVARCHAR(MAX);
DECLARE @DbName NVARCHAR(128);
 
-- Удаление временных таблиц, если они уже существуют
IF OBJECT_ID('tempdb..#DatabaseFG') IS NOT NULL DROP TABLE tempdb..#DatabaseFG;
IF OBJECT_ID('tempdb..#SysFiles') IS NOT NULL DROP TABLE tempdb..#SysFiles;
IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL DROP TABLE tempdb..#DatabaseList;
 
-- Создание временных таблиц
CREATE TABLE #DatabaseFG (
    DatabaseName NVARCHAR(128),
    FG_name NVARCHAR(128),
    data_space_id INT,
    type NVARCHAR(128),
    is_default INT,
    is_autogrow_all_files INT
);
 
CREATE TABLE #SysFiles (
    DatabaseName NVARCHAR(128),
    FileName NVARCHAR(128),
    FileSizeMB DECIMAL(18, 2),
    SpaceUsedMB DECIMAL(18, 2),
    FreeSpaceMB DECIMAL(18, 2)
);
 
CREATE TABLE #DatabaseList (
    DatabaseName NVARCHAR(128)
);
 
-- Курсор для обхода баз данных
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name
… Читать далее

Как вывести размер всех баз в mssql с файловыми группами и Space Use Free Space Свободное место

DECLARE @Sql NVARCHAR(MAX)
DECLARE @DbName NVARCHAR(128)
 
-- Курсор для обхода баз данных
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb') AND state_desc = 'ONLINE' -- Исключаем системные базы данных
 
-- Удаление временных таблиц, если они уже существуют
IF OBJECT_ID('tempdb..#DatabaseFG') IS NOT NULL
BEGIN
    DROP TABLE tempdb..#DatabaseFG;
END

IF OBJECT_ID('tempdb..#SysFiles') IS NOT NULL
BEGIN
    DROP TABLE tempdb..#SysFiles;
END
 
-- Создание временных таблиц
CREATE TABLE #DatabaseFG (
    DatabaseName NVARCHAR(128),
    FG_name NVARCHAR(128),
    data_space_id INT,
    type NVARCHAR(128),
    is_default INT,
    is_autogrow_all_files INT
);

CREATE TABLE #SysFiles (
    DatabaseName NVARCHAR(128),
    FileName NVARCHAR(128),
    FileSizeMB DECIMAL(18, 2),
    SpaceUsedMB DECIMAL(18, 2),
… Читать далее

Как в mssql узнать какие права на базы данных имеет пользователь.

Задача узнать на какие базы и какие права имеет определённый logon в mssql

-- Создаем временную таблицу для сохранения результатов
IF OBJECT_ID('tempdb..#UserMapping') IS NOT NULL
    DROP TABLE #UserMapping;

CREATE TABLE #UserMapping (
    DatabaseName NVARCHAR(128),
    LoginName NVARCHAR(128),
    UserName NVARCHAR(128),
    UserType NVARCHAR(128),
    DefaultSchema NVARCHAR(128),
    DatabaseRole NVARCHAR(128)
);

-- Динамический SQL для всех баз данных
DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += '
USE [' + name + ']; 
INSERT INTO #UserMapping (DatabaseName, LoginName, UserName, UserType, DefaultSchema, DatabaseRole)
SELECT 
    ''' + name + ''' AS [DatabaseName], 
    sp.name AS [LoginName],
    dp.name AS [UserName],
    dp.type_desc AS [UserType],
    dp.default_schema_name AS [DefaultSchema],
    dp2.name AS [DatabaseRole]
FROM
… Читать далее

Как сделать шаг проверки первичной реплики в задании в always on

Задача в job сделать первым шагом проверку primary сервера

$StartTime = (Get-Date)
#Добавление шага проверки в джобы
$Sql_primary = 'db01'   # Первичная реплика
 
$JobNames =  Get-DbaAgentJob -SqlInstance $Sql_primary -ExcludeJob 'syspolicy_purge_history', 'AlwaysOn_Latency_Data_Collection', 'dba_Monitoring_Long_Query_Execution', 'sp_WhoIsActive'
foreach ($JobName in $JobNames) {
 
$jobStep = @{
    SqlInstance = $Sql_primary
    Job = $JobName.Name
    StepId = 1
    StepName = "CheckAO"
    Subsystem = "TransactSql"
    Command = "--шаг в джобах для проверки аг
    IF (master.sys.fn_hadr_is_primary_replica ('beer') = 0)
    BEGIN
        EXEC msdb.dbo.sp_stop_job @job_id =  `$(ESCAPE_SQUOTE(JOBID))
    END"
    OnSuccessAction = "GoToNextStep"
    OnFailAction = "QuitWithFailure"
    Insert = $true
    #Force = $true
    }
 New-DbaAgentJobStep @jobStep
}
 
$EndTime = (Get-Date)
$TotalTime =$EndTime-$StartTime
$TotalTime.ToString()

 

Как бэкапить логины (login) и задания (JOB) mssql через dbatools

Задача настроить бэкап в папку включенных и отключённых job и login с mssql

Set-DbatoolsInsecureConnection -SessionOnly
$StartTime = (Get-Date)
$AGLSN = 'db01' #'msk-dblistener' # имя листенера группы доступности
$ExcludeJobs = ''
$ExcludeLogins = ''
$BackupPath = '\\ARC03\sql_backup$\' # В конце обязательно должен стоять "\"
$CurrDate = (Get-Date -Format 'yyyy.MM.dd.HH.mm')

$PrimaryReplica = Get-DbaAgReplica -SqlInstance $AGLSN | Where-Object Role -eq Primary

# Папки для хранения бэкапов
$BackupDisabledJobPath = $BackupPath + $primaryReplica.Name + '\BackupDisabledJob'  # Папка для выключенных джобов
$BackupEnabledJobPath = $BackupPath + $primaryReplica.Name + '\BackupEnabledJob'  # Папка для включённых джобов
$BackupDisabledLoginPath = $BackupPath + $primaryReplica.Name + '\BackupDisabledLogin'  # Папка для выключенных логинов
$BackupEnabledLoginPath
… Читать далее

Как синхронизировать обьекты в always on c первичной реплики на вторичную

$StartTime = (Get-Date)
#Копируем все объекты силой (-force), хорошо выполнять раз в сутки
$AGLSN = 'shr-db01'
  
$primaryReplica =    Get-DbaAgReplica -SqlInstance $AGLSN | Where-Object Role -eq Primary
$secondaryReplicas = Get-DbaAgReplica -SqlInstance $AGLSN | Where-Object Role -eq Secondary
 
      
$secondaryReplicas | ForEach-Object {
         
 
    Copy-DbaAgentJobCategory -Source $primaryReplica.Name -Destination $_.Name -Force   | Select-Object DateTime, Type, DestinationServer, name, status, Notes | Format-Table -Property * -AutoSize | Out-String -Width 1024 | Out-File C:\DBA_scripts_AG\DBA_synchronize_AG_log.txt -Append
    Copy-DbaAgentOperator -Source $primaryReplica.Name -Destination $_.Name -Force      | Select-Object DateTime, Type, DestinationServer, name, status, Notes | Format-Table -Property * -AutoSize | Out-String -Width 1024 | Out-File C:\DBA_scripts_AG\DBA_synchronize_AG_log.txt -Append
    Copy-DbaAgentAlert -Source $primaryReplica.Name -Destination $_.Name -Force
… Читать далее

Как в always on mssql синхронизировать JOB на вторичной реплики и удалять JOB со вторичной когда удалили на первичной.

задача с primary always on копировать JOB на secondary  реплику.

Set-DbatoolsInsecureConnection -SessionOnly
$StartTime = (Get-Date)

# Скрипт проверяет джобы и копирует их на вторичные реплики, если их там нет по имени.
$AGLSN = 'shr-db01'
 
$primaryReplica = Get-DbaAgReplica -SqlInstance $AGLSN | Where-Object Role -eq Primary
$secondaryReplicas = Get-DbaAgReplica -SqlInstance $AGLSN | Where-Object Role -eq Secondary

# Получение списка джобов на первичной реплике, исключая указанные системные задания
$JobsOnPrimary = Get-DbaAgentJob -SqlInstance $primaryReplica.Name -ExcludeJob 'syspolicy_purge_history', 'AlwaysOn_Latency_Data_Collection', 'dba_Monitoring_Long_Query_Execution', 'sp_WhoIsActive'

# Обработка каждой вторичной реплики
$secondaryReplicas | ForEach-Object {
    
    $JobsOnSecondary = Get-DbaAgentJob -SqlInstance $_.Name -ExcludeJob 'syspolicy_purge_history', 'AlwaysOn_Latency_Data_Collection', 'dba_Monitoring_Long_Query_Execution', 'sp_WhoIsActive'

    
    # Найдем джобы, которые есть на
… Читать далее

Как в always on mssql синхронизировать логины и пароль на вторичной реплики и удалять логины когда удалили на первичной.

Задача синхронизировать копировать логины с первичной реплики на вторичную и так же удалять логины на вторичной реплики если удалили логин на первичной.

Set-DbatoolsInsecureConnection -SessionOnly
$StartTime = (Get-Date)

$AGLSN = 'db01'
 
$primaryReplica = Get-DbaAgReplica -SqlInstance $AGLSN | Where-Object Role -eq Primary
$secondaryReplicas = Get-DbaAgReplica -SqlInstance $AGLSN | Where-Object Role -eq Secondary
$secondaryReplicas.name
# Получение логинов на первичной реплике
$LoginsOnPrimary = Get-DbaLogin -SqlInstance $primaryReplica.Name  -ExcludeSystemLogin

# Обработка каждой вторичной реплики
$secondaryReplicas | ForEach-Object {
    
    $LoginsOnSecondary = Get-DbaLogin  -SqlInstance $_.Name -ExcludeSystemLogin
    
    # Найдем логины, которые отсутствуют на вторичной реплике, но есть на первичной
    $diffToAdd = $LoginsOnPrimary | Where-Object Name -notin ($LoginsOnSecondary.Name)
    
    # Найдем
… Читать далее
Яндекс.Метрика