MSSQL

• Wypisanie komend pod shrink wszystkich plików
• Sprawdzenie aktualnych zadań / zapytań na bazie danych
• Sprawdzenie ile % bazy danych się odtworzyło
• Przełączenie bazy danych z [RESTORING...] do open
• Sprawdzenie ostatnich backupów
• Purge informacji o backupach z bazy danych
• Sortowanie tabel po rozmiarze
• Zajętość poszczególnych tabel w DB
• Przełączanie bazy w tryb read-only
• Sprawdzenie wszystkich plików, rozmiarów oraz trybu recovery
• Shrink rozmiaru logów transakcyjnych
• Naprawa uszkodzonej przystawki Configuration Manager
• Weryfikacja zapchanych logów przy replikacji
• Sprawdzenie co blokuje przełączanie trybu bazy po stronie silnika
• Zmiana bazy na single user mode / Multi user mode
• Rebuild pliku LDF (plik logów bazy danych)
• Weryfikacja TotalIndexSize / Sprawdzenia potrzebnego miejsca na dysku dla reindexacji
• Weryfikacja statusu indeksacji bazy danych
• Sprawdzenie wersji edycji silnika MSSQL (Express / Standard / Enterprise)
• Downgrade / zmiana edycji silnika MSSQL
• Sprawdzenie wielkości danych w msdb
• Sprawdzenie zajętości plików bazy danych
• Sprawdzenie aktualnie działających jobów SQL Agent
• Naprawa uprawnień usera SQL po odtworzeniu baz (zmiana ID)
• Wyciąganie pliku JPK w XML z baz Optimy
• Wciągnięcie modyfikacji danych w księgach rozliczeniowych - OPTIMA

Wypisanie komend pod shrink wszystkich plików

W T-SQL (SSMS / SQL Server management studio) wykonujemy polecenie:

SELECT 
      'USE [' + d.name + N']' + CHAR(13) + CHAR(10) 
    + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)' 
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) 
FROM 
         sys.master_files mf 
    JOIN sys.databases d 
        ON mf.database_id = d.database_id 

Jeżeli chcemy wykonać shrink bez baz systemowych dopisujemy na końcu:

WHERE d.database_id > 4;

Wynik:

Kopiujemy wszystko z outputu i wklejamy do nowego query:

I klikamy wykonaj. Po czym pokaże się w wynikach proces shrinku:

 

Sprawdzenie aktualnych zadań / zapytań na bazie danych

Wykonujemy w SSMS:

SELECT session_id, command, text FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)

 

Sprawdzenie ile % bazy danych się odtworzyło

Wykonujemy w SSMS, mając wybraną bazę master:

SELECT 
    [session_id], 
    [command], 
    [percent_complete], 
    DATEADD(MILLISECOND, [estimated_completion_time], GETDATE()) AS [estimated_completion_datetime] 
FROM sys.dm_exec_requests 
WHERE [command] = 'RESTORE DATABASE' 
    AND [start_time] IS NOT NULL; 

Przełączenie bazy danych z [RESTORING...] do open

Wykonujemy w SSMS z wybraną bazą master:

RESTORE DATABASE NazwaBazyDanych 
    WITH RECOVERY; 

Sprawdzenie ostatnich backupów

Uruchamiamy w SSMS z wybraną bazą master

SELECT database_name, backup_finish_date AS last_log_backup 
FROM msdb.dbo.backupset 
WHERE type = 'L' -- L oznacza backup logów 
AND database_name = 'NazwaBazy' -- podaj nazwę bazy danych, której backup chcesz sprawdzić 
ORDER BY backup_finish_date DESC; 

Poprzez wybranie Where type = X wybieramy typ backupu jak poniżej:

           WHEN type = 'D' THEN 'Full Backup'

           WHEN type = 'I' THEN 'Differential Backup'

           WHEN type = 'L' THEN 'Log Backup'

           WHEN type = 'F' THEN 'File Backup'

           WHEN type = 'G' THEN 'Differential File Backup'

           WHEN type = 'P' THEN 'Partial Backup'

           WHEN type = 'Q' THEN 'Differential Partial Backup'

 

 

 

 

Purge informacji o backupach z bazy danych

Uruchamiamy z poziomu SSMS z wybraną bazą master / msdb

use msdb 
go
exec sp_delete_database_backuphistory 'NazwaBazy'  
go 

Sortowanie tabel po rozmiarze

Uruchamiamy w SSMS z wybraną odpowiednią bazą danych:

SELECT
    t.name AS TableName,
    SUM(ps.reserved_page_count) * 8 AS ReservedSpaceKB,
    SUM(ps.used_page_count) * 8 AS UsedSpaceKB
FROM
    sys.tables t
JOIN
    sys.dm_db_partition_stats ps ON t.object_id = ps.object_id
GROUP BY
    t.name
ORDER BY
    SUM(ps.used_page_count) DESC; 

Zajętość poszczególnych tabel w DB

Uruchamiamy w SSMS z wybraną odpowiednią bazą danych:

select top 30 schema_name(tab.schema_id) + '.' + tab.name as [table],  
    cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_mb, 
    cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as allocated_mb 
from sys.tables tab 
join sys.indexes ind  
     on tab.object_id = ind.object_id 
join sys.partitions part  
     on ind.object_id = part.object_id and ind.index_id = part.index_id 
join sys.allocation_units spc 
     on part.partition_id = spc.container_id 
group by schema_name(tab.schema_id) + '.' + tab.name 
order by sum(spc.used_pages) desc; 

 

Przełączanie bazy w tryb read-only

Uruchamiamy w SSMS z wybraną baza master:

USE [master] 
GO 
ALTER DATABASE NazwaBazy SET READ_ONLY WITH NO_WAIT 
GO 

 

Sprawdzenie wszystkich plików, rozmiarów oraz trybu recovery

Uruchamiamy w SSMS z wybraną bazą master:

SELECT  
    d.name AS 'Nazwa bazy danych', 
    d.recovery_model_desc AS 'Tryb odzyskiwania', 
    mf.type_desc AS 'Typ pliku', 
    mf.name AS 'Nazwa pliku', 
    CONVERT(decimal(12,2),mf.size*8/1024.0) AS 'Wielkość pliku (MB)' 
FROM  
    sys.databases d 
JOIN  
    sys.master_files mf ON d.database_id = mf.database_id; 

Shrink rozmiaru logów transakcyjnych

Uruchamiamy w SSMS z wybraną bazą master

Przed samą operacją zaleca się wykonanie backupu full DB. 
Należy również sprawdzić recovery model. 

USE NazwaBazyDanych; 
GO 

-- Zmieniamy tryb recovery na SIMPLE by można było wykonać shrink zapchanego logu
ALTER DATABASE NazwaBazyDanych 
SET RECOVERY SIMPLE; 
GO 

-- Wykonujemy shrink pliku log do 1mb
DBCC SHRINKFILE (NazwaBazyDanych_Log, 1); (1MB) 
GO 

-- Zmieniamy tryb recovery spowrotem na FULL 
ALTER DATABASE NazwaBazyDanych 
SET RECOVERY FULL; 
GO 

Naprawa uszkodzonej przystawki Configuration Manager

W momencie wystąpienia błędu po uruchomieniu: 

Uruchamiamy cmd jako administrator i przechodzimy do shared komponentów MSSQL: 

 cd "C:\Program Files (x86)\Microsoft SQL Server\140\Shared" 

 W tym miejscu uruchomiamy rejestrację komponentów: 

mofcomp sqlmgmproviderxpsp2up.mof 

Po wykonaniu powinniśmy dostać taki komunikat:

Weryfikacja zapchanych logów przy replikacji

Na serwerze MSSQL, "zacięły" się logi. Baza danych nie mogła zwolnić opublikowanych logów z pliku LDF, mimo wykonywanego backupu fulldb oraz backupu logów.  

Nie pomogło rozpięcie replikacji przez "usunięcie" backupów, oraz usuniecie wpisu replikacji z ustawień DB. 

Weryfikujemy czy jakieś zadanie. zatrzymuje coś w plikach LDF (tutaj problemem była nieistniejąca replikacja): 

 SELECT name, log_reuse_wait_desc FROM sys.DATABASES 

Reset informacji o logach - replikacja 

Wykonujemy Query z poziomu bazy danych z problemem: 

 EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time= 0, @reset = 1 

Query usuwające replikacje: 

USE NazwaBazy; 
GO 

EXEC sp_removedbreplication 'NazwaBazy' 
GO 

CHECKPOINT 
GO 

Oraz usunięcie publikacji bazy danych poprzez: 

use master 
exec sp_replicationdboption @dbname = N'NazwaBazy', @optname = N'publish', @value = N'false' 
GO 

 To powinno sprawić że wykonanie query: 

SELECT name, log_reuse_wait_desc FROM sys.DATABASES 

Zmieni status na poprawny 

 

Sprawdzenie co blokuje przełączanie trybu bazy po stronie silnika

Wykonujemy z poziomu SSMS z wybraną bazą master:

select 
    l.resource_type, 
    l.request_mode, 
    l.request_status, 
    l.request_session_id, 
    r.command, 
    r.status, 
    r.blocking_session_id, 
    r.wait_type, 
    r.wait_time, 
    r.wait_resource, 
    request_sql_text = st.text, 
    s.program_name, 
    most_recent_sql_text = stc.text 
from sys.dm_tran_locks l 
left join sys.dm_exec_requests r 
on l.request_session_id = r.session_id 
left join sys.dm_exec_sessions s 
on l.request_session_id = s.session_id 
left join sys.dm_exec_connections c 
on s.session_id = c.session_id 
outer apply sys.dm_exec_sql_text(r.sql_handle) st 
outer apply sys.dm_exec_sql_text(c.most_recent_sql_handle) stc 
where l.resource_database_id = db_id('NazwaBazyDanych') 
order by request_session_id; 

Wynik zapytania:

kill 62 
go

Zmiana bazy na single user mode / Multi user mode

 

Zapytanie uruchamiamy w SSMS z wybraną bazą master:

ALTER DATABASE NazwaBAZY 
SET SINGLE_USER; 
GO 

 

ALTER DATABASE NazwaBAZY 
SET MULTI_USER; 
GO 

Rebuild pliku LDF (plik logów bazy danych)

Przełączenie bazy w tryb Offline:

Jeśli zadanie trwa dłużej niż kilka sekund/minut należy ubić sesje ręcznie 

Sprawdzenie co blokuje... | Konio-DC Bookstack

Przebudowa logów:

Dobrą praktyką jest zapisane najpierw do nowego pliku, a następnie zmiana nazwy oryginalnego pliku na taką z dopiskiem "_OLD". Potem należy z powrotem przełączyć bazę w tryb online i znowu offline, a następnie ponownie wykonać przebudowę logów już z taką nazwą jaka była oryginalnie. 

alter database [NazwaBazy] rebuild log on(Name=[NazwaLogiczna_Pliku_LOG], Filename='Ścieżka_do_nowego pliku.ldf') 

 Przykładowy wynik:

Przywrócenie bazy w tryb Online: 

Jeśli obok nazwy bazy jest "(Restricted User)" to należy przełączyć bazę ma Multi User
Zmiana bazy na single ... | Konio-DC Bookstack

Przełączenie bazy z mode SIMPLE na FULL

ALTER DATABASE [NazwaBazy] SET RECOVERY FULL;

W przeciwny razie nie będzie wykonywać się backup logów

Weryfikacja:

SELECT name, log_reuse_wait_desc FROM sys.DATABASES 

Weryfikacja TotalIndexSize / Sprawdzenia potrzebnego miejsca na dysku dla reindexacji

Wykonujemy z poziomu SSMS z wybraną odpowiednią bazą

SELECT 
    SUM(p.used_page_count) * 8 / 1024.0 / 1024.0  AS TotalIndexSizeGB 
FROM 
    sys.dm_db_partition_stats p 
JOIN 
    sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id 
WHERE 
    i.type > 0;  -- Exclude heap indexes (index_id = 0) 
GO 

Weryfikacja statusu indeksacji bazy danych

Wykonujemy w SSMS z wybraną bazą master.

SELECT TOP (10000000) [ID] 
      ,[DatabaseName] 
      ,[SchemaName] 
      ,[ObjectName] 
      ,[ObjectType] 
      ,[IndexName] 
      ,[IndexType] 
      ,[StatisticsName] 
      ,[PartitionNumber] 
      ,[ExtendedInfo] 
      ,[Command] 
      ,[CommandType] 
      ,[StartTime] 
      ,[EndTime] 
      ,[ErrorNumber] 
      ,[ErrorMessage] 
  FROM [master].[dbo].[CommandLog] 
-- where [CommandType] = 'ALTER_INDEX'
  order by [StartTime] desc 

 

Sprawdzenie wersji edycji silnika MSSQL (Express / Standard / Enterprise)

Wykonujemy w SSMS z wybraną bazą master

SELECT 
    SERVERPROPERTY('Edition') AS 'Edition ', 
    SERVERPROPERTY('ProductVersion') AS 'Product Version ', 
    SERVERPROPERTY('ProductLevel') AS 'Product Level ', 
    SERVERPROPERTY('EngineEdition') AS 'Engine Edition ' 

 

Downgrade / zmiana edycji silnika MSSQL

Wykonujemy z poziomu CMD, w katalogu z plikami instalacyjnymi MSSQL

setup.exe /ACTION=EditionUpgrade /SkipRules=Cluster_EditionDownGradeCheck 

Sprawdzenie wielkości danych w msdb

Wykonujemy w SSMS z wybraną bazą danych msdb

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 

 

Sprawdzenie zajętości plików bazy danych

Wykonujemy w SSMS z wybraną bazą danych master

DECLARE @DB NVARCHAR(128) = 'NazwaBazyDanych'; 
DECLARE @sql NVARCHAR(MAX); 

SET @sql = N' 
USE ' + QUOTENAME(@DB) + N'; 
SELECT 
    ''' + @DB + N''' AS DatabaseName, 
    mf.name AS LogicalFileName, 
    mf.physical_name AS PhysicalFileName, 
    mf.type_desc AS FileType, 
    mf.size / 128.0 AS FileSizeMB, 
    FILEPROPERTY(mf.name, ''SpaceUsed'') / 128.0 AS SpaceUsedMB, 
    (FILEPROPERTY(mf.name, ''SpaceUsed'') / 128.0) / (mf.size / 128.0) * 100 AS SpaceUsedPercent 
FROM 
    sys.master_files mf 
WHERE 
    mf.database_id = DB_ID(''' + @DB + N'''); '; 

-- Execute the dynamic SQL 
EXEC sp_executesql @sql; 

Sprawdzenie aktualnie działających jobów SQL Agent

Wykonujemy w SSMS z wybraną bazą master 

   WITH
    CTE_Sysession (AgentStartDate)
    AS
    (
        SELECT MAX(AGENT_START_DATE) AS AgentStartDate FROM MSDB.DBO.SYSSESSIONS
    )
SELECT sjob.name AS JobName 
        ,CASE  
            WHEN SJOB.enabled = 1 THEN 'Enabled' 
            WHEN sjob.enabled = 0 THEN 'Disabled' 
            END AS JobEnabled 
        ,sjob.description AS JobDescription 
        ,CASE  
            WHEN ACT.start_execution_date IS NOT NULL AND ACT.stop_execution_date IS NULL  THEN 'Running' 
            WHEN ACT.start_execution_date IS NOT NULL AND ACT.stop_execution_date IS NOT NULL AND HIST.run_status = 1 THEN 'Stopped' 
            WHEN HIST.run_status = 0 THEN 'Failed' 
            WHEN HIST.run_status = 3 THEN 'Canceled' 
        END AS JobActivity 
        ,DATEDIFF(MINUTE,act.start_execution_date, GETDATE()) DurationMin 
        ,hist.run_date AS JobRunDate 
        ,run_DURATION/10000 AS Hours 
        ,(run_DURATION%10000)/100 AS Minutes  
        ,(run_DURATION%10000)%100 AS Seconds 
        ,hist.run_time AS JobRunTime  
        ,hist.run_duration AS JobRunDuration 
        ,act.start_execution_date AS JobStartDate 
        ,act.last_executed_step_id AS JobLastExecutedStep 
        ,act.last_executed_step_date AS JobExecutedStepDate 
        ,act.stop_execution_date AS JobStopDate 
        ,act.next_scheduled_run_date AS JobNextRunDate 
        ,sjob.date_created AS JobCreated 
        ,sjob.date_modified AS JobModified       
            FROM MSDB.DBO.syssessions AS SYS1 
        INNER JOIN CTE_Sysession AS SYS2 ON SYS2.AgentStartDate = SYS1.agent_start_date 
        JOIN  msdb.dbo.sysjobactivity act ON act.session_id = SYS1.session_id 
        JOIN msdb.dbo.sysjobs sjob ON sjob.job_id = act.job_id 
        LEFT JOIN  msdb.dbo.sysjobhistory hist ON hist.job_id = act.job_id AND hist.instance_id = act.job_history_id 
        WHERE ACT.start_execution_date IS NOT NULL AND ACT.stop_execution_date IS NULL 
        ORDER BY ACT.start_execution_date DESC

 Przykładowy wynik:

Naprawa uprawnień usera SQL po odtworzeniu baz (zmiana ID)

Komendę uruchamiamy na bazie w której user powinien mieć naprawione uprawnienia bądź dodajemy:

USE [NazwaBazy]

EXEC sp_change_users_login 'Auto_Fix', 'NazwaUsera';

Wyciąganie pliku JPK w XML z baz Optimy

Uruchamiamy w PowerShell z konta które ma połączenie autentykacją windows do bazy danych:

$server = "NazwaSerwera"
$db     = "NazwaBazy"
$nazwa  = "NazwaPlikuDocelowego"
$out    = "C:\SciezkaDocelowa\$nazwa.xml"
$tabela = "[NazwaBazy].[NazwaTabeli]" #Zwykle [NazwaBazy].[CDN].[PlikiJPK]

$conn = New-Object System.Data.SqlClient.SqlConnection("Server=$server;Database=$db;Integrated Security=True")
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText = "SELECT JPK_XML FROM $tabela WHERE JPK_Nazwa = @n"
$null = $cmd.Parameters.AddWithValue("@n", $nazwa)
$bytes = [byte[]]$cmd.ExecuteScalar()
$conn.Close()

[IO.File]::WriteAllBytes($out, $bytes)
Write-Host "Zapisano $($bytes.Length) bajtów do $out"

Zbieranie danych:

Pobieranie danych o JPK by wiedzieć jak wygląda tabela:

SELECT TOP (10) [JPK_JPKID]
      ,[JPK_KodOsoby]
      ,[JPK_Typ]
      ,[JPK_Nazwa]
      ,[JPK_DataUtworzenia]
      ,[JPK_CelZlozenia]
      ,[JPK_WariantFormularza]
      ,[JPK_XML]
      ,[JPK_RefNr]
      ,[JPK_Path]
      ,[JPK_Status]
      ,[JPK_StatusCode]
      ,[JPK_StatusOpis]
      ,[JPK_KodUrzedu]
      ,[JPK_DataWyslania]
      ,[JPK_DataWplyniecia]
      ,[JPK_KodOsobyOdbierajacej]
      ,[JPK_DataOdebrania]
      ,[JPK_SkrotDokumentu]
      ,[JPK_SkrotStruktury]
      ,[JPK_StrukturaLogiczna]
      ,[JPK_StempelCzasu]
      ,[JPK_SigningTime]
      ,[JPK_NazwaPodmiotu]
      ,[JPK_NIP1]
      ,[JPK_OkresOd]
      ,[JPK_OkresDo]
  FROM [NazwaBazy].[CDN].[PlikiJPK] 

Weryfikacja w SSMS które kolumny są varbinary (zawierają pliki)

SELECT t.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t ON t.TABLE_NAME = c.TABLE_NAME
WHERE c.DATA_TYPE IN ('varbinary','image','xml')
  AND (t.TABLE_NAME LIKE '%JPK%' OR c.COLUMN_NAME LIKE '%JPK%');

Wciągnięcie modyfikacji danych w księgach rozliczeniowych - OPTIMA

DECLARE @Od    date = '2026-01-01';   -- okno akcji: początek roku
DECLARE @Do    date = '2026-07-08';   -- koniec półotwarty = do 7 lipca włącznie
DECLARE @StareOd date = '2025-01-01'; -- "stare księgi": rok 2025
DECLARE @StareDo date = '2026-01-01';

IF OBJECT_ID('tempdb..#baza') IS NOT NULL DROP TABLE #baza;

SELECT
    n.DeN_DeNId,
    n.DeN_Dziennik,
    n.DeN_NrDziennika,
    n.DeN_DataDok,
    n.DeN_Bufor,
    n.DeN_TS_Zal,
    n.DeN_TS_Mod,
    CASE WHEN n.DeN_TS_Zal >= @Od AND n.DeN_TS_Zal < @Do
         THEN 'Nowy wpis z datą 2025'
         ELSE 'Modyfikacja starego wpisu' END AS TypZmiany,
    CASE WHEN n.DeN_TS_Zal >= @Od AND n.DeN_TS_Zal < @Do
         THEN n.DeN_TS_Zal ELSE n.DeN_TS_Mod END AS AkcjaTS
INTO #baza
FROM CDN.DekretyNag n
WHERE n.DeN_DataDok >= @StareOd AND n.DeN_DataDok < @StareDo        -- księga 2025
  AND ( (n.DeN_TS_Zal >= @Od AND n.DeN_TS_Zal < @Do)
     OR (n.DeN_TS_Mod >= @Od AND n.DeN_TS_Mod < @Do) );             -- ruszone w 2026

-- 1) LISTA SZCZEGÓŁOWA
SELECT
    b.DeN_Dziennik,
    b.DeN_NrDziennika,
    b.DeN_DataDok  AS DataKsieg_2025,
    b.AkcjaTS      AS KiedyRuszone_2026,
    b.TypZmiany,
    b.DeN_Bufor,
    e.DeE_KontoWn,
    e.DeE_KontoMa,
    e.DeE_Kwota,
    e.DeE_Kategoria,
    e.DeE_Dokument
FROM #baza b
JOIN CDN.DekretyElem e ON e.DeE_DeNId = b.DeN_DeNId
ORDER BY b.AkcjaTS, b.DeN_Dziennik, b.DeN_NrDziennika, e.DeE_Lp;

-- 2) PODSUMOWANIE: ile dokumentów dot. 2025 ruszono w kolejnych miesiącach 2026
SELECT
    DATEFROMPARTS(YEAR(b.AkcjaTS), MONTH(b.AkcjaTS), 1) AS MiesiacAkcji,
    b.TypZmiany,
    COUNT(*)                                            AS LiczbaDokumentow,
    SUM(CASE WHEN b.DeN_Bufor = 1 THEN 1 ELSE 0 END)    AS WBuforze
FROM #baza b
GROUP BY DATEFROMPARTS(YEAR(b.AkcjaTS), MONTH(b.AkcjaTS), 1), b.TypZmiany
ORDER BY MiesiacAkcji, TypZmiany;

DROP TABLE #baza;

Wynik: