SQL Server Administration: finding blocking process with procedure

When one process blocks another from accessing an object, it’s often because yet another process is blocking it. The best tool for the sleuth in this case isn’t a magnifying glass or meerschaum pipe—it’s a stored procedure that traces process blocks back to their originators.

That’s what sp_find_ root_blocker does. Here’s the code:

01.USE master
02.GO
03.IF OBJECT_ID('sp_find_root_blocker') IS NOT NULL
04. DROP PROC sp_find_root_blocker
05.GO
06.CREATE PROCEDURE sp_find_root_blocker @help char(2)=NULL
07.AS
08.IF (@help='/?') GOTO Help
09.IF EXISTS (SELECT * FROM master..sysprocesses p1 JOIN master..sysprocesses p2 ON
10.(p1.spid=p2.blocked)) BEGIN
11. DECLARE @spid int
12. SELECT @spid=p1.spid -- Get the _last_ prime offender
13. FROM master..sysprocesses p1 JOIN master..sysprocesses p2 ON
14.(p1.spid=p2.blocked)
15. WHERE p1.blocked=0
16. SELECT p1.spid,
17. p1.status,
18. loginame=LEFT(p1.loginame,20),
19. hostname=substring(p1.hostname,1,20),
20. blk=CONVERT(char(3),p1.blocked),
21. db=LEFT(db_name(p1.dbid),10),
22. p1.cmd,
23. p1.waittype
24. FROM master..sysprocesses p1 JOIN master..sysprocesses p2 ON
25.(p1.spid=p2.blocked)
26. WHERE p1.blocked=0
27. RETURN(@spid) -- Return the last root blocker
28.END ELSE BEGIN
29. PRINT 'No processes are currently blocking others.'
30. RETURN(0)
31.END
32.RETURN 0
33.Help:
34.EXEC sp_usage @objectname='sp_find_root_blocker', @desc='Finds the root
35.offender(s) in
36.the chain(s) of blocked processes',
37.@parameters='', @returns='spid of the root blocking process (returns the last one
38.if
39.there are multiple)',
40.@author='Technet Guru', @email='msdn.com',
41.@version='6', @revision='0',
42.@datecreated='20060101', @datelastchanged='20161212',
43.@example='sp_find_root_blocker'
44.RETURN -1
45.GO
Testing our procedure:
sp_find_root_blocker
------------
No processes are currently blocking others.
SQL Server Administration: finding blocking process with procedure

Sql Server: Unusual String Functions

One of  unusual string functions in sql server is formatmessage()

What is formatmessage() function?
and where we may need to use this unusal function ?

Using FORMATMESSAGE(), you can format strings using a printf()-like syntax. It takes a parameter
specifying the ID of the message from the master..sysmessages table that you want to use, as well as a list of
arguments to insert into the message. FORMATMESSAGE() works similarly to RAISERROR(), except that it
doesn’t return an error. Instead, it returns the resulting message as a string, which you may then do with as
you please. Unfortunately, FORMATMESSAGE() is limited to messages that exist in sysmessages—you can’t
use it to format a plain character string.

With the help of formatmessage() function we can provide awesome results for printing our result query

Here’s a technique for working around that

First we need create table

and then need to run our query

 

CREATE TABLE #engagements
(Engagement varchar(30),
 EngagementStart smalldatetime,
 EngagementEnd smalldatetime)
INSERT #engagements VALUES('Gulf of Tonkin','19640802','19640804')
INSERT #engagements VALUES('Da Nang','19650301','19650331')
INSERT #engagements VALUES('Tet Offensive','19680131','19680930')
INSERT #engagements VALUES('Bombing of Cambodia','19690301','19700331')
INSERT #engagements VALUES('Invasion of Cambodia','19700401','19700430')
INSERT #engagements VALUES('Fall of Saigon','19750430','19750430')
DECLARE @msg varchar(60), @msgid int, @Engagement_id varchar(10), @inprint int
SELECT @msgid=ISNULL(MAX(error)+1,999999) FROM master..sysmessages WHERE error >50000
SELECT @Engagement_id=CAST(Engagement AS varchar), @inprint=COUNT(*) FROM #engagements GROUP BY Engagement
 --Get the last one
BEGIN TRAN
EXEC sp_addmessage @msgid,1,'Show  Format Message: %s has %d titles in print'
SET @msg=FORMATMESSAGE(@msgid,@Engagement_id,@inprint)
ROLLBACK TRAN
SELECT @msg
(No column name)
Show  Format Message: Tet Offens has 1 titles in print

 

and results was successful.
We got good result for printing from simple data

you can see this article in msdn wiki page too:

http://social.technet.microsoft.com/wiki/contents/articles/33038.sql-server-unusual-string-functions.aspx

Sql Server: Unusual String Functions

How to find incorrect datetime data from «Char» format column

One of my colleges was asked me
about problem finding incorrect data
but hier problem is this column is Char type
and find incorrect rows by the date type

for example hava a one table

 

CREATE TABLE #test (c1 char(8) NULL)
INSERT #test VALUES ('20150131')
INSERT #test VALUES ('20000131')
INSERT #test VALUES ('20150229')
INSERT #test VALUES ('20150229')
INSERT #test VALUES ('19990331')
INSERT #test VALUES ('20000331')
INSERT #test VALUES ('20150131')
INSERT #test VALUES ('20000131')
INSERT #test VALUES ('20150229')
INSERT #test VALUES ('20150229')
INSERT #test VALUES ('19990331')
INSERT #test VALUES ('20000331')
INSERT #test VALUES ('20150131')
INSERT #test VALUES ('20000131')
INSERT #test VALUES ('20150229')
INSERT #test VALUES ('20150229')
INSERT #test VALUES ('19990331')
INSERT #test VALUES ('20000331')
in this insert process have a row ‘20150229’ does not match in really datetime
because in really in 2015 year february is 28 dayhow select this incorrect rows with automaticly wihtout diffuclut queries or without cross or inner join

for this problems in Microsoft Sql Server have a  awesome function
this is isdate()

in correct time isdate() give us 1 in incorrect time give us 0

then we are need select our table for give incorrect rows

CREATETABLE#test (c1 char(8) NULL)
INSERT#test VALUES('20150131')
INSERT#test VALUES('20000131')
INSERT#test VALUES('20150229')
INSERT#test VALUES('20150229')
INSERT#test VALUES('19990331')
INSERT#test VALUES('20000331')
INSERT#test VALUES('20150131')
INSERT#test VALUES('20000131')
INSERT#test VALUES('20150229')
INSERT#test VALUES('20150229')
INSERT#test VALUES('19990331')
INSERT#test VALUES('20000331')
INSERT#test VALUES('20150131')
INSERT#test VALUES('20000131')
INSERT#test VALUES('20150229')
INSERT#test VALUES('20150229')
INSERT#test VALUES('19990331')
INSERT#test VALUES('20000331')
SELECT*
FROM#test
WHEREISDATE(c1)=0
c1
20150229
20150229
20150229
20150229
20150229
20150229

result was succes

in table  6 rows  does not match in really datetime

and you can see this articel in my msdn wiki page too

How to find incorrect datetime data from «Char» format column

SQL Server:Find updated row by another user

One of my colleges asked me about interesting problems
the problem that
have a one table and adminstator database want see every updating this table
(for example inserting or updating rows) by another users
solution have a SQL SERVER
but a interesting
we must use «TIMESTAMP» function

have a quession what is a «TIMESTAMP» function this is time function from sql server?

answear one — no  this is no time function

A timestamp is a special
binary(8) value that’s guaranteed to be unique across a database
and give value from @@DBTS system function
and from different databases give us different value

for example

USE tempdb
select @@DBTS as tempdb_timestamp
use master 
select @@DBTS as masterdb_timestamp
use msdb 
select @@DBTS as msddbdb_timestamp
tempdb_timestamp
0x0000000000000887
masterdb_timestamp
0x0000000000000FA0
msddbdb_timestamp
0x0000000000002710
and using «TIMESTAMP» function with inserting time in a different rows will give us  different value

for example

CREATETABLE #test(k1 intidentity,timestamptimestamp)
INSERT #test DEFAULTVALUES
go 5
select* from#test
droptable#test
k1  timestamp
1   0x0000000000000888
2   0x0000000000000889
3   0x000000000000088A
4   0x000000000000088B
5   0x000000000000088C
in this example rows of column timestamp was give in inserting time different value from «TIMESTAMP» function

and in updating time default value from  @@DBTS was give different value from updating process

for find different value we are need «TSEQUAL()» function SQL SERVER

This command compares two timestamp values—returns 1 if they’re identical, raises an error if they’re not

then we can created our query for give message from updating our table by another user

CREATE TABLE #test
(k1 intidentity,
timestamptimestamp)
DECLARE@ts1 timestamp, @ts2 timestamp
SELECT@ts1=@@DBTS, @ts2=@ts1
SELECTCASEWHENTSEQUAL(@ts1, @ts2) THEN'Equal'ELSE'Not Equal'END
INSERT#test DEFAULTVALUES
SET@ts2=@@DBTS
SELECTCASEWHENTSEQUAL(@ts1, @ts2) THEN'Equal'ELSE'Not Equal'END
GO
(1 row(s) affected)
(1 row(s) affected)
Msg 532, Level16, State 2, Line 11
The timestamp(changed to0x0000000000000892) shows that the row has been updated byanother user.
and  result was succes

from inserting process  we was  give message    «the row has been updated by another user"

and you can see this article my msdn wiki page too
SQL Server:Find updated row by another user

SQL SERVER : Creating Pivot Table With sign() Function

First we will created #test table and will inserting data this table

 

CREATE TABLE #test
(SalesYear smalldatetime,
 Sales money)
INSERT #test VALUES ('20150101',100.09)
INSERT #test VALUES ('20150101',200.12)
INSERT #test VALUES ('20140202',300.56)
INSERT #test VALUES ('20140306',420.50)
INSERT #test VALUES ('20120505',600.55)
INSERT #test VALUES ('20120505',610.45)
 
 
SalesYear           Sales
 
2015-01-01 00:00:00 100,09
2015-01-01 00:00:00 200,12
2014-02-02 00:00:00 300,56
2014-03-06 00:00:00 420,50
2012-05-05 00:00:00 600,55
2012-05-05 00:00:00 610,45
and then we will created simple pivot table for sum years
but for understanding logic creation pivot table
i will use in this example
with «Case » querying
SELECT
 "2015"=SUM(CASEWHENYEAR(SalesYear)=2015 THENSales ELSENULLEND),
 "2014"=SUM(CASEWHENYEAR(SalesYear)=2014 THENSales ELSENULLEND),
 "2012"=SUM(CASEWHENYEAR(SalesYear)=2012 THENSales ELSENULLEND)
FROM#test
 
 
2015    2014    2012
300,21  721,06  1211,00
and query results give us sum sales for every years
and have a one quession hier
what is a logic this query and what we need update this query by using without case or pivot queries?
in this example
the logic one
we will summarized all Sales by categories years «2015» «2014» «2012»
and another logic
if salesyear is 2015 then we will sum sales in 2015 year but in another year we will update this sal to  0
CASE WHEN YEAR(SalesYear)=2015 THENSales ELSENULLEND
and how we will update this logic to another ?

Power of Microsoft Sql Server give us awesome function
this is «SIGN»

characteristic of  «SIGN» function that return 1,-1,0 from the sql formula

for example

select sign(5+10) as"return value sign"
 
return value sign
1
 
-------------------------------------------------
select sign(5-10)  as"return value sign"
 
retur nvalue sign
-1
 
------------------------------------------------
select sign(5-5)   as"return value sign"
 
return value sign
0
then power of this function we will created our pivot table without pivot or case query
SELECT
 "2015"=SUM(Sales*(1-ABS(SIGN(YEAR(SalesYear)-2015)))),
 "2014"=SUM(Sales*(1-ABS(SIGN(YEAR(SalesYear)-2014)))),
 "2012"=SUM(Sales*(1-ABS(SIGN(YEAR(SalesYear)-2012))))
FROM #test
2015    2014    2012
300,21  721,06  1211,00
and you can see this article in my msdn wiki page too:
SQL SERVER : Creating Pivot Table With sign() Function