La bodeguita de Nacho
SQL Server Troubleshooting Stories - "y una ración de bravas"
Planning to set up an Always On Availability Group configuration to move data data from a 2012 primary replica into a 2014 “readable” secondary replica? Think it twice!
I had a customer who wanted to use AOAG, not as a HA/DR solution but as one to replicate data to...
Date: 05/18/2015
How is @@DBTS expected to behave on an Always On Availability Group secondary replica, a Database Mirroring mirror, or a database which is being created out of a restored backup and is in standby or in-recovery state?
A coworker recently posted the following to an internal forum: “We are redirecting read load of our...
Date: 05/08/2015
SQL Server 2005 upgrade to SQL Server 2014 and compatibility levels
A database currently attached to an instance of SQL Server 2005 could be either backed up (or...
Date: 04/17/2015
How to identify whether a database has participated in a cross-database transaction or in a distributed transaction
When customers evaluate using Database Mirroring or AlwaysOn Availability Groups as a HA/DR solution...
Date: 04/10/2015
Why is everyone classified in the internal workgroup?
A PFE colleague recently posted a question describing what seemed like a weird and unexpected...
Date: 02/25/2015
sys.dm_exec_requests showing negative values for total_elapsed_time, wait_time, or any other column it exposes as an integer (int) data type
In the case of that DMV (and probably in many other) the problem is that the internal value which...
Date: 02/06/2015
Microsoft SQL Server 2008 R2 Best Practice Analyzer fails with “Engine - Login does not exist or is not a member of the System Administrator role”
If the Windows account you are using for SQL BPA to connect and analyze an instance of SQL Server...
Date: 02/04/2015
Do the 10GB DB size limit in SQL Server Express SKUs also apply to TempDB?
No, it doesn’t. The CheckSize function from the file manager class, which is called when a file is...
Date: 02/04/2015
Got a read only database and the contents of sys.dm_db_index_usage_stats.last_user_update contains today’s date. How’s that possible?
Any attempt to update the index (via any DML statement) updates that index usage statistics as part...
Date: 10/03/2014
Standard edition of SQL Server supports only a 2 node cluster, but will it support multi-subnet clustering or do we need Enterprise edition?
Even though the Features Supported by the Editions of SQL Server 2014 doesn’t have any indication on...
Date: 10/03/2014
What’s that HTDELETE wait type?
One of the many improvements, shipped with the SQL Server 2014, made to the iterators used in the...
Date: 07/23/2014
Applying DELAYED_DURABILITY = FORCED on TEMPDB
My PFE colleague Sam Mesel posted the following information a few days ago on an internal...
Date: 07/23/2014
What events and columns are analyzed by SQL Server Upgrade Advisor from a SQL Trace .trc file?
Went to a customer this week to assist them with the risk assessment and work planning to...
Date: 07/23/2014
RESTORE VERIFYONLY: How does it check for available space on destination devices?
“How does RESTORE VERIFYONLY checks for space on destination drives when a drive isn’t...
Date: 07/19/2014
Why is the compatibility level of the database that supports Data Collection set to 100 even in SQL Server 2012 or SQL Server 2014?
A question posted by Greg Low to the MCMs distribution group recently: I was surprised that...
Date: 07/16/2014
What’s the optional_spid in sys.dm_exec_plan_attributes?
My answer to a question asked by Vedran Kesegić asked earlier this week in the MCMs group: Queries...
Date: 07/16/2014
Finding the list of invalidated plans in cache: Is that even possible?
About three weeks ago, Kimberly approached me because she was seeking for a way to...
Date: 07/16/2014
When doing a database restore, what actions is the engine taking after it gets past the 100%?
This was a question my admired Greg Low posted on the SQL Server MCMs discussion group a few days...
Date: 06/06/2014
NEWSEQUENTIALID() defect in SSMS. Wasn’t it fixed already in SQL 2012 RTM?
I have recently received this question from different sources, so I think it’s worth documenting why...
Date: 05/21/2014
Is SQL 2014 Buffer Pool Extension an Enterprise Edition only feature?
No, it’s not. It’s on Standard too. If an attempt is made to enable it on any other edition, you’ll...
Date: 05/21/2014
Encrypted text in Profiler for unencrypted modules
A colleague came up with this situation: ************ I am tracing some SQL activity on a SQL 2008...
Date: 05/21/2014
Is eXtreme Transaction Processing (ie In-Memory OLTP) Supported?
Someone recently asked under which circumstances SQL Server 2014 would return false when...
Date: 05/21/2014
Log initialization on Azure Blob
One fellow MCM posted the other day the following question about log initialization on Azure Blob...
Date: 05/21/2014
Premier TechTracks SQL Server just released
And I’ve created and produced the first video on the SQL Server track. It is called SQL Server...
Date: 01/17/2013
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...
Date: 11/26/2012
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...
Date: 11/15/2012
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 -...
Date: 11/08/2012
Why does restoring a database needs TempDB?
Or the chicken and egg problem when attempting to restore a corrupted model database as it was...
Date: 10/24/2012
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...
Date: 10/15/2012
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...
Date: 10/08/2012
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...
Date: 10/08/2012
Can I install SQL Server 2012 Standard Edition to run in a server with more than 4 sockets or more than 16 cores? Are there any limitations?
I’ve found myself answering these questions more than three times only during the the last week. All...
Date: 09/13/2012
How do you explain that sys.dm_os_spinlock_stats.backoffs keeps increasing and no sqlos.spinlock_backoff event is produced?
A PFE colleague from the UK approached me yesterday with the following concern: Hi Nacho, I may need...
Date: 09/06/2012
What’s the maximum level of recursion for the hash iterator before forcing bail-out?
This is a question I was asked recently whose answer I had to look in the code because it wasn’t...
Date: 09/05/2012
Who, ME, an almighty Sysadmin getting a permission denied error?
This is the story of someone who claimed to be a member of the sysadmin fixed server role and, when...
Date: 07/26/2012
Which database and language the domain user goes by default when is a member of multiple windows groups, and these groups have logins created in SQL with different default databases and languages?
When a domain user is in multiple Windows groups, and these group logins have a different default...
Date: 07/25/2012
GetDataSourceContents will always return null for the Password member of the instance of DataSourceDefinition it returns
Even though current and past versions of the documentation have never explicitly said so, for...
Date: 07/22/2012
Adjusting the maximum number of worker threads per SQL Server Agent’s subsystem when processors are added or removed from the machine after the instance of SQL Server was installed
Inside MSDB, there exists a system table named dbo.subsystems which is populated the first time SQL...
Date: 07/22/2012
Reason for the mismatch between sys.dm_os_memory_nodes.virtual_address_space_committed_kb and aggregated sys.dm_os_memory_clerks.virtual_memory_committed_kb by memory_node_id
A PFE colleague presented the following scenario and asked for the justification of what seemed to...
Date: 07/22/2012
Be careful with setting the SessionTimeout system property to a value out of the [10, 2147483647] range (SSRS 2008 R2)
Most of the functionality implemented in the web service that requires getting the value of any...
Date: 07/08/2012
Is this OperationNotSupportedNativeModeException exception expected?
If your instance of Reporting Services is not installed in SharePoint integrated mode and you see...
Date: 07/07/2012
Understanding when, in a PowerShell script, the call of a method produces output upon return from the call and when it doesn’t
A colleague asked the following a few days ago: <MESSAGE> With the following rows I’m trying...
Date: 07/07/2012
The unexpected client impersonation scenario that seemed to be only justified by the effects of black magic
For whatever reason whose understanding has been lost over time it has been the case since SQL...
Date: 07/07/2012
The improved guide on How to Turn on or off the Report Server Web Service (SSRS 2008 R2)
A couple of weeks ago, during a visit to a customer whose reporting platform, which is architected...
Date: 07/07/2012