How To Get Space Used by Tables and Indexes in SQL Server?

 Databases can use a lot of storage space. It is always good to know how the space is distributed among the tables and indexes. SQL Server allows us to query all relevant data and create our desired reports. In this article I will show different queries and alternative options to access the storage sizes at various levels of detail.

What requires storage space?

A database has a total size which includes the actual data, transaction log data and reserved storage space, since the database is expected to grow. The data is stored in one primary data file with the file extension .mdf, optional user-defined data files with the file extension .ndf and at least one transaction log file with the file extension .ldf. See this article about database files and filegroups to learn more about it.

The data is basically separated into two types: tables and indexes. A table has either a clustered index with all data or it is a heap table. Indexes are copies of the data and need space too. Note that a view can have indexes and therefore can require storage space too. Additional space is used to store the references to the actual allocations (IAM Pages).

Pages and extents are used to structure the data. All data pages are the same size: 8 KB. An extent is eight physically contiguous pages, or 64 KB. See this article about pages and extents to learn more about it.

Getting storage sizes from properties with SSMS

An easy way to get the total size of database is by using SQL Server Management Studio, right clicking on a database in the Object Explorer and open the properties window. There are the properties “Size” and “Space available”. In the “Files” page are all files listed including the reserved size by file.

In the property window of tables on the “Storage” page are the “Data space” and the “Index space” listed.

Using SSMS Reports

SQL Server Management Studio also offers reports to get the storage size of tables. These can be accessed by right clicking on a database and then go to “Reports”. The most interesting report is “Disk Usage by Table” which uses the following SQL query:

SELECT

    (row_number() over(order by a3.name, a2.name)) % 2 as l1,

    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

    WHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables WHERE is_memory_optimized = 1)

    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 a3.name, a2.name

The data is read from the sys.dm_db_partition_stats view. The LFET JOIN with the sub query is used to get the size for columns with the xml type.

The result shows the count of rows, the reserved size for the whole table (or indexed view), the size of the data (this is the size of the cluster index or heap without the indexes), the size of all indexes including xml columns and the size of unused space.

Using sp_spaceused

sp_spaceused is a stored procedure to get the size of an object. You can either provide the name of a table or nothing to get the size of the whole database.

The @updateusage parameter with the value 'true' will run DBCC UPDATEUSAGE for the given object or the whole database. This can be used to update the stats, since the stats are not guaranteed to be up to date.

-- show the storage space of the whole database

EXECUTE sp_spaceused

-- show the storage space of a single table

EXECUTE sp_spaceused 'Person.Address'

-- update the stats for the given table and show the storage space

EXECUTE sp_spaceused 'Person.Address', 'true'

Getting file sizes with SQL query

The sys.database_files view contains all files of the selected database. The table contains the count of pages per file and in combination with the FILEPROPERTY function and 'SpaceUsed' argument we can get the used pages. By multiplying the count of pages with 8 we get the kilobytes. The final query looks like this:

SELECT

    F.file_id AS FileId,

    F.type_desc AS FileType,

    F.name AS FileName,

    F.physical_name AS PhysicalFileName,

    CASE WHEN F.max_size <= 0 THEN -1 ELSE CAST(CAST(F.max_size AS DECIMAL(18, 2)) * 8 / 1024 AS DECIMAL(18, 2)) END AS MaxSizeMb,

    CAST(CAST(F.size AS DECIMAL(18, 2)) * 8 / 1024 AS DECIMAL(18, 2)) AS TotalMb,

    CAST(CAST(FILEPROPERTY(F.name, 'SpaceUsed') AS DECIMAL(18, 2)) * 8 / 1024 AS DECIMAL(18, 2)) AS UsedMb,

    CAST(CAST((F.size - FILEPROPERTY(F.name, 'SpaceUsed')) AS DECIMAL(18, 2)) * 8 / 1024 AS DECIMAL(18, 2)) AS UnusedMb

FROM sys.database_files AS F

Getting index sizes with SQL query

The sys.allocation_units view contains the allocations in the partitions (container_id). It has a column for the total pages and one for the used pages. The view can be joined with sys.partitions to get the index it belongs to.

The sys.partitions view does contain the allocations by index, the sys.dm_db_partition_stats view, which is used by the “Disk Usage by Table”, is only for tables.

The final query looks like this:

SELECT

    S.name AS SchemaName,

    T.name AS TableName,

    ISNULL(I.name, '') AS IndexName,

    T.object_id AS TableObjectId,

    T.type_desc AS TableType,

    I.index_id AS IndexId,

    I.type_desc AS IndexType,

    A.Rows AS Rows,

    A.TotalPages + ISNULL(X.XmlTotalPages, 0) AS TotalPages,

    A.UsedPages + ISNULL(X.XmlUsedPages, 0) AS UsedPages,

    (A.TotalPages + ISNULL(X.XmlTotalPages, 0)) - (A.UsedPages + ISNULL(X.XmlUsedPages, 0)) AS UnusedPages,

    (A.TotalPages + ISNULL(X.XmlTotalPages, 0)) * 8 AS TotalKb,

    (A.UsedPages + ISNULL(X.XmlUsedPages, 0)) * 8 AS UsedKb,

    ((A.TotalPages + ISNULL(X.XmlTotalPages, 0)) - (A.UsedPages + ISNULL(X.XmlUsedPages, 0))) * 8 AS UnusedKb,

    CAST(CAST((A.TotalPages + ISNULL(X.XmlTotalPages, 0)) * 8 AS DECIMAL(18, 4)) / 1024 AS DECIMAL(18, 4)) AS TotalMb,

    CAST(CAST((A.UsedPages + ISNULL(X.XmlUsedPages, 0)) * 8 AS DECIMAL(18, 4)) / 1024 AS DECIMAL(18, 4)) AS UsedMb,

    CAST(CAST(((A.TotalPages + ISNULL(X.XmlTotalPages, 0)) - (A.UsedPages + ISNULL(X.XmlUsedPages, 0))) * 8 AS DECIMAL(18, 4)) / 1024 AS DECIMAL(18, 4)) AS UnusedMb,

    CAST(CASE

             WHEN A.TotalPages + ISNULL(X.XmlTotalPages, 0) = 0 THEN

                 0

             ELSE

                 CAST((A.UsedPages + ISNULL(X.XmlUsedPages, 0)) AS DECIMAL(18, 4)) / CAST((A.TotalPages + ISNULL(X.XmlTotalPages, 0)) AS DECIMAL(18, 4)) * 100

         END AS DECIMAL(18, 4)) AS PercentUsed,

    CAST(CASE

             WHEN A.TotalPages + ISNULL(X.XmlTotalPages, 0) = 0 THEN

                 0

             ELSE

                 100 - (CAST((A.UsedPages + ISNULL(X.XmlUsedPages, 0)) AS DECIMAL(18, 4)) / CAST((A.TotalPages + ISNULL(X.XmlTotalPages, 0)) AS DECIMAL(18, 4)) * 100)

         END AS DECIMAL(18, 4)) AS PercentUnused,

    CAST(CASE

             WHEN A.TotalPages + ISNULL(X.XmlTotalPages, 0) = 0 THEN

                 0

             ELSE

                 CAST((A.TotalPages + ISNULL(X.XmlTotalPages, 0)) AS DECIMAL(18, 4)) / SUM(CAST((A.TotalPages + ISNULL(X.XmlTotalPages, 0)) AS DECIMAL(18, 4))) OVER() * 100

         END AS DECIMAL(18, 4)) AS PercentFromTotal

FROM sys.objects AS T

    INNER JOIN sys.schemas AS S ON S.schema_id = T.schema_id

    INNER JOIN sys.indexes AS I ON I.object_id = T.object_id

    INNER JOIN (SELECT

                    PA.object_id,

                    PA.index_id,

                    SUM(CASE WHEN AU.type_desc = 'IN_ROW_DATA' THEN PA.rows ELSE 0 END) AS Rows,

                    SUM(AU.total_pages) AS TotalPages,

                    SUM(AU.used_pages) AS UsedPages

                FROM sys.allocation_units AS AU

                    INNER JOIN sys.partitions AS PA ON PA.partition_id = AU.container_id

                GROUP BY PA.object_id, PA.index_id

               ) AS A ON A.object_id = I.object_id AND A.index_id = I.index_id

    LEFT JOIN (SELECT

                   IT.parent_id,

                   SUM(AU.total_pages) AS XmlTotalPages,

                   SUM(AU.used_pages) AS XmlUsedPages

               FROM sys.allocation_units AS AU

                   INNER JOIN sys.partitions AS PA ON PA.partition_id = AU.container_id

                   INNER JOIN sys.internal_tables AS IT ON IT.object_id = PA.object_id

               WHERE IT.internal_type_desc = 'XML_INDEX_NODES'

               GROUP BY IT.parent_id

              ) AS X ON X.parent_id = T.object_id AND I.type_desc IN ('HEAP', 'CLUSTERED')

WHERE T.type_desc IN ('USER_TABLE', 'VIEW')

ORDER BY PercentFromTotal DESC, S.name ASC, T.name ASC, I.index_id ASC

The result does contain the storage sizes in different units and percentages. The xml columns are appended to the clustered index or heap. Indexes on views are also included in the result. The PercentFromTotal column can be used to identify big tables or indexes in the database.

Getting table sizes with SQL query

The query for the indexes is the basis for the table size query. The grouping by index is removed in the allocations sub query and the xml size is appended to the entire table:

SELECT

    S.name AS SchemaName,

    T.name AS TableName,

    T.object_id AS TableObjectId,

    T.type_desc AS TableType,

    A.Rows AS Rows,

    A.TotalPages + ISNULL(X.XmlTotalPages, 0) AS TotalPages,

    A.UsedPages + ISNULL(X.XmlUsedPages, 0) AS UsedPages,

    (A.TotalPages + ISNULL(X.XmlTotalPages, 0)) - (A.UsedPages + ISNULL(X.XmlUsedPages, 0)) AS UnusedPages,

    (A.TotalPages + ISNULL(X.XmlTotalPages, 0)) * 8 AS TotalKb,

    (A.UsedPages + ISNULL(X.XmlUsedPages, 0)) * 8 AS UsedKb,

    ((A.TotalPages + ISNULL(X.XmlTotalPages, 0)) - (A.UsedPages + ISNULL(X.XmlUsedPages, 0))) * 8 AS UnusedKb,

    CAST(CAST((A.TotalPages + ISNULL(X.XmlTotalPages, 0)) * 8 AS DECIMAL(18, 4)) / 1024 AS DECIMAL(18, 4)) AS TotalMb,

    CAST(CAST((A.UsedPages + ISNULL(X.XmlUsedPages, 0)) * 8 AS DECIMAL(18, 4)) / 1024 AS DECIMAL(18, 4)) AS UsedMb,

    CAST(CAST(((A.TotalPages + ISNULL(X.XmlTotalPages, 0)) - (A.UsedPages + ISNULL(X.XmlUsedPages, 0))) * 8 AS DECIMAL(18, 4)) / 1024 AS DECIMAL(18, 4)) AS UnusedMb,

    CAST(CASE

             WHEN A.TotalPages + ISNULL(X.XmlTotalPages, 0) = 0 THEN

                 0

             ELSE

                 CAST((A.UsedPages + ISNULL(X.XmlUsedPages, 0)) AS DECIMAL(18, 4)) / CAST((A.TotalPages + ISNULL(X.XmlTotalPages, 0)) AS DECIMAL(18, 4)) * 100

         END AS DECIMAL(18, 4)) AS PercentUsed,

    CAST(CASE

             WHEN A.TotalPages + ISNULL(X.XmlTotalPages, 0) = 0 THEN

                 0

             ELSE

                 100 - (CAST((A.UsedPages + ISNULL(X.XmlUsedPages, 0)) AS DECIMAL(18, 4)) / CAST((A.TotalPages + ISNULL(X.XmlTotalPages, 0)) AS DECIMAL(18, 4)) * 100)

         END AS DECIMAL(18, 4)) AS PercentUnused,

    CAST(CASE

             WHEN A.TotalPages + ISNULL(X.XmlTotalPages, 0) = 0 THEN

                 0

             ELSE

                 CAST((A.TotalPages + ISNULL(X.XmlTotalPages, 0)) AS DECIMAL(18, 4)) / SUM(CAST((A.TotalPages + ISNULL(X.XmlTotalPages, 0)) AS DECIMAL(18, 4))) OVER() * 100

         END AS DECIMAL(18, 4)) AS PercentFromTotal

FROM sys.objects AS T

    INNER JOIN sys.schemas AS S ON S.schema_id = T.schema_id

    INNER JOIN (SELECT

                    PA.object_id,

                    SUM(CASE WHEN AU.type_desc = 'IN_ROW_DATA' AND PA.index_id IN (0, 1) THEN PA.rows ELSE 0 END) AS Rows,

                    SUM(AU.total_pages) AS TotalPages,

                    SUM(AU.used_pages) AS UsedPages

                FROM sys.allocation_units AS AU

                    INNER JOIN sys.partitions AS PA ON PA.partition_id = AU.container_id

                GROUP BY PA.object_id

               ) AS A ON A.object_id = T.object_id

    LEFT JOIN (SELECT

                   IT.parent_id,

                   SUM(AU.total_pages) AS XmlTotalPages,

                   SUM(AU.used_pages) AS XmlUsedPages

               FROM sys.allocation_units AS AU

                   INNER JOIN sys.partitions AS PA ON PA.partition_id = AU.container_id

                   INNER JOIN sys.internal_tables AS IT ON IT.object_id = PA.object_id

               WHERE IT.internal_type_desc = 'XML_INDEX_NODES'

               GROUP BY IT.parent_id

              ) AS X ON X.parent_id = T.object_id

WHERE T.type_desc IN ('USER_TABLE', 'VIEW')

ORDER BY PercentFromTotal DESC, S.name ASC, T.name ASC

SQLDBA- SQL Server Wait Events

 

To solve slowness of SQL Server database, you should find which wait events exists in the database.

You can find wait events of database with following query.

with waits as
(select
wait_type,
wait_time_ms / 1000.0 as waits,
(wait_time_ms - signal_wait_time_ms) / 1000.0 as resources,
signal_wait_time_ms / 1000.0 as signals,
waiting_tasks_count as waitcount,
100.0 * wait_time_ms / sum (COALESCE (wait_time_ms,1)) over() as percentage,
row_number() over(order by wait_time_ms desc) as rownum
from sys.dm_os_wait_stats
where wait_type not in (
N'CLR_SEMAPHORE', N'LAZYWRITER_SLEEP',
N'RESOURCE_QUEUE', N'SQLTRACE_BUFFER_FLUSH',
N'SLEEP_TASK', N'SLEEP_SYSTEMTASK',
N'WAITFOR', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
N'XE_TIMER_EVENT', N'XE_DISPATCHER_JOIN',
N'LOGMGR_QUEUE', N'FT_IFTS_SCHEDULER_IDLE_WAIT',
N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
N'CLR_AUTO_EVENT', N'DISPATCHER_QUEUE_SEMAPHORE',
N'TRACEWRITE', N'XE_DISPATCHER_WAIT',
N'BROKER_TO_FLUSH', N'BROKER_EVENTHANDLER',
N'FT_IFTSHC_MUTEX', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'DIRTY_PAGE_POLL', N'SP_SERVER_DIAGNOSTICS_SLEEP')
)
select
w1.wait_type as waittype, 
cast (w1.waits as decimal(14, 2)) wait_s,
cast (w1.resources as decimal(14, 2)) resource_s,
cast (w1.signals as decimal(14, 2)) signal_s,
w1.waitcount wait_count,
cast (w1.percentage as decimal(4, 2)) percentage,
cast ((w1.waits / w1.waitcount) as decimal (14, 4)) avgWait_s,
cast ((w1.resources / w1.waitcount) as decimal (14, 4)) avgResource_s,
cast ((w1.signals / w1.waitcount) as decimal (14, 4)) avgSignal_s
from waits as w1
inner join waits as w2 on w2.rownum <= w1.rownum
group by w1.rownum, w1.wait_type, w1.waits, w1.resources, w1.signals, w1.waitcount, w1.percentage
having sum (w2.percentage) - w1.percentage < 95; -- percentage threshold

You can get list and status of currently waiting tasks with following script.

select wt.session_id, wt.exec_context_id, wt.wait_duration_ms, wt.wait_type, wt.blocking_session_id, wt.resource_address, wt.resource_description, s.program_name, st.text, sp.query_plan, s.cpu_time cpu_time_ms, s.memory_usage*8 memory_usage_kb
from sys.dm_os_waiting_tasks wt
join sys.dm_exec_sessions s on s.session_id=wt.session_id
join sys.dm_exec_requests r on r.session_id=s.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) st
outer apply sys.dm_exec_query_plan(r.plan_handle) sp
where s.is_user_process=1
order by wt.session_id, wt.exec_context_id

You can find wait events of database with following query.

SELECT getdate() as 'Run_Time' --script running time
    , wait_type --wait type
    ,waiting_tasks_count
, CAST(wait_time_ms / 1000. AS DECIMAL(12, 2)) AS wait_time_s --saniye cinsinden bekleme zamaný
, CAST(100. * wait_time_ms / SUM(wait_time_ms) OVER() AS DECIMAL(12, 2)) AS pct --toplam beklemeye oraný
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('BROKER_TASK_STOP','Total','SLEEP','BROKER_EVENTHANDLER','BROKER_RECEIVE_WAITFOR',
      'BROKER_TRANSMITTER','CHECKPOINT_QUEUE','CHKPT,CLR_AUTO_EVENT','CLR_MANUAL_EVENT','KSOURCE_WAKEUP','LAZYWRITER_SLEEP',
      'LOGMGR_QUEUE','ONDEMAND_TASK_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','RESOURCE_QUEUE','SERVER_IDLE_CHECK',
      'SLEEP_BPOOL_FLUSH','SLEEP_DBSTARTUP','SLEEP_DCOMSTARTUP','SLEEP_MSDBSTARTUP','SLEEP_SYSTEMTASK','SLEEP_TASK',
      'SLEEP_TEMPDBSTARTUP','SNI_HTTP_ACCEPT','SQLTRACE_BUFFER_FLUSH','TRACEWRITE','WAIT_FOR_RESULTS','WAITFOR_TASKSHUTDOWN',
       'XE_DISPATCHER_WAIT','XE_TIMER_EVENT','WAITFOR')
ORDER BY 4 DESC

SQLDBA- Generate the ALTER script for set compatibility for all databases

 

-- Alter  script set  compatibility y to sql server  2022

DECLARE @DBName SYSNAME;

DECLARE @SQL NVARCHAR(MAX);

-- Table variable to hold database names

DECLARE @DBList TABLE (DBName SYSNAME);

-- Insert all user databases except system ones

INSERT INTO @DBList (DBName)

SELECT name FROM sys.databases

WHERE database_id > 4 -- Exclude system DBs

AND state_desc = 'ONLINE';

-- Initialize loop

WHILE EXISTS (SELECT 1 FROM @DBList)

BEGIN

    -- Get one database name

    SELECT TOP 1 @DBName = DBName FROM @DBList;

    -- Build and execute the ALTER statement

    SET @SQL = 'ALTER DATABASE [' + @DBName + '] SET COMPATIBILITY_LEVEL = 160;';

    PRINT 'Updating compatibility for: ' + @DBName;

PRINT @SQL

    --EXEC sp_executesql @SQL;

    -- Remove processed DB

    DELETE FROM @DBList WHERE DBName = @DBName;

END

--Set  compatability  to  SQL server  2022

 SELECT 'ALTER DATABASE [' +  name+ '] SET COMPATIBILITY_LEVEL = 160

 GO' FROM sys.databases

WHERE name IN ( 

 'master'

)

-- Roll back script

 SELECT 'ALTER DATABASE [' +  name+ '] SET COMPATIBILITY_LEVEL = '+CONVERT(VARCHAR(5),compatibility_level) +'

 GO' FROM sys.databases

WHERE name IN ( 

 'master'

)

SQLDBA - Script to Loop through all the transaction log file and restore them automatically.

 

Script to Loop through all the transaction log file and restore them automatically.

Below script is generating the restoring the  transactional log backups.  Initially  we have inserted transaction files into  temp table and then we have generating restoring the tractional log backup.

Method1:

USE master;

DECLARE @t AS TABLE (fname VARCHAR(max));

INSERT @t EXEC xp_cmdshell 'dir /b /S E:\dirname\*.trn';

SELECT 'RESTORE LOG [DBNAME] FROM DISK = ''' + fname + ''' WITH NORECOVERY;' FROM @t WHERE fname IS NOT NULL

Method2:

Powershell  Script;

To automatically loop through and restore all SQL Server transaction log files (.trn), you can use a PowerShell script or T-SQL script depending on your environment and preferences. Here's a PowerShell script that reads all .trn files from a folder and restores them in sequence.

# Define variables
$serverName = "YourSQLServerInstance"  
$databaseName = "YourDatabase"  
$backupFolder = "C:\Backups\Logs"
$restoreDatabase = $false  # Set to $true if you want to restore the full DB first

# Optional: Restore full backup if needed
if ($restoreDatabase) {
    $fullBackupFile = "C:\Backups\YourDatabase.bak"
    Invoke-Sqlcmd -ServerInstance $serverName -Query "
        RESTORE DATABASE [$databaseName]
        FROM DISK = N'$fullBackupFile'
        WITH NORECOVERY, REPLACE"
}

# Get all .trn files sorted by creation time
$logFiles = Get-ChildItem -Path $backupFolder -Filter *.trn | Sort-Object LastWriteTime

# Loop through and restore each log file
foreach ($file in $logFiles) {
    Write-Host "Restoring log file: $($file.Name)"
    Invoke-Sqlcmd -ServerInstance $serverName -Query "
        RESTORE LOG [$databaseName]
        FROM DISK = N'$($file.FullName)'
        WITH NORECOVERY"
}

# Finally, recover the database
Invoke-Sqlcmd -ServerInstance $serverName -Query "
    RESTORE DATABASE [$databaseName] WITH RECOVERY"

Write-Host "Transaction log restore completed."

Method3:

# Define parameters
$serverInstance = "localhost\SQLEXPRESS"  # Change to your SQL Server instance
$databaseName = "YourDatabaseName"        # Change to your target database
$logBackupFolder = "C:\LogBackups"        # Folder containing .trn files

# Load SQL Server SMO
Add-Type -AssemblyName "Microsoft.SqlServer.SMO"
Add-Type -AssemblyName "Microsoft.SqlServer.ConnectionInfo"
Add-Type -AssemblyName "Microsoft.SqlServer.SmoExtended"

# Connect to SQL Server
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $serverInstance

# Get all .trn files sorted by name (assuming naming reflects order)
$logFiles = Get-ChildItem -Path $logBackupFolder -Filter *.trn | Sort-Object Name

foreach ($logFile in $logFiles) {
    Write-Host "Restoring log file: $($logFile.Name)"

    $restore = New-Object Microsoft.SqlServer.Management.Smo.Restore
    $restore.Action = "Log"
    $restore.Database = $databaseName
    $restore.Devices.AddDevice($logFile.FullName, "File")
    $restore.NoRecovery = $true  # Keep in restoring state for next log

    try {
        $restore.SqlRestore($server)
        Write-Host "Successfully restored: $($logFile.Name)"
    } catch {
        Write-Host "Failed to restore: $($logFile.Name)"
        Write-Host $_.Exception.Message
    }
}

Write-Host "All log files processed."

SQL DBA - Find the job names with user name with running as job owner.

 

-- Find the job names with user name with running as job owner. 

SELECT j.name AS JobName,

       j.owner_sid,

       s.name AS JobOwner,

       a.start_execution_date

FROM msdb.dbo.sysjobs AS j

INNER JOIN msdb.dbo.sysjobactivity AS a

    ON j.job_id = a.job_id

INNER JOIN sys.server_principals AS s

    ON j.owner_sid = s.sid

WHERE a.run_requested_date IS NOT NULL

  AND a.stop_execution_date IS NULL  -- Job is still running

  AND s.name LIKE  '%SVC_%';       -- Replace with the specific user

-- Find the job name, with command context . 

SELECT j.name AS JobName,

       js.step_id,

       js.step_name,

       js.database_name,

       js.command,

       js.subsystem

FROM msdb.dbo.sysjobs AS j

INNER JOIN msdb.dbo.sysjobsteps AS js

    ON j.job_id = js.job_id

WHERE js.command LIKE '%SVC%';