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