What OLEDB providers should you expect to show up under the Providers node in SSMS?

  The short answer is: Those returned by master.dbo.xp_enum_oledb_providers. But let’s dig deeper and try to give you the detailed description you deserve. The triggering reason why I’m writing about this is because a colleague described the following situation in one of our internal lists: I designed a OLE DB Provider, and want to register…


How to set the default transaction isolation level server wide?

Last week I was involved in a discussion about the default transaction isolation level used by the transaction objects in WCF’s services (Serializable) versus the one used by the transaction objects in Entity Framework for SQL Server (ReadCommitted) whose details I’m not going to cover in this post.   At a given point during that…


Even if some client tools in SQL seem to be forward compatible with later versions, you may be missing something important

Today, a colleague exposed the following case:   In the execution plan I do see some exclamatory marks on Sort, Hash Joins operators but there is no warning information available. I am using SQL Server 2012.  How do I interpret the exclamatory marks?       Here goes my explanation to this problem: New execution…


Does larger TEMPDB log file affect the performance of startup of SQL Server?

I invested some time today investigating what SQL Server does with the initialization of TEMPDB’s log file, because a question was raised last week during Robert Davis session on TEMPDB at SQL PASS Summit in Seattle, and I wasn’t sure about what the answer would be. The reason why people is afraid the size of…


Are you one of those rare birds whom, while running batches that involve distributed queries, have encountered any of the following errors?

3988 – New transaction is not allowed because there are other threads running in the session. 3930 – The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.   If so, I would appreciate you send me an email to ialonso@microsoft.com telling me your story….


Why does restoring a database needs TempDB?

Or the chicken and egg problem when attempting to restore a corrupted model database as it was presented by my MCM colleague Gail Shaw the other day in our MCM discussion group. This is the case he presented us with: Hi all Something I’ve been puzzling over recently. Restore database appears to need or use…


What’s the meaning of different States for error 9002?

I just happened to find that when the log manager raises error 9002 “The transaction log for database ‘%s’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases” the State can vary and is loaded with meaning. The value reflects whatever was the holdup reason…


How much is crash recovery parallelized? In which order are databases recovered?

Following is the answer I provided to the interesting question asked by my colleague Pat Martin about the insides of crash recovery in SQL Server.   The iterator used to scan the catalog for the databases that need startup and recovery returns them sorted by database id. So, if you want a database to start…


FAQ around sys.dm_db_index_usage_stats

For those of you who may not be familiarized with this object, sys.dm_db_index_usage_stats is one of the many Dynamic Management Views built into SQL Server. This one specifically falls into the category index related DMVs, and it returns counts of different types (scans, seeks, and lookups) of index operations, triggered by user or system initiated…


Inaccurate values for “Currently allocated space” and “Available free space” in the Shrink File dialog for TEMPDB only

Last week I went to a customer who showed me the following weird information.   He opened SSMS (SQL Server Management Studio) 2008 R2 and connected to one particular instance of SQL Server 2008 R2 in which he observed this behavior we wasn’t able to reproduce with any other.   From the Object Explorer window,…