Trace for Database Options?
-
based on another post, where someone wanted to know when the database compatibility had changed, i wanted to try and create a trace for whenever a database option had changed;Looks like i could use a pointer or two.
i don't see what i think i'm looking for when i'm trying to create the trace in profiler.
when i look for "Database" or "Server" trace events, i'm seeing justthese items:
i thought i'd be able to find and trace just twhen a database option changed, but i'm not so sure now.
for example, here's a decent but not necessarily comprehensive list of some database options:
SELECT
DATABASEPROPERTYEX(db_name(),'Collation') AS Collation,
DATABASEPROPERTYEX(db_name(),'ComparisonStyle') AS ComparisonStyle,
DATABASEPROPERTYEX(db_name(),'IsAnsiNullDefault') AS IsAnsiNullDefault,
DATABASEPROPERTYEX(db_name(),'IsAnsiNullsEnabled') AS IsAnsiNullsEnabled,
DATABASEPROPERTYEX(db_name(),'IsAnsiPaddingEnabled') AS IsAnsiPaddingEnabled,
DATABASEPROPERTYEX(db_name(),'IsAnsiWarningsEnabled') AS IsAnsiWarningsEnabled,
DATABASEPROPERTYEX(db_name(),'IsArithmeticAbortEnabled') AS IsArithmeticAbortEnabled,
DATABASEPROPERTYEX(db_name(),'IsAutoClose') AS IsAutoClose,
DATABASEPROPERTYEX(db_name(),'IsAutoCreateStatistics') AS IsAutoCreateStatistics,
DATABASEPROPERTYEX(db_name(),'IsAutoShrink') AS IsAutoShrink,
DATABASEPROPERTYEX(db_name(),'IsAutoUpdateStatistics') AS IsAutoUpdateStatistics,
DATABASEPROPERTYEX(db_name(),'IsCloseCursorsOnCommitEnabled') AS IsCloseCursorsOnCommitEnabled,
DATABASEPROPERTYEX(db_name(),'IsFulltextEnabled') AS IsFulltextEnabled,
DATABASEPROPERTYEX(db_name(),'IsInStandBy') AS IsInStandBy,
DATABASEPROPERTYEX(db_name(),'IsLocalCursorsDefault') AS IsLocalCursorsDefault,
DATABASEPROPERTYEX(db_name(),'IsMergePublished') AS IsMergePublished,
DATABASEPROPERTYEX(db_name(),'IsNullConcat') AS IsNullConcat,
DATABASEPROPERTYEX(db_name(),'IsNumericRoundAbortEnabled') AS IsNumericRoundAbortEnabled,
DATABASEPROPERTYEX(db_name(),'IsParameterizationForced') AS IsParameterizationForced,
DATABASEPROPERTYEX(db_name(),'IsQuotedIdentifiersEnabled') AS IsQuotedIdentifiersEnabled,
DATABASEPROPERTYEX(db_name(),'IsPublished') AS IsPublished,
DATABASEPROPERTYEX(db_name(),'IsRecursiveTriggersEnabled') AS IsRecursiveTriggersEnabled,
DATABASEPROPERTYEX(db_name(),'IsSubscribed') AS IsSubscribed,
DATABASEPROPERTYEX(db_name(),'IsSyncWithBackup') AS IsSyncWithBackup,
DATABASEPROPERTYEX(db_name(),'IsTornPageDetectionEnabled') AS IsTornPageDetectionEnabled,
DATABASEPROPERTYEX(db_name(),'LCID') AS LCID,
DATABASEPROPERTYEX(db_name(),'Recovery') AS Recovery,
DATABASEPROPERTYEX(db_name(),'SQLSortOrder') AS SQLSortOrder,
DATABASEPROPERTYEX(db_name(),'Status') AS Status,
DATABASEPROPERTYEX(db_name(),'Updateability') AS Updateability,
DATABASEPROPERTYEX(db_name(),'UserAccess ') AS UserAccess ,
DATABASEPROPERTYEX(db_name(),'Version') AS Version
So, has anyone ever create a trace for these events? what am i missing? which event(s) is/are it hidden under?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I believe event id 128 or 129 are what you are looking for. I don't know where they show up in Profiler. I found them in BOL under the sp_trace_setevent.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton -
Lowell!
DDL trigger. log it to a table.
Cheers!
Craig Outcalt
Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply