Unable to connect to SQL servers Error 17300

I recently worked on issue in which we were unable to connect to the SQL instance and we were getting below error while we try to connect  even though the SQL service was running

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

While analyzing the  error log I was able to find error 17300 which points to memory pressure , but the instance  was a newly provisioned one and there was no chance for any workloads causing memory pressure and while analyzing the logs further I found that while doing the post install tasks Junior DBA accidentally set  max memory to a very small value 

OK.. So now we know what change caused the issue and max memory need to be increased to fix the issue  . How can we increase the max memory where we are not able to connect to instance ?

These are the situations in which Dedicated Administrator Connections comes as savior

I took the DAC session by giving 'admin:Instnace name' in the connection window which popups while i open a new query window[Ctrl +N] in SSMS( if you try to take DAC connection by connecting to instance from object explorer it will fail with the message :"Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design" as object explorer make multiple connection to instance) another option to connect to DAC is using SQLCMD.

 

Once you have DAC connection run the command to change the max memory setting 

EXEC sp_configure 'show advanced option', '1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'max server memory', '10240'
GO
RECONFIGURE WITH OVERRIDE
GO 

After increasing max memory setting  normal SQL connection was working fine but i have an interesting question from my curios Junior DBA " what if he would have tried to restart the instance and instance failed to start due to lack of memory? " 

So let me show you how we can resolve such situation. I changed the max memory back to 128 MB and restarted the instance and  the instance failed to start and as expected there was 17300 logged in the error log

So how to change max memory setting now  as DAC is not an option when the SQL itself is down 😝

We still have a Savior "Minimal configuration Mode" which helps us when the SQL fails to start due to configuration issue , we can start Sql service in minimal configuration mode by giving -f in startup parameter or running "net start MSSQLSERVER /f" from an elevated command prompt.

Once the SQL Service was started in minimal configuration mode we took the DAC connection and changed the memory setting ,stopped and restarted the service in normal mode and now were are able to connect to SQL instance.

 

Taking log backup when there is no disk space for taking backup

Have you come across a situation in which log file filled up disk and you don't have enough space to take a log backup ? I have come across one ,let me share my learning here
 
One of my application team member contacted me and told their applications development environment is down. When i checked the SQL health i found that database log file have filled up the disk and log reuse wait was showing log backup

So what's next take a log backup right ? but unfortunately in my case since this  was non prod server log backups was never configured and we didn't have enough space for taking large log backup so what else can be done here ? even though there was  no point in time requirement but switching to simple recovery model was not an easy option as this database was part of availability group. Backup to 'NUL' option came to help in this scenario. I triggered a log backup to NUL backup log [databasename] to disk='NUL' which truncated the log file and  i was able shrink the database to  create space in log drive.


What is backup device NUL?
The NUL disk device( it is not NULL)  will discard all information sent to it,even you won't be able to create a file or folder NUL in windows. NUL is a system reserved device name. 

In short we will be able to mimic a backup but actually we won't be  having a backup file and it really don't server the purpose of a backup 😆. If you are taking a log backup to NUL SQL Server will treat it as a valid log backup(it doesn't take database to pseudo-simple recovery model like the truncate_only option we used before SQL 2008). Subsequent log backups will succeed   but you won't be able to restore the subsequent log backups as you took the log backup to NUL so take a differential or full backup if you want to have a restore able log backup chain.

Let me share backup and restore scenario created in my lab machine  where i took a log back to NUL

I took a log backup to NUL and next log backup completed successfully as you you can see below 

 

When i try to restore all the backup files ,log backup after the backup to NUL fails as earlier log backup with a lesser LSN is missing .

You will end up in same situation if you are trying to restore a differential backup taken after a full backup to NUL