SQL Server – default trace FAQ
·
Has someone deleted a
table?
·
Are you trying to
track auto grow events? Problem scenarios such as Database auto grow and
slow database recovery
·
When did SQL memory
usage change? Read SQL Server Performance, memory pressure and memory usage for
SQL Server memory analysis
·
SQL Server
security changes?
The default trace has loads of information.
What is
TRACE?
A Trace allows you to track the specific and particular actions and event
that performed against a SQL Server databases. A TRACE provide vital and
valuable information and details for troubleshooting and monitor database
issues, problems and tuning database engine performance.
What type of data is available from the default trace?
Object creation, object deletion, error events, auditing
events, full text events
What sort of events does the default trace file capture?
--returns full list of events
SELECT * FROM sys.trace_events
--returns a full list of categories
SELECT * FROM sys.trace_categories
--returns a full list of subclass values
SELECT * FROM
sys.trace_subclass_values
How many
types of TRACE categories?
1. Cursors
2. Database
3. Errors
and Warnings
4. Locks
5. Objects
6. Performance
7. Scans
8. Security
Audit
9. Server
10. Sessions
11. Stored
Procedures
12. Transactions
13. TSQL
14. User
configurable
15. OLEDB
16. Broker
17. Full text
18. Deprecation
19. Progress
Report
20. CLR
21. Query
Notifications
How do I check default trace is on?
SELECT * FROM sys.configurations WHERE configuration_id = 1568
How do I enable default trace?
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'default trace enabled', 1;
GO
RECONFIGURE;
GO
How do I find the default trace file?
SELECT * FROM ::fn_trace_getinfo(0)
How can I list objects deleted in the last 24 hrs from a specific database?
SELECT *
FROM ::fn_trace_gettable('C:\Program Files\Microsoft
SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log_154.trc',0) tf
INNER JOIN sys.trace_events te
ON eventclass
= trace_event_id
INNER JOIN sys.trace_categories AS tc
ON te.category_id = tc.category_id
WHERE
databasename = 'AdventureWorks'
AND
objectname IS NOT NULL AND
te.category_id = 5 AND
te.trace_event_id =
47
--SQL server- 47 trace event it is for Oject Deleted.
