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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo Date: 05/21/2014

Is crash recovery run serially or in parallel and if so how are CPUs, threads used? If I have 20 databases, for example…

This contents is based on the response I gave to Pat Martin, a Principal Premier Field Engineer and...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo Date: 01/17/2013

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...

Author: Nacho Alonso Portillo Date: 12/12/2012

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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo Date: 11/15/2012

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...

Author: Nacho Alonso Portillo Date: 11/12/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 -...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo Date: 10/15/2012

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...

Author: Nacho Alonso Portillo Date: 10/08/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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo Date: 09/13/2012

Another dirty trick played by an antivirus

A PFE colleague from Italy, recently exposed the following situation in an internal distribution...

Author: Nacho Alonso Portillo Date: 09/10/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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo Date: 07/25/2012

What’s in PREEMPTIVE_OS_FILEOPS wait type?

This one wait type is one of the many hundreds which came up new with SQL Server 2008. As my...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo 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...

Author: Nacho Alonso Portillo Date: 07/07/2012

Next>