SQL Server Transaction Log Corruption & Recovery

In the field of database, a transaction log is defined as a record of actions. Transaction log records all the actions performed in the database. It is an important component of any database because it helps in recovery of database during disaster strike to the database. In SQL server, Every database has a transaction log that list all the changes done in the SQL server database.

For smooth functioning of your SQL server database, you need to monitor and backup transaction log of your database periodically. Frequency of taking log backups depends upon your business requirement. Specially how much time you can afford your business down. It might be 30 minutes to daily. But I would suggest that a frequent transaction log backup minimize the chance of data loss.

Sometime back I faced transaction log backup issue. Backup job was failing continuously and through below error message:

2014-04-28 14:22:15.48 Backup Error: 3041, Severity: 16, State: 1.
2014-04-28 14:22:15.48 Backup BACKUP failed to complete the command BACKUP DATABASE willium. Check error log for detailed messages.

Above error message clearly indicates that backup was failing on the willium database. I checked errorlog for detailed information and got below error message:

2014-04-28 14:22:15.48 Backup detected log corruption in database willium. Context is FirstSector.LogFile: 34 ‘K:\Mount12\willium_Log\willium_Log.ldf’

VLF SeqNo:x18e44eVLFBase:x374000000LogBlockOffset:x3777c4200SectorStatus:2 LogBlock.StartLsn.SeqNo: x3020001LogBlock.StartLsn

As above error messag indicates log file of willium database had been corrupted.

Solution: I had performed below steps to remove corruption from transaction log file of willium database.

1. First of all, I changed the recovery model of willium database from Full to Simple. You can do this by Alter command. Alter database set Recovery Simple.
2. Then after shrinked the willium database log file.
3. Take a full backup of database and it was accomplished without any error message.
4. And finally changed the recovery model of willium database from Simple to Full.
5. Again take full backup of database and it was successfully accomplished.   

»»  Read More...

Resolving SQL server Error Message 5123: Cannot attach DB

To move a SQL database from one location to another location; first you need to detach the database from server and then re-attach it on another server. SQL server offers two methods for detaching a database: using SQL server management studio (SSMS) or using Transact SQL (T-SQL). And the detached database can be moved to another server and attached over new server.

Sometimes you may got following error message while attaching a detached database on another server

SQL Server -Error 5123: Cannot attach DB.

Cause: It is a very common error message that has been seen by most of the database administrator during attaching a database. Above error message generally occurs when attaching a database on the different location.

Resolution: Please check any of the following solutions that can fix your problem:

Verify the Name of database: It might be possible that you have misspelled the name of database or trying to attach the database from different location. Please verify the name and path of database and try again.

Rename the database file: Just rename the database file name and try to reattach the database from newly named database.

Disable the Anti-virus Software: If you have installed any anti-virus software on your PC then disable it and re-try to attach your database. It has been seen that sometimes anti-virus software locks the database.

Check the Backup Software: Check the Backup software. It also locks the database.

Allow NTFS permission security: It might be possible that you have not given NTFS security permission to everyone. So first all the permission for every one and try again.

Restart the SQL server services: First close all the services of SQL server and start the SQL server again. Most of the experts DBAs recommend restarting the SQL services after your defined working hours.

Use SP_Attach_DB Command: It is another method to attach a database. If you had tried GUI method to attach the database then try sp_attach_db command. Open a new query and use sp_attach_db command.

Change the Location of database file: Move the database to a new location (sub directory, root directory etc.) and try to re-attach the database from there.

Each of the above mentioned possible solutions can do the trick. Please try re-attaching the DB after each possible solution.

»»  Read More...

Recover Lost Data using Database snapshot in SQL Server

Database snapshot was firstly introduced in SQL server 2005 enterprise edition. It is a static view of SQL server database (source database) and read-only in nature. It resides on the same instance as its source database. When a source database is updated then its snapshots database are also updated. Database administrator can create database snapshot for all source database irrespective of their recovery model. It can be used for reporting purpose and quick recovery of SQL data. Here, you will see how to create and use database snapshot features.

How to Create a Source Database?

Database administrator can create a source database by executing following T-SQL command:

Use master

GO

CREATE DATABASE Name-Source-Database

GO

# Create a table in the source database

Create Table-Name in Name-Source-Database

Note: Replace the Name-Source-Database & Table-Name with the name of your source database & table name.

How to Create a Database Snapshot?

After the creating of source database, now database administrator can create the database snapshot for its source database by executing following T-SQL command:

Use master

GO

CREATE DATABASE Name-Source-Database_Snapshot

ON

(

NAME = 'Name-Source-Database',

FILENAME = 'D:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATAName-Source-Database_Snapshot.SS')

AS

SNAPSHOT OF Name-Source-Database

GO

Note: Database administrator can't create database snapshot with the help of SQL server management studio (SSMS).

How to Recover?

If database administrator has lost data from the source database due to any reasons then deleted data can be recovered by two methods: Full Backup & Database snapshot. Full backup is out of scope of this article so here you will see how to recover data by database snapshot.

Recovery with Database Snapshot

Database snapshot's Insert into...Select options helps in recovery of deleted record from the table.

Database administrator can restore source database with the help of database snapshot. To restore the database, execute the following T-SQL command:

USE master

GO

RESTORE DATABASE Name-Source-Database

FROM DATABASE_SNAPSHOT ='Name-Source-Database_Snapshot'

GO

Now use Insert into...Select option to recover the lost data.

INSERT INTO Name-Source-Database.dbo.record

SELECT * FROM Name-Source-Database_Snapshot.dbo.record

GO

Advantages of Database Snapshot:

• It helps in reporting purpose.
• It safeguards the database against user & administrator error.
• It is used for maintaining the historical data.
• It helps in creating multiple database snapshots of a given source database.
• Flexibility to create database snapshot for all source database irrespective of their sizes.  

»»  Read More...

How to Create a SQL server database with .MDF file only?

Are you new in SQL server and trying to create a new database for test or study purpose? Then you can do this with the help of mdf file only. MDF file is the primary database file of the SQL server. It stores all the user data & files. MS SQL server offers sample databases for the test purpose which available at MicrosoftSQL Server Product Samples &Database website.

For your referrence I have downloaded the AdventureWorks2008R2database from the codeplex website. This database has mdf file only.

Attach the database

You can attach the mdf file without ldf file by two methods:

1. Through SQL command
2. Through SQL server management studio

I will show you both the methods; firstly through SQL command.

Run below SQL command to attach the mdf file:

CREATE DATABASE YourDatabaseName ON

( FILENAME = N'C:\DATA\YourDatabaseName.mdf')

FOR ATTACH_REBUILD LOG;

GO

Above command will attempt to attach the database file and create an empty log file while attaching the database.

Note: Create a backup of your database to minimize the unavailability time of the database. As you are new in the SQL server so it may possible that you will perform a wrong command that makes your database unavailable.

Attach through SQL Server Management Studio: Perform below steps to attach a mdf file without ldf file using SQL server management studio.

1. Connect to the SQL server instance using SSMS.
2. From the Object Explorer, right click the Databases node and a drop-down menu will be appeared.
3. Now click on the Attach tab.
4. Click on the Add button from appeared dialog box.
5. Now a Locate Database Files dialog box will be appeared.
6. Click on the browse button to select your database MDF file after choosing .mdf file click on the OK button.
7. Now you will see your mdf file is selected but SQL server is unable to select your transaction log file because transaction log file is missing.
8. To attach .mdf file without transaction log file; select the log file and click on the remove button to remove it.
9. Now click on the OK button to attach the database file.
10. SQL server will create a transaction log file for you while attaching the database.

Suggestion: Make a habit to run DBCC CHECKDB command on your database. It will check and report the error message. If it reports any error message then re-run it with proper repair clause to repair the database.

»»  Read More...

Reason & Resolution steps for SQL server error 233

Are you getting below error message while connecting to the Microsoft SQL server?

If 'Yes' then don't worry!! You have reached at the right place.

After getting any error message; first of all check the error log for error message details recommended by experts. When you check error log; you may got given below details:

Cause: As per the above detail is explaining the maximum number of user connection is 1 and that is already reached the maximum value so you can't login to the MS SQL server. If you are system administrator of the database then you can increase the maximum value by using stored procedure (sp_configure) or connect with your system administrator if you can't increase the maximum value.

How to Increase Maximum Value: Follow below steps to increase the maximum value using sp_configure stored procedure:

• Open the SQL server management studio (SSMS).
• Now open a new query editor.
• Write down below query in the query editor.

sp_configure 'show advanced options', 1;

Go

reconfigure

Go

sp_configure 'user connections', 0

Go

reconfigure

Go

• Now press the execute button to execute the query.

»»  Read More...

Is your SQL server database backup OK? Check it now

A data can be any thing like employee attendance record, student marks detail, passenger list, customer list, booking history, and so on. Normally data backup is taken by database administrator but if you are working for any organization then it’s your responsibility to maintain the backup of your data which is a very crucial task for protecting data. Sometimes making a backup of your database is not enough for protecting data because backup can get corrupt too. So checking the integrity of your database backup is also essential. The same concept is applied in SQL server. SQL server backup helps in recovery of corrupt or deleted database.

How to Check your Backup?

If you have made backup of your SQL server database and did not checked its integrity yet then I will recommend you to check it now by below syntax:

RESTORE VERIFYONLY FROM DISK = ''

To illustrate it, I have made a backup of my database. I have made another copy of my backup & corrupted it intentionally.

Name of my perfect backup is perfectdatabasebackup.bak.

Name of corrupt database backup is corruptdatabasebackup.bak

First I will run RESTORE VERIFYONLY syntax on the perfect database backup and the syntax is given below:

RESTORE VERIFYONLY FROM DISK = 'C:\Database Backup\perfectdatabasebackup.bak'

Output: The backup set on file 1 is valid.

As the output is indicating that my backup is OK

Now I will run RESTORE VERIFYONLY syntax on the corrupt database backup and the syntax is given below:

RESTORE VERIFYONLY FROM DISK = 'C:\Database Backup\corruptdatabasebackup.bak'

Output:

Msg 3242, Level 16, State 2, Line 1

The file on device 'C:\Database Backups\corruptdatabasebackup.bak' is not a valid Microsoft Tape Format backup set.

Msg 3013, Level 16, State 1, Line 1

VERIFY DATABASE is terminating abnormally.

As the output is indicating that backup is not valid.

If you are trying to restore your database from backup and unable to do that then use RESTORE VERIFYONLY command on the database backup to verify it.

Conclusion: It is recommended to run RESTORE VERIFY command on SQL database backup after every successful backup. 

»»  Read More...

Unable to Restore SQL Database Backup: error 3205

When you try to restore a SQL server database backup created on SQL server 2005 instance to a different SQL server instance using SQL server management studio (SSMS), you may get following error message on your computer screen:

“An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Additional information:

Too many backup devices specified for backup or restore; only 64 are allowed.

RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)”

Cause: Above error message is appearing because you are trying to restore a SQL server backup created using SQL server 2005 into SQL Server 2000 instance.

Note: Microsoft suggests that backup created using higher instance of SQL server can't be restored into the earlier instance of SQL server.

For simple understanding:

Restore a backup from 2005 ---> 2000 - Not allowed

Restore a backup from 2008 ---> 2005, 2000 - Not allowed

Restore a backup from 2000 ---> 2005 - Allowed

Restore a backup from 2000, 2005 ---> 2008 - Allowed

Solution: To solve this problem, follow below steps:

1. First of all run 'SELECT @@version' command on the target SQL server instance.
2. Verify the version of target SQL server instance.
3. Upgrade the target SQL server.

   

»»  Read More...

What is the Recovery Model for Newly Created Database?

SQL server offers a facility to all database administrator for managing transaction log that is known as Recovery model. It will decide that you are going to manage your transaction log or not. There are three types of recovery model in SQL server: Simple, Full, & Bulk-Logged. If you have chosen simple recovery model for your database then you will be unable to make transaction log backup but you can make backup of transaction log in full & bulk-logged recovery model.

Recovery Model for Newly Created Database: Default recovery model of SQL server database is same like as recovery model of Model database. Model Database recovery model depends upon the edition of SQL server. If your model database is in 'Full' recovery model then your newly created database will also in 'Full' recovery model. To verify this lets create a database.

Simple create statement for creating a database:

CREATE DATABASE [New Test Database]

GO

# 'New Test Database' is the name of database.

Now run a query to select recovery model for 'model' & 'new test database'.

SELECT Name AS 'Databasename', recovery_model_desc

FROM sys.databases

WHERE name = 'Model' OR Name = 'New Test Database'

GO

Output:

	Databasename	recovery_model_desc
1	Model	Full
2	New Test Database	Full

As you can see that both the databases have same recovery model i.e 'Full'.

If you would like to change the recovery model for your database then you can change it with the help of SQL Server Management Studio (SSMS) or Transact SQL (T-SQL).

Is Your SQL Server Database Really in Full Recovery Model? It has been noticed that your SQL server database is not in full recovery model as conformed by 'Select recovery model' query. I am not telling anything wrong you guys! You can verify this at you end also as I have done.

As discussed in the above paragraph of article, you can make backup of transaction log if your database is in full recovery model & also confirmed by query that my newly created database (NEW Test Database) is in full recovery mode that means I am able to take transaction log backup right! But when I try to take transaction log backup got below error message:

BACKUP LOG New Test Database TO DISK = 'C:\DATA\Backup\New_Test_Database.BAK'

Got error message on my screen:

Msg 4214, Level 16, State 1, Line 1

Backup log cannot be performed because there is no current database backup.

Msg 3013, Level 16, State 1, Line 1

Backup log is terminating abnormally.

»»  Read More...

Getting Started with SQL Server Transaction Log

SQL server transaction log records of all the changes made on the database while the actual records are placed in a separate file. This file is known as transaction log file and the file extension for transaction log file is .ldf. Transaction log holds enough record to undo all the changes made on the database. It is the most critical component in the SQL server that can recover database if there is a system failure.

Note: Never delete or move transaction log unless you know the effect of this.

As we have seen above transaction log file uses a transaction log file (.ldf) to records all the changes made on the database and the size of this file grows as per every change on the database. SQL server offers two designed to counterbalance the size of transaction log: transaction log truncation & log file shrinking.

Transaction Log Truncation: SQL server automatically removes the entries from the transaction log file. The frequency of record removal depends upon the recovery model used in the database. If you have chosen full or bulk-logged recovery model for your database then SQL server truncates the transaction log file when you perform backup. In case of simple recovery model, sql server truncates the transaction log file at every transaction checkpoint.

Note: It is recommended to use full recovery model for production database.

Transaction Log File Shrinking: Transaction log truncation removes the entries only from the log file; it does not reduce the size of transaction log file. If you know there is a unused space in your transaction log file then you can shrink your log file and this process is know as transaction log file shrinking. Run below T-SQL command to shrink a transaction log file:

DBCC SHRINKFILE (, )

Sometime, you do not finish with above command. Try following code to shrink the transaction log file.

USE DatabaseName

GO

DBCC SHRINKFILE(, 1)

BACKUP LOG WITH TRUNCATE_ONLY

DBCC SHRINKFILE(, 1)

GO

Note: You can't shrink a transaction log file when your database is offline.

Backup Transaction Log: If you are using simple recovery model for your database then you can't backup the transaction log, if you still want to backup then you have to change the recovery model for your database first. Read my article “Twoways to Change the Recovery Model”. After changing the recovery model for your database, you can perform backup by following T-SQL command:

BACKUP LOG Your-Database-Name

TO Your-desired-location;

GO

»»  Read More...

Top 5 Disaster Recovery Solutions in SQL Server

Hey Guys!! How are you? I hope that you all are doing well. As we all know disaster can strike on the database in any form & any time. So it is better to well prepared to get rid from it. There are so many disaster recovery solutions available in SQL server, here we will go through top 5 of them.

Disaster: In term of SQL server, disaster is defined as an event that makes data unavailability or stop the normal functioning of database known as disaster. An event can appear in any form like hardware failure, virus attack, power failure, natural disaster, human errors, system hang, and many more. Some of the examples are given below:

• Online railway reservation halt: Unable to show seats availability.
• Data is unavailable: Think about big data retrieval companies like Google, Yahoo, and MSN.
• Order can not be placed: Think about online shopping sites like Amazon, where user wants to place an order but unable to do.

Disaster Recovery Solution: It is a process to overcome from data loss or business down after any disaster. Top 5 disaster recovery solutions in SQL server are as follows:

Top 1: High Availability & Disaster Recovery Solution: It is shortly known as HADR. This disaster recovery solution introduced in newly SQL server version 2012 & much expensive in comparison with other disaster recovery solutions. HADR offers high availability along with data recovery service if disaster has made business down.

Top 2: SQL Server Clustering Solution: SQL server clustering is one the best disaster recovery solution in the SQL server. In this technology; there are two or more physical servers that handle high availability and minimize the business downtime almost zero. When a physical server suddenly fails to perform the operation then all the tasks & responsibilities of failed server automatically take over by another physical server. It was introduced with Windows NT Server 4.0 enterprise edition.

Top 3: SQL Server Database Mirroring Solution: It is the fastest disaster recovery solution to bring database online in comparison of all other solutions. In addition to clustering solution that provides data protection at SQL server instance level, it provides data protection at database level. In database mirroring transaction log records are sent to the mirrored transaction log as soon as log buffer is written to the disk on the principle server. There is only one mirrored server for each principle server.

Top 4: SQL Server Log Shipping Solution: It works on the database & server levels so you can configure more than one database in this. Unlike database mirroring that supports full recovery model only, it supports full as well as bulk logged recovery models. It was also introduced in SQL server 2005 & does not support automatic failover. Log shipping failover time duration is larger than database mirroring failover time duration that can vary from minutes to hours (sometimes).

Top 5: 3^rd Party SQL Server Recovery Program: There are so many 3^rd party SQL server recovery programs available online like StellarPhoenix SQL recovery. Most of the recovery programs offer free demo version to download that shows the preview of your corrupt database. If you are satisfied with the demo then go forward to register your full copy from their official websites.

»»  Read More...