Friday, September 28, 2012

SQL Server: Why a Session With sp_readrequest Takes so Long to Execute

While applying, Long Running Sessions Detection Job on a production server, we start receiving alert that a session is taking more then 3 minutes. But what actually this session was doing. Here is the alert report.

Stored Procedure Call
DB Name
Executing Since
3 min
sp_readrequest is a system stored procedure, which basically reads a message request from the the queue and returns its  contents.
This process can remain active for a time we have configured for parameter DatabaseMailExeMinimumLifeTime, at the time of database mail profile configuration. 600 seconds is the default value for this external mail process. According to BOL DatabaseMailExeMinimumLifeTime is the The minimum amount of time, in seconds, that the external mail process remains active.
This can be changed, at the time of mail profile configuration or you can just use update query to change this time.

UPDATE msdb.dbo.sysmail_configuration
SET paramvalue = 60 --60 Seconds
WHERE paramname = 'DatabaseMailExeMinimumLifeTime'
We have changed this to 60 seconds to resolve our problem.

Thursday, September 27, 2012

SQL Server: Template Explorer, A Developer’s Close Friend

How many of us really memorize all create, update or drop/delete scripts. Very honestly, I just remember Create Procedure and Create Function scripts. But, reality is that, we need not to remember all these codes/scripts, especially when Template Explorer is there for our help.
If you can’t see Template Explorer  in your SQL Server Management Studio, then just move your mouse pointer to top menu, view , Template Explorer or if you are short keys fan then just press Ctrl+Alt+T from your keyboard.

See how quickly I can create a new trigger.


Wednesday, September 26, 2012

SQL Server Errors: ORDER BY items must appear in the select list if SELECT DISTINCT is specified

SQL Server force you to put columns in SELECT DISTINCT list which are part of ORDER BY clause. But what if we don’t want to add that column/s in SELECT list. Lets try it.
--create temporary table to hold records
CREATE TABLE #DistinctSortTest (Val1 INT, Val2 INT)
--insert some records
INSERT INTO #DistinctSortTest
VALUES (1,100),(8,55),(3,33),(1,1),(9,999)
--lets see what we have in table
SELECT * FROM #DistinctSortTest
From this table we need only “Val1” column with distinct values BUT sorting output with “Val2”. Lets try simple query.
FROM #DistinctSortTest
Opps. Error
Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
To resolve this problem, we can use GROUP BY clause with MIN()or MAX() function in ORDER BY clause. 
FROM #DistinctSortTest
Through GROUP BY (all select columns) we will achive functionality of DISTINCT and MIN()/MAX() functions for sorting. MIN() in Order By clause can be used for ASC sort and MAX() for DESC sort.
--drop temporary table when not required
DROP TABLE #DistinctSortTest

Monday, September 17, 2012

SQL Server: Disable Logon Trigger Using DAC to Resolve Login Problem

Recently I have received a mail from one of blog reader, who explained his problem as following:
“I have tried scrip to create logon trigger from your blog post Restrict Login from Valid Machine IPs Only (Using Logon Trigger) BUT problem is that, I forgot to put localhost in my safe list, and now I am unable to login to my instance.”

Well, if same happened to you, then you need to login using Dedicated Administrator Connection. What is DAC and how to you use it Read This.
DAC can be established using sqlcmd or through SSMS. On command prompt, type this to establish connection.

Sqlcmd –S localhost –d master –A
You can provide instance name instead of localhost. Next thing is to disable our logon trigger, using following command.

Where “tr_LogOn_CheckIP” is the name of our logon trigger. On next line type GO to execute DISABLE command.

Now you can login to your database server. Once login, check out trigger is disabled.

You can achieve all this through SQL Server Management Studio. To establishing dedicated connection, click on  FILE----NEW----Database Engine Query

Login through valid SYSADMIN user, by providing server name with extra word and a colon, i.e. Admin:

In query window, type same tsql and execute to disable trigger.

And never forget to add your server IP or <localhost> in safe list, while creating logon trigger.

Wednesday, September 12, 2012

SQL Server: Keeping Log/Alert for Job Disable/Enable Status

To monitor production database servers, Database Administrators create different jobs and depends upon these jobs to work for them i.e. to check if server have enough space, database is not corrupt, queries are not running slow, index defragmentation and many more. BUT what if somehow, someone accidently disabled a job and forgot to enable it back. No alert will be created as job is disabled. Or it can be fatal when you need to restore a database and found that backup job was not working as it was disabled by someone ;)

Is there any way to get alert if someone changes any job status on production server?
YES, by creating following trigger on msdb.dbo.sysjobs can resolve this problem. It will detect any change in job status and will mail a message like following to your DBA team.

Job "Daily Full Backup" is recently DISABLED by user aasim.abdullah with session id 167 and host name IdeaWrox-DB01 at Sep 12 2012 4:00:03:673AM

Monday, September 10, 2012

SQL Server: Simple Way to Swap Columns values

To resolve a problem, sometime we start thinking at high level while, simple solutions of said problem are available. This is what happened to me, when one of my colleagues (Tehman) asked me how to swap two column values in a table.
My answer was, create a third column (temporary) and swap using this third column, which you can remove later on. Here was the plan.
  1. Move Col2 data to Col3
  2. Move Col1 data to Col2
  3. Move Col3 data to Col1
  4. Drop Col3

-- Create Temporary table to hold values
CREATE TABLE #ForSwappingTest ( Col1 VARCHAR(50), Col2 VARCHAR(50))
-- Insert test reocrds
INSERT INTO #ForSwappingTest (Col1,Col2)
VALUES ('A','X'),
-- Check Results
SELECT * FROM #ForSwappingTest
-- Add third column to hold data temporarily
ALTER TABLE #ForSwappingTest ADD  Col3 VARCHAR(50)
-- Start Swaping
UPDATE #ForSwappingTest

UPDATE #ForSwappingTest

UPDATE #ForSwappingTest
-- Remove additional temporary column
--Drop temporary table when not required
DROP TABLE #ForSwappingTest

But he came with a very simple solution, by writing following simple query.
UPDATE #ForSwappingTest
SET Col2 = Col1,
Col1 = Col2

Wednesday, September 5, 2012

SQL Server: Automatically Detect and Kill Long Running Sessions on Production Servers

Problem:  On production servers, how to detect long running sessions and kill them automatically if they are exceeding a specific amount of time.
Solution to this problem is very simple. Just create a job, which will detect long running sessions by running query against sys.sysprocesses executing SP_WHO or sp_WHO2 and then kill those sessions which are exceeding a time limit. BUT major problem is that SP_WHO or SP_WHO2 are unable to provide important information, like actually which execution command, stored procedure call or tsql batch is being executed by this culprit session? To avoid, in future and to find out permanent solution for such long running quires we need to mail them before we kill these sessions.
Following is the script we like to use on production servers, to find out costly sessions and send complete information to DBA team through mail, before killing these costly processes, automatically.

Mail output would be as following.