How to rebuild a log

ERROR : 9004 An error occurred while processing the log for database.
If possible, restore from backup.
If a backup is not available, it might be necessary to rebuild the log.

If you receive above error it means you are in great trouble. This error occurs when database is attempted to attach and it does not get attached. I have solved this error using following methods. Hope this will help anybody who is facing the same error.

Microsoft suggest there are two solution to this problem.

1) Restore from a backup.

· Create Empty Database with same name and physical files (.ldf and .mdf).

· Shut down SQL Server.

· Replace the files which you want to attach with this new empty database files.

· Start SQL Server.

· Database will be in suspect mode which means so far everything going as it should be.

· Next is to put database in emergency mode. ALTER DATABASE SET EMERGENCY will change the database status to emergency.

· A database which is in emergency mode can be repaired with allowing some data loss. DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS will do the task.

· In this process some of the data may be lost but database will in working condition with most of the data retrieved from log.

2) Rebuild the log.

· Create database using CREATE DATABASE FOR ATTACH_REBUILD_LOG.

· This may fix the problem right away if database which is attempted to be attach is shut down properly. If database is corrupt it will be not restored correctly.

· Next DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS should be ran to bring back the database operational. This may loose some data but it may bring back database in working condition.

· In this option as log is rebuilt SQL Server does not use log file to retrieve any data. In this process none of the log operation like transaction rollback will work.

To find installed instances from T-SQL

CreateTable #SQLInstances
( Value nvarchar(100),
InstanceName nvarchar(100),
Data nvarchar(100))

Insertinto #SQLInstances
EXECUTExp_regread
@rootkey ='HKEY_LOCAL_MACHINE',
@key ='SOFTWARE\Microsoft\Microsoft SQL Server',
@value_name ='InstalledInstances'


select instancename,Data from #SQLInstances

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.