SQL Job Error: The log for database is not available. Check the event log for related error messages

The log for database ‘name_here’ is not available.

SQL BLOG Here is the story: you wake up one morning, and as you read your email, you come across a SQL Server notification about ”SQL Server Alert System: ‘Severity 021’ occurred on Server1”. Sounds bad. How bad is it? Do you panic? Not yet.

Step 1: understand what the error means. After a short consultation with the web you realize that the error is related to the corrupt or missing log file of the database, or a malfunctioning hard drive.

Step 2. Is there a backup? NO?! Since the database log is not readable / accessible, it only makes sense that there is no backup, and since you delete the backups older than 2 days, the last working backup of the database is gone. Do you panic now? A little. Step 3. Check the SQL Server error log for additional messages. All you find is ”Error: 9001, Severity: 21, State: 1.” and ”The log for database ‘name_here’ is not available. Check the event log for related error messages. Resolve any errors and restart the database.” Cryptic, huh.

Step 4. You try to backup the database. No go, it is expected.

Step 5. You try to detach the database. You know that SQL Server re-creates the log file if you try to attach an existing database and if you do not point to a specific log file. Good plan, but the detaching of the database does not work. The database is flagged as ”not ready” and you cannot detach it. NOW do you panic? No, because you have a lot more tricks up your sleeve.

Step 6. Query a table in the database. Ha! It works. The database is ”not ready”, but it is not ”Suspect” yet. So, you can actually get the data out…

Step 7. Right-click on the database, go to ‘Tasks’ and then to ‘Copy Database’. The wizard starts, you follow it, and through it you create a SSIS package which helps you create a new database where all the data from the ‘not ready’ database is extracted. Step 8. Drop the ‘not ready’ database, rename the newly created one, move the files, sprinkle with sugar, bake for an hour and serve warm… 🙂 (yes, the last one was a ”spur-of-the-moment-pure-daydreaming” 🙂 ) So, here is how you go around to fix the Error 9001, Severity: 21. Note that there is much more to do: check why the error occurred in the first place, check the hard drive integrity and so on.

And remember: there is no single ultimate solution for a problem. Bottom line: If you would like to have automated email notifications from your SQL Server(s), or if you would like to have proper backups setup, or if you would like to get help with solving any SQL Server related errors – then you should contact us. We are here to help.

SQL Error: Event viewer id 10016 – The application-specific permission settings do not grant Local Activation permission for the COM Server {EE4171E6-C37E-4D04-AF4C-8617BC7D4914}

I was running SQL Job as user SHELTER\SSIS_Admin and I saw an error in the event viewer–>Windows Logs–>System

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{806835AE-FD04-4870-A1E8-D65535358293}
and APPID
{EE4171E6-C37E-4D04-AF4C-8617BC7D4914}
to the user SHELTER\SSIS_Admin SID (S-1-5-21-1721117035-1253583819-976960199-28417) from address LocalHost (Using LRPC) running in the application container Unavailable SID (Unavailable). This security permission can be modified using the Component Services administrative tool.

Solution

1. Click Start, click Run, type dcomcnfg in the Open box, and then click OK.
2. Expand Component Services, expand Computers, expand My Computer, and then click DCOM Config.
3. Right-click EE4171E6-C37E-4D04-AF4C-8617BC7D4914, and then click Properties.
4. Click the Security tab.
5. Under Launch and Activation Permissions, click Edit.
6. In the Launch Permission dialog box, click Add.
7. In the Select Users, Computers, or Groups dialog box, type SHELTER\SSIS_Admin, click Check Names, and then click OK.
8. In the Permissions for UserName list, click to select the Allow check box that is next to Local Activation, and then click OK two times.

SQL Error: Database restore error, Cannot open backup device, Operating system error 5(Access is denied.).

Error

Msg 3201, Level 16, State 2, Line 1
Cannot open backup device ‘D:\i5-share\SQL\AdventureWorks2012_Database\AdventureWorks2012_Data.mdf’. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

 

Solutions

Suggestion 1

From the error message, it says there’s an error when validating the target (c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DataLabTables.mdf) of your restore operation.

That sounds like:

a) that file already exists (because you’ve already restored it previously) and is in use by SQL Server

or

b) that directory doesn’t exist at all

In your question, you mentioned you created a backup for that table – that’s not how SQL Server backups work. Those backups are always the whole database (or at least one or several filegroups from that database).

My hunch is: you’ve already restored that database previously, and now, upon a second restore, you didn’t check the checkbox “Overwrite existing database” in your restore wizard – thus the existing file cannot be overwritten and the restore fails.

Suggestion 2

Sounds like the service account that SQL Server runs under does not have permission to folder C:\Clients\SQLitis.

When SQL Server was installed, it hopefully was set up to use a domain user account (the “service account”) that is NOT an administrator on the machine. As such, the SQL Server installer only grants permissions to the folders the SQL Server setup creates.

Yeah I just scored this one.

Look in Windows Services. Start > Administration > Services

Find the Service in the list called: SQL Server (MSSQLSERVER) look for the “Log On As” column (need to add it if it doesn’t exist in the list).

This is the account you need to give permissions to the directory, right click in explorer > properties > Shares (And Security)

NOTE: Remember to give permissions to the actual directory AND to the share if you are going across the network.

Apply and wait for the permissions to propogate, try the backup again.

NOTE 2: if you are backing up across the network and your SQL is running as “Local Service” then you are in trouble … you can try assigning permissions or it may be easier to backup locally and xcopy across outside of SQL Server (an hour later).

NOTE 3: If your running as network service then SOMETIMES the remote machine will not recognize the network serivce on your SQL Server. If this is the case you need to add permissions for the actual computer itself eg. MyServer$.

NOTE 4: If SQL Server services running using Local System or Network Service account rather than any domain account then you cannot give permission on network folder.

If you are using a unc, eg \\server\share\mydb.bak, for a backup or restore and it is complaining that the file location can not be resolved or that the login has failed, there is an easy solution. Make sure the file is visible from the machine with sql server. If it is but you still get the error, it can be only one thing – the login that the SQL Server service is running under does not have access to the network location. In my case, I went to Control Panel -> Administrative Tools -> Services (on the server). I found the SQL Server service and checked it’s properties and discovered that it was using the local administrator account (.\Administrator) which obviously doesn’t have access to the rest of the domain. I changed this to a domain user, restarted the service, and voila, unc paths work fine now.

SQL Error: ‘DATEFROMPARTS’ is not a recognized built-in function name.

Datefromparts is new feature of sql2012. I was running command in sql2008

SQL Error: Derived column which calculate percentage was returning 0 for every rows

Scenario

I had a valid sql query to calculate percentage from two columns which were holding integer value,

Actual.PossibleAttendance/ Possible.PossibleAttendance AS Percentage

But it has not worked.

Another scenario was, I wanted to get derived columns with 2 digit decimal points

Solution

I converted the data into decimal points and then it worked.

CONVERT(DECIMAL(5,2), CONVERT(DECIMAL, Actual.PossibleAttendance) / CONVERT(DECIMAL, Possible.PossibleAttendance))*100 AS Percentage

ROUND(CAST(Actual.PossibleAttendance AS FLOAT) / CAST(Possible.PossibleAttendance AS FLOAT),4)*100 AS Percentage

####################

1. select cast(3 as float)/cast(2 as float);
2. select convert(float, 3)/convert(float, 2);
3. select convert(decimal(5, 2), convert(decimal, 3)/convert(decimal, 2))

SQL Error: Restoring backup error, Unable to open physical file

Error:

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file “C:\Download\TSQL2012.mdf”. Operating system error 2: “2(The system cannot find the file specified.)”.
Msg 5181, Level 16, State 5, Line 1
Could not restart database “TSQL2012”. Reverting to the previous status.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

 

Solution:

Just for database backup and restore testing purposes, I had deleted the master data file (.mdf) of TSQL2012 database then tried to access that database and I have got this error message.

SQL Error: The log in this backup, which is too recent to apply to the database

When performing a point in time restore of a SQL database, the restore may fail with the following error:

Error message:

Msg 4305, Level 16, State 1, Line 9
The log in this backup set begins at LSN 90000000031400001, which is too recent to apply to the database. An earlier log backup that includes LSN 90000000030600001 can be restored.
Msg 3013, Level 16, State 1, Line 9
RESTORE LOG is terminating abnormally.

Solution:

I tried to restore the transaction log, in time which was too recent to restore. What I mean is that stopat time was after taking the transaction log backup.

 

SQL Error: ALTER DATABASE is not permitted while a database is in the Restoring state.

Problem:

ALTER DATABASE is not permitted while a database is in the Restoring state.

Solution:

RESTORE DATABASE dbname 
FROM DISK = 'dbname .bak'
WITH REPLACE, RECOVERY --force restore 

or just

RESTORE DATABASE dbname WITH RECOVERY

the REPLACE Overwrite the existing database, do it only if you are sure you want to override your existing database as you mentioned you dont care to delete it

RESTORE WITH RECOVERY is the default behavior which leaves the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored.

That should bring the database online. Then you can delete it & try again.

 

SQL Error: The log or differential backup cannot be restored because no files are ready to rollforward.

Problem:

Msg 3117, Level 16, State 4, Line 3
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

Solution:

Well I have answered this question in my earlier post, 2 years ago, over here SQL SERVER – Fix : Error : Msg 3117, Level 16, State 4 The log or differential backup cannot be restored because no files are ready to rollforward. However, I will try to explain it a little more this time.

For SQL Server database to be used it should in online state. There are multiple states of SQL Server Database.

• ONLINE (Available – online for data)
• OFFLINE
• RESTORING
• RECOVERING
• RECOVERY PENDING
• SUSPECT
• EMERGENCY (Limited Availability)

If the database is online, it means it is active and in operational mode. It will not make sense to apply further log from backup if the operations have continued on this database. The common practice during the backup restore process is to specify the keyword RECOVERY when the database is restored. When RECOVERY keyword is specified, the SQL Server brings back the database online and will not accept any further log backups.

However, if you want to restore more than one backup files, i.e. after restoring the full back up if you want to apply further differential or log backup you cannot do that when database is online and already active. You need to have your database in the state where it can further accept the backup data and not the online data request. If the SQL Server is online and also accepts database backup file, then there can be data inconsistency. This is the reason that when there are more than one database backup files to be restored, one has to restore the database with NO RECOVERY keyword in the RESTORE operation.

I suggest you all to read one more post written by me earlier. In this post, I explained the time line with image and graphic SQL SERVER – Backup Timeline and Understanding of Database Restore Process in Full Recovery Model.

Sample Code for reference:

RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\AdventureWorksFull.bak'
WITH NORECOVERY;
RESTORE DATABASE AdventureWorks
FROM DISK = 'C:\AdventureWorksDiff.bak'
WITH RECOVERY;

In this post, I am not trying to cover complete backup and recovery. I am just attempting to address one type of error and its resolution. Please test these scenarios on the development server. Playing with live database backup and recovery is always very crucial and needs to be properly planned. Leave a comment here if you need help with this subject.

Similar Post:
SQL SERVER – Restore Sequence and Understanding NORECOVERY and RECOVERY

Note: We will cover Standby Server maintenance and Recovery in another blog post and it is intentionally, not covered this post.

SQL Error: The tail of the log for the database has not been backed up.

Error message:

Msg 3159, Level 16, State 1, Line 3
The tail of the log for the database “TSQL2012” has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

Solution:

The error message you are getting tells you exactly what you need to do if you don’t care about the existing database or log.

RESTORE DATABASE DAtabaseName FROM DISK = 'C:\DBName-Full Database Backup' 
WITH REPLACE

In SQL Server Management Studio (Tasks > Restore), you can add the WITH REPLACE option by opening the page “Options” on the left side and ticking “Overwrite the existing database”.

The message is warning you that there are transactions in the log that will be lost if you restore. It’s a safety net. The assumption is, if the DB is in full recovery then data loss is not acceptable and hence losing data because you haven’t backed the tail of the log up is a very bad thing.

If point-in-time restores are not necessary for this DB, put the DB into simple recovery model. If point-in-time restores is a requirement and data must not be lost, then you need to back the tail of the log up before restoring over the DB or you’ll lose all data since the last log backup.

You backup the tail of the log with BACKUP LOG … WITH NORECOVERY, that puts the database into a restoring state, ensures that no more transactions can occur and that you cannot lose data by restoring over the DB.

If your database is in full recovery model you will get this warning because in your backup restore you are not restoring transaction log. If your database was in simple recovery model you would not get this error message.