SQL Server: Error Msg 701, Level 17, State 130, Line 12 There is insufficient system memory in resource pool 'default' to run this query

How to resolve insufficient system memory issue in SQL Server

Msg 701, Level 17, State 130, Line 12
There is insufficient system memory in resource pool 'default' to run this query

This error occurs when SQL Server uses Maximum memory allotted to SQL Server. You can check the Maximum memory allotted to the SQL Server by checking the server properties.

If RAM is available on the Server where SQL Server is installed you can increase the MAX RAM size by connecting the SQL Server using DAC (Dedicated Administrator Connection and execute the following commands. Change the RAM size based on the RAM available on the server


EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'298844'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO

SQL SERVER: How to open DAC (Dedicated Administrator Connection) to SQL Server Management Studio (SSMS)

DAC connection is used to SQL Server when you are not able to connect SQL Server.

Only one DAC connection is allowed for SQL Server instance.

1. Open the SQL Server Management Studio as Administrator
2. Click on “Database Engine Query” (icon next to New Query) on the toolbar.
3. In the “Server Name” box enter the server name in the following formatADMIN:local
4. Connect the server and execute the required commands.

For more details check the technet article.

SQL Server: How to release unused memory from SQL Server

Often times, we need to release unused memory from SQL Server. SQL Server not releases unused memory immediately. One way to release the memory is to restart the SQL Server Service. This is not always possible in Production environment.

To release SQL Server unused memory in Production environments use the following commands by connecting the server using DAC (Dedicated Administrator Connection) Connection

Run the following commands to release the unused memory from SQL Server.



/*Use maximum of 10 GB of RAM and release rest of Memory*/

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'102400'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO

/*Run the following command after releasing the memory. Make sure you changed the Memory values based on the Server RAM (MAX 85%)  */

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure N'max server memory (MB)', N'298844'

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE

GO

Pig: How to clear Pig Console

How to clear Pig Console / Screen?

Execute the following command at Grunt / Pig prompt

      clear;

Hive: How to view different objects present in Hive Metastore

How to view the databases present in Hive Metastore?

Execute the following command at Hive prompt

         SHOW DATABASES;

How to change / use different database?

Execute the following command at Hive prompt. Replace the <<Database_Name>> with actual database name.

       USE <<Database_Name>>;
 
       USE default;

How to view the tables present in a database?

Execute the following command at Hive prompt

       SHOW TABLES;

Hive: How to clear Hive Console

How to clear Hive Console / Screen?

Execute any of the following commands on Hive Console

     !clear;

OR

    CTRL + L

Hive: How to display column headers when executed SELECT statement

How to display Column Names / Headers when executed SELECT statement?

Set the below Hive property before executing the SELECT statement at hive prompt.

        SET hive.cli.print.header=true;

After executing the above statement execute the SELECT statement