Labels

admin (1) aix (1) alert (1) always-on (2) Architecture (1) aws (3) Azure (1) backup (3) BI-DWH (10) Binary (3) Boolean (1) C# (1) cache (1) casting (3) cdc (1) certificate (1) checks (1) cloud (3) cluster (1) cmd (7) collation (1) columns (1) compilation (1) configurations (7) Connection-String (2) connections (6) constraint (6) copypaste (2) cpu (2) csv (3) CTE (1) data-types (1) datetime (23) db (547) DB2 (1) deadlock (2) Denali (7) device (6) dotNet (5) dynamicSQL (11) email (5) encoding (1) encryption (4) errors (124) excel (1) ExecutionPlan (10) extended events (1) files (7) FIPS (1) foreign key (1) fragmentation (1) functions (1) GCP (2) gMSA (2) google (2) HADR (1) hashing (3) in-memory (1) index (3) indexedViews (2) insert (3) install (10) IO (1) isql (6) javascript (1) jobs (11) join (2) LDAP (2) LinkedServers (8) Linux (15) log (6) login (1) maintenance (3) mariadb (1) memory (4) merge (3) monitoring (4) MSA (2) mssql (444) mssql2005 (5) mssql2008R2 (20) mssql2012 (2) mysql (36) MySQL Shell (5) network (1) NoSQL (1) null (2) numeric (9) object-oriented (1) offline (1) openssl (1) Operating System (4) oracle (7) ORDBMS (1) ordering (2) Outer Apply (1) Outlook (1) page (1) parameters (2) partition (1) password (1) Performance (103) permissions (10) pivot (3) PLE (1) port (4) PostgreSQL (14) profiler (1) RDS (3) read (1) Replication (12) restore (4) root (1) RPO (1) RTO (1) SAP ASE (48) SAP RS (20) SCC (4) scema (1) script (8) security (10) segment (1) server (1) service broker (2) services (4) settings (75) SQL (74) SSAS (1) SSIS (19) SSL (8) SSMS (4) SSRS (6) storage (1) String (35) sybase (57) telnet (2) tempdb (1) Theory (2) tips (120) tools (3) training (1) transaction (6) trigger (2) Tuple (2) TVP (1) unix (8) users (3) vb.net (4) versioning (1) windows (14) xml (10) XSD (1) zip (1)

Find out who modified an object in SQL Server


SELECT --T.*, 'I', I.*, 'E', E.*
E.trace_event_id, E.category_id, E.name,
T.[path], T.start_time, T.last_event_time, T.event_count,
I.NTUserName, I.NTDomainName, I.HostName, 
I.ClientProcessID, I.ApplicationName,
I.LoginName, I.SPID, I.ObjectID, I.ServerName,
I.ObjectName, I.DatabaseName, I.SessionLoginName,
I.TextData, I.BinaryData
FROM sys.traces T 
CROSS Apply ::fn_trace_gettable(T.path, T.max_files) I 
JOIN sys.trace_events E On I.eventclass = E.trace_event_id 
WHERE T.id = 1 
AND E.name = 'Object:Altered' -- or 'Object:Deleted'  or ...
--AND ObjectName like '%AnObjectName%' -- <- filter for a specific object
ORDER BY T.start_time desc

Get columns list of an index


SELECT i.[object_id] as TableID, object_name(i.[object_id]) as TableName, 
i.index_id as IndexID, i.[name] as IndexName, 
i.is_primary_key, i.type_desc as IndexTypeDesc,
ic.column_id, c.[name] as column_name
FROM sys.indexes i
JOIN sys.index_columns ic ON i.index_id = ic.index_id
AND i.[object_id] = ic.[object_id]
JOIN sys.columns c ON ic.[object_id] = c.[object_id]
AND ic.column_id = c.column_id
-- optional filters:
WHERE object_name(i.[object_id]) = 'TableName'
WHERE i.[name] = 'IndexName'
WHERE c.[name] = 'ColumnName'

tempdb files and usage

-- space usage information for each data file in tempdb
SELECT *, 
(user_object_reserved_page_count)*8 as usr_obj_kb,
(internal_object_reserved_page_count)*8 as internal_obj_kb,
(version_store_reserved_page_count)*8 as version_store_kb
FROM sys.dm_db_file_space_usage
GO



-- tempdb files
SELECT * FROM tempdb.sys.database_files
GO

Troubleshooting tempdb Space Usage: