PowerShell: How to rename multiple files in a folder at the same time

To rename single file you can manually edit the file names. If there are multiple files you want to change the name of the file or part of the file name, it is easy to rename the file in PowerShell.

You can use rename-item with -Replace option like below. To replace ".StoredProcedure" in all the files present in the current directory with "_", you can use the following syntax

   dir | rename-item -NewName {$_.name -replace ".StoredProcedure","_"}

For complete syntax and other examples visit the site at rename-item

SQL SERVER: How to refresh local cache in SQL Server Management Studio

When working on SQL Server Management Studio, when any new tables are created or columns are added, the local cache is not refreshed immediately and display as error for the new objects or columns in the Query Window. To avoid this we need to update local cache of SSMS.

If you need to refresh the local cache of SSMS you can use two methods

First Method:
       Press the following combination of keys on your keyboard by selecting SSMS

CTRL + Shift + R

Second Method:
    Select the Query window in SSMS
    In the Edit Menu
          Go to IntelliSense
          Press on Refresh Local Cache

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