Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gauravkits
Starting Member
11 Posts |
 Posted - 2007-10-30 : 06:52:28
|
| Hi Everyone,I am new to this forum and new to SQL server too. Can anybody tell me about the recovery interval.Its usually for the recovery of the database uptill last 30-60 seconds. The default value is 0 for this to happen in real project scenarios. But we faced a problem recently when the whole day transanctions data was lost. Can anybody share a light on recovery interval to work effectively and if there can be anything added from your side. Waiting for a response.Gaurav Arora |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-30 : 07:49:33
|
| To recovery to a point in time you need to be using the FULL Recovery Model (not SIMPLE Recovery Model), and you need to be making Transaction backups at intervals of, say, 10-15 minutes.Are you already doing that?Kristen |
 |
|
|
gauravkits
Starting Member
11 Posts |
Posted - 2007-10-30 : 08:01:00
|
| Hi Kristen,Ya we are already using FULL RECOVERY model and still that problem occured about the data lost which i wrote in my last mail.Gaurav Arora |
|
|
|
gauravkits
Starting Member
11 Posts |
Posted - 2007-10-30 : 08:07:37
|
| Hi Kristen,Adding to the previous thing...We are taking transanction backups every one hour. Can you suggest for a possible root cause of this problem.Gaurav Arora |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-30 : 10:22:20
|
| "we faced a problem recently when the whole day transanctions data was lost"OK, so you ahve transaction backups every hourSlightly separate issue: are you happy to lose a hours work? Why not increase frequency of TLog backups to 10 minutes? There is no cost, total size of the backup files will be about the same, there will just be more, but smaller, files, and you will have more control over the size of the LDF file as a benefit too.OK, so how did you lose a days transaction? Did you restore the Full backup and then all the Tlog backups for the day up to the time of the disaster? Or were some Tlog backups missing?Kristen |
|
|
|
gauravkits
Starting Member
11 Posts |
Posted - 2007-10-31 : 06:25:48
|
| Thanks for you advice Kristen. But, the point is that i am not asking about the FULL Recovery model or SIMPLE Recovery model or the transaction backups. I am asking about a database server parameter which is Recovery interval. It is set to 0(ideal value) for checkpoints to occur every 30-60 seconds but for our data lost there was no checkpoint for the whole day and we had lost full day's data. If you can give any view in this regard then that would be great. Thanks in advance.Gaurav Arora |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-01 : 08:02:14
|
| I did understand your question, but I am struggling to believe you had no checkpoint for the day, which is why I asked for details of what your recovery process was to see if that shed some light on how a days transactions went missing.Kristen |
|
|
|
gauravkits
Starting Member
11 Posts |
Posted - 2007-11-01 : 09:44:46
|
| Thats what the point is...The recovery interval didnt work properly. We dont know why it happened?Gaurav Arora |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-11-01 : 10:15:27
|
| And I don't know what you did during your restore, because you aren't telling me. Never mind. |
|
|
|
|
|
|
|
|
Recovery interval