How to Resolve Error - The log or differential backup cannot be restored because no files are ready to rollforward

It was when one of my friend who got paused at his work when he received an error message as

Msg 8766, Level 16, State 1, Line 1

The log or differential backup cannot be restored because no files are ready to roll forward.
Msg 4523, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

Before moving on to resolve the issue, lets first check the status of the database from which the error is being generated. The database status could be either in ONLINE, OFFLINE, RECOVERY, RECOVERY PENDING, RESTORING, SUSPECT or EMERGENCY (Limited Availability).

Resolution:

In almost all the status of the database mentioned, there could be two possible scenarios of the database, where we can experience this error. While I was checking with my friend regarding the issue, he was stuck at a point when the database is live and running with critical data in the production server. However, nothing to panic at this stage. Let’s check for the two scenarios to resolve the issue.

 Solution 1:

In case, if you are trying to restore on a live database, which is already restored with “WITH RECOVERY” then there is no point of either applying Differential backup or T-LOG backup on an existing live database. If your latest Full and Differential backups are ready on your disk for the concerned database, then proceed to restore the Full backup “WITH NORECOVERY” and then rest data with the Differential backup “WITH RECOVERY”.

RESTORE DATABASE SQLArena_TEST

FROM DISK = 'C:\SQLArena_TEST_Full_Temp1.bak'

WITH NORECOVERY; -- with NORECOVERY means still, there are backups to be restored

RESTORE DATABASE SQLArena_TEST

FROM DISK = 'C:\SQLArena_TEST_Full_Temp1.bak'

WITH RECOVERY; -- with RECOVERY means there are no backups pending to be restored

NOTE: This can resolve only if your backups are consistent. 

Solution 2:

In case, you try the Solution 1 and fail to succeed, then try to take a full backup of the concerned database for both Full backup and Differential backup. Once, the Full and Differential backups are ready, try to restore Full backup with “NORECOVERY” and Differential with “RECOVERY”. This can for sure resolve your issue from erroring out.

BACKUP DATABASE SQLARENA_TEST

TO DISK = N'C:\SQLARENA\SQLArena_TEST_Full_Temp1.trn'

WITH NOFORMAT, STATS = 10 -- full backup

BACKUP DATABASE SQLARENA_TEST

TO DISK = N'C:\SQLARENA\SQLArena_TEST_Full_Temp1.trn'

WITH NOFORMAT, STATS = 10 -- differential backup

RESTORE DATABASE SQLArena_TEST

FROM DISK = 'C:\SQLArena_TEST_Full_Temp1.bak'

WITH NORECOVERY; -- with NORECOVERY for full backup

RESTORE DATABASE SQLArena_TEST

FROM DISK = 'C:\SQLArena_TEST_Full_Temp1.bak'

WITH RECOVERY; -- with RECOVERY for differential backup

Please do let me know if this doesn’t work as there are many other possible situations that could be solved in a different way.

Important Note: Please try not to test these on production databases, which can probably mess around. For testing purposed please try in test or development servers. 

You may also refer:
"Creating a COPY_ONLY Backups in SQL server"

LCK_M_XXX Wait Type in SQL Server

Locking is a structure used in the SQL Server Database Engine to retrieve and access a piece of data by multiple users at the same time. Using this mechanism can help the morality of the data being retrieved from the database.

Let’s look at some of the LCK_M_XXX types present in SQL Server. These are some of the basic lock operations encountered by me and these types of locks are probably handled by either DBAs or Database Developers. 

1. LCK_M_BU

Encountered when a request is waiting to acquire a Bulk Update(BU) lock. Basically, occurs when there is a need to protect database objects during bulk insertions.

2. LCK_M_IS

Encountered when a request is waiting to acquire an Intent Shared(IS) lock. Basically, occurs when there is a need to modify a database object the user, who will have the access to read but denied on updates.

3. LCK_M_IU

Encountered when a request is waiting to acquire an Intent Update(IU) lock. Basically, occurs when there is a need to write on a database object, which is already either in reading or modifying operation.

4. LCK_M_IX

Encountered when a request is waiting to acquire an Intent Exclusive(IX) lock. Basically, this is something like Intent Update, where there is a need to write on a database object, which is already either in reading or modifying operation.

5. LCK_M_S

Encountered when a request is waiting to acquire a Shared(S) lock. Basically, occurs when there is a need to read a request on a database object when already a write operation is on. 

6. LCK_M_U

Encountered when a request is waiting to acquire an Update(U) lock. Basically, occurs when there is a need to write on a database object, which already is either in reading or modify operation, something like LCK_M_IU. 

7. LCK_M_X

Encountered when a request is waiting to acquire an Exclusive(X) lock. Basically, occurs when there is a need to read/write on a database object, wherein a write operation is already on.

How to Resolve:

1. Keep all your transactions short and simple.

2. In case taking a backup/restore, try not to take a backup of the same database at the same time.

3. Use EXEC sp_who2 Active to check for blocking in SQL Server.

4. Try not to change the isolation to “Read Uncommitted”, because the data getting generated would be irrelevant. Setting Read Uncommitted can cause other statements to read rows that are modified but still not committed. 

5. Set up a job to monitor the queries that are running on your server for quite a long time. This can help in recognizing and killing the query in case the query is found junk.

6. Carry out an option to generate a timeout mail for the queries exceeded certain allowable limits.

7. Check for network connectivity for the server, as there are chances are transport-level error thereby causing the session to automatically go to rollback state.

8. Monitor the I/O alerts on your server, which could be probably due to the long-duration to fetch the logical file from the physical disk path. 

You may also refer:

"Simple way to identify blocking in SQL Server"

"How to Kill a SPID in SQL Server?"

SET QUOTED IDENTIFIER ON/OFF and ANSI NULLS ON/OFF in SQL Server

The SET QUOTED IDENTIFIER and ANSI NULLS are probably not used while writing a stored procedure, user function or triggers in SQL Server by beginners in DBA or SQL development. However, even without them, the results will be generated, but in case if you specify the SET QUOTED IDENTIFIER and ANSI NULLS, there could be fewer errors relating to quotes and NULL value comparisons respectively. Here are some reasons for setting these before to start to write stored procedures, functions or triggers.

SET QUOTED IDENTIFIER ON/OFF:

Setting the SET QUOTED IDENTIFIER ON can emphasize any data defined in double-quotes as either table name, column name, function name or procedure name. While defining the data in single quotes, it emphasizes data to be literal and can lead to an error. Let’s look at an example.

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE "TEST_TABLE" --Success

(ID int,

"Function_Dummy" varchar(100))

SET QUOTED_IDENTIFIER OFF

GO

CREATE TABLE "TEST_TABLE" -- Fails since it takes the data entered as literal

(ID int,

"Function_Dummy" varchar(100))

SET QUOTED_IDENTIFIER ON

GO

SELECT "Function_Dummy" --Fails since it takes the data entered as literal

SET QUOTED_IDENTIFIER OFF

GO

SELECT "Function_Dummy" --Success

ANSI NULL ON/OFF:

The ANSI NULL ON/OFF emphasizes how SQL Server compares the NULL values.

When set to ON, if the value NULL is compared with a = or <> sign, then the result produced would be false. In other words, any value comparing with NULL returns a zero.

SET ANSI_NULLS ON

IF NULL=NULL

PRINT 'SQL'

ELSE

PRINT 'ARENA' -- ARENA is a false statement if set to ANSI_NULL ON

When set to OFF, if the value NULL is compared with a = or <> sign, then the result produced would be true. In other words, any value comparing with NULL returns a NULL value itself.

SET ANSI_NULLS OFF

IF NULL=NULL

PRINT 'SQL'   -- SQL is a true statement if set to ANSI_NULL OFF

ELSE

PRINT 'ARENA' 

You may also need:
"Advantages of Using UNION ALL Over UNION in SQL Server"
"TRIM Function to Remove Blank Spaces From a String in SQL Server"
"Difference Between Truncate and Delete Command in SQL Server"
"Understanding the Importance of Database Schema in SQL Server"

Rearrange Column of Database Table in SQL Server

Many developers come up with a common question of whether a workaround can be made on changing the column structure. The straight answer to them is a “No” for an immediate fix. However, there are methods to rearrange a newly inserted column for an already existing table.

Probably in big organizations, there would be a requirement to create a new column for an already existing table. This new column could be as per the requirement linked to the applications created by the organization. In general terms, a new column added for an application in the middle of the table obviously makes no sense and is considered unethical for table modification. However, in case the situation is too worse to just proceed and implement a new column to the existing table, then here are some workaround.

Method 1: Drop and Recreate Table If No Data Available in the Table

Chances are high that a table would have been created and flushed with data and truncated. In such cases, if you find no data in the table where you need to add a new column then just drop the table and recreate with the new column added in the table structure.

Warning: Please do not drop the table in case you find data loaded in it. 

Method 2: Create a View for the Table

The simplest form is to create a view for the base table in whichever order you desire.

Method 3: Use GUI by Using Design

1. Expand the database where the table is located.

2. Right-click on “Tables” and select “Filter” to select the desired table.

3. Right-click on your table and click “Design”.

4. Here, you will be able to reorder the table structure based on your requirement.

Method 4: Create a New Table and Rename the Table by Dropping Old One

Create a new table say with “_1” with whichever table order of your choice. Once the table is created, import the entire data present in your table to a newly created “_1” table. After transferring the data to a new table, Rename the table with “exec sp_rename 'schema.old_table_name', 'new_table_name' “ command. This can produce a warning message, but nothing to worry about.

Note: Please do not use this method for critical tables.