https://blog.sqlauthority.com/2009/06/27/sql-server-fix-error-17892-logon-failed-for-login-due-to-trigger-execution-changed-database-context-to-master/

Some important things to check after server migration

1. make sure tempdb files are configured per cpu, Initial and autoghrowth should have same size for all files mdf, ndf, log foles 2. Cost of threshold can be 50 3. max dop based on cpu

Audit login trigger

USE [AuditDB] GO /****** Object: DdlTrigger [AuditObjects] Script Date: 2/15/2023 12:24:21 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [AuditObjects] ON DATABASE FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION, CREATE_VIEW,ALTER_VIEW,DROP_VIEW,DROP_TRIGGER, CREATE_TRIGGER,ALTER_TRIGGER AS BEGIN SET NOCOUNT ON DECLARE @data XML DECLARE @HostName VARCHAR(500) SET @data = EVENTDATA() SET @HostName = HOST_NAME() INSERT INTO AuditDB.dbo.ObjectTracking(DatabaseName,EventType,ObjectName,ObjectType,LoginName,HostName,SqlCommand,date) VALUES( @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(500)'), @data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(500)'), @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(500)'), @data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(500)'), @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(500)'), @HostName, @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), GETDATE() ) END EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Default', @recipients = 'naresh.koudagani@frontpointsecurity.com', @subject = 'Object has been Altered/Created/Dropped! please look at Audtdb.dbo.ObjectTracking' , @body_format = 'HTML', @importance = 'High' GO ENABLE TRIGGER [AuditObjects] ON DATABASE GO

Get schema name , table name, row count and total size

SELECT s.Name AS SchemaName, t.Name AS TableName, p.rows AS RowCounts, CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id GROUP BY t.Name, s.Name, p.Rows ORDER BY 4 desc GO

How to delete Temp dB files when you get error The file cannot be removed because it is not empty.

USE [tempdb] GO ALTER DATABASE [tempdb] REMOVE FILE [temp6] GO Msg 5042, Level 16, State 1, Line 13 The file 'temp6' cannot be removed because it is not empty. USE [tempdb] GO ALTER DATABASE [tempdb] REMOVE FILE [temp6] GO that should help if not --DBCC FREEPROCCACHE('ALL') but test in test environments before doing in production

SQL SERVER did not detect newly added CPUs

hi , we had a situation where Windows team added 2cpu and it did not detect in SQL SERVER before:

EXECUTE RECONFIGURE now the change showuld show and no need of reboot After:

Audit login time, login name, hostname ,program name, client Etc Connection tracking and other info

1.USE [master] GO /****** Object: Table [dbo].[DBALogonAudit] Script Date: 1/12/2023 4:39:31 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[DBALogonAudit]( [SessionId] [int] NULL, [LogonTime] [datetime] NULL, [HostName] [varchar](50) NULL, [ProgramName] [varchar](500) NULL, [LoginName] [varchar](50) NULL, [ClientHost] [varchar](50) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO 2.---create trigger USE [master] GO /****** Object: DdlTrigger [LogonAuditTrigger] Script Date: 1/12/2023 4:26:12 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [LogonAuditTrigger] /* Creates trigger for logons */ ON ALL SERVER FOR LOGON AS BEGIN DECLARE @LogonTriggerData xml, @EventTime datetime, @LoginName varchar(50), @ClientHost varchar(50), @LoginType varchar(50), @HostName varchar(50), @AppName varchar(500) SET @LogonTriggerData = eventdata() SET @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') SET @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)') SET @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)') SET @HostName = HOST_NAME() SET @AppName = APP_NAME() INSERT INTO DBALogonAudit SELECT @@spid, @EventTime, @HostName, @AppName, @LoginName, @ClientHost END GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ENABLE TRIGGER [LogonAuditTrigger] ON ALL SERVER GO 3. put below inside a sql job --table should exist in a database Use master go DECLARE @xml NVARCHAR(MAX) DECLARE @body NVARCHAR(MAX) SET @xml = CAST(( SELECT @@SERVERNAME AS 'td','', s.name + '.' + t.Name AS 'td','', part.rows AS 'td','', CAST((SUM( DISTINCT au.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(18, 3)) AS 'td' FROM SYS.Tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id INNER JOIN SYS.Indexes idx ON t.Object_id = idx.Object_id INNER JOIN SYS.Partitions part ON idx.Object_id = part.Object_id AND idx.Index_id = part.Index_id INNER JOIN SYS.Allocation_units au ON part.Partition_id = au.Container_id INNER JOIN SYS.Filegroups fGrp ON idx.Data_space_id = fGrp.Data_space_id INNER JOIN SYS.Database_files Df ON Df.Data_space_id = fGrp.Data_space_id where s.name + '.' + t.Name Like '%DBALogonAudit%' GROUP BY t.Name, s.name, part.rows ORDER BY 2 DESC FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)) SET @body ='

Schemas Info, Please vist master.dbo.DBALogonAudit table to get more detailed Login Report

' SET @body = @body + @xml +'

Servername	Tablename	RowsModified	TotalSpaceinGB

' EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQLMAil', -- replace with your SQL Database Mail Profile @body = @body, @body_format ='HTML', @recipients = 'abc@abc.com', -- replace with your email address @subject = 'Audit report' ;

Serious performance issues after migrating SQL SERVER 2016 on a windows platform from windows Server 2012 to 2019

We migrated sql server 2016 from windows 2012 to windows 2019 and we had very bad perforamnce on queries.. we updated stats with full scan, rebuildd indexes after migration.. looked at Mx dop cost of threshold etc all loked good from our side. but no improvement and asked windows and network team to look what are rubnning new in 2019 vs 2012 and they found out that They have identified one potential issue that may be causing the performance drop between Server 2012 and Server 2019. We've noticed that Server 2019 does not properly disable Windows Defender when FireEye is installed, which may be causing additional resource contention from having two malware scanning platforms installed and running concurrently. Uninstalled windows defernder reboot and BOOM all queries are running as before in old production system

Backup Percentage completed, to check status and also to heck history of backups

SELECT command, s.text, percent_complete, CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), ' + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, ' + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time, CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), ' + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, ' + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go, start_time, dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG','ALTER DATABASE','DROP DATABASE','CREATE DATABASE') go SELECT top 150 bs.database_name, bs.backup_start_date, bs.backup_finish_date, bs.server_name, bs.user_name, bs.type, bm.physical_device_name FROM msdb.dbo.backupset AS bs INNER JOIN msdb.dbo.backupmediafamily AS bm on bs.media_set_id = bm.media_set_id where backup_start_date>'2023-01-06 09:00:00.000' order by backup_finish_date desc