Uncategorized

Save Disk space using TF 3042 during compressed backups – SQL SERVER

To allow the backup file to grow only as needed to reach its final size, use trace flag 3042. Trace flag 3042 causes the backup operation to bypass the default backup compression pre-allocation algorithm. This trace flag is useful if you need to save on space by allocating only the actual size required for the compressed backup. However, using this trace flag might cause a slight performance penalty (a possible increase in the duration of the backup operation).

TSQL to get the total Log send queue size of the AG group in SQL

;WITH UpTime AS
(
SELECT DATEDIFF(SECOND,create_date,GETDATE()) [upTime_secs]
FROM sys.databases
WHERE name = ‘tempdb’
),
AG_Stats AS
(
SELECT AR.replica_server_name,
HARS.role_desc,
Db_name(DRS.database_id) [DBName],
CAST(DRS.log_send_queue_size AS DECIMAL(19,2)) log_send_queue_size_KB,
(CAST(perf.cntr_value AS DECIMAL(19,2)) / CAST(UpTime.upTime_secs AS DECIMAL(19,2))) / CAST(1024 AS DECIMAL(19,2)) [log_KB_flushed_per_sec]
FROM sys.dm_hadr_database_replica_states DRS
INNER JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id
INNER JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id
AND AR.replica_id = HARS.replica_id
INNER JOIN sys.dm_os_performance_counters perf ON perf.instance_name = Db_name(DRS.database_id)
AND perf.counter_name like ‘Log Bytes Flushed/sec%’
CROSS APPLY UpTime
),
Pri_CommitTime AS
(
SELECT replica_server_name
, DBName
, [log_KB_flushed_per_sec]
FROM AG_Stats
WHERE role_desc = ‘PRIMARY’
),
Sec_CommitTime AS
(
SELECT replica_server_name
, DBName
, log_send_queue_size_KB
FROM AG_Stats
WHERE role_desc = ‘SECONDARY’
)
SELECT
sum(s.log_send_queue_size_KB)/1024/1024 as Queue_size_in_GB
FROM Pri_CommitTime p
LEFT JOIN Sec_CommitTime s ON [s].[DBName] = [p].[DBName]

Get Index Creation Progress – SQL Server

For monitoring Index progress during creation or alter, we need to enable the STATISTICS PROFILE by adding the below statement before create/ alter index T-SQL statement.

SET STATISTICS PROFILE ON

SET STATISTICS PROFILE ON
GO
CREATE NONCLUSTERED INDEX ……….
GO

To monitor the progress run below T-SQL. replace the session ID with your session id.

DECLARE @SPID INT = 65;

;WITH agg AS
(
SELECT SUM(qp.[row_count]) AS [RowsProcessed],
SUM(qp.[estimate_row_count]) AS [TotalRows],
MAX(qp.last_active_time) – MIN(qp.first_active_time) AS [ElapsedMS],
MAX(IIF(qp.[close_time] = 0 AND qp.[first_row_time] > 0,
[physical_operator_name],
N”)) AS [CurrentStep]
FROM sys.dm_exec_query_profiles qp
WHERE qp.[physical_operator_name] IN (N’Table Scan’, N’Clustered Index Scan’,
N’Index Scan’, N’Sort’)
AND qp.[session_id] = @SPID
), comp AS
(
SELECT *,
([TotalRows] – [RowsProcessed]) AS [RowsLeft],
([ElapsedMS] / 1000.0) AS [ElapsedSeconds]
FROM agg
)
SELECT [CurrentStep],
[TotalRows],
[RowsProcessed],
[RowsLeft],
CONVERT(DECIMAL(5, 2),
(([RowsProcessed] * 1.0) / [TotalRows]) * 100) AS [PercentComplete],
[ElapsedSeconds],
(([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]) AS [EstimatedSecondsLeft],
DATEADD(SECOND,
(([ElapsedSeconds] / [RowsProcessed]) * [RowsLeft]),
GETDATE()) AS [EstimatedCompletionTime]
FROM comp;

Get SQL Server Database File Locations

These 2 tables will give the Database file location in SQL

select * from sys.sysaltfiles

select * from sys.master_files

Sample 1:

SELECT 

    mdf.database_id, 

    mdf.name, 

    mdf.physical_name as data_file, 

    ldf.physical_name as log_file, 

    db_size = CAST((mdf.size * 8.0)/1024 AS DECIMAL(8,2)), 

    log_size = CAST((ldf.size * 8.0 / 1024) AS DECIMAL(8,2))

FROM (SELECT * FROM sys.master_files WHERE type_desc = ‘ROWS’ ) mdf

JOIN (SELECT * FROM sys.master_files WHERE type_desc = ‘LOG’ ) ldf

ON mdf.database_id = ldf.database_id