Yukon-Katmai Discussion

Replication : Latency but no Latency

2012-01-18T10:32:00.002+05:30

I am sure those of you who have been through the replication latency issues know that these issues are not easy to crack .Gaining expertise in Replication especially issues related to latency cannot be learned or taught in a class.

Mostly, issues related to latency are due to :

1: Blocking on DIST/PUB/SUB
2: High Resource consumption : CPU,Storage,Memory
3: Network issues
4: Huge size of Distribution database (msrepl_transactions and msrepl_comands)
5: Transaction log of publisher is huge (too many VLFs) causing log reader latency .

-I am sharing this one with you where actually there was no latency due to any of these as I
mentioned above.
-There was no blocking
-CPU,memory and Diskes were doing good .
-There was no network issue
-We shrunk the publisher database which did not help us at all .
-Cleanup job was running fine .We ran update stats with full scan on msrepl_transactions and
msrepl_comands which did not help either .

When latency started piling up , we first had a look at the log reader and distributor agent history in distribution database but could not get much information due to another issue (out of scope of this post) because of which the log reader history was not showing up and distributor history was showing that almost no data is coming to subscriber ..Normally every 5 mins the throughput of logreader and distribution agent threads (reader and writer threads) are written in to mslogreader_history and msdistribution_history system tables .But we were getting false entries there .

We had no choice but to take the verbose log for distribution and log reader agents .From the logs it was clear that the log reader agent was delivering more than 1000 commands/sec while distribution agent was delivering less than 100 cmds/sec .

We were totally clue less because :
- Log Reader was running at its usual speed .So there was no issue with the Publisher .Nor there was any issue in pumping the data to Distributor.The log Reader Verbose log cleared it .So this was ruled out .

- Data from Distributor to Subscriber was slow .But we were not able to find out whether its slow in reading the data from distribution db or slow in pumping the data to the subscriber .db .What we could clearly see was that there was almost no activity on the subscriber .

-No Resource bottlenecks .Its just that something was stopping the data to flow from distributor to subscriber .This was like when we see an hour glass where there is a lot of sand at the top but the hole is so small that very less can go out of it .

I then saw the distribution verbose logs again and found that there were very frequent entries related to committing the transactions .The 5 similar sentences in 5 continuous lines were not very much clear but I could feel that something related to committing transactions was happening .But still very unclear .These entries were repeating in a group of 5-6 very frequently .The time consumption was around 2 seconds for each spell of 5-6 entries .

This gave us some hint and we immediately jumped to the Distribution agent profile to see if someone has not modified any setting which might be causing this issue.We did a mistake again because we just right clicked replication >> Distributor properties >> agent profiles >> Distribution agent .This was looking fine .

However, one of our colleagues opened up the profile of the distribution agent that was giving us the issue .This was the right way guys .We needed to do this . We found that -CommitBatchSize (default Value 100) and -CommitBatchThreshold(default Value 1000) values were changed to 10 each .

We then changed it back to the default and recycled the Distribution Agent .Thats it , the story ends here .
Due to this the rate at which the transactions were delivered at 1 cmd/transaction .

Even though it was a small setting that caused us slogging for hours , but the experience of working on the replication latency issue when there was actually no resource bottleneck (I call it as false latency) was amazing ..

Cheers and Happy Learning ..

Can restoring a database to another instance reduce Index fragmentation of underlying tables ?

2012-01-14T13:29:00.001+05:30

Answer is NO ..but to come to this conclusion , I had to spend some time .One of my colleagues came to me with this question .My instant answer was a clear NO ...But then I asked him with a curiosity the reason for asking this question .As per him the nightly index reorg job that should run for a very long time , finished in just 3 hours .Also we cannot check the index fragmentation since it takes around 2 hours .Our tables are huge ..

I thought of 3 reasons :

1) Since there is a logic in our job to do re-org only if there is certain level of fragmentation , that day there might be no index coming in the rebuild category.This possibility was less but cannot be ruled out .

2) The restore actually reshuffled the pages and in that attempt cleared out some leaf level fragmentation .I remembered that restores take more time than backups .So I started believing this .

3) There might be some Re-org activity happening during the time the backups were happening .The backup might have a copy of well re-orged pages and this might have resulted in a less fragmented database .

I first started off with point 2 and soon realized that I was not correct .This did not take me much time .For point one , we added the log in the job so that as the job finishes , it creates the log which we can read .But this will take time to generate.

Now , I was left with option 3 .I had the table with > 99% fragmentation and 24085822 rows .The table size was around 4GB . DBCC Showcontig output is shared below :

DBCC SHOWCONTIG scanning 'stats_test' table...
Table: 'stats_test' (711673583); index ID: 1, database ID: 6
TABLE level scan performed.
- Pages Scanned................................: 268007
- Extents Scanned..............................: 33569
- Extent Switches..............................: 268005
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 12.50% [33501:268006]
- Logical Scan Fragmentation ..................: 99.22%
- Extent Scan Fragmentation ...................: 0.01%
- Avg. Bytes Free per Page.....................: 3334.3
- Avg. Page Density (full).....................: 58.81%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I then ran Index Reorg and noticed percent_complete column in sys.dm_exec_requests DMV .When it reached 46% , I kicked off the backup of the database .

Now let me restore the backup .....fingers crossed :) ....

DBCC SHOWCONTIG scanning 'stats_test' table...

Table: 'stats_test' (711673583); index ID: 1, database ID: 6

TABLE level scan performed.

- Pages Scanned................................: 158633

- Extents Scanned..............................: 19862

- Extent Switches..............................: 19866

- Avg. Pages per Extent........................: 8.0

- Scan Density [Best Count:Actual Count].......: 99.81% [19830:19867]

- Logical Scan Fragmentation ..................: 0.15%

- Extent Scan Fragmentation ...................: 3.47%

- Avg. Bytes Free per Page.....................: 51.2

- Avg. Page Density (full).....................: 99.37%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Mystery solved ....

Happy learning ..

Replicating SP execution : Issue in SQL Server 2005 SP3 CU2 .Works fine in SQL 2008 SP1

2012-01-13T12:25:00.000+05:30

posting after a long time gap and might still have not posted until yesterday when I got to know how smart Replication is .

Brief Summary :
We have a very large OLTP environment where millions of small queries do inserts and updates (No deletes) .The same is replicated to other subscribers .the data is so much that most of the time we firefight latency . So because of the size of the data we started archiving ,which also started deleting the data in batches from OLTP environment and replicating the same to the subscribers .This further added to latency for obvious reasons.

So to reduce latency ,we started thinking of replicating the execution of Stored Proc that deletes the rows in batches .No, I am not trying to say the just because we can replicate the SP execution , that the replication is smart .This feature is quite old now and perhaps you all might be aware of this already.

Issue that we thought we might face :
We already have had all the required tables added as articles in the respective publications .And now if we add the Stored procedures in the publication then we thought Replication will try to update and insert the data twice . For example Lets say there are 2 articles in the publication .The first one is a table (lets say REPL_TAB) and the second one is a SP (say REPL_SP) .REPL_SP deletes x rows from REPL_TAB.

Now, if we execute REPL_SP , we thought that it should affect the Subscriber table twice .One when the SP deletes the rows and two , since the rows are being deleted REPL_TAB should also replicate the same .So we thought this might not work .We then thought of creating another publication with this SP added as an article but had same reservations .

But logically , I thought that Log Reader agent should pickup the command from the T-Log and should be smart enough to replicate it once .I mean if I run EXEC XYZ which deletes 10 rows in a table ABC ,then it should only replicate EXEC XYZ and not the delete command because the rows are being deleted from the table and that table is also an article in the same or for that sake different publication .

I first tested this in SQL Server 2005 SP3 CU2 and got it partially working .In the same publication If I have both the articles the executing the stored procedure will fill msrepl_commands and merepl_transactions with 1 row each .But If there are 2 publications with one article in each and I execute the SP to delete x rows its replicated twice .First 1 command and 1 transactions and then x commands and 1 transactions .Distribution history confirms the same .

I then tested the same on SQL Server 2008 SP1 and it worked like a charm .Below is the proof of concept for your reference :

Test 1:
We have 2 publications on Adventureworks database .One is publishing a table and the other is a stored procedure execution (by default Stored procedure execution is not enabled).Stored Procedure Del_stats_scan, deletes top 10 rows from table dbo.Stats_State in the Publisher database Adventureworks)

SQL Server verion :
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 Evaluation Edition on Windows XP SP3

Publisher database : Adventureworks
|_Table :dbo.Stats_State
|_SP:dbo.del_stats_scan
|_Publication :ADV_Table
| |_Article :dbo.Stats_State
|_Publication:ADV_SP
|_Article :dbo.del_stats_scan

Subscriber Database : ADV_SUB
|_Table :dbo.Stats_State
|_SP:dbo.del_stats_scan

Jobs :
Log Reader Agents :1
Snapshot Agent :2
Distribution Agent :2

Replication is in synchronization :

Queries fired on publisher DB :

Query 1

delete top (10) from dbo.stats_test

Results :

Data replicated only once .The second Distribution agent did not Do anything . The reason you are seeing 2 transactions in log reader is because both are same images .Log Reader for one database is only ONE .

Fire these queries to find out what is being replicated :

select * from distribution.dbo.MSrepl_commands

select * from distribution.dbo.MSrepl_transactions

Query 2 :

exec del_stats_scan

Results :Data Replicated only once.Only the SP executed and replicated .The other distribution agent did not do any thing . The reason you are seeing 2 transactions in log reader is because both are same images. Log Reader for one database is only ONE .

Run these queries to find out what has been replicated

select COUNT(*) 'No. of rows in Repl_cmds' from distribution.dbo.MSrepl_commands

select COUNT(*) 'No. of rows in Repl_Trans'from distribution.dbo.MSrepl_transactions

Test 2:

We have 1 publication on Adventureworks database with 2 articles.One article is publishing a table and the other article is publishing the stored procedure execution (by default Stored procedure execution is not enabled).

Stored Procedure Del_stats_scan, deletes top 10 rows from table dbo.Stats_State in the Publisher database (Adventureworks).

SQL Server verion :

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 Evaluation Edition on Windows XP SP3

Publisher database :

Adventureworks

|_Table :dbo.Stats_State

|_SP:dbo.del_stats_scan

|_Publication :ADV_Table

|_Article :dbo.Stats_State

|_Article :dbo.del_stats_scan

Subscriber Database : ADV_SUB

|_Table :dbo.Stats_State

|_SP:dbo.del_stats_scan

Jobs :

Log Reader Agents :1

Snapshot Agent :1

Distribution Agent :1

Repeat Test 1 and 2 and see the results .

Conclusion :

SQL Server 2008 Replication(log Reader) is smart enough and replicate data only once from the transaction log to Distributor .Distributor then distributes the command to the subscriber.There is a bug in SQL Server 2005 SP3 CU2 where the second test works fine but not the first test and replicate twice if we execute the SP. You will have to find out which CU in 2005 fixed this or might want to directly apply SP4.

Happy Learning .

What does resourcedb contain ?

2011-07-22T13:49:00.000+05:30

Many a times this question has been asked (either in interviews or just out of curiosity) that what does resourcedb contain and why is it so important to SQL Server ?

Normally we can't see it and hence can't use it .However, there is a way you can use resource database .But be careful. If you mess up anything you might end up paying a heavy cost.

Since we are discussing about this ,there is one more point that I would like to touch here .Starting from SQL 2005 there are no system tables but DMVs for us to query .However, if you query sys.objects and filter it on type ='S' ,you will notice a lot of system tables listed in the output .So there are system tables and we can see them .But if you try to query them ,you will get an annoying 208 error stating that the object does not exist which is not correct.So in this post we will see how to query resource database and also in a similar manner other system and user database .

Let us see how can we use resource database and also query system tables.Start SQL Server with -m switch ( in single user mode ) . There are 2 ways :

1) Through services console (after adding -m do not click on OK but click on start )

2) Through DOS prompt

Once SQL Server has been started in single user mode , we can make only one connection . We will connect to SQL Server using DAC .DAC option can only be used in sqlcmd utility and not in OSQL or ISQL .Again there are two ways to do this .But before trying to attempt for DAC connection make sure you have enabled remote admin connection option via sp_configure (you can see the run value of 1):

1) connecting to SQL Server with DAC (using SQLCMD) using -A option

2)Connecting via MGT Studio
Open Management studio , It will prompt you to enter the instance name . Just before the instance name add admin:

you might or might not get this error :

If you get this error then click OK ( this error window will go away ) and then instead of Connect click on cancel .
you will see a clean screen like below :

Click on new query

You will again see the same connection popup :

This time click on connect and it will work :) .A new query window will open even though you will not see the databases in the left hand side pane but the connection is there and working ...

Run the query 'use mssqlsystemresource' and press F5 :) ...It will work :

Also if you query sys.sysdbreg sytem table (an alternative for sys.sysdatabases DMV )you will see the resource database :

This database is currently in read-only mode (trust me :-) ) . If you want to cross check this run dbcc shrinkdatabase (mssqlsystemresource) and you will get to know .
You can set it to read_write mode though by running : alter database mssqlsystemresource set read_write.Now the very fact that this table is Read-Only and that we cannot take the backup of this database ,proves that this database might not contain very important information ...if you query the tables you will see that certain static information is stored which the engine might be using from time to time .Something like we store the values in a temp table or a variable .So coming back to the original question , resource database contain a lot of static information which the engine might need from time to time for its internal use .

At the end you might ask "Whats the need to touch the system tables in the database ?"
The answer is : We normally do not need to do this (especially Resource DB ) but there are other databases (system as well as user ) having some information we can use to resolve some issues (by updating those tables as needed ). And to resolve those issues , we need to login in this way ....

Hope you have found it interesting .But remember , BE VERY CAREFUL WHEN YOU TRY TO PLAY AROUND WITH SYSTEM TABLES (as i said in the beginning).
Happy Learning !!

Replication :Archiving partitioned and non-Partitioned tables (Without removing the articles from the publications)

2011-06-26T11:16:00.000+05:30

Recently there was a request on the MSDN forums where the poster wanted to archive the replicated partitioned tables in the publisher database . I think it would be good to share the solution with everyone in this forum as well .
In this post we will see :

Part 1) archiving the replicated non-partitioned (normal) tables .
Part 2) archiving the replicated partitioned tables .

At the end ,you will notice one nice to know feature of partitioned tables .

Part 1) Archiving the replicated non-partitioned (normal) tables .
Publisher : DB2Migration
Subscriber : DB2Migration_Sub
Replication Topology : Transactional Replication
Articles : dbo.Test
Other details : Both tables have 10000 rows each after first synchronization.

Now we need to archive the test table in publisher database but want to keep the Subscriber untouched i.e. the rows in subscriber should not change .For this example we will delete all the rows of the publisher table .

How should we do it ?
If I delete any row on publisher , the same will be replicated to subscriber . One way might be to stop the log reader agent and then delete the rows .After this , I can fire sp_repldone on the publisher and start the logreader agent .yes this is perfectly achievable . Here we go ...

DBCC opentran will show :
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

We will enable the LogReader agent now and will see that there are no transactions to be replicated.

Select count * from both the tables will show 0( zero ) and 10000 rows respectively .

After this we will insert 1000 rows in publisher table ( we need to be careful as the tables have primary key).As a result the subscriber now, has 11000 rows and publisher has 1000 rows .This is going to be costly when there will be millions of rows because delete (or update or insert )is a logged activity.There is one more drawback and perhaps more critical.The log reader agent is one per database .So if there are more than one publications on the same database and we run sp_repldone , then we will hurt other subscriptions and publications .So we have to be careful. Other way is to truncate the table (after moving the data to an archived table) but replicated tables cannot be truncated (why ???.....simple , truncate is a non-logged activity and log-reader agent reads the log file to find the transactions marked for replication using sp_replcmds).So to truncate the table ,you need to remove the article from the publication .If you want to do that the steps are :

*******TEST THIS BEFORE IMPLEMENTING IT IN PRODUCTION********
1) Stop the log reader agent and distribution agent
2) Drop the article(s) from the publication
3) Archive the table to another table ( this will be a logged activity ) by Bulk insert or BCP or import export wizard
4) Truncate the table 5) Add the article again
6) Change the properties of all the articles in the publication properties to "Keep existing object unchanged" for option action "if name is in use " .This is the most important step and please cross check it a few times to make sure that "Keep existing object unchanged " is set
6) generate the snapshot again ....
7) start the log reader agent and distribution agent and initiate the new snapshot ....
*******TEST THIS BEFORE IMPLEMENTING IT IN PRODUCTION********
Trust me .you are done :) ..But don't you think its lengthy and a bit risky ..Now lets see something new ...

Part 2) Archiving the replicated partitioned tables .
Let us first create 2 new databases followed by creating partition functions followed by partition schemes followed by partitioned tables followed by inserting data in the tables .

--creating database and filegroups
create database test
GO
ALTER DATABASE test ADD FILEGROUP [second]
GO
ALTER DATABASE test ADD FILEGROUP [third]
GO
ALTER DATABASE test ADD FILEGROUP [forth]
GO
ALTER DATABASE test ADD FILEGROUP [fifth]
GO

--Adding new files to the filegroups
USE [master]
GO
ALTER DATABASE test ADD FILE ( NAME = N'test2', FILENAME = N'C:\Program Files\Microsoft SQL Server\test2.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [second]
GO
ALTER DATABASE test ADD FILE ( NAME = N'test3', FILENAME = N'C:\Program Files\Microsoft SQL Server\test3.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [third]
GO
ALTER DATABASE test ADD FILE ( NAME = N'test4', FILENAME = N'C:\Program Files\Microsoft SQL Server\test4.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [forth]
GO
ALTER DATABASE test ADD FILE ( NAME = N'test5', FILENAME = N'C:\Program Files\Microsoft SQL Server\test5.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fifth]
GO

--The following partition function will partition a table or index into four partitions.
USE test
GO
CREATE PARTITION FUNCTION [PF_test](int) AS RANGE LEFT FOR VALUES (1,100,1000)

-- Creating partition scheme
use test
GO
IF NOT EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'PS_test')
create PARTITION SCHEME [PS_test] AS PARTITION [PF_test] TO ([second],[third],[forth],[fifth])
--[Note if you want to have one filegroup for all the files then : create PARTITION SCHEME [PS_test] AS PARTITION [PF_test] All TO ([secondary]) ]

--creating table with constraint and assigning a partition scheme to it
create table test (dummy [int] primary key constraint test_c check ([dummy] > 0 and [dummy] <=20000)) on ps_test (dummy) --inserting values
declare @val int
set @val=1000
while (@val > 0)
begin
insert into test..test values (@val)
set @val=@val-1
end

On Subscriber we will only create the same filegroups and add files to them :
--creating database and filegroups
create database test_sub
GO
ALTER DATABASE test_sub ADD FILEGROUP [second]
GO
ALTER DATABASE test_sub ADD FILEGROUP [third]
GO
ALTER DATABASE test_sub ADD FILEGROUP [forth]
GO
ALTER DATABASE test_sub ADD FILEGROUP [fifth]
GO

--Adding new files to the filegroups
USE [master]
GO
ALTER DATABASE test_sub ADD FILE ( NAME = N'test_sub2', FILENAME = N'C:\Program Files\Microsoft SQL Server\test_sub_sub2.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [second]
GO
ALTER DATABASE test_sub ADD FILE ( NAME = N'test_sub3', FILENAME = N'C:\Program Files\Microsoft SQL Server\test_sub_sub3.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [third]
GO
ALTER DATABASE test_sub ADD FILE ( NAME = N'test_sub4', FILENAME = N'C:\Program Files\Microsoft SQL Server\test_sub_sub4.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [forth]
GO
ALTER DATABASE test_sub ADD FILE ( NAME = N'test_sub5', FILENAME = N'C:\Program Files\Microsoft SQL Server\test_sub_sub5.ndf' , SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fifth]
GO

Once you are done create the publication on database TEST and the add the article TEST.Once Its done, the Test_Pub Publication is ready to publish.After this we will create the Subscription on this publication .Our subscriber database is test_sub.Once the initial snapshot is synchronized you will see the following values :

select OBJECT_ID('test..test')
select OBJECT_ID('test_sub..test')
select * from test.sys.partitions where object_id in (2105058535) order by partition_number
select * from test_sub.sys.partitions where object_id in (133575514) order by partition_number

So as of now everything is as per plan . The data is synchronized in the correct partitions .Now if we need to archive the publisher table we can try the same old approach that we used in stage 1 .However , we will try something new here .That something new is SWITCHING OF PARTITIONS in the table .I will not explain what does it means because you will see it in few seconds(or you can refer BOL) .

--Lets first create the archive table on publisher .Its the replica of the original test table
create table test..test_archive (dummy [int] primary key constraint test_c_a check ([dummy] > 0 and [dummy] <=20000)) on ps_test (dummy) --Switching the partitions from test to test_archive table on publication
ALTER TABLE test..test SWITCH PARTITION 1 TO test_archive Partition 1;
GO

Msg 21867, Level 16, State 1, Procedure sp_MStran_altertable, Line 259
ALTER TABLE SWITCH statement failed. The table '[dbo].[test]' belongs to a publication which does not allow switching of partitions

Oopsss , What happened...Yes , that is true , we cannot switch the partitions for the replicated table , unless ..........we explicitly allow partition switching for the publisher :
sp_changepublication 'test_pub' ,@property='allow_partition_switch',@value='true'

You will get this message
The publication has updated successfully.

--Switching the partitions from test to test_archive table on publication. This is for partition number 1 . We have 4 partitions.
ALTER TABLE test..test SWITCH PARTITION 1 TO test_archive Partition 1;
GO
ALTER TABLE test..test SWITCH PARTITION 2 TO test_archive Partition 2;
GO
ALTER TABLE test..test SWITCH PARTITION 3 TO test_archive Partition 3;
GO
ALTER TABLE test..test SWITCH PARTITION 4 TO test_archive Partition 4;
GO

Now , just check the number of rows in the tables test and test_archive on publisher test and the test table on subscriber test_sub:

Thats the magic :)..Did you also notice that we did not create any partitions for the test_archive table .Lets query the syspartition DMV and notice the partitions in test_archive table .

Thats the beauty .You did not have to delete or truncate a single row.Nor did you remove the article or stopped any agent . Now if you add rows to table TEST it will be replicated to subscriber as usual .lets try this by inserting 1000 rows in table test in the publisher and then check the subscriber table
--inserting new values in test table ( in publisher database )
declare @val int
set @val=2000
while (@val > 1000)
begin
insert into test..test values (@val)
set @val=@val-1
end

Suggestions are welcome as we are here to help each other technicaly grow.Happly learning

ORA-00942: table or view does not exist .

2011-06-18T17:23:00.000+05:30

The complete error is :
select xactts, subguid from MSrepl7 where pubsrv = ? and pubdb = ? and indagent = 0 and subtype = 0
ORA-00942: table or view does not exist

We were setting up heterogeneous replicaton between SQL and Oracle 9i.This was done successfully.But when we tried synchronize the Articles (actually we were replicating only a view)we got stuck at this error.

It was clear that the error is coming from the subscriber .But we were not replicating the object : MSrepl7 and we dont know whether this is a table or view .Since we were not replicating it , I was sure that this is a table or view that replication is creating .I saw a KB article which talks about this table for DB2 : http://support.microsoft.com/KB/313332 .

Later I found from other oracle subscribers that MSrepl7 is nothing but a replica of msreplication_subscriptions table in SQL Server subscribers. This table is looked up and matched with msrepl_transactions .The columns that are compared are transaction_timestamp in MSrepl7 with xact_seqno in msrepl_transactions table .

moving forward we wanted to find why this table does not exist on the subscriber . I suspected that it should be created while Subscription setup or while we reinitialize subscription or while synchronizing .

To see if its really being created I enabled tracing on Distribution agent since its this job which is failing .
-Output C:\Temp\OUTPUTFILE.txt -Outputverboselevel 2.

Ran the Agent again which failed with same error .checked the file:

OLE DB Subscriber 'ERPDEV.WORLD': create table MSrepl7 (pubsrv varchar2 (128), pubdb varchar2 (128), publcn varchar2 (128), indagent number (1, 0),subtype number (10, 0), dstagent varchar2 (100),timecol date,descr varchar2 (255), xactts raw (16), updmode number (3, 0), agentid raw (16), subguid raw (16), subid raw (16), immsync number (1, 0))

Connecting to Distributor 'MCMSMESVS1.distribution'
[4/21/2010 1:32:46 AM]MCMSMESVS1.distribution: {call master..sp_MScheck_agent_instance(N'MCMSMESVS1-MES-ERPDEV.WORLD-1', 10)}
OLE DB Subscriber 'ERPDEV.WORLD': select xactts, subguid from MSrepl7 where pubsrv = ? and pubdb = ? and indagent = 0 and subtype = 0
Agent message code 20046. ORA-00942: table or view does not exist

[4/21/2010 1:32:46 AM]MCMSMESVS1.distribution: {call sp_MSadd_distribution_history(1, 6, ?, ?, 0, 0, 0.00, 0x01, 1, ?, -1, 0x01, 0x01)}
Adding alert to msdb..sysreplicationalerts: ErrorId = 12,

This means that the table is being created.But its not there .Strange.

To check whats happening ,I connected to Oracle server and tried to create a test table ....

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> create table test(t int);
create table test(t int)
*
ERROR at line 1:
ORA-01031: insufficient privileges

Looks like when we start synchronizing it does not create sufficient objects at Oracle side due to permission issue .Sadly , SQL also does not throw any errors that we cannot create the object due to permission issue (might not have put the Try catch for this error).

So,I requested the Oracle DBA in charge of this to give the appropriate permissions to the login that is executing the distribution agent job and execute the Distributor job again .

BINGO ........the issue was resolved ...hope this blog helps someone someday ..

Regards
Abhay

What should we do first : Try to find the solution or try to find the Problem

2011-06-18T16:44:00.000+05:30

Had I been asked this question a few years ago , I would have said "I would search for a solution" .

Most of us do this i.e.we first try to find the solution .Sometimes we succeed but most of the times we do not.After many unsuccessful attempts I realized that the step to find the solution goes through another step first, and that is Finding the problem .Not going deep in to it .

A couple of months back one of my collegue came to me with a problem " There is a job that fails on every Monday" .This job takes some values from somewhere and inserts it in SQL Server tables.The error was :
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.

Earlier ,my collegue explained the client that this is not a SQL issue and suggested the poor client to touch base with the application team .But the client was not a fool .He said that there is some problem in SQL Server and he don't want to go to DEV without proof.He is not a techie though.

What Should I do , Google it or BING it :-) .we did not do that .
you can see this message in sysmessages . [select * from sys.messages where message_id=241]

We decided to reproduce the issue and with in 15 mins , we proved that the format in which Date is entered at the application level should be incorrect and datetime datatype is not recognizing it .

Repro of the issue

Repro 1
declare @date datetime ,@string char(100)
select @date =getdate()
set @string =@date

Command(s) completed successfully.

Repro 2
declare @date datetime ,@string char(100)
select @date ='28/07/2010'
set @string =@date

Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

So we are near as this error is almost similar to 241 , but not the same.

Repro 3
declare @date datetime ,@string char(100)
select @date ='2010/07/28'
set @string =@date

Command(s) completed successfully.

Repro 4
declare @date datetime ,@string char(100)
select @date =NULL <-- assuming someone might be putting NULL in date and since NULL can be anything, it might not be a CHAR and we will get the error.
set @string =@date
select @string
Command(s) completed successfully. <-- We did not Repro 5

declare @date datetime ,@string char(100)
select @date ='NULL'
set @string =@date
select @string

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.
here we got the error .

Repro 6
declare @date datetime ,@string char(100)
select @date ='NULL'

This is more clear
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.

So the reason could be :
The application adds single quotes to any entry .For example NULL will be converted to 'NULL' and 2010/07/28 will be converted to '2010/07/28' . In this case 'NULL' will give 241 but the date will be absolutely correct and will not throw error when inserted in the table (inside SQL Server).

Conclusion :
Always try to look for the reason behind the error/issue first rather jumping for solutions here and there .It might take time but you will learn more .

Regards
Abhay

Immediate Sync option in Transactional Replication : Good or Bad ..

2011-06-18T16:30:00.001+05:30

Around 4 months back we faced a latency issue in replication. We used tracer tokens and found that the distribution agent was lagging behind .Before I go forward, let me explain you that the distribution agent has 2 threads .Reader thread reads the value from the MSrepl_transactions table (this activity happens in parallel with the log reader agent where the rows are pumped in to the msrepl_transactions table) from distributor database and the writer thread apply those commands to the subscriber .

To find out where we are getting delayed we configured the verbose logging with level 2 in the distribution agent job (http://support.microsoft.com/kb/312292). In the output we saw that there is a good time gap after the sp_MSget_repl_commands command is fired and the next command .Now this command is fired to read the distribution database and populates the rows in the memory tables .These rows are then read by the writer thread and inserted into the subscriber database.

In the verbose log you can also check the statistics which is fired every 5 mins (starting SQL Server 2005 ):
•Cumulative Update 12 for SQL Server 2005 Service Pack 2
•Cumulative Update 5 for SQL Server 2008
•Cumulative Update 2 for SQL Server 2008 Service Pack 1

you will see data between these lines

*************STATISTICS SINCE AGENT STARTED *******************
[data]
***************************************************************************

It was clear that the reader thread was taking a lot of time in retrieving the rows as compared to the writer thread writing those commands to the subscriber database .

The next stage is to check the msrepl_transactions and msrepl_commands tables .We checked the number of rows in those tables and found that there a millions of rows which are replicated but still showing up in the in those tables . This was strange . We checked the cleanup job and found that the job was running fine .

So whats the issue ? Why the job is not removing the replicated rows ??
upon digging deep , we found that the DBA selected the "Create a snapshot immediately and keep the snapshot available to initialize subscriptions" option when he configured the replication .As a result Replication is suppose to keep all the transactions cached in the Distribution database for the entire Retention Period.You will also see all the snapshot files in the snapshot folder on the distributor .

Why it happens :
Every new subscriber that is added with in the subscription retention period needs the initial snapshot and then the data from the logreader is applied over the snapshot that is accumulated in the distribution database. But the Snapshot gets OLD too as the database image changes from time to time .Therefore every time a new snapshot is needed first. Because of this option set, the same old snapshot is applied first and then all the remaining LogReader entries from the distribution database .That is the reason all the old transactions are kept till the subscription expires.

Why is it configured ?
This command is configured in the environment where there is a need to create the subscription quite often and also if the snapshot increases in size quite considerab;y over the time .

How is it configured ?
Commandline :
via sp_addpublication <--Check BOLGUI :

Drawback :
Due to this option the size of Msrepl_transactions and Msrepl_commands increases ( which is more than normal) which slows up the synchronizations and clogs up the system.

How to disable it ?
EXEC sp_changepublication
@publication = 'your_publication_name',
@property = 'immediate_sync',
@value = 'false'
GO

Happy Learning !!!!!!!

Address translation: How Virtual Memory is mapped to Physical Memory

2011-06-18T15:13:00.000+05:30

Introduction
We all know that data retrieval will be fast if the data pages are found in RAM .If the data pages are not in RAM, they are fetched into RAM from the disk. This causes a physical IO .The page remains in the RAM until it’s again kicked off to Disk.
But the process and threads do not access the Physical memory (RAM) directly .Instead the RAM is accessed indirectly through Virtual Memory or Virtual address space (VAS) pointers. On a x86 operating system the number of such pointers in virtual memory that can point to physical memory is 4,294,967,296 (2^32) .This is equal to 4 GB .Out of this 4GB VAS pointers, 2GB worth of pointers are located in the Kernel address space and remaining 2GB in the User Address Space .It’s this 2GB of user address space which is used by the Processes and threads for their use and to map it to RAM. Other 2GB Kernel Address space is also mapped to RAM for the OS routines and APIs .So, normally on a 32 bit windows OS, SQL Server will use 2GB RAM (1.66GB Buffer Pool region and 384MB Mem2Leave region) .
But what’s the need for Virtual memory when we have Physical memory and it is the real memory .Let me correlate this to a smart Bank. The bank started with $5000.A customer deposited $1000 and the bank will return $1100 after a year .after 1 month, Another customer deposited $2000 for 1 year and the Bank will return $2200 after a year. So the bank has now $8000 for around 2 years .Then someone took a loan of $3000 for 1 year and the bank will get $4000 after a year .In between if the earlier 2 clients want to withdraw their deposit before time, they can pay the penalty and Bank has sufficient money to give back from the initial investment .In reality the banks or the moneylenders keep revolving the money which they might not even have.

I hope you have some idea now. The OS also works like this .It assures every process 4GB of memory .Right now there are 119 processes running on my laptop .If we go by this fact then the OS is ensuring 476 GB RAM to the processes .But I have only 2GB RAM on this laptop. That’s where the Virtual memory comes into picture .This 476 GB is actually a virtual memory address space and nothing else ; which does physically not exist .OS memory managers maps this virtual memory to Physical memory (RAM) .During this process the Page file on the disk is also used if a thread needs more physical memory than available .

Let’s skip discussing about AWE, /3GB, /USERVA and PAE for now as it will divert us from the topic which is to know how the Virtual addresses get translated to Physical addresses in RAM.

Address translation is the process of translating the virtual memory to physical memory.

Every Virtual Address has 3 components:

The Page Directory Index : For each process the OS memory manager creates this directory to use it to map all the page tables for the process.The address of this directory in stored somewhere in the address space called as KProcess Block (Kernal Process Block). To keep this subject less complex I will not explain what Kprocess Block is .The CPU keeps track of this Page Directory Index via a register called as CR3 or Control Register 3 .This Register also resides in KProcess Block .So the CPU’s MMU knows where the Page Directory Index is located with the help of This register (MMU: ttp://en.wikipedia.org/wiki/Memory_management_unit ).So the first 10 bits of the address space pointer has Page Directory Index value (there are a lot of page directory entries).This tells Windows which page table to use to locate the physical memory
associated with the address.

The Page Table Index : The second 10 bits of a 32-bit virtual address provide an index into this table and indicate which page table entry (PTE) contains the address of the page in physical memory to which the virtual address is mapped.

The Byte Index: the last 12 bits of a 32-bit virtual address contain the byte offset on the physical memory page to which the virtual address refers. The system page size determines the number of bits required to store the offset. Since the system page size on x86 processors is 4K, 12 bits are required
to store a page offset (4,096 = 2^12).

Let’s summarize it now:
1. The CPU’s Memory Management Unit locates the page directory for the process using the special register mentioned above.
2. The page directory index (from the first 10 bits of the virtual address) is used to locate the (P)age(D)irectory(E)ntry that identifies the page table needed to map the virtual address to a physical one.
3. The page table index (from the second 10 bits of the virtual address) is used to locate the PTE that maps the physical location of the virtual memory page referenced by the address.
4. The PTE is used to locate the physical page. If the virtual page is mapped to a page that is already in physical memory, the PTE will contain the page frame number (PFN) of the page in physical memory
that contains the data in question. If the page is not in physical memory, the MMU raises a page fault, and the Windows page fault–handling code attempts to locate the page in the system paging file. If the page can be located, it is loaded into physical memory, and the PTE is updated to reflect its location. If it cannot be located and the translation is a user mode translation, an access violation occurs because the virtual address references an invalid physical address. If the page cannot be located and the translation is occurring in kernel mode, a bug check (also called a blue screen) occurs.

How the Address translation happens with PAE in place:
Everything is same as above except that:
1)There is a new table which is above PDEs and PTEs .Its Page Directory Pointer Table.
2)The PTEs and PDEs are 64 bit wide as compared to 32 bit wide when PAE is not enabled.

How to resolve setup errors when registry keys are missing (Failed to install and configure assemblies C:\Program Files (x86)\Microsoft SQL Server\90\NotificationServices\9.0.242\Bin\microsoft.sqlserver.notificationservices.dll in the COM+ catalog)

2011-06-18T14:58:00.001+05:30

Hi Guys ,
A couple of weeks back we had a setup issue where the SP3 setup was failing on Notification services and Client components .I am sharing my experience with you because it took us a lot of time to figure out that a few registry keys were missing .This explanation has 3 parts viz.

First Part : Solution of the issue .
Second Part : Reproducing this issue and finding a solution in a better way so that the same type of process can be followed for other similar setup issues as well .
Third Part : 2 bugs (Unfortunately , I could not reproduce it on my machine, but repeatedly reproduced it on clients machine .Will still try on my machine and file them later.

First Part : Issue and its solution .
Issue
As per the Security Bulitin MS09-004 we rasied a change to patch the DEV (and later would be Prod Servers)server to SQL Sevrer 2005 SP3 .Since we were failing on the DEV Server , we were not able to initiate the setup on Prod until the setup on Dev is resolved . Till then the Prod was under potential threat of SQL injection. The issue was that we were not able to upgrade SQL Server 2005 RTM (32 bit) to SQL Server 2005 SP3 (32 bit) on Windows Server 2008 (64 bit) .

Error Messages
Setup Errors :
Failed to install and configure assemblies C:\Program Files (x86)\Microsoft SQL Server\90\NotificationServices\9.0.242\Bin\microsoft.sqlserver.notificationservices.dll in the COM+ catalog.
Error: 2148734209
Error message: Unknown error 0x80131501
Error descrition: MSDTC was unable to read its configuration information. (Exception from HRESULT: 0x8004D027)

Errors in Application log :
Unable to get the file name for the OLE Transactions Proxy DLL. Error Specifics: hr = 0x8004d027,
d:\rtm\com\complus\dtc\dtc\xolehlp\xolehlp.cpp:176, CmdLine: C:\Windows\syswow64\MsiExec.exe -Embedding 0E2EA3ADA5DC17201B521333814654B2 C, Pid: 3484

Failed to read the name of the default transaction manager from the registry. Error Specifics: hr = 0x00000002,
d:\rtm\com\complus\dtc\dtc\xolehlp\xolehlp.cpp:382, CmdLine: C:\Windows\syswow64\MsiExec.exe -Embedding 0E2EA3ADA5DC17201B521333814654B2 C, Pid: 3484

Errors in SQL Server :
QueryInterface failed for "ITransactionDispenser": 0x8004d027(XACT_E_UNABLE_TO_READ_DTC_CONFIG).

Client Environment Details
SQL Server 2005 RTM 32 bit Standard Edition (WOW mode)
Windows 2008 64 bit Standard

Troubleshooting done
-> The setup was failing only for Notification services and Client tools .Rest all components were successfully upgraded to SP3 .
-> Found the Both Notification services and Client tools were failing because of same error :

---------------------------------------------------------------------------Product : Client Components
Product Version (Previous): 1399
Product Version (Final) :
Status : Failure
Log File : C:\Program Files (x86)\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQLTools9_Hotfix_KB955706_sqlrun_tools.msp.log
Error Number : 29549
Error Description : MSP Error: 29549 Failed to install and configure assemblies c:\Program Files (x86)\Microsoft SQL Server\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)
---------------------------------------------------------------------------

---------------------------------------------------------------------------Product : Notification Services
Product Version (Previous): 1399
Product Version (Final) :
Status : Failure
Log File : C:\Program Files (x86)\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQLTools9_Hotfix_KB955706_sqlrun_tools.msp.log
Error Number : 29549
Error Description : MSP Error: 29549 Failed to install and configure assemblies c:\Program Files (x86)\Microsoft SQL Server\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)
---------------------------------------------------------------------------
-> Detailed MSP logs showed that the error comes when MSDTC is trying to register the notification services using regsvcs

Error: 2148734209
Error message: Unknown error 0x80131501
Error descrition: MSDTC was unable to read its configuration information. (Exception from HRESULT: 0x8004D027)
Error Code: -2146233087
Windows Error Text: Source File Name: sqlca\sqlassembly.cpp
Compiler Timestamp: Sat Oct 25 08:47:00 2008
Function Name: Do_sqlAssemblyRegSvcs
Source Line Number: 155

-> So the issue was with registering Notification Services DLL file which was common to both NS and Client components .As you can see RegSvcs was being called from inside the function Do_sqlAssemblyRegSvcs.

-> We then tried to manually register the microsoft.sqlserver.notificationservices.dll through command prompt :
%windir%\Microsoft.NET\Framework64\v2.0.50727\RegSvcs.exe /fc "C:\Program Files (x86)\Microsoft SQL Server\90\NotificationServices\9.0.242\Bin\microsoft.sqlserver.notificationservices.dll"

-> This did not work and we were getting some other error .

-> Since COM+ Catalog was also showing up in the error ,I ran the SQL Server RTM setup and found the the SCC ( System configuration check ) is failing on COM + Catalog requirements .
->So,I checked the Component Services (DCOMCNFG) and found that there are no issues there .Everything was working fine .We also noticed that there were no errors related to COM components in the Application logs as well except the error related to MSDTC .The 32 bit COM components were also running fine .This was strange but made me believe that this error might be misleading .

-> We checked the SQL Sevrer errorlogs and found the same entry related to MSDTC , but in a slightly different manner :
QueryInterface failed for "ITransactionDispenser": 0x8004d027XACT_E_UNABLE_TO_READ_DTC_CONFIG)

-> We also saw some errors related to MSDTC :
Unable to get the file name for the OLE Transactions Proxy DLL.
Error Specifics: hr =
0x8004d027,d:\rtm\com\complus\dtc\dtc\xolehlp\xolehlp.cpp:176, CmdLine: C:\Windows\syswow64\MsiExec.exe
-Embedding -E2EA3ADA5DC17201B521333814654B2 C, Pid: 3484

Failed to read the name of the default transaction manager from the registry. Error Specifics: hr = 0x00000002,
d:\rtm\com\complus\dtc\dtc\xolehlp\xolehlp.cpp:382,
CmdLine: C:\Windows\syswow64\MsiExec.exe
-Embedding 0E2EA3ADA5DC17201B521333814654B2 C, Pid: 3484

-> Now the picture was clear that MSDTC had issues for sure. Also the COM+ Catalog warning is related to MSDTC issue (I had resolved the same issue a few years back :http://ms-abhay.blogspot.com/2009/10/msdtc-was-unable-to-read-its.html. But it was on Win server 2003)

-> The first error code is same 0x8004d027 but the second error code , which is coming before 0x8004d027 is 0x00000002 is nothing but telling us that there is some registry key missing ( there might be more ) . error 2 means "System cannot find the file specified".

-> Since we were not sure of which key was missing we decided to uninstall and reinstall MSDTC . This will automatically recreate all the missing registry keys.

-> But there was some more twist left. On windows Server 2008 we cannot simply uninstall MSDTC by using command "msdtc -uninstall" .We have to remove the MSDTC server role >> reboot the server >> re-add MSDTC in the Server roles.

-> We tried that ,but even after removing MSDTC from the server role , it was still showing as running in the services console .We tried a few times without success.

-> In between we also tried to repair .net framework 3.5 SP1 which did not help .

-> I also went through this article which talks about making sure that all the MSDTC related keys are present in the registry hive:
http://msdn.microsoft.com/en-us/library/dd300421(v=WS.10).aspx(This was the 1st step towards solution , although it did not work.)

-> However , the keys mentioned in this article were present in both WOW mode and normal mode in the Registry .

-> Then on my machine I tried to find the registries with the value "OLETransactionManagers". The first Key hit was : HKEY_CLASSES_ROOT\OLETransactionManagers

-> Since SQL Server was running in WOW mode on client's server , we tried to find HKEY_CLASSES_ROOT\Wow6432Node\OLETransactionManagers key and got it in first attempt.

-> However , on my laptop this key had some values while there were no values on the Client side registry key (mentioned above).

-> We found that these 64 bit MSDTC registry keys were there but the 32 bit (WOW mode) registry keys related to MSDTC were missing .

-> We created these keys in WOW mode

HKEY_CLASSES_ROOT\Wow6432Node\OLETransactionManagers
String value name = DefaultTM and value data=MSDTC

HKEY_CLASSES_ROOT\Wow6432Node\OLETransactionManagers\MSDTC
String value name=DLL and value data=MSDTCPRX.DLL

->We restarted MSDTC and SQL Server . We checked the SQL Server errorlogs and found that MSDTC related error is no longer showing and also the SQL Server SCC check was not showing that error related to COM+ Catalog any more.

->This gave us some hope and with that hope we hoped that the setup will run successfully .This in turn did happen (",).

Second Part: Reproducing the error
I reproduced this on my machine by removing all the keys from HKEY_CLASSES_ROOT\OLETransactionManagers

When I ran the setup it gave me the same warnings on SCC :

I will still go ahead with the setup and select only the client components to install :

The setup will encounter this error:

Let’s check the Application logs:

At this stage First let me introduce a tool that can show you how to find the missing registry key or if there are any permission related issues .I have been using this tool since long now .This tool is Procmon from sysinternals http://technet.microsoft.com/en-us/sysinternals/bb896645".We will use this tool to find the missing registry keys .However this tool captures a lot of information of all the processes .So we need to first PAUSE it as soon as we launch it .

Now, we will find the process ID of MSIEXEC.exe from Task Manager since as per the Application logs its the msiexec command that is failing:

I will now filter it on those 3 PIDs

Once it’s done, uncheck the PAUSE icon (the magnifying glass) and click on the retry option on the setup window. Quickly after that ,again PAUSE the Procmon by clicking on the magnifying glass icon. Notice the output in the Procmon. You will see a lot of different keys there .We now need to delete not necessary entries:

Right click on SUCCESS and select:

Right click on RegOpenKey and select:

You are now left with only 6 keys to look at(You can follow the same steps for other missing Reg keys issues ) and all these keys are REGQueryValue:

Now ,notice the error copied above from application log:
*******

Now, double click on each of the 6 keys (start from the bottom most) and select the option >> PROCESS. You will notice the same command line string as showing in the application logs:

So the first key that is missing is HKCR\OLETransactionManagers\DefaultTM .The reason it’s not showing all the missing keys is because it’s failing on first key itself. If you create the first key and then click on retry button on the setup the Procmon will show you the next missing key.

But what should be its value .We can check on other servers (preferably same server version).Create the missing keys and click on retry button .The setup will be successful (in our case it was SP3 and not the initial setup .But the resolution is same).

Third part : The Bugs
1) The issue is , when the SP3 setup fails it should ideally rollback everything to normal .So the NS and client tools should work fine.But On the client's server we saw that the Management Studio stop working and throws an error ( I dont have the screenshot now . Will try and reproduce it ) . The only solution is to uninstall the client tools and reinstall again .During the setup you will again get this error .Click on ignore and the setup will complete .

2) On my laptop , I saw that it also corrupt other components of SQL Server 2008 like Books on Line ..Again I do not have that proof now but will reproduce it .The solution is to uninstall client tools and reinstall tools of SQL Server 2008.

Happy Learning !!

Understanding how SQL Server behaves in a non-preemptive mode while still running on the OS which is preemptive

2011-06-18T14:04:00.000+05:30

Today , I was asked by someone how SQL Server behaves in a non-preemptive way while still running on the OS which is preemptive .Even though I explained this theoritically ,I was requested if there is a way we can see it practically . It wasn't difficult but it really proved to what I explained .I alos felt that if we add practicals to theory it will have much deeper impact ...

NOTE : I am doing all the testing on SQL Server 2008 RTM EVAL as what I am trying to explain can be done in 2008 onwards.On 2005 you will not see what is written here.So I would request to use SQL 2008 and above .

Windows Scheduling (Preemptive) :
Starting from Windows NT 3.1 (XP ,2000,2003 etc) Windows scheduling was priority driven i.e preemptive scheduling .So every thread will have a priority associated with it .Based on this priority the threads will get the time slice (Quantum) to run on the CPU .So , even if thread of lower priority is running and all of a sudden another thread of a higher
priority comes up , the low priority thread will be preempted interrupted) and the higher priority thread will be scheduled to run on the CPU.However , the scheduler is smart enough .It will keep the preempted thread on the top of the waiting threads by adjusting its priority (Lets not get too deep in to this at this point).

Prior to this OS scheduling was non-preemptive i.e. cooperative scheduling .Remember the days when Windows 98 use to hang and we use to reboot the server quite often ,to get rid of it.Cooperative scheduling is good if all the threads leave the CPU after some time and give chance to other threads (including kernal mode threads which are more
Important and get a chance to run whenever required) after a fixed interval of time . But that normally does not happen .some nasty application threads don't yield and hence blocks other threads.

SQL Server Scheduling (non-preemptive) :
SQL Server has its own scheduling mechanism and it does not follow OS scheduling (looks strange as it runs on the preemptive OS) .Its called as UMS (User Mode Scheduling) in 2000 and SOS (SQL OS) in 2005 and above .BUT :
1) Why SQL Server does not hang just like windows 98 use to ?
Answer: SQL Server does not hang because its threads yield every voluntarily.In case a thread does not yield in 60 seconds (unlike the faulty application where the threads does not yield)SQL Server throws non-yielding scheduler hung error and throws a mini dump with the stack information of all the threads in it .

2) How SQL Server manages to schedule in the non-preemptive way ?
Answer: Windows OS will not schedule any thread which is running in infinite wait loop and simply ignores it. SQL Server (actually UMS) takes advantage of this and cleverly puts all the threads which it does not want to schedule to infinitely sleep by calling WaitforsingleObject function in an infinite loop .When SQL Server wants the thread to run it simply signal the thread and it comes out of the sleep modes .Its the Windows which then schedules the thread .Its important to know that UMS schedules only ONE SQl Server thread per CPU .However , there is an exception to this . There are moments where to complete a task the thread leaves the SQL Server scheduler and goes to preemptive mode scheduling . For example using xp_cmdshell to open notepad or running an extended stored procedure that deals with filesystem (like reading a file) or a linked server query
etc.In that situation , you will see more than one thread on a single CPU in runnable status .That is because one thread is scheduled via UMS\SOS and the other one directly via OS scheduling.

Let me show you a Demo since my Laptop has only one dual core processor (Its SQL Server 2008 RTM):
Lets first run a simple query and find runnable and sleeping threads Select Status ,* from sysprocesses where status not in ('background')

you will notice that all the SPIDS will show you the status of sleeping and there is only one SPID that will be showing you the status of runnable .Its waittype will be PREEMPTIVE_os_WAITFORSINGLEOBJECT .Notice that only runnable SPID has a KPID associated with it .This KPID is nothing but the worker thread associated with the SPID.You can run it a few times but the output will not change except the KPID which means that one thread is yielding to another after the context switching .The reason why we see runnable state and not running ,because by the time we get the query output the thread again goes to runnable state.You might also see other runnable or suspended SPIDs but its because they are running in preemptive mode .

Now lets open another Query window and execute the same command there .
Select Status ,* from sysprocesses where status not in ('background'). This time its SPID 53 (current SPID on my machine) which is showing us the Runnable state while SPID 52 (the previous SPID)is now sleeping .

Lets do one more experiment.Open a new query window (SPID 51 in my case) and run select @@servicename around 1000 times . Come back to SPID 53 window and notice if the runnable state is showing for SPID 53 or SPID 51.You will notice that we have the SPID 51 doing its task .But why is it showing as sleeping while CPU value is still increasing ? The reason is that when we run the query via SPID 53 , during (and only) that time 51 shows as sleeping because 53 needs to run . So thread related to 51 yields voluntarily. when this query finishes , SPID 51 again picks up , but we cant see that since we have only one processor :) ...

Anyway , let me show you the small test when the SQL Server thread goes preemptive .We have 2 Query windows.One with sysprocesses query (SPID 53) and one with calling xp_readerrorlog 100 times (SPID 51).I further modified my sysprocesses query by filtering sleeping SPIDs.

Select Status ,* from sysprocesses where status not in ('background','sleeping') .Lets run the query through SPID 51 and then by SPID 53.Notice that you have 2 runnable SPIDs now . Thats because SPID 51 is scheduled by OS and not SQL OS \UMS .

Have a nice day and Happy Learning !!!

Finding optimal number of CPUs for a given long running CPU intensive queries (except OLAP queries)

2011-04-27T13:23:00.000+05:30

Hi Guys ,

Hope this article will help you in some or the other way one day :) .....

Introduction:
This small article is applicable for finding optimal number of CPUs for long running CPU intensive queries/workload that doesn’t frequently wait for other resources and is not applicable if your queries/workload is often waiting for resources (like I/Os, Locks, Latches etc.) without consuming CPU in a stretch .it can also provide information on uneven CPU load across NUMA nodes and uneven CPU load within same NUMA node (load_factor effect).
It is recommended to analyze Windows Performance Monitor Counters for monitoring CPU pressure. Processor utilization greater then 75% to 80% indicates CPU pressure. Using Windows Performance Monitor should be the 1st step, the procedure suggested in this article should be considered as an additional step.
Further ,it is very important to find ways to optimize the queries/workload by tuning the database schema before attempting to add additional CPUs.

Description:
When a customer asks you: I am running a resource consuming SQL job and it takes x amount of time, how can I reduce the time so the SQL job completes sooner, can I add more CPUs ? if yes, how many ?
When you see CPU pressure, there are 2 options: you can either upgrade to faster CPUs or add additional CPUs [assuming that the queries are well tuned and normalized]. Upgrading to faster CPU will always help. Adding additional CPUs may not always help the SQL job to run faster unless that SQL job can take advantage of additional CPUs [read Max Degree of parallelism form BOL]. If the customer already has the fastest CPUs available in the market then they have to wait for the next release of faster CPUs. One more choice woiuld be to add additional CPUs and see if it helps, the below procedure will help you identify if this is the case.
This method calculates total user waits for CPU during the SQL workload and suggests additional CPUs if necessary. If CPU usage is at 100%, but no one waited for CPU during the workload, then adding additional CPU will not help; this is the basics of this calculation.
Current recommendations that are available on this topic calculates ‘signal wait time’ to ‘wait time’ ratio to suggest CPU pressure – but this cannot help one easily identify number of additional CPUs necessary.

Procedure:
When concurrent users apply simultaneous CPU intensive workload, there could be CPU pressure. We can conclude presence of CPU pressure when at any given moment during this time period at least one or more user tasks waited for CPU resource.
In this case one can run the below query to find out how many CPU on an average will help to scale(out) the workload better. It might be more informative to collect the below information in short time intervals (many samples) than just once to understand during which time of the workload application the CPU pressure was the most. Single sample will lead to average additional CPUs necessary for the entire workload duration.
1. Reset Wait Stats
dbcc sqlperf('sys.dm_os_wait_stats', clear)
2. Apply workload (you can find sample workload query at the end of this article, you need to execute the sample workload query simultaneously in many sessions to simulate concurrent user tasks).
3. Run the below query to find Additional CPUs Necessary – it is important to run the query right after the workload completes to get reliable information.

select round(((convert(float, ws.wait_time_ms) / ws.waiting_tasks_count) / (convert(float, si.os_quantum) / si.cpu_ticks_in_ms) * cpu_count), 2) as Additional_CPUs_Necessary,
round((((convert(float, ws.wait_time_ms) / ws.waiting_tasks_count) / (convert(float, si.os_quantum) / si.cpu_ticks_in_ms) * cpu_count) / hyperthread_ratio), 2) as Additional_Sockets_Necessary
from sys.dm_os_wait_stats ws cross apply sys.dm_os_sys_info si where ws.wait_type = 'SOS_SCHEDULER_YIELD'

Example:
When you have 2 CPUs and you run the sample workload with just 1 or 2 concurrent sessions – you will see no recommendation for addition additional CPUs – unless there is unbalanced user task distribution across CPUs. On the other hand if you run the workload with 4 concurrent sessions – you will notice the query suggests you to add 2 additional CPUs. If you run with 6 concurrent sessions – you will notice the query suggests you to add 4 additional CPUs.
If each workload runs in parallel (MAXDOP not 1), then you will notice additional CPU suggestion, you need to be careful in this case. For example with 2 CPUs when you run the workload (in parallel, MAXDOP 0/2) with 2 concurrent sessions, you will notice the suggestion to add 2 additional CPUs – this just indicates the workload is more scalable with more CPUs – parallel query execution as you can imagine can consume as many CPUs as you have and can consume more!!
The results are not reliable when other applications are running in the system. Also the results might be incorrect on a hyper threading enabled system.

Explanation:
When there are more user tasks concurrently needing CPU than available CPU, the excess user tasks will wait for CPU (there are exceptions when the workload is not evenly distributed across CPUs). In this case each user task uses its quantum, then goes into a wait state (waiting for CPU with wait_type SOS_SCHEDULER_YIELD. sys.dm_exec_requests doesn’t show this wait type, probably by design to avoid showing user tasks in wait state when they are waiting for CPU. But sys.dm_os_wait_stats will include these waits) until all other runnable user tasks have used their quantum. If one measures how many tasks went into this wait state and for how long while the workload was applied – it is possible to calculate the CPUs necessary to scale the workload better.
runnabkle_task_count from the dm_os_schedulers is also a indication for CPU pressure, but it is just a probe – one cannot reasonably conclude the number of CPUs necessary for a given workload.

Exception:
There is an exception(for OLTP like workload) where a user tasks doesn’t consume all of its quantum(goes into some other wait state before the quantum expires, waiting for I/Os, Locks, Latches etc.) in a stretch, but continues to run in a loop using CPU without using its full quantum(You know what quantum is ...right :D). The method mentioned here cannot calculate the necessary additional CPUs in this case.. Most common example is short transactions using part of its quantum and starts WRITELOG waits and continues in a loop – inserts using implicit transactions in a loop is a typical example.

Sample Workload:
Create the below table before running the query to generate CPU intensive workload.
Serial Workload:
select max(t1.c2 + t2.c2) from tab7 t1 cross join tab7 t2 option (maxdop 1)
Parallel Workload:
select max(t1.c2 + t2.c2) from tab7 t1 cross join tab7 t2
Table:
create table tab7 (c1 int primary key clustered, c2 int, c3 char(2000))
go
begin tran
declare @i int
set @i = 1
while @i <= 5000
begin
insert into tab7 values (@i, @i, 'a')
set @i = @i + 1
end
commit tran
go

Happy Learning !!!

SQL Server Setup has encountered the following error:File format is not valid

2010-11-09T22:19:00.000+05:30

Today , we faced an issue where SQL Server 2008 R2 setup was failing at the very beginning .This issue can also be reproduced on 2008 and also in 2005 (in a slightely different way) .Please find the RCA below :

Version : SQL Server 2008 R2
OS : Win Server 2008

Error :
TITLE: SQL Server Setup failure.
------------------------------
SQL Server Setup has encountered the following error:
File format is not valid..
------------------------------
BUTTONS:
OK
------------------------------

Resolution :
Its very clear that there is a file that does not have a correct format and SQL Server cannot read it .So we need to find which file is that .We need to first open the Setup logs .In SQL 2008 a folder is created with the timstamp and all the logs are created inside it .On my machine it was C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20101109_201205 .

I first opened Detail_ComponentUpdate.txt

2010-11-09 20:13:14 Slp: Running Action: GatherUserSettings
2010-11-09 20:13:21 Slp: -- PidPublicConfigObject : ValidateSettings is normalizing input pid=[PID value hidden]
2010-11-09 20:13:21 Slp: -- PidPrivateConfigObject : NormalizePid is normalizing input pid=[PID value hidden]
2010-11-09 20:13:21 Slp: -- PidPrivateConfigObject : NormalizePid found a pid containing dashes, assuming pid is normalized, output pid=[PID value hidden]
2010-11-09 20:13:21 Slp: -- PidPublicConfigObject : ValidateSettings proceeding with normalized pid=[PID value hidden]
2010-11-09 20:13:21 Slp: -- PidPrivateConfigObject : Initialize is initializing using input pid=[PID value hidden]
2010-11-09 20:13:21 Slp: -- PidPrivateConfigObject : NormalizePid is normalizing input pid=[PID value hidden]
2010-11-09 20:13:21 Slp: -- PidPrivateConfigObject : NormalizePid found a pid containing dashes, assuming pid is normalized, output pid=[PID value hidden]
2010-11-09 20:13:21 Slp: -- PidPrivateConfigObject : Initialize proceeding with normalized pid=[PID value hidden]
2010-11-09 20:13:21 Slp: -- PidPrivateConfigObject : Initialize called ValidatePid, output is pid=[PID value hidden] validateSuccess=True output editionId=EVAL(0x2467BCA1)
2010-11-09 20:13:21 Slp: -- PidPublicConfigObject : ValidateSettings initialized private object, result is initializeResult=Success

2010-11-09 20:13:22 Slp: Detected localization resources folder: 1033
2010-11-09 20:13:22 Slp: License file: C:\Documents and Settings\Abhay\Desktop\2008 R2 X64\2008_R2_x86\x86\1033\License_EVAL.rtf
2010-11-09 20:13:22 Slp: Error: Action "GatherUserSettings" threw an exception during execution.

2010-11-09 20:13:22 Slp: Microsoft.SqlServer.Setup.Chainer.Workflow.ActionExecutionException: Thread was being aborted. ---> System.Threading.ThreadAbortException: Thread was being aborted.
2010-11-09 20:13:22 Slp: at System.Threading.WaitHandle.WaitOneNative(SafeWaitHandle waitHandle, UInt32 millisecondsTimeout, Boolean hasThreadAffinity, Boolean exitContext)
2010-11-09 20:13:22 Slp: at System.Threading.WaitHandle.WaitOne(Int64 timeout, Boolean exitContext)
2010-11-09 20:13:22 Slp: at System.Threading.WaitHandle.WaitOne(Int32 millisecondsTimeout, Boolean exitContext)
2010-11-09 20:13:22 Slp: at System.Threading.WaitHandle.WaitOne()
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Configuration.UIExtension.Request.Wait()
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Configuration.UIExtension.UserInterfaceProxy.SubmitAndWait(Request request)
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Configuration.UIExtension.UserInterfaceProxy.NavigateToWaypoint(String moniker)
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Configuration.UIExtension.UserInterfaceService.Waypoint(String moniker)
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Configuration.UIExtension.WaypointAction.ExecuteAction(String actionId)
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.InvokeAction(WorkflowObject metabase, TextWriter statusStream)
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Setup.Chainer.Workflow.PendingActions.InvokeActions(WorkflowObject metaDb, TextWriter loggingStream)
2010-11-09 20:13:22 Slp: --- End of inner exception stack trace ---
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Setup.Chainer.Workflow.PendingActions.InvokeActions(WorkflowObject metaDb, TextWriter loggingStream)
2010-11-09 20:13:25 Slp: Received request to add the following file to Watson reporting: C:\Documents and Settings\Abhay\Local Settings\Temp\tmp11E.tmp
2010-11-09 20:13:25 Slp: The following is an exception stack listing the exceptions in outermost to innermost order
2010-11-09 20:13:25 Slp: Inner exceptions are being indented
2010-11-09 20:13:25 Slp:
2010-11-09 20:13:25 Slp: Exception type: System.ArgumentException
2010-11-09 20:13:25 Slp: Message:
2010-11-09 20:13:25 Slp: File format is not valid.
2010-11-09 20:13:25 Slp: Stack:
2010-11-09 20:13:25 Slp: at System.Windows.Forms.RichTextBox.StreamIn(Stream data, Int32 flags)
2010-11-09 20:13:25 Slp: at System.Windows.Forms.RichTextBox.LoadFile(Stream data, RichTextBoxStreamType fileType)
2010-11-09 20:13:25 Slp: at System.Windows.Forms.RichTextBox.LoadFile(String path, RichTextBoxStreamType fileType)
2010-11-09 20:13:25 Slp: at Microsoft.SqlServer.Configuration.InstallWizard.EULAPidView.UpdateLicenseText(String filepath)
2010-11-09 20:13:25 Slp: at Microsoft.SqlServer.Configuration.InstallWizard.EULAPidController.LoadData()
2010-11-09 20:13:25 Slp: at Microsoft.SqlServer.Configuration.InstallWizardFramework.InstallWizardPageHost.PageEntered(PageChangeReason reason)
2010-11-09 20:13:25 Slp: at Microsoft.SqlServer.Configuration.WizardFramework.UIHost.set_SelectedPageIndex(Int32 value)
2010-11-09 20:13:25 Slp: at Microsoft.SqlServer.Configuration.WizardFramework.UIHost.GoNext()
2010-11-09 20:13:25 Slp: at Microsoft.SqlServer.Configuration.WizardFramework.NavigationButtons.nextButton_Click(Object sender, EventArgs e)
2010-11-09 20:13:25 Slp: at System.Windows.Forms.Control.OnClick(EventArgs e)
2010-11-09 20:13:25 Slp: at System.Windows.Forms.Button.OnClick(EventArgs e)
2010-11-09 20:13:25 Slp: at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
2010-11-09 20:13:25 Slp: at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
2010-11-09 20:13:25 Slp: at System.Windows.Forms.Control.WndProc(Message& m)
2010-11-09 20:13:25 Slp: at System.Windows.Forms.ButtonBase.WndProc(Message& m)
2010-11-09 20:13:25 Slp: at System.Windows.Forms.Button.WndProc(Message& m)
2010-11-09 20:13:25 Slp: at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
2010-11-09 20:13:25 Slp: at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
2010-11-09 20:13:25 Slp: at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
2010-11-09 20:28:33 Slp: Sco: Attempting to write hklm registry key SOFTWARE\Microsoft\Microsoft SQL Server to file C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20101109_201205\Registry_SOFTWARE_Microsoft_Microsoft SQL Server.reg_
2010-11-09 20:28:33 Slp: Sco: Attempting to write hklm registry key SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall to file C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20101109_201205\Registry_SOFTWARE_Microsoft_Windows_CurrentVersion_Uninstall.reg_
2010-11-09 20:28:33 Slp: Sco: Attempting to write hklm registry key SOFTWARE\Microsoft\MSSQLServer to file C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20101109_201205\Registry_SOFTWARE_Microsoft_MSSQLServer.reg_
2010-11-09 20:28:36 Slp: File format is not valid.
2010-11-09 20:28:36 Slp: Watson bucket for exception based failure has been created
2010-11-09 20:28:36 Slp: Sco: Attempting to create base registry key HKEY_LOCAL_MACHINE, machine
2010-11-09 20:28:36 Slp: Sco: Attempting to open registry subkey Software\Microsoft\PCHealth\ErrorReporting\DW\Installed
2010-11-09 20:28:36 Slp: Sco: Attempting to get registry value DW0200
2010-11-09 20:29:01 Slp: Submitted 1 of 1 failures to the Watson data repository

Assuming that there is a file with incorrect format I took a chance to open this file as mentioned in the error above .C:\Documents and Settings\Abhay\Desktop\2008 R2 X64\2008_R2_x86\x86\1033\License_EVAL.rtf . Since this is an RTF file we can open it in WORDPAD ....

When opened , I found it unreadable ...Initially I thought it is suppose to be like that as there might be something encrypted .
However , there were other license files in the same folder which were absolutely readable ..

This made me curious and I checked the same file on my machine as I also had the same EVAL setup ...I was able to read it word by word .So it was clear that the file was corrupt ....We tried and swapped the file between my TP and the server ...The setup moved forward :) .

Hope this helps .

Error 1706. An installation package for the product Microsoft SQL Server 2005 cannot be found. Try the installation again using a valid copy of the installation package 'SqlRun_SQL.msi'.

2010-11-09T22:12:00.000+05:30

adding a new post after a good gap ...
Recently we faced an issue where we lost physical files of Master database (master.mdf and mastlog.ldf).We had the backup files but we could not use them unless SQL Server is up and running .So we had no choice but to rebuild master .

We tried the step below via DOS prompt :
C:\Documents and Settings\Abhay\Desktop\softwares\SQLEVAL_2005\Servers>start /wait setup.exe /qn INSTANCENAME=CORRUPT REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=XXXXX

This had always worked for me and is also mentioned in BOL .However , this time this did not work .The errors I got in the log file were (to find the error we should search for Return Value 3):

Error 1706. An installation package for the product Microsoft SQL Server 2005 cannot be found. Try the installation again using a valid copy of the installation package 'SqlRun_SQL.msi'.
MSI (s) (2C:08) [14:07:26:265]: User policy value 'DisableRollback' is 0
MSI (s) (2C:08) [14:07:26:265]: Machine policy value 'DisableRollback' is 0
Action ended 14:07:26: InstallFinalize. Return value 3.

The setup files were valid and had been used many time in the past ..I ran SQLRun_SQL.msi manually and it was running fine .I also used 2 different setups and go the same error .
Also

MSI (s) (2C:08) [14:07:26:281]: File: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\modellog.ldf; To be installed; Won't patch; No existing file
MSI (s) (2C:08) [14:07:26:281]: Executing op: FileCopy(SourceName=C:\Config.Msi\3bf6ceb.rbf,,DestName=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\model.mdf,Attributes=32800,FileSize=1245184,PerTick=0,,VerifyMedia=0,ElevateFlags=3,,,,,,,InstallMode=4194308,,,,,,,)
MSI (s) (2C:08) [14:07:26:281]: File: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\model.mdf; To be installed; Won't patch; No existing file
MSI (s) (2C:08) [14:07:26:296]: Executing op: FileCopy(SourceName=C:\Config.Msi\3bf6cea.rbf,,DestName=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdblog.ldf,Attributes=32800,FileSize=786432,PerTick=0,,VerifyMedia=0,ElevateFlags=3,,,,,,,InstallMode=4194308,,,,,,,)
MSI (s) (2C:08) [14:07:26:296]: File: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdblog.ldf; To be installed; Won't patch; No existing file
MSI (s) (2C:08) [14:07:26:296]: Executing op: FileCopy(SourceName=C:\Config.Msi\3bf6ce9.rbf,,DestName=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdbdata.mdf,Attributes=32800,FileSize=12255232,PerTick=0,,VerifyMedia=0,ElevateFlags=3,,,,,,,InstallMode=4194308,,,,,,,)
MSI (s) (2C:08) [14:07:26:296]: File: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdbdata.mdf; To be installed; Won't patch; No existing file
MSI (s) (2C:08) [14:07:26:296]: Executing op: FileCopy(SourceName=C:\Config.Msi\3bf6ce8.rbf,,DestName=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mastlog.ldf,Attributes=32800,FileSize=853016576,PerTick=0,,VerifyMedia=0,ElevateFlags=3,,,,,,,InstallMode=4194308,,,,,,,)
MSI (s) (2C:08) [14:07:26:296]: File: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mastlog.ldf; To be installed; Won't patch; No existing file
MSI (s) (2C:08) [14:07:26:312]: Executing op: FileCopy(SourceName=C:\Config.Msi\3bf6ce7.rbf,,DestName=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf,Attributes=32800,FileSize=92602368,PerTick=0,,VerifyMedia=0,ElevateFlags=3,,,,,,,InstallMode=4194308,,,,,,,)
MSI (s) (2C:08) [14:07:26:312]: File: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf; To be installed; Won't patch; No existing file

This was strange as these files were existing ....

I ran the same command using \qb option and got the same error but in a form of a pop-up box.

I then checked and found that there is an option called as REINSTALLMODE .;REINSTALLMODE is used to repair installed components. The supported values are:
O – Reinstall if file is missing, or an older version is present.
M – Rewrite machine specific reg keys under HKLM
U – Rewrite user specific reg keys under HKCU
S – Reinstall all shortcuts

The Option O looked appropriate but I used all i.e.
C:\Documents and Settings\Abhay\Desktop\softwares\SQLEVAL_2005\Servers>start /wait setup.exe /qn INSTANCENAME=CORRUPT REINSTALL=SQL_Engine REINSTALLMODE=OMUS REBUILDDATABASE=1 SAPWD=XXXXX

This resolved the issue on my laptop but not on client server .
Finally I found that there is one more option which is never documented ...and this option is V
The issue was that the setup was copied for a different server and the original media location of where the RTM bits where installed in some cache file.That was the reason we were getting the error about the installation package not being found.To resolve this we had to use the option V to re-cache the media from the new location.

C:\Documents and Settings\Abhay\Desktop\softwares\SQLEVAL_2005\Servers>start /wait setup.exe /qn INSTANCENAME=CORRUPT REINSTALL=SQL_Engine REINSTALLMODE=V REBUILDDATABASE=1 SAPWD=XXXXX

This ran like a knife through butter ..

Hope it will help you in future ...

Error: 26049, Severity: 16, State: 1 :Server local connection provider failed to listen on [ \\.\pipe\SQLLocal\XXXXX ]. Error: 0x5

2010-09-29T17:35:00.000+05:30

There might be many reasons and many solutions for this kind of error.But let me explain my situation :) ...For testing I installed a new default instance on one of the test servers.The setup was successful .However , later one of the other named instance did not come up after the restart.

The errors were :

2010-09-30 03:17:31.65 Server Error: 26049, Severity: 16, State: 1.
2010-09-30 03:17:31.65 Server Server local connection provider failed to listen on [ \\.\pipe\SQLLocal\XXXXX ]. Error: 0x5
2010-09-30 03:17:31.65 Server Error: 17182, Severity: 16, State: 1.
2010-09-30 03:17:31.65 Server TDSSNIClient initialization failed with error 0x5, status code 0x40.
2010-09-30 03:17:31.65 Server Error: 17182, Severity: 16, State: 1.
2010-09-30 03:17:31.65 Server TDSSNIClient initialization failed with error 0x5, status code 0x1.
2010-09-30 03:17:31.65 Server Error: 17826, Severity: 18, State: 3.
2010-09-30 03:17:31.65 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2010-09-30 03:17:31.65 Server Error: 17120, Severity: 16, State: 1.
2010-09-30 03:17:31.65 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

The issue is quite simple unlike it looks like .I tried everything like changing the named pipe , etc. etc.
Assuming, that 0x5 is the OS error which means Access Denied ,I gave the permission to the domain ID on the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer.
This resolved the issue .

status code 0x40 means that there is an issue with Shared memory listener
status code 0x50 means that there is an issue with Named pipe listener
status code 0x0A means that there is an issue with TCP/IP listener

Please go through this MSDN blog (which has one more link in it).
http://blogs.msdn.com/b/sql_protocols/archive/2006/03/09/546655.aspx?wa=wsignin1.0

Happy Learning !!!

A simple VB script to retain Errorlogs worth 90 days (or as you like)

2010-09-28T16:28:00.000+05:30

So far ,I heard retaining X number of errlogs which is widely used (So I am not writing that script here)...But one of our clients asked us to retain errlogs worth only 90 days .The client was not ready to recycle the errorlogs and wanted us to keep them to the default ...

Finally , we could come out with a simple VB script that can do it .The script code is mentioned below .

Code :

sFolder = "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG"
iMaxAge = 90
Set oFSO = CreateObject("Scripting.FileSystemObject")
If oFSO.FolderExists(sFolder) Then
for each oFile in oFSO.GetFolder(sFolder).Files
If DateDiff("d", oFile.DateLastModified, Now) > iMaxAge and (oFile.name= "ERRORLOG.1" or oFile.name= "ERRORLOG.2" or oFile.name= "ERRORLOG.3" or oFile.name= "ERRORLOG.4" or oFile.name= "ERRORLOG.5" or oFile.name= "ERRORLOG.6") Then
wscript.echo "Deleting" &oFile.Name
oFile.Delete
End If
next
End If

You will need to create a scheduled task/Or SQL Server job using xp_cmdshell, to run at a specific time .Once its kicked off , if any of the files mentioned in the code (Note : errorlog will not be tried upon) have a timestamp greater than 90 days from the day you are executing the file , It will delete those files ...for example if I have 7 files below :

File Timestamp
Errorlog 12/9/2009
Errorlog.1 12/8/2009
Errorlog.2 12/7/2009
Errorlog.3 12/6/2009
Errorlog.4 12/5/2009
Errorlog.5 12/4/2009
Errorlog.6 12/3/2009

The files deleted will be : Errorlog.3,Errorlog.4,Errorlog.5 and Errorlog.6

You need to change the Path of sFolder variable ..
Happy learning ...

Abhay

checkODBCConnectError: sqlstate = 28000; native error = 4818; message = [Microsoft][SQL Native Client][SQL Server]Login failed for user 'XXXXX\clusterservice

2010-09-28T16:23:00.000+05:30

a little Background :
As per our security guidelines Builtin\Administrator login should be removed from all the SQL Server instances.It was implemented on all the SQL Server instances including those which are on MCSC (Windows Cluster).
After that, the nodes were rebooted due to patching requirements .The nodes came up , but SQL Server did not :D ...

Error in cluster logs (you will not find it in SQL Server logs) :

ERR SQL Server : [sqsrvres] checkODBCConnectError: sqlstate = 28000; native error = 4818; message = [Microsoft][SQL Native Client][SQL Server]Login failed for user 'XXXXX\clusterservice'.
ERR SQL Server : [sqsrvres] ODBC sqldriverconnect failed

The error was clear .The cluster service was not able to login to SQL Server through user XXXXX\clusterservice but via a LOGIN ...That login is BUILTIN\Administrators.
But why it needs to login to SQL Server ?? Because it needs to run the isAlive check to make sure that the SQL Server is up and running .It also runs the looksalive (its a function)check but that does not need to query SQL Server .Is Alive check runs select @@servername and waits for the return message through ODBC client (in our case its SQL Server Native client).Thus the Isalive check was not able to create a trusted connection and we lost the access to Virtual server.

So, in a SQL Server 2005\2008 failover cluster installation, the cluster service account relies on membership in the BUILTIN\Administrators group to log on to SQL Server 2005\2008 to run the IsAlive check.If you remove the BUILTIN\Administrators group from a failover cluster, you must explicitly grant the MSCS service account permissions to log on to the SQL Server 2005 failover cluster.

The SQL Server 2005 resource starts an instance of the Sqlcmd.exe utility under the security context of the MSCS service account. Then, the SQL Server 2005 resource runs an SQL script over a dedicated administrator connection (DAC) that samples various dynamic management views (DMV). Because a DAC connection is used to collect some diagnostic data, the clustering service account must be provisioned in the SYSADMIN fixed server role. If later someone says that clustering service account cannot be provisioned in the SYSADMIN fixed server role, then we can create a login for cluster service account that is not given the SYSADMIN fixed server role .I have not tested it yet .So cannot confirm that this will work on not ...

Commands :
CREATE LOGIN [\] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
EXEC master.sp_addsrvrolemember @loginame = N'\ ', @rolename = N'sysadmin'

happly learning .....
Regards
Abhay

Msg 22004, Level 16, State 1, Line 0 :Failed to open loopback connection. Please see event log for more information.Failed to open loopback connection. Please see event log for more information.

2010-09-20T20:37:00.000+05:30

I am back with one more solution :).The problem was simple but the error made me thinking ..I was trying to do xp_readerrorlog on a small file .But my SPID hanged ..after some time I got this error :

SQL Server error in QA :
Msg 22004, Level 16, State 1, Line 0
Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line 0
error log location not found

I read somewhere that this error comes when SQL Server Agent failes to come up.Yes my agent was down.But I faied to understand what is the relation between running xp_readerrlog and SQL Agent not running .Still, I tried to run the agent and got the error ...So something is related to SQL Agent here and that something is that If SQL Agent is not running , I can run xp_readerrlog successfully (I will prove it wrong later).

I checked the application logs immediately and got these errors :

Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 09/20/2010
Time: 18:24:22
User: N/A
Computer: abchaudh
Description:
Severity: 16 Error:10061, OS: 10061 [Microsoft][SQL Native Client]TCP Provider: No connection could be made because the target machine actively refused it.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 4d 27 00 00 0a 00 00 00 M'......
0008: 12 00 00 00 61 00 62 00 ....a.b.
0010: 63 00 68 00 61 00 75 00 c.h.a.u.
0018: 64 00 68 00 00 00 0e 00 d.h.....
0020: 00 00 6d 00 61 00 73 00 ..m.a.s.
0028: 74 00 65 00 72 00 00 00 t.e.r...

Event Type: Error
Event Source: SQLAgent$CORRUPT
Event Category: Service Control
Event ID: 103
Date: 09/20/2010
Time: 18:24:45
User: N/A
Computer: abchaudh
Description:
SQLServerAgent could not be started (reason: Unable to connect to server 'abchaudh\CORRUPT'; SQLServerAgent cannot start).

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Event Type: Information
Event Source: SQLAgent$CORRUPT
Event Category: Service Control
Event ID: 102
Date: 09/20/2010
Time: 18:24:52
User: N/A
Computer: abchaudh
Description:
SQLServerAgent service successfully stopped.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

I also checked the SQL Agent logs :

SQL Agent logs :

2010-09-20 18:25:36 - ! [298] SQLServer Error: 10061, TCP Provider: No connection could be made because the target machine actively refused it. [SQLSTATE 08001]
2010-09-20 18:25:36 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
2010-09-20 18:25:36 - ! [298] SQLServer Error: 10061, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]
2010-09-20 18:25:36 - ! [000] Unable to connect to server 'abchaudh\CORRUPT'; SQLServerAgent cannot start
2010-09-20 18:25:42 - ! [298] SQLServer Error: 10061, TCP Provider: No connection could be made because the target machine actively refused it. [SQLSTATE 08001]
2010-09-20 18:25:42 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
2010-09-20 18:25:42 - ! [298] SQLServer Error: 10061, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]
2010-09-20 18:25:42 - ! [382] Logon to server 'abchaudh\CORRUPT' failed (DisableAgentXPs)
2010-09-20 18:25:43 - ? [098] SQLServerAgent terminated (normally)

So, now I have 2 issues : SQL Agent is not running and xp_readerrorlog is timing out.

.If you see one of my older posts on "target machine actively refuses it" , you will have some information .

So I opened CLICONFG and found 3 incorrect aliases which were not using the right port ..

I removed them and SQL Agent came on line ...xp_read errorlog also started working ...

I stopped SQL Agent and stil everything was working ...
So the issue was that xp_readerrlog tries to connect to SQL Server but stucks due to the Alias pointing to incorrect port.

But this does not affect SQL Server service .To check why its not affecting SQL Server service , I disabled the sahred memory protocol and BANG....SQL Server connection failed ...

Happy learning ....

Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.)

2010-08-25T14:48:00.000+05:30

Hi Team ,
This issue was faced by someone outside IBM but my main intention is to explain the benefit of another nice tool : Dependency Walker (http://www.dependencywalker.com/)

Issue :
SQL Server Agent failed to come up after the service account password was reset at AD level .

Error(s) :
In the event log you will see these errors in sequence :

Description:
Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).

Description:
Failed to retrieve SQLPath for syssubsystems population.

Description:
SQLServerAgent could not be started (reason: Failed to load any subsystems. Check errorlog for details.).

The first error is the main error and rest are the errors following the first error and we need not to think about them .

Troubleshooting and Resolution :
The error clearly says that either there is a problem with xpstar90.dll or the other dlls that this dll references .
This file is located in I first tried to re-register xpstar90.dll by using regsvr32 xrstar90.dll and got this message :

xpstar90.dll was loaded , But the dllRegisterServer entry point was not found.

I have heard that sometimes there is a different way of registering some DLLs , so by this error I did not come to the conclusion that this file is corrupt.
I was also thinking that there might be some other DDL that this DLL refers to , which got corrupted.

I decided to see the tree structure of xpstar90.dll in Dependency Walker . I opened the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\xpstar90.dll in it and go this output.

So, in this case XPSTAR90.DLL itself was corrupt .I found its version 2005.90.4035.0 and replaced it with another one that I had in another instance .
SQL Agent came online .
In case if it dos not , then we need to uninstall Native client from ADD Remove Programs and reinstall it .

Happy learning
Abhay

Error 1117 :The request could not be performed because of an I/O device error.

2010-08-02T16:40:00.001+05:30

Our backups were failing under these conditions :

Scenario 1: The System databases plus few user databases are on local disk & few user databases are on LUNs.

Scenario 2: The System & user databases are completely on LUNs

The backups were running for some good amount of time but then use to fail with Error 1117.I know that taking backups on network is not suported but I was breaking my head on this ERROR (1117)to know the reason behind this error .After going through a few tests on my machine using external HDDs ,my understanding of this error is :

-> Error 1117 is ERROR_IO_DEVICE .Thats fine .But I was curious about knowing the situations under which this error might occur and what is the exact meaning on this Error .Does Error_IO_Device means that the Hardware is corrupt ? Found that this error occurs under the below situations and then found the reasons behind those situations as well :

STATUS_FT_MISSING_MEMBER
ERROR_IO_DEVICE

An attempt was made to explicitly access the secondary copy of information via a device control to the fault tolerance driver and the secondary copy is not present in the system.

STATUS_FT_ORPHANING
ERROR_IO_DEVICE
{FT Orphaning} A disk that is part of a fault-tolerant volume can no longer be accessed.

STATUS_DATA_OVERRUN
ERROR_IO_DEVICE
{Data Overrun} A data overrun error occurred.

STATUS_DATA_LATE_ERROR
ERROR_IO_DEVICE
{Data Late} A data late error occurred.

STATUS_IO_DEVICE_ERROR
ERROR_IO_DEVICE
The I/O device reported an I/O error

STATUS_DEVICE_PROTOCOL_ERROR
ERROR_IO_DEVICE
A protocol error was detected between the driver and the device.

STATUS_DRIVER_INTERNAL_ERROR
ERROR_IO_DEVICE
An error was detected between two drivers or within an I/O driver.

So this error mapping says that this error will be thrown out if anyof these conditions are met .In my situation we were falling in into STATUS_DATA_LATE_ERROR since we were also getting thses entries in the SQL serve errorlogs : "x I/O requests are pending for more then 15 secs ............filename.mdf"

If you are running backup jobs you might also get this error -1073548784 .
This is a common error and may come when the query you are running remotely is incorrect , or the table you are trying to drop does not exist .Try to export a table that already exists in another DB and you will recreate this OLEDB error.So we need not to worry about finding the message identifier for this number .

Action plan :
-----------------
--try to take backup of another database located remotely and of near about same size . I mean around 20GB.

--Run Chkdsk on this drive or ask someone to do that and see if the consistency errors come up .

--Create a similar database on another external drive like this one and take the backup .

Conclusion :
---------------
I am very much certain that the issue is with the drive and(OR)Network.The 15 sec IO delay messages in Errorlogs also suggests the same .But as you can see this error also comes when dataa gets late in reaching the destination (STATUS_DATA_LATE_ERROR) I am suspecting that the network might also be a bit slow and contributing to the backup failure .

Now the ball is in your court how you explain this to the client :) .

Hapy Learning

Msg 8914, Level 16, State 1, Line 1 -> Incorrect PFS free space information for page

2010-07-27T15:56:00.000+05:30

Msg 8914, Level 16, State 1, Line 1
Incorrect PFS free space information for page (1:61991) in object ID 1993058136, index ID 1, partition ID 72057594955366400, alloc unit ID 71906736119218176 (type LOB data). Expected value 0_PCT_FULL, actual value 100_PCT_FULL.

This was the error we were getting in the Docs table of one of the Sharepoint database .The Compatibility level was 80 and Build was 1399 (2005 RTM).

I tried a lot of things on it like :
-> I Rebuilt the clustered index with and without LOB_COMPACTION option .
-> DBCC page shows is fill factor 100
-> Changed the fill factor to 100 explicitly
-> Ran dbcc updateusage
-> changed the compatibility level to 90
-> changed the fill factor to to 99 ,50 etc
Nothing helped .The profiler did not show much (my intention was to know what checkdb is doing internally).

Finally I took the backup of the database and restored it as a test database .It did not give any errors .This means that actually its not a corruption .

On the restored database Ran DBCC checkdb with repair_allow_data_loss .
It fixed the issue without harming the data .Finally Ran the same on the Sharepoint database and it resolved the issue .

Hope this gives you the confidence to run the repair_allow_data_loss for this issue .
But remember , almost every time if you run it with repair_allow_data_loss you will end up loosing the data .So be careful .

This situation was AN EXCEPTION and you can safely use this option of checkdb.

Root cause :
Microsoft says that
the engine (just like OS does which giving pages to processes ) pre-allocates a set of data pages (say X) to the SPID which needs it and marks them as 100% full in PFS assuming that those pages will eventually get filled very soon.It does this to avoid frequently updating PFS page and improving performance.But later when the SPID completes its work in less pages (say X-Y) , these remaining pages are released .However, the remaining pages should be marked again as empty (0_PCT_FULL) which it does not do and hence DBCC CheckDB reports those errors (SQL 2000 silently use to fix it ).Repair_allow_data_loss will fix it with no data loss actually.

Regards
Abhay

Finding the last date when the LOG/FULL/DIFF/FILEGROUP backup was taken for all the databases

2010-07-26T17:28:00.000+05:30

Hi Guys ,
While creating a few scripts , a requirement came where I had to find the last backups (all types) taken for all the databases (except tempdb) .
Please find the script below .Hope it helps you in your daily activities .If you want to automate it for all the instances in your environment , please let me know and I can send you some more files.

/*
Script : Last_bckp.sql
Author : Abhay Chaudhary,
Date : 26th JUL, 2010
Purpose : Collecting SQL Server 2000/2005/2008 last backup taken information.
Requirements : Do a CTRL+F and change the to the DB where you want to
create the object.
Suggestions : hi_abhay78@yahoo.co.in
Version : 1.0
*/

USE
set nocount on
if not exists (select * from ..sysobjects where name ='bckp_types' and type ='S')
begin
create table ..bckp_types (num int identity(1,1),type varchar(1),bkp_name varchar(20))
insert into ..bckp_types (type,bkp_name) values ('D','Full backup')
insert into ..bckp_types (type,bkp_name) values ('L','Log Backup')
insert into ..bckp_types (type,bkp_name) values ('F','Filegroup backup')
insert into ..bckp_types (type,bkp_name) values ('I','Differential backup')
end
go

Declare @loop int
select @loop= max(num) from bckp_types
While (@loop !=0)
begin
Select 'last ' + bkp_name +' taken details.' from bckp_types where num=@loop
declare @bk_type varchar(1)
select @bk_type = type from bckp_types where num=@loop

SELECT s.name 'database Name',
b.backup_finish_date 'last backup date',
bmf.physical_device_name 'location of backup'
FROM master..sysdatabases s LEFT OUTER JOIN msdb..backupset b ON s.name = b.database_name
INNER JOIN msdb..backupmediafamily bmf ON b.media_set_id = bmf.media_set_id
WHERE s.name <> 'tempdb'
AND b.backup_finish_date = (SELECT MAX(backup_finish_date)
FROM msdb..backupset
WHERE database_name = b.database_name
AND type = @bk_type)
ORDER BY s.name

set @loop=@loop-1
end
go
Drop table ..bckp_types

Happy Learning ...
Abhay

SQLServer Error: 848, SQL Network Interfaces: The system detected a possible attempt to compromise security.

2010-07-16T16:39:00.000+05:30

We faced a strange but simple issue yesterday and as usual I would like to share it with you .

Situation :
-------------
SQL server 2005 SP2
Windows Server 2003 SP2
Cluster : Yes 2 node A-P cluster

Service account of SQL Server Agent service and SQL Server service were same .SQL Server is Clustered .

While SQL Sevrer as well as agent were running fine the account under both the services are running ,got locked(we came to know this later as a rootcause of this issue).Still ,everything was fine and there was no issue since the account got locked after SQL Server and agent were started.

Then we found that all the jobs that were scheduled stopped working .In the job history we found that there is no JOB HISTORY created since the jobs stopped working .But there was not a single failure of the jobs .

Which means that the jobs were not scheduled by the Job schedular >> to the Threads >> to the SPIDs .So , we manually executed the jobs and all of them completed successfully .But again , there was no history being created and those jobs were not doing anything .For example , the backup job was running successfully when we ran it explicitly but no backups were taken .

To drill down further , we ran the commands under the jobs in QA and those were running fine .We created new jobs and there was no change at all in the situation .

Then we checked the SQL Agent logs and found this :

[298] SQLServer Error: 848, SQL Network Interfaces: The system detected a possible attempt to compromise security. Please ensure that you can contact the server that authenticated you. [SQLSTATE HY000]
[298] SQLServer Error: 848, Cannot generate SSPI context [SQLSTATE HY000]
[382] Logon to server '(local)' failed (ConnAttemptCachableOp)

This was strange to us as why the connectivity error is not being displayed when we were explicitely executing the job, which completes successfully and doing nothing.
But since it was the connectivity error by agent , we decided to run the jobs by logging on to the server using the account under which SQL Server and agent are running.

We then found out the the account was locked under which SQL Server and agent were still running.

Once the account got unlocked at the AD ,the jobs ACTUALLY started working .

To me it looks like a bug in design and i have logged it on the CONNECT :
https://connect.microsoft.com/SQLServer/feedback/details/575388/strange-behaviour-in-sql-agent-job-on-cluster-where-the-job-runs-but-does-not-do-anything

hope it helos you to resolve your issue .

Data auditing in SQL Server

2010-06-19T20:32:00.000+05:30

There are 2 ways we can audit the SQL Server events to the tracefile (people call it audit log file).

- setting sp_configure parameter 'c2 audit mode' to 1.This will automatically capture all the audit events for all the databases and all the columns . You cannot modify it .Even if you try to , it will
not take the changes made manually .

- Creating our own trace for selected events and columns .Please check BOL for it .

In case you want to go through the second option and that is to create our own trace please see the demo below:

Step 1
In this step we are creating test_yasir trace in C: drive.Then we are setting the Events and columns adn settin gthem to ON .I have choosen a few events and columns .

declare @TraceIdOut int
exec sp_trace_create @TraceIdOut OUTPUT,6, N'c:\test_Yasir'
PRINT @TraceIdOut

declare @On bit
SET @On = 1
exec sp_trace_setevent @TraceIdOut, 14, 6, @On
exec sp_trace_setevent @TraceIdOut, 14, 7, @On
exec sp_trace_setevent @TraceIdOut, 14, 8, @On
exec sp_trace_setevent @TraceIdOut, 14, 9, @On
exec sp_trace_setevent @TraceIdOut, 14, 10, @On
exec sp_trace_setevent @TraceIdOut, 15, 6, @On
exec sp_trace_setevent @TraceIdOut, 15, 7, @On
exec sp_trace_setevent @TraceIdOut, 15, 8, @On
exec sp_trace_setevent @TraceIdOut, 15, 9, @On
exec sp_trace_setevent @TraceIdOut, 15, 10, @On
exec sp_trace_setevent @TraceIdOut, 20, 6, @On
exec sp_trace_setevent @TraceIdOut, 20, 7, @On
exec sp_trace_setevent @TraceIdOut, 20, 8, @On
exec sp_trace_setevent @TraceIdOut, 20, 9, @On
exec sp_trace_setevent @TraceIdOut, 20, 10, @On

exec sp_trace_setevent @TraceIdOut, 104, 1, @On
exec sp_trace_setevent @TraceIdOut, 104, 3, @On
exec sp_trace_setevent @TraceIdOut, 104, 6, @On
exec sp_trace_setevent @TraceIdOut, 104, 7, @On
exec sp_trace_setevent @TraceIdOut, 104, 8, @On
exec sp_trace_setevent @TraceIdOut, 104, 7, @On
exec sp_trace_setevent @TraceIdOut, 104, 8, @On
exec sp_trace_setevent @TraceIdOut, 104, 10, @On
exec sp_trace_setevent @TraceIdOut, 104, 11, @On
exec sp_trace_setevent @TraceIdOut, 104, 14, @On
exec sp_trace_setevent @TraceIdOut, 104, 22, @On
exec sp_trace_setevent @TraceIdOut, 104, 26, @On
exec sp_trace_setevent @TraceIdOut, 104, 35, @On
exec sp_trace_setevent @TraceIdOut, 104, 7, @On
exec sp_trace_setevent @TraceIdOut, 104, 8, @On
exec sp_trace_setevent @TraceIdOut, 104, 7, @On
exec sp_trace_setevent @TraceIdOut, 104, 8, @On

exec sp_trace_setevent @TraceIdOut, 107, 1, @On
exec sp_trace_setevent @TraceIdOut, 107, 3, @On
exec sp_trace_setevent @TraceIdOut, 107, 6, @On
exec sp_trace_setevent @TraceIdOut, 107, 7, @On
exec sp_trace_setevent @TraceIdOut, 107, 8, @On
exec sp_trace_setevent @TraceIdOut, 107, 7, @On
exec sp_trace_setevent @TraceIdOut, 107, 8, @On
exec sp_trace_setevent @TraceIdOut, 107, 10, @On
exec sp_trace_setevent @TraceIdOut, 107, 11, @On
exec sp_trace_setevent @TraceIdOut, 107, 14, @On
exec sp_trace_setevent @TraceIdOut, 107, 22, @On
exec sp_trace_setevent @TraceIdOut, 107, 26, @On
exec sp_trace_setevent @TraceIdOut, 107, 35, @On
exec sp_trace_setevent @TraceIdOut, 107, 7, @On
exec sp_trace_setevent @TraceIdOut, 107, 8, @On
exec sp_trace_setevent @TraceIdOut, 107, 7, @On
exec sp_trace_setevent @TraceIdOut, 107, 8, @On

exec sp_trace_setevent @TraceIdOut, 106, 1, @On
exec sp_trace_setevent @TraceIdOut, 106, 3, @On
exec sp_trace_setevent @TraceIdOut, 106, 6, @On
exec sp_trace_setevent @TraceIdOut, 106, 7, @On
exec sp_trace_setevent @TraceIdOut, 106, 8, @On
exec sp_trace_setevent @TraceIdOut, 106, 7, @On
exec sp_trace_setevent @TraceIdOut, 106, 8, @On
exec sp_trace_setevent @TraceIdOut, 106, 10, @On
exec sp_trace_setevent @TraceIdOut, 106, 11, @On
exec sp_trace_setevent @TraceIdOut, 106, 14, @On
exec sp_trace_setevent @TraceIdOut, 106, 22, @On
exec sp_trace_setevent @TraceIdOut, 106, 26, @On
exec sp_trace_setevent @TraceIdOut, 106, 35, @On
exec sp_trace_setevent @TraceIdOut, 106, 7, @On
exec sp_trace_setevent @TraceIdOut, 106, 8, @On
exec sp_trace_setevent @TraceIdOut, 106, 7, @On
exec sp_trace_setevent @TraceIdOut, 106, 8, @On

exec sp_trace_setevent @TraceIdOut, 105, 1, @On
exec sp_trace_setevent @TraceIdOut, 105, 3, @On
exec sp_trace_setevent @TraceIdOut, 105, 6, @On
exec sp_trace_setevent @TraceIdOut, 105, 7, @On
exec sp_trace_setevent @TraceIdOut, 105, 8, @On
exec sp_trace_setevent @TraceIdOut, 105, 7, @On
exec sp_trace_setevent @TraceIdOut, 105, 8, @On
exec sp_trace_setevent @TraceIdOut, 105, 10, @On
exec sp_trace_setevent @TraceIdOut, 105, 11, @On
exec sp_trace_setevent @TraceIdOut, 105, 14, @On
exec sp_trace_setevent @TraceIdOut, 105, 22, @On
exec sp_trace_setevent @TraceIdOut, 105, 26, @On
exec sp_trace_setevent @TraceIdOut, 105, 35, @On
exec sp_trace_setevent @TraceIdOut, 105, 7, @On
exec sp_trace_setevent @TraceIdOut, 105, 8, @On
exec sp_trace_setevent @TraceIdOut, 105, 7, @On
exec sp_trace_setevent @TraceIdOut, 105, 8, @On

Step 2 :

In this step we wil apply the filter since you said you need to audit only a user database.We will achieve it using sp_trace_setfilter

sp_trace_setfilter 3,3,0,0,1

In this example I have set the filter on databaseid (to 1 which is master) in traceid 3

Step 3:
In this step we will first confirm if our trace is showing up in the metadata.Do a select * from sys.traces and check the trace you created as well its trace id .
Then start the trace (which is 1) using sp_trace_setstatus

example :
sp_trace_setstatus 3,1

Here traceid is 3 and staus is 1
Further , if you want to add or remove any event use sp_trace_setevent after stopping the trace using sp_trace_setstatus

But in this method there is a problem .The problem is that , if you restart the instance the trace metadata will be washed from the sys.traces DMV.
So you will have to manually run it again .Further the physical trace file (log file) still exist.So you will get the error while creaing the trace .To over come this :

1) I have added the datetime in the file name .So it will create a unique file each minute.
2) I have encapsulated the query into an SP and pinned it to SQL Server startup.

So now

step 1 would be

create proc audit_trace as
declare @TraceIdOut int
Declare @D1 nvarchar(30)
Declare @D2 nvarchar(30)
Declare @D3 nvarchar(30)
Declare @D4 nvarchar(30)
Declare @D5 nvarchar(30)
Declare @trace_name nvarchar(256)

SELECT @D1=DATENAME(Day, GETDATE())
SELECT @D2=DATENAME(month, GETDATE())
SELECT @D3=DATENAME(year, GETDATE())
SELECT @D4=DATENAME(hour, GETDATE())
SELECT @D5=DATENAME(minute, GETDATE())

set @trace_name='c:\trace_'+@d1+'_'+@d2+'_'+@d3+'_'+@d4+'_'+@d5+'_'
print @trace_name

--set @trace_name = 'c:\trace_'+@trace_date+'.trc'
--print @trace_name
exec sp_trace_create @TraceIdOut OUTPUT,6, @trace_name
PRINT @TraceIdOut

declare @On bit
SET @On = 1
exec sp_trace_setevent @TraceIdOut, 14, 6, @On
exec sp_trace_setevent @TraceIdOut, 14, 7, @On
exec sp_trace_setevent @TraceIdOut, 14, 8, @On
exec sp_trace_setevent @TraceIdOut, 14, 9, @On
exec sp_trace_setevent @TraceIdOut, 14, 10, @On
exec sp_trace_setevent @TraceIdOut, 15, 6, @On
exec sp_trace_setevent @TraceIdOut, 15, 7, @On
exec sp_trace_setevent @TraceIdOut, 15, 8, @On
exec sp_trace_setevent @TraceIdOut, 15, 9, @On
exec sp_trace_setevent @TraceIdOut, 15, 10, @On
exec sp_trace_setevent @TraceIdOut, 20, 6, @On
exec sp_trace_setevent @TraceIdOut, 20, 7, @On
exec sp_trace_setevent @TraceIdOut, 20, 8, @On
exec sp_trace_setevent @TraceIdOut, 20, 9, @On
exec sp_trace_setevent @TraceIdOut, 20, 10, @On

exec sp_trace_setevent @TraceIdOut, 104, 1, @On
exec sp_trace_setevent @TraceIdOut, 104, 3, @On
exec sp_trace_setevent @TraceIdOut, 104, 6, @On
exec sp_trace_setevent @TraceIdOut, 104, 7, @On
exec sp_trace_setevent @TraceIdOut, 104, 8, @On
exec sp_trace_setevent @TraceIdOut, 104, 7, @On
exec sp_trace_setevent @TraceIdOut, 104, 8, @On
exec sp_trace_setevent @TraceIdOut, 104, 10, @On
exec sp_trace_setevent @TraceIdOut, 104, 11, @On
exec sp_trace_setevent @TraceIdOut, 104, 14, @On
exec sp_trace_setevent @TraceIdOut, 104, 22, @On
exec sp_trace_setevent @TraceIdOut, 104, 26, @On
exec sp_trace_setevent @TraceIdOut, 104, 35, @On
exec sp_trace_setevent @TraceIdOut, 104, 7, @On
exec sp_trace_setevent @TraceIdOut, 104, 8, @On
exec sp_trace_setevent @TraceIdOut, 104, 7, @On
exec sp_trace_setevent @TraceIdOut, 104, 8, @On

exec sp_trace_setevent @TraceIdOut, 107, 1, @On
exec sp_trace_setevent @TraceIdOut, 107, 3, @On
exec sp_trace_setevent @TraceIdOut, 107, 6, @On
exec sp_trace_setevent @TraceIdOut, 107, 7, @On
exec sp_trace_setevent @TraceIdOut, 107, 8, @On
exec sp_trace_setevent @TraceIdOut, 107, 7, @On
exec sp_trace_setevent @TraceIdOut, 107, 8, @On
exec sp_trace_setevent @TraceIdOut, 107, 10, @On
exec sp_trace_setevent @TraceIdOut, 107, 11, @On
exec sp_trace_setevent @TraceIdOut, 107, 14, @On
exec sp_trace_setevent @TraceIdOut, 107, 22, @On
exec sp_trace_setevent @TraceIdOut, 107, 26, @On
exec sp_trace_setevent @TraceIdOut, 107, 35, @On
exec sp_trace_setevent @TraceIdOut, 107, 7, @On
exec sp_trace_setevent @TraceIdOut, 107, 8, @On
exec sp_trace_setevent @TraceIdOut, 107, 7, @On
exec sp_trace_setevent @TraceIdOut, 107, 8, @On

exec sp_trace_setevent @TraceIdOut, 106, 1, @On
exec sp_trace_setevent @TraceIdOut, 106, 3, @On
exec sp_trace_setevent @TraceIdOut, 106, 6, @On
exec sp_trace_setevent @TraceIdOut, 106, 7, @On
exec sp_trace_setevent @TraceIdOut, 106, 8, @On
exec sp_trace_setevent @TraceIdOut, 106, 7, @On
exec sp_trace_setevent @TraceIdOut, 106, 8, @On
exec sp_trace_setevent @TraceIdOut, 106, 10, @On
exec sp_trace_setevent @TraceIdOut, 106, 11, @On
exec sp_trace_setevent @TraceIdOut, 106, 14, @On
exec sp_trace_setevent @TraceIdOut, 106, 22, @On
exec sp_trace_setevent @TraceIdOut, 106, 26, @On
exec sp_trace_setevent @TraceIdOut, 106, 35, @On
exec sp_trace_setevent @TraceIdOut, 106, 7, @On
exec sp_trace_setevent @TraceIdOut, 106, 8, @On
exec sp_trace_setevent @TraceIdOut, 106, 7, @On
exec sp_trace_setevent @TraceIdOut, 106, 8, @On

exec sp_trace_setevent @TraceIdOut, 105, 1, @On
exec sp_trace_setevent @TraceIdOut, 105, 3, @On
exec sp_trace_setevent @TraceIdOut, 105, 6, @On
exec sp_trace_setevent @TraceIdOut, 105, 7, @On
exec sp_trace_setevent @TraceIdOut, 105, 8, @On
exec sp_trace_setevent @TraceIdOut, 105, 7, @On
exec sp_trace_setevent @TraceIdOut, 105, 8, @On
exec sp_trace_setevent @TraceIdOut, 105, 10, @On
exec sp_trace_setevent @TraceIdOut, 105, 11, @On
exec sp_trace_setevent @TraceIdOut, 105, 14, @On
exec sp_trace_setevent @TraceIdOut, 105, 22, @On
exec sp_trace_setevent @TraceIdOut, 105, 26, @On
exec sp_trace_setevent @TraceIdOut, 105, 35, @On
exec sp_trace_setevent @TraceIdOut, 105, 7, @On
exec sp_trace_setevent @TraceIdOut, 105, 8, @On
exec sp_trace_setevent @TraceIdOut, 105, 7, @On
exec sp_trace_setevent @TraceIdOut, 105, 8, @On

/*adding the SP to execute at SQl Server startup */
exec sp_procoption N'audit_trace', 'startup', 'on'

Step 2 and 3 will be same as mentioned above in the begening .

Disadvantage
-------------
Simple ..Its resource consuming .Do not add a lot of columns in the trace .Do specifically what you want to audit.
It entirely depends what all columns you are auditing.
You need to keep the instance under testing phase and monitor the resource consumption due to tracing .
Clear the client that we need to make sure that we have fast disks ,more/faster CPUs, IO processing capabbilities and enough RAM in case they need to do extensive auditing (if there are performance issues).

Using WMI and SQL Agent to fire low memory threshold alert ...

2010-06-19T20:21:00.000+05:30

This will work perfectly ....The only thing I wanted to add to the table was when it alerts you it should also fill the column with available MBytes so that you know how much memory was available .......But after trying it for 2 days , I realized that the class through which I am checking another class (Perfmon >> memory >> Available Mbytes) does not have a column for this.I am using "_instance modification" class .May be its due to this that the alert is fired but the job fails when it inserts the availableMbytes ...because this column is not in _instancemodificationevent class...the error number also suggests that .

By the way this one will only alert if your RAM is > 256 every 10 seconds ....this is because I wanted to test it ....you need to modify it to < 256 and every 300 seconds ...so that you get alert every 5 mins or whatever you decide ....

/*******************************************************************************************
* This script will create an Alert to Monitor Physical RAM reaching a low threshold.
* 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].[memory]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[memory]
GO

CREATE TABLE [dbo].[memory] (
[PostTime] [datetime] NOT NULL default (getdate()) ,
[computerName] sql_variant Not Null ,
[RecordID] [int] IDENTITY (1,1) NOT FOR REPLICATION NOT NULL,
[Flag] [int] NOT NULL CONSTRAINT [DF_MEMORY_Flag] DEFAULT ((0)),
) ON [PRIMARY]
GO

CREATE INDEX [Memory_IDX01] ON [dbo].[memory]([recordid]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO

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

USE [msdb]
GO

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Capture memory Event')
EXEC msdb.dbo.sp_delete_job @job_name = N'Capture Memory 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 Memory 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 memory 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'
declare @@server sql_variant
select @@server =serverproperty (''machinename'')

INSERT INTO memory (
PostTime,
Computername
)

VALUES (
GETDATE(),
@@server)
',
@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 Memory_event')
EXEC msdb.dbo.sp_delete_alert @name=N'Respond to memory_event'

GO

DECLARE @server_namespace varchar(255)
SET @server_namespace = N'\\.\root\Cimv2\'

EXEC msdb.dbo.sp_add_alert @name=N'Respond to memory_event',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@category_name=N'[Uncategorized]',
@wmi_namespace=N'\\.\root\Cimv2',
@wmi_query=N'SELECT * FROM __InstanceModificationEvent WITHIN 10 WHERE TargetInstance ISA ''Win32_PerfFormattedData_PerfOS_Memory'' AND TargetInstance.AvailableBytes > 256',
@job_name='Capture memory Event' ;

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

--/* Step 5: Create a stored proc for sending the [Create_user] information as .CSV file */

--Create proc [dbo].[Deadlock_rpt]
--as
--DECLARE @SQL varchar(Respond to memory_event2000)
--DECLARE @date varchar (2000)
--DECLARE @File varchar(1000)
--select @date= convert(date,GETDATE())
--SET @SQL = 'select * from [Create_user] where flag = 0'
--SET @File = '[Create_user] report'+@date+'.csv'

--EXECUTE msdb.dbo.sp_send_dbmail
--@profile_name = 'test',
--@recipients = 'your email.com',
--@subject = 'low memory threshold reached...',
--@body = '***URGENT***Attached please find the low memory threshold 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_user] set flag = 1 where flag = 0
--go

Printing DBCC inputbuffer for any SPID > 50

2010-06-19T20:18:00.001+05:30

It helps in many cases .For example if SPIDS are involved in blocking .and we are doing inputbuffer one by one.
For blocking you can just add : and blocked !=0 in the sysprocesses query
Similarly you can modify it for waittype,waittime, CPU etc (as per your need)

--Input buffers
PRINT 'Input buffer SPIDs'

declare @spid smallint
declare @i_buff_string char(30)
set nocount on
declare bufCursor CURSOR FOR SELECT spid from master.dbo.sysprocesses where spid > 50
FOR READ ONLY
open bufCursor
fetch next from bufCursor into @spid
while (@@fetch_status <> -1)
begin
SET @i_buff_string = ('DBCC INPUTBUFFER (' + convert(char(6),@spid) +')')
PRINT '-> '+@i_buff_string
exec (@i_buff_string)
PRINT ''
fetch next from bufCursor into @spid
end
close bufCursor
deallocate bufCursor

Why do we need the Non-clustered Index

2010-06-19T19:56:00.000+05:30

I was asked this question by an SME , So I thought let me pass on if it makes sense .

Lets create a table test which has a few columns from Person.address table (Adventureworks database)
select addressid,addressline1,addressline2,postalcode,stateprovinceid into test from Person.Address

On this table I created clustered index on AddressID and stateprovinceID
CREATE UNIQUE CLUSTERED INDEX [combined index] ON [dbo].[testo]
(
[addressid] ASC,
[stateprovinceid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Another non-clustered index was created with include option
CREATE NONCLUSTERED INDEX [provinceID] ON [dbo].[testo]
(
[postalcode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

*** BUT I WILL DISABLE THE INDEX ********

Now I ran a few queries:
-----------------------------------
select * from test where postalcode >'98501'

select * from test where postalcode >'98501' order by stateprovinceid

select postalcode from testo where postalcode >'98501' order by stateprovinceid

select addressline1,addressline2 from testo where postalcode >'98501' order by stateprovinceid

Notice that In all the cases above we have the clustered index Scan and not a simple SCAN .
So the clustered index is being used .And since Its visible that a clustered index is being used there is no need of any other index .Why ............because the data itself lies in the clustered index and we can not improve the plan anymore.

Lets see why a scan would be used
Your Data is sorted on the bases of 2 columns in the clustered index .So any query will use that Index because the data lies there .So in worst to worst case you will have a Clustered Index Scan (Better than a Index scan [heap scan]).

But why Scan and not Seek
Because the columns you are using in the where clause or the columns you are looking for as an output have no index or you can say they are not indexed (indirectly , working like a
heap).So , You will do a scan for those Rows .But since its ordered already , So it will be a clustered Scan.

Now you enable the non-clustered index and run few queries

select postalcode from testo where postalcode >'98501'

It did an index seek .remember it is not a Clustered index seek like below

select addressid from testo where addressid < 100

The reason is that In the earlier case a non-cluseterd index was used to find out the postalcode very quickly but since the data was in the clustered index it needed to point to that Leaf and hence some more efforts .But these efforts were far less than Clustered index scan .

That is the reason we need to Non-clustered index .TO reduce the ROW Lookups or Bookmark lookups.which are the pointers to the clustered index.

Happy learning!!

xp_sendmail: failed with mail error 0x80040111

2010-06-19T19:34:00.000+05:30

Hi guys,
Its been long since i posted any new stuff ...so far I've posted nothing related SQL 2000 but i thought this one is needed .

We recently faced a SQL Mail issue on WIN 2k Server .Anyone who has worked on SQL Mail issue in the past would be knowing how irritating this issue can be sometimes.But its actually our dis-interest that makes this issue difficult.Somehow we don't like such issues :) ...Since we resolved this issue and went through different permutations and combinations I would like to discuss this with you all so that in future we can save some time and resources on the same issue.This includes

-> Some very basic understanding on SQL mail .
-> What you should do/do not do when SQL Mail issue comes up.

Brief understanding on SQL Mail
--------------------------------
There are two ways you can send mails in SQL Server 2000 (Not talking about 2005 or 2008).

-> SQL Agent Mail
-> SQL Mail .

Yes , you heard it right .SQL Agent uses its own Extended M(ail)API session where as SQL Mail uses its own .However,Both SQL Mail and SQL Agent Mail connect with Microsoft Exchange Server, Microsoft Windows NT® Mail, or a Post Office Protocol 3 (POP3) server using Mail Profile. It is possible to use different profiles for SQL Mail and SQL Agent Mail at the same time.
Extended MAPI is nothing but a better version over Simple MAPI .Anyway, we don't have to go in this direction .

The SPs that we use in SQL Mail are :

xp_startmail <-- used to start the mail session
xp_sendmail <-- To send mail .This invokes sp_processmail.
xp_findnextmsg <--sp_processmail uses this SP
xp_readmail <-- used by sp_processmail
xp_deletemail<--deletes the message.
sp_processmail <-- processses the mail
xp_stopmail<-- Stops the mail session

We will concentrate on First two and the last one in the list above.You can read about these SPs under : http://msdn.microsoft.com/en-us/library/ms186841.aspx?PHPSESSID=lm71lj7i6gj5fjtebtb2srl4n3
This link is for sp_findnextmsg but has link to all other SPs and XPs.

The Issue that we faced.
-------------------------
One of the job was not sending the mail after completion .SQL Mail was connecting to Exchange server successfully before .
Outlook 2000 client was installed on the Win 2K server (and was working fine under SQL Server service account).
Although we concentrated mostly troubleshooting SQLMail it was SQL Agent mail issue actually .
But Since troubleshooting steps are same for both , we resolved the issue .

TroubleShooting done

-> Logged in to the Server > opened the QA and ran sp_stopmail .Success
-> Sp_startmail ....Took 10 mins to start and with error.
-> The error was :
Server: Msg 18025, Level 16, State 1, Line 0
xp_startmail: failed with mail error 0x80040111
-> We tried to send the test mail using xp_sendmail
xp_sendmail 'abhay.chaudhary@in.ibm.com','test'
Server: Msg 18025, Level 16, State 1, Line 0
xp_sendmail: failed with mail error 0x80040111
-> Assuming that the Profile might be corrupt we created nwe profile through control panel >> Mail option .
-> To our surprise we could not see that profile in the drop down list of both SQL Mail and Agent properties.
-> So we failed there as well .We have the new profile , but we cant use it .Another Big question ? WHY ?
-> Agent logs were showing this :[260] Unable to start mail session (reason: Unable to logon (reason: Unable to get the default MAPI Message Store due to MAPI
error 273: The information store could not be opened); check the mail profile and/or the SQLSERVERAGENT service startup account)
-> After wasting time on 18025 we concentrated on Error 0x80040111
-> Found KB 238119 (List of Extended MAPI numeric result codes)
-> found that the error means : MAPI_E_LOGON_FAILED
-> Now we were clear that this is an authentication issue.
-> SQL Agent mail was not able to talk to Exchange Server.
-> The exchange Engineer cleared that since both the servers can ping each other and we can use outlook web using the same account under Exchange is running , there is no isues with
Exchange Mail box .
-> He was pretty convincing.
-> Then we found that (Actually we already knew that )in the SQL Mail configuration it was mentioned that we need to make sure that in we are logged in to the server using the SQL Server
service account.
-> That is what we have not done and were not able to do actually (Since begening) because of some permission issues on that account while logging on to the Win 2k Server.
-> Since rest all trials were failing , we decided to force ourselves for one last time in resolving this issue after loggin gon tho the server using SQL Server domain account .
-> The Intel team helped us in doing that .
-> We logged on to the server >> created a new profile >> Changed the SQL Agent setting to point to that profile (which was now visible..wow).
-> Started the mail session using xp_startmail.
SQL Mail session started.
-> Sent a test mail : xp_sendmail 'abhay.chaudhary@in.ibm.com','test'
Mail sent.
-> That resolved the issue.

Do's to solve this issue
--------------------------
-> Ping the exchange server or the POP3 Server and make sure its a success.
-> Make sure that the Domain account for SQL Server(including agent) , Exchange and OS are same .
-> Stop the mail session first as it might be Stuck.
-> Start the mail session .If it works , fine .
-> If not then read the MAPI error and match it with the KB 238199 to understand the root cause .
-> In one of the situations when I was testing it on Lotus Notes to POP 3 account on Gmail I got 8004011d which means MAPI_E_FAILONEPROVIDER (due to slow network)
-> Check the Agent logs and make sure MAPI32.dll exists (Using MAPI32.DLL from C:\WINDOWS\SYSTEM32 (version 1.0.2536.0))
-> Make sure the mapistub.dll exists in C:\WINDOWS\SYSTEM32 folder
-> You can also test you MAPI profile : xp_test_mapi_profile 'hello'
-> Create the new profile and see if that helps .If it does it means that the previous profile was corrupt(We did not test this in our case and created a new profile straightaway).

Don'ts
---------
Never try to resolve this issue without logging on to the server with SQL Server service account .

Hope this helps .
let me know your comments ..

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

2010-01-05T17:18:00.000+05:30

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

2010-01-05T17:07:00.000+05:30

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

2009-12-23T15:38:00.001+05:30

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

2009-12-19T15:20:00.000+05:30

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

2009-12-18T19:08:00.001+05:30

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

2009-12-17T19:10:00.000+05:30

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 :)

2009-12-16T13:37:00.000+05:30

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...

2009-11-27T14:38:00.000+05:30

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

2009-11-20T13:42:00.000+05:30

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.
*
* For any suggestion contact :gur.sethi@in.ibm.com and abhay.chaudhary@in.ibm.com
*******************************************************************************************/

/* 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

2009-11-20T13:19:00.000+05:30

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

2009-10-07T16:53:00.000+05:30

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)

2009-10-07T16:31:00.000+05:30

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

2009-10-06T16:50:00.000+05:30

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......

2009-10-06T15:53:00.000+05:30

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

SQL 2008 setup failes : This Process does not possess the SeSecurity privilege' privilege which is required for this operation.

2009-10-03T19:07:00.000+05:30

Just got off this case on an idle Saturday :) ....let me share it .

SQL Server 2008 on windows server 2008 .........I just hate this combination ...sorry but i really do ...

Actually , i hate windows server 2008 because of its too many security restrictions ..

anyway lets come straigh to the topic .

The error is :This Process does not possess the SeSecurity privilege' privilege which is required for this operation

This is Manage Auditing and Security Log right which can be seen in the local security settings (start >> run >> secpol.msc >> local policies >> User rights assignments). for more information see : http://msdn.microsoft.com/en-us/library/ms813959.aspx and http://msdn.microsoft.com/en-us/library/bb530716(VS.85).aspx.

Access to the security event log is governed by SeSecurityPrivilege.Holders of the privilege have Read, Clear, and Backup permission. Holders of SeAuditPrivilege can write to the log via internal LSA APIs only.LocalSystem can write to the security event log via the ReportEvent API due to permission granted via the log ACL.

By default, these are the privilege assignments:

SeSecurityPrivilege Administrators, LocalSystem
SeAuditPrivilege LocalSystem

So , you need to add your domain account or the domain group to which you belong in to this policy ......and yes you need to reboot the server.

After reboot you will see SWEET Success...

Hope this helps ..

Regards
Abhay

No connection could be made because the target machine actively refused it.

2009-09-25T17:15:00.000+05:30

I faced this situation yesterday .
We were using Tivoli to connect to SQL Server 2005 (Clustered instance) to take backups.

But it was failing with the error (not printing the complee error here):
No connection could be made because the target machine actively refused it.

I asked myself why the server is refusing the client request ??
Since the connection was failing , this issue had to do with connectivity .

I started checking few things :

1) Port on which SQL is listening
2) named or default instance
3) Protocols enabled @ server level and @ client level .

It was a default instance on some port other than 1433 (as hackers can catch it easily).We found that it was SQL Server that was using this port ...how ??
By connecting to SQL Server through management studio using

tcp:servername\instancename , portname

and it connected .We also did netstat -aon and or -aonb(this takes a lot of time as it also finds the exe name) and confirmed that only SQL Server is using this port.

Then we checked SQL Server Client network utility (cliconfg) and found that no protocols were enabled .We enabled named pipes and TCP/IP (not sure how it got disabled).

Tried to connect tivoli which failed again .Finally we forcibly made tivoli to connect to SQL Server usin gthat port by creating a TCP/IP alias ...
This worked and our issue got resolved ..

In past I found that the port SQL Server is using is either blocked or being used by other application .I that case stop that application and create the alias .

Hope this helps !!!

Regards
Abhay

A significant part of sql server process memory has been paged out.

2009-09-14T13:58:00.000+05:30

First of all :
There is no solution to this issue in Windows 2003 (You might have lost the confidence after reading this :) ...read it fully).

This is the sample error that we get :
2007-01-23 16:30:10.14 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 1086400, committed (KB): 2160928, memory utilization: 50%.

Lets try to understand this error first :

Duration : the time or interval or gap after which this error has been reported. Here 0 means that this is the first time on that day , that this error occured.You will see a fixed pattern of duration .

Working set :The memory pages kept in the RAM for the use of process/threads.Remember that this is set approximately by the OS and its the OS that can trim it or add to it if it see that too many pages ar ein RAM for a process and it needs to give minimum bare pages to some other process which it cannot .In our case its roughly 1 GB . Check wikipedia for a better definition (http://en.wikipedia.org/wiki/Working_set)

Committed : The actual memory committed by SQL Server .NO one should be able to take this memory from SQL Server or deallocate it .Not even the OS .But it can be paged :) by the OS and thats what it does.

Memory Utilization : the actual memory utilization by SQL Server after its working set is paged out .In this case its 50 which means the remaining 50 has been paged out .Had it been 2% then the remaining 98% is paged out .This error will start apperaing once the working set of SQL Server trims equal to or more than 50%

Now , one can easily deduce that 2GB RAM was committed [see commited in the error] but only 1 GB was the working set (see the error again) .So 50% is the current working set as compared to its committed value .Remaining 50% in in the disk and hence SQL Server will have to go for Hard page fault in case the page its looking for is on the disk .

Many a times you will see that on a cluster it will simply failover .This is because of the same effect .

Why it happens ??

Every process needs A contiguous chunk of memory but memory is largely fragmented ...
Some EXEs needs large chunks of contiguous memory .So even if there is enough memory available , its fragmented .And thats the cause of this issue .

When troubleshooting these type of issues, the key is to find out why the physical memory on the server gets exhausted and creates a situation that forces sql server to scale its memory usage back. As long as the available memory is below the limits for which Windows keeps sending low memory notification, sql server has no option but to scale itself back to avoid poor performance.

More on the dynamic memory management capabilities of sql server is discussed in the topic http://msdn2.microsoft.com/en-us/library/ms178145.aspx

There are a few situations where SQL Server Process working set might be paged out by Windows despite these memory resource notification mechanism.

-> If windows is not sending the correct notifications to all listening processes at the right moment and thresholds.

-> If SQL Server is not responding fast enough to the low memory resource notification from Windows.

-> Conditions in Windows where working sets of all processes are trimmed.

-> Windows might decide to trim a certain percentage of working set of various or specific processes.

-> Incorrect usage of memory management functions by Device Drivers.

SQL Server memory management is designed to dynamically adjust the committed memory based on the amount of available memory on the system. SQL Server does this to avoid being paged out by the OS memory manager with the help of some APIs.

SQL Server registers with the memory resource notifications of Windows. If a low memory notification comes from Windows, sql server scales its memory usage down. When windows sends the high memory notification, sql server can grow its memory usage target. The Win32 API's being used for these are documented in http://msdn.microsoft.com/library/default.asp?url=/library/en-us/memory/base/creatememoryresourcenotification.asp

Solutions
This issue can be fake or real .Do not panic if that workign ser is low , say in KBs or you get these errors when SQL Server starts up .

But if its the other way out then you can go through options i have mentioned below .If eventually nothing works , then you need to dig out who on the server needs so much of memory that makes OS to trim SQL Server's working set .

KNOWN ISSUES[Please go through each of it and apply as relevant]:

Microsoft has found some known issues that may cause the SQL Server 2005 process working set to be trimmed.

Issue1:

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

905865 The sizes of the working sets of all the processes in a console session may be trimmed when you use Terminal Services to log on to or log off from a computer that is running Windows Server 2003

Issue2:

Computers that are running Windows Server 2003 can be too aggressive when they cache dirty buffers if there is an application performing buffered I/O, such as a file copy operation. This behavior can cause the working set in SQL Server to be trimmed. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

920739 You may experience a decrease in overall system performance when you are copying files that are larger than approximately 500 MB in Windows Server 2003 Service Pack 1 or Service Pack 2

Issue3:

On a computer that is running SQL Server, applications may use the system cache too much. Therefore, the operating system will trim the working set of SQL Server or of other applications. If you notice that the application uses the system cache too much, you can use some memory management functions in the application. These functions control the system cache space that file IO operations can use in the application. For example, you can use the SetSystemFileCacheSize function and the GetSystemFileCacheSize function to control the system cache space that file IO operations can use.

Issue4:

Additionally, third-party device drivers that use the MmAllocateContiguousMemory function and specify the value of the HighestAcceptableAddress parameter to less than 4 gigabytes (GB) may also cause the SQL Server 2005 64-bit working set to be trimmed.

Currently, the iLO Management Channel Interface Driver (Cpqcidrv.sys) from Hewlett-Packard is known to cause this issue on x64 editions of SQL Server 2005.

The integrated Lights-Out Management (iLO) Interface Driver update for Windows Server 2003 is available at the following Hewlett-Packard Web site:

http://h20000.www2.hp.com/bizsupport/TechSupport/Document.jsp?lang=en&cc=us&objectID=c00688313&jumpid=reg_R1002_USEN

The problem is fixed in this driver update.

Issue5:

Broadcom driver bxvbda.sys caused memory trimming via MmAllocateContiguousMemory()

This driver is in Windows 2003 SP2, and OEM'ed from DELL and HP, but originally from Broadcom.

Disabling the advanced functionality from the driver may fully resolve the issue. In order to do that you have to do 2 things:

- On Windows 2003 Sp1, apply the hotfix download from http://support.microsoft.com/default.aspx?scid=kb;EN-US;936594 and perform the below mentioned registry changes. On Windows 2003 Sp2, just perform the below mentioned registry changes.

- Disable TCP Chimney, TCPA, and RSS using the registry entries

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters]

"DisableTaskOffload"=dword:00000001

"EnableTCPA"=dword:00000000

"EnableRSS"=dword:00000000

"EnableTCPChimney"=dword:00000000

TCP Chimney is a stateful offload. TCP Chimney offload enables TCP/IP processing to be offloaded to network adapters that can handle the TCP/IP processing in hardware. Each TCP Chimney offload-capable network adapter has a finite number of connections that it can support in hardware. TCP connections will be offloaded to hardware as long as the hardware can support these connections. After the hardware offload connection limit has been reached, all additional connections are handled by the host stack.

Side effects: the NIC will actually function better

Article on the affect of the setting: http://support.microsoft.com/default.aspx?scid=kb;EN-US;912222

Article on how this setting affects SQL operations: http://support.microsoft.com/default.aspx?scid=kb;EN-US;918483

Issue 6:

938486 A Windows Server 2003-based computer becomes unresponsive because of a memory manager trimming operation that is caused by an indeterminate module that requests lots of memory

http://support.microsoft.com/default.aspx?scid=kb;EN-US;938486

This kernel hotfix limits the number of trim operation per process to 8,192 pages. Previously, the memory manager would try to trim one-quarter of the working set size of a process. This can help to a certain extent on some of the processes being paged out.

ACTION PLAN that you can follow :

1. Cap SQL Server Max server memory to 75% of RAM and min to say , 1 GB .
2. Increasing the LowMemoryThreshold value.
Set the LowMemoryThreshold value (in MB), so the OS will notify applications such as SQL on low memory conditions much earlier.

In Regedit -> go to

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\SessionManager\MemoryManagement

Right click on the right pane,

Select New -> select click DWORD Value -> enter LowMemoryThreshold

Double Click LowMemoryThreshold -> value (choose decimal) -> 650

Reboot required to take effect.

Default values per MSDN:

“The default level of available memory that signals a low-memory-resource notification event is approximately 32 MB per 4 GB, to a maximum of 64 MB. The default level that signals a high-memory-resource notification event is three times the default low-memory value.”

3. Disable TCP Chimney, TCPA, and RSS using the registry entries [mentioned above in known issues # 5]

4.Reboot the server for the registry values to take effect .

5. If needed be involve the OS(platforms) team to find out if there is any memory leak (chance of this is very less, but still) .

References :
http://support.microsoft.com/kb/918483

GOOD NEWS :
Lock Pages in memory option is available in SQL Server STD edition from SQL Server 2005 SP3 CU4 and SQL Server 2008 SP1 CU2 onwards .

CAUTION:
LPIM should be the last resort that you should opt for .In worst case if you have to opt for it ,make sure you cap SQL Server Max server memory to 75% of RAm or might be less .

Happy Learning !!!

Recreating Builtin/administrator account in SQL Server 2005

2009-09-12T00:34:00.001+05:30

Someone at client's site removed the builtin admin from SQL Server as it was one of the risks mentioned in the agreement .Everything was fine .

However,during maintenance SQL server did not come up after the server reboot .They tried it with SA but they also forgot the SA pasword :-) ....awesome ...
They were about to uninstall and reinstall SQL server when we finally did the steps below .It took me a lot of time but one of the options worked (with my previous MS experience ofcourse :) ).Below are the repro steps and the solution .

Repro of issue :

1) Delete the Builtin/administrator account .
2) tested it through sqlcmd and got the error 18456 Level 14 State 1.
3) assume that i have forgotten the SA password as well.

Solution:

1) Stop SQL Server service and start it with -m
2) go to C:\Program Files\Microsoft SQL Server\90\Tools\Binn through cmd prompt
3) type sqlcmd -E and hit enter .If its named instance then sqlcmd -
-SServer\instance -E and hit enter.
4) you will get > sign
5) commands you need to use
use master
go
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=
[master], DEFAULT_LANGUAGE=[us_english]
go

6) You are done.Exit out of it .
7) restart you SQL Server service without -m parameter.

Happy learning .....

Script to copy errors form error log and get it through mail .

2009-09-10T20:44:00.000+05:30

This tim ei was asked to create job/SP that is capable to send a daily mail to DBAs with selected errors form error logs. Below are the steps to create a stored procedure to copy the errors from errorlogs to a table and then send the table as an attachment to our inbox .You might have to modify it as per your need .Or you can use WMI for SQL Server errorlogs ..I did not try that yet ...but will post it once done ...

for mailing you can also use CDO...will post that stuff soon :)

Steps included are :

1) Creating database mail account

2)Creating database mail profile

3) increasing the errorlogs to a default of 50 from 7

4) creating a table to store xp_readerrorlogs values

5) filtering the errors though a query

5) using database mail to send the output as an attachment to inbox

6) Finally recycling the errorlog

USE [msdb]

GO

/*Step 1:Creating a database mail account */

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = 'Test1',

@description = 'Test account for Database Mail',

@email_address = 'your_email.com',

@display_name = 'Test1',

@mailserver_name = 'smtp..com'

/*Step 2:Creating a database mail profile */

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = 'TestProfile',

@description = 'Test Profile for database mail'

/*Step 3: adding database mail account to database profile created earlier*/

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = 'TestProfile',

@account_name = 'Test1',

@sequence_number = 1

/*Step 4:To make the TestProfile we created a default public profile */

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = 'TestProfile',

@principal_name = 'default',

@is_default = 1 ;

/* To increase the number of error logs to 50 :

1: regedit >> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer

2: create a REG_DWORD key 'NumErrorlogs' and set the value to 50

*/

/* Step 6:create a table for keeping the error log values */

USE [master]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[errorlog](

[logdate] [date] NULL,

[processinfo] [varchar](20) NULL,

[line] [varchar](1000) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/* Step 7: Create a stored proc for :

-> Truncating the table we created above .

-> Inserting the values from error log into the table we created above

-> Sending the mail .

-> cycling the errorlog

*/

Create proc [dbo].[ErrorlogEmail]

as

truncate table errorlog

insert into errorlog exec xp_readerrorlog

DECLARE @SQL varchar(2000)

DECLARE @date varchar (2000)

DECLARE @File varchar(1000)

select @date= convert(date,GETDATE())

SET @SQL = 'select * from errorlog where line like (''%Severity%'') or line like (''15 seconds'') or line like (''%latch%'')or line like (''%BEGIN STACK DUMP%'')or line like (''%time-out%'')and logdate = convert(date,GETDATE())'

SET @File = 'Errorlog report'+@date+'.csv'

EXECUTE msdb.dbo.sp_send_dbmail

@profile_name = 'test',

@recipients = 'your email.com',

@subject = 'Errorlog report',

@body = 'Attached please find the Daily errorlog 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 8: recycle errorlog */

EXEC master.sys.sp_cycle_errorlog

print 'Error log recycled'

Hope this helps ...

Happy Learning ...

WMI alert that can detect any alteration to the SP [SQL 2005 and above]

2009-09-02T19:09:00.000+05:30

This is the WMI alert that can detect any alteration to the Stored Procedure .Its going to tell you the date , login name , user name , object name .
Like this we can create the alert on anything you want .As I said in my previous post ,WMI is very flexible , uses less resources and much faster .

/* Step 1: creating the table to capture the Alter Proc information */

USE adventureworks
GO

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

CREATE TABLE [dbo].[Alterprocevents] (
[AlertTime] [datetime] NOT NULL ,
[Object_name] varchar(100),
[Login_Name] varchar(100),
[user_name] varchar(100),
[RecordID] [int] IDENTITY (1,1) NOT FOR REPLICATION NOT NULL,
[Flag] [int] NOT NULL CONSTRAINT [DF_AlterprocEvents_Flag] DEFAULT ((0))
) ON [PRIMARY]
GO

--CREATE INDEX [Alterproc_IDX01] ON [dbo].[Alterprocevents]([AlertTime]) WITH FILLFACTOR = 100 ON [PRIMARY]
--GO

/*Step 2 : Creating the Job that will enter values into the Alterprocevents table created above*/
/*Error handling is also added and we are running it in a transaction*/
USE [msdb]
GO

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

GO

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 Alter proc 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 Capture Alter proc event events',
@category_name=N'[Uncategorized (Local)]',
@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 graph 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 [dbo].[Alterprocevents](
AlertTime,
object_name,
login_name,
user_name
)
VALUES (
GETDATE(),
N''$(ESCAPE_NONE(WMI(objectname)))'',
N''$(ESCAPE_NONE(WMI(loginname)))'',
N''$(ESCAPE_NONE(WMI(username)))''
)',
@database_name=N'adventureworks',
@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

/* Step 4: 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 alterproc_change')
EXEC msdb.dbo.sp_delete_alert @name=N'Respond to alterproc_change'

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 alterproc_change',
@wmi_namespace=@server_namespace,
@wmi_query=N'SELECT * FROM DDL_PROCEDURE_EVENTS',
@job_name='Capture Alter proc event' ;
GO

Hope you like it .
Regards
Abhay

WMI alert for deadlocking (no need to configure trace flags 1222) : For SQL 2005 and above only

2009-09-02T19:03:00.000+05:30

Before I proceed forward , I just wanted to say that scriting through WMI is amazing ..You will take some time in learning it and might get bored with syntax ...But believe me it will catch your interest as anything is posible wih it ..

Here is the WMI script to trace the deadlock .Its only for SQL Server 2005 and above as I have to see if SQL Server 2000 supports WMI namespace by default.
This also has the mailing script , which we can use for all kinds of alerts .WMI scrips are faster and less resource consuming .I have also added error handling and kept everything important in a transaction , so that it rollsback completely .This is for one database but we can configure for as many by only adding more alerts which will be using the same job.

IMP : Its not going to work on RTM as there is a Bug where the job fails giving the incorrect syntax error (actualy it parses successfully).I applied SP3 and it worked .So not sure if it works on SP1 and SP2 .

Part 1 :

1) Creating the table to capture the deadlock information.
2) Creating the Job that will enter values into the Deadlockevents table created above.
3) Insert graph into LogEvents.
4) Creating the alert and associating it with the Job to be fired.
5) Create a stored proc for sending the deadlock information as .CSV file through the mail.
6) Changing the flag to 1 so that next time this information is not sent.

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

USE
GO

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

CREATE TABLE [dbo].[DeadlockEvents] (
[AlertTime] [datetime] NOT NULL ,
[DeadlockGraph] [xml],
[RecordID] [int] IDENTITY (1,1) NOT FOR REPLICATION NOT NULL,
[Flag] [int] NOT NULL CONSTRAINT [DF_DeadlockEvents_Flag] DEFAULT ((0))
) ON [PRIMARY]
GO

CREATE INDEX [DeadlockEvents_IDX01] ON [dbo].[DeadlockEvents]([AlertTime]) WITH FILLFACTOR = 100 ON [PRIMARY]
GO

/*Step 2 : Creating the Job that will enter values into the Deadlockevents table created above*/
/*Service account and sql operator option are optional*/
/*Error handling is also added and we are running it in a transaction*/
USE [msdb]
GO

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Capture Deadlock Graph')
EXEC msdb.dbo.sp_delete_job @job_name = N'Capture Deadlock Graph', @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 Deadlock Graph',
@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 DEADLOCK_GRAPH 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 graph 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 DeadlockEvents (
AlertTime,
DeadlockGraph
)

VALUES (
GETDATE(),
N''$(ESCAPE_NONE(WMI(TextData)))''
)',
@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 DEADLOCK_GRAPH')
EXEC msdb.dbo.sp_delete_alert @name=N'Respond to DEADLOCK_GRAPH'

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 DEADLOCK_GRAPH',
@wmi_namespace=@server_namespace,
@wmi_query=N'SELECT * FROM DEADLOCK_GRAPH',
@job_name='Capture Deadlock Graph' ;
GO

/* Step 5: Create a stored proc for sending the deadlock information as .CSV file */

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

EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'test',
@recipients = 'your email.com',
@subject = 'Deadlock report',
@body = '***URGENT***Attached please find the deadlock 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.DeadlockEvents set flag = 1 where flag = 0
go

Part 2:

1) Creating the DbMmail account
2) Creating a DbMail profile.
3) Adding account to profile.
4) making the profile as public.

USE [msdb]
GO

/*Step 1:Creating a database mail account */
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'Test1',
@description = 'Test account for Database Mail',
@email_address = 'your_email.com',
@display_name = 'Test1',
@mailserver_name = 'smtp..com'

/*Step 2:Creating a database mail profile */
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'TestProfile',
@description = 'Test Profile for database mail'

/*Step 3: adding database mail account to database profile created earlier*/
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'TestProfile',
@account_name = 'Test1',
@sequence_number = 1

/*Step 4:To make the TestProfile we created a default public profile */
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'TestProfile',
@principal_name = 'default',
@is_default = 1 ;

How to drop all the user databases in one GO

2009-09-02T18:41:00.000+05:30

It was Saturday Morning and I had a task @ hand .
One of my collegues wanted me to write a script to drop all the user databases at one go ..

I was initially reluctant as I thought any DDL can not take variables .But then I tried with some other stuff which did not use variables with DMLs.

Initially I came up with the script below :

Create table drop_db(name sysname)
Insert into drop_db select name from sysdatabases where dbid >4
Select 'drop database ' + name from db_name

Copy the output and execute.You canuse the same trick for other purposes as well .

But then I wanted to give it some more try and came up with the final script .

Drop table #db_name
Go
Create table #db_drop (name sysname,flag int identity (1,1) )
Insert into #db_drop select name from sysdatabases where dbid>4
Declare @@flag1 int
Select @@flag1 =max (flag) from #db_drop

While @@flag1 >0
Begin
Declare @string nvarchar (30)
Declare @dbname sysname
Select @dbname =name from #db_drop where flag=@@flag1
Print @dbname + ' is being deleted'
Set @string =' drop database ' + @dbname
Execute sp_executesql @string
Set @@flag1 =@@flag1 +1
End
Drop table #db_drop

How to automate LogShipping setup On the large number of databases

2009-06-17T12:27:00.000+05:30

Warning : Use it at your own risk after testing on DEV/TEST.Publisher has no responsibility whatsoever of the issues that you might face :D ....

Well, I was given the task of setting up logshipping for more than 800 databases .
I started scratching my head since there was no help available anywhere .
Finally , I decided to create my own scripts .Below I have copied the scripts and also a resolution of the issue that I found very common on the internet .

Assumptions :
-> this is for the fresh setup of logshipping and not for the setup where you have already set it up on some databses and now wan tto set it up on some more databases .
-> You need to first backup and restore the databases to the standby server in the standby mode or you can automate it .There are many scripts available on the internet.So i am not reinventing the wheel.
-> I will update this script at a later stage to take backups and restore it in standby mode on another server with move option .
-> This script is assuming that all the user databases on the server instance needs to be log shipped .So , you need to either manually delete the jobs that are going to be created or need to automate it.I am sure its not that difficult .something like tweaking sp_helpdb and creating a new SP wiht the dbname output (or just inserting the complete output in a temp table ).sp_helpdb will nto show you the databases that are offline .So you can take all the databases offfline that you do not want to be logshipped .Ofcourse its a bad idea but i just gave you the hint that its possible to have some adjustment that can allow you to logship few databases .
-> At many places you need to change the inputs like server/instance name , job/schedule name etc etc .In short whatever is fixed and in single quotes can be changed .Again it can be automated for sure .But I just dint take that pain.
-> You need to be in SQLCMD mode. on the Query menu select the SQLCMD mode .
-> You need to first run this script on the principal server and from there it will connect to the mirror using SQLCMD command :connect server\instance.
-> Instead of running this as a script you can also create a stored proc and execute it .
-> I have not added any kind of rolling back stuff so that if anything goes wrong it rolls back the changes .So you need to manually remove the logshipping using GUI in case any step fails .Its quite easy but generally there is no need to do this as it runs fine .
-> I have also created 3 jobs that are going to serially execute all the other jobs[backup/copy/restore] on both the servers .But all of them does not have the last step set as "quit with success" .You need to manually set that in 3 jobs.
-> wait for delay is optional .You can remove it if needed be .

Check this first :
---------------------

Run these SPs first on secondary and then on Primary if your previous setup failed or had some issue wiping out the stuff:
You can write a script to take the database names from sysdatabases and then fetch it to the variables (you will see how its done in the script many times).

sp_delete_log_shipping_primary_database @database= ''
sp_delete_log_shipping_primary_secondary @primary_database='',@secondary_server='abhay-test\SPIDER',@secondary_database =''
sp_delete_log_shipping_secondary_database @secondary_database=''
sp_delete_log_shipping_secondary_primary @primary_database='',@primary_server='abhay-test\Apollo'

check these tables on both primary and secondary in MSDB context .If you find any row that should not be there i.e. a row for a database which is no longer a part of logshipping but still exists , delete that row using where clause .if you feel that you first setup of logshipping failed and all the rows in the tables below are not needed delete all the rows .

dbo.log_shipping_monitor_alert
dbo.log_shipping_monitor_error_detail
dbo.log_shipping_monitor_history_detail
dbo.log_shipping_secondary_databases
dbo.log_shipping_secondary
dbo.log_shipping_secondaries
dbo.log_shipping_primary_secondaries
dbo.log_shipping_primary_databases
dbo.log_shipping_primaries
dbo.log_shipping_monitor_secondary

/* Start of the Script Primary is ORION and Secondary is SHERRY*/

:connect abhay\Orion <--you can change it/*STEP 1 : This step will setup logshipping jobs on the principal instance*/use MSDBgoprint 'STEP 1:This step will setup logshipping jobs on the principal instance'gowaitfor delay '00:00:01'declare @var intDECLARE @dbname sysname ,@dbsid intDECLARE LSP_cursor CURSOR FORSELECT name,dbid FROM sys.sysdatabases where dbid >4 --order by dbid desc
OPEN LS_cursor
FETCH NEXT FROM LSP_cursor INTO @dbname,@dbsid

WHILE @@FETCH_STATUS = 0

BEGIN
declare @database1 sysname
declare @backup_job_name1 sysname
declare @schedule_name1 sysname
declare @primary_database1 sysname

begin
set @database1 =@dbname
set @backup_job_name1 =N'LSBackup_dbid_' + @dbname
set @primary_database1 = @dbname

Begin
DECLARE @LS_BackupJobId AS uniqueidentifier
DECLARE @LS_PrimaryId AS uniqueidentifier
DECLARE @SP_Add_RetCode As int
DECLARE @backup_job_name sysname
DECLARE @backup_job_id AS uniqueidentifier

EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database
@database = @database1
,@backup_directory = N'C:\Principal Mirror share' <--you can change it,@backup_share = N'\\abhay\Principal Mirror share' <--you can change it,@backup_job_name = @backup_job_name1 ,@backup_retention_period = 4320,@backup_compression = 2,@backup_threshold = 60 ,@threshold_alert_enabled = 1,@history_retention_period = 5760 ,@backup_job_id = @LS_BackupJobId OUTPUT ,@primary_id = @LS_PrimaryId OUTPUT ,@overwrite = 1IF (@@ERROR = 0 AND @SP_Add_RetCode = 0) BEGIN DECLARE @LS_BackUpScheduleUID As uniqueidentifier DECLARE @LS_BackUpScheduleID AS intEXEC msdb.dbo.sp_add_schedule @schedule_name =N'LSBackupSchedule_ORION_common'<--you can change it,@enabled = 1 ,@freq_type = 4 ,@freq_interval = 1 ,@freq_subday_type = 4 ,@freq_subday_interval = 15 ,@freq_recurrence_factor = 0 ,@active_start_date = 20090507 ,@active_end_date = 99991231 ,@active_start_time = 0 ,@active_end_time = 235900 ,@schedule_uid = @LS_BackUpScheduleUID OUTPUT ,@schedule_id = @LS_BackUpScheduleID OUTPUT EXEC msdb.dbo.sp_attach_schedule @job_id = @LS_BackupJobId ,@schedule_id = @LS_BackUpScheduleID EXEC msdb.dbo.sp_update_job @job_id = @LS_BackupJobId ,@enabled = 0ENDset @LS_BackUpScheduleUID =nullset @LS_BackUpScheduleID =nullENDEXEC master.dbo.sp_add_log_shipping_alert_jobEXEC master.dbo.sp_add_log_shipping_primary_secondary @primary_database = @dbname,@secondary_server = N'ABHAY\Sherry' <--you can change it,@secondary_database = @primary_database1 ,@overwrite = 1 FETCH NEXT FROM LS_cursor INTO @dbname,@dbsidset @LS_BackupJobId =null set @LS_PrimaryId =nullset @SP_Add_RetCode =nullset @backup_job_name =nullset @backup_job_id =nullset @database1 =nullset @backup_job_name1 =nullset @schedule_name1 =nullset @primary_database1 =nullENDendCLOSE LSP_cursorDEALLOCATE LS_cursorGOprint '****** End of STEP 1 ******' waitfor delay '00:00:01'/* STEP2: This will create a job that is going to execute all the logshipping backup jobs serially on principal instance*/print 'STEP 2:This step will create a job that is going to execute all the logshipping backup jobs serially on principal instance'gowaitfor delay '00:00:01'USE [msdb]GODECLARE @ReturnCode INTSELECT @ReturnCode = 0IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'ENDDECLARE @jobId BINARY(16)EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Serial Execution of logshipping backup ', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'Serial Execution of log shipping backup jobs.', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'emids1\abhayc', @job_id = @jobId OUTPUT DECLARE @sec_dbname varchar(200), @command1 varchar(200)DECLARE bckp_cursor CURSOR FORselect secondary_database ,primary_id from dbo.log_shipping_primary_secondaries where secondary_database <> 'model'

OPEN bckp_cursor
FETCH NEXT FROM bckp_cursor INTO @sec_dbname, @command1
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @command2 varchar (1000)
Declare @sec_dbname1 varchar (1000)
Set @command2=N'"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqllogship.exe" -Backup '+@command1+' -server ABHAY\Sherry' <--you can change itSet @sec_dbname1= N'for '+ @sec_dbnameEXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=@sec_dbname1, @cmdexec_success_code=0, @on_success_action=3, @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'CmdExec', @command=@command2,@flags=0FETCH NEXT FROM bckp_cursor INTO @sec_dbname, @command1 ENDCLOSE bckp_cursorDEALLOCATE bckp_cursorGOUSE msdb <-- this is because many a times i have got the error that the job is not registered with the server .So need to add this step .EXEC sp_add_jobserver @job_name = 'Serial Execution of logshipping backup',@server_name = 'ABHAY\Orion'<--you can change itgoprint '****** End of STEP 2 ******' waitfor delay '00:00:01'/* STEP 3: This step will create all the logshipping jobs on standby instance*/:connect abhay\Sherryprint 'STEP 3: This step will create all the logshipping jobs on standby instance'waitfor delay '00:00:01'Use MSDBGodeclare @var intDECLARE @dbname sysname ,@dbsid intDECLARE LSS_cursor CURSOR FORSELECT name,dbid FROM sys.sysdatabases where dbid > 4 --order by dbid desc
OPEN LS_cursor
FETCH NEXT FROM LSS_cursor INTO @dbname,@dbsid

WHILE @@FETCH_STATUS = 0

Begin

declare @copy_job_name1 sysname
declare @restore_job_name1 sysname
declare @database1 sysname
declare @schedule_name1 sysname
declare @primary_database1 sysname

begin
set @database1 =@dbname
set @copy_job_name1 =N'LScopy_dbid_' + @dbname
set @primary_database1 = @dbname
set @copy_job_name1=N'LSCopy_dbid_' + @dbname
set @restore_job_name1=N'LSRestore_dbid_' + @dbname

begin
DECLARE @LS_Secondary__CopyJobId AS uniqueidentifier
DECLARE @LS_Secondary__RestoreJobId AS uniqueidentifier
DECLARE @LS_Secondary__SecondaryId AS uniqueidentifier
DECLARE @LS_Add_RetCode As int

EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary
@primary_server = N'ABHAY\Orion' <--you can change it,@primary_database = @database1 ,@backup_source_directory = N'\\abhay\Principal Mirror share' <--you can change it,@backup_destination_directory = N'E:\Mirroring test\backups' <--you can change it,@copy_job_name = @copy_job_name1 ,@restore_job_name = @restore_job_name1 ,@file_retention_period = 4320 ,@overwrite = 1 ,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT ,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT ,@secondary_id = @LS_Secondary__SecondaryId OUTPUTIF (@@ERROR = 0 AND @LS_Add_RetCode = 0) BEGINDECLARE @LS_SecondaryCopyJobScheduleUID As uniqueidentifier DECLARE @LS_SecondaryCopyJobScheduleID AS int EXEC msdb.dbo.sp_add_schedule @schedule_name =N'DefaultCopyJobSchedule_Sherry' <--you can change it [using 1 schedule for all jobs],@enabled = 1 ,@freq_type = 4 ,@freq_interval = 1 ,@freq_subday_type = 4 ,@freq_subday_interval = 15 ,@freq_recurrence_factor = 0 ,@active_start_date = 20090511 ,@active_end_date = 99991231 ,@active_start_time = 0 ,@active_end_time = 235900 ,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT ,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUTEXEC msdb.dbo.sp_attach_schedule @job_id = @LS_Secondary__CopyJobId ,@schedule_id = @LS_SecondaryCopyJobScheduleIDDECLARE @LS_SecondaryRestoreJobScheduleUID As uniqueidentifier DECLARE @LS_SecondaryRestoreJobScheduleID AS int EXEC msdb.dbo.sp_add_schedule @schedule_name =N'DefaultRestoreJobSchedule_Sherry' ,@enabled = 1 ,@freq_type = 4 ,@freq_interval = 1 ,@freq_subday_type = 4 ,@freq_subday_interval = 15 ,@freq_recurrence_factor = 0 ,@active_start_date = 20090511 ,@active_end_date = 99991231 ,@active_start_time = 0 ,@active_end_time = 235900 ,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT ,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUTEXEC msdb.dbo.sp_attach_schedule @job_id = @LS_Secondary__RestoreJobId ,@schedule_id = @LS_SecondaryRestoreJobScheduleID END DECLARE @LS_Add_RetCode2 As int IF (@@ERROR = 0 AND @LS_Add_RetCode = 0) BEGINEXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database @secondary_database = @database1 ,@primary_server = N'ABHAY\Orion' ,@primary_database = @database1 ,@restore_delay = 0 ,@restore_mode = 1 ,@disconnect_users = 0 ,@restore_threshold = 45 ,@threshold_alert_enabled = 1 ,@history_retention_period = 5760 ,@overwrite = 1END IF (@@error = 0 AND @LS_Add_RetCode = 0) BEGINEXEC msdb.dbo.sp_update_job @job_id = @LS_Secondary__CopyJobId ,@enabled = 0 <-- disabled by defaultEXEC msdb.dbo.sp_update_job @job_id = @LS_Secondary__RestoreJobId ,@enabled = 0 <-- Disabled by defaultENDFETCH NEXT FROM LS_cursorINTO @dbname,@dbsid/*need to set the values to null else it will only take the last database values and will create logshippin gon only 1 database.*/set @LS_Secondary__CopyJobId =null set @LS_Secondary__RestoreJobId =nullset @copy_job_name1 =nullset @database1 =nullset @restore_job_name1 =nullset @schedule_name1 =nullset @primary_database1 =nullENDendendCLOSE LSS_cursorDEALLOCATE LSS_cursorGOprint '****** End of STEP 3 ******' waitfor delay '00:00:01'/* STEP 4: This step will create a job that is going to execute all the logshipping restore jobs serially on standby instance*/print 'STEP 4: This step will create a job that is going to execute all the logshipping restore jobs serially on standby instance'gowaitfor delay '00:00:01'USE [msdb]GODECLARE @ReturnCode INTSELECT @ReturnCode = 0IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'ENDDECLARE @jobId BINARY(16)EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Serial Execution Restore', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'Serial Execution of log shipping Restore jobs.', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'emids1\abhayc', @job_id = @jobId OUTPUT DECLARE @sec_dbname varchar(200), @command1 varchar(200)DECLARE rest_cursor CURSOR FORselect primary_database ,secondary_id from dbo.log_shipping_secondary where primary_database <> 'model'

OPEN cpy_rest_cursor
FETCH NEXT FROM rest_cursor INTO @sec_dbname, @command1
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @command2 varchar (1000)
Declare @sec_dbname1 varchar (1000)
Set @command2=N'"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqllogship.exe" -Restore '+@command1+' -server ABHAY\Sherry' <--you can change itSet @sec_dbname1= N'for '+ @sec_dbnameEXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=@sec_dbname1, @cmdexec_success_code=0, @on_success_action=3, @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'CmdExec', @command=@command2,@flags=0FETCH NEXT FROM rest_cursor INTO @sec_dbname, @command1ENDCLOSE rest_cursorDEALLOCATE rest_cursorGOUSE msdbEXEC sp_add_jobserver @job_name = 'Serial Execution Restore', @server_name = 'Abhay\Sherry'goprint '****** End of STEP 4 ******' waitfor delay '00:00:01'/* STEP 5: This step will create a job that is going to execute all the logshipping copy jobs serially on standby instance*/print 'STEP 5: This step will create a job that is going to execute all the logshipping copy jobs serially on standby instance'waitfor delay '00:00:01'USE [msdb]GODECLARE @ReturnCode INTSELECT @ReturnCode = 0IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)BEGINEXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'ENDDECLARE @jobId BINARY(16)EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Serial Execution Copy', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'Serial Execution of log shipping Copy jobs.', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'emids1\abhayc', @job_id = @jobId OUTPUT DECLARE @sec_dbname varchar(200), @command1 varchar(200)DECLARE cpy_cursor CURSOR FORselect primary_database ,secondary_id from dbo.log_shipping_secondary where primary_database <> 'model'

OPEN cpy_cursor
FETCH NEXT FROM cpy_cursor INTO @sec_dbname, @command1
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @command2 varchar (1000)
Declare @sec_dbname1 varchar (1000)
Set @command2=N'"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqllogship.exe" -Copy '+@command1+' -server ABHAY\Sherry' <--you can change itSet @sec_dbname1= N'for '+ @sec_dbnameEXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=@sec_dbname1, @cmdexec_success_code=0, @on_success_action=3, @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'CmdExec', @command=@command2,@flags=0FETCH NEXT FROM cpy_cursor INTO @sec_dbname, @command1 ENDCLOSE cpy_cursorDEALLOCATE cpy_cursorGOUSE msdbEXEC sp_add_jobserver @job_name = 'Serial Execution Copy', @server_name = 'Abhay\Sherry'goprint '****** End of STEP 5 ******' Issues I faced :
----------------------

Issue 1:
first issue that I faced was that the job was not registerd to the server .It was not every time but sometimes .

Solution :
So I explicitly add the step to register the job using "sp_add_jobserver" .

Issue 2 :
The second issue that I faced ate a lot of my time :) .Issue is the copy and restore jobs failing on the secondary server .
*** Error: The specified agent_id 76D087E5-B8BB-47BE-886B-3EF9CCC3A7DE or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
-> this agent id is the database ID .Cross check in the copy job .And notice that its saying about log shipping monitoring processing .

Solution :

Notice what the error is trying to say :
-> agent_type 1 : is the copy job .0 is for backup and 2 is for restore
-> 76D087E5-B8BB-47BE-886B-3EF9CCC3A7DE: is the secondary database UID .unique for all the databases .
-> This issue has something to do with log shipping monitoring .

So I decided to go through all the logshipping system tables .I found that dbo.log_shipping_secondary_databases and dbo.log_shipping_monitor_secondary have one row each for all the logshipped database.

dbo.log_shipping_monitor_secondary : is going to keep the last backup and restore information of the log .the value changes with every copy and restore on the standby server.
dbo.log_shipping_secondary_databases : is going to have the entry for all the secondaty databases and the job properties with the last restored date .

I found that even after settign up the log shipping these 2 tables were not showing me the 2 entries .Further , i got 1 entry form the previous setup that was still existing .
I cleaned up the table and inserted 2 rows each in both the tables :

insert into dbo.log_shipping_secondary_databases values ('Adventureworks','C569B95C-ADA3-466A-B814-E19756D42EBA',0,1,1,0,-1,-1,-1,NULL,NULL)
insert into log_shipping_monitor_secondary values ('abhay\SHERRY','Adventureworks','C569B95C-ADA3-466A-B814-E19756D42EBA','abhay\ORION','Adventureworks',45,14421,1,NULL,null,NULL,null,null,NULL,null,5760)

insert into log_shipping_monitor_secondary values ('abhay\Sherry','OHM','76D087E5-B8BB-47BE-886B-3EF9CCC3A7DE','abhay\Orion','OHM',45,14421,1,NULL,null,NULL,null,null,NULL,null,5760) . Restart the agent ...it wont work
insert into dbo.log_shipping_secondary_databases values ('OHM','76D087E5-B8BB-47BE-886B-3EF9CCC3A7DE',0,1,1,0,-1,-1,-1,NULL,NULL)

I ran the job again and the issue was resolved .

Issue 3:
You might face a situation where the backup copy and restore jobs are running fine but still the restore is not happening and skipping the log file .

Solution :
this is because the restore is trying to find a previous log file that you you have manually deleted :) ..
You need to backup the database again and restore it on the standby database again (recover the standby database first or drop it )

Please let me know if you have any queries .You can post it here or mail me at hi_abhay78@yahoo.co.in

Useful tools from Sysinternals

2009-05-19T12:57:00.000+05:30

I was searching a tool capable of showing the Server enviornment on the desktop ..
Well its indeed BigInfo :) .You will find it on Sysinternal site .
Infact there are a lot of other useful tools that you can use ..or simply download the complete suit ..
Here you go ..

http://technet.microsoft.com/en-us/sysinternals/bb897557.aspx
Enjoy (",)

How to see the SSIS packages for the Named Instance (2005/2008) of SQL Server .

2009-05-18T13:40:00.000+05:30

-> Go to C:\Program Files\Microsoft SQL Server\100\DTS\Binn [this is SQL Server 2008 but the path would be same for 2005 as well .instead of 100 it will be 90].
-> Find the file MsDtsSrvr.ini.xml and open it in notepad .
-> You will see 10-12 XML format lines .
-> By default you will see : .
-> Remove the DOT (.) and replace it with the server name \instance name .
-> Restart the integertion service from Services console .

Hope this helps .

Manually Add Full Text Search Resources For A SQL 2005 Failover Cluster

2009-04-27T15:46:00.000+05:30

Reciently, I Faced this issue where the customer did not install the FT search initially and later on they came for help since it was a cluster.so I thought to share it here .Others are most welcome to Edit or Add ..

-> First you need to check if FT search service is installed (in case its there and someone deleted the resource from cluadmin)...if its not there in services or configuration manager , install it through the setup .

->In the Cluster Administrator, right-click on 'Resources' and choose New -> Resource

->In the New Resource dialog, choose 'Generic Service' for the type and give it a
name like SQL FULLTEXT. Any name is fine. Choose the desired group for the resource.

->In the next screen choose the nodes that the Full-text resource can run on.These should be the same as your SQL Server resource.

->In the next screen don't do anything. Full-text search doesn't need any dependencies.

->In the Generic Service Parameters screen type msftesql$ in the
'Service Name' box, where is the Virtual Server name for the SQL
Server. You can verify this name by opening Services.msc (Start -> Run -> type
'Services.msc' -> OK) and right-clicking on the SQL ServerFulltext Search service
and choosing properities. Us the Service Name in the properties dialog as the
Service Name in the New Resource dialog.

->In the 'Startup Parameters' box, enter the parameters shown in the service
property dialog (Start -> Run -> type 'Services.msc' -> OK -> right-click on SQL
ServerFulltext Search service -> choose 'Properties) at the end of the path in the
'Path to executable' box. For example: -s:MSSQL.2 -f:INST2

-> In the next screen don't do anything. Leave the 'Registry Replication'
information blank.

-> Click finish. The Full-text resource should be visible in your SQL Server group
in the Cluster Administrator.

-> Test by bringing the Full-text resource online and failing it over to the other
node.

Regards
Abhay

Perfmon Counter recommended values for SQL Server 2005

2009-04-10T16:48:00.000+05:30

Below are the perfmon counter benchmarks that I generally refer to :

Memory
Available Mbytes
> 100MB

Paging File
%Usage
< 70%

Process (sqlservr)
%Privileged Time
< 30% of %Processor Time (sqlservr)

Processor
%Privileged Time
< 30% of Total %Processor Time

PhysicalDisk
Avg. Disk Sec/Read
< 8ms

PhysicalDisk
Avg. Disk sec/Write
< 8ms (non cached) < 1ms (cached)

SQLServer:Access Methods
Forwarded Records/sec
< 10 per 100 Batch Requests/Sec

SQLServer:Access Methods
FreeSpace Scans/sec
<10 per 100 Batch Requests/Sec

SQLServer:Access Methods
Full Scans / sec
(Index Searches/sec)/(Full Scans/sec) > 1000

SQLServer:Access Methods
Workfiles Created/Sec
< 20 per 100 Batch Requests/Sec

SQLServer:Access Methods
Worktables Created/Sec
< 20 per 100 Batch Requests/Sec

SQL Server:Buffer Manager
Buffer Cache hit ratio
> 90%

SQL Server:Buffer Manager
Free list stalls/sec
< 2

SQL Server:Buffer Manager
Lazy Writes/Sec
< 20

SQL Server:Buffer Manager
Page Life Expectancy
> 300

SQLServer:Buffer Manager
Page lookups/sec
(Page lookups/sec) / (Batch Requests/sec) < 100

SQL Server:Locks
Lock Requests/sec
(Lock Request/sec)/(Batch Requests/sec) < 500

SQLServer:SQL Statistics
SQL Compilations/sec
< 10% of the number of Batch Requests/Sec

SQLServer:SQL Statistics
SQL Re-Compilations/sec
< 10% of the number of SQL Compilations

How to find how Cache/Bpool is being used and what objects are using the cache/Bpool ..

2009-04-10T16:32:00.001+05:30

1: To determine what plans are in the cache and how often they're used we can use
sys.dm_os_memory_cache_counters dm view .

SELECT TOP 6
LEFT([name], 20) as [name],
LEFT([type], 20) as [type],
[single_pages_kb] + [multi_pages_kb] AS cache_kb,
[entries_count]
FROM sys.dm_os_memory_cache_counters
order by single_pages_kb + multi_pages_kb DESC

Here :

CACHESTORE_OBJCP are compiled plans for stored procedures, functions and triggers.

CACHESTORE_SQLCP are cached SQL statements or batches that aren't in stored procedures, functions and triggers.This includes any dynamic SQL or raw SELECT statements sent to the server.

CACHESTORE_PHDR These are algebrizer trees for views, constraints and defaults. An algebrizer tree is the parsed SQL text that resolves the table and column names.

You will find these counters in DBCC Memorystatus as well.Infact DBCC Memory Status uses this dmv.This will give us idea about which cache is getting filled up .

Generally, you will find that CACHESTORE_SQLCP > CACHESTORE_OBJCP(because we cannot frame everything in to SPs) , but if the ratio of one to another is very high then we can say that there are more adhoc plans being run then Stored procedures.

You can also monitor the number of data pages in the plan cache using Performance Monitor (PerfMon) using SQLServer:Plan Cache object with the Cache Pages counter.There are instances for SQL Plans (CACHESTORE_SQLCP), Object Plans (CACHESTORE_OBJCP) and Bound Trees (CACHESTORE_PHDR). This will give you the same picture ..for e.g. under bound tree : multiply cache pages by 8. you will get the same output as in dbcc memorystatus and the dm we used above.

2 : After this to know the querry are being cached and used , we can use
sys.dm_exec_cached_plans and sys.dm_exec_sql_text dm views.

select TOP 50
objtype,
usecounts,
p.size_in_bytes/1024 'IN KB',
LEFT([sql].[text], 100) as [text]
from sys.dm_exec_cached_plans p
outer apply sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY usecounts DESC

and then we can check the query plans(and size) for the one we have some doubt .

3: To find what tables and indexes are in the buffer memory of your server you can use sys.dm_os_buffer_descriptors DMV.The query below can give us total currrent size of buffer pool .

select count(*) AS Buffered_Page_Count
,count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
from sys.dm_os_buffer_descriptors

After we have found the Bufferpool size , we can see which database is using more memory by runnig the query below .

SELECT LEFT(CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END, 20) AS Database_Name,
count(*)AS Buffered_Page_Count,
count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MB
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY Buffered_Page_Count DESC

And then we can go further at object level to see what all objects are consuming memory (and how much) .We can use the query below in each database we wish to :

SELECT TOP 25
obj.[name],
i.[name],
i.[type_desc],
count(*)AS Buffered_Page_Count ,
count(*) * 8192 / (1024 * 1024) as Buffer_MB
-- ,obj.name ,obj.index_id, i.[name]
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id, object_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id, object_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
LEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_id
WHERE database_id = db_id()
GROUP BY obj.name, obj.index_id , i.[name],i.[type_desc]
ORDER BY Buffered_Page_Count DESC

Setting CPU Priority level for a SQL Server (high/ low)

2009-01-06T13:04:00.000+05:30

SQL Server by default runs at priority base 7 , which is normal .
To raise it to 13 we can use Priority boost in sp_configure .

But reciently , I got a question if we can set the CPU priority to lower than normal.There is no option for this inside SQL Server.

So there was in inderect answer to this : use affinity mask , so that SQL is restricted to particular CPU/CPUs .But thats not the correct answer .

So is there a way to do that via task manager .Well , it has the option but we cant as we get some access denied error

What you can do is :

1) Open the DOS window >>Start /Low "\Binn\sqlservr.exe"
2) This will open another window .
3) In that new window type : "C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn\sqlservr.exe"[path of sqlserver executable for an instance] -sMSSQLSERVER <--instance name [copy it from the services dialog box]
4) connect to the management studio or OSQL/ISQL.
5) cross check in task manager .You will see that SQL Server is running under low prority
6) Now from the task manager you can change the priority ...not sure why :) ...

Hope this helps .......

To wihch database does the object belongs

2008-10-14T19:16:00.000+05:30

You know the object name but you do not know to which database does it belongs :) ..

Try msforeacdb :
sp_msforeachdb 'use ?;print "?";select name from sys.sysobjects where name='''''

How to detach All user databases ?

2008-10-14T19:05:00.000+05:30

SQL 2005

===========

select 'EXEC sp_detach_db @dbname = N'''+RTRIM ( name)+''','''+'true''' from sys.sysdatabases where dbid>6 and name not like 'distribution'

SQL 2000

==========

select 'EXEC sp_detach_db @dbname = N'''+RTRIM ( name)+''','''+'true''' from sysdatabases where dbid>4 and name not like 'distribution'

hozzzaaat ..

ERROR 229: The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'

2008-06-24T11:03:00.000+05:30

I once got this error while experimenting :

ERROR 229: The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

So seems like xp_cmdshell is an object in database 'mssqlsystemresource' and schema 'sys'. mssqlsystemresource cannot be accessed without DAC ...

In the first go I could not understand much ..But then i tried to run the same query ( it was a BCP query ) using the windows domain account it was successful ..

So it was clear that this is a permission issue on the sql login that I was using ..
Then after long time searching BOL i found that we need to set the database trustworthy .

ALTER DATABASE mydb SET TRUSTWORTHY ON ...and we were comfortable ..

The other way is to create the xp_cmdshell proxy account to impersonate the non-sysadmin users to allow them to access resources outside the SQL Server.

sp_xp_cmdshell_proxy_account [ NULL | { 'account_name' , 'password' } ]

otherwise you can also use the GUI ..

Hope this helps ..

Script that will help in Identifying query causing TempDB growth

2008-06-24T11:00:00.000+05:30

I am not creating any indexes assuming we know how to create them

CREATE TABLE [dbo].[tempUsage](

[Date_Time] [datetime] NOT NULL,

[session_id] [smallint] NULL,

[sql_handle] [varbinary](64) NULL,

[SQL_Text] [varchar](8000) NULL,

[request_internal_objects_alloc_page_count] [bigint] NULL,

[request_internal_objects_dealloc_page_count] [bigint] NULL

) ON [PRIMARY]

GO

CREATE VIEW all_request_usage

AS

SELECT session_id, request_id,

SUM(internal_objects_alloc_page_count) AS request_internal_objects_alloc_page_count,

SUM(internal_objects_dealloc_page_count)AS request_internal_objects_dealloc_page_count

FROM sys.dm_db_task_space_usage

GROUP BY session_id, request_id;

GO

CREATE VIEW all_query_usage

AS

SELECT R1.session_id, R1.request_id,

R1.request_internal_objects_alloc_page_count, R1.request_internal_objects_dealloc_page_count,

R2.sql_handle, R2.statement_start_offset, R2.statement_end_offset, R2.plan_handle

FROM all_request_usage R1

INNER JOIN sys.dm_exec_requests R2 ON R1.session_id = R2.session_id and R1.request_id = R2.request_id;

GO

Let’s run the statement every minute to capture SQL Statement that is causing TempDB growth.

{

INSERT INTO tempUsage

SELECT

GetDate() As Date_Time,

R1.session_id,

R1.sql_handle,

Cast ( R2.text As Varchar(8000)) SQL_Text,

R1.request_internal_objects_alloc_page_count,

R1.request_internal_objects_dealloc_page_count

FROM all_query_usage AS R1

OUTER APPLY sys.dm_exec_sql_text(R1.sql_handle) AS R2

where R1.request_internal_objects_alloc_page_count <> 0;

}

Installing Replication bits on a cluster after upgrading to SP2

2008-06-12T20:29:00.000+05:30

Reciently we faced an issue where SQL Server was on SP2 ( clustered Enviornment) and we were not able to install replication ..This is fairly simple on a standalone machine but not on cluster ..

If you try to achieve it through ADD/Remove (appwiz.cpl) program you may get this error : The Installer has encountered an unexpected error. The Error code is 2819 .ControlProductclash1 on dialog Product clash needs a property linked to it .

The solution is to use the commandline setup ..we tried several combinations untill this was successful :

start /wait setup.exe /qb VS=TSQL31 INSTANCENAME=TSQL31 ADDLOCAL=SQL_Replication

Now , Replication is installed but its on RTM :) ...you still need to apply SP2 ..
Moreover , any CU after SP2 will fail as Replication bits are on RTM ...

use the same command with hotfix name instead of setup.exe

Can we detach the databses with Replication ON ..

2008-06-12T20:20:00.000+05:30

The answer is no ........we cannot detach the database which is participating in replicaion ..

Consider a scenario where you have hundereds of databases replicated and you fall short of disk :D ...

Disabling/removing Replication will be costly and time consuming ...
But we can follow the simple steps below and everything will be fine ...

1.Run ALTER DATABASE database_name SET OFFLINE.
2.Move the file to the new location.
3.Run ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path/os_file_name'.
4. Run ALTER DATABASE database_name SET ONLINE.

Trust me ...you are done :) ..

Comments are welcome as always ..

Understanding Memory issues through Perfmon:Page faults

2008-02-17T03:08:00.000+05:30

I have been using Perfmon almost daily, but never took pain to learn how to use it efficiently until I came across a situation …In this part we will discuss using perfmon to see memory bottleneck.
Before we proceed forward let us go through few definitions so that the rest of the stuff is easily understood.

Virtual memory: Its actually virtual …and from here the concept of paging comes in to picture .On a 32 bit machine its 2 gigs for user process by default ….So, if your RAM is 512 MB, the remaining stuff (I mean greater that 512 MB) that’s going to come from Virtual memory will page out to disk .SO operations worth 2 gigs will happen in VAS (VM) and as a storage RAM will be used and the moment RAM limit is exceeded …..Paging/Swapping .

Page: Committed data in VAS is written to Page tables .From there it goes to RAM or Pagefile .This data is written in to pages (in bytes) and its size is processor dependent. this page is fitted in to page frame in RAM…it is essential that , that frame must be filled with zeros before a page from disk is inserted into page frame .zeroing is essential as the page before zeroing may have information from any other process ..So when we restart Windows this zeroing activity takes place and complete by the virtual memory manager before the Windows comes up..And if there is some problem during zeroing then………….. :D …we will discuss some other time …

Page file: a space in the HDD to be used to save committed memory objects.

Reserved memory: the memory quota given to a process by the VM manager .So the process first has to consume this before the next quota is allocated …

Committed memory: The chunk from reserved memory that is actually being used by the process. Remember that whenever memory is committed the same amount of space is created on the pagefile so that if needed it can be flushed to disk.

Working Set: Physical memory assigned to a process by VM manager (or say OS).It will be always less than Private Bytes.

Page Fault : When the page needed is not found in the working set of the process but its with in the RAM (may be in some cache) its called as Soft page fault .But when the page is not at all found in the RAM , its paged out and called as Hard page fault.

So now, it’s sure that whenever there is a memory issue there will be high (hard) Page fault rate...If there is a constant high hard page fault rate it means that there is some data not found in the RAM for the working set of that process .So , it has to be fetched from the disk (which is going to be costly).And the hard page faults cause Paging .So is paging harmful ….No (we cannot avoid it ), but the excess of it is a signal that something is going wrong .Generally I have seen that keeping the page file to be equal to 1.5 times of RAM is ok ..Not bad.

In case of memory issue the counters that we need to see in Perfmon are Memory and Process...

The Sub counters for memory counter are: Page Faults/sec, Page Reads/sec, Page Writes/sec, Pages Input/sec, Pages Output/sec, Available bytes and nonpaged pool bytes.

The Sub counters for Process counter are: Page Faults/sec, working set, Private Bytes and Page File Bytes

So, % Soft page faults = ((Page fault/sec - Pages input/sec) x 100)/Page faults/sec

Now, if there are more hard page faults, there should be more pages output/sec (no of pages sent to disk to make room for the pages coming form disk due to hard page fault) since there is a limited working set and hence there has to be some space for the pages coming from the disk (hard page faults) ...Pages read/sec and pages input/sec should be almost similar but can be a bit different …So these counters can tell you the system story..One more counter that I forgot to mention is Available Bytes .Its the no. of bytes left for the process to allocate. To see how much SQL Server shares you can see Page Faults/sec and proportionate it with Total Page faults. Generally, hard page fault of 10% to 15% should be tolerated …Before, we totally blame memory let’s check the Disk performance as well...

Subtract Memory:Page reads/sec from logical disk:disk reads/sec .If the output is small (+ or -) then the memory is the bottleneck because this actually means that all the disk reading is done to find the faulting pages .Also if disk output/sec is high then you can check by finding the difference of disk output/sec and Disk write bytes/sec ÷ 4096 ßintel and fine its percentage in total Disk write bytes/sec

Similarly, you can then correlate it to SQL Server as well …..Once you are sure that memory is the bottleneck then we can proceed with the troubleshooting steps...
Next time we will try to dig into memory issues including memory leaks and also perfmon counters specific to SQL Server …

As always, your comments are welcome...

Shall We use /PAE and /3GB switches together ..my views

2008-02-13T06:30:00.000+05:30

Physical Address Extension (PAE) refers to a feature of x86 and x86-64 processors that allows the OS to see more than regular 4 gigs of physical RAM on a 32-bit machine.To do that we need to specify /PAE switch in the boot.ini file and reboot the machine so that this setting takes effect.

So when you enable PAE ,the physical address size is increased from 32 bits to 36 bits which is equal to 64 GB. Note that the 32-bit size of virtual address is never changed, and it remains 4 GB.The OS uses PTE ( Page Table Entries) to map this 4 GB address space onto the 64 GB of total memory.Now any APP which needs access to more than 4 GiB of memory,has to have some special mechanism and on MS Windows this mechanism is called Address Windowing Extensions (AWE), while on Unix-like systems a variety of tricks are used, such as using mmap() to map regions of a file into and out of the address space as neededbut not yet adopted as a standard.

AWE is an MS API that allows the apps to access more physical memory than available in VAS ..The process through which it maps the VAS to physical memory is called as Windowing ...Not all the applications are AWE enabled ..but SQL Server is ..However , for doing that we need to have the locked pages in memory privilege enabled .One more benefit of using AWE in SQL Server is that the memory being allocated using AWE will not be written in Page files ..So,AWE can prevent Windows from swapping sensitive data from RAM to pagefile.

Now , I am pretty sure that we all know /3GB setting but just wanted to convey that with this setting the physical memory limit on Windows Server 2003 is 16 GB only ..So even if you have more than 16GB RAM you cannot see/use it :D...thats because it trims the PTE table rows ..

Now comes the real question ....Should we use /PAE and /3GB switch together .....well one negative factor i have already mentioned above. Secondly just for 1 GB I dont feel it would be worth .....Its set on my machine and i have no issues :)..

However , I have seen a few cases where these 2 combinations creates situations like Blue screen or black screen on machine upon boot up .....that is something because the OS goes for a toss while going through ZEROING activity ..But i think this stuff still needs some good research ...So, I do not say dont use it , but surely test it first .

So , summary is that I am not totally against it but i wont prefer it ....
Your choice :D .......

What all objects are consuming memory (and how much)

2008-02-12T06:55:00.000+05:30

To determine what plans are in the cache and how often they're used we can use sys.dm_os_memory_cache_counters dm view .

SELECT TOP 6 LEFT([name], 20) as [name], LEFT([type], 20) as [type], [single_pages_kb] + [multi_pages_kb] AS cache_kb, [entries_count]FROM sys.dm_os_memory_cache_counters order by single_pages_kb + multi_pages_kb DESC

here :
CACHESTORE_OBJCP are compiled plans for stored procedures, functions and triggers. CACHESTORE_SQLCP are cached SQL statements or batches that aren't in stored procedures, functions and triggers. This includes any dynamic SQL or raw SELECT statements sent to the server. CACHESTORE_PHDR These are algebrizer trees for views, constraints and defaults. An algebrizer tree is the parsed SQL text that resolves the table and column names.
(you will find these counters in DBCC Memorystatus as well.Infact DBCC Memory Status uses this dm)

Generally you will find that CACHESTORE_SQLCP > CACHESTORE_OBJCP , but if the ratio of one to another is very high then we can say that there are more adhoc plans being run then Stored procedures.That is the reason the sal statements are going in to Plan cache.
You can also monitor the number of data pages in the plan cache using Performance Monitor (PerfMon) using SQLServer:Plan Cache object with the Cache Pages counter. There are instances for SQL Plans (CACHESTORE_SQLCP), Object Plans (CACHESTORE_OBJCP) and Bound Trees (CACHESTORE_PHDR). This will give you the same picture ..for e.g. under bound tree : multiply cache pages by 8. you will get the same output as in dbcc memorystatus and the dm we used above.

After this we can use sys.dm_exec_cached_plans and sys.dm_exec_sql_text dm views to find the queries :
select TOP 100 objtype, usecounts, p.size_in_bytes/1024 'IN KB', LEFT([sql].[text], 100) as [text]from sys.dm_exec_cached_plans pouter apply sys.dm_exec_sql_text (p.plan_handle) sqlORDER BY usecounts DESC

And then we can check the query plans(and size) for the one we have some doubt .

Now , SQL Server memory is primarily used to store data (buffer) and query plans (cache). We will try to find what tables and indexes are in the buffer memory of your server you can use sys.dm_os_buffer_descriptors DMV.Further , the query below can give us total currrent size of buffer pool .

select count(*) AS Buffered_Page_Count ,count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MBfrom sys.dm_os_buffer_descriptors

After we have found the Bufferpool size , we can see which database is using more memory by runnig the query below :

SELECT LEFT(CASE database_id WHEN 32767 THEN 'ResourceDb' ELSE db_name(database_id) END, 20) AS Database_Name, count(*)AS Buffered_Page_Count, count(*) * 8192 / (1024 * 1024) as Buffer_Pool_MBFROM sys.dm_os_buffer_descriptorsGROUP BY db_name(database_id) ,database_idORDER BY Buffered_Page_Count DESC

And then we can go further at object level to see what all objects are consuming memory (and how much) .We can use the query below in each database we wish to :

SELECT TOP 25 obj.[name], i.[name], i.[type_desc], count(*)AS Buffered_Page_Count , count(*) * 8192 / (1024 * 1024) as Buffer_MB -- ,obj.name ,obj.index_id, i.[name]FROM sys.dm_os_buffer_descriptors AS bd INNER JOIN ( SELECT object_name(object_id) AS name ,index_id ,allocation_unit_id, object_id FROM sys.allocation_units AS au INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id AND (au.type = 1 OR au.type = 3) UNION ALL SELECT object_name(object_id) AS name ,index_id, allocation_unit_id, object_id FROM sys.allocation_units AS au INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id AND au.type = 2 ) AS obj ON bd.allocation_unit_id = obj.allocation_unit_idLEFT JOIN sys.indexes i on i.object_id = obj.object_id AND i.index_id = obj.index_idWHERE database_id = db_id()GROUP BY obj.name, obj.index_id , i.[name],i.[type_desc]ORDER BY Buffered_Page_Count DESC

Auto Parameterization in SQL Server

2008-02-12T06:14:00.000+05:30

Let us look at how and when SQL Server can parameterize dynamic SQL to reuse the query plan.Its through Parsing and compiling that the query plans get in to memory and then may or may not be reused.

Before executing a batch of SQL statements, SQL Server creates an execution plan (or query plan) for those statements. This is also referred to as compiling the SQL statements. One of the benefits of stored procedures is that SQL Serve will reuse compiled query plans.

Here is the example :
DBCC FREEPROCCACHEGO
set showplan_all on
SELECT *FROM Sales.SalesOrderHeaderWHERE SalesOrderID = 56000GO
select stats.execution_count AS exec_count, p.size_in_bytes as [size], [sql].[text] as [plan_text]from sys.dm_exec_cached_plans pouter apply sys.dm_exec_sql_text (p.plan_handle) sqljoin sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handleGO

Using the above mentioned DMV's we can find the number of times this plan has been executed, how big the plan is in bytes and the text of the plan.

Notice that the integer 56000 has been replaced by a variable ([@1])in the text of the query plan. Also notice that the list of parameters (only one parameter in this case) has been inserted at the beginning of the query plan. This is called "Simple Parameterization" (It was called "Auto Parameterization" in SQL Server 2000). If we run it twice we'd see the execution count increase to two but there would still only be a single plan.
SQL Server can also parameterize queries if they aren't identical.

lets check this :

DBCC FREEPROCCACHEGO
SELECT *FROM Sales.SalesOrderHeaderWHERE SalesOrderID = 56000GO
SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderID = 56001GO
select *from Sales.SalesOrderHeaderwhere SalesOrderID = 56002GO
select stats.execution_count AS exec_count, p.size_in_bytes as [size], [sql].[text] as [plan_text]from sys.dm_exec_cached_plans pouter apply sys.dm_exec_sql_text (p.plan_handle) sqljoin sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handleGO

Now let's look at something different.

DBCC FREEPROCCACHEGO
SELECT *FROM Sales.SalesOrderHeaderWHERE SalesOrderID = 56000GO
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader WHERE SalesOrderID = 56001GO
declare @i intset @i = 56004SELECT *FROM Sales.SalesOrderHeaderWHERE SalesOrderID = @iGO
select stats.execution_count AS exec_count, p.size_in_bytes as [size], [sql].[text] as [plan_text]from sys.dm_exec_cached_plans pouter apply sys.dm_exec_sql_text (p.plan_handle) sqljoin sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handleGO

So,even slight changes in the text of the plan prevent SQL Server from reusing the query plans.If you want SQL Server to parameterize your SQL statements you have three options: stored procedures, sp_executesql or Forced Parameterization. Stored procedures almost always have a query plan created and reused.
Lets try without sp_executesql first
---------------------------------------------
DBCC FREEPROCCACHEGO
SELECT SUM(LineTotal) AS LineTotalFROM Sales.SalesOrderHeader HJOIN Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderIDWHERE H.SalesOrderID = 56000GO
SELECT SUM(LineTotal) AS LineTotalFROM Sales.SalesOrderHeader HJOIN Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderIDWHERE H.SalesOrderID = 56001GO
select stats.execution_count AS exec_count, p.size_in_bytes as [size], LEFT([sql].[text], 80) as [plan_text]from sys.dm_exec_cached_plans pouter apply sys.dm_exec_sql_text (p.plan_handle) sqljoin sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handleGO

Now we will use sp_executesql
------------------------------------------
Now we will use sp_executesql .
DBCC FREEPROCCACHEGO
EXEC sp_executesql N'SELECT SUM(LineTotal) AS LineTotalFROM Sales.SalesOrderHeader HJOIN Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderIDWHERE H.SalesOrderID = @SalesOrderID', N'@SalesOrderID INT', 56000GO
EXEC sp_executesql N'SELECT SUM(LineTotal) AS LineTotalFROM Sales.SalesOrderHeader HJOIN Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderIDWHERE H.SalesOrderID = @SalesOrderID', N'@SalesOrderID INT', 56005GO
select stats.execution_count AS exec_count, LEFT([sql].[text], 80) as [plan_text]from sys.dm_exec_cached_plans pouter apply sys.dm_exec_sql_text (p.plan_handle) sqljoin sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handleGO
See the difference :D
You can set the parameterization at databse level by :
ALTER DATABASE adventureworks set PARAMETERIZATION SIMPLE.

Awaiting your comments :)