| Author |
Topic |
|
sqlpal2007
Posting Yak Master
200 Posts |
 Posted - 2011-12-13 : 15:52:49
|
| Hello All,How can I trace when the SQL server logs are purged? Is there any trigger event or system sproc which I can run manually to purge the SQL Server logs and then record the date stamp for audits?Thanks for help, |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sqlpal2007
Posting Yak Master
200 Posts |
Posted - 2011-12-13 : 16:07:00
|
| The auditors say they wanted to track the event when the SQL purges event logs. I have no idea what that means. My guess is neither do they. How would you infer this Tara so I can propose auditors? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sqlpal2007
Posting Yak Master
200 Posts |
Posted - 2011-12-13 : 21:47:30
|
| Thanks Tara.I have a job which already captures falied login attempts using sp_readErrorLog. Also, I have created a SQL job automated to run every 1st day of the month to create a new error log file which calls sp_cycle_errorlog system sproc. Do you think this sproc purges one oldest file before creating new one? If yes, how do I trace the purge event and dump it in audit table? Please advice? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-12-13 : 22:50:40
|
| Well it doesn't purge, it rolls it over. So it creates a new log file and archives the old one. Depending upon your settings, you will likely have several archive files. It's configurable. The auditor is probably interested in this because you can cycle the error log multiple times to erase certain activities. So if you have your system configured to have 5 archive files, the malicious person could cycle the error log 6 times in a row.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
sqlpal2007
Posting Yak Master
200 Posts |
Posted - 2011-12-13 : 23:41:56
|
| By cycling the log multiple times how would I erase entries from there? Wouldn't the lod log gets archieved?the auditors. So are u saying all the entires since the server was built are still there in one of the error log file..I am still not clear on this. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-12-14 : 01:16:50
|
| I am not saying that at all. Look at your error log directory. You'll see just a few archive files. They only go back a few reboots or a few cycling of the logs. You can even open these up in SSMS to see what dates they go back to. If you reboot monthly like we do for MS security patches, you can only go back a few months. And if you cycle the logs, you can go back even less than that.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-12-14 : 09:03:57
|
quote:
Originally posted by sqlpal2007
By cycling the log multiple times how would I erase entries from there?
Lets say that your system is configured to keep the 5 most recent log files.I do something malicious (which is recorded in the log file) then cycle the log file 6 times. You no longer have any trace of what I did ...That may be what your auditors want protecting against. |
|
|
|
sqlpal2007
Posting Yak Master
200 Posts |
Posted - 2011-12-14 : 09:11:50
|
| Thanks guys.I got it now. How do I capture recycling of log file? I have to dump the LogDate to a audit table for this event and design report so auditors can run it on demand.Any idea? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Purge of SQL Server logs