Event ID: 7000 The SQL Server...service failed to start due to the following error: Access is denied.

Event ID: 7000 The SQL Server...service failed to start due to the following error: Access is denied.

Two months back, I worked through a new install of SQLServer 2012 with SP1 on Microsoft Windows Server 2012 Standard on a VMware virtual machine.  During the install everything went smoothly, however once complete I noticed the services were stopped and there were errors in the OS system log:

Log Name:      System
Source:        Service Control Manager
Date:          4/11/2013 12:50:41 PM
Event ID:      7000
Task Category: None
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      ...
Description:
The SQL Server (MSSQLSERVER) service failed to start due to the following error: 
Access is denied.

I turned to my best friend, in situations like this: www.google.com.  Unfortunately this didn't get me very far; it took a bit more digging.  I gathered more errors from the Application log, the System log, and the bootstrap summary (%programfiles%\Microsoft SQL Server\110\Setup Bootstrap\Log\Summary.txt): 

• Attempted to perform an unauthorized operation
• error: 40 - Could not open a connection to SQL Server

I also notice that if I attempted to change the "log on as" user in SQL server Configuration Manager again I received an "access is denied" error.  This occurred even if I attempted to use a system user.

It was googling more detailed error messages from the bootstrap summary log that lead me to the solution.  

Apparently, when you build Windows Server 2012 on VMware we need to disable the HotAdd/HotPlug capability.  Who knew?

This was a difficult one to track down, but thanks to SQL Server Central forums, MSDN forums and the community I now have a new item on my build checklist.

One of the things that really helped quickly get through multiple install attempts was using an install config file.  This allowed me to run through the install over and over without having to click through the install each time.  There's a really good article on how to setup an unattended install here: SQL Server 2008 R2 – Unattended Silent Install (Works the same in 2012).

Happy Installs!

Error: 3241 in sql server

There are two reasons for getting this error. They are:

First reason:

It looks like, you are getting error message due to a mismatch of SQL server versions between the backup and restore. Generally, you can't restore a backup created using higher version to lower version.


Second Reason:

It seems that your backup is corrupted.

Try to take another back up and attempt restore that. If that works, which means this backup has some corruption.
 

Error 701 in sql server

 Error: 701 There is insufficient system memory, I've never come across this problem before in SQL Server 2008 R2, but long back I had faced similar kind of problem for SQL Server 2005, after installing hotfix it got resolved. To resolve 701 issue on SQL Server 2008 R2 I have followed below steps and it worked forking fine:

1. Increase SQL Server Maximum Memory through SQL Server Properties.
 

2. It is actually a bug in the SQL Server. It is not releasing the memory which it holds when it is necessary.

DBCC FreeProcCache -- Clear entire proc cache
DBCC DropCleanBuffers -- Clear entire data cache
DBCC DROPCLEANBUFFERS
DBCC FREESESSIONCACHE

3. If above steps not resolved, check min memory per query, Default value is 1024 KB, it is not recommended to decrease the value but still you can try.

Error 7391 in Sql server

Msg 7391, Level 16, State 2, Line 8

One of our application team members was trying to run Distributed Transaction query through linked server. He is saying whenever he is running the select query with Begin Distributed transaction it is throwing an error but the simple SELECT statement is working fine. So here no issue with linked server only issue with Distributed Transaction.

Below error message he is getting while running Distributed transaction through linked server.

Error Description:

OLE DB provider "SQLNCLI10" for linked server "Linkserver1" returned message "No transaction is active.".

Msg 7391, Level 16, State 2, Line 3

The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVERNAME" was unable to begin a distributed transaction.

Workaround/Solution:

Step 1:  Select Administrative Tools from start menu and click Component Services 
Step 2:  After Clicking Component Services it will open the Component Services console like below.

Step 3:  Expand the Component Services inside that expand folder "Distributed Transaction Coordinator", now you can see Local DTC. 
Step 4:  Right click on the Local DTC Properties, it will open a new popup like below.

Step 5:  On the Local DTC Properties window, click on the Security Tab like below.

Step 6:  Check Network DTC Access under Security Settings like above.

Step 7:  In the Transaction Manager Communication box, Check Allow Inbound and Allow Outbound  and check the required Authentication method as per your requirement.

FIX Transaction Log Full Error 9002‏

We have to find out full transaction log of any database depends on what condition caused the log to fill entire drive.
Here is the script for releasing transactional log from the database

STEP-1

Run the below script for finding logical name of your log file 
EXEC SP_Helpdb 'XXX'

GO 

STEP- 2

USE XXX

GO

CHECKPOINT

GO

BACKUP LOG XXX WITH TRUNCATE_ONLY

GO

dbcc shrinkfile ('XXX_log',TRUNCATEONLY)

go

dbcc SHRINKFILE (XXX_log, 1)

GO

Change your database name with XXX and logical name of your database log file with XXX_log.

Error: 9004, Severity: 21, State: 1 in sql server

An operation in SQL Server that needs to read or process the transaction log can fail with an error like the following if the transaction log is damaged.

Error: 9004, Severity: 21, State: 1.

An error occurred while processing the log for database 'mydb'.  If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

Microsoft suggest 2 options for this 9004 error, let me explain the steps in details. Click here for BOL. 

Option 1: 
First you have to create an empty database with the same name and physical file structure, then shut down the server, swap the both .log and .mdf files you want to attach in place of the empty DB files, and start the server. After above step the database should come up in suspect mode. You can then run this script ALTER DATABASE <database name> SET EMERGENCY to put it in emergency mode, and then run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS . The above command clear the some data in log to make the database consistent, but may have to delete some data in order to make the database consistent.  This is the best option which is most likely to get the maximum data back. 

Option 2: 
You can attempt to use the CREATE DATABASE FOR ATTACH_REBUILD_LOG to see if that will bring it back.  If the database was cleanly shut down, you MIGHT be able to succeed.  There is also the chance that the database will be inconsistent or corrupt if there are transactions which could not be rolled back.  You should run DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS to make your database consistent. If there were transactions in process no rollback will be possible, so the ALLOW_DATA_LOSS will be required

Error: 945 in sql server

Error 945 Database cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details.

This error is very common for any database administrator who are handling more databases in a single instance. It will leads to suspect the database most of the time. 

Fix / Solution:

Step 1: if the drive is specifically assigned to Log Files and Log drive was full, clear the drive space by shrinking the log space of other databases which occupied more log space. 
Step 2: ALTER DATABASE [Database name] SET ONLINE 
Step 3: If possible add more drive space by adding new hard drive to server. 
Step 4: Increase the Database log size to more or set to Unlimited grow. 
Step 5: Find out if the account which is trying to access the database has enough permission to access the database. 
Step 6: Configure one maintenance job for backup log.