July 19, 2012 at 9:54 pm
What can i use to track changes made st server level
July 20, 2012 at 1:59 am
Server wide DDL triggers, traces, extended events etc etc.
Depends what you want to track.
Please elaborate furthre for a more consise answer.
July 21, 2012 at 6:30 pm
wanox (7/19/2012)
What can i use to track changes made st server level
Yes, please provide more info. Which version are you using? If you are using 2008 for any DML changes, you can use CDC(change data capture). This is a new feature that came with 2008 which can be replaced with DML triggers.
Regards,
TA.
Regards,
SQLisAwe5oMe.
July 22, 2012 at 5:50 am
Basic SQL Server auditing provided by default trace provides information such as when an object was altered/created/deleted can prove very useful and is already in place on every SQL Server instance ( assuming that you have not disabled it explicitly).
Following are the events captured by SQL Server default auditing trace that keeps running in the background:
Database: Data File Auto Grow
Database: Data File Auto Shrink
Database: Database Mirroring State Change
Database: Log File Auto Grow
Database: Log File Auto Shrink
Errors and Warnings: ErrorLog
Errors and Warnings: Hash Warning
Errors and Warnings: Missing Column Statistics
Errors and Warnings: Missing Join Predicate
Errors and Warnings: Sort Warnings
Full text: FT:Crawl Aborted
Full text: FT:Crawl Started
Full text: FT:Crawl Stopped
Objects: Object:Altered
Objects: Object:Created
Objects: Object:Deleted
Security Audit: Audit Add DB User Event
Security Audit: Audit Add Login to Server Role Event
Security Audit: Audit Add Member to DB Role Event
Security Audit: Audit Add Role Event
Security Audit: Audit Addlogin Event
Security Audit: Audit Backup/Restore Event
Security Audit: Audit Change Audit Event
Security Audit: Audit Change Database Owner
Security Audit: Audit Database Scope GDR Event
Security Audit: Audit DBCC Event
Security Audit: Audit Login Change Property Event
Security Audit: Audit Login Failed
Security Audit: Audit Login GDR Event
Security Audit: Audit Schema Object GDR Event
Security Audit: Audit Schema Object Take Ownership Event
Security Audit: Audit Server Alter Trace Event
Security Audit: Audit Server Starts And Stops
Server: Server Memory Change
The information mentioned above has been based on the following URLs \ References:
July 22, 2012 at 4:37 pm
wanox (7/19/2012)
What can i use to track changes made st server level
Do you mean, 'server' as in the physical server? as in platform including O/S? as in platform including O/S and RDBMS?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 22, 2012 at 8:04 pm
I want to audit the changes made on our servers by those with administrative rights in our group. This includes both the SQL and Windows environments. Need to know who changed something (create, delete, modify...ect), when did this change occur and what changed.
July 23, 2012 at 4:13 pm
If you want to see the database changes then you can set a trace for the users having admin rights.
Following are the ones you need to understand and use to set the trace.
sp_trace_create
sp_trace_setevet
sp_trace_filter
sp_trace_setstatus
July 24, 2012 at 6:46 am
Can traces also be used to monitor the windows environment?
July 24, 2012 at 8:24 am
As per my current understanding you cannot use profiler or SQL server side trace ( that is what the earlier post was suggesting) to monitor windows related events.
Further a server side trace ( unless it is very lightweight) is very taxing on the server and hence is used only sparingly for the purpose of troubleshooting.
Monitoring windows is totally a different cup of tea and probably out of discussion scope in this forum but then you could get very some basic information from the eventviewer logs.
SQL Server 2008 / R2 did introduce new DMVs that exposed a lot of useful operating system related information information but that may not be exactly what you are looking for.
Nevertheless I am listing the URL to excellent performance troubleshooting queries from Glenn Berry.
Glenn is simply the best when it comes to performance troubleshooting queries and i doubt if there a better consolidated list available anywhere else:
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply