List of backup details – Oracle Database

To get list of full backup from Oracle database run the script below, which uses the rman_backup_job_details system view, this list all backups in database with archivelog mode on and backup size of at least 1 Terabyte.

SELECT * FROM v$rman_backup_job_details
WHERE input_type = 'DB INCR'
AND output_bytes_display LIKE '%T'
ORDER BY session_key DESC;

Find out time left in backup/restore – SQL Server

Sometimes you want to find out how soon the backup or restore will finish. We can use sys.dm_exec_requests table to find out that information. sys.dm_exec_requests displays all request waiting to be executed or are currently executing. Cross joining the sys.dm_exec_sql_text table will give the query text in the queue. The time shown in estimated_completion_time are estimates hence estimated in the name, but relatively accurate and gives the user a general idea of how long the backup or restore will take.

Query about full backup:

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in (‘BACKUP DATABASE’)


Query about log backup:

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in (‘BACKUP LOG’)


Query about restore job:

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in (‘RESTORE DATABASE’)

Automate index reorganize – SQL Server

Script below checks for indexes with fragmentation between 5 and 30 percent and with pagecount of > 5000, then inserts those records into temp table and uses it to reorganize each index. Script is very basic and can be improved upon based on user needs.


DECLARE @reorg_table TABLE(
table_name NVARCHAR(35),
index_name NVARCHAR(255))

INSERT INTO @reorg_table
SELECT OBJECT_NAME(ip.object_id) AS table_name, si.name AS index_name FROM sys.indexes si
INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) ip ON si.object_id=ip.object_idand si.index_id=ip.index_id
WHERE 1=1
AND ip.avg_fragmentation_in_percent BETWEEN 5 AND 30
AND ip.page_count > 5000
ORDER BY OBJECT_NAME(ip.object_id), ip.page_count

DECLARE @table_name NVARCHAR(35),
@index_name NVARCHAR(255),
@reorg_stmnt NVARCHAR(250)

WHILE (SELECT COUNT(*) FROM @reorg_table) > 0

BEGIN

SELECT @table_name = table_name, @index_name = index_name FROM @reorg_table

PRINT @table_name

PRINT @index_name

EXECUTE ('ALTER INDEX ' + @index_name + ' ON ' + @table_name + ' REORGANIZE WITH (LOB_COMPACTION = ON )')

DELETE FROM @reorg_table

INSERT INTO @reorg_table

SELECT OBJECT_NAME(ip.object_id) AS table_name, si.name AS index_name FROM sys.indexes si
INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) ip ON si.object_id=ip.object_idand si.index_id=ip.index_id
WHERE 1=1
AND ip.avg_fragmentation_in_percent > 5
AND ip.page_count > 5000
ORDER BY OBJECT_NAME(ip.object_id), ip.page_count

END

Fragmentation and rebuild/reorganize of indexes – SQL Server

Fragmentation will happen in database, that’s not news. Why it happens is beyond scope of this post, but for a good explanation look at Paul Randall’s excellent post about fragmentation here.

Microsoft recommends that if fragmentation < 5% or with pages < 1000 then do nothing. According to Paul Randall he made these up, read here. If fragmentation is >5% and <30% than reorganize the indexes, else rebuild.

ALTER INDEX ‘name_index’ ON ‘table_name’ REORGANIZE WITH (LOB_COMPACTION = ON)

During REORGANIZE table is available, hence an online operation.

ALTER INDEX ‘name_index’ ON ‘table_name’ REBUILD WITH (LOB_COMPACTION = ON)

Rebuild normally is offline operation meaning the table will not be available, unless run on enterprise version with ONLINE option.