February 3, 2022 at 9:57 pm
I can see only when the indexes got created. However, I am looking is there a way to find who created the indexes?
February 3, 2022 at 10:24 pm
Pretty sure SQL doesn't log that information. In general, I have found that I don't care who created an index, I just care that the correct indexes are created. Only a handful of people have permissions to create indexes where I work, so if a new index pops up, I have a rough idea as to who created it and can have a quick meeting to discuss the index if needed. I find it is better to educate those who have the permissions to create indexes and use a "bad" index as a learning opportunity to the team rather than singling out an individual and making them feel embarrassed for being called out on a bad index.
Now, if you need to know who created them going forward, you can create a trigger for that (I believe). For all existing indexes though, I am not aware of any way to look up who created them.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 3, 2022 at 10:35 pm
I can see only when the indexes got created. However, I am looking is there a way to find who created the indexes?
No. You could create and add a database trigger to log who made schema changes.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 4, 2022 at 1:32 pm
Extended Events is our friend.
Take a look at the object_created event. You could filter by object_type and only see when indexes get created.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 4, 2022 at 4:25 pm
Extended Events is our friend.
Take a look at the object_created event. You could filter by object_type and only see when indexes get created.
That will only work after Extended Events have been set up, right ?
I don't think OP can find out about existing indexes.
February 4, 2022 at 4:37 pm
Yeah. There really isn't a way to do this after the fact.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 7, 2022 at 7:05 pm
What do you think about schema changes history? How long does it keep the history?
February 7, 2022 at 9:17 pm
What do you think about schema changes history? How long does it keep the history?
How long does what keep the history?
If you are talking about the report built-in to SQL Server, that uses the default trace. That keeps 5 rollover files of 20 MB each. So, in a lot of systems, not very long.
If you create your own extended events or database triggers, you can control that. With extended events, there is still a rollover that is configurable, but you could save the output to a table. The output of a trigger would go to a table.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
February 9, 2022 at 2:07 am
What do you think about schema changes history? How long does it keep the history?
If I recall correctly (and I might not), that's based on the default trace. If your machine is as busy as mine, that information can last less than a couple of minutes.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply