August 12, 2011 at 9:48 am
We have SOX Audits going on and i was asked to show errologs,
there is option in sql server which says limit the number of errorlogs 6 to 99
i have unchecked this option long back but today when i went to see 6 month old log it doesnt have records for that date,Our sql server is restarting everynight
in off business hours ( i am mentioning this becoz this does same job as sp_cycle_errorlog)
i have log records from last year but entries for this year are missing,, so what settings should i do to make my production server show Archive#1 ............ Archive#N as Logs separated by individual days without any break in sequence or missing days
I am surprised i have all the logs from Sept2010 to Dec 2010, then i see Log Records For Month OF August 2011...In between everything is missing?? y is that???........
pls Help
August 12, 2011 at 11:30 am
Is some other process going in and clearing off space from that location by deleting files it thinks are archived?
Are your older logs in the same location as the current logs?
A log that hasn't been cutting off will continue storing data in the same log. For example, here are my logs:
Current - 8/11/2011 9:00:00 PM
Archive #1 - 6/23/2011 11:45:00 PM
Archive #2 - 6/23/2011 11:23:00 PM
Archive #3 - 6/17/2011 1:19:00 AM
The current log contains every entry from 06/23/2011 11:46 on to the current date. The date in the log file is the cut-off date, not the start date. So, you might be "missing logs" because the date you're looking for is hidden in another log.
Does this help?
August 12, 2011 at 2:19 pm
1. i have checked my logs thoroughly its not storing data of different dates in same log
2.yes my older logs are in same location, i.e by default LOG Folder with in programfiles sql server
Brandie Tarvin (8/12/2011)
Is some other process going in and clearing off space from that location by deleting files it thinks are archived?Are your older logs in the same location as the current logs?
A log that hasn't been cutting off will continue storing data in the same log. For example, here are my logs:
Current - 8/11/2011 9:00:00 PM
Archive #1 - 6/23/2011 11:45:00 PM
Archive #2 - 6/23/2011 11:23:00 PM
Archive #3 - 6/17/2011 1:19:00 AM
The current log contains every entry from 06/23/2011 11:46 on to the current date. The date in the log file is the cut-off date, not the start date. So, you might be "missing logs" because the date you're looking for is hidden in another log.
Does this help?
August 12, 2011 at 5:40 pm
First, even if you uncheck that box, I'm not sure SQL Server keeps an unlimited number of logs. I haven't found documentation and don't really want to restart an instance a hundred or so times.
In terms of your restarts, do you have errorlogs > 99? So errorlog.100, errorlog.101, etc?
It is possible that someone either removed logs or you didn't have restarts some days. That might account for missing dates, if you are going on dates.
If the logs are gone, there's nothing in SQL to help you get the data back. I might check with the groups that do file server backups, perhaps they have the older logs backed up, or you can check tapes to see if those files were created on the missing days.
Moving forward, I'm not sure I'd depend on this, and I'd also ask why you are restarting SQL each night? You shouldn't need to restart SQL Server 2005. I know people that run years without restarts, and some that only restart for patches every quarter.
In terms of the logs, you ought to have a process to archive these to a specific folder, and even name them with the date to prevent collisions.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 12, 2011 at 7:38 pm
we have a job which is scheduled to restart sql server everyday, so i am sure we cannot miss restarts,
we are not restarting sql just to create logfiles, we can o that by running dbcc recycle command too.
i got surprised by fact that in front of me another errorlog file is created and no one ran any recycle comand neither the server got restarted....why is it happening??....
i have only 15 log files shown in my errorlog......though i have unchecked the limit.....
is there any setting within system tables or registry which i have to checkin????
can someone help with SOP(standard operatng proc or any code ) to archive these logs in seperate folders as per date???? pls help!!!
Steve Jones - SSC Editor (8/12/2011)
First, even if you uncheck that box, I'm not sure SQL Server keeps an unlimited number of logs. I haven't found documentation and don't really want to restart an instance a hundred or so times.In terms of your restarts, do you have errorlogs > 99? So errorlog.100, errorlog.101, etc?
It is possible that someone either removed logs or you didn't have restarts some days. That might account for missing dates, if you are going on dates.
If the logs are gone, there's nothing in SQL to help you get the data back. I might check with the groups that do file server backups, perhaps they have the older logs backed up, or you can check tapes to see if those files were created on the missing days.
Moving forward, I'm not sure I'd depend on this, and I'd also ask why you are restarting SQL each night? You shouldn't need to restart SQL Server 2005. I know people that run years without restarts, and some that only restart for patches every quarter.
In terms of the logs, you ought to have a process to archive these to a specific folder, and even name them with the date to prevent collisions.
August 13, 2011 at 10:24 am
Jobs fail or don't run sometimes. There is no guarantee that a job restarted your server every day.
I have had a few people claim the limit is 99 logs which is just a few months. In that case, you need to script something to save these logs, or have your backup process pick them up and archive them.
If you have Powershell installed, you could use the copy files command[/url], or script something with VBScript that moves the files. There isn't a set command for doing this automatically, so something will need to be built.
This script (http://qa.sqlservercentral.com/scripts/Log+Shipping/67173/) is for backups, but you could modify it to do error logs. The thing you might need to do is rename the files.
By default, which I believe is the case if the SSMS box is not checked for log properties, only six are maintained. However the process doesn't delete other files, so it's possible that other files are just older and not being deleted.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 15, 2011 at 6:00 am
Calm down. We're trying to help. Panic doesn't do anything for you but enable you to ignore what we're saying.
Yes, error logs cycle on their own without a restart. It's a fact of life. It's based on the error log reaching a certain size (they are not truly unlimited in size). Re-read the stuff Steve has posted. It's good information on error logs. Also, read Books Online about error logs.
And remember for SOX audits that the auditors can want what they want, but if SQL Server doesn't provide, then you can't help them and it is NOT your fault. I've got through that myself, with the auditors asking for information that just isn't built into SQL Server or is built in such a way that no one besides Microsoft can access it.
If you document that SQL Server only provides X amount of information and give it to the auditors, then you're covering your backside. Don't let them convince you that SQL Server has some magic "forever" storage that it doesn't. You know the software. They don't. If they know of another IT department (in your company) with SQL Server that managed to get them the same information, have them put you in contact with that department so you can duplicate what that dept. did. Otherwise, document the lack, send it to them (CC your boss), and relax. You can only give them so much for SOX. Don't kill yourself trying to provide information that doesn't exist.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply