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: 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.