New fix for SQL 2012 SP1. Fix data loss possibility

On June 10, a new fix released that fixes a bug in SQL 2012 SP1. It’s not included in CU#9 that’s included in SP2 so if you choose to install it, you need to delay SP2 until the first CU#1 for SP2 relaeased.  ​The fix is very important becuase the bug is causing data loss…


Index maintenance script

  Another script that I wanted to share for so much time but didn’t have the commitment to be persisted with my postsJ. Using the built-in SQL Server maintenance plans for index maintenance works well for small databases without HA solutions that are affected by the amount of transaction logs generated. When it comes to…


Get Current Activity in SQL Server instance

 [Update: Version 2.0 uploaded with some bug fixes] When troubleshooting customer’s performance issue that’s happening at the moment, we have many tools out of the box. The good old SQL traces that are not needed for the majority of the cases and if it’s needed then my first option would ne XE if we are…


Viewing encrypted data using Trace

Today morning I got an email from a customer to review some design recommendation. In the recommendation, another DBA claimed that encrypting the data by using EncryptByKey or EncryptByPassPhrase is not secured because we can trace the data and read it as plain text. This is not correct and if you have the patience to…


Select everything from every table in every database

[Caution: This is for testing only if you want to have a cached dataset, will have severe issues in production servers if you just executed it for trial]   We all know the undocumented (then it’s not supported) procedure that executes a code in every database which is sp_msforeachdb And its twin that executes against…


Inequality predicates do not trigger scans

I see sometimes there is an understanding that when using inequality operators in the where clause, this will make the query optimizer decide to do a table scan for the table or index and will not use seek operations. Sometimes this is called non-SARGable predicate. This blog post is to discuss this understanding, but if…


New columns in exec_sessions DMV in SQL 2012

While reproducing the issue with DBCC OpenTran I wrote about earlier this week here I found a pleasant addition to the sys.dm_exec_sessions DMV. In SQL 2012 we have 3 new columns added Open_transaction_count: which shows the number of open transactions for the session whether or not these transactions are reading or writing. So an open…


When DBCC OpenTran doesn’t list all open transactions

Recently I was troubleshooting a customer problem on SQL 2000 instance when there was a hanging session that locking tens of sessions on the server. The customer was suspecting that there was an open transaction for days which causes this problem but when using DBCC OpenTran it doesn’t show any transaction. However when checking sysprocesses…


SQL Server 2008 R2 Express

Good news for independent developers who are using SQL Express as backend database. With SQL Server 2008 R2, SQL Express supports databases up to 10 GB. For more information and download, go to product site  


Free e-book from MS Press

Microsoft Press has just released a new free e-book for SQL Server 2008 R2 ” Introducing Microsoft SQL Server 2008 R2″ Can be downloaded from  //Mohamed..