Deadlock information from "system health" extended events

In SQL 2012 and above versions we can reterive deadlock information without enabling trace flags. Steps as below.

1) Go to Management -> Extended Events -> Sessions -> system_health

2) Right click on “package0.event_file”

3) Select “View Target Data..”

4) Click on “Filters” from task bar as below

5) Apply filter on Name field with “xml_deadlock_report”

6) Select on any deadlock based on timestamp, select deadlock tab for more info.

Fixing an SSRS Password Error while Changing Credentials

While applying the domain accounts suddenly an error arose.

“The password is not valid. Please retype the password”,

This appeared whenever a domain account name was entered in user name field. I tried the other options to reset the error and allow me to test the connection but was repeatedly prompted for credentials with the same error even if the correct credentials are entered.

Refer my post below for more details

http://www.sqlservercentral.com/articles/SSRS/155071/

SQL Server Crashed EXCEPTION_ACCESS_VIOLATION

I recently got escalation to investigate a SQL instance that was unexpectedly shutdown due to access violation, no other error or message was logged in Application/System/SQL error log except a memory dump.

Exception Address = 00000000776CE660 Module(ntdll+000000000004E660)

Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION

Access Violation occurred reading address 0000000000000000

So I decided to review the dump file using windbg and public symbols. Stack shown me the very last function was OraOLEDBButl11 which is Oracle driver for linked server so it was clear that distributed query is culprit here and right after OS started to release the heap memory and exception dispatched which is very strange behaviour, while searching for OraOLEDBButl11 I found a reference that gave me more info about Oracle driver related bug, for more info refer below reference.

Reference:

https://support.microsoft.com/en-us/help/2295405/sql-server-service-crashes-when-you-run-an-oracle-linked-server-query

Justify the Remote access requirement for Biztalk Log Shipping

I have been in a situation where our Security compliance team requested to prove that remote access configuration is indeed mandatory to setup log shipping for a Biztalk database. As all we know this configuration option is used to execute remote stored procedures from local server or allow access from remote server to execute a local stored procedure. Of course this has to be accompanied with a linked server that provide RPC_OUT services.

Refer below my article for more details.

http://www.sqlservercentral.com/links/337157/360187

Top Processes consuming Memory and CPU

It is at time important to find out what other processes are doing in term of CPU and memory utilization, following commands are helpful for ad-hoc or reporting purpose.

Top 5 Memory consuming processes:

Powershell > get-wmiobject WIN32_PROCESS | Sort-Object -Property ws -Descending|select -first 5|Select processname, @{Name="Mem Usage(MB)";Expression={[math]::round($_.ws / 1mb)}},@{Name="ProcessID";Expression={[String]$_.ProcessID}},@{Name="UserID";Expression={$_.getowner().user}}

Memory consuming processes more than 100MB

tasklist /FI "MEMUSAGE ge 100000"



CPU consuming processes:

wmic path Win32_PerfFormattedData_PerfProc_Process where PercentProcessorTime!=0 get Name,PercentProcessorTime







Top CPU consuming processes that are running for more than 5 minutes:

tasklist /fi "cputime gt 00:05:00"

DBCC CHECKDB failed due to invalid object id

This anomaly persist due to deleting objects that are not removed from system tables properly due to some reasons which is in fact ignored in legacy SQL version (2000) however catch up by DBCC in later versions (Most probably after migration or upgrade).

Error Description:

Error 3854: [Microsoft][ODBC SQL Server Driver][SQL Server]Attribute (parent_object_id=1977058079) of row (object_id=1993058136) in sys.objects has a matching row (object_id=1977058079) in sys.objects (type=S ) that is invalid

Workarounds:

1) Ignore the errors, though database consistency check will not able to complete.

2) Export the data to new database using import/export utility.

3) Remove the anomaly from system base table, as system table (sysschobjs) is not directly accessible so DAC access is required, steps as below.

- Enable configuration (allow update and DAC)
- Restart SQL in single user mode with –m
- As both objects (1977058079, 1993058136) are in same table along with syskeys table so delete the child record 1993058136.
- delete  FROM sys.sysschobjs WHERE ID=1993058136;
- remove –m parameter and restart SQL in normal model
- disable allow update and DAC

How to find 3-party DLLs loaded in SQL Server

There are cases where heap memory get corrupted and SQL Server generally does not use heap, for this kind of case, it is possibly caused by third party DLL which did not manage heap well.

ListDlls tool is one of solution, which will help us get all loaded DLL in SQL Server, it will help us figure out if there is any 3rd-party DLLs loaded in SQL Server. You could download the tool here,

https://technet.microsoft.com/en-us/sysinternals/bb896656.aspx

And the command to use it is,

1.Please open a command prompt with run as administrator. And then switch to the folder of ListDlls.exe
2.Then run “ListDlls -v sqlservr.exe” will list all loaded DLLs of SQL Server.

SQL Server configuration manager is not opening

SQL server configuration manager relies on WMI provide. I found that the WMI provider failed to load with error 0x80080005 (Server execution failed).

I failed to rebuild wmi database.

wmiprvse /regserver     --> No entry point error

mofcomp hang

DCOM security issue or wmiprvse.exe failed to work.

Workaround:

1) Check DCOM permission.

2) Replace wmiprvse.exe with the one copied from good server.

C:\Windows\System32\wbem\wmiprvse.exe

3) Successfully rebuild the WMI database.

4) Then we re-compiled the SQL server WMI MOF file by using below command:

mofcomp "C:\Program Files\Microsoft SQL Server\90\Shared\sqlmgmproviderxpsp2up.mof"

5) Refer below updates if you encounter error while compiling.

After that, the SQL configuration manager opens successfully.

Updates: 

Recently encounter another issue while recompiling WMI MOF and it gave below error.

When I checked the consistency of WMI repository, it shows inconsistent.

As a workaround, I stopped the WMI service and salvage the WMI repository as below.

After that it shows me that it is consistent now, at this point recompilation of MOF works fine.

Note: This command (Winmgmt /salvagerepository) will take the content of the inconsistent repository and merge it into the rebuilt repository if it is readable.

If the above doesn’t work, then run:

Winmgmt /resetrepository

Reference:

https://blogs.technet.microsoft.com/askperf/2009/04/13/wmi-rebuilding-the-wmi-repository/

How to handle orphan transactions in SQL Server

-2 blocking means Orphaned distributed transactions, in this transaction become in-doubt and MSDTC lost control over it, these transactions may or may not be listed in MSDTC (depend upon issues occurred).



In order to find the session that is handling request for this UOW (Unit of Work), refer to below query that will lead to session id which can provide detailed information of activity. There are few ways to release the transaction, one from SQL (Use KILL <UOW> command) and another from MSDTC, right click on UOW and select resolve. I would suggest to cross verify with session_id and UOW before resolving in MSDTC.

 select distinct request_session_id from sys.dm_tran_locks where request_owner_guid='{8241D0EE-364B-4315-A293-D46F9659CAEF}’

 If MSDTC do not list any transaction we can still find with below query. Let me know if any questions.

 select request_owner_guid from sys.dm_tran_locks where request_session_id = -2