Author: Lakshmi Kiran Reddy

How to start SQL Server with Minimal Configuration

Problem Statement:

We recently migrated one of On-prem sql server to SQL server in Azure VM, As part of configuration settings, when we try to change the MAX memory of sql server, it accidentally changed the value to 128MB . We were unable to connect to sql server as sql server was not restarting due to low memory. This blog post explains steps that we followed the steps to fix this issue

  1. RDP to the server
  2. Make sure there are no other users RDP’d to the server, if they are, kick them off. You want to be the only administrator on the box.
  3. Open SQL Configuration Manager.
  4. Make sure all services are stopped. If they aren’t, right click stop them all.
  5. Right click SQL Server (InstanceName)>Properties>Startup Parameters tab>type -f in “Specify a startup parameter:”>Click Add
  6. This will add -f to the list on the bottom list (again not screenshotting that because I don’t actually want to change it right now).
    INFO: The -f flag, starts an instance of SQL Server with minimal configuration. This is useful if the setting of a configuration value (for example, over-committing memory) has prevented the server from starting. Starting SQL Server in minimal configuration mode places SQL Server in single-user mode. For more information, see the description for -m that follows.

OK out of this window. (It will warn you it won’t take effect until the next time SQL services are started, click OK on that box)

Open PowerShell ISE as administrator and put this code in the scripting pane:

net stop “SQL Server (InstanceName)”
net start “SQL Server (InstanceName)” -m”SQLCMD”

SQLCMD -S .\<InstanceName> -q "sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO"

If all the SQL services are turned off, you don’t need to execute the net stop line but is included incase the SQLCMD fails to get a connection, you don’t need to stop services from Configuration manager or services.msc.

Execute the Powershell code. I don’t have a good screenshot of the output or possible errors but if it throws any errors related to not being able to start the instance because its already running, or can’t connect to the instance during the SQLCMD part. Stop the script if its running, and run it again. I had to run it a few times (may have been due to trying to get all the code syntax right, or it was because something was making it in before SQLCMD (that service account had an RDP session open until I logged it off) could get a connection. Eventually you should see output that indicates the max memory was set and that you need to run a RECONFIGURE. Since the RECONFIGURE is part of the code to execute, it will execute, it just won’t output that the reconfigure was run. All done, Max memory is set to 4GB.

After getting Max memory set to 4GB, the instance is still up in single user mode and minimal configuration so all that’s left is to put it back to normal.

Stop the SQL services.

Remove the -f Startup Parameter.

Start all the SQL services.

Connect with SSMS and set the Max memory to what you actually want it to be.

Few additional notes:

  1. The main idea behind why this works is a combination of net start “SQL Server (InstanceName)” -m”SQLCMD” and using a single SQLCMD command (connect to the instance AND run a query in the same PosH command) in PowerShell in the same script to be executed all as close to each other as possible. I suspect doing it from CMD prompt may not be fast enough as separate commands.
  2. Net start with -m”SQLCMD” should force the instance to ONLY allow connections from SQLCMD. That means no other application should be able to steal our single admin session before we can connect.
  3. we choose 4096 for the max server memory because we only wanted to get enough memory to start up the instance like normal and we could adjust to final max memory after the fact. we didn’t want to go overboard on the emergency. It is also beneficial because most servers should have at least this much space to be able to allocate so if we use the script for other instances, it’s one less thing to change to make it work.
Key take away everyone should remember:

It doesn’t matters what issue you are trying to solve but if we EVER need to get into an instance in minimal configuration and single user mode, PowerShell net start -m”SQLCMD” and SQLCMD -q, all in the same script executed at the same time is very likely your best friend due to it limiting the single user to someone trying to get in with SQLCMD and then running SQLCMD immediately after so you get the only connection.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

How to re-queue an email in SQL that has been successfully sent at first time?

Let’s say you send an email from inside a SQL instance using something like the following:

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'SQLZEALOTS_Manager',  
    @recipients = 'Lkiran@xxx.com',  
    @body = 'This email Sent',  
    @subject = 'SqLZeaLoTs' ;

And recipient DOES successfully receive the email.

Now for one reason or another, they want the original email sent again (not an execution of msdb.dbo.sp_send_dbmail, but instead to basically re queue the original email to be sent again).

Step 1: Execute the following:

SELECT mailItem_id
       FROM [msdb].[dbo].[sysmail_mailitems]
       WHERE subject = 'SqLZeaLoTs'
       ORDER BY sent_date desc

Copy the mailItem_id value for the specific email you want to requeue, in this example 2738274.

Use msdb
GO
DECLARE @rc INT
,@sendmailxml VARCHAR(max)
,@mailID INT = 2738274 -- This is from the mailItem_ID column in [msdb].[dbo].[sysmail_mailitems] for the mail you want to resend. 2738274 is just an example.

SET @sendmailxml = '<requests:SendMail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.microsoft.com/databasemail/requests RequestTypes.xsd" xmlns:requests="http://schemas.microsoft.com/databasemail/requests"><MailItemId>'
                        + CONVERT(NVARCHAR(20), @mailID) + N'</MailItemId></requests:SendMail>'

    -- Send the send request on queue.
    EXEC @rc = sp_SendMailQueues @sendmailxml
    IF @rc <> 0
    BEGIN
       RAISERROR(14627, 16, 1, @rc, 'send mail')
END

If the query completes successfully, it has queued the original email again and the original recipient will receive another email with exactly the same subject and body.

Step 2 has only been confirmed to work on SQL 2008 R2 SP3.

Note: If you want to try it for later SQL Server versions, you may need to verify the code of step 2 by following the steps.

Script out the system procedure: msdb.dbo.sp_send_dbmail and look for the section near the bottom that executes sp_SendMailQueues. The most important piece to verify is the “SET @sendmailxml…” statement. The reason for this is that sp_SendMailQueues sends the email to a queue that is monitored by a broker that calls DatabaseMail.exe. It is entirely possible that different versions of SQL may have different versions of DatabaseMail.exe that requires the XML to be formatted differently so you want to make sure that the XML is formatted for the version required.

Please try this out on your environment and feedback if you have any comments.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Configure BlockedProcessReport in SQL Server

In this blog we will discuss on how to configure Blocked Process report on a particular database. This will be useful for troubleshooting any processes which are getting blocked for more than a specific time threshold. Even though blocking is common behavior in sql, but if the block duration is longer than expected, then we can experience performance issues. We have many ways to identify the blocking in sql (which are not going to be discussed here) but I am going to cover one method using event notifications.

By default, the “blocked process threshold” is zero, meaning that SQL Server won’t generate the blocked process reports. We need to configure by the “blocked process threshold” to a specific value (in seconds) using the sp_configure option. For example, if we set it to 15 seconds, then the event will fire three times if a session is blocked for 45 seconds.

Lets see the step by step process how to configure the blocked process report:

First the database should be enabled for service broker.

step1

We need to create QUEUE,SERVICE,ROUTE and EVENT NOTIFICATION to capture the blocking/deadlock events.

step2

step3

Please note in the above step , we have created a Event Notification at Server level , and on the database where you want to track the blocking for more than the configured value. All the above 3 steps are specific to DB level where as creating notification is at server level.  You can get the list of  already configured event notifications on your server with the below query:

SELECT * FROM sys.server_event_notifications

Now lets create a table to hold the blocking information

IF OBJECT_ID('[dbo].[BlockedProcessReports]') IS NULL
CREATE TABLE [dbo].[BlockedProcessReports]
(
blocked_process_id int IDENTITY(1,1) PRIMARY KEY,
database_name sysname,
post_time datetime,
blocked_process_report xml
);
GO

let’s create a stored procedure to read the messages receives from service broker queue  and capture the event in the above table we created:

CREATE PROCEDURE [dbo].[ProcessBlockProcessReports]
WITH EXECUTE AS OWNER
AS
SET NOCOUNT ON
DECLARE @message_body XML
,@message_type INT
,@dialog UNIQUEIDENTIFIER
,@subject VARCHAR(MAX)
,@body VARCHAR(MAX)

WHILE (1 = 1)
BEGIN
BEGIN
BEGIN TRANSACTION
-- Receive the next available message from the queue
WAITFOR (
RECEIVE TOP(1) -- just handle one message at a time
@message_type=message_type_id, --the type of message received
@message_body=CAST(message_body AS XML), -- the message contents
@dialog = conversation_handle -- the identifier of the dialog this message was received on
FROM dbo.BlockedProcessReportQueue
), TIMEOUT 1000 -- if the queue is empty for one second, give UPDATE and go away
-- If we didn't get anything, bail out
IF (@@ROWCOUNT = 0)
BEGIN
ROLLBACK TRANSACTION
BREAK
END

INSERT INTO [dbo].[BlockedProcessReports]
(
database_name
,post_time
,blocked_process_report
)
SELECT DB_NAME(CAST(@message_body AS
XML).value('(/EVENT_INSTANCE/DatabaseID)[1]',
'int'))
,CAST(@message_body AS XML).value('(/EVENT_INSTANCE/PostTime)[1]',
'datetime')
,CAST(@message_body AS XML)

END

-- Commit the transaction. At any point before this, we could roll
-- back - the received message would be back on the queue AND the response
-- wouldn't be sent.
COMMIT TRANSACTION
END;
GO

The final step is to activate the queue to call the stored procedure

ALTER QUEUE BlockedProcessReportQueue
WITH
ACTIVATION
(STATUS=ON,
PROCEDURE_NAME = [dbo].[ProcessBlockProcessReports],
MAX_QUEUE_READERS = 1,
EXECUTE AS OWNER);
GO

We will test this by creating a blocking scenario manually and lets check whether we can see the blocking information on the table we created:

Lets create an exclusive lock and shared locks on a table and see our table for blocking info.
create table
concurrent screen
table data
block xml.JPG

As you can see above we can see the queries which are involved in blocking from the above xml.

Hope you have enjoyed the post and share your comments

Python Dictionaries

A dictionary is a set of unordered key, value pairs. In a dictionary, the keys must be unique and they are stored in an unordered manner.

In this tutorial you will learn the basics of how to use the Python dictionary.

Creating a Dictionary:

dict1

Accessing Items:
You can access the items of a dictionary by referring to its key name, inside square brackets:

dict2

Updating Dictionary:
You can update a dictionary by adding a new entry or a key-value pair, modifying an existing entry, or deleting an existing entry as shown below in the simple example −

dict3

Loop Through a Dictionary:
You can loop through a dictionary by using a for loop.When looping through a dictionary, the return value are the keys of the dictionary, but there are methods to return the values as well.

dict4.JPG

Check if Exists:

You can test the presence of a key using ‘in’ or ‘not in’

dict5

Restrictions on Dictionary Keys:
Almost any type of value can be used as a dictionary key in Python. As an example,  integer, float, and Boolean objects are used as keys:

dict6

However, there are a couple restrictions that dictionary keys must abide by.

First, a given key can appear in a dictionary only once. Duplicate keys are not allowed. A dictionary maps each key to a corresponding value, so it doesn’t make sense to map a particular key more than once.
You could see below that when you assign a value to an already existing dictionary key, it does not add the key a second time, but replaces the existing value:

dict7

Restrictions on Dictionary Values:
By contrast, there are no restrictions on dictionary values. Literally none at all. A dictionary value can be any type of object Python supports, including mutable types like lists and dictionaries, and user-defined objects.There is also no restriction against a particular value appearing in a dictionary multiple times.

Hope you have enjoyed the post. Keep reading

Tuple in Python

Tuples in Python are immutable sequences of random objects. Once created, the objects within them cannot be replaced or removed,and new elements cannot be added.

Tuples have a similar syntax to lists except that they are delimited by parentheses rather than square brackets.Here’s a literal tuple containing a string, a float, and an integer.
We can access the elements of a tuple by zero-based index using square brackets, and we can determine the number of elements in the tuple using the built-in len function. We can iterate over tuples using the for loop, and we can concatenate tuples using the plus operator.

ex1

Sometimes a single element tuple is required. To write this, we can’t just use a simple number in parentheses. This is because Python pauses that as an integer enclosed in the president’s controlling parentheses of a math expression. To create a single element tuple, we make use of the trailing comma separator, which we’re allowed to use when specifying literal tuples, lists, and dictionaries.

A single element with a trailing comma is passed as a single element tuple. This leaves us with the problem of how to specify an empty tuple. In actuality the answer is simple.
We just use empty parentheses. here is the demo:

ex2

In many cases, the parentheses of literal tuples may be omitted. This feature is often used when returning multiple values from a function.

Here we make a function to return the minimum and maximum values of a series, the hard work being done by the two built-in functions min and max. Returning multiple values as tuple is often used in conjunction with a wonderful feature of Python called tuple unpacking.Tuple unpacking is a destructuring operation, which allows us to unpack data structures into named references. For example, we can assign the result of our minmax function to two new references like this.

ex3

Tuple unpacking works with arbitrarily nested tuples, although not with other data structures. Should you need to create a tuple from an existing collection object such as a list, you can use the tuple constructor, here also shown for strings.

ex4

Hope you have enjoyed the post. Please share your comments