Category: SQL Server
-
Transaction Space Utilization Info
use DB_Name select db_name(database_id) ‘db_name’, total_log_size_in_bytes/(1024*1024) ‘total_log_size_in_mb’,used_log_space_in_bytes/(1024*1024) ‘used_log_space_in_mb’,used_log_space_in_percent,log_space_in_bytes_since_last_backup/(1024*1024) ‘log_space_in_mb_since_last_backup’ from sys.dm_db_log_space_usage DBCC SQLPERF(LOGSPACE); –VLF Count info.. SELECT [name], COUNT(l.database_id) AS ‘vlf_count’ FROM sys.databases AS s CROSS APPLY sys.dm_db_log_info(s.database_id) AS l GROUP BY name –VLF Status Info use DB_Name ;WITH cte_vlf AS ( SELECT ROW_NUMBER() OVER(ORDER BY vlf_begin_offset) AS vlfid, DB_NAME(database_id) AS [Database Name], vlf_sequence_number, vlf_active, vlf_begin_offset, vlf_size_mb FROM sys.dm_db_log_info(DEFAULT)), cte_vlf_cnt AS (SELECT [Database Name], COUNT(vlf_sequence_number) AS vlf_count, (SELECT COUNT(vlf_sequence_number) FROM cte_vlf WHERE vlf_active = 0) AS vlf_count_inactive, (SELECT COUNT(vlf_sequence_number) FROM cte_vlf WHERE vlf_active = 1) AS vlf_count_active, (SELECT MIN(vlfid) FROM cte_vlf WHERE vlf_active = 1) AS ordinal_min_vlf_active, (SELECT MIN(vlf_sequence_number) FROM cte_vlf WHERE vlf_active = 1) AS min_vlf_active, (SELECT MAX(vlfid) FROM cte_vlf WHERE vlf_active = 1) AS ordinal_max_vlf_active, (SELECT MAX(vlf_sequence_number) FROM cte_vlf WHERE vlf_active = 1) AS max_vlf_active FROM cte_vlf GROUP BY [Database Name]) SELECT [Database Name], vlf_count, min_vlf_active, ordinal_min_vlf_active, max_vlf_active, ordinal_max_vlf_active, ((ordinal_min_vlf_active-1)*100.00/vlf_count) AS free_log_pct_before_active_log, ((ordinal_max_vlf_active-(ordinal_min_vlf_active-1))*100.00/vlf_count) AS active_log_pct, ((vlf_count-ordinal_max_vlf_active)*100.00/vlf_count) AS free_log_pct_after_active_log FROM cte_vlf_cnt; GO dbcc loginfo –VLF Info of T-Log declare @database_id int set @database_id = DB_ID(); select * from sys.dm_db_log_info ( @database_id ) DBCC OPENTRAN; SELECT name, log_reuse_wait, log_reuse_wait_desc, is_cdc_enabled FROM sys.databases WHERE name = ‘DB_Name’; –To find Big Tables List in SQL Server WITH TableSizes AS (SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS…
//
-
SQL Server Statistics
— Turn on/off statistics IO SET STATISTICS IO ON SET STATISTICS IO OFF — Turn on/off statistics TIMESET STATISTICS TIME ON SET STATISTICS TIME OFF — Turn on/off profile information. Note: It displays the profile information for a statement. STATISTICS PROFILE works for ad hoc queries, views, and stored procedures. SET STATISTICS PROFILE ON SET…
//
-
DB Backup and Restore History
–DB Backup HistorySELECT CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server,msdb.dbo.backupset.database_name,msdb.dbo.backupset.backup_start_date,msdb.dbo.backupset.backup_finish_date,msdb.dbo.backupset.expiration_date,CASE msdb..backupset.type WHEN ‘D’ THEN ‘Database’ WHEN ‘L’ THEN ‘Log’ WHEN ‘I’ THEN ‘Diff’END AS backup_type, msdb.dbo.backupset.backup_size, msdb.dbo.backupmediafamily.logical_device_name, msdb.dbo.backupmediafamily.physical_device_name, msdb.dbo.backupset.name AS backupset_name, msdb.dbo.backupset.description FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() – 30) and msdb.dbo.backupset.database_name like ‘DB_Name’ order by 3 desc —DB Restore History SELECT [rh].[destination_database_name], [rh].[restore_date], [b].[backup_start_date], [b].[backup_finish_date], [b].[type], [b].[first_lsn], [b].[last_lsn], [b].[checkpoint_lsn], [b].[database_backup_lsn] FROM [msdb].[dbo].[restorehistory] AS [rh] JOIN [msdb].[dbo].[backupset] AS [b] ON [rh].[backup_set_id] = [b].[backup_set_id] WHERE [rh].[destination_database_name] = ‘DB_Name’ ORDER BY [rh].[restore_date] DESC; ====================================================================================================================== —DB Backup –Full backup of DB_Name BACKUP DATABASE [DB_Name] TO DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_1.bak’, DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_2.bak’, DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_3.bak’, DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_4.bak’, DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_5.bak’, DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_6.bak’, DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_7.bak’, DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_8.bak’, DISK = N’T:\DB_Backup\DB_Name\DB_Name_FULL_Backup_9.bak’, DISK…
//
-
AAOG Log Sync Analysis
SELECT r.replica_server_name AS Replica, DB_name(rs.database_id) ‘database_name’, rs.is_primary_replica IsPrimary, rs.last_received_lsn, rs.last_hardened_lsn, rs.last_redone_lsn, rs.end_of_log_lsn, rs.last_commit_lsn FROM sys.availability_replicas r INNER JOIN sys.dm_hadr_database_replica_states rs ON r.replica_id = rs.replica_id ORDER BY replica; select log_reuse_wait_desc from sys.databases where name like ‘DB_Name’ SELECT ar.replica_server_name as Replica, adc.database_name, drs.is_local, drs.is_primary_replica, drs.synchronization_state_desc, drs.is_commit_participant, drs.synchronization_health_desc, drs.recovery_lsn FROM sys.dm_hadr_database_replica_states AS drs INNER JOIN sys.availability_databases_cluster AS adc ON drs.group_id = adc.group_id AND drs.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups AS ag ON ag.group_id = drs.group_id INNER JOIN sys.availability_replicas AS ar ON drs.group_id = ar.group_id AND drs.replica_id = ar.replica_id ORDER BY Replica; Secondary replica is lagging behind the Primary replica SELECT ag.name AS ag_name, ar.replica_server_name AS ag_replica_server, db_name(dr_state.database_id) AS database_name, is_ag_replica_local = CASE WHEN ar_state.is_local = 1 THEN N’LOCAL’ ELSE ‘REMOTE’…
//
-
ALL About TempDB
For TempDB –Determining the amount of free space in tempdb USE tempdb; SELECT SUM(unallocated_extent_page_count) AS [free pages], (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB] FROM sys.dm_db_file_space_usage; –Determining the Amount of Space Used by User Objects USE tempdb; SELECT SUM(user_object_reserved_page_count) AS [user object pages used], (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB] FROM sys.dm_db_file_space_usage; use tempdb SELECT GETDATE() AS runtime ,(SUM(user_object_reserved_page_count) * 8)/1024 AS usr_obj_mb ,SUM(internal_object_reserved_page_count) * 8 AS internal_obj_kb ,(SUM(version_store_reserved_page_count) * 8)/1024 AS version_store_mb ,(SUM(unallocated_extent_page_count) * 8)/1024 AS freespace_mb ,(SUM(mixed_extent_page_count) * 8)/1024 AS mixedextent_mb FROM sys.dm_db_file_space_usage use tempdb SELECT session_id, SUM(internal_objects_alloc_page_count) AS NumOfPagesAllocatedInTempDBforInternalTask, SUM(internal_objects_dealloc_page_count) AS NumOfPagesDellocatedInTempDBforInternalTask, SUM(user_objects_alloc_page_count) AS NumOfPagesAllocatedInTempDBforUserTask, SUM(user_objects_dealloc_page_count) AS NumOfPagesDellocatedInTempDBforUserTask FROM sys.dm_db_task_space_usage GROUP BY session_id ORDER BY NumOfPagesAllocatedInTempDBforInternalTask DESC, NumOfPagesAllocatedInTempDBforUserTask DESC…
//
-
CDC in SQL Server
A brief description of SQL Server’s CDC- Change Data Capture, or CDC for short, was initially introduced in the SQL Server 2008 edition as a useful feature to track and record changes made to SQL Server database tables without requiring extra programming work. Prior to SQL Server 2016, a SQL Server database could only have…
//
-
Trigger and create a simple logon Trigger
Introduction- In order to connect using SSMS or client apps, SQL Server offers Windows and SQL authentication methods. For DBAs, security is always the first priority, particularly in the production setting. We can manage database access by giving users the minimal and suitable permissions for their task. SQL Server’s provide and DENY statements can be…
//
-
Normalization in SQL Server
Introduction- Normalization in SQL Server is a crucial concept in database design that aims to organize data efficiently, reduce redundancy, and maintain data integrity. It involves structuring relational databases to ensure that they meet certain criteria regarding the relationships between tables and the dependencies among their attributes. Normalization’s primary objectives are- Eliminating Redundancy- Redundant data…
//
-
OFFSET Command in SQL Server
Introduction- In SQL Server, the OFFSET command is used in conjunction with the ORDER BY clause to specify the number of rows to skip before starting to return rows from a query. This is particularly useful for implementing pagination in applications or when you need to skip a certain number of rows before fetching results.…
//
-
Extended Events in SQL Server
Introduction- Extended Events (XEvents) is a highly scalable and adaptable event-driven monitoring system that debuted with SQL Server 2008. It is intended to assist database administrators (DBAs) and developers in gathering extensive information on SQL Server behavior, such as performance issues, query execution, and system diagnostics. Extended Events’ architecture provides for minimum performance effect, making…
//
-
Correlated Subquery
Introduction- A correlated subquery is a SQL query in which the values of the inner query (subquery) are derived from the outer query. Essentially, the subquery is conducted many times, one for each row processed by the outer query. This makes correlated subqueries very useful for complex data retrieval scenarios, but they can also be…
//
-
Network actual bandwidth between servers.
Iperf: Download the tool from https://iperf.fr/iperf-download.php Keep it on both source and target server. Copy code iperf3 -s On server (server2){Cline machine}, run: Copy code iperf3 -c server1_IP
//