Recover a database with no transaction log file - SQL Server 2005!
Have you ever been in a situation that one of the database has been giving hard time!
It may be the times that every DBA might go through this path without realising the root cause of the issue. The issue could be out of space on the drive where that database is located and further you might see the errors (may familiar to you):
2008-11-08 20:30:12.06 spid95 Error: 17053, Severity: 16, State: 1.
2008-11-08 20:30:12.06 spid95 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf: Operating system error 112(error not found) encountered.
2008-11-08 20:30:12.10 spid95 Error: 1101, Severity: 17, State: 10.
2008-11-08 20:30:12.10 spid95 Could not allocate a new page for database 'TEMPDB' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
with further messages such as:
2008-11-09 23:00:00.25 spid53 Error: 9002, Severity: 17, State: 2.
2008-11-09 23:00:00.25 spid53 The transaction log for database 'DatabaseName' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Obviously you will attempt to add more space to the transaction log file in order to avoid the error, say if there is no free space available on the drive then out of panic you attempt to shrink the log size that has resulted to database corruption! Not only that there is a further issue of backups for this database and you need to recover to the point-in-time!
Here is how you can get back the database causing such an issues, before I go on to the steps make sure you have practiced this on your development environment and not to attempt directly on the live systems.
For the obvious reasons if you ever lose the drive that contains your log file, your database will become suspect and will stop working. Even though if you attempt to view the properties from SQL Server Management studio and you will notice that you cannot expand the database. If you do, you will get an error message.
At this point no hope
on restoring say if the data is x days old which will lead to the data
loss since your last backup, the way to recover the data is to detach the database and reattach it without the log. SQL Server 2005 does allow you to do this. You can reattach the database by following these simple steps:
- First, you need to detach the suspect database:
The “detach database” dialog appears, click ok.
You might get an error just ignore it and the database will be detached. You will have to refresh the database list to remove your database from the list.
- Right-click on Databases and select Attach
The “attach databse” dialog appears. Click on the “Add” button on locate your mdf file.
- Under database details, select the .LDF file and click the Remove button.
By doing this, you are telling SQL Server to create a new log file.
- Click OK. The database will be attached with a new logfile.
BINGO!
The database is back online for you to continue your work, at this point make sure to take full backup of this database and ensure to monitor the disk free space regularly.