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.

Microsoft SQL Server, Error: 1222

Issue :  Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222).

Issue: In Sql Server 2000 we face this issue very frequently due to bug. Please see the screen shot above, which we got while expanding the tables (+) button.

In SQL server 2000 cannot grant a lock to a transaction on a resource because another transaction already owns a conflicting lock on that resource, the first transaction becomes blocked waiting on that resource. We will get these errors due to any transaction containing the statement is not rolled back or canceled by SQL Server.

Workaround: 

Step 1. Check SP_who2 and SP_lock for any locks or blocks and who is blocking it.

Step 2. If you found any locks then kill those locks.

Step 3. If you are not found any blocks then push this databases to single user mode and bring back to Multi user mode after 10 seconds. then it will works fine. 

Step 4. After doing above steps then all users can access the table through EM or SSMS Step 5. Microsoft released one bug fix for this as well check msdn.

Here is the SQL Query for changing the database options  In 2000 for pushing the Database to Single User Mode, use below script. 

use master  GO  EXEC sp_dboption 'DatabaseName', 'single user', 'true'  GO 
In 2000 for bringing the Database to Multi User Mode, use below script. use master  GO  EXEC sp_dboption 'DatabaseName', 'single user', 'false'  GO

Error 18456 in sql server

In SQL Server's common issue in securities is login failed for user, Error 18456.
To resolve 18456 error, you have to change the Server Authentication mode "SQL Server and Windows Authentication mode".

In order to resolve the above issue through selecting "SQL Server and Windows Authentication mode", please refer to the following steps:
Here is the error #18456 getting while connecting to the server through "SA" login and password.

Workaround:  Step 1. Login to the Microsoft SQL Server Management Studio with Windows Authentication.

Step 2. In SQL Server Management Studio Object Explorer, right-click on the server, and then click Properties.

Step 3. Under the Server Properties, select "Security" page.

Step 4. Select the Server authentication as "SQL Server and Windows Authentication mode" and click Ok.

Step 5. After changing the server authentication mode from windows to SQL Server and Windows Authentication Mode, server needs to restart then only changes will effect.