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
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
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
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
One of the many improvements, shipped with the SQL Server 2014, made to the iterators used in the...
Date: 07/23/2014
My PFE colleague Sam Mesel posted the following information a few days ago on an internal...
Date: 07/23/2014
Went to a customer this week to assist them with the risk assessment and work planning to...
Date: 07/23/2014
“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
My answer to a question asked by Vedran Kesegić asked earlier this week in the MCMs group: Queries...
Date: 07/16/2014
About three weeks ago, Kimberly approached me because she was seeking for a way to...
Date: 07/16/2014
This was a question my admired Greg Low posted on the SQL Server MCMs discussion group a few days...
Date: 06/06/2014
I have recently received this question from different sources, so I think it’s worth documenting why...
Date: 05/21/2014
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
A colleague came up with this situation: ************ I am tracing some SQL activity on a SQL 2008...
Date: 05/21/2014
Someone recently asked under which circumstances SQL Server 2014 would return false when...
Date: 05/21/2014
One fellow MCM posted the other day the following question about log initialization on Azure Blob...
Date: 05/21/2014
And I’ve created and produced the first video on the SQL Server track. It is called SQL Server...
Date: 01/17/2013
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
Or the chicken and egg problem when attempting to restore a corrupted model database as it was...
Date: 10/24/2012
I just happened to find that when the log manager raises error 9002 “The transaction log for...
Date: 10/15/2012
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
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
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
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
A couple of weeks ago, during a visit to a customer whose reporting platform, which is architected...
Date: 07/07/2012