SQL SERVER - ERROR : 4214 BACKUP LOG cannot be performed because there is no current database backup

SQL SERVER – FIX : ERROR : 4214 BACKUP LOG cannot be performed because there is no current database backup

I recently got the below error.

Even thought my database is in full recovery mode when I try to take log backup I am getting following error.

BACKUP LOG cannot be performed because there is no current database backup. (Microsoft.SqlServer.Smo)

How to fix it?

Solution / Fix:

This error can happen when you have never taken full backup of your database and you try to attempt to take backup of the log only. Take full backup once and attempt to take log back up. If the name of your database is MyTestDB follow procedure as following.

BACKUP DATABASE [MyTestDB]TO DISK = N'C:\MyTestDB.bak'GOBACKUP LOG [MyTestDB]TO DISK = N'C:\MyTestDB.bak'GO

Testing connectivity to Microsoft SQL Server

Testing connectivity to Microsoft SQL Server without any tools installed

Often you'll find yourself logged into a web or application server and need to simply test that you can get to the SQL server you've unsuccessfully been trying to point your app at. Luckily there is a nifty trick built into Windows that allows you to save the day without having to install anything on your box – and it works on both client and server operating systems just as well.

Why would i ever need this?

Your app is having trouble connecting to SQL and you don't know what's wrong.

Is something wrong with your app?

Is it a network or firewall issue?

Is SQL even there…?

The "so easy it almost can't be true" solution

The guys at Microsoft have baked a SQL Connectivity tool right into Windows with the creation of Microsoft Universal Data Link files (*.UDL) – something designed for another purpose.

These were designed as a way to save SQL connection data for use in a whole range of applications and are available on pretty much every version of Windows since *eternity*.

They also provide an awesome way to test SQL Connectivity.

To make this work simply follow the bouncing ball:

Create a new file anywhere. For arguments sake create a new Text file on your desktop

Now rename that file and change it's file extension to TestDatabase.udl (take note of the file extension).

Select "Yes" when asked if you want to change the file extension.

This will give you a Data Link Editor that allows you to test the connection.

Pretty nifty in times of need!

SQL server job failed - Index rebuild failed error

SQL server job failed -  Index rebuild failed error

Error

Msg 2552, Level 16, State 1, Line 1 The index "Index_Name" (partition 1) on table "Table_Name" cannot be reorganized because page level locking is disabled

Solution

This error  can be resolved by enabling Page Level Locking on the index and then reorganize the index.

Enable page level locking on index using SSMS

As shown in the snapshot, select the checkbox Use page locks when accessing the index as highlighted and then save the changes before rebuilding the index.

To perform the same action(enable page level locking on index) using TSQL command

ALTER INDEX ON SET (ALLOW_PAGE_LOCKS = ON) GO