Monday, August 13, 2012

MSSQL Error 701 There Is Insufficient System Memory

Monday, August 13, 2012  Nareshkar Pakanati  2 comments

Two days back I had encountered MSSQL 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. I am not exactly sure is there any hotfix is available for SQL Server 2008 R2 or not?

To resolve 701 issue on SQL Server 2008 R2 I have followed below steps and it worked for me:

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.
Resolving 701 There is insufficient system memory to run this query. SQL Server reports 701: "There is insufficient memory to run this. Memory Manager Errors. There is insufficient system memory to run this query. Error 701. MSSQLSERVER 701. SQL Server 2005 Error: 701. Severity: 17. State: 193. Memory problem. insufficient system memory. Memory Manager Errors. There is insufficient system memory in resource pool 'internal' to run. SQLServer Error: 802. 701. 733. SQL Error 701 Insufficient Memory. Slow performance and out of memory issues. in sql server 2008. in sql server 2012. in sql server 2000. in sql server 2005.

Read More

Friday, August 10, 2012

How To check Your Last SQL Database Backup Time

Friday, August 10, 2012  Nareshkar Pakanati  No comments

It is good to check when was your last backup happened, I have wrote small query which it pull the information from msdb backupset table, it lists all the database names with last backup date and time. 

It is very easy to track when was your last backup was happened. You can use the following query.

SELECT xx.name AS [Database],
max(backup_finish_date) AS [LastBackupDate]
FROM [master].[dbo].[sysdatabases] xx
LEFT OUTER JOIN [msdb].[dbo].[backupset] xy
ON xy.database_name = xx.name
AND xy.type = 'D'
GROUP BY xx.name
ORDER BY xx.name


Backing Up Your Database. Create a Full Database Backup. Backing Up and Restoring Databases. Have your database ever been backed up. Finding Last Backup Time for All Database. Find out when your SQL Server databases were last backed up. SQL Server database backup restore on lower version. Checking when a database was last backed up. Considerations for Backing Up the master Database. Best Practices for Backing Up Commerce Server. Backup Overview. What is your SQL Server backup strategy. Database.LastLogBackupDate Property. SQL Server 2008 R2. sql server 2012. sql server 2008. sql server 2000. sql server 2005.

Read More

ERROR 14151 Replication Distribution

Friday, August 10, 2012  Nareshkar Pakanati  No comments

Error : 14151, Severity: 18, State: 1.    Replication-Replication Transaction Queue Reader Subsystem: agent [Server_Name].5 failed. The Queue Reader Agent has encountered the error ''There is insufficient system memory to run this query.'' when connecting to ''distribution'' on ''Server_Name''. Ensure that the publication and subscription are defined properly.

This error usually is a result of replication rollback when network traffic is heavy. Normally this wouldn't be a problem, but some of the subscribing tables had triggers for data redistribution. The replication command failed and rolled back, but the trigger did not. This caused integrity problems that appeared to be reported incorrectly by replication when it retried.

Suggested Actions:
1. Check the Connection, its required faster connection.
2. Stop the users surfing on the same connection
3. Re-write the triggers to be integrity friendly
4. Replace trigger functionality with scheduled batch processing
5. Reduce the default replication batch size from 100 to < 10 (reduces no. of transactions tried in one batch, thus reducing the possibility of timeouts). This will reduce replication throughput on a busy system. Open the SQL Agent distribution task for the publication affected and change the -c100 to -c10 (check in SQL BOL).
Error: 14151. Severity: 18. State: 1. Access to the path is denied. Error 14151. SQLSTATE 42000. Replication Error. Error 14151 (0x3747) Information - Error Code 14151. The subscription fails to Sync with the Publisher and marked inactive. Fun little replication error. sql error 14151 severity 18 state 1. error 14151 severity 18 state 1 replication. error 14151 severity 18 state 1 sql 2008. error 14151 sql server replication. error 14151 severity 18 state 1 sql 2005. sql server error 14151. error 14151 queue reader. 1 error when reinitialising a publication. SQL Server 2012. SQL Server 2008. SQL Server 2008 R2. SQL Server 2005. SQL Server 2000.

Read More