February 22, 2011 at 10:14 am
I'm trying to find out which login last modified a stored procedure in our TEST environment.
There are several logins with this kind of access, and, although I can get the last modified data from sys.procedures or nformation_schema.routines, I cannot seem to be able to get the login that did the modification.
Is there a way to get to this information?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 22, 2011 at 5:46 pm
Default trace.
Dan
February 22, 2011 at 8:26 pm
There is a standard report that you can use to see these changes. The report is the schema change history report - just run that and you will see all of the changes to the database.
Run a profiler trace when running the report and you can capture the statement being executed and modify it to your specific requirements.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 23, 2011 at 10:46 am
Jeffrey Williams-493691 (2/22/2011)
There is a standard report that you can use to see these changes. The report is the schema change history report - just run that and you will see all of the changes to the database.Run a profiler trace when running the report and you can capture the statement being executed and modify it to your specific requirements.
Thank you! That seems to do the trick.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 23, 2011 at 1:54 pm
drawlings (2/22/2011)
Default trace.Dan
Thanks, it turns out default trace is being used behind the scenes for the SSMS report.
I have the following query from the profiler trace:
select ObjectName
, ObjectID
, DatabaseName
, StartTime
, EventClass
, EventSubClass
, ObjectType
, ServerName
, LoginName
, NTUserName
, ApplicationName
, 'temp'
from ::fn_trace_gettable( '...\Log\log.trc', default )
where EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID = db_id() ;
The problem is that I am getting only today's events from query above. How can I set up the default trace to cover a longer period? (say 7 days)
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 23, 2011 at 2:26 pm
Based on the following link, there is no way to change the default-trace file size and number of log files; is that correct?
http://www.eggheadcafe.com/software/aspnet/32855710/default-trace-logs--increase-size.aspx
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
February 23, 2011 at 3:08 pm
how about just creating an audit ddl trigger.
Be sure to cover that with a server event in case the trigger is disabled.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 23, 2011 at 3:08 pm
Yes, that is correct - you cannot change the default trace.
I am a bit surprised that you don't have data any further back. I just checked one of my systems and I have information going back several weeks. You must have a lot of changes that are happening on that system that are recorded in the default trace to only have a days worth available.
What I would do is create an extract process to extract the data from the trace on a regular basis and import it to a reporting system. Since you only have a days worth of data, I would recommend at least twice a day.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 23, 2011 at 5:47 pm
My understanding is default trace rotates based on volume (20MB) and service restarts. Older files will be named log_n.trc, where n equals the number of rotations. Point your query to an older file.
...and thanks for all your assistance to others Mario.
Dan
February 23, 2011 at 6:47 pm
Marios,
At least I got your name correct this time, sorry about that.
So it appears we have a server rotating the trace file every 30 minuntes. That's an issue. Since maxfilesize is set when sp_trace_create fires, I doubt we can alter it. Also doubt the system health event session captures ddl changes. Will look into a solution, leaning towards extended events.
Dan
February 24, 2011 at 6:29 am
Thank you all for your responses.
This is a QA environment, so there are indeed a lot of changes happening all the time.
I will study all your answers in more detail and post further if I think of anything.
Certainly, persisting the data somewhere on a regular basis makes a lot of sense. I must admit, I never gave the default trace much thought until this post; it seems like a treasure trove of info!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply