Sql-server – Trying to read a log file encrypted by TDE

Since your databases are in simple recovery mode checkpoint will be taken care of by database engine either automatically or when log file grow 70 % of its size, you can also give manual checkpoint . But still a long running transaction can hold log hostage and would not allow it to truncate even if you manually give checkpoint.

Log backup would not be possible in your case since recovery model is simple.

You can use Bulk logged recovery model to take benefit of bulk logged features supported.But non minimally logged transactions will be fully logged in bulk logged recovery model so be careful. Since you dont care about point in time recovery you can switch recovery model between full and bulk logged as required.

http://technet.microsoft.com/en-us/library/ms191244(v=sql.105).aspx

Laslty if your team is doing huge DML which generates lot of logs you would ask them to break there DML in chunks to avoid filling of logs. Index rebuild of huge table also generates massive logs.

Use truncate table to remove records from table( if you want to remove complete record) as it is mildly logged as compared to delete but dont use if table has Foreign key constraints and Indentity. http://msdn.microsoft.com/en-us/library/ms177570.aspx

Above points will surely give you some relief with huge logging.

Hope this helps

I will answer you question point wise like you asked

1. No its not necessary that LSN of differential backup would match the LSN of full backup. LSN is basically a stamp of last records that accessed the log. You should have also included DatabaseBackupLSN/checkpointLSN and then you will note that DatabasebackupLSN of differential backup will match the checkpointLSN of full backup and this will show to which full backup this differential backup relates to. Differential backup is also kind of full backup just taking backup of extents which has changed so LSN number might not be same as that of Full backup.

2. Neither Transaction Log backup nor LSN chain would is affected by Differential backup or Full database backup. Only log backup effects the chain.Proof you can see last LSN of 12 AM trn backup is same as first LSN of 1 AM transaction log backup.

Hope this helps