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 |
|
2revup
Posting Yak Master
112 Posts |
 Posted - 2007-03-09 : 00:30:56
|
| i have created a backup that does once weekly BAK and every night it does TRN backup. This is in the same maintaince plan, the jobs appear as seprate jobs as they should. I have noticed my TRN is always failing, after looking into the event logs i see this.please note this is lastest time to early:4:22pmthis is the warning that the event log gives:SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'xxxxxxxx TRN'' (0xB87B2FA07E4FB74CA01A1D38134C9C4F) - Status: Failed - Invoked on: 2007-03-09 16:19:40 - Message: The job failed. The Job was invoked by User domain\Administrator. The last step to run was step 1 (Step 1).the next is 4:22pm18265 :Log backed up: Database: Staging, creation date(time): 2007/01/30(09:35:34), first LSN: 24624:14008:1, last LSN: 24624:14021:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'f:\program files\microsoft sql server\MSSQL\BACKUP\Staging\Staging_tlog_200703091622.TRN'}).last is 4:21pm18264 :Database backed up: Database: Staging, creation date(time): 2007/01/30(09:35:34), pages dumped: 93982, first LSN: 24624:14008:2, last LSN: 24624:14018:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'f:\program files\microsoft sql server\MSSQL\BACKUP\Staging\Staging_db_200703091618.BAK'}).So for some very strange reason its running a full backup then a Transactional, for this reason i would say its failing but why is it even doing this backup? the job is only for TRN????ThanksBRAD |
|
|
2revup
Posting Yak Master
112 Posts |
Posted - 2007-03-09 : 00:32:34
|
| by the way a manual transactional works like a charm. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-09 : 04:45:56
|
This, IME and IMHO, is the problem with the Maintenance Plan Wizard. its neigh on impossible to work out what the problem is when it goes wrong.Sorry, not much help, I know!  Couple of observations:"every night it does TRN backup"Why only once a night? Why not make then every 10-15 minutes? Given that you have the ability to make transaction backups I would want the shortest "reasonable" window of possible data loss ..."f:\program files\microsoft sql server\MSSQL\BACKUP\Staging\Staging_tlog_200703091622"I would recommend you store them in something more obvious than a "Program files\" folder path. Its a pet-hate of mine, and I don't understand why Microsoft defaults to putting the data, and backups, under Program Files ...Kristen |
|
|
|
2revup
Posting Yak Master
112 Posts |
Posted - 2007-03-11 : 17:44:36
|
| 'Why only once a night? Why not make then every 10-15 minutes? Given that you have the ability to make transaction backups I would want the shortest "reasonable" window of possible data loss ...'its a staging enviornment the data is not of a critical nature, and not that much is written to it.'I would recommend you store them in something more obvious than a "Program files\" folder path. Its a pet-hate of mine, and I don't understand why Microsoft defaults to putting the data, and backups, under Program Files ...'we have another 5 SQL servers and all are in the same config, to be quite honest this works for us so we are happy with it.Any others able to shed some light on this backup issue? |
|
|
|
2revup
Posting Yak Master
112 Posts |
Posted - 2007-03-11 : 17:51:35
|
| Its fixed northwind db still active and set to simple recovery mode rather then fullThanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-12 : 08:30:41
|
| "its a staging enviornment the data is not of a critical nature, and not that much is written to it."Sounds like changing the Recovery Model to SIMPLE and having a FULL backup weekly and a DIFFERENTIAL backup daily would be better than having full TLog-ing then - but that doesn't answer your original question either I'm afraid!Kristen |
|
|
|
|
|
|
|
|
TRN backups falling over... strange