October 20, 2004 at 8:21 am
When we view the SQL Server log via SQL Server Enterprise Manager/Managment, we will see -
-> Current (date range)
-> Archive # 1
-> Archive # 3
-> Archive # 4
-> Archive # 5 ...
Would like to know how SQL Server determine when to archive, since we realize that the date range is not the same between different achives. Also, is this SQL Server log different from the transaction log?
Any articles explain this?
Thank you!
October 20, 2004 at 9:37 am
Yes they are different. The logs that your are viewing through Enterprise Manager are the activity logs for the SQL Server. This includes startup messages, status of scheduled jobs completion, failed logins (if you're auditing them) and other general informational messages regarding the server activity. It is, in a sense comparable to the Windows Server Event logs. Each time SQL Server is restarted an archive containing the old information will be created and a new log started and this explains the different date ranges you are seeing. You can configure the system to keep additional logs if you want.
These logs ARE NOT to be confused with Transaction logs which are used by the individual databases to record data transactions and these can vary greatly on their size and activity depending on the database and the recovery model chosen. Each database will have its own transaction log. According to SQL Server Books On Line(BOL):
"The transaction log is a serial record of all the transactions that have been performed against the database since the transaction log was last backed up. With transaction log backups, you can recover the database to a specific point in time (for example, prior to entering unwanted data), or to the point of failure."
I strongly suggest that you read up on Transaction Logs and the Backup and Recovery topics in BOL as a starting point. You will find this to be an excellent resource. More information is also available on the Microsoft Web Site. Hope this helps.
My hovercraft is full of eels.
October 23, 2004 at 5:38 am
SQL Server archives your errorlogs every time you stop/start SQL server or when you issue the sp_cycle_errorlog command. Within these errorlogs (by default there are 6) you will see an entry for each transaction log backup among other things such as error messages, ect..
If you want to have more errorlogs you need to right-click on SQL Server agent in Enterprise Manager and change the value from 6 to your desired value.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply