Indexes deleted

Question

Indexes deleted

juliava

SSC-Addicted

Points: 442
More actions
August 25, 2021 at 3:51 pm

#3923931

Is it anyway to see if ever existed in table, and if they did, how/when they were deleted?
September 9, 2021 at 7:53 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113445 More actions · Answer 1

Once the index is dropped all remnants of it are removed from all system tables, so its like it never existed.

Do you have version control for your database schema, where you can see who checked in a drop or removal from the object definitions.

You could check the default trace, but it only stores a few MB of data then rolls over so depending how busy your server is you may find a drop very recent, but going back hours/days yeah not going to happen.

If it is of concern to see this stuff you'll need to put in some custom auditing to track it, XE sessions or ddl triggers something along those lines.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 2

juliava wrote:

Is it anyway to see if ever existed in table, and if they did, how/when they were deleted?

If it happened kind of recently, you could read the transaction log file backups to find the drops.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael L John One Orange Chip Points: 27044 More actions · Answer 3

There is also the Schema Changes history report under the Standard Reports in SSMS

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/

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 4

Be VERY aware that the Schema Changes History report is based on the "Default Trace". The default trace "file" is actually incredibly small. Even with the 6 they roll through, the life time expectancy of any such data can be listed in seconds on a busy machine (like ours, which is why I know this to be true). Just because something doesn't appear there, doesn't mean it didn't happen, even just seconds ago.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 5

Heh... good lord. It's been so long since I looked at that built in report that I'd forgotten a couple major (IMHO) flaws it has...

First, It does NOT report the schema of the objects. Only the object name.

That can still be a helpful but, again, the lifetime of any data in that report can be measure in seconds on a busy machine. Or worse...

Second, I'm not sure what the cause is but the report on our production box is currently reporting on what happened on May the third... 2018. That happens to be the day when we migrated to new hardware.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Michael L John One Orange Chip Points: 27044 More actions · Answer 6

Jeff Moden wrote:

Heh... good lord. It's been so long since I looked at that built in report that I'd forgotten a couple major (IMHO) flaws it has...
First, It does NOT report the schema of the objects. Only the object name.
That can still be a helpful but, again, the lifetime of any data in that report can be measure in seconds on a busy machine. Or worse...
Second, I'm not sure what the cause is but the report on our production box is currently reporting on what happened on May the third... 2018. That happens to be the day when we migrated to new hardware.

Hmm. I've not seen that one. Is it possible that the original default trace file is still in place on your box??

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/

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 7

Michael L John wrote:

Jeff Moden wrote:
Heh... good lord. It's been so long since I looked at that built in report that I'd forgotten a couple major (IMHO) flaws it has...
First, It does NOT report the schema of the objects. Only the object name.
That can still be a helpful but, again, the lifetime of any data in that report can be measure in seconds on a busy machine. Or worse...
Second, I'm not sure what the cause is but the report on our production box is currently reporting on what happened on May the third... 2018. That happens to be the day when we migrated to new hardware.
Hmm. I've not seen that one. Is it possible that the original default trace file is still in place on your box??

I'd have to say that's the probable reason but why should a consumer have to worry about that? Why doesn't the MS code take care of such an issue? Why did it become an issue to begin with?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)