1

I am newbie in the world of Database administration, so excuse my stupid question :)

I have the following maintenance jobs scheduled through SQL Server Management Studio :

  1. Full DB Backup at 2 am, 10 am & 6 Pm daily (after every 8 hours).
  2. Transactional log backup at 2:30 am daily.
  3. Shrink job at 9:30 am & 9:30 pm daily.

I have run the Shrink job at about 1 AM manually, after that the scheduled Transaction Log Backup got failed with following error

Code: 0xC002F210 Source: Back Up Database (Transaction Log) Execute SQL Task Description: Executing the query "BACKUP LOG [PRODDB] TO DISK = N'S:\CAS Backup\Log..." failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup. BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query "ResultSet" property not set correctly parameters not set correctly or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 03:01:12 Finished: 03:01:12 Elapsed: 0.359 seconds. The package execution failed. The step failed.,00:00:00,0,0,,,,0

I need to understand was manually running Shrink Job before transaction log backup failed the later at 2:30 AM??

Improve this question

2 Answers 2

Reset to default
3

Your maintenance plan should not contain any shrink tasks anyway. These cause fragmentation by moving pages around the file and likely the database will just need to grow back to its original size shortly anyway.

But this isn't causing your error.

BACKUP LOG cannot be performed because there is no current database backup.

The transaction logs would need to be restored after a compatible backup has been restored. This is telling you that there is no such backup as one has never been taken since the database has been in full or bulk logged recovery mode.

It doesn't look like step 1 was run successfully for that database, or possibly someone temporarily changed the recovery mode for that database to simple and broke the log chain so you must take another full backup.

Improve this answer
2
  • Thanks Martin. I have checked the Shrink script, it does changes the recovery model to SIMPLE and then to FULL. But it was executed before Full DB backup . I have also checked that Full DB was successfull at 2AM
    – Prince Maini
    Commented Jun 20, 2016 at 8:49
  • @prince you must have missed something in your description or investigation as no full backup has been taken since it was last switched into full model.
    – Martin Smith
    Commented Jun 20, 2016 at 11:01
2

I have run the Shrink job at about 1 AM manually, after that the scheduled Transaction Log Backup got failed with following error

I am sure you are running shrink job by changing database recovery model to simple and then changing it back to full and then shrinking it. This would break log chain and you would need full backup again to start the log chain. That is why log backup is failing

Improve this answer
2
  • Thanks shanky. The shrink script does changes the recovery model to simple and then to full. But it was run at 1 am & full db backup was done successfully at 2 am. then why log backup was failed at 2:30 am.
    – Prince Maini
    Commented Jun 20, 2016 at 8:56
  • 1
    Log backup cannot just fail giving error you mentioned without any reason. Something is breaking the log chain are you sure you looked correctly at the errorlogs. Show me proof that full backup "completed" successfully at 2 AM you can get that information in errorlo. Plus any possibility someone manually ran the shrink job between 2 to 2:30 AM. Can you check that
    – Shanky
    Commented Jun 20, 2016 at 9:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.