| Author |
Topic |
|
mikebird
Aged Yak Warrior
529 Posts |
 Posted - 2007-09-12 : 12:22:54
|
| I've been adding new databases recently, and obviously adding these to the backups. All is fine - the BAK files are being generated and 27 of the TRN files of 30, but 3 are still missing, in 3 empty directories! There is no reason for it. I have been over and over the settings for all databases and nothing ties this together. One database with no TRNs being created is our oldest one and has never had a TRN. The other two are recent and are similar - a live and a test instance - all the same data and setting - no difference - no TRNs. Other examples are similar data - Facilities Maintenance data at different sites in the country - test and live instances. It shouldn't matter what is in that data - just make the TRN of it! Some DBs don't generate it. What could be wrong?There are never any errors in the jobs. The transaction backups happen every 4 hours, and the BAKs once overnight. At present 3 of 30 TRNs are missing for unsubstantial reasons and I expect this to rise as I add more databases.Why can't a simple job make all the files it's meant to? Why does it get the hump with particular databases?Has anyone seen this before??Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-09-12 : 12:50:30
|
| And another to add. Check out the stored procedures that I wrote that replace maintenance plans and also do what they are told to do:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspxTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-12 : 14:16:31
|
| Are the 3 databases with missing TRN backups in SIMPLE, rather than FULL, Recovery Model?Do the Full backups work on those DBs?Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-09-12 : 22:50:54
|
| Check plan log file and sql server log for detailed reason. |
|
|
|
mikebird
Aged Yak Warrior
529 Posts |
Posted - 2007-09-13 : 05:45:25
|
quote:
Originally posted by Kristen
Are the 3 databases with missing TRN backups in SIMPLE, rather than FULL, Recovery Model?FULLDo the Full backups work on those DBs?All working fine for the BAKs, and all the rest of the TRNs are perfect for particular DBs. As I add more databases (and some older ones my predecessors added), some TRNs are selectively not produced. The General properties tab shows None for the Last Database Log Backup - (none ever for these DBs)Kristen
|
|
|
|
mikebird
Aged Yak Warrior
529 Posts |
Posted - 2007-09-13 : 07:18:05
|
| What is the difference between DIFFERENTIAL and TRANSACTION LOG backups? I know what they're defined as, but what is the actual difference between the two?When I changed from the usual transaction log style to differential (it makes BAK files) it works! The files are generated now.Yippeee!How do I do the restores, when the time comes?? |
|
|
|
Bharat
Starting Member
3 Posts |
Posted - 2007-09-13 : 08:54:49
|
| Hi Mikebird,You first check your model Database recovery model.Because all DB at first take the properties of model DB !! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-13 : 12:50:49
|
| "What is the difference between DIFFERENTIAL and TRANSACTION LOG backups?"DIFFERENTIAL backup saves all the pages in the database which are flagged as having changed (since the last Full backup). Thus over time Differential backups will get larger and larger, until the next Full backup is made. To restore you must first restore the Full backup and then the most recent Differential backup you require. There must have been NO additional Full backups made [between the Full and Differential that you are Restoring].TRANSACTION LOG backup saves the data in the LDF / TLog file, and then frees up that space in the file for reuse. The TLog stores data apertaining to each transaction that is processed, in order. So they can be large, depending on the amount of activity on the server. Restoring requires a Full backup file, then optionally restoring a Different backup file, and EVERY Tlog backup file since [up to the time you want to restore to]. So, you can restore an even earlier FULL backup, provided you then restore ALL SUBSEQUENT Tlog backups. This is helpful if a Full backup is damaged (provided that all the subsequent TLog backups are available and not damaged of course!). You can set a Date/Time for the TLog Restore to STOP AT. Thus you can restore up to a moment-in-time. This is not possible with Full/Differential backups - you can only restore them to the moment in time when the Full / Differential backups was created.Tlogs can be examined (with 3rd party tools) to discover exactly what took place. This can be useful in cases of fraud, hard-to-find bugs, or diagnosing human-error etc."You first check your model Database recovery model. Because all DB at first take the properties of model DB !!"Not sure what you mean there. If a UserDatabase is set to FULL Recovery model that is what it is, regardless of what MODEL database is currently set to.Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-09-13 : 14:05:01
|
| More than likely, the databases you are not getting transaction log backups for are in Simple Recovery mode.The SQL Server 2005 maintenance plan is smart enough to know if a database is in Simple Recovery mode, and not attempt to make transaction log backups for that database.This makes it easier to setup an "all databases" maintenance plan that will do transaction log backups only on the databases in full recovery mode.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-13 : 14:16:34
|
| "More than likely, the databases you are not getting transaction log backups for are in Simple Recovery mode."Well, FWIW the OP did say:"Originally posted by KristenAre the 3 databases with missing TRN backups in SIMPLE, rather than FULL, Recovery Model?FULL"Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-09-13 : 14:47:24
|
quote:
Originally posted by Kristen
"More than likely, the databases you are not getting transaction log backups for are in Simple Recovery mode."Well, FWIW the OP did say:"Originally posted by KristenAre the 3 databases with missing TRN backups in SIMPLE, rather than FULL, Recovery Model?FULL"Kristen
I didn't see that answer because of the odd way he posted it.He could still be wrong, of course. Wouldn't be the first time someone gave us some bad info without bothering to check, or without knowing what was really being asked.  "One database with no TRNs being created is our oldest one and has never had a TRN."Unless the transaction level is very low, that makes me think it is in simple recovery mode. Otherwise, the log file would be causing some problem due to uncontrolled growth by now.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-13 : 14:49:35
|
| Indeed, but I'm giving him the benefit of the doubt - given that he's from the UK!!Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-09-13 : 14:51:58
|
quote:
Originally posted by Kristen
Indeed, but I'm giving him the benefit of the doubt - given that he's from the UK!!Kristen
He could be wrong about that too.See my revised post about why I suspect simple recovery mode.CODO ERGO SUM |
|
|
|
mikebird
Aged Yak Warrior
529 Posts |
Posted - 2007-09-18 : 10:22:02
|
| Michael Valentine Jones, you did it for me. It all came together. I had never heard of the recovery model. Now I won't forget it. All the transaction logs being generated were the DBs set to full. I have no idea how the oldest DB on this server or the latest few I just created popped up with the model set to simple and both some with full - I didn't set these - but this was the problem. Now I've set these all to FULL and I thought it was PROBLEM FIXED. However now my Transaction Log Backup job just runs forabout 15 secs and completely fails - no TRNs generated whatsoever. At least it used to make about 30 of them about 4 times each day...Error isn't even logged. Pathetic. It shows big red cross at runtime but says check error logs for details. Details my ass. Logs show the right time I keep running it. Shows success! No TRN files generated! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-09-18 : 11:58:03
|
| You must run run at least one full backup after changing the recovery mode to Full before you can run a transaction log backup.CODO ERGO SUM |
|
|
|
mikebird
Aged Yak Warrior
529 Posts |
Posted - 2007-09-18 : 12:07:44
|
| You know the lot! I was guessing that actually. Seems about right. I'm glad the boss is never here. I'm really winging it here, and it's a great way to learn.I've always had a Mac at home and I really have a deep hate for Microsoft. I started with UNIX |
|
|
|
|
Transaction Logs - Some are created, some not!