All about locking , blocking and deadlocking ....Part 5

This is the last one in this series .Let me know if you have any comments : Good or Bad (",)

How to reduce blocking and deadlocks:
I can give you one answer from my own and then we will see more : Do not commit the stupidity that I did in the example in the previous post (deadlock example).

As a DBA
-> Make sure that there are proper Indexes on tables and that they are actually being used.sometimes we feel relaxed after creating the indexes .But the query might be written in such a way that , that particular index might not be used and we might be doing scan instead of seek .One Such example is not using SARGable arguments (Now dont ask whats that ..we will discuss some other day or refer BOL).

-> Make sure you rebuild the indexes regularly based on the fragmentation level (you might be having so many scripts to do that).Do not bother to rebuild if the number of pages are less than 1000.rebuilding index will recompute the stats

-> Check if the Query is doing parallelism .If yes then Try everything else before running the query with maxdop hint.Many a times under CPU pressure the ECIDs have to wait on CPUs as its been taken by other threads.Thus causing blocking and or deadlocking .

-> Although the auto-update stats is ON by default.It does not mean that you should not update the stats .I will not cover this fact in detail here .Check auto-update statistics in BOL and you will know a lot ....trust me .

-> Any query that is going to do sorts in tempdb will surely take a lot of time as compared to in database sorting .For more on this check the facts on my Blog entry http://ms-abhay.blogspot.com/2009/11/sorts-spills-in-tempdb-due-to-incorrect.html

-> make sure that you are not facing disk IOs contention even after following steps .Perfmon will show high Disk queue length .Normally DQL of 2 per spindle is OK

-> Check on what resource the query is waiting using sysprocesses .It might be Network IO as well .

-> When you do a bulk insert disable the indexes and enable them during the time of least activity so that it can catch up to the new rows and update the stats accordingly .Or better go for rebuilding it after the bulk insert or delete.

-> Do not play with the locks option in sp_configure .I would suggest to keep the default setting of 0.

-> Deadlocks can occur due to memory pressure .When concurrent requests are waiting for memory grants that cannot be satisfied with the available memory,a deadlock can occur.For example,two concurrent queries,Q1 and Q2,acquire 10MB and 20MB of memory respectively.If each query needs 30MB and the total available memory is 20MB,then Q1 and Q2 must wait for each other to release memory.This results in a deadlock.


As a Developer

-> Use Temp tables instead of temp variables .Or test both and see which one works better .There is a great debate outside on this topic.

-> Use as less Adhoc queries as possible .There might be situations where too many adhoc queries kicks off the cached plans of SPs and in turn those SPs will
again be recompiled.If you want to use Adhoc queries use EXEC or sp_executesql and the plans will be cached.E.g. exec ('select * from t2') .First time it took 10 seconds .Second time it took 5 seconds.

-> Keep Small batches and transactions .Do not run a lot of transactions from one connection or SPs and try to achieve everything in one go.

-> Try to avoid Sorts in tempdb by creating and calling the temp table from the same SP and not from other SP .Google or Bing on SORT IN TEMPDB

-> Its not always true to say that more tempdb files will improveperformance .Your SAN should be capable enough to do that .

-> Choose Isolation level as per requirement .If you can live with read-uncommitted , go for it .

-> Its not bad to go for Snapshot isolation level but it will stress your tempdb.

-> Keep your table normalized so that you use the DISTINCT clause as less as possible .But make sure you do not over normalize the tables else you will be
ending up making your query more complex to understand for optimizer as it will have more joins .

-> One of the most important reasons for blocking and deadlocking could be lock escalation .If you have a slightest doubt that your query is going for lock
escalation , use Lock:Escalation event in profiler and see if its actually happening .If you find it happening , check if the same SPID is the blocker or
being blocked as well .you can disable lock escalation by enabling trace flag 1211 But I would not suggest that.To avoid lock escalation modify your query
to take less locks.

Tip :
Mostly I have seen that, if the deadlocking issue comes right after the application goes live then its the application fault (the way it synchronizes the
access to the data) .But if the application owner says that everything was fine and he is facing the deadlocks all of a suden then some major data change
might have happened in the tables that might be causing blocking and deadlocking .One such cause is " Parameter Sniffing ".

Sometimes we do a select in such a way that we crosses the threshold of in-database sort and the sort goes to tempdb
(http://ms-abhay.blogspot.com/2009/11/sorts-spills-in-tempdb-due-to-incorrect.html) .In this case we need to keep on testing the query until we find the threshold where the query spills the sorts in the tempdb and reduce the value of the column in the where clause.

Both Developer and DBA has to work together in resolving such issues.


SET DEADLOCK_PRIORITY Option

Sometimes , you might wish that in any case a particular transaction should not be treated as a victim in the event of deadlock.
In that case you can set the deadlock_priority of the trasnaction higher than others.An example could be a case of an update and select , you might be ready
to rollback the select but you want the update to be successful .

Which session is chosen as the deadlock victim depends on each session's deadlock priority:
-If both sessions have the same deadlock priority, the instance of SQL Server chooses the session that is less expensive to roll back as the deadlock victim.
For example, if both sessions have set their deadlock priority to HIGH, the instance will choose as a victim the session it estimates is less costly to roll
back.

-If the sessions have different deadlock priorities, the session with the lowest
deadlock priority is chosen as the deadlock victim.

Unresolved Deadlocks
You need not to bother about it .If it happens open a case with Microsoft as a prooduct bug .Either they have the hotfix or they dont .
In the error log you will find the deadlock graph and the Dump information .

*******************************************************************************
2007-09-12 11:43:53.51 spid4s *
2007-09-12 11:43:53.51 spid4s * BEGIN STACK DUMP:
2007-09-12 11:43:53.51 spid4s * 09/12/07 11:43:53 spid 4
2007-09-12 11:43:53.51 spid4s *
2007-09-12 11:43:53.51 spid4s * Unresolved deadlock
2007-09-12 11:43:53.51 spid4s *
2007-09-12 11:43:53.51 spid4s *
2007-09-12 11:43:53.51 spid4s *
*******************************************************************************
You will also see Cost:(N/A) and deadlock victim=process0 in the deadlock graph.


Few references
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://technet.microsoft.com/hi-in/library/cc966425(en-us).aspx

How to resolve blocking problems that are caused by lock escalation in SQL Server
http://support.microsoft.com/kb/323630

All about locking , blocking and deadlocking ....Part 4

continuing from the previous 3 entries ............

Blocking
So far we have executed many queries and seen blocking as well.But I would still like to discuss blocking a bit more ...
Use sysprocesses DMV/Table to find blocking .This has 3 advanages .One that you will see only the blocking SPIDs and not all the SPIDs by using sp_lock.
Two , you will be able to see head of the blocking chain. Three , you wil be able to see on what resource the SPID is waiting for .Once you have all the knowledge above you can surely use sp_lock to get some more information .

Lab

1st User
set transaction isolation level serializable
begin tran
update t1 set c1 = 3000 where c2 < 3000
--rollback

2nd user
set transaction isolation level serializable
begin tran
select * from t1

Now check the sysprocesses DMV (I am using 2005) :
select * from sys.sysprocesses where blocked <>0

You will notice that lastwaittype is LCK_M_IS
This means that the SPID which is being blocked is actually waiting for an IS lock to grab but cannot and being blockd by another spid.
The SPID that is blocking is shown in the "Blocked" column .
You can cross check by running sp_lock .

Now if you run one more query :
set transaction isolation level serializable
begin tran
select * from t1

You will notice the now there are 2 rows in the sysprocesses DMV where blocked <>0
here you will see :

SPID A is blocked by SPID B --> SPID B is blocked by SPID C
So who is the actual culprit ..yes its C and is called as the head of the blockign chain .


Now a tough question for you :
Why SPID A is blocked by SPID B and not C ? .....Think :) ..


Deadlocks
you have already seen blocking ..
Lets try our hands at creating a deadlock.I am not going to explain what is a deadlock .

For the benefit of all I will keep the Isolation level to serializable .
It will help us in creating deadlocks easily .

Lab
First do this :
select count(*) from t2
select count(*) from t1

If the number of rows are same then we are good to go .
If not, then drop the table with less number of rows and recreate it with the help of another table .
How to Do it .......check previous updates ...



First Window :
set transaction isolation level serializable
go
begin tran
update dbo.t3 set c2 = 230 where c2 > 1000 and c3 > 700

Second Window :
set transaction isolation level serializable
go
begin tran
update dbo.t2 set c3 = 230 where c2 > 1000 and c3 > 700


First Window :
begin tran
select * from t2
--rollback

Second Window :
begin tran
select * from t3
--rollback


You will see that the SPID in second window will be the deadlock victim:

Msg 1205, Level 13, State 56, Line 2
Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Like this you can create many deadlocks

How to capture the deadlock Graph
There are 2 ways :

- Use Traceflag 1204 <-- for 2000 and 1222 <-- for 2005 and above.
- Use WMI class deadlock_graph to capture the graph for SQL Server 2005 and above(for 2000 its a bit tricky .Hence not mentioning it here.)

Using TraceFlag 1222
dbcc traceon(1222 , -1)
go

Create the deadlock (I am creating the same one as mentioned above)

Using WMI Class and SQL Server Agent
Please go through one of my previous post : http://ms-abhay.blogspot.com/2009/09/wmi-alert-for-deadlocking-no-need-to.html

How to read the deadlock graph
Check the Error logs and copy the deadlock information on a separate notepad window.
You can find my analysis below:

deadlock-list
deadlock victim=process968d48
process-list
process id=process968d48 taskpriority=0 logused=543996 waitresource=OBJECT: 8:629577281:0 waittime=3359 ownerId=29121 transactionname=user_transaction lasttranstarted=2009-12-28T15:40:39.240 XDES=0x3e8f838 lockMode=IS schedulerid=2 kpid=6124 status=suspended spid=52 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-12-28T15:40:56.500 lastbatchcompleted=2009-12-28T15:40:39.413 lastattention=2009-12-28T15:31:16.907 clientapp=Microsoft SQL Server Management Studio - Query hostname=abchaudh hostpid=1336 loginname=abchaudh\Abhay isolationlevel=serializable (4) xactid=29121 currentdb=8 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
executionStack
frame procname=adhoc line=2 stmtstart=26 sqlhandle=0x02000000bea8742faf157cd9e8e65d380d705fefe8dfb182
select * from t3
inputbuf
begin tran
select * from t3
process id=process969018 taskpriority=0 logused=576116 waitresource=OBJECT: 8:597577167:0 waittime=10000 ownerId=28599 transactionname=user_transaction lasttranstarted=2009-12-28T15:40:34.780 XDES=0x3e8f290 lockMode=IS schedulerid=2 kpid=8140 status=suspended spid=53 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-12-28T15:40:49.873 lastbatchcompleted=2009-12-28T15:40:34.907 lastattention=2009-12-28T15:27:27.483 clientapp=Microsoft SQL Server Management Studio - Query hostname=abchaudh hostpid=1336 loginname=abchaudh\Abhay isolationlevel=serializable (4) xactid=28599 currentdb=8 lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200
executionStack
frame procname=adhoc line=2 stmtstart=26 sqlhandle=0x02000000b7cdeb20804433e7571f29d85516ff688d005724
select * from t2
inputbuf
begin tran
select * from t2



resource-list
objectlock lockPartition=0 objid=597577167 subresource=FULL dbid=8 objectname=deadlocktest.dbo.t2 id=lockcc7f700 mode=X associatedObjectId=597577167
owner-list
owner id=process968d48 mode=X
waiter-list
waiter id=process969018 mode=IS requestType=wait
objectlock lockPartition=0 objid=629577281 subresource=FULL dbid=8 objectname=deadlocktest.dbo.t3 id=lockcc7f780 mode=X associatedObjectId=629577281
owner-list
owner id=process969018 mode=X
waiter-list
waiter id=process968d48 mode=IS requestType=wait




Analysis :
----------
Divide the output into 2 parts : Deadlock list and resource list

process968d48
--------------
logused=543996
waitresource=OBJECT: 8:629577281:0 <-- here 8 is DBID ,629577281 is the object ID and 0 is heap
lockMode=IS
spid=52
sbid=0 <-- Its the Batch ID .It will be 0 until the application is using MARS (check BOL for MARS)
ecid=0 <-- zero means no paralelism is going on (Refer BOL for Query Parallelism)
transcount=2 <-- We are running 2 transaction under this SPID (select and update)
isolationlevel=serializable
currentdb=8
procname=adhoc (select * from t3) <-- Its an adhoc query
inputbuf =
begin tran
select * from t3


process969018
--------------
logused=576116
waitresource=OBJECT: 8:597577167:0
lockMode=IS
spid=53
sbid=0
ecid=0
transcount=2
isolationlevel=serializable
currentdb=8
procname=adhoc (Select * from t2)
inputbuf=
begin tran
select * from t2


Story
---------
-> At the time of deadlock 2 queries were running (under SPID 52 and 53) .
-> The processes were process968d48 for SPID 52 and process969018 for SPID 53
-> process968d48 took X lock on table T2 and requested IS on table T3 ..which was blocked by process969018
-> process969018 took X lock on table T3 and requested IS on table T2.. which was blocked by process968d48
-> deadlock victim is process968d48

Why process968d48 is deadlock victim ?
Because it has less undo to rollback .

How do you know if it has less undo ?
See log used values .

Rest ..........in next blog ....keep looking ...

All about locking , blocking and deadlocking ....Part 3

Isolation Levels

Read uncommitted
An isolation level where Dirty Reads , non-Repeatable reads and Phantom reads are possible .
least amount of blocking.

Read committed
An isolation level where non-Repeatable reads and Phantom reads are possible .
Reades will block writers only until the read is going on .But writers will block readers

Repeatable read
An isolation level where Phantom reads are possible .

Serializable
An isolation level where Dirty Reads , Repeatable reads and Phantom reads are not possible .

Snapshot
This Isolation keeps the previous committed copy of the row in tempdb and hence avoids blocking .It does no Dirty reads , Phantom reads or non-repeatable reads.
I will not be adding any Demos for this Isolation level .You can use all the demos give so far and in future .The only points you need to remember are :

-> before setting the isolation level to snapshot do
alter database deadlocktest set allow_snapshot_isolation on
go

-> Check the snapshots and rew version using these DMVs
sys.dm_tran_active_snapshot_database_transactions
sys.dm_tran_active_transactions
sys.dm_tran_current_transaction
sys.dm_tran_transactions_snapshot


Demo : Dirty Read
I hope you have all the Tables with you that we have created before .
If not , recreate them and we are ready to go :

1st Window :

set transaction isolation level read uncommitted
go
begin tran
update t1 set c1 = c1+5
--rollback

2nd Window :
set transaction isolation level read uncommitted
go
begin tran
select * from t1
--rollback

There is no blocking and the data that is not committed was read in window 2.This is called as Dirty read where an uncommitted data can be read.
This is only possible in read uncommitted .Try changing the isolation level to read committed and you will see blocking .



Demo : Non-repeatable Read
Since its possible in read-committed isolation level as well , I am usihg it instead of read - uncommitted isolation level .

1st Window
set transaction isolation level read committed
go
begin tran
select * from t1 where c1=240
--rollback

2nd Window
set transaction isolation level read committed
go
begin tran
update t1 set c1=240 where c1>200
commit

3rd Window
begin tran
select * from t1 where c1=240
--rollback


I know your confusion and anger .You might say I am stupid and its obvious that once the data is committed we can see its committed values :) ...
But lets imagine this :

-> The select in the first window is on millions of rows and is still running .
-> The update in the second row quickly comppleted in few mili seconds .
-> The third window started another select .
-> Finally both 1st and 3rd transactions finished .


The 1st Select * will see a different value and the second one will see a different one .

Am I right .........:D

But my question is Why the update transcation was allowed to change the data when there was a select on the whole table in the 1st window ??
If that update is blocked then I cannot see the changed values at all.
Why the update was not blocked even though I am doing a Select under begin tran and still not committed it ??
Why it looks like the S-lock is compatible with X-lock (selecy takes S lock)??

This is called as non-repeatable read .

The answer lies in the property of read-committed Isolation level.
In this Isolation level or in read-uncommitted isolation level , the S-locks are not kept till the transaction is rolledback or committed.
As soon as the read is over the lock exits .

And that is the reason you were able to update the rows .

Question
In one window you are doing select * from Table X under begin tran.
In another window you are doing Drop table X .
Will you be able to drop the table ??

I am sure you know the answer .Still try it yourself.
Also ,Try the above lab (where you are updating the rows to 240) in repeatable read or serializable mode.


Demo : Phantom reads
Phantom reads occur when an insert or delete action is performed against a row that belongs to a range of rows being read by another transaction. The transaction's first read of the range of rows shows a row that no longer exists in the second or succeeding read as a result of a deletion by a different transaction.Same is for insertion .

Imagine , if you are doing select * from customers table where date > 12/12/2012 and date < 15/12/2012
Another transaction inserted 200 more rows in this range after the first transaction read it (but the transaction is not yet committed).The third transaction also did a selsct and got 200 more rows ...


Be ready to get confused again ....but I will try my best not to get you confused ...

Lets create a small table

create table phantom (p1 int,p2 int)
go
insert into phantom values (1,2)
go 10
insert into phantom values (3,4)
go 10
insert into phantom values (5,6)
go 10
insert into phantom values (7,8)
go 10


Ist Window

set transaction isolation level repeatable read
go
begin tran
select * from phantom where p1 > 1 and p2 > 4
[remember this transaction is still running on millions of rows and trying to fetch the data]


2nd window
set transaction isolation level repeatable read
go
begin tran
insert into phantom values (5,8)
commit

[Assume here that the first transaction has already read the rows falling in to the range 1 and 4 and i s still running ....while second transaction committed very quickly.]

3rd window
set transaction isolation level repeatable read
go
begin tran
select * from phantom where p1 > 1 and p2 > 4


By the end when both 1st and 3rd trasnaction finishes there will be a difference of 1 row.
Try the same in serializable i.e. inserting the new row in 2nd window and see if you are allowed to do so :D .......
Fire me any question you have .........

Next we will discuss about Blocking and Deadlocking ...

Games Port Play

Yesterday evening, I was winding up when my collegue came and said :

"I have a 2008 instance which when I connect to shows me the right insance name but the version is 3073 which is 2005 and the databases also belongs to 2005 instance" .
This means that , I invited you to my house #X, you followed a route and came to my house .But you found that its not my house and somebody else's house Y even though the house number is correct .How is that possible ...........

After resolving the issue I reproduced the issue for better understanding .

screenshot :

I have one 2000 instance , one 2005 instance and one 2008 instance showing below




I did something and here is the new screenshot :




Test 1:
-----------
Assuming that there might be some issue with SSMS / SQLWB (in short assuming management Studio got corrupted ), I connected wih the 2008 instance using SQLCMD



April is 2008 instance but its showing as 2000 instance .So Management studio is not corrupt and we need not uninstall it .


Test 2 :
-----------
Tried to connect to April using Named Pipe protocol instead of TCPIP



It was successful and showing the right version 10.0.1600.So there is no isue with the Named pipe query .




Test 3 :
----------
Found the port on which April is listening through registry and tried to connect using the port



and the result was :



It means that April is not listening on PORT 1414.Then on which port its listening ??
So I decided to create an alias to force April to listen on 1414 port .
I used Client Network utility (CLICONFG) to create the alias .
Guess what ..........there was already an alias which was meant for April but was pointing to port 1918 .
On 1918 SQL Server 2000 was listening ....[used netstat -abn] ......
Removed that alias and created the new one forcing April to listen on its own port 1414....
And it worked .......2005 also had the same issue ...

Not sure if its a Bug as its possible only from higher version to lower version and not from lower to higher version
i.e. its possible that SQL 2008 or 2005 listen on 2000 port but not vice versa ...

Let me log the bug and see what happens ........

Hence the mistry was resolved ...

Happy learning .....

All about locking , blocking and deadlocking ....Part 2

Now , we know about :

Locks
Types of locks (S,U,X)
Lock compatibility with each other .

Before going further , let me breifly explain you about Intent locks.Mainly Its IS and IX .

Both Intent-Exclusive and Intent-Shared are used so that there should be no problem for Shared or Exclusive locks .
In other words the traffic police blocks the read if any big minister is about to come , fearing that the minister might get late or stopped if the the road is not blocked for public used .But the road will be actually used only when the minister's vehicle will come .

So the intent locks are just a way to ensure better concurrency in accessing the data .
For other types of locks please refer BOL or the links I referred in my previous post.

Lets move forward ....

Lock Escalation
-----------------
Each lock takes 96 bytes worth memory (lock manager gives it).So the more locks are there the more memory will be used ....

Under certain circumstances the Engine realizes that the resources are gettin gwasted and that it needs to change the several smaller level locks to fewer higher level locks.This is called as lock escalation .You can overrride by using locking hints (use BOL for locking hints )


these circumstances are :

1)
When a single Transact-SQL statement acquires 5,000 locks on a single table or index.

2)
When the number of locks in an instance of the Database Engine exceeds memory (60% of Max server memory) or configuration thresholds(using sp-configure 'locks' option).


Test :
--------
Lock escalation LAB
====================

Under sp_configure setting for 5000 locks
------------------------------------------

use master
go
sp_configure 'locks',5000
go
reconfigure with override
go


Restart the instance as this setting is non dynamic .Then copy the code below in one query window .
After creating the table run the first update statement unde begin tran and notice the number of locks using sp_locks.
Then rollback the tran and try with another update statement .You will notice that before the number of locks reaches 60% of 5000 lock escalation occurs.

USE [deadlocktest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[t1](
[c1] [int] NULL,
[c2] [int] NULL,
[c3] [int] NULL,
[c4] [char](5000) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
declare @val int
set @val = 2000
while @val >0
begin
insert into t1 values (@val,@val,@val,@val)
set @val=@val-1
end
go

update t1 set c2 = 1600 where c1 > 700 and c1 <1000
go
update t1 set c2 = 1650 where c1 > 900 and c1 <1200
go
update t1 set c2 = 1700 where c1 > 1100 and c1 <1400
go
update t1 set c2 = 1800 where c1 > 1300 and c1 <1600
go
update t1 set c2 = 1850 where c1 > 1500 and c1 <1800
go
update t1 set c2 = 1900 where c1 > 1700 and c1 <2000
go


begin tran
update dbo.t1 set c2 = 231 where c2 > 1800 and c3 > 700
--rollback
--sp_lock

begin tran
update dbo.t1 set c2 = 231 where c2 > 1700 and c3 > 700
--rollback
--sp_lock

begin tran
update dbo.t1 set c2 = 231 where c2 > 1655 and c3 > 700
--rollback
--sp_lock

begin tran
update dbo.t1 set c2 = 231 where c2 > 1654 and c3 > 700
--rollback
--sp_lock

begin tran
update dbo.t1 set c2 = 231 where c2 > 1650 and c3 > 700
--rollback
--sp_lock

begin tran
update dbo.t1 set c2 = 231 where c2 > 1600 and c3 > 700
--rollback
--sp_lock

begin tran
update dbo.t1 set c2 = 231 where c2 > 1550 and c3 > 700
sp_lock
rollback


Under sp_configure setting for 0 locks(which means maximum)
-----------------------------------------------------------

The table and data is same .Just run the update queries below as you were running above and roll them back after checking the number of locks.

begin tran
update dbo.t1 set c2 = 231 where c2 > 1800 and c3 > 700
--rollback
--sp_lock

begin tran
update dbo.t1 set c2 = 231 where c2 > 1700 and c3 > 700
--rollback
--sp_lock

begin tran
update dbo.t1 set c2 = 231 where c2 > 1655 and c3 > 700
--rollback
--sp_lock

begin tran
update dbo.t1 set c2 = 231 where c2 > 1600 and c3 > 700
--rollback
--sp_lock

begin tran
update dbo.t1 set c2 = 231 where c2 > 1550 and c3 > 700
--sp_lock
--rollback

begin tran
update dbo.t1 set c2 = 231 where c2 > 100 and c3 > 200
--rollback
--sp_lock


After knowing about locks , their compatibility and lock escalation lets talk about Isolation levels .
Broadly , there are 4 Isolation levels :

-Read Uncommitted
-Read committed
-Repeatable read
-Serializable

SQL Server 2005 introduced to more choices
-READ_COMMITTED_SNAPSHOT option for Read committed Isolation level
-Snapshot Isolation Level

We differentiate the Isolation levels on the basis of Dirty Reads , Non-repeatable reads and Phantom reads.
we need to know about them first .But if I start explaining them I will use these isolation levels again and you will be confused.
So I will be explaining these three reads as well as Isolation levels in parallel .
for theory purpose :

Dirty reads : We can read the uncommitted data
Repeatable read : A transaction Selects the data without committing >> B transaction is able to modify the data >> A transaction sees the changed data .
Phantom reads : the ability to insert the data by one transaction in to the range of values which is being read by other transaction .

Rest ...............in next blog ..

Happy learning ..

All about locking , blocking and deadlocking ....Part 1

Locks
------
Locks are the objects that helps in access synchronization for objects like Schema , database , tables , extents ect .


Types of locks
---------------

Shared (S) locks
-----------------
allows us to read the data by running the select queries .During the time Shared lock exist no other transaction can modify the data being read .The good part it that these locks are released as soon as the select completes even though the transaction might still be running .But there is an exception .This does not hold true if the isolation level is Repeatable Read or serializable .Why ???? because the Shared locks are not released and are kept till the transaction commits or rolls back .


Test
-----

1st Qquery window
-------------------
Create table Shared_test(sh int)
go
declare @sht int
set @sht=50
while @sht >0
begin
Insert into shared_test values (@sht)
set @sht=@sht-1
end
go
begin tran
select * from shared_test


2nd Qquery window
------------------
sp_lock
go

/*you will notice that there is no lock on that object*/

drop table shared_test
go

/*The drop will be successful*/

Again 1st Qquery window
-----------------------
Rollback
go
set transaction isolation level repeatable read
go

Create table Shared_test(sh int)
go
declare @sht int
set @sht=50
while @sht >0
begin
Insert into shared_test values (@sht)
set @sht=@sht-1
end
go
begin tran
select * from shared_test


Again 2nd Qquery window
-------------------------
sp_lock
go

/*you will notice that there is no lock on that object*/

drop table shared_test
go


Result:
---------
You will see that there are a lot of shared locks on the RID (row identifier for each row)and the Drop will be blocked (try doing sp_lock from another window to see the SCH-M lock in wait state).

Compatibility
--------------
Shared locks are compatible only with Shared , Intent Shared and update locks.



Exclusive(X) locks
-------------------
Its the most strict lock .Unless you are in read-uncommitted isolation level or using NOLOCK hint you will be blocked...

Compatibility
---------------
Exclusive lock will only be compatible with SCH-S(Shared Schema) and RI-N(Insert Range Null) locks.


Update (U) locks
-----------------
Before understanding about the update locks let me tell you that whenever we update , insert or delete we do two operations : Searching where to insert update or delete and doing that action .

Seaching is done using Shared locks and completing the action of insert , update or delete will be done by the exclusive lock (both covered above).

Other important point that you have read above (search where (",)..) that in isolation level higher than read committed the shared locks are kept and not released till the end of transaction .So Imagine that there is no UPDATE locks and you and your friend both are updating x rows to a different values in the same table from 2 different client machines , at the same time and in isolation level repeatable read (we will learn about Isolation levels in sometime ) .I forgot to mention that the transaction is not committed.since the shared locks are compatible with shared locks , you both will be granted the shared locks ....fine..

Now , the X mode lock of A friend is not compatible with S mode lock of another friend named B ..so Blocking occure as X mode is not able to get converted to S mode .Similarly , the same will happen with B friend as well (I will leave it up to you to guess)...and this will cause deadlock..


Update lock will ensure that No two transactions can try to update the same resource at the same time ...further till the update resource is doing the searching it will show you as U in sp_lock .The moment it tries to modify the row , U mode disappear and X mode is shown.

Test :

I am sure you still have the same two queryanalyzer windows open with repeatable read isolation level.


1st Query Window
------------------
There is already a select going on .But for your understanding I am typing the same again :

begin tran
select * from shared_test


2nd Query Window
-----------------
If you have not yet stopped the previous transaction of dropping the table , then stop it .
run this command

begin tran
update Shared_test set sh=(sh*2) where sh > 35

It will be blocked for sure

1st Query Window
------------------
sp_lock
go


Result:
---------
among other locks, you will see(modified for better view) :

51 RID 1:18773:0 X CNVT
51 TAB IX GRANT
51 PAG 1:18773 IX GRANT
51 RID 1:18773:0 U GRANT
53 RID 1:18773:0 S GRANT
53 PAG 1:18773 IS GRANT



Here ,
18873 is the page id
1 is the datafile
0 is the row id


1)
SPID 53 took IS lock on page 18773 and then S lock on rowid 0 fearing if someone tries to modify the page should be blocked ....agreed

2)
SPID 51 took U lock row id 0 of page 18773 which is allowed as S and U locks are compatible.Remember internally its also S lock at this moment .The moment it tries to change something it will be converted to X lock and will no forther be U lock any more ---> 51 then moved ahead and before trying to take the X lock on the RID it first took the IX lock on the page (in case someone drops the object which its trying to modify)which is granted because IS and IX are compatible .

But when U lock on RID 1:18773:0 tried to convert in to X lock , It got blocked and you can see CNVT ..

But this test was not exactly the example that I gave when I was talking about the U locks

So lets try what i said ...2 updates on the same table and same rows ...


Test
-----
make sure you rollback all the transactions on the 2 QA windows and remove all the query text .

1st Window
------------
Set transaction isolation level repeatable read
go
begin tran
update Shared_test set sh=(sh*2) where sh > 35

2nd window
------------
Set transaction isolation level repeatable read
go
begin tran
update Shared_test set sh=(sh*2) where sh > 35

1st Window
------------
sp_lock


Results:
-----------

51 741577680 PAG 1:18773 IU GRANT
51 741577680 RID 1:18773:0 U WAIT
51 741577680 TAB IX GRANT
53 741577680 TAB IX GRANT
53 741577680 PAG 1:18773 IX GRANT
53 741577680 RID 1:18773:0 X GRANT


1)

53 took IX on page before X on rowid (I will explain later on why IX and IS are taken) which is right and then IX on table

2)

53 also took IX on table (IX and IX are compatible) and IU on page (IX and IU are compatible) .But U and X are not compatible and hence blocking.Had there been no U it would have been a deadlock ....


Like this do some testing on your own and gain some knowlwdge..


Referance :
Lock Compatibility (Database Engine):
http://msdn.microsoft.com/en-us/library/ms186396(SQL.90).aspx

SP_LOCK :
http://msdn.microsoft.com/en-us/library/aa238824(SQL.80).aspx

Happy learning !!!!!

Let me know what you would like me to post and research upon :)

Dear Blog Visitors ,

So far I have been blogging on the issues i have faced and research done related to SQL Server 2005 (not yet started 2008).

However , This might or might not interest you as the probability of you facing the same issue as I have faced is rare ..

So for the benefit of all , I am ready to assist anyone on the SQL Server issues or any kind of request ..

Please add your requests as comments or mail me at hi_abhay78@yahoo.co.in / 1978abhay@gmail.com

Happy learning ...

Sorts Spills in tempdb due to incorrect behavour by Query optimizer...

This one is really bothering me .I tried n tried n tried but failed to understand why is it happening ....

To give you a brief description , A query runs in 25-45 ms for x rows but the same query runs 15-20 times slower if i just add 1 row or a dozen more rows in some other caese (you will see it later)...If i see the execution plans , its totally identical .The only difference is that , the query which is slower does sorting in tempdb instead of in the user database where its been executing ...

Here we go :

create table SQLDBA (a1 int primary key clustered,a2 int,a3 char(2000))
go

begin tran
go
declare @i int
set @i=1
while @i <=200000
begin
insert into SQLDBA values (@i,rand() * 2000000,replicate('a',2000))
set @i=@i+1
end
commit tran
go

update statistics SQLDBA with fullscan
go


So , we created a table with 3 columns and inserted 200000 rows to it .
Now I will run 2 queries :

set statistics time on
go

/* Good Query */
declare @a1 int,@a2 int , @a3 char(2000)
select @a1=a1 ,@a2=a2,@a3=a3 from SQLDBA
Where a1 <3000
order by a2
go

/* Bad Query */
declare @a1 int,@a2 int , @a3 char(2000)
select @a1=a1 ,@a2=a2,@a3=a3 from SQLDBA
Where a1 <3079
order by a2
go

Notice that there are only 79 more rows .But if you execute them on SQL Server 2005 SP3 as I did with 2 GB RAM , the 1st query runs in approax 15-22 secs .While the second query runs in 320-410 secs.Try it yourself .You will see some difference in duration as its hardware specific.Also , in my case it was running OK till 3078 rows . Yes, the execution time when a1 <3000 or a1 <3078 is almost similar .If I increase just 1 more row it degrades the performance .

NOTE : You might have to find out this threshold limit on your machine .In my case it was 3078 .The moment I use 3079 ..Booooommmmmmmmm

So why its happening :D ......

-Setting Statistics profile to ON showed that The Subtree cost is 2.5% more ..which is fine .

-Setting Statistics IO to ON showed :

Table 'SQLDBA'. Scan count 1, logical reads 1007, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 34 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

Table 'SQLDBA'. Scan count 1, logical reads 1034, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 344 ms.


So , looks like this is Neither a CPU issue (query that runs faster is taking more CPU time) nor an IO issue as there are no physical reads.


One thing that I found when I took the XML output of the execution plan was that the good query was always taking compile time 2 and the bad query was taking 0 ....this was again strange to me.

Totaly stuck ..........

Then I searched many DMVs related to query execution and used sys.dm_exec_query_memory_grants DMV which show the row for each query that is waiting for the memory or those waited and acquired but will not show the querries that do not have to wait for the memory grant .

surprisingly , it was showing only one query in the output and that was the query taking more time .Strange but true .Why its waiting for memory when there is enough RAM on my server and max server memory is unlimited.

sys.dm_exec_query_stats also shows that bad query is doing around 100 physical reads while good one is doing none.

Then i queried sys.dm_exec_query_memory_grants:

select granted_memory_kb,used_memory_kb,max_used_memory_kb
from sys.dm_exec_query_memory_grants
where session_id in (51,52)

found that good query is using very less memory out of granted but bad query using full and more then that .....how ........

Because whatever extra memory it needs for sorting is coming form tempdb :

select num_of_reads,num_of_bytes_read,num_of_writes,num_of_bytes_written
From sys.dm_io_virtual_file_stats(db_id('tempdb'),1)
go


So looks like Optimizer is not able to allocate the right amount of memory to the bad query such that it does not go to tempdb .......NOPE i am incorrect i think ..because if you see the number of rows in where condition the amount of memory given looks directly proportional .....S oallocating memory to the query does not seems to be a problem here ..the problem is why the query is using more memory that it needs to spill to tempdb .....

Let me give you one more example that will further open your eyes :D ...

set statistics time on
GO
declare @a1 int,@a2 int , @a3 char(2000)
select top 100 @a1=a1 ,@a2=a2,@a3=a3 from SQLDBA
Where a1 <3000
order by a2

Go

declare @a1 int,@a2 int , @a3 char(2000)
select top 101 @a1=a1 ,@a2=a2,@a3=a3 from SQLDBA
Where a1 <3000
order by a2

Now , these queries are identical in any respect except that in the second query I am doing select top 101 ...just 1 row extra ....and see the difference ...HELL ....
And the status is same here if you go through the DMVs etc ..Even if you wrap up in a SP or use different hints ....it wont help ...

However you will see that In this case i.e. the second one 1 MB of memory is given to both the queries and nonoe of them is consuming the complete memory given ...but still the bad query is doing Sort spills to tempDB .....So its proved that granting of memory to the queries by the optimizer is Correct ...but its the way query sorts in tempdb and also the usage of memory by the query that looks incorrect here ...



So whats happening ........ :D ...Why just one extra row makes the query to spill to tempdb for sorting ....(use profiler to see if sorting in happening)..


NOW , TRY THIS ....

On my machine i have 2GB RAM .....i capped max server memory to 100MB...
And then the query was running fine top 100 and top 101 and the previous query all are running well except that the bad query takes more time once in 7-8 attampts (sometimes less or sometimes more)....

Looks like something is related to memory regions or chunks ...not sure , I may be incorrect ....Something is really wrong .....


Filing a bug ..

Happy learning ...

Creating SQL Server Alerts using WMI classes- Part 1

When we create alerts or when we try to create script we search net a lot .We get some thing but we do not get all what we look for .And then we stop pursuing it ...

I was given a task to create SQl Server alerts (as many as I can).I could find a couple of scripts ready but that did not satisfy my requirement as everyone creats the scripts as per their needs .

So I decided to do everything on my own and hence sharing my experiance with you .
To know what kind of alerts you can create go through this file



You will be amazed to see that almost everything is covered here :-) ..

Let us start now by creating an alert that will notify us whenever a new database is created .

Pre-requsites :

-> Need to be SQL Server 2005 SP2 or above or SQL Server 2008 as there is a bug in RTM or SP1 .

-> We need to execute it in master (do not change the script to be run in your user database).This is because Master captures the metadata of all the databases including itself .If you create it in anyother database then it will not work .Other scripts will also work only for that user database .

-> Make sure the SQL Server Agent is started and make sure that "REPLACE TOKENS FOR ALL JOB RESPONSES TO ALERTS" option is checked under Agent properties -> Alert system .

-> Read the code a few times and understand it .You might have to change it as per your need .

CODE :
Part 1:

/*******************************************************************************************
* This script will create an Alert to Monitor Create DATABASE event
* The alert will run a job and the job will enter data in a table.
*******************************************************************************************/


/* Step 1: creating the table to capture the Event information */

USE Master
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Create_database]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Create_database]
GO

CREATE TABLE [dbo].[Create_database] (
[PostTime] [datetime] NOT NULL ,
[Instance] varchar(20),
[DatabaseName] varchar(20),
[computerName] Varchar(20),
[Loginname] Varchar(20),
[TSQLCommand] varchar (1000),
[RecordID] [int] IDENTITY (1,1) NOT FOR REPLICATION NOT NULL,
[Flag] [int] NOT NULL CONSTRAINT [DF_Create_database_Flag] DEFAULT ((0))
) ON [PRIMARY]
GO

CREATE INDEX [Create_database_IDX01] ON [dbo].[Create_database]([Posttime]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO

/*Step 2 : Creating the Job that will enter values into the table created above*/
/*Service account and sql operator option are optional and hence commented*/

USE [msdb]
GO

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Capture Create_Database Event')
EXEC msdb.dbo.sp_delete_job @job_name = N'Capture Create_Database Event', @delete_unused_schedule=1

GO
--DECLARE @ServiceAccount varchar(128)
--SET @ServiceAccount = N''
--DECLARE @SQLOperator varchar(128)
--SET @SQLOperator = N''

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Capture Create_Database Event',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=3,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Job for responding to Create_database events',
@category_name=N'[Uncategorized (Local)]',
--@owner_login_name=@ServiceAccount,
--@notify_email_operator_name=@SQLOperator,
@job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/*Step 3: Insert graph into LogEvents*/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert data into LogEvents',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'

INSERT INTO Create_database (
PostTime,
Instance,
DatabaseName,
Computername,
LoginName,
TSQLCommand
)

VALUES (
GETDATE(),
N''$(ESCAPE_NONE(WMI(SQLInstance)))'',
N''$(ESCAPE_NONE(WMI(DatabaseName)))'',
N''$(ESCAPE_NONE(WMI(ComputerName)))'',
N''$(ESCAPE_NONE(WMI(Loginname)))'',
N''$(ESCAPE_NONE(WMI(TSQLCommand)))''
)',
@database_name=N'master',
@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

/*Creating the alert and associating it with the Job to be fired */

USE [msdb]
GO

IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to Create_database')
EXEC msdb.dbo.sp_delete_alert @name=N'Respond to Create_database'

GO

DECLARE @server_namespace varchar(255)
IF ISNULL(CHARINDEX('\', @@SERVERNAME), 0) > 0
SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + SUBSTRING(@@SERVERNAME, ISNULL(CHARINDEX('\', @@SERVERNAME), 0) + 1, LEN(@@SERVERNAME) - ISNULL(CHARINDEX('/', @@SERVERNAME), 0))
ELSE
SET @server_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER'

EXEC msdb.dbo.sp_add_alert @name=N'Respond to Create_database',
@enabled=1,
@notification_message=N'Your Message',
@wmi_namespace=@server_namespace,
@wmi_query=N'SELECT * FROM Create_database',
@job_name='Capture Create_Database Event' ;

EXEC msdb.dbo.sp_add_notification @alert_name=N'Respond to Create_database', @operator_name=N'Test', @notification_method = 1
GO



--select * from master..Create_database


How it works :

As soon as the database is created WMI class create_database will generate an event and trigger the alert through SQL Server agent .The alert will in turn execute the job that will insert the event information to the table we have created .


Part 2:
This part will send a mail to the desired user that an alert has been fired .
This can be achieved by :

1) Using Database mail (including creating an operator)and choosing the option in the job itself for notifying the operator .

2) Using custom based CDO message via VBscript and execute it using XP_cmdshell as a job step.

Mail.vbs :
Set objEmail = CreateObject("CDO.Message")
objEmail.From = "abhay.chaudhary@in.ibm.com"
objEmail.To = "abhay.chaudhary@in.ibm.com"
objEmail.Subject = "Mail for you using CDO :-) "
objEmail.Textbody = "Hi sir this mail has been sent without using SQL Server and from WMI script."
objEmail.AddAttachment "C:\Script repository\Alerts\final Alerts\readme.txt"
objEmail.Send


3) Using the Stored procedure below :
/* Step 5: Create a stored proc for sending the [Create_database] information as .CSV file */

Create proc [dbo].[Create_database_rpt]
as
DECLARE @SQL varchar(2000)
DECLARE @date varchar (2000)
DECLARE @File varchar(1000)
select @date= convert(date,GETDATE())
SET @SQL = 'select * from [Create_database] where flag = 0'
SET @File = 'Deadlock report'+@date+'.csv'

EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'test',
@recipients = 'your email.com',
@subject = '[Create_database] report',
@body = '***URGENT***Attached please find the Create Database report',
@query =@SQL ,
@attach_query_result_as_file = 1,
@query_attachment_filename = @file,
@query_result_header = 1,
@query_result_separator = ' ',
@query_result_no_padding = 1,
@query_result_width = 32767

/* Step 6: Changing the flag to 1 so that next time this information is not sent*/
update dbo.[Create_database] set flag = 1 where flag = 0
go

Like this Try your hands on different alerts by going through the classes copied above .You can also go through the classes at MSDN :http://msdn.microsoft.com/en-us/library/ms186449(SQL.90).aspx

Hope this helps .I am also learning ropes on WMI and will post some good stuff as soon as I am ready .Keep reading as there is much more to come .

Happy learning .

Msg 3132, Level 16, State 1, Line 1

Today as soon as i reached office in the morning , an issue came up which was related to restoring of SQL Server database .The issue was that we were not able to restore one of the user database backups taken on some remote server .

The error we were getting was :

Msg 3132, Level 16, State 1, Line 1
The media set has 2 media families but only 1 are provided. All members must be provided.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

There was some .bak say test.bak file, which we were trying to restore and getting the error above.

This error says that there are 2 media families and only 1 has been provided .
Media families here means a Disk or a tape .If we take it as disk , then it means that "The backup was taken on two backup devices or say backup files (in case you have not created disk devices)" . This kind of backup is called as a srtipped backup where both the backup devices have some part of backup but not complete.

But the guy who took the backp says that he did nothing special that made the stripped backup i.e. he did not take the stripped backup at all .

Then how come the stripped backup took place on it own ....this was pretty strange.

I checked this issue through Bing and google and most of the people were saying the same that they never did intend to take the stripped backup .

Now, i had to prove the client that we are missing one backup device here or we are missing one .bak file , I did a small repro :


USE master
GO
EXEC master.dbo.sp_addumpdevice
@devtype = N'disk',
@logicalname = N'rest_issue',
@physicalname = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\rest_issue.bak'
GO

EXEC master.dbo.sp_addumpdevice
@devtype = N'disk',
@logicalname = N'rest_issue_1',
@physicalname = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\rest_issue_1.bak'
GO

backup database testdba to rest_issue,rest_issue1
restore database testdba from rest_issue <-- used only one mediaset

Msg 3132, Level 16, State 1, Line 1
The media set has 2 media families but only 1 are provided. All members must be provided.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.


restore database testdba from rest_issue,rest_issue1 <-- used both mediaset

Restore was successful..
So the error was genuine and working fine .

Further ,If you think that you will restore only 1 file i.e. data file and leave the log file using option partial , even that will not work as the backup for data file is also stripped.

Restore filelistonly will tell you the files in the backup set

Restore headeronly will give us the MediasetID number and other information

Restore labelonly will give us the BindingID number and other information

Restore verifyonly will fail with the same error if you use it on one file as it needs both the files


Solution :There is no solution to this issue you need to find out another device else it will not work (use restore headeronly and labelonly ,backupmediafamily, backupset for assistance) and use both the files to restore .Once you have it , you can issue this

command :
Restore database ibmdba from anoop_issue,anoop_issue1 with replace
OR Restore database ibmdba from disk = , disk = with replace

Why Hhis happens :)

This will happen if :

1) You have created two(or more) backup devices and taken backup on 2
(or more) devices or you can say stripped backup and while restoring yo uare using only one .

2) You are using GUI to take the backup without seeing that the backup is going to more than 1 device or files .If you select both the devices/files it will take stripped backups .If you insert 2 devices / files but select NONE of them then also it will happen (That should not happen and should give us the error "Please select a device") .And thats where the bug is .

So the BUG is :
-------------------------

If a user do not select a device in the backup set when more than one devices are existing , by default SQL Server selects all the devices and not giving the error "please select the device"....

If this default behaviour is by design then its not the correct design and should be changed .


Repro of this issue through GUI :
-> right click adventure works database > Tasks > backup
-> Under Destination remove anything if its mentioned by default.
-> Click on Add > a new box will pop up > select backup device you have created
-> you will get the device name > click on OK.
-> Again click on Add >a new box will pop up > select another backup device .
-> You will see that both the backup devices are unselected now (",).
-> If now you click ok it will take the backup .Ideally it should give the error that "please select the device".

I have filed a bug with MS through connect...

Happy learning

Error 14262:The specified server xxxxx019 does not exist

I got this error when I was trying to setup logshipping .I was strange
I checked the select * from sys.servers output and i was getting the entry of the server there .......whats wrong then .

I tried to ping that server using the server name .But it was timed out .I then tried to use the IP of that server while pinging with -a option and guess what ...
I found the different servername there ..... :D

this was the issue .Some admin changed the Server name which SQL Server did not pick u automatically .

i used sp_addserver and sp_drop server to add the right server and everything was on track ...

Regards
Abhay

MSDTC was unable to read its configuration information. (Exception from HRESULT: 0x8004D027)

I was installing SP2 on SQL Server 2005 x64 RTM ...Everything went fine but it got stuck at Notification Services and Client Tools .

The error SNIP is :
Error : Failed to install and configure assemblies D:\Program Files\Microsoft SQL Server (x86)\90\NotificationServices\9.0.242\Bin\microsoft.sqlserver.notificationservices.dll in the COM+ catalog. Error: -2146233087

Error message : Unknown error 0x80131501
Error description: MSDTC was unable to read its configuration information. (Exception from HRESULT: 0x8004D027)

So , there was some issue with MSDTC ....and I am no tthe MSDTC guy ......
If you do not install Notification services,reporting services and client tools , MSDTC errror will not pop up ....

Moreover there is an option to retry on the error window .

-> I checked and found that MSDTC service was running fine .
-> I also checked if my account has all the priveleges at the domain level bla bla bla ..

-> Finally , i uninstalled and reinstalled MSDTC [DOS >> msdtc -uninstall and then MSDTC -install >> start MSDTC if its not].

once done , I clicked on retry and it worked just fine ...
We had the similar issue on another server and we resolved it in similar way ...

Hope it helps you too ..

Regards
Abhay

Getting SQL Server inventory for all the instances of SQL Server 2005 and above

This one really ate my head but finally came up with something .We (thanks to Sethi who an equal partner in taking the pain) have completely automated it but due to a bug in Excel or Microsoft SQL Server I could not export all the data to one Excel sheet .Result is that you need to copy the output and paste it to the excel sheet .mail me any changeso you are looking for or if you have issue swith the output (hint : check the query tab in SSMS and change ot appropriately :D).

You need to have the CMS servers (SQL Server 2008 Express) and all the instances registered .


-> This script will run on 2000,2005 and 2008 and will be using SQL Server express CMS as well.
-> We can live without SQL Server 2008 CMS but then the code will be 364 lines x numbers of server .
-> This Script will first check if SQL Server is 2000 or above and will only execute the code based on that check .
-> We can also print the output in to excel sheet using openrowset and opendatasource but its throwing a bug when multiple instances try to update the excel sheet using distributed transactions .
-> So we have decided to get the output all together in Query Analyzer and paste it to the excel Sheet.
-> Let us know if you need that code as well and we will add it as comment.
-> The CMS should have connectivity to all the servers (It has to be the Central Server)





Part 1:
-------

-- Install SQL Server 2008 Express Edition and open the management studio (Start -> Run -> SSMS)
-- Go to View --> Registered servers
-- Right click on the Central Management Servers and select Register Central Management Servers
-- Select the Express instance and register it .
-- open the test.regsrvr file in SSMS .
-- You will see few values inside various TAGS starting with <> and ending with .
-- If you see line 48 you will see the server group name .In our case its "test":
/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/Test
-- Do a CTRL+ F and also choose replace .You can give any name instead of test and replace it or keep the same name .
-- Now search for the tag here you will see three instances registered .Its Line 72.
-- You can remove or add the instance names here .
-- All you need to do is copy the below code -> change the instance name -> and paste it back in the code just before the TAG:

/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/Test/RegisteredServer/abchaudh\ASLAN

-- Repeat the activity for as many instances as you want .
-- Now go to line 96 and you will see the individual instance information .The code will be like this :




/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/Test/RegisteredServer/abchaudh\ASLAN







/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup/ServerGroup/Test


abchaudh\ASLAN

abchaudh\ASLAN
DatabaseEngine
server=abchaudh\ASLAN;integrated security=true
None




-- Just change the instance name with your instance and paste it just before line 167 i.e. before the tag
-- Repeat this step i.e. add as many instances as needed be .
-- Thats it .Save this file and close it .
-- Go back to the CMS tab --> right click the CMS instance under Central management servers --> tasks --> Import
-- Import this test.regsrvr file .
-- You will see the instances registered under the 2008 Express instance.
-- Click on the Express instance and open a new Query .
-- Paste the code below in Part 2 and execute it .

NOTE : Make 2 different groups . One for SQL Server 2000 instances and another for 2005/2008 instances .Use the same test.regsrvr file .





Part 2:
--------
--Step 1: Setting NULLs and quoted identifiers to ON and checking the version of SQL Server
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'prodver') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table prodver
create table prodver ([index] int, Name nvarchar(50),Internal_value int, Charcater_Value nvarchar(50))
insert into prodver exec xp_msver 'ProductVersion'
if (select substring(Charcater_Value,1,1)from prodver)!=8
begin


-- Step 2: This code will be used if the instance is Not SQL Server 2000

Declare @image_path nvarchar(100)
Declare @startup_type int
Declare @startuptype nvarchar(100)
Declare @start_username nvarchar(100)
Declare @instance_name nvarchar(100)
Declare @system_instance_name nvarchar(100)
Declare @log_directory nvarchar(100)
Declare @key nvarchar(1000)
Declare @registry_key nvarchar(100)
Declare @registry_key1 nvarchar(300)
Declare @registry_key2 nvarchar(300)
Declare @IpAddress nvarchar(20)
Declare @domain nvarchar(50)
Declare @cluster int
Declare @instance_name1 nvarchar(100)
-- Step 3: Reading registry keys for IP,Binaries,Startup type ,startup username, errorlogs location and domain.
SET @instance_name = coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
If @instance_name!='MSSQLSERVER'
Set @instance_name=@instance_name

Set @instance_name1= coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
If @instance_name1!='MSSQLSERVER'
Set @instance_name1='MSSQL$'+@instance_name1
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;

Set @key=N'SYSTEM\CurrentControlSet\Services\' +@instance_name1;
SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';
If @registry_key is NULL
set @instance_name=coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;

SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';
SET @registry_key1 = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\supersocketnetlib\TCP\IP1';
SET @registry_key2 = N'SYSTEM\ControlSet001\Services\Tcpip\Parameters\';

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ImagePath',@value=@image_path OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='Start',@value=@startup_type OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ObjectName',@value=@start_username OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key,@value_name='SQLArg1',@value=@log_directory OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='IpAddress',@value=@IpAddress OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key2,@value_name='Domain',@value=@domain OUTPUT

Set @startuptype= (select 'Start Up Mode' =
CASE
WHEN @startup_type=2 then 'AUTOMATIC'
WHEN @startup_type=3 then 'MANUAL'
WHEN @startup_type=4 then 'Disabled'
END)

--Step 4: Getting the cluster node names if the server is on cluster .else this value will be NULL.

declare @Out nvarchar(400)
SELECT @Out = COALESCE(@Out+'' ,'') + Nodename
from sys.dm_os_cluster_nodes

-- Step 5: printing Server details

SELECT
@domain as 'Domain',
serverproperty('ComputerNamePhysicalNetBIOS') as 'MachineName',
CPU_COUNT as 'CPUCount',
(physical_memory_in_bytes/1048576) as 'PhysicalMemoryMB',
@Ipaddress as 'IP_Address',
@instance_name1 as 'InstanceName',
@image_path as 'BinariesPath',
@log_directory as 'ErrorLogsLocation',
@start_username as 'StartupUser',
@Startuptype as 'StartupType',
serverproperty('Productlevel') as 'ServicePack',
serverproperty('edition') as 'Edition',
serverproperty('productversion') as 'Version',
serverproperty('collation') as 'Collation',
serverproperty('Isclustered') as 'ISClustered',
@out as 'ClusterNodes',
serverproperty('IsFullTextInstalled') as 'ISFullText'
From sys.dm_os_sys_info


-- Step 6: Printing database details

SELECT
serverproperty ('ComputerNamePhysicalNetBIOS') as 'Machine'
,@instance_name1 as InstanceName,
(SELECT 'file_type' =
CASE
WHEN s.groupid <> 0 THEN 'data'
WHEN s.groupid = 0 THEN 'log'
END) AS 'fileType'
, d.dbid as 'DBID'
, d.name AS 'DBName'
, s.name AS 'LogicalFileName'
, s.filename AS 'PhysicalFileName'
, (s.size * 8 / 1024) AS 'FileSizeMB' -- file size in MB
, d.cmptlevel as 'CompatibilityLevel'
, DATABASEPROPERTYEX (d.name,'Recovery') as 'RecoveryModel'
, DATABASEPROPERTYEX (d.name,'Status') as 'DatabaseStatus' ,
--, d.is_published as 'Publisher'
--, d.is_subscribed as 'Subscriber'
--, d.is_distributor as 'Distributor'
(SELECT 'is_replication' =
CASE
WHEN d.category = 1 THEN 'Published'
WHEN d.category = 2 THEN 'subscribed'
WHEN d.category = 4 THEN 'Merge published'
WHEN d.category = 8 THEN 'merge subscribed'
Else 'NO replication'
END) AS 'Is_replication'
, m.mirroring_state as 'MirroringState'
--INTO master.[dbo].[databasedetails]
FROM
sys.sysdatabases d INNER JOIN sys.sysaltfiles s
ON
d.dbid=s.dbid
INNER JOIN sys.database_mirroring m
ON
d.dbid=m.database_id
ORDER BY
d.name





--Step 7 :printing Backup details

Select distinct
b.machine_name as 'ServerName',
b.server_name as 'InstanceName',
b.database_name as 'DatabaseName',
d.database_id 'DBID',
CASE b.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as 'BackupType'
--INTO [dbo].[backupdetails]
from sys.databases d inner join msdb.dbo.backupset b
On b.database_name =d.name


End
else

begin



--Step 8: If the instance is 2000 this code will be used.

declare @registry_key4 nvarchar(100)
declare @Host_Name varchar(100)
declare @CPU varchar(3)
declare @nodes nvarchar(400)
set @nodes =null /* We are not able to trap the node names for SQL Server 2000 so far*/
declare @mirroring varchar(15)
set @mirroring ='NOT APPLICABLE' /*Mirroring does not exist in SQL Server 2000*/
Declare @reg_node1 varchar(100)
Declare @reg_node2 varchar(100)
Declare @reg_node3 varchar(100)
Declare @reg_node4 varchar(100)

SET @reg_node1 = N'Cluster\Nodes\1'
SET @reg_node2 = N'Cluster\Nodes\2'
SET @reg_node3 = N'Cluster\Nodes\3'
SET @reg_node4 = N'Cluster\Nodes\4'

Declare @image_path1 varchar(100)
Declare @image_path2 varchar(100)
Declare @image_path3 varchar(100)
Declare @image_path4 varchar(100)

set @image_path1=null
set @image_path2=null
set @image_path3=null
set @image_path4=null


Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node1, @value_name='NodeName',@value=@image_path1 OUTPUT
Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node2, @value_name='NodeName',@value=@image_path2 OUTPUT
Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node3, @value_name='NodeName',@value=@image_path3 OUTPUT
Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node4, @value_name='NodeName',@value=@image_path4 OUTPUT

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'nodes') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table nodes
Create table nodes (name varchar (20))
insert into nodes values (@image_path1)
insert into nodes values (@image_path2)
insert into nodes values (@image_path3)
insert into nodes values (@image_path4)
--declare @Out nvarchar(400)
--declare @value nvarchar (20)
SELECT @Out = COALESCE(@Out+'/' ,'') + name from nodes where name is not null

-- Step 9: Reading registry keys for Number of CPUs,Binaries,Startup type ,startup username, errorlogs location and domain.

SET @instance_name = coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');
IF @instance_name!='MSSQLSERVER'

BEGIN
set @system_instance_name=@instance_name
set @instance_name='MSSQL$'+@instance_name

SET @key=N'SYSTEM\CurrentControlSet\Services\' +@instance_name;
SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';
SET @registry_key1 = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\Setup';
SET @registry_key2 = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\';
SET @registry_key4 = N'SYSTEM\CurrentControlSet\Control\Session Manager\Environment'


EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='SQLPath',@value=@image_path OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='Start',@value=@startup_type OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ObjectName',@value=@start_username OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key,@value_name='SQLArg1',@value=@log_directory OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key2,@value_name='Domain',@value=@domain OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key4,@value_name='NUMBER_OF_PROCESSORS',@value=@CPU OUTPUT


END

IF @instance_name='MSSQLSERVER'
BEGIN
SET @key=N'SYSTEM\CurrentControlSet\Services\' +@instance_name;
SET @registry_key = N'Software\Microsoft\MSSQLSERVER\MSSQLServer\Parameters';
SET @registry_key1 = N'Software\Microsoft\MSSQLSERVER\Setup';
SET @registry_key2 = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\';
SET @registry_key4 = N'SYSTEM\CurrentControlSet\Control\Session Manager\Environment'



EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='SQLPath',@value=@image_path OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='Start',@value=@startup_type OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ObjectName',@value=@start_username OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key,@value_name='SQLArg1',@value=@log_directory OUTPUT
--EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='IpAddress',@value=@IpAddress OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key2,@value_name='Domain',@value=@domain OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key4,@value_name='NUMBER_OF_PROCESSORS',@value=@CPU OUTPUT

END
set @startuptype= (select 'Start Up Mode' =
CASE
WHEN @startup_type=2 then 'AUTOMATIC'
WHEN @startup_type=3 then 'MANUAL'
WHEN @startup_type=4 then 'Disabled'
END)

--Step 10 : Using ipconfig and xp_msver to get physical memory and IP

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'tmp') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE tmp
create table tmp (server varchar(100)default cast( serverproperty ('Machinename') as varchar),[index] int, name sysname,internal_value int,character_value varchar(30))
insert into tmp([index],name,internal_value,character_value) exec xp_msver PhysicalMemory

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'ipadd') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table ipadd
create table ipadd (server varchar(100)default cast( serverproperty ('Machinename') as varchar),IP varchar (100))
insert into ipadd (IP)exec xp_cmdshell 'ipconfig'
delete from ipadd where ip not like '%IP Address.%' or IP is null


-- Step 11 : Getting the Server details

SELECT top 1
@domain as 'Domain',
serverproperty('Machinename') as 'MachineName',
@CPU as 'CPUCount',
cast (t.internal_value as bigint) as PhysicalMemoryMB,
cast(substring ( I.IP , 44,41) as nvarchar(20))as IP_Address,
serverproperty('Instancename') as 'InstanceName',
@image_path as 'BinariesPath',
@log_directory as 'ErrorLogsLocation',
@start_username as 'StartupUser',
@Startuptype as 'StartupType',
serverproperty('Productlevel') as 'ServicePack',
serverproperty('edition') as 'Edition',
serverproperty('productversion') as 'Version',
serverproperty('collation') as 'Collation',
serverproperty('Isclustered') as 'ISClustered',
@Out as 'ClustreNodes',
serverproperty('IsFullTextInstalled') as 'ISFullText'
From tmp t inner join IPAdd I
on t.server = I.server

-- Step 12 : Getting the instance details

SELECT
serverproperty ('Machinename') as 'Machine',
serverproperty ('Instancename') as 'InstanceName',
(SELECT 'file_type' =
CASE
WHEN s.groupid <> 0 THEN 'data'
WHEN s.groupid = 0 THEN 'log'
END) AS 'fileType'
, d.dbid as 'DBID'
, d.name AS 'DBName'
, s.name AS 'LogicalFileName'
, s.filename AS 'PhysicalFileName'
, (s.size * 8 / 1024) AS 'FileSizeMB' -- file size in MB
,d.cmptlevel as 'CompatibilityLevel'
, DATABASEPROPERTYEX (d.name,'Recovery') as 'RecoveryModel'
, DATABASEPROPERTYEX (d.name,'Status') as 'DatabaseStatus' ,
(SELECT 'is_replication' =
CASE
WHEN d.category = 1 THEN 'Published'
WHEN d.category = 2 THEN 'subscribed'
WHEN d.category = 4 THEN 'Merge published'
WHEN d.category = 8 THEN 'merge subscribed'
Else 'NO replication'
END) AS 'Is_replication',
@Mirroring as 'MirroringState'
FROM
sysdatabases d INNER JOIN sysaltfiles s
ON
d.dbid=s.dbid
ORDER BY
d.name

-- Step 13 : Getting backup details

Select distinct
b.machine_name as 'ServerName',
b.server_name as 'InstanceName',
b.database_name as 'DatabaseName',
d.dbid 'DBID',
CASE b.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as 'BackupType'
from sysdatabases d inner join msdb.dbo.backupset b
On b.database_name =d.name


-- Step 14: Dropping the table we created for IP and Physical memory

Drop Table TMP
Drop Table IPADD
drop table Nodes

end
go

-- Step 15 : Setting Nulls and Quoted identifier back to Off

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO


Happy learning
Abhay
hi_abhay78@yaoo.co.in

what if yo udo not know the servers to whic hlogins belong......

Request :
I was give a text file with a list of logins to be removed from SQL Servers as those employees were no more existing .I asked if they know the server names where the logins exist .The answer was an expected NO ....made my task more dificult .But I had to do it . I came out with a script that can be used to find the instance name where the logins exist .I have not created the script to remove those logins as its pretty simple ,but have explained in brief what can be done .So my juniors will do it :) ...

Requirements :
-> SQL Server 2008 Express edition [Central management Servers]
We will be using it to execute queries on a group of servers and getting the consolidated result .

-> .regsrvr file
We will modify this file and add steps to add more servers instead of registering instances manually one by one .

-> login_info view
This view will keep the information about all the logins having users and schemas associated with them .If there is a login which has no user associated with it , it will not show up in this view.
-> Instances table
This will keep the list of all the 2005 instances in a domain .
-> Del_logins table
This will keep the list of all the logins that have to be searched for deletion.

-> Instances.txt
This will keep the list of the instances that will be BCPed in to instances table

-> logins.txt
This will have the list of all the logins that have to be deleted.This wil be BCPed to del_login table
-> BCP
We will be using BCP in ,to import values from instances.txt and logins.txt.

-> :connect
We wil be using :connect to connect to instances and and doing BCP in.

-> WMI
We will use WMI to copy files to different servers .



Important :
Before doing anything you need to register the servers in the CMS .
register a couple of servers and then export the .regsrvr file somewhere.
open that file in SQl Server QA .You can easily see the pattern of instance registration .Just copy the same as many time as there are the instances and change the instance names .Import it back and you have the instances under the server group name .



--Step 1: create login_info view on all servers using CMS.

use master
go
create view login_info as
select d.name as [user_name],d.type_desc as [user_type],s.name as[login_name],s.type_desc as [login_type],d.default_schema_name as [schema_name]
from sys.database_principals d right outer join sys.server_principals s
on d.sid =s.sid
where s.type not in ('R')


--Step 2:Copy the logins.txt that the client gave to all the 2005 servers in the common drive .
--this is a VBS file which will copy test.doc to different servers listed in computers.txt.

Const ForReading = 1
Const OverwriteExisting = TRUE

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\Scripts\Computers.txt")

Do Until objFile.AtEndOfStream
strComputer = objFile.ReadLine
strRemoteFile = "\\" & strComputer & "\C$\Scripts\LOGINS.TXT"
objFSO.CopyFile "C:\Scripts\Test.doc", strRemoteFile, OverwriteExisting
Loop




-- step 3: Create a text file instances.txt with 2005 instance names on any one server with SQL Server installed .
-- you can copy it from the inventory or try importing it with xp_cmdshell using OSQL -L and inporting it to a table
/*
sp_configure 'show advanced options',1
reconfigure with override
sp_configure 'xp_cmdshell',1
reconfigure with override

create table test (ins varchar(30))
go

insert into test exec xp_cmdshell 'osql -L'

Then delete few unnecessary rows or you need to modify the output by >> Query >> Query options

*/

-- Step 4: Create the instances table in the 2005 instance server you copied the file above.

use master
go
Create table instances (iname sysname)



-- Step 5: BCP in the instance names from the instances.txt files to instances table .
xp_cmdshell 'bcp master.dbo.instances in c:\temp\instances.txt -T -c -S

-- sTEP 6: create del_logins in all the instances using CMS
create table master.dbo.del_logins (lname varchar(50))

-- Step 7: BCP in values from the text file to the del_login table (run this command on the same server where you have instances table .Copy the output to the CMS and execute)

Create table #inst (iname sysname,flag int identity (1,1) )
Insert into #inst select iname from instances
Declare @@flag1 int
Select @@flag1 =max (flag) from #inst
While @@flag1 >0
Begin
Declare @string nvarchar (100)
DECLARE @string1 nvarchar(50)
Declare @iname sysname
Select @iname =iname from #inst where flag=@@flag1
Set @string ='xp_cmdshell '+ '''bcp master.dbo.del_logins in c:\temp\logins.txt -T -c -S' +@iname+''''
set @string1= ':connect' +@iname
print @string1
print @string
print 'GO'
Set @@flag1 =@@flag1 -1
End
Drop table #inst

-- Step 8: We need to run an intersect query in CMS

select login_name from master.dbo.login_info
intersect
select lname from master.dbo.del_logins

Result :
----------

This output will give you the logins that exist on the different servers .And then you can drop those logins one by one .

Other way to drop the logins is to create a table with servername , login name and a flag default to an identity starting with 1.

create the SP around the intesect output and then insert it to this table .
run it in the while loop and drop the logins .

You can also create the scrip to change the schema to DBO before dropping the users and logins.

mail me for any clarification at : hi_abhay78@yahoo.co.in

Happy learning..
Abhay