Sql Dirty Pages

Lazy writing , eager writing and checkpoint use asynchronous IO in writing pages to disk. The purpose of asynchronous IO is to release resources and for other tasks to progress.
i.e releases the thread to allow further threads.Usually this takes less than 10 ms – but under circumstances delays can occur.
When this occurs , you’ll see an error message IO Requests taking more than 15 sec to complete
IO writes in SQL Server are broken down into physical and logical. A Physical IO writes to disk. A Logical IO writes to the buffer cache – a basic SQL Server memory region
When a page is modified in the buffer cache and isn’t moved immediately to disk it’s marked as dirty.
Each time a change occurs on the dirty page a transaction log record is written. A dirty page is not moved from the buffer until the transaction log record is written to disk. The method used is write-ahead logging
The write-ahead logging method ensure the transaction log is written to disk and is fundamental to the Recovery Manager.
How is the dirty page written to disk?
Eager writing - Non-logged bcp, SELECT INTO, WRITETEXT,UPDATETEXT,BULK INSERT are examples of non-logged operations. To speed up the tasks , eager writing manages page creation and page writing in parallel. The requestor does not need to wait for all the page creation to occur prior to writing pages
Lazy writing is a process to move pages containing changes from the buffer onto disk. This clears the buffers for us by other pages.
Checkpoint writes all dirty pages to disk. SQL Server periodically commits a CHECKPOINT to ensure all dirty pages are flushed to disk.
Explicitly issuing a CHECKPOINT will force a checkpoint
Examples of events causing a CHECKPOINT
a) net stop mssqlserver
b) SHUTDOWN
c) ALTER DATABASE adding a file

If a CHECKPOINT fails , SQL Server must negotiate with the Recovery manager to restore to an earlier checkpoint

TUF(Transaction Undo File)

The transaction undo file contains modifications that were not committed on the source database but were in progress when the transaction log was backed up AND when the log was restored to another database, you left the database in a state that allowed addition transaction log backups to be restored to it (at some point in the future. When another transaction log is restored, SQL Server uses data from the undo file and the transaction log to continue restoring the incomplete transactions (assuming that they are were completed in the next transaction log file). Following the restore, the undo file will be re-written with any transactions that, at that point, are incomplete.

Hope its not too geeky.

Question: In my environment there is an issue with Log shipping destination file path, I've to change the file path on the destination, I've changed and LS copy is working fine and LS restore is failing because it is trying find the .tuf file on the old path which is not exists on the destination.

I don't want to do full restore for 30+ databases, so I'm trying to update the .tuf path on msdb on destination server but I couldn't find out the path details on any of the log shipping system tables. I knew the last restored file path details can be found on dbo.log_shipping_monitor_secondary , dbo.log_shipping_secondary_databases tables, updating these tables not helping to resolve my issue.

Where is the .tuf file path details on msdb?

Ans: The tuf file path is none other than the column backup_destination_directory in log_shipping_secondary on the primary server. And this will be automatically updated when you change the folder name in the LS setup page . But TUF should be available in the old directory when the next restore happens.

SELECT backup_destination_directory FROM dbo.log_shipping_secondary

If you are changing the path for this directory what SQL server does is , when the next restore happens it first tries to copy the TUF file from the old directory to new directory and then only go ahead with the restore operation . If SQL server cannot find the tuf file in the old directory or the old directory is itself lost – then there is no other way than reconfiguring your LS setup from scratch.

What is Undo File? Why it is required?

Undo file is needed in standby state because while restoring the log backup, uncommited transactions will be recoreded to the undo file and only commited transactions will be written to disk there by making users to read the database. When you restore next tlog backup SQL server will fetch the uncommited transactions from undo file and check with the new tlog backup whether the same is commited or not. If its commited the transactions will be written to disk else it will be stored in undo file until it gets commited or rolledback.