Sql-server – Affect of Shrinking Database before Transaction log backup

sql servertransaction-log

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??

Best Answer

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.

Related Question