Category: SQL Server
-
Scan SQL Server Error Log
Scan SQL Server Error Log create table #t_789(Log_date datetime,Process_Info Nvarchar(1000),Txt Nvarchar(3500) ) insert into #t_789 exec sp_readerrorlog; insert into #t_789 exec sp_readerrorlog 1; insert into #t_789 exec sp_readerrorlog 2; insert into #t_789 exec sp_readerrorlog 3; select * from #t_789 where Txt like ‘%error %’; Scan Current Error Log –create table #t_789(Log_date datetime,Process_Info Nvarchar(1000),Txt Nvarchar(3500) ) insert into #t_789 exec sp_readerrorlog; select Log_date,txt from #t_789 order by 1 desc ; truncate table #t_789
-
Database Maintenance
Index Fragmentation Info –All Indexes select object_schema_name(ps.object_id) as ObjectSchema, object_name (ps.object_id) as ObjectName, ps.object_id ObjectId, i.name as IndexName, ps.avg_fragmentation_in_percent, ps.page_count from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) ps inner join sys.indexes i on i.object_id = ps.object_id and i.index_id = ps.index_id where avg_fragmentation_in_percent > 10 and ps.index_id > 0 and page_count >100 order by page_count desc –Specific Index select object_schema_name(ps.object_id) as ObjectSchema, object_name (ps.object_id) as ObjectName, ps.object_id ObjectId, i.name as IndexName, ps.avg_fragmentation_in_percent, ps.page_count from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) ps inner join sys.indexes i on i.object_id = ps.object_id and i.index_id = ps.index_id where ps.index_id > 0 and i.name like ‘Index_Name‘ order by 5 desc —————————————————————- ————————————–> use Database_Name ————————————– Declare @TBname nvarchar(255), @schema nvarchar(255), @DB_NAME nvarchar(255), @SQL nvarchar(max) DECLARE @db_id SMALLINT; DECLARE @object_id INT; ————————————–> SET @DB_NAME =…
-
Missing Indexes in a DB
–It runs at DB Instance Level. SELECT CONVERT (varchar(30), getdate(), 126) AS runtime, mig.index_group_handle, mid.index_handle, CONVERT (decimal (28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) ) AS improvement_measure, ‘CREATE INDEX missing_index_’ + CONVERT (varchar, mig.index_group_handle) + ‘_’ + CONVERT (varchar, mid.index_handle) + ‘ ON ‘ + mid.statement + ‘ (‘ + ISNULL (mid.equality_columns, ”) + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ‘,’ ELSE ” END + ISNULL (mid.inequality_columns, ”) + ‘)’ + ISNULL (‘ INCLUDE (‘ + mid.included_columns + ‘)’, ”) AS create_index_statement, migs.*, mid.database_id, mid.[object_id] FROM sys.dm_db_missing_index_groups mig INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle WHERE CONVERT (decimal (28, 1),migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10 ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC
-
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…