I had blogged about debugging deadlocked schedulers earlier for SQL Server 2008 R2 and below releases. Since there have been some fundamental changes in SQL Server 2012, I thought it would be a good idea to post about debugging the same scenario on SQL Server 2012 instances.
Older posts:
Debugging Deadlocked Schedulers Part 1
Debugging Deadlocked Schedulers Part 2
Over the past few months, I have discussed the feasibility of enabling transaction replication for customer databases on various occasions. Every time I end up writing queries to answer certain questions about the database… the most common one being if the tables that need to be replicated have primary keys.
So I finally decided to write a T-SQL script which will help me answer the most common questions asked about a database while deciding on the feasibility of enabling transaction replication.
The script doesn’t capture information like workload, performance metrics etc. to decide if the replication workload (snapshot and distribution agent) can be supported on the existing hardware and resources available in the environment.
My take on the matter is that this information is required only once we have figured out if transactional replication can be enabled on the database or not. Eg. If the main tables that need to be replicated do not have primary keys, then the question of resource availability and hardware capability is moot point!
The script below checks the following:
1. Existing of primary keys on the tables in the database. Objects (articles) without primary keys cannot be replicated as part of a transactional replication publication.
2. If the database has transparent database encryption enabled. The subscriber database is not automatically enabled for TDE in such a scenario.
3. Constraints, primary keys, triggers and identify columns which have NOT FOR REPLICATION bit set and which objects do not. You might choose to replicate or not replicate some of these objects. However, you need to be aware of what you are replicating.
4. Tables having ntext, text and image columns as there are special considerations for handling DMLs on such columns.
5. XML schema collections present in the database. Modifications to the XML Schema collection are not replicated.
6. Tables with sparse column sets as they cannot be replicated.
7. Objects created using WITH ENCRYPTION option. Such objects cannot be replicated either.
As always, in case you think that there are additional checks that could be included in the script, then please leave a comment on my blog and I will add the same into the script.
I had written a script a while back which would set the TCP/IP port for a SQL Server instance. Before you start throwing brick bats at me…. Powershell was not in existence in those days and yes…. doing the same tasks in Powershell is much easier! Phew… Now let me get back to my story!
One of my colleagues told me that the script was failing due with the following error message:
SQL_PortChange.vbs(52, 1) Microsoft VBScript runtime error
: Object required: ‘objOutParams‘
Now the above error is not the most intuitive of error messages considering the fact the object is being assigned a value in my VBscript. With a little bit of troubleshooting, we figured out that the RunAs Administrator (it can really catch you off-guard) option was not used to launch the command prompt.
So when running such VBscripts, do not forget to use RunAs Administrator option!
Now let us look under the hood a bit! I naturally was curious as to why the access denied message was not being thrown back to the user. I used Process Monitor to trace the registry activity of cscript.exe and wmiprvse.exe which actually works in the background to perform the tasks mentioned in the VBscript. I found that there were Access Denied messages in the Process Monitor trace but they were not being bubbled up to the user (see screenshot below)!
As you can see above, the access denied was on the SQL Server TCP/IP registry key and the WBEM keys. Since the registry key could not be read, the object was not created. And which is why we got the weird error listed above.
I thought this would be a good issue to blog on in case some one else ran into a similar issue!
TroubleshootingSQL 