October 23, 2009 at 8:09 am
Hi all,
I had my reorg indexes job fail last night, and while I know the cause of it (page level locking was disabled on a particular index), I'm confused because the same job worked flawlessly the night before.
When I asked the developers if there had been a change to the index, I was told no. So what I'm looking for is a way to get some history on the index.. is there a way to pull the last modified date and last modified user of an index?
Thanks!
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
October 23, 2009 at 8:14 am
Check the schema changes report for the database. That'll have anything that's in the default trace.
If that doesn't go back far enough (sometimes it doesn't go back very far at all), then you could set up a DDL trigger and a logging system. That won't get who changed it in the past, but it will log any changes in the future.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 23, 2009 at 8:25 am
Hmm.. it shows a change.. but I suspect (from the user and time) that the change is from either a re-org or a rebuild
IX_v_CompanyIndex
DDL OperationTime Login Name User Name
ALTER 10/20/2009 11:30:40 PMDOMAIN\MSSQLMSSQL
Thanks anywho.. guess i'll chalk it up to one of lifes great mysteries.
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
October 23, 2009 at 8:28 am
Object changes are captured by the default trace, so you should be able to find the information there.
http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/
October 23, 2009 at 8:32 am
MVJ: That's already been suggested. That's what the report pulls its data from.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 23, 2009 at 8:34 am
Michael Valentine Jones thanks.. I dont think its what I'm looking for.. I believe thats what the report comes from?.. but..
with that aside.. glad to have the link.. could be an invaluable method of looking things like log growths etc up in the future.
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
October 23, 2009 at 9:00 am
GSquared (10/23/2009)
MVJ: That's already been suggested. That's what the report pulls its data from.
I suggested this because you can find info in the trace that the report does not have, for example, the actual text of the DDL statement.
declare @path nvarchar(1000)
select @path=path from sys.traces where id = 1
select * into #t from fn_trace_gettable(@path,default)
select top 100 * from #t
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply