tag:blogger.com,1999:blog-84366125316364286472024-02-07T09:02:17.827+05:30Yukon-Katmai DiscussionUnknownnoreply@blogger.comBlogger80125tag:blogger.com,1999:blog-8436612531636428647.post-91956695507631781382012-01-18T10:32:00.002+05:302012-01-18T10:45:22.541+05:30Replication : Latency but no Latency<div dir="ltr" style="text-align: left;" trbidi="on">
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.<br />
<br />
Mostly, issues related to latency are due to :<br />
<br />
1: Blocking on DIST/PUB/SUB<br />
2: High Resource consumption : CPU,Storage,Memory<br />
3: Network issues<br />
4: Huge size of Distribution database (msrepl_transactions and msrepl_comands)<br />
5: Transaction log of publisher is huge (too many VLFs) causing log reader latency .<br />
<br />
-I am sharing this one with you where actually there was no latency due to any of these as I<br />
mentioned above.<br />
-There was no blocking<br />
-CPU,memory and Diskes were doing good .<br />
-There was no network issue<br />
-We shrunk the publisher database which did not help us at all .<br />
-Cleanup job was running fine .We ran update stats with full scan on msrepl_transactions and<br />
msrepl_comands which did not help either .<br />
<br />
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 .<br />
<br />
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 .<br />
<br />
We were totally clue less because :<br />
- 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 .<br />
<br />
- 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 .<br />
<br />
-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 .<br />
<br />
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 .<br />
<br />
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 .<br />
<br />
<b>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 .</b> We found that -CommitBatchSize (default Value 100) and -CommitBatchThreshold(default Value 1000) values were changed to 10 each .<br />
<br />
We then changed it back to the default and recycled the Distribution Agent .Thats it , the story ends here .<br />
Due to this the rate at which the transactions were delivered at 1 cmd/transaction .<br />
<br />
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 ..<br />
<br />
Cheers and Happy Learning ..</div>Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-8436612531636428647.post-58444246786630286812012-01-14T13:29:00.001+05:302012-01-14T13:29:34.047+05:30Can restoring a database to another instance reduce Index fragmentation of underlying tables ?<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Verdana, sans-serif;">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 ..</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span><br />
<span style="font-family: Verdana, sans-serif;">I thought of 3 reasons :</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span><br />
<span style="font-family: Verdana, sans-serif;">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 .</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span><br />
<span style="font-family: Verdana, sans-serif;">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 .</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span><br />
<span style="font-family: Verdana, sans-serif;">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 .</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span><br />
<span style="font-family: Verdana, sans-serif;">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.</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span><br />
<span style="font-family: Verdana, sans-serif;">Now , I was left with option 3 .I had the table with > 99% fragmentation and 24085822 rows</span><span style="font-family: Verdana, sans-serif;"> .The table size was around 4GB . DBCC Showcontig output is shared below :</span><br />
<span style="font-family: Verdana, sans-serif;"><br /></span><br />
<br />
<span style="font-family: Verdana, sans-serif;">DBCC SHOWCONTIG scanning 'stats_test' table...</span><br />
<span style="font-family: Verdana, sans-serif;">Table: 'stats_test' (711673583); index ID: 1, database ID: 6</span><br />
<span style="font-family: Verdana, sans-serif;">TABLE level scan performed.</span><br />
<span style="font-family: Verdana, sans-serif;">- Pages Scanned................................: <b>268007</b></span><br />
<span style="font-family: Verdana, sans-serif;">- Extents Scanned..............................: <b>33569</b></span><br />
<span style="font-family: Verdana, sans-serif;">- Extent Switches..............................: <b>268005</b></span><br />
<span style="font-family: Verdana, sans-serif;">- Avg. Pages per Extent........................: 8.0</span><br />
<span style="font-family: Verdana, sans-serif;">- Scan Density [Best Count:Actual Count].......: 12.50% [33501:268006]</span><br />
<span style="font-family: Verdana, sans-serif;">- Logical Scan Fragmentation ..................: 99.22%</span><br />
<span style="font-family: Verdana, sans-serif;">- Extent Scan Fragmentation ...................: 0.01%</span><br />
<span style="font-family: Verdana, sans-serif;">- Avg. Bytes Free per Page.....................: 3334.3</span><br />
<span style="font-family: Verdana, sans-serif;">- Avg. Page Density (full).....................: 58.81%</span><br />
<span style="font-family: Verdana, sans-serif;">DBCC execution completed. If DBCC printed error messages, contact your system administrator.</span><br />
<div style="font-family: Verdana, sans-serif;">
<br /></div>
<div style="font-family: Verdana, sans-serif;">
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 .</div>
<div style="font-family: Verdana, sans-serif;">
<br /></div>
<div style="font-family: Verdana, sans-serif;">
Now let me restore the backup .....fingers crossed :) ....</div>
<div style="font-family: Verdana, sans-serif;">
<br /></div>
<div>
<div>
<span style="font-family: Verdana, sans-serif;">DBCC SHOWCONTIG scanning 'stats_test' table...</span></div>
<div>
<span style="font-family: Verdana, sans-serif;">Table: 'stats_test' (711673583); index ID: 1, database ID: 6</span></div>
<div>
<span style="font-family: Verdana, sans-serif;">TABLE level scan performed.</span></div>
<div>
<span style="font-family: Verdana, sans-serif;">- Pages Scanned................................: 158633</span></div>
<div>
<span style="font-family: Verdana, sans-serif;">- Extents Scanned..............................: 19862</span></div>
<div>
<span style="font-family: Verdana, sans-serif;">- Extent Switches..............................: 19866</span></div>
<div>
<span style="font-family: Verdana, sans-serif;">- Avg. Pages per Extent........................: 8.0</span></div>
<div>
<span style="font-family: Verdana, sans-serif;">- Scan Density [Best Count:Actual Count].......: 99.81% [19830:19867]</span></div>
<div>
<span style="font-family: Verdana, sans-serif;">- Logical Scan Fragmentation ..................: 0.15%</span></div>
<div>
<span style="font-family: Verdana, sans-serif;">- Extent Scan Fragmentation ...................: 3.47%</span></div>
<div>
<span style="font-family: Verdana, sans-serif;">- Avg. Bytes Free per Page.....................: 51.2</span></div>
<div>
<span style="font-family: Verdana, sans-serif;">- Avg. Page Density (full).....................: 99.37%</span></div>
<div>
<span style="font-family: Verdana, sans-serif;">DBCC execution completed. If DBCC printed error messages, contact your system administrator.</span></div>
<div style="font-family: Verdana, sans-serif;">
<br /></div>
</div>
<div style="font-family: Verdana, sans-serif;">
Mystery solved ....</div>
<div style="font-family: Verdana, sans-serif;">
<br /></div>
<div style="font-family: Verdana, sans-serif;">
Happy learning ..</div>
</div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-8436612531636428647.post-50174994888192290912012-01-13T12:25:00.000+05:302012-01-25T23:39:18.713+05:30Replicating SP execution : Issue in SQL Server 2005 SP3 CU2 .Works fine in SQL 2008 SP1<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: 'Trebuchet MS', sans-serif;">posting after a long time gap and might still have not posted until yesterday when I got to know how smart Replication is .</span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;"><br /></span><br />
<b><span style="font-family: 'Trebuchet MS', sans-serif;">Brief Summary :</span></b><br />
<span style="font-family: 'Trebuchet MS', sans-serif;">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.</span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;"><br /></span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;">So to reduce latency ,we started thinking of <b>replicating the execution of Stored Proc</b> 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.</span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;"><br /></span><br />
<b><span style="font-family: 'Trebuchet MS', sans-serif;">Issue that we thought we might face :</span></b><br />
<span style="font-family: 'Trebuchet MS', sans-serif;">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.</span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;"><br /></span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;">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 .</span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;"><br /></span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;">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 .</span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;"><br /></span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;">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 <b>msrepl_commands</b> and <b>merepl_transactions</b> 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 .</span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;"><br /></span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;">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 :</span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;"><br /></span><br />
<br />
<b><span style="font-family: 'Trebuchet MS', sans-serif;">Test 1:</span></b><br />
<span style="font-family: 'Trebuchet MS', sans-serif;">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)</span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;"><br /></span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;"><b>SQL Server verion :</b> </span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;">Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 Evaluation Edition on Windows XP SP3</span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;"><br /></span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;"><b>Publisher database :</b> Adventureworks</span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>|_<b>Table :</b>dbo.Stats_State</span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>|_<b>SP:</b>dbo.del_stats_scan</span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>|_<b>Publication :</b>ADV_Table</span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>|<span class="Apple-tab-span" style="white-space: pre;"> </span>|_<b>Article :</b>dbo.Stats_State</span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>|_<b>Publication:</b>ADV_SP</span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>|_<b>Article :</b>dbo.del_stats_scan</span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;"><br /></span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;"><b>Subscriber Database :</b> ADV_SUB</span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>|_<b>Table :</b>dbo.Stats_State</span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;"><span class="Apple-tab-span" style="white-space: pre;"> </span>|_<b>SP:</b>dbo.del_stats_scan </span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;"><br /></span><br />
<b><span style="font-family: 'Trebuchet MS', sans-serif;">Jobs :</span></b><br />
<span style="font-family: 'Trebuchet MS', sans-serif;">Log Reader Agents <span class="Apple-tab-span" style="white-space: pre;"> </span>:1</span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;">Snapshot Agent <span class="Apple-tab-span" style="white-space: pre;"> </span>:2</span><br />
<span style="font-family: 'Trebuchet MS', sans-serif;">Distribution Agent <span class="Apple-tab-span" style="white-space: pre;"> </span>:2 </span><br />
<div>
<span style="font-family: 'Trebuchet MS', sans-serif;"><br /></span></div>
<div>
<b><span style="font-family: 'Trebuchet MS', sans-serif;">Replication is in synchronization :</span></b></div>
<div>
<b><span style="font-family: 'Trebuchet MS', sans-serif;">Queries fired on publisher DB :</span></b></div>
<div style="text-align: left;">
<div style="color: black; font-weight: bold;">
<span style="font-family: 'Trebuchet MS', sans-serif;">Query 1</span></div>
<div style="color: black;">
<span style="font-family: 'Trebuchet MS', sans-serif;">delete top (10) from dbo.stats_test</span></div>
<div style="color: black;">
<div>
<span style="font-family: 'Trebuchet MS', sans-serif;"><b>Results :</b></span></div>
<div>
<span style="font-family: 'Trebuchet MS', sans-serif;">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 .</span></div>
<div>
<span style="font-family: 'Trebuchet MS', sans-serif;"><br /></span></div>
<div>
<span style="font-family: 'Trebuchet MS', sans-serif;">Fire these queries to find out what is being replicated :</span></div>
<div>
<span style="font-family: 'Trebuchet MS', sans-serif;">select * from distribution.dbo.MSrepl_commands</span></div>
<div>
<span style="font-family: 'Trebuchet MS', sans-serif;">select * from distribution.dbo.MSrepl_transactions</span></div>
</div>
<div style="color: black;">
<span style="font-family: 'Trebuchet MS', sans-serif;"><br /></span></div>
<div>
<span style="font-family: 'Trebuchet MS', sans-serif;"></span><br />
<div>
<span style="font-family: 'Trebuchet MS', sans-serif;"><b>Query 2 : </b></span></div>
<span style="font-family: 'Trebuchet MS', sans-serif;">
</span><br />
<div>
<span style="font-family: 'Trebuchet MS', sans-serif;">exec del_stats_scan</span></div>
<span style="font-family: 'Trebuchet MS', sans-serif;">
</span><br />
<div>
<span style="font-family: 'Trebuchet MS', sans-serif;"><b>Results :</b>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 .</span></div>
<span style="font-family: 'Trebuchet MS', sans-serif;">
</span><br />
<div>
<span style="font-family: 'Trebuchet MS', sans-serif;"><br /></span></div>
<span style="font-family: 'Trebuchet MS', sans-serif;">
<div>
Run these queries to find out what has been replicated</div>
<div>
select COUNT(*) 'No. of rows in Repl_cmds' from distribution.dbo.MSrepl_commands</div>
<div>
select COUNT(*) 'No. of rows in Repl_Trans'from distribution.dbo.MSrepl_transactions</div>
<div>
<br /></div>
<div>
Test 2:</div>
<div>
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).</div>
<div>
Stored Procedure Del_stats_scan, deletes top 10 rows from table dbo.Stats_State in the Publisher database (Adventureworks).</div>
<div>
<br /></div>
<div>
<b>SQL Server verion :</b> </div>
<div>
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 Evaluation Edition on Windows XP SP3</div>
<div>
<b>Publisher database :</b> </div>
<div>
Adventureworks</div>
<div>
<span class="Apple-tab-span" style="white-space: pre;"> </span>|_Table :dbo.Stats_State</div>
<div>
<span class="Apple-tab-span" style="white-space: pre;"> </span>|_SP:dbo.del_stats_scan</div>
<div>
<span class="Apple-tab-span" style="white-space: pre;"> </span>|_Publication :ADV_Table</div>
<div>
<span class="Apple-tab-span" style="white-space: pre;"> </span>|_Article :dbo.Stats_State</div>
<div>
<span class="Apple-tab-span" style="white-space: pre;"> </span>|_Article :dbo.del_stats_scan</div>
<div>
<br /></div>
<div>
Subscriber Database : ADV_SUB</div>
<div>
<span class="Apple-tab-span" style="white-space: pre;"> </span>|_Table :dbo.Stats_State</div>
<div>
<span class="Apple-tab-span" style="white-space: pre;"> </span>|_SP:dbo.del_stats_scan </div>
<div>
<br /></div>
<div>
Jobs :</div>
<div>
Log Reader Agents <span class="Apple-tab-span" style="white-space: pre;"> </span>:1</div>
<div>
Snapshot Agent <span class="Apple-tab-span" style="white-space: pre;"> </span>:1</div>
<div>
Distribution Agent <span class="Apple-tab-span" style="white-space: pre;"> </span>:1 </div>
<div>
<br /></div>
<div>
Repeat Test 1 and 2 and see the results .</div>
<div>
<br /></div>
<div>
<b>Conclusion :</b></div>
<div>
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.</div>
<div>
<br /></div>
<div>
Happy Learning .</div>
</span></div>
</div>
<br /></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-8436612531636428647.post-84119404592674835122011-07-22T13:49:00.000+05:302012-01-15T20:08:22.459+05:30What does resourcedb contain ?<div dir="ltr" style="text-align: left;" trbidi="on">
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 ?<br />
<br />
Normally we can't see it and hence can't use it .However, there is a way you can use resource database .<b>But be careful. If you mess up anything you might end up paying a heavy cost.</b><br />
<br />
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 .<br />
<br />
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 :<br />
<br />
1) Through services console (after adding <b>-m</b> do not click on <b>OK</b> but click on <b>start</b> )<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSnH0HcQcfJqK9kViGav2HdU06nFh-BJKQKMalnuARdTmh3ETbxFV70cmsuWPCBLgL-F8RMVnpbwsBTiOFWoQeKijuFleCqLF0z9rVonaeVZBSMDqmoHnR_faPWUvlhfVQA8EfE45zepW3/s1600/untitled.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSnH0HcQcfJqK9kViGav2HdU06nFh-BJKQKMalnuARdTmh3ETbxFV70cmsuWPCBLgL-F8RMVnpbwsBTiOFWoQeKijuFleCqLF0z9rVonaeVZBSMDqmoHnR_faPWUvlhfVQA8EfE45zepW3/s400/untitled.JPG" width="350" /></a></div>
<br />
2) Through DOS prompt<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYc9wXkPQ7b1MDBf4R_4kI1DDic0ExJiuwMgbxg645HqWhZzxFV_55XBLW7b7xCtlPRsInRUaBpi1mXWGalwMRLZC5qg1e8UqOaf7xo9qHbQ9jI5rvWyX4jkOn0-gzXnSiOSP5qlb7EIWc/s1600/untitled1.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="66" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYc9wXkPQ7b1MDBf4R_4kI1DDic0ExJiuwMgbxg645HqWhZzxFV_55XBLW7b7xCtlPRsInRUaBpi1mXWGalwMRLZC5qg1e8UqOaf7xo9qHbQ9jI5rvWyX4jkOn0-gzXnSiOSP5qlb7EIWc/s400/untitled1.JPG" width="400" /></a></div>
<br />
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 <b>remote admin connection</b> option via <b>sp_configure</b> (you can see the run value of 1):<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgm8_vxM9ldxF_wsN6I09PCiq20JnofNOjrl7xvZqMMBHE2TQEBqxG20krJwheanTzXgf_J3i6CEiFmGrgOmZqW-zQyN0k16oI9HtSiOrMXdyFPFTUBTFnQwz2dsr7bYZ9gObiKw2N5w0zb/s1600/untitled2.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="104" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgm8_vxM9ldxF_wsN6I09PCiq20JnofNOjrl7xvZqMMBHE2TQEBqxG20krJwheanTzXgf_J3i6CEiFmGrgOmZqW-zQyN0k16oI9HtSiOrMXdyFPFTUBTFnQwz2dsr7bYZ9gObiKw2N5w0zb/s400/untitled2.JPG" width="400" /></a></div>
<br />
1) connecting to SQL Server with DAC (using SQLCMD) using -A option<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjX0nj-q5x362E2QvWkt1TNI8ygkCevJef8_G5Q_icqKb98xC0RcTG1BcE1rEs4VmwvM8mCpCNdD4WSc5TQs4OsycnGqoUqU9RGzud9O1bL91PnNRnMNCwkG4VtPVgzb9GItP-MJ1-5hxy0/s1600/untitled3.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="59" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjX0nj-q5x362E2QvWkt1TNI8ygkCevJef8_G5Q_icqKb98xC0RcTG1BcE1rEs4VmwvM8mCpCNdD4WSc5TQs4OsycnGqoUqU9RGzud9O1bL91PnNRnMNCwkG4VtPVgzb9GItP-MJ1-5hxy0/s400/untitled3.JPG" width="400" /></a></div>
<br />
2)Connecting via MGT Studio<br />
Open Management studio , It will prompt you to enter the instance name . Just before the instance name add <b>admin</b>: <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifVe9rXyeQkDweGxu8APzKrDryOfeNBL_yfPiJCB_59Tv6tkzTgSKEKtd-i5RbZMYPysF0catyaC7GrjdW8KfZlQ5nnR0UzsJXzOt20zIajKE5Qqu4gG5CFQyhj7Gu0p4fY11mYcvBKD0X/s1600/untitled4.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="303" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifVe9rXyeQkDweGxu8APzKrDryOfeNBL_yfPiJCB_59Tv6tkzTgSKEKtd-i5RbZMYPysF0catyaC7GrjdW8KfZlQ5nnR0UzsJXzOt20zIajKE5Qqu4gG5CFQyhj7Gu0p4fY11mYcvBKD0X/s400/untitled4.JPG" width="400" /></a></div>
<br />
you might or might not get this error :<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEht0DsfCNR8_46sfGkxSjuekYRQG3US0pY5WI2LBM2711NkJ9EYsgmdC6oAdSm2rCC67URPnxGh4hY_Nn97e6wvYH53W3u1l8_ilQVr18zuoB9i6xD6HdJO2V4bgA4iNpW2cBkJjtZ_pFHS/s1600/untitled5.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="102" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEht0DsfCNR8_46sfGkxSjuekYRQG3US0pY5WI2LBM2711NkJ9EYsgmdC6oAdSm2rCC67URPnxGh4hY_Nn97e6wvYH53W3u1l8_ilQVr18zuoB9i6xD6HdJO2V4bgA4iNpW2cBkJjtZ_pFHS/s400/untitled5.JPG" width="400" /></a></div>
<br />
If you get this error then click OK ( this error window will go away ) and then instead of <b>Connect</b> click on <b>cancel </b>.<br />
you will see a clean screen like below :<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg6KK-nfNDVF-Ftuc-YtxpKjBY2jzFHP03hwu-ZFy9RijEkYnesJ-P6ER6q3js-VSbrrfobqtUYPouAfDQ6Vjulb47XqPfgV5sNkJuz803VWKguzT_LET9u_cjY3B5an5KKFdNnlKbcivgA/s1600/untitled6.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="231" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg6KK-nfNDVF-Ftuc-YtxpKjBY2jzFHP03hwu-ZFy9RijEkYnesJ-P6ER6q3js-VSbrrfobqtUYPouAfDQ6Vjulb47XqPfgV5sNkJuz803VWKguzT_LET9u_cjY3B5an5KKFdNnlKbcivgA/s400/untitled6.JPG" width="400" /></a></div>
<br />
<br />
Click on new query <br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijVox-FpCoFjleUWzwmd-L4gRE8jeAifffEaFHCa9Xu5GUFpE_lgU2ewd1ARJ3h0FA2q8FCNAGWv2bMz_24FUhFnss48nY5Znxse14ZkrXFyDaZoLWmR_AHSPRwhFpe2YC9FiFUZvLXoH0/s1600/untitled7.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="26" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEijVox-FpCoFjleUWzwmd-L4gRE8jeAifffEaFHCa9Xu5GUFpE_lgU2ewd1ARJ3h0FA2q8FCNAGWv2bMz_24FUhFnss48nY5Znxse14ZkrXFyDaZoLWmR_AHSPRwhFpe2YC9FiFUZvLXoH0/s400/untitled7.JPG" width="338" /></a></div>
<br />
You will again see the same connection popup :<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGxDab6HeWqEm4Qi82_y1dgpeXjcc31cE_Kw2qDjRbjJEzfdPuPmXKjafUOC0jovgssGbqHk2ZmaQPVvbS6gFEdbn2TaQ5RzG3YQlZJcOA5ZR-Epl5N2dgaCy16z6EdecvbK0lji2BX4UI/s1600/untitled8.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="303" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGxDab6HeWqEm4Qi82_y1dgpeXjcc31cE_Kw2qDjRbjJEzfdPuPmXKjafUOC0jovgssGbqHk2ZmaQPVvbS6gFEdbn2TaQ5RzG3YQlZJcOA5ZR-Epl5N2dgaCy16z6EdecvbK0lji2BX4UI/s400/untitled8.JPG" width="400" /></a></div>
<br />
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 ...<br />
<br />
Run the query 'use mssqlsystemresource' and press F5 :) ...It will work :<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgYUVxE5fhR-LCe9n1sJQfdC252er6e-oNVgaVnsNfvFy_GddRNeOaSGAwj-i6NjkX6viKLZqzVH_hAoua2-VCMXL_-Aq_7s6gz37P5xHORY4RZuTKz_ajsxWE16GbpNyeSVe6GFQZN8v3k/s1600/untitled9.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="151" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgYUVxE5fhR-LCe9n1sJQfdC252er6e-oNVgaVnsNfvFy_GddRNeOaSGAwj-i6NjkX6viKLZqzVH_hAoua2-VCMXL_-Aq_7s6gz37P5xHORY4RZuTKz_ajsxWE16GbpNyeSVe6GFQZN8v3k/s400/untitled9.JPG" width="400" /></a></div>
<br />
Also if you query sys.sysdbreg sytem table (an alternative for sys.sysdatabases DMV )you will see the resource database :<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj1b6uOlaRBDBJEVkxW0SB8m-grYpdwb6y24mVM518HkFbFDjw0n7F7awnejMrzSdIfnmGK9qI9SWp1U4F1HrDcxTxb-LfOvyHPeR9FJXeth1GhjNJpHRGcX4Eq0sm7INlzwfKH2ZJzA9C3/s1600/untitled10.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="310" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj1b6uOlaRBDBJEVkxW0SB8m-grYpdwb6y24mVM518HkFbFDjw0n7F7awnejMrzSdIfnmGK9qI9SWp1U4F1HrDcxTxb-LfOvyHPeR9FJXeth1GhjNJpHRGcX4Eq0sm7INlzwfKH2ZJzA9C3/s400/untitled10.JPG" width="250" /></a></div>
<br />
This database is currently in read-only mode (trust me :-) ) . If you want to cross check this run <b>dbcc shrinkdatabase (mssqlsystemresource)</b> and you will get to know .<br />
You can set it to read_write mode though by running : <b>alter database mssqlsystemresource set read_write</b>.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 .<br />
<br />
At the end you might ask "<b>Whats the need to touch the system tables in the database ?</b>"<br />
<b>The answer is :</b> 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 ....<br />
<br />
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).<br />
Happy Learning !!</div>Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-8436612531636428647.post-53018416882616965722011-06-26T11:16:00.000+05:302011-06-26T11:16:02.340+05:30Replication :Archiving partitioned and non-Partitioned tables (Without removing the articles from the publications)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 .<br />
In this post we will see :<br />
<b><br />
Part 1) archiving the replicated non-partitioned (normal) tables .<br />
Part 2) archiving the replicated partitioned tables .<br />
</b><br />
At the end ,you will notice one nice to know feature of partitioned tables .<br />
<br />
<b>Part 1) Archiving the replicated non-partitioned (normal) tables .</b><br />
<b>Publisher :</b> DB2Migration<br />
<b>Subscriber :</b> DB2Migration_Sub<br />
<b>Replication Topology :</b> Transactional Replication <br />
<b>Articles :</b> dbo.Test<br />
<b>Other details :</b> Both tables have 10000 rows each after first synchronization.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEge3s9uhxQkoxMcmAFv3WXW7KpkQ4KMrKrrQ4YJQdnw9wxbjIjY3h_7PNxzMcZsUt5ek89EjQofh5rCjdRj_wkS29T2Ag4Ae-Krd4zeajIOxBmHBIJXzEWfrRQhlYHHrHbblQ4l5JPEiUin/s1600/untitled.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="181" width="382" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEge3s9uhxQkoxMcmAFv3WXW7KpkQ4KMrKrrQ4YJQdnw9wxbjIjY3h_7PNxzMcZsUt5ek89EjQofh5rCjdRj_wkS29T2Ag4Ae-Krd4zeajIOxBmHBIJXzEWfrRQhlYHHrHbblQ4l5JPEiUin/s400/untitled.JPG" /></a></div><br />
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 .<br />
<br />
How should we do it ?<br />
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 ...<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiESTCQ-uetQQTIfLmY2fV1cUzlG51XwlzV0FLgA9ql9a_IxPmnwBudKzk-E4Gn3v_EEk6r9jT62cR5rvQJ4inNMS2SyHWFN6k5BTHKuvtlH1fSTLO4v8jJ-QzPD7LvxBxpDYrIMIVtEXb9/s1600/untitled1.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="191" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiESTCQ-uetQQTIfLmY2fV1cUzlG51XwlzV0FLgA9ql9a_IxPmnwBudKzk-E4Gn3v_EEk6r9jT62cR5rvQJ4inNMS2SyHWFN6k5BTHKuvtlH1fSTLO4v8jJ-QzPD7LvxBxpDYrIMIVtEXb9/s400/untitled1.JPG" /></a></div><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhFxp5m686vxwfIeCkgTGGd3d9FweaEHOY8ktYWFTJyinjnqhAKeiVlTpQ-AIqfdzf1nHvrjh-jzyn58G2L-gNg9lKOkHDQQdD25meyg5XjCLeRWyZOMuqHaHvpGETUWt8Uf-ZQwo0g3TfU/s1600/untitled2.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="36" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhFxp5m686vxwfIeCkgTGGd3d9FweaEHOY8ktYWFTJyinjnqhAKeiVlTpQ-AIqfdzf1nHvrjh-jzyn58G2L-gNg9lKOkHDQQdD25meyg5XjCLeRWyZOMuqHaHvpGETUWt8Uf-ZQwo0g3TfU/s400/untitled2.JPG" /></a></div><br />
<b>DBCC opentran will show :</b><br />
No active open transactions.<br />
DBCC execution completed. If DBCC printed error messages, contact your system administrator.<br />
<br />
We will enable the LogReader agent now and will see that there are no transactions to be replicated.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiFE9nFp8G41P6XVeG6j1VpxSJVhHJeIR_bZqJq2wNHMWqchcY84plMg72B_065_WG_vEDP87MAvtoWc2P6RWM6y118MqLkMvDubD4FKZkciu-PMsf8TvDkfGiF78_jj6w9tHkdfl9nxRT8/s1600/untitled3.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="191" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiFE9nFp8G41P6XVeG6j1VpxSJVhHJeIR_bZqJq2wNHMWqchcY84plMg72B_065_WG_vEDP87MAvtoWc2P6RWM6y118MqLkMvDubD4FKZkciu-PMsf8TvDkfGiF78_jj6w9tHkdfl9nxRT8/s400/untitled3.JPG" /></a></div><br />
Select count * from both the tables will show 0( zero ) and 10000 rows respectively .<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiecM3dSuWq5k4RGTjuxY8Gj01WM9OPbD58sgMFIkdYQ3rgkCv3q8NTCCvk4lejhYKADAKWw1gGkf5fCx6V8Fwe6R5XdwXaqnt9AcSy3ZAAaZBhMiB5nu98STbN1aA9t32TEsrqsV-SfLke/s1600/untitled4.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="196" width="386" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiecM3dSuWq5k4RGTjuxY8Gj01WM9OPbD58sgMFIkdYQ3rgkCv3q8NTCCvk4lejhYKADAKWw1gGkf5fCx6V8Fwe6R5XdwXaqnt9AcSy3ZAAaZBhMiB5nu98STbN1aA9t32TEsrqsV-SfLke/s400/untitled4.JPG" /></a></div><br />
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 .<i>This is going to be costly when there will be millions of rows because delete (or update or insert )is a logged activity</i>.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 (<b>why ???</b>.....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 :<br />
<br />
*******TEST THIS BEFORE IMPLEMENTING IT IN PRODUCTION********<br />
1) Stop the log reader agent and distribution agent <br />
2) Drop the article(s) from the publication <br />
3) Archive the table to another table ( this will be a logged activity ) by Bulk insert or BCP or import export wizard <br />
4) Truncate the table 5) Add the article again <br />
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 <br />
6) generate the snapshot again ....<br />
7) start the log reader agent and distribution agent and initiate the new snapshot ....<br />
*******TEST THIS BEFORE IMPLEMENTING IT IN PRODUCTION********<br />
Trust me .you are done :) ..But don't you think its lengthy and a bit risky ..Now lets see something new ...<br />
<br />
<b>Part 2) Archiving the replicated partitioned tables .</b><br />
Let us first create 2 new databases <b>followed by</b> creating partition functions <b>followed by</b> partition schemes <b>followed by</b> partitioned tables <b>followed by</b> inserting data in the tables .<br />
<br />
<b><i>--creating database and filegroups</i></b><br />
create database test<br />
GO <br />
ALTER DATABASE test ADD FILEGROUP [second] <br />
GO <br />
ALTER DATABASE test ADD FILEGROUP [third] <br />
GO <br />
ALTER DATABASE test ADD FILEGROUP [forth] <br />
GO <br />
ALTER DATABASE test ADD FILEGROUP [fifth] <br />
GO<br />
<br />
<i><b>--Adding new files to the filegroups </b></i><br />
USE [master] <br />
GO <br />
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] <br />
GO<br />
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] <br />
GO <br />
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] <br />
GO <br />
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] <br />
GO <br />
<br />
<i><b>--The following partition function will partition a table or index into four partitions.</b></i><br />
USE test<br />
GO <br />
CREATE PARTITION FUNCTION [PF_test](int) AS RANGE LEFT FOR VALUES (1,100,1000) <br />
<br />
<i><b>-- Creating partition scheme </b></i><br />
use test <br />
GO <br />
IF NOT EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'PS_test') <br />
create PARTITION SCHEME [PS_test] AS PARTITION [PF_test] TO ([second],[third],[forth],[fifth]) <br />
<i>--[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]) ]<br />
</i><br />
<b><i>--creating table with constraint and assigning a partition scheme to it </i></b> <br />
create table test (dummy [int] primary key constraint test_c check ([dummy] > 0 and [dummy] <=20000)) on ps_test (dummy)
<i><b>--inserting values </b></i><br />
declare @val int<br />
set @val=1000<br />
while (@val > 0)<br />
begin <br />
insert into test..test values (@val)<br />
set @val=@val-1<br />
end<br />
<br />
On Subscriber we will only create the same filegroups and add files to them :<br />
<i><b>--creating database and filegroups</b></i> <br />
create database test_sub<br />
GO <br />
ALTER DATABASE test_sub ADD FILEGROUP [second] <br />
GO <br />
ALTER DATABASE test_sub ADD FILEGROUP [third] <br />
GO <br />
ALTER DATABASE test_sub ADD FILEGROUP [forth] <br />
GO <br />
ALTER DATABASE test_sub ADD FILEGROUP [fifth] <br />
GO <br />
<br />
<i><b>--Adding new files to the filegroups</b></i> <br />
USE [master] <br />
GO <br />
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] <br />
GO<br />
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] <br />
GO <br />
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] <br />
GO <br />
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] <br />
GO <br />
<br />
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 :<br />
<br />
<i>select OBJECT_ID('test..test')<br />
select OBJECT_ID('test_sub..test')<br />
select * from test.sys.partitions where object_id in (2105058535) order by partition_number <br />
select * from test_sub.sys.partitions where object_id in (133575514) order by partition_number <br />
</i><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh1t4iBnxFCynL0A3vmWOmss3GJNoygeuJtZO0c1vndNJU9YaSFF8A4OuBpub5VvZG08TNhn-xsc8Ho0A-d6xfZMIxep3qjffK2REcKTHwFgDDwlyN7rcWKmkfFaIBhnC7PmvI2EDt_DKxH/s1600/untitled5.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="165" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh1t4iBnxFCynL0A3vmWOmss3GJNoygeuJtZO0c1vndNJU9YaSFF8A4OuBpub5VvZG08TNhn-xsc8Ho0A-d6xfZMIxep3qjffK2REcKTHwFgDDwlyN7rcWKmkfFaIBhnC7PmvI2EDt_DKxH/s400/untitled5.JPG" /></a></div><br />
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 <i><b>SWITCHING OF PARTITIONS</b></i> in the table .I will not explain what does it means because you will see it in few seconds(or you can refer BOL) .<br />
<br />
<b>--Lets first create the archive table on publisher .Its the replica of the original test table</b> <br />
create table test..test_archive (dummy [int] primary key constraint test_c_a check ([dummy] > 0 and [dummy] <=20000)) on ps_test (dummy)
<b>--Switching the partitions from test to test_archive table on publication</b><br />
ALTER TABLE test..test SWITCH PARTITION 1 TO test_archive Partition 1;<br />
GO<br />
<br />
Msg 21867, Level 16, State 1, Procedure sp_MStran_altertable, Line 259<br />
ALTER TABLE SWITCH statement failed. The table '[dbo].[test]' belongs to a publication which does not allow switching of partitions<br />
<br />
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 :<br />
<i>sp_changepublication 'test_pub' ,@property='allow_partition_switch',@value='true'</i><br />
<br />
You will get this message<br />
<i>The publication has updated successfully.</i><br />
<br />
<b>--Switching the partitions from test to test_archive table on publication. This is for partition number 1 . We have 4 partitions.</b><br />
ALTER TABLE test..test SWITCH PARTITION 1 TO test_archive Partition 1;<br />
GO<br />
ALTER TABLE test..test SWITCH PARTITION 2 TO test_archive Partition 2;<br />
GO<br />
ALTER TABLE test..test SWITCH PARTITION 3 TO test_archive Partition 3;<br />
GO<br />
ALTER TABLE test..test SWITCH PARTITION 4 TO test_archive Partition 4;<br />
GO<br />
<br />
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:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwRw5Kw3XsQT-cTwuAhFfTcf8iahw68AmUjjYf0bk3i3HtA21mYJGc3gXvW2lUaw2Q3Fvsl4m6YB-kvmqz5Hk7ktYnKlbTmF3XwNEjm2MjdIZweCLZQJSlyw1bjSGIp-QvkXkyd5Kw9rQo/s1600/untitled6.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="150" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwRw5Kw3XsQT-cTwuAhFfTcf8iahw68AmUjjYf0bk3i3HtA21mYJGc3gXvW2lUaw2Q3Fvsl4m6YB-kvmqz5Hk7ktYnKlbTmF3XwNEjm2MjdIZweCLZQJSlyw1bjSGIp-QvkXkyd5Kw9rQo/s400/untitled6.JPG" /></a></div>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 .<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqwX6OSwNIsueX46qoVQ5VnpL6TqVyqQM_fsXF7S71LVzfCe9Gbbo1gTGBlma_VTddpE1fXlH-CRVwIpzScMULmtAilJQwzWo-87Eu-N769N7S9hnnyE5bd0BYYn1xjwWDJsqIleJaNbdZ/s1600/untitled7.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="214" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgqwX6OSwNIsueX46qoVQ5VnpL6TqVyqQM_fsXF7S71LVzfCe9Gbbo1gTGBlma_VTddpE1fXlH-CRVwIpzScMULmtAilJQwzWo-87Eu-N769N7S9hnnyE5bd0BYYn1xjwWDJsqIleJaNbdZ/s400/untitled7.JPG" /></a></div>Thats the beauty .<i>You did not have to delete or truncate a single row.Nor did you remove the article or stopped any agent .</i> 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 <br />
<b>--inserting new values in test table ( in publisher database )</b><br />
declare @val int<br />
set @val=2000<br />
while (@val > 1000)<br />
begin <br />
insert into test..test values (@val)<br />
set @val=@val-1<br />
end<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgrEJCg_YM1PhrFaqMJxG3TLNsSPkju9jwJkdNAy6ky0CgmTCuoJV9TDtp2zFhSuzLCRd0a4ycT5HoaU3I9CJkxAVXxm4tStAUenSDBC5Aj3EaDBnD9mT1wWa7yNQk_iJGk3GNriSS-72Oq/s1600/untitled8.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="155" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgrEJCg_YM1PhrFaqMJxG3TLNsSPkju9jwJkdNAy6ky0CgmTCuoJV9TDtp2zFhSuzLCRd0a4ycT5HoaU3I9CJkxAVXxm4tStAUenSDBC5Aj3EaDBnD9mT1wWa7yNQk_iJGk3GNriSS-72Oq/s400/untitled8.JPG" /></a></div><br />
Suggestions are welcome as we are here to help each other technicaly grow.Happly learningUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-8436612531636428647.post-26221470443856197002011-06-18T17:23:00.000+05:302011-06-18T17:23:53.392+05:30ORA-00942: table or view does not exist .The complete error is :<br />
select xactts, subguid from MSrepl7 where pubsrv = ? and pubdb = ? and indagent = 0 and subtype = 0 <br />
ORA-00942: table or view does not exist <br />
<br />
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.<br />
<br />
It was clear that the error is coming from the subscriber .But we were not replicating the object <b>: </b><b>MSrepl7</b> 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 .<br />
<br />
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 .<br />
<br />
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 . <br />
<br />
<br />
To see if its really being created I enabled tracing on Distribution agent since its this job which is failing . <br />
<b>-Output C:\Temp\OUTPUTFILE.txt -Outputverboselevel 2. </b><br />
<br />
Ran the Agent again which failed with same error .checked the file: <br />
<br />
<SNIP> <br />
OLE DB Subscriber 'ERPDEV.WORLD': <b>create table MSrepl7</b> (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)) <br />
<br />
Connecting to Distributor 'MCMSMESVS1.distribution' <br />
[4/21/2010 1:32:46 AM]MCMSMESVS1.distribution: {call master..sp_MScheck_agent_instance(N'MCMSMESVS1-MES-ERPDEV.WORLD-1', 10)} <br />
OLE DB Subscriber 'ERPDEV.WORLD': <b>select xactts, subguid from MSrepl7 where pubsrv = ? and pubdb = ? and indagent = 0 and subtype = 0 <br />
Agent message code 20046. ORA-00942: table or view does not exist </b><br />
<br />
[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)} <br />
<b>Adding alert to msdb..sysreplicationalerts: ErrorId = 12, </b><br />
</SNIP> <br />
<br />
This means that the table is being created.But its not there .Strange. <br />
<br />
To check whats happening ,I connected to Oracle server and tried to create a test table .... <br />
<br />
<SNIP> <br />
Connected to: <br />
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production <br />
With the Partitioning, OLAP and Oracle Data Mining options <br />
JServer Release 9.2.0.8.0 - Production <br />
<br />
SQL> create table test(t int); <br />
create table test(t int) <br />
* <br />
ERROR at line 1: <br />
ORA-01031: insufficient privileges <br />
<br />
</SNIP><br />
<br />
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). <br />
<br />
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 . <br />
<br />
BINGO ........the issue was resolved ...hope this blog helps someone someday ..<br />
<br />
Regards<br />
AbhayUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-8436612531636428647.post-49474689958995296002011-06-18T16:44:00.000+05:302011-06-18T16:44:29.876+05:30What should we do first : Try to find the solution or try to find the ProblemHad I been asked this question a few years ago , I would have said "I would search for a solution" .<br />
<br />
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 .<br />
<br />
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 :<br />
<b>Msg 241, Level 16, State 1, Line 2</b> <br />
<b>Conversion failed when converting datetime from character string.</b><br />
<br />
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.<br />
<br />
What Should I do , Google it or BING it :-) .we did not do that .<br />
you can see this message in sysmessages . [select * from sys.messages where message_id=241]<br />
<br />
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 .<br />
<br />
<b>Repro of the issue</b><br />
<br />
<b>Repro 1</b><br />
declare @date datetime ,@string char(100)<br />
select @date =getdate()<br />
set @string =@date<br />
<br />
Command(s) completed successfully.<br />
<br />
<b>Repro 2</b><br />
declare @date datetime ,@string char(100)<br />
select @date ='28/07/2010'<br />
set @string =@date<br />
<br />
<i>Msg 242, Level 16, State 3, Line 2<br />
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.<br />
</i><br />
<br />
So we are near as this error is almost similar to 241 , but not the same.<br />
<br />
<b>Repro 3</b><br />
declare @date datetime ,@string char(100)<br />
select @date ='2010/07/28'<br />
set @string =@date<br />
<br />
Command(s) completed successfully.<br />
<br />
<b>Repro 4</b><br />
declare @date datetime ,@string char(100)<br />
select @date =NULL <i><-- 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.</i><br />
set @string =@date<br />
select @string<br />
Command(s) completed successfully. <-- We did not
<b>Repro 5</b><br />
<br />
declare @date datetime ,@string char(100)<br />
select @date ='NULL'<br />
set @string =@date<br />
select @string<br />
<br />
Msg 241, Level 16, State 1, Line 2<br />
Conversion failed when converting datetime from character string.<br />
<i>here we got the error .</i><br />
<br />
<b>Repro 6</b><br />
declare @date datetime ,@string char(100)<br />
select @date ='NULL'<br />
<br />
<i>This is more clear </i><br />
Msg 241, Level 16, State 1, Line 2<br />
Conversion failed when converting datetime from character string.<br />
<br />
<b>So the reason could be :</b><br />
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).<br />
<br />
Conclusion :<br />
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 .<br />
<br />
Regards <br />
AbhayUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-8436612531636428647.post-53992945885614341512011-06-18T16:30:00.001+05:302011-06-18T16:31:55.412+05:30Immediate Sync option in Transactional Replication : Good or Bad ..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 . <br />
<br />
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.<br />
<br />
In the verbose log you can also check the statistics which is fired every 5 mins (starting SQL Server 2005 ): <br />
•Cumulative Update 12 for SQL Server 2005 Service Pack 2 <br />
•Cumulative Update 5 for SQL Server 2008 <br />
•Cumulative Update 2 for SQL Server 2008 Service Pack 1<br />
<br />
you will see data between these lines <br />
<br />
*************STATISTICS SINCE AGENT STARTED *******************<br />
[data]<br />
***************************************************************************<br />
<br />
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 .<br />
<br />
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 .<br />
<br />
So whats the issue ? Why the job is not removing the replicated rows ??<br />
upon digging deep , we found that the DBA selected the <b>"Create a snapshot immediately and keep the snapshot available to initialize subscriptions"</b> 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 .<br />
<br />
<b>Why it happens :</b> <br />
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.<br />
<br />
<b>Why is it configured ?</b><br />
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 .<br />
<br />
<b>How is it configured ?</b><br />
<b>Commandline :</b> <br />
via sp_addpublication <--Check BOL
GUI :
<div class="separator" style="clear: both; text-align: center;"><br />
<br />
<a href="http://4.bp.blogspot.com/-tJdnuh5vJDM/TfyEcwJuXVI/AAAAAAAADoQ/zhEuCOndSCY/s1600/immediate_sync.JPG" imageanchor="1" style="clear:left; float:left;margin-right:1em; margin-bottom:1em"><img border="0" height="291" width="320" src="http://4.bp.blogspot.com/-tJdnuh5vJDM/TfyEcwJuXVI/AAAAAAAADoQ/zhEuCOndSCY/s320/immediate_sync.JPG" /></a></div><br />
<b>Drawback :</b> <br />
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.<br />
<br />
<b>How to disable it ?</b><br />
EXEC sp_changepublication <br />
@publication = 'your_publication_name',<br />
@property = 'immediate_sync', <br />
@value = 'false' <br />
GO <br />
<br />
Happy Learning !!!!!!!Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-8436612531636428647.post-92144161745701906302011-06-18T15:13:00.000+05:302011-06-18T15:13:14.820+05:30Address translation: How Virtual Memory is mapped to Physical Memory<b>Introduction</b><br />
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.<br />
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) .<br />
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.<br />
<br />
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 .<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgBil-roV3QaQOUpa74DRBJJiBESRRdkKakk_TrsamdGnVNg-NrUc-C0v4IfuJOl8S8GGx0WhY0ZE0eTbblrssedaJpDQq_zy4YjJ-KdTiShVttpC46ZI0wL1Wmgc7FyZNUfF8mpg65JIHB/s1600/add_tr.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="400" width="388" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgBil-roV3QaQOUpa74DRBJJiBESRRdkKakk_TrsamdGnVNg-NrUc-C0v4IfuJOl8S8GGx0WhY0ZE0eTbblrssedaJpDQq_zy4YjJ-KdTiShVttpC46ZI0wL1Wmgc7FyZNUfF8mpg65JIHB/s400/add_tr.JPG" /></a></div><br />
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.<br />
<br />
<b>Address translation</b> is the process of translating the virtual memory to physical memory.<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgg4AGwMMu9ZqjlVTCTJ4b45Intsyhbq6_rlDsQNRWW4tACykAJdpnWfvBN4-m3MSiRIe47rf-dclX86Ms2F5JgdZ0GtRARDPKZ9iz1jKPZYeJL-qwwMh_bX1pLuRin9r4VClNb9KyIDdaK/s1600/add_tr1.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="400" width="376" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgg4AGwMMu9ZqjlVTCTJ4b45Intsyhbq6_rlDsQNRWW4tACykAJdpnWfvBN4-m3MSiRIe47rf-dclX86Ms2F5JgdZ0GtRARDPKZ9iz1jKPZYeJL-qwwMh_bX1pLuRin9r4VClNb9KyIDdaK/s400/add_tr1.JPG" /></a></div><br />
<br />
Every Virtual Address has 3 components:<br />
<br />
The <b>Page Directory Index :</b> 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: <b> ttp://en.wikipedia.org/wiki/Memory_management_unit </b> ).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<br />
associated with the address.<br />
<br />
The <b>Page Table Index</b> : 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.<br />
<br />
<b>The Byte Index:</b> 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<br />
to store a page offset (4,096 = 2^12).<br />
<br />
<b>Let’s summarize it now:<i></i></b><br />
1. The CPU’s Memory Management Unit locates the page directory for the process using the special register mentioned above.<br />
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.<br />
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.<br />
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<br />
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.<br />
<br />
How the Address translation happens with PAE in place:<br />
Everything is same as above except that:<br />
1)There is a new table which is above PDEs and PTEs .Its Page Directory Pointer Table.<br />
2)The PTEs and PDEs are 64 bit wide as compared to 32 bit wide when PAE is not enabled.Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-8436612531636428647.post-6868782865984979222011-06-18T14:58:00.001+05:302011-06-18T15:04:47.784+05:30How 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)Hi Guys ,<br />
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.<br />
<br />
<b>First Part :</b> Solution of the issue .<br />
<b>Second Part :</b> 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 .<br />
<b>Third Part :</b> 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.<br />
<br />
<b>First Part : Issue and its solution .</b><br />
<b>Issue</b><br />
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) .<br />
<br />
<b>Error Messages</b><br />
<b>Setup Errors :</b><br />
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.<br />
Error: 2148734209<br />
Error message: Unknown error 0x80131501<br />
Error descrition: MSDTC was unable to read its configuration information. (Exception from HRESULT: 0x8004D027)<br />
<br />
<b>Errors in Application log :</b><br />
Unable to get the file name for the OLE Transactions Proxy DLL. Error Specifics: hr = 0x8004d027, <br />
d:\rtm\com\complus\dtc\dtc\xolehlp\xolehlp.cpp:176, CmdLine: C:\Windows\syswow64\MsiExec.exe -Embedding 0E2EA3ADA5DC17201B521333814654B2 C, Pid: 3484<br />
<br />
Failed to read the name of the default transaction manager from the registry. Error Specifics: hr = 0x00000002, <br />
d:\rtm\com\complus\dtc\dtc\xolehlp\xolehlp.cpp:382, CmdLine: C:\Windows\syswow64\MsiExec.exe -Embedding 0E2EA3ADA5DC17201B521333814654B2 C, Pid: 3484<br />
<br />
<b>Errors in SQL Server :</b> <br />
QueryInterface failed for "ITransactionDispenser": 0x8004d027(XACT_E_UNABLE_TO_READ_DTC_CONFIG).<br />
<br />
<b>Client Environment Details</b><br />
SQL Server 2005 RTM 32 bit Standard Edition (WOW mode)<br />
Windows 2008 64 bit Standard <br />
<br />
<b>Troubleshooting done</b><br />
-> The setup was failing only for Notification services and Client tools .Rest all components were successfully upgraded to SP3 .<br />
-> Found the Both Notification services and Client tools were failing because of same error :<br />
<br />
---------------------------------------------------------------------------Product : Client Components<br />
Product Version (Previous): 1399<br />
Product Version (Final) : <br />
Status : Failure<br />
Log File : C:\Program Files (x86)\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQLTools9_Hotfix_KB955706_sqlrun_tools.msp.log<br />
Error Number : 29549<br />
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<br />
Error message: Unknown error 0x80131501<br />
Error description: MSDTC was unable to read its configuration information. (Exception from HRESULT: 0x8004D027)<br />
---------------------------------------------------------------------------<br />
<br />
---------------------------------------------------------------------------Product : Notification Services<br />
Product Version (Previous): 1399<br />
Product Version (Final) : <br />
Status : Failure<br />
Log File : C:\Program Files (x86)\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQLTools9_Hotfix_KB955706_sqlrun_tools.msp.log<br />
Error Number : 29549<br />
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<br />
Error message: Unknown error 0x80131501<br />
Error description: MSDTC was unable to read its configuration information. (Exception from HRESULT: 0x8004D027)<br />
---------------------------------------------------------------------------<br />
-> Detailed MSP logs showed that the error comes when MSDTC is trying to register the notification services using regsvcs<br />
<b><snip></b><br />
Error: 2148734209<br />
Error message: Unknown error 0x80131501<br />
Error descrition: MSDTC was unable to read its configuration information. (Exception from HRESULT: 0x8004D027)<br />
Error Code: -2146233087<br />
Windows Error Text: Source File Name: sqlca\sqlassembly.cpp<br />
Compiler Timestamp: Sat Oct 25 08:47:00 2008<br />
Function Name: Do_sqlAssemblyRegSvcs<br />
Source Line Number: 155<br />
<b></SNIP></b><br />
<br />
-> 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.<br />
<br />
-> We then tried to manually register the microsoft.sqlserver.notificationservices.dll through command prompt :<br />
%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" <br />
<br />
-> This did not work and we were getting some other error .<br />
<br />
-> 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 .<br />
->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 .<br />
<br />
-> We checked the SQL Sevrer errorlogs and found the same entry related to MSDTC , but in a slightly different manner :<br />
QueryInterface failed for "ITransactionDispenser": 0x8004d027XACT_E_UNABLE_TO_READ_DTC_CONFIG)<br />
<br />
-> We also saw some errors related to MSDTC : <br />
Unable to get the file name for the OLE Transactions Proxy DLL. <br />
Error Specifics: hr = <br />
0x8004d027,d:\rtm\com\complus\dtc\dtc\xolehlp\xolehlp.cpp:176, CmdLine: C:\Windows\syswow64\MsiExec.exe <br />
-Embedding -E2EA3ADA5DC17201B521333814654B2 C, Pid: 3484<br />
<br />
Failed to read the name of the default transaction manager from the registry. Error Specifics: hr = 0x00000002, <br />
d:\rtm\com\complus\dtc\dtc\xolehlp\xolehlp.cpp:382, <br />
CmdLine: C:\Windows\syswow64\MsiExec.exe <br />
-Embedding 0E2EA3ADA5DC17201B521333814654B2 C, Pid: 3484<br />
<br />
-> 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 :<b>http://ms-abhay.blogspot.com/2009/10/msdtc-was-unable-to-read-its.html</b>. But it was on Win server 2003)<br />
<br />
-> 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".<br />
<br />
-> 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.<br />
<br />
-> 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.<br />
<br />
-> 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.<br />
<br />
-> In between we also tried to repair .net framework 3.5 SP1 which did not help .<br />
<br />
-> I also went through this article which talks about making sure that all the MSDTC related keys are present in the registry hive: <br />
http://msdn.microsoft.com/en-us/library/dd300421(v=WS.10).aspx(This was the 1st step towards solution , although it did not work.)<br />
<br />
-> However , the keys mentioned in this article were present in both WOW mode and normal mode in the Registry .<br />
<br />
-> Then on my machine I tried to find the registries with the value "OLETransactionManagers". The first Key hit was : HKEY_CLASSES_ROOT\OLETransactionManagers<br />
<br />
-> 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.<br />
<br />
-> However , on my laptop this key had some values while there were no values on the Client side registry key (mentioned above).<br />
<br />
-> We found that these 64 bit MSDTC registry keys were there but the 32 bit (WOW mode) registry keys related to MSDTC were missing . <br />
<br />
-> We created these keys in WOW mode <br />
<br />
<b> HKEY_CLASSES_ROOT\Wow6432Node\OLETransactionManagers <br />
String value name = DefaultTM and value data=MSDTC<br />
<br />
HKEY_CLASSES_ROOT\Wow6432Node\OLETransactionManagers\MSDTC <br />
String value name=DLL and value data=MSDTCPRX.DLL<br />
</b><br />
<br />
->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.<br />
<br />
->This gave us some hope and with that hope we hoped that the setup will run successfully .This in turn did happen (",).<br />
<br />
<b>Second Part: Reproducing the error </b><br />
I reproduced this on my machine by removing all the keys from HKEY_CLASSES_ROOT\OLETransactionManagers<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPDK8z9TGQlNNn86LJ66phi5Dv_-ypGcmoIFFIIh2iQI_2jr2Wn32vHdR1ifJxlJk9vIn0KgzHd3xY6WWwBC8piNEbZIGrA4tIYU0vTqIVSqq06hwHOd34ZuRGRaDt3z1B6WD1ilE0m5N7/s1600/1.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="106" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPDK8z9TGQlNNn86LJ66phi5Dv_-ypGcmoIFFIIh2iQI_2jr2Wn32vHdR1ifJxlJk9vIn0KgzHd3xY6WWwBC8piNEbZIGrA4tIYU0vTqIVSqq06hwHOd34ZuRGRaDt3z1B6WD1ilE0m5N7/s400/1.JPG" /></a></div><br />
When I ran the setup it gave me the same warnings on SCC :<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgntE7Q8XDgCawEs1hj2frGe3QHcmnme8g8ekQgNpa8ceow6UBbvNis4-OeWdERkxlDPPpwjZTdaS5XVjMsupX3ZtdGQcW7DkJcRVqwZS5tlDPmiO6I-gAUC2862dwE3HWKHbBWyzbqtu9S/s1600/2.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="346" width="379" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgntE7Q8XDgCawEs1hj2frGe3QHcmnme8g8ekQgNpa8ceow6UBbvNis4-OeWdERkxlDPPpwjZTdaS5XVjMsupX3ZtdGQcW7DkJcRVqwZS5tlDPmiO6I-gAUC2862dwE3HWKHbBWyzbqtu9S/s400/2.JPG" /></a></div><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgesruXyR_mwCXITXrmpqSLysgm2E8L0z0NJZp-0k_HSYplA5PTf-B953aNS1a5CYMsAPX8dJ2CntGGNuR0pN0UXrRIOfUZuLWbxcMhshBh4IbA8t2lBv-8jttTE_cm-xlt56uPxD2-KGCP/s1600/2_1.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="205" width="365" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgesruXyR_mwCXITXrmpqSLysgm2E8L0z0NJZp-0k_HSYplA5PTf-B953aNS1a5CYMsAPX8dJ2CntGGNuR0pN0UXrRIOfUZuLWbxcMhshBh4IbA8t2lBv-8jttTE_cm-xlt56uPxD2-KGCP/s400/2_1.JPG" /></a></div><br />
I will still go ahead with the setup and select only the client components to install :<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh7jhICNdZLoQ6d655-h0kv8XfyEdYhmd8iE0UujyTy_lELlbVjpQcVbj_0Talczdv8zsMkdTsa_FvRKC7azIJQPB6ynEhNtz_P-F9MWyJgmUL9mlJEr-Wo8EICsnVxP1SEYlqhX-GPvLZg/s1600/3.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="370" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh7jhICNdZLoQ6d655-h0kv8XfyEdYhmd8iE0UujyTy_lELlbVjpQcVbj_0Talczdv8zsMkdTsa_FvRKC7azIJQPB6ynEhNtz_P-F9MWyJgmUL9mlJEr-Wo8EICsnVxP1SEYlqhX-GPvLZg/s400/3.JPG" /></a></div><br />
The setup will encounter this error:<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgcOe78gAbecVZ-DFJgq9ruGz3dLyNL6oUe3Mj8K7_PobGxTK4fSSqJJ_ja2btS0I3ZGjMPI_gxOdx4w0mWkv0E8mIXj7pyjBKwWOlOGMZOlHsx5AghlXWXCpZ1y3Qh5AEzGw4Sn2YpAsEl/s1600/4.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="126" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgcOe78gAbecVZ-DFJgq9ruGz3dLyNL6oUe3Mj8K7_PobGxTK4fSSqJJ_ja2btS0I3ZGjMPI_gxOdx4w0mWkv0E8mIXj7pyjBKwWOlOGMZOlHsx5AghlXWXCpZ1y3Qh5AEzGw4Sn2YpAsEl/s400/4.JPG" /></a></div><br />
Let’s check the Application logs:<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJZYLfytpfNGhqxKnOitN_LRSv_bWgNt7aaRTnOMoeQW2-OXxw_OBby9pUj6FXr3gCNiK82TueXG0mMrlXDyPIJVJYVHEkTKMtMsTfdi-o8iYKgcLF8JGBudfYIWkeabvJg-wWKhMaM8r-/s1600/5.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="400" width="354" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJZYLfytpfNGhqxKnOitN_LRSv_bWgNt7aaRTnOMoeQW2-OXxw_OBby9pUj6FXr3gCNiK82TueXG0mMrlXDyPIJVJYVHEkTKMtMsTfdi-o8iYKgcLF8JGBudfYIWkeabvJg-wWKhMaM8r-/s400/5.JPG" /></a></div><br />
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 <b>http://technet.microsoft.com/en-us/sysinternals/bb896645"</b>.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 .<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh72A-Ri6vssLLGkfFLZZZIjSFxHUg36ty9w-ZwMm6mHZW_1kA3geJSaBcPMPt4ldPMCZwUbwlrOIdkQQek-XrmQ4ua01AaNMYXE8A6pwPhNod8dWNdss0eS9GhRPSev4aysivnNGlgC4cS/s1600/6.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="81" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh72A-Ri6vssLLGkfFLZZZIjSFxHUg36ty9w-ZwMm6mHZW_1kA3geJSaBcPMPt4ldPMCZwUbwlrOIdkQQek-XrmQ4ua01AaNMYXE8A6pwPhNod8dWNdss0eS9GhRPSev4aysivnNGlgC4cS/s400/6.JPG" /></a></div><br />
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:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjETLmIN4KwGJaI3xH7ciwXXuFyA1jiOYqh9LAkbp1NLEjdYKty6a5Q7esTkR-9R1oRy_gVAi9txiLNQkg3z4-CZbbzcMCKu84qBLcp2eNnismPGWhm9Ly_dUhPhRdaiya6I4ay_zEi7s9Z/s1600/7.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="400" width="388" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjETLmIN4KwGJaI3xH7ciwXXuFyA1jiOYqh9LAkbp1NLEjdYKty6a5Q7esTkR-9R1oRy_gVAi9txiLNQkg3z4-CZbbzcMCKu84qBLcp2eNnismPGWhm9Ly_dUhPhRdaiya6I4ay_zEi7s9Z/s400/7.JPG" /></a></div><br />
I will now filter it on those 3 PIDs<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiYTRLVW8ZRxz2K4q9R9PYBTapm7zfQTSoCB3DksRRmry1C4VXOAtLDG9QO9jabL8FdC_XfwITe3a_IlXPU_jztdAK7EJT3Jzc6_HxRmYtEwgyCE8ozW1FUa5gq6ZyD7DrvKXKPoeCNO6bc/s1600/8.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="73" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiYTRLVW8ZRxz2K4q9R9PYBTapm7zfQTSoCB3DksRRmry1C4VXOAtLDG9QO9jabL8FdC_XfwITe3a_IlXPU_jztdAK7EJT3Jzc6_HxRmYtEwgyCE8ozW1FUa5gq6ZyD7DrvKXKPoeCNO6bc/s400/8.JPG" /></a></div><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQanBfdljaG59hWkem2CNkDZ2xVTRc7r8xtHqtWSx3RNHHtbioQAcyuhTXbMwyom7XVfn8VuvQOgiN1cCQvKGDDxFCHadla1Se5dVzAmvgUgo9V4P95pC-mCErXsGuVtZsMS2O9kGV2Ljj/s1600/9.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="251" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQanBfdljaG59hWkem2CNkDZ2xVTRc7r8xtHqtWSx3RNHHtbioQAcyuhTXbMwyom7XVfn8VuvQOgiN1cCQvKGDDxFCHadla1Se5dVzAmvgUgo9V4P95pC-mCErXsGuVtZsMS2O9kGV2Ljj/s400/9.JPG" /></a></div><br />
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:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj89uV0IZaKI2Txaz_B_kmIlwpGajbyDlctFPIrpRq9rnP13XwKXo0XgZGBBDjeMKPyRLHGbZIJxUFH9trwiSDNGQpIX73-YnM3Ed7q6n0VVKWtmzsRWxaMAJKYylJpBaCHhQSdgzvZm38h/s1600/10.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="251" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj89uV0IZaKI2Txaz_B_kmIlwpGajbyDlctFPIrpRq9rnP13XwKXo0XgZGBBDjeMKPyRLHGbZIJxUFH9trwiSDNGQpIX73-YnM3Ed7q6n0VVKWtmzsRWxaMAJKYylJpBaCHhQSdgzvZm38h/s400/10.JPG" /></a></div><br />
Right click on SUCCESS and select:<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhGb7t9NuVGxBKCEtvXCM27gAGNAKinYgh4yk2zS7KJnQifMb1t5ktZWMD4E0-oXHmGrbWgNhCnJC_XJtWX8Poh-sNXibNlM5WGBEJ9KTUAIJ-j2X6bf8wObVZs0oZwlGjy7M12TzyJGdHo/s1600/11.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="208" width="173" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhGb7t9NuVGxBKCEtvXCM27gAGNAKinYgh4yk2zS7KJnQifMb1t5ktZWMD4E0-oXHmGrbWgNhCnJC_XJtWX8Poh-sNXibNlM5WGBEJ9KTUAIJ-j2X6bf8wObVZs0oZwlGjy7M12TzyJGdHo/s400/11.JPG" /></a></div><br />
Right click on RegOpenKey and select:<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgw_lCLz_Y2SjgHx0It47VVKnISc3F8Sjoe_lRWli_fmGj3bAAPrqylJkTr9yD3wk9Uhhy4o-v2XfPzUgsZoB_JHmkvsE7A_r8XaL48-nEZ3ifsNvc8m317cQSTJId_EViMgGrXx_MF7fFV/s1600/12.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="208" width="198" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgw_lCLz_Y2SjgHx0It47VVKnISc3F8Sjoe_lRWli_fmGj3bAAPrqylJkTr9yD3wk9Uhhy4o-v2XfPzUgsZoB_JHmkvsE7A_r8XaL48-nEZ3ifsNvc8m317cQSTJId_EViMgGrXx_MF7fFV/s400/12.JPG" /></a></div><br />
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:<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9Lr8Gzx7MxB1NG20FVScohZ74txoUWE3xJv2i_lC3gXXQPn3ExT6RJi-E207uIErePwZWrTVw7ysx0QxCbUrj_RSW5A3FeuxWDVHqvz_j3hPcGfgtkme3lpjPliz1g7hyoF-KzFlrbJIx/s1600/13.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="45" width="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9Lr8Gzx7MxB1NG20FVScohZ74txoUWE3xJv2i_lC3gXXQPn3ExT6RJi-E207uIErePwZWrTVw7ysx0QxCbUrj_RSW5A3FeuxWDVHqvz_j3hPcGfgtkme3lpjPliz1g7hyoF-KzFlrbJIx/s400/13.JPG" /></a></div><br />
Now ,notice the error copied above from application log:<br />
*******<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj97k7r2qZyBezlv_fa0DCGP4aFup5NsKCBzwiNprsoZGd7Tdnbm4tX_mpTvQezF5kUdD-akHUTqqglANoPSdPz2H9SckeMSY29nha5x6IiIetyMtJ5EKvgHy5z1a2j-cuopLYGT4IzLb_n/s1600/15.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="31" width="307" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj97k7r2qZyBezlv_fa0DCGP4aFup5NsKCBzwiNprsoZGd7Tdnbm4tX_mpTvQezF5kUdD-akHUTqqglANoPSdPz2H9SckeMSY29nha5x6IiIetyMtJ5EKvgHy5z1a2j-cuopLYGT4IzLb_n/s400/15.JPG" /></a></div><br />
<br />
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:<br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFQk0zhUOBkVxJj34Q6et55e5t-61rW3tFiU58fpr8chLhPGFh9Rh6Q1w_BSxbjyYwOkMBBKgmQ2jRz7OXp13bIGaZ65i2KnCFwtpculXvt4xzIzO_C2w2Rk4PN0kWpbgOSEjVn8EtHUhH/s1600/14.JPG" imageanchor="1" style="margin-left:1em; margin-right:1em"><img border="0" height="400" width="344" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFQk0zhUOBkVxJj34Q6et55e5t-61rW3tFiU58fpr8chLhPGFh9Rh6Q1w_BSxbjyYwOkMBBKgmQ2jRz7OXp13bIGaZ65i2KnCFwtpculXvt4xzIzO_C2w2Rk4PN0kWpbgOSEjVn8EtHUhH/s400/14.JPG" /></a></div><br />
So the first key that is missing is <b>HKCR\OLETransactionManagers\DefaultTM</b> .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.<br />
<br />
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).<br />
<br />
<b>Third part : The Bugs</b><br />
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 .<br />
<br />
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.<br />
<br />
<br />
Happy Learning !!Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-8436612531636428647.post-17843839266192612782011-06-18T14:04:00.000+05:302011-06-18T14:04:25.311+05:30Understanding how SQL Server behaves in a non-preemptive mode while still running on the OS which is preemptiveToday , 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 ...<br />
<br />
<b>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 .</b><br />
<br />
<b> Windows Scheduling (Preemptive) :</b> <br />
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 <br />
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). <br />
<br />
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 <br />
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.<br />
<br />
<b>SQL Server Scheduling (non-preemptive) :</b> <br />
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 :<br />
<b>1) Why SQL Server does not hang just like windows 98 use to ?</b><br />
<b>Answer:</b> 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 .<br />
<br />
<b>2) How SQL Server manages to schedule in the non-preemptive way ?</b><br />
<b>Answer: </b>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 <br />
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.<br />
<br />
Let me show you a Demo since my Laptop has only one dual core processor (Its SQL Server 2008 RTM):<br />
<b>Lets first run a simple query and find runnable and sleeping threads </b> <i>Select Status ,* from sysprocesses where status not in ('background')</i><br />
<br />
you will notice that all the SPIDS will show you the status of <b>sleeping</b> and there is only one SPID that will be showing you the status of <b>runnable</b> .Its waittype will be <b>PREEMPTIVE_os_WAITFORSINGLEOBJECT</b> .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 .<br />
<br />
Now lets open another Query window and execute the same command there .<br />
<b>Select Status ,* from sysprocesses where status not in ('background')</b>. 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 .<br />
<br />
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 :) ...<br />
<br />
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.<br />
<br />
<b>Select Status ,* from sysprocesses where status not in ('background','sleeping')</b> .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 .<br />
<br />
Have a nice day and Happy Learning !!!Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-8436612531636428647.post-72195381443842267412011-04-27T13:23:00.000+05:302011-04-27T13:23:12.516+05:30Finding optimal number of CPUs for a given long running CPU intensive queries (except OLAP queries)Hi Guys ,<br />
<br />
Hope this article will help you in some or the other way one day :) .....<br />
<br />
<b>Introduction:</b><br />
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).<br />
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.<br />
Further ,it is very important to find ways to optimize the queries/workload by tuning the database schema before attempting to add additional CPUs.<br />
<br />
<b>Description:</b><br />
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 ?<br />
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.<br />
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.<br />
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.<br />
<br />
<b>Procedure:</b><br />
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.<br />
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.<br />
1. Reset Wait Stats<br />
dbcc sqlperf('sys.dm_os_wait_stats', clear)<br />
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).<br />
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.<br />
<br />
<i>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,<br />
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<br />
from sys.dm_os_wait_stats ws cross apply sys.dm_os_sys_info si where ws.wait_type = 'SOS_SCHEDULER_YIELD' <br />
</i><br />
<b>Example:</b><br />
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.<br />
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!!<br />
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.<br />
<br />
<b>Explanation:</b><br />
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.<br />
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.<br />
<br />
<b>Exception:</b><br />
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.<br />
<br />
<br />
<b>Sample Workload:</b><br />
Create the below table before running the query to generate CPU intensive workload.<br />
<i>Serial Workload: </i><br />
select max(t1.c2 + t2.c2) from tab7 t1 cross join tab7 t2 option (maxdop 1)<br />
<i>Parallel Workload: </i><br />
select max(t1.c2 + t2.c2) from tab7 t1 cross join tab7 t2Â <br />
<i>Table:</i><br />
create table tab7 (c1 int primary key clustered, c2 int, c3 char(2000))<br />
go<br />
begin tran<br />
declare @i int<br />
set @i = 1<br />
while @i <= 5000<br />
begin<br />
insert into tab7 values (@i, @i, 'a')<br />
set @i = @i + 1<br />
end<br />
commit tran<br />
go<br />
<br />
<br />
<br />
Happy Learning !!!Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-8436612531636428647.post-69687887435293289032010-11-09T22:19:00.000+05:302010-11-09T22:19:50.227+05:30SQL Server Setup has encountered the following error:File format is not validToday , 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 :<br />
<br />
Version : SQL Server 2008 R2 <br />
OS : Win Server 2008<br />
<br />
Error : <br />
TITLE: SQL Server Setup failure.<br />
------------------------------<br />
SQL Server Setup has encountered the following error:<br />
File format is not valid..<br />
------------------------------<br />
BUTTONS:<br />
OK<br />
------------------------------<br />
<br />
<b>Resolution :</b> <br />
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 .<br />
<br />
I first opened Detail_ComponentUpdate.txt <br />
<br />
2010-11-09 20:13:14 Slp: Running Action: GatherUserSettings<br />
2010-11-09 20:13:21 Slp: -- PidPublicConfigObject : ValidateSettings is normalizing input pid=[PID value hidden]<br />
2010-11-09 20:13:21 Slp: -- PidPrivateConfigObject : NormalizePid is normalizing input pid=[PID value hidden]<br />
2010-11-09 20:13:21 Slp: -- PidPrivateConfigObject : NormalizePid found a pid containing dashes, assuming pid is normalized, output pid=[PID value hidden]<br />
2010-11-09 20:13:21 Slp: -- PidPublicConfigObject : ValidateSettings proceeding with normalized pid=[PID value hidden]<br />
2010-11-09 20:13:21 Slp: -- PidPrivateConfigObject : Initialize is initializing using input pid=[PID value hidden]<br />
2010-11-09 20:13:21 Slp: -- PidPrivateConfigObject : NormalizePid is normalizing input pid=[PID value hidden]<br />
2010-11-09 20:13:21 Slp: -- PidPrivateConfigObject : NormalizePid found a pid containing dashes, assuming pid is normalized, output pid=[PID value hidden]<br />
2010-11-09 20:13:21 Slp: -- PidPrivateConfigObject : Initialize proceeding with normalized pid=[PID value hidden]<br />
2010-11-09 20:13:21 Slp: -- PidPrivateConfigObject : Initialize called ValidatePid, output is pid=[PID value hidden] validateSuccess=True output editionId=EVAL(0x2467BCA1)<br />
2010-11-09 20:13:21 Slp: -- PidPublicConfigObject : ValidateSettings initialized private object, result is initializeResult=Success<br />
<b><br />
2010-11-09 20:13:22 Slp: Detected localization resources folder: 1033<br />
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<br />
2010-11-09 20:13:22 Slp: Error: Action "GatherUserSettings" threw an exception during execution.<br />
</b><br />
2010-11-09 20:13:22 Slp: Microsoft.SqlServer.Setup.Chainer.Workflow.ActionExecutionException: Thread was being aborted. ---> System.Threading.ThreadAbortException: Thread was being aborted.<br />
2010-11-09 20:13:22 Slp: at System.Threading.WaitHandle.WaitOneNative(SafeWaitHandle waitHandle, UInt32 millisecondsTimeout, Boolean hasThreadAffinity, Boolean exitContext)<br />
2010-11-09 20:13:22 Slp: at System.Threading.WaitHandle.WaitOne(Int64 timeout, Boolean exitContext)<br />
2010-11-09 20:13:22 Slp: at System.Threading.WaitHandle.WaitOne(Int32 millisecondsTimeout, Boolean exitContext)<br />
2010-11-09 20:13:22 Slp: at System.Threading.WaitHandle.WaitOne()<br />
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Configuration.UIExtension.Request.Wait()<br />
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Configuration.UIExtension.UserInterfaceProxy.SubmitAndWait(Request request)<br />
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Configuration.UIExtension.UserInterfaceProxy.NavigateToWaypoint(String moniker)<br />
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Configuration.UIExtension.UserInterfaceService.Waypoint(String moniker)<br />
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Configuration.UIExtension.WaypointAction.ExecuteAction(String actionId)<br />
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)<br />
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.InvokeAction(WorkflowObject metabase, TextWriter statusStream)<br />
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Setup.Chainer.Workflow.PendingActions.InvokeActions(WorkflowObject metaDb, TextWriter loggingStream)<br />
2010-11-09 20:13:22 Slp: --- End of inner exception stack trace ---<br />
2010-11-09 20:13:22 Slp: at Microsoft.SqlServer.Setup.Chainer.Workflow.PendingActions.InvokeActions(WorkflowObject metaDb, TextWriter loggingStream)<br />
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<br />
2010-11-09 20:13:25 Slp: The following is an exception stack listing the exceptions in outermost to innermost order<br />
2010-11-09 20:13:25 Slp: Inner exceptions are being indented<br />
2010-11-09 20:13:25 Slp: <br />
2010-11-09 20:13:25 Slp: Exception type: System.ArgumentException<br />
2010-11-09 20:13:25 Slp: Message: <br />
2010-11-09 20:13:25 Slp: File format is not valid.<br />
2010-11-09 20:13:25 Slp: Stack: <br />
2010-11-09 20:13:25 Slp: at System.Windows.Forms.RichTextBox.StreamIn(Stream data, Int32 flags)<br />
2010-11-09 20:13:25 Slp: at System.Windows.Forms.RichTextBox.LoadFile(Stream data, RichTextBoxStreamType fileType)<br />
2010-11-09 20:13:25 Slp: at System.Windows.Forms.RichTextBox.LoadFile(String path, RichTextBoxStreamType fileType)<br />
2010-11-09 20:13:25 Slp: at Microsoft.SqlServer.Configuration.InstallWizard.EULAPidView.UpdateLicenseText(String filepath)<br />
2010-11-09 20:13:25 Slp: at Microsoft.SqlServer.Configuration.InstallWizard.EULAPidController.LoadData()<br />
2010-11-09 20:13:25 Slp: at Microsoft.SqlServer.Configuration.InstallWizardFramework.InstallWizardPageHost.PageEntered(PageChangeReason reason)<br />
2010-11-09 20:13:25 Slp: at Microsoft.SqlServer.Configuration.WizardFramework.UIHost.set_SelectedPageIndex(Int32 value)<br />
2010-11-09 20:13:25 Slp: at Microsoft.SqlServer.Configuration.WizardFramework.UIHost.GoNext()<br />
2010-11-09 20:13:25 Slp: at Microsoft.SqlServer.Configuration.WizardFramework.NavigationButtons.nextButton_Click(Object sender, EventArgs e)<br />
2010-11-09 20:13:25 Slp: at System.Windows.Forms.Control.OnClick(EventArgs e)<br />
2010-11-09 20:13:25 Slp: at System.Windows.Forms.Button.OnClick(EventArgs e)<br />
2010-11-09 20:13:25 Slp: at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)<br />
2010-11-09 20:13:25 Slp: at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)<br />
2010-11-09 20:13:25 Slp: at System.Windows.Forms.Control.WndProc(Message& m)<br />
2010-11-09 20:13:25 Slp: at System.Windows.Forms.ButtonBase.WndProc(Message& m)<br />
2010-11-09 20:13:25 Slp: at System.Windows.Forms.Button.WndProc(Message& m)<br />
2010-11-09 20:13:25 Slp: at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)<br />
2010-11-09 20:13:25 Slp: at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)<br />
2010-11-09 20:13:25 Slp: at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)<br />
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_<br />
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_<br />
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_<br />
<b>2010-11-09 20:28:36 Slp: File format is not valid.</b><br />
2010-11-09 20:28:36 Slp: Watson bucket for exception based failure has been created<br />
2010-11-09 20:28:36 Slp: Sco: Attempting to create base registry key HKEY_LOCAL_MACHINE, machine <br />
2010-11-09 20:28:36 Slp: Sco: Attempting to open registry subkey Software\Microsoft\PCHealth\ErrorReporting\DW\Installed<br />
2010-11-09 20:28:36 Slp: Sco: Attempting to get registry value DW0200<br />
2010-11-09 20:29:01 Slp: Submitted 1 of 1 failures to the Watson data repository<br />
<br />
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 ....<br />
<br />
When opened , I found it unreadable ...Initially I thought it is suppose to be like that as there might be something encrypted .<br />
However , there were other license files in the same folder which were absolutely readable ..<br />
<br />
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 :) .<br />
<br />
Hope this helps .Unknownnoreply@blogger.com6tag:blogger.com,1999:blog-8436612531636428647.post-46777819083527673882010-11-09T22:12:00.000+05:302010-11-09T22:12:33.903+05:30Error 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'.adding a new post after a good gap ...<br />
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 .<br />
<br />
<i>We tried the step below via DOS prompt :</i><br />
<b>C:\Documents and Settings\Abhay\Desktop\softwares\SQLEVAL_2005\Servers>start /wait setup.exe /qn INSTANCENAME=CORRUPT REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=XXXXX<br />
</b><br />
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 (<b>to find the error we should search for Return Value 3</b>):<br />
<br />
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'.<br />
MSI (s) (2C:08) [14:07:26:265]: User policy value 'DisableRollback' is 0<br />
MSI (s) (2C:08) [14:07:26:265]: Machine policy value 'DisableRollback' is 0<br />
<b>Action ended 14:07:26: InstallFinalize. Return value 3.</b><br />
<br />
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 .<br />
Also <br />
<br />
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<br />
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,,,,,,,)<br />
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; <b>No existing file<br />
</b>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,,,,,,,)<br />
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; <b>No existing file</b> <br />
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,,,,,,,)<br />
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; <b>No existing file</b><br />
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,,,,,,,)<br />
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; <b>No existing file</b><br />
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,,,,,,,)<br />
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; <b>No existing file</b><br />
<br />
This was strange as these files were existing ....<br />
<br />
<b>I ran the same command using \qb option and got the same error but in a form of a pop-up box.<br />
</b><br />
I then checked and found that there is an option called as <b>REINSTALLMODE</b> .;<b>REINSTALLMODE</b> is used to repair installed components. The supported values are:<br />
O – Reinstall if file is missing, or an older version is present.<br />
M – Rewrite machine specific reg keys under HKLM<br />
U – Rewrite user specific reg keys under HKCU<br />
S – Reinstall all shortcuts<br />
<br />
The Option O looked appropriate but I used all i.e. <br />
<b>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<br />
</b><br />
This resolved the issue on my laptop but not on client server .<br />
Finally I found that there is one more option which is never documented ...and this option is <b>V</b><br />
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.<br />
<br />
<b>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<br />
</b><br />
<br />
This ran like a knife through butter ..<br />
<br />
Hope it will help you in future ...Unknownnoreply@blogger.com5tag:blogger.com,1999:blog-8436612531636428647.post-53429607483931126892010-09-29T17:35:00.000+05:302010-09-29T17:35:01.887+05:30Error: 26049, Severity: 16, State: 1 :Server local connection provider failed to listen on [ \\.\pipe\SQLLocal\XXXXX ]. Error: 0x5There 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.<br />
<br />
The errors were :<br />
<br />
2010-09-30 03:17:31.65 Server Error: 26049, Severity: 16, State: 1.<br />
2010-09-30 03:17:31.65 Server Server local connection provider failed to listen on [ \\.\pipe\SQLLocal\XXXXX ]. Error: 0x5<br />
2010-09-30 03:17:31.65 Server Error: 17182, Severity: 16, State: 1.<br />
2010-09-30 03:17:31.65 Server TDSSNIClient initialization failed with error 0x5, status code 0x40.<br />
2010-09-30 03:17:31.65 Server Error: 17182, Severity: 16, State: 1.<br />
2010-09-30 03:17:31.65 Server TDSSNIClient initialization failed with error 0x5, status code 0x1.<br />
2010-09-30 03:17:31.65 Server Error: 17826, Severity: 18, State: 3.<br />
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.<br />
2010-09-30 03:17:31.65 Server Error: 17120, Severity: 16, State: 1.<br />
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.<br />
<br />
The issue is quite simple unlike it looks like .I tried everything like changing the named pipe , etc. etc.<br />
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.<br />
This resolved the issue .<br />
<br />
status code 0x40 means that there is an issue with Shared memory listener<br />
status code 0x50 means that there is an issue with Named pipe listener<br />
status code 0x0A means that there is an issue with TCP/IP listener<br />
<br />
Please go through this MSDN blog (which has one more link in it).<br />
http://blogs.msdn.com/b/sql_protocols/archive/2006/03/09/546655.aspx?wa=wsignin1.0<br />
<br />
Happy Learning !!!Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-8436612531636428647.post-36959648258207630022010-09-28T16:28:00.000+05:302010-09-28T16:28:39.859+05:30A simple VB script to retain Errorlogs worth 90 days (or as you like)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 ...<br />
<br />
Finally , we could come out with a simple VB script that can do it .The script code is mentioned below .<br />
<br />
<b>Code :<br />
</b><br />
<br />
sFolder = "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG"<br />
iMaxAge = 90<br />
Set oFSO = CreateObject("Scripting.FileSystemObject")<br />
If oFSO.FolderExists(sFolder) Then<br />
for each oFile in oFSO.GetFolder(sFolder).Files<br />
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<br />
wscript.echo "Deleting" &oFile.Name<br />
oFile.Delete<br />
End If<br />
next<br />
End If<br />
<br />
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 :<br />
<br />
File Timestamp<br />
Errorlog 12/9/2009<br />
Errorlog.1 12/8/2009<br />
Errorlog.2 12/7/2009<br />
Errorlog.3 12/6/2009<br />
Errorlog.4 12/5/2009<br />
Errorlog.5 12/4/2009<br />
Errorlog.6 12/3/2009<br />
<br />
The files deleted will be : Errorlog.3,Errorlog.4,Errorlog.5 and Errorlog.6<br />
<br />
You need to change the Path of sFolder variable ..<br />
Happy learning ...<br />
<br />
AbhayUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-8436612531636428647.post-61648185170069743072010-09-28T16:23:00.000+05:302010-09-28T16:23:59.386+05:30checkODBCConnectError: sqlstate = 28000; native error = 4818; message = [Microsoft][SQL Native Client][SQL Server]Login failed for user 'XXXXX\clusterservicea little Background :<br />
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).<br />
After that, the nodes were rebooted due to patching requirements .The nodes came up , but SQL Server did not :D ...<br />
<br />
Error in cluster logs (you will not find it in SQL Server logs) :<br />
<br />
ERR SQL Server <SQL Server>: [sqsrvres] checkODBCConnectError: sqlstate = 28000; native error = 4818; message = [Microsoft][SQL Native Client][SQL Server]Login failed for user 'XXXXX\clusterservice'.<br />
ERR SQL Server <SQL Server>: [sqsrvres] ODBC sqldriverconnect failed <br />
<br />
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.<br />
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.<br />
<br />
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.<br />
<br />
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 ...<br />
<br />
Commands :<br />
CREATE LOGIN [<Domain Name>\<MSCS Service Account>] FROM WINDOWS WITH DEFAULT_DATABASE=[master]<br />
EXEC master.sp_addsrvrolemember @loginame = N'<Domain Name>\<MSCS Service Account> ', @rolename = N'sysadmin'<br />
<br />
happly learning .....<br />
Regards<br />
AbhayUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-8436612531636428647.post-38030090410317623612010-09-20T20:37:00.000+05:302010-09-20T20:37:28.138+05:30Msg 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.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 :<br />
<br />
<b>SQL Server error in QA :</b><br />
Msg 22004, Level 16, State 1, Line 0<br />
Failed to open loopback connection. Please see event log for more information.<br />
Msg 22004, Level 16, State 1, Line 0<br />
error log location not found<br />
<br />
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).<br />
<br />
<b>I checked the application logs immediately and got these errors :<br />
</b><br />
Event Type: Error<br />
Event Source: MSSQLSERVER<br />
Event Category: (2)<br />
Event ID: 17052<br />
Date: 09/20/2010<br />
Time: 18:24:22<br />
User: N/A<br />
Computer: abchaudh<br />
Description:<br />
Severity: 16 Error:10061, OS: 10061 [Microsoft][SQL Native Client]TCP Provider: No connection could be made because the target machine actively refused it.<br />
<br />
<br />
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.<br />
Data:<br />
0000: 4d 27 00 00 0a 00 00 00 M'......<br />
0008: 12 00 00 00 61 00 62 00 ....a.b.<br />
0010: 63 00 68 00 61 00 75 00 c.h.a.u.<br />
0018: 64 00 68 00 00 00 0e 00 d.h.....<br />
0020: 00 00 6d 00 61 00 73 00 ..m.a.s.<br />
0028: 74 00 65 00 72 00 00 00 t.e.r...<br />
<br />
<br />
Event Type: Error<br />
Event Source: SQLAgent$CORRUPT<br />
Event Category: Service Control <br />
Event ID: 103<br />
Date: 09/20/2010<br />
Time: 18:24:45<br />
User: N/A<br />
Computer: abchaudh<br />
Description:<br />
SQLServerAgent could not be started (reason: Unable to connect to server 'abchaudh\CORRUPT'; SQLServerAgent cannot start).<br />
<br />
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.<br />
<br />
Event Type: Information<br />
Event Source: SQLAgent$CORRUPT<br />
Event Category: Service Control <br />
Event ID: 102<br />
Date: 09/20/2010<br />
Time: 18:24:52<br />
User: N/A<br />
Computer: abchaudh<br />
Description:<br />
SQLServerAgent service successfully stopped.<br />
<br />
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.<br />
<br />
I also checked the SQL Agent logs :<br />
<br />
<b>SQL Agent logs :<br />
</b><br />
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] <br />
2010-09-20 18:25:36 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00] <br />
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] <br />
2010-09-20 18:25:36 - ! [000] Unable to connect to server 'abchaudh\CORRUPT'; SQLServerAgent cannot start<br />
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] <br />
2010-09-20 18:25:42 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00] <br />
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] <br />
2010-09-20 18:25:42 - ! [382] Logon to server 'abchaudh\CORRUPT' failed (DisableAgentXPs)<br />
2010-09-20 18:25:43 - ? [098] SQLServerAgent terminated (normally)<br />
<br />
So, now I have 2 issues : SQL Agent is not running and xp_readerrorlog is timing out.<br />
<br />
.If you see one of my older posts on "target machine actively refuses it" , you will have some information .<br />
<br />
So I opened CLICONFG and found 3 incorrect aliases which were not using the right port ..<br />
<br />
I removed them and SQL Agent came on line ...xp_read errorlog also started working ...<br />
<br />
I stopped SQL Agent and stil everything was working ...<br />
So the issue was that xp_readerrlog tries to connect to SQL Server but stucks due to the Alias pointing to incorrect port.<br />
<br />
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 ...<br />
<br />
Happy learning ....Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-8436612531636428647.post-57984235528327976962010-08-25T14:48:00.000+05:302010-08-25T14:55:37.368+05:30Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.)Hi Team ,<br />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/)<br /><br /><strong>Issue :</strong><br />SQL Server Agent failed to come up after the service account password was reset at AD level .<br /><br /><strong>Error(s) :</strong><br />In the event log you will see these errors in sequence :<br /><br /><strong>Description:</strong><br />Could not load the DLL xpstar90.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.).<br /><br /><strong>Description:</strong><br />Failed to retrieve SQLPath for syssubsystems population.<br /><br /><strong>Description:</strong><br />SQLServerAgent could not be started (reason: Failed to load any subsystems. Check errorlog for details.).<br /><br />The first error is the main error and rest are the errors following the first error and we need not to think about them .<br /><br /><strong>Troubleshooting and Resolution :</strong><br />The error clearly says that either there is a problem with xpstar90.dll or the other dlls that this dll references .<br />This file is located in I first tried to re-register xpstar90.dll by using regsvr32 xrstar90.dll and got this message :<br /><br /><strong>xpstar90.dll was loaded , But the dllRegisterServer entry point was not found.</strong><br /><br />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.<br />I was also thinking that there might be some other DDL that this DLL refers to , which got corrupted.<br /><br />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.<br /><br /><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhesgqcZf7y5E8Abvc-NwDOYMtnHCWBkRY_twsi4PkDTSN3JXZf3TR6KiHjvNGRLvrjPSuHhYnofiAGLf7R39nOjpu3AHI3L3cQvWeGW4aBsHTD3KNGylVvVWhpeff9HFpCwj2IxrFnRxKg/s1600/untitled.bmp"><img style="display:block; margin:0px auto 10px; text-align:center;cursor:pointer; cursor:hand;width: 320px; height: 225px;" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhesgqcZf7y5E8Abvc-NwDOYMtnHCWBkRY_twsi4PkDTSN3JXZf3TR6KiHjvNGRLvrjPSuHhYnofiAGLf7R39nOjpu3AHI3L3cQvWeGW4aBsHTD3KNGylVvVWhpeff9HFpCwj2IxrFnRxKg/s320/untitled.bmp" border="0" alt=""id="BLOGGER_PHOTO_ID_5509275789513953634" /></a><br /><br />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 .<br />SQL Agent came online .<br />In case if it dos not , then we need to uninstall Native client from ADD Remove Programs and reinstall it .<br /><br />Happy learning<br />AbhayUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-8436612531636428647.post-1992029524986645062010-08-02T16:40:00.001+05:302010-08-02T16:56:35.479+05:30Error 1117 :The request could not be performed because of an I/O device error.Our backups were failing under these conditions :<br /><br />Scenario 1: The System databases plus few user databases are on local disk & few user databases are on LUNs.<br /><br />Scenario 2: The System & user databases are completely on LUNs<br /><br />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 :<br /><br /><br />-> 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 :<br /><br />STATUS_FT_MISSING_MEMBER <br />ERROR_IO_DEVICE<br /><br />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.<br /><br /><br />STATUS_FT_ORPHANING <br />ERROR_IO_DEVICE<br />{FT Orphaning} A disk that is part of a fault-tolerant volume can no longer be accessed.<br /><br /><br />STATUS_DATA_OVERRUN <br />ERROR_IO_DEVICE<br />{Data Overrun} A data overrun error occurred.<br /><br />STATUS_DATA_LATE_ERROR <br />ERROR_IO_DEVICE<br />{Data Late} A data late error occurred.<br /><br /><br />STATUS_IO_DEVICE_ERROR <br />ERROR_IO_DEVICE<br />The I/O device reported an I/O error<br /><br />STATUS_DEVICE_PROTOCOL_ERROR <br />ERROR_IO_DEVICE<br />A protocol error was detected between the driver and the device.<br /><br /><br />STATUS_DRIVER_INTERNAL_ERROR <br />ERROR_IO_DEVICE<br />An error was detected between two drivers or within an I/O driver.<br /><br /><br />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"<br /><br />If you are running backup jobs you might also get this error -1073548784 .<br />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 .<br /><br /><br />Action plan :<br />-----------------<br />--try to take backup of another database located remotely and of near about same size . I mean around 20GB.<br /><br />--Run Chkdsk on this drive or ask someone to do that and see if the consistency errors come up .<br /><br />--Create a similar database on another external drive like this one and take the backup .<br /><br /><br />Conclusion :<br />---------------<br />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 .<br /><br />Now the ball is in your court how you explain this to the client :) .<br /><br />Hapy LearningUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-8436612531636428647.post-59335779572570224262010-07-27T15:56:00.000+05:302010-07-29T14:16:57.511+05:30Msg 8914, Level 16, State 1, Line 1 -> Incorrect PFS free space information for pageMsg 8914, Level 16, State 1, Line 1<br />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.<br /><br /><br />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).<br /><br />I tried a lot of things on it like :<br /> -> I Rebuilt the clustered index with and without LOB_COMPACTION option .<br /> -> DBCC page shows is fill factor 100<br /> -> Changed the fill factor to 100 explicitly<br /> -> Ran dbcc updateusage<br /> -> changed the compatibility level to 90<br /> -> changed the fill factor to to 99 ,50 etc <br />Nothing helped .The profiler did not show much (my intention was to know what checkdb is doing internally).<br /><br />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 .<br /><br />On the restored database Ran DBCC checkdb with repair_allow_data_loss .<br />It fixed the issue without harming the data .Finally Ran the same on the Sharepoint database and it resolved the issue .<br /><br />Hope this gives you the confidence to run the repair_allow_data_loss for this issue .<br />But remember , almost every time if you run it with repair_allow_data_loss you will end up loosing the data .So be careful .<br /><br />This situation was AN EXCEPTION and you can safely use this option of checkdb.<br /><br />Root cause :<br />Microsoft says that <br />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.<br /><br />Regards<br />AbhayUnknownnoreply@blogger.com3tag:blogger.com,1999:blog-8436612531636428647.post-11749568054271421192010-07-26T17:28:00.000+05:302010-07-26T17:30:08.260+05:30Finding the last date when the LOG/FULL/DIFF/FILEGROUP backup was taken for all the databasesHi Guys , <br />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) .<br />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. <br /><br /><br /><br />/*<br />Script : Last_bckp.sql<br />Author : Abhay Chaudhary, <br />Date : 26th JUL, 2010<br />Purpose : Collecting SQL Server 2000/2005/2008 last backup taken information.<br />Requirements : Do a CTRL+F and change the <DBNAME> to the DB where you want to <br /> create the object.<br />Suggestions : hi_abhay78@yahoo.co.in<br />Version : 1.0<br />*/<br /><br /><br />USE <DBNAME><br />set nocount on<br />if not exists (select * from <dbname>..sysobjects where name ='bckp_types' and type ='S')<br />begin <br />create table <dbname>..bckp_types (num int identity(1,1),type varchar(1),bkp_name varchar(20))<br />insert into <dbname>..bckp_types (type,bkp_name) values ('D','Full backup')<br />insert into <dbname>..bckp_types (type,bkp_name) values ('L','Log Backup')<br />insert into <dbname>..bckp_types (type,bkp_name) values ('F','Filegroup backup')<br />insert into <dbname>..bckp_types (type,bkp_name) values ('I','Differential backup')<br />end <br />go<br /><br />Declare @loop int<br />select @loop= max(num) from bckp_types<br />While (@loop !=0)<br />begin <br />Select 'last ' + bkp_name +' taken details.' from bckp_types where num=@loop<br />declare @bk_type varchar(1)<br />select @bk_type = type from bckp_types where num=@loop<br /><br />SELECT s.name 'database Name',<br /> b.backup_finish_date 'last backup date',<br /> bmf.physical_device_name 'location of backup'<br /> FROM master..sysdatabases s LEFT OUTER JOIN msdb..backupset b ON s.name = b.database_name<br /> INNER JOIN msdb..backupmediafamily bmf ON b.media_set_id = bmf.media_set_id<br /> WHERE s.name <> 'tempdb'<br /> AND b.backup_finish_date = (SELECT MAX(backup_finish_date)<br /> FROM msdb..backupset<br /> WHERE database_name = b.database_name<br /> AND type = @bk_type) <br /> ORDER BY s.name<br /><br />set @loop=@loop-1<br />end<br />go<br />Drop table <dbname>..bckp_types<br /><br /><br />Happy Learning ...<br />AbhayUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-8436612531636428647.post-58928657801067062192010-07-16T16:39:00.000+05:302010-07-16T16:51:11.390+05:30SQLServer Error: 848, SQL Network Interfaces: The system detected a possible attempt to compromise security.We faced a strange but simple issue yesterday and as usual I would like to share it with you .<br /><br /><br /><br />Situation :<br />-------------<br />SQL server 2005 SP2<br />Windows Server 2003 SP2<br />Cluster : Yes 2 node A-P cluster<br /><br />Service account of SQL Server Agent service and SQL Server service were same .SQL Server is Clustered .<br /><br />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.<br /><br />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 .<br /><br />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 .<br /><br />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 .<br /><br />Then we checked the SQL Agent logs and found this :<br /><br />[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] <br />[298] SQLServer Error: 848, Cannot generate SSPI context [SQLSTATE HY000] <br />[382] Logon to server '(local)' failed (ConnAttemptCachableOp)<br /><br />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.<br />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.<br /><br />We then found out the the account was locked under which SQL Server and agent were still running.<br /><br />Once the account got unlocked at the AD ,the jobs ACTUALLY started working . <br /><br />To me it looks like a bug in design and i have logged it on the CONNECT :<br />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<br /><br />hope it helos you to resolve your issue .Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-8436612531636428647.post-32141235324951039582010-06-19T20:32:00.000+05:302010-06-19T20:45:36.401+05:30Data auditing in SQL ServerThere are 2 ways we can audit the SQL Server events to the tracefile (people call it audit log file).<br /><br />- 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 <br /> not take the changes made manually .<br /><br />- Creating our own trace for selected events and columns .Please check BOL for it .<br /><br />In case you want to go through the second option and that is to create our own trace please see the demo below:<br /><br />Step 1 <br />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 .<br /><br />declare @TraceIdOut int<br />exec sp_trace_create @TraceIdOut OUTPUT,6, N'c:\test_Yasir'<br />PRINT @TraceIdOut<br /><br />declare @On bit<br />SET @On = 1<br />exec sp_trace_setevent @TraceIdOut, 14, 6, @On<br />exec sp_trace_setevent @TraceIdOut, 14, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 14, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 14, 9, @On<br />exec sp_trace_setevent @TraceIdOut, 14, 10, @On<br />exec sp_trace_setevent @TraceIdOut, 15, 6, @On<br />exec sp_trace_setevent @TraceIdOut, 15, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 15, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 15, 9, @On<br />exec sp_trace_setevent @TraceIdOut, 15, 10, @On<br />exec sp_trace_setevent @TraceIdOut, 20, 6, @On<br />exec sp_trace_setevent @TraceIdOut, 20, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 20, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 20, 9, @On<br />exec sp_trace_setevent @TraceIdOut, 20, 10, @On<br /><br /><br />exec sp_trace_setevent @TraceIdOut, 104, 1, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 3, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 6, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 10, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 11, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 14, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 22, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 26, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 35, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 8, @On<br /><br />exec sp_trace_setevent @TraceIdOut, 107, 1, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 3, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 6, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 10, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 11, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 14, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 22, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 26, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 35, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 8, @On<br /><br />exec sp_trace_setevent @TraceIdOut, 106, 1, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 3, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 6, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 10, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 11, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 14, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 22, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 26, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 35, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 8, @On<br /><br /><br />exec sp_trace_setevent @TraceIdOut, 105, 1, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 3, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 6, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 10, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 11, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 14, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 22, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 26, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 35, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 8, @On<br /><br /><br />Step 2 :<br /><br />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<br /><br />sp_trace_setfilter 3,3,0,0,1<br /><br />In this example I have set the filter on databaseid (to 1 which is master) in traceid 3 <br /><br />Step 3:<br />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 .<br />Then start the trace (which is 1) using sp_trace_setstatus<br /><br />example :<br />sp_trace_setstatus 3,1<br /><br />Here traceid is 3 and staus is 1 <br />Further , if you want to add or remove any event use sp_trace_setevent after stopping the trace using sp_trace_setstatus<br /><br /><br />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.<br />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 :<br /><br />1) I have added the datetime in the file name .So it will create a unique file each minute.<br />2) I have encapsulated the query into an SP and pinned it to SQL Server startup.<br /><br />So now <br /><br />step 1 would be <br /><br />create proc audit_trace as<br />declare @TraceIdOut int<br />Declare @D1 nvarchar(30)<br />Declare @D2 nvarchar(30)<br />Declare @D3 nvarchar(30)<br />Declare @D4 nvarchar(30)<br />Declare @D5 nvarchar(30)<br />Declare @trace_name nvarchar(256)<br /><br />SELECT @D1=DATENAME(Day, GETDATE())<br />SELECT @D2=DATENAME(month, GETDATE())<br />SELECT @D3=DATENAME(year, GETDATE())<br />SELECT @D4=DATENAME(hour, GETDATE())<br />SELECT @D5=DATENAME(minute, GETDATE())<br /><br />set @trace_name='c:\trace_'+@d1+'_'+@d2+'_'+@d3+'_'+@d4+'_'+@d5+'_'<br />print @trace_name<br /><br />--set @trace_name = 'c:\trace_'+@trace_date+'.trc'<br />--print @trace_name<br />exec sp_trace_create @TraceIdOut OUTPUT,6, @trace_name<br />PRINT @TraceIdOut<br /><br />declare @On bit<br />SET @On = 1<br />exec sp_trace_setevent @TraceIdOut, 14, 6, @On<br />exec sp_trace_setevent @TraceIdOut, 14, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 14, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 14, 9, @On<br />exec sp_trace_setevent @TraceIdOut, 14, 10, @On<br />exec sp_trace_setevent @TraceIdOut, 15, 6, @On<br />exec sp_trace_setevent @TraceIdOut, 15, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 15, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 15, 9, @On<br />exec sp_trace_setevent @TraceIdOut, 15, 10, @On<br />exec sp_trace_setevent @TraceIdOut, 20, 6, @On<br />exec sp_trace_setevent @TraceIdOut, 20, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 20, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 20, 9, @On<br />exec sp_trace_setevent @TraceIdOut, 20, 10, @On<br /><br /><br />exec sp_trace_setevent @TraceIdOut, 104, 1, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 3, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 6, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 10, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 11, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 14, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 22, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 26, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 35, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 104, 8, @On<br /><br />exec sp_trace_setevent @TraceIdOut, 107, 1, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 3, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 6, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 10, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 11, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 14, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 22, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 26, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 35, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 107, 8, @On<br /><br />exec sp_trace_setevent @TraceIdOut, 106, 1, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 3, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 6, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 10, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 11, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 14, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 22, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 26, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 35, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 106, 8, @On<br /><br /><br />exec sp_trace_setevent @TraceIdOut, 105, 1, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 3, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 6, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 10, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 11, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 14, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 22, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 26, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 35, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 8, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 7, @On<br />exec sp_trace_setevent @TraceIdOut, 105, 8, @On<br /><br />/*adding the SP to execute at SQl Server startup */<br />exec sp_procoption N'audit_trace', 'startup', 'on'<br /><br />Step 2 and 3 will be same as mentioned above in the begening .<br /><br />Disadvantage <br />-------------<br />Simple ..Its resource consuming .Do not add a lot of columns in the trace .Do specifically what you want to audit.<br />It entirely depends what all columns you are auditing.<br />You need to keep the instance under testing phase and monitor the resource consumption due to tracing .<br />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).Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-8436612531636428647.post-14608789419781482572010-06-19T20:21:00.000+05:302010-06-19T20:24:11.274+05:30Using WMI and SQL Agent to fire low memory threshold alert ...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 .<br /><br />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 ....<br /><br /><br />/*******************************************************************************************<br />* This script will create an Alert to Monitor Physical RAM reaching a low threshold.<br />* The alert will run a job and the job will enter data in a table.<br />*******************************************************************************************/<br /><br />/* Step 1: creating the table to capture the Event information */<br /><br />USE Master<br />GO<br /><br />IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[memory]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)<br />DROP TABLE [dbo].[memory]<br />GO<br /><br />CREATE TABLE [dbo].[memory] (<br />[PostTime] [datetime] NOT NULL default (getdate()) ,<br />[computerName] sql_variant Not Null ,<br />[RecordID] [int] IDENTITY (1,1) NOT FOR REPLICATION NOT NULL,<br />[Flag] [int] NOT NULL CONSTRAINT [DF_MEMORY_Flag] DEFAULT ((0)),<br />) ON [PRIMARY]<br />GO<br /><br />CREATE INDEX [Memory_IDX01] ON [dbo].[memory]([recordid]) WITH FILLFACTOR = 100 ON [PRIMARY]<br />GO<br /><br />/*Step 2 : Creating the Job that will enter values into the table we created above*/<br />/*Service account and sql operator option are optional*/<br /><br />USE [msdb]<br />GO<br /><br />IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'Capture memory Event')<br />EXEC msdb.dbo.sp_delete_job @job_name = N'Capture Memory Event', @delete_unused_schedule=1<br /><br />GO<br />--DECLARE @ServiceAccount varchar(128)<br />--SET @ServiceAccount = N'<job_owner_account>'<br />--DECLARE @SQLOperator varchar(128)<br />--SET @SQLOperator = N'<sql_agent_operator>'<br /><br />BEGIN TRANSACTION<br />DECLARE @ReturnCode INT<br />SELECT @ReturnCode = 0<br /><br />IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)<br />BEGIN<br />EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'<br />IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback<br />END<br /><br />DECLARE @jobId BINARY(16)<br />EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Capture Memory Event', <br />@enabled=1, <br />@notify_level_eventlog=2, <br />@notify_level_email=3, <br />@notify_level_netsend=0, <br />@notify_level_page=0, <br />@delete_level=0, <br />@description=N'Job for responding to memory events', <br />@category_name=N'[Uncategorized (Local)]', <br />--@owner_login_name=@ServiceAccount, <br />--@notify_email_operator_name=@SQLOperator, <br />@job_id = @jobId OUTPUT<br /><br />IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback<br /><br />/*Step 3: Insert graph into LogEvents*/<br /><br /><br />EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert data into LogEvents', <br />@step_id=1, <br />@cmdexec_success_code=0, <br />@on_success_action=1, <br />@on_success_step_id=0, <br />@on_fail_action=2, <br />@on_fail_step_id=0, <br />@retry_attempts=0, <br />@retry_interval=0, <br />@os_run_priority=0, @subsystem=N'TSQL', <br />@command=N'<br />declare @@server sql_variant<br />select @@server =serverproperty (''machinename'')<br /><br />INSERT INTO memory (<br />PostTime, <br />Computername<br />)<br /><br />VALUES (<br />GETDATE(), <br />@@server)<br />', <br />@database_name=N'master', <br />@flags=0<br /><br />IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback<br />EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1<br />IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback<br />EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'<br />IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback<br />COMMIT TRANSACTION<br /><br />GOTO EndSave<br /><br />QuitWithRollback:<br />IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION<br />EndSave:<br />GO<br /><br />/*Creating the alert and associating it with the Job to be fired */<br /><br />USE [msdb]<br />GO<br /><br />IF EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to Memory_event')<br />EXEC msdb.dbo.sp_delete_alert @name=N'Respond to memory_event'<br /><br />GO<br /><br />DECLARE @server_namespace varchar(255)<br />SET @server_namespace = N'\\.\root\Cimv2\'<br /><br />EXEC msdb.dbo.sp_add_alert @name=N'Respond to memory_event', <br /> @message_id=0, <br /> @severity=0, <br /> @enabled=1, <br /> @delay_between_responses=0, <br /> @include_event_description_in=0, <br /> @category_name=N'[Uncategorized]', <br /> @wmi_namespace=N'\\.\root\Cimv2', <br /> @wmi_query=N'SELECT * FROM __InstanceModificationEvent WITHIN 10 WHERE TargetInstance ISA ''Win32_PerfFormattedData_PerfOS_Memory'' AND TargetInstance.AvailableBytes > 256', <br /> @job_name='Capture memory Event' ;<br /><br />--EXEC msdb.dbo.sp_add_notification @alert_name=N'Respond to memory_event', @operator_name=N'Test', @notification_method = 1<br />--GO<br /><br />--/* Step 5: Create a stored proc for sending the [Create_user] information as .CSV file */<br /> <br />--Create proc [dbo].[Deadlock_rpt] <br />--as<br />--DECLARE @SQL varchar(Respond to memory_event2000)<br />--DECLARE @date varchar (2000)<br />--DECLARE @File varchar(1000)<br />--select @date= convert(date,GETDATE())<br />--SET @SQL = 'select * from [Create_user] where flag = 0'<br />--SET @File = '[Create_user] report'+@date+'.csv'<br /> <br />--EXECUTE msdb.dbo.sp_send_dbmail<br />--@profile_name = 'test',<br />--@recipients = 'your email.com',<br />--@subject = 'low memory threshold reached...',<br />--@body = '***URGENT***Attached please find the low memory threshold report',<br />--@query =@SQL ,<br />--@attach_query_result_as_file = 1,<br />--@query_attachment_filename = @file,<br />--@query_result_header = 1,<br />--@query_result_separator = ' ',<br />--@query_result_no_padding = 1,<br />--@query_result_width = 32767 <br /><br /><br />--/* Step 6: Changing the flag to 1 so that next time this information is not sent*/ <br />--update dbo.[Create_user] set flag = 1 where flag = 0<br />--goUnknownnoreply@blogger.com2