The Curious Engineer

All blogs / Databases

La bodeguita de Nacho

learn.microsoft.com/en-us/archive/blogs/ialonso

SQL Server Troubleshooting Stories - "y una ración de bravas"

DatabasesMicrosoft blogsMSDN archive

’10’15’20’25
Posts
161 from 13 Nov 2007 to 18 May 2015
Posting
archived, read-only
Kind
personal blog
Feed
https://learn.microsoft.com/en-us/archive/blogs/ialonso/feed.xml

2015 8

  1. 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!
  2. 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?
  3. SQL Server 2005 upgrade to SQL Server 2014 and compatibility levels
  4. How to identify whether a database has participated in a cross-database transaction or in a distributed transaction
  5. Why is everyone classified in the internal workgroup?
  6. 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
  7. 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”
  8. Do the 10GB DB size limit in SQL Server Express SKUs also apply to TempDB?

2014 15

  1. 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?
  2. Standard edition of SQL Server supports only a 2 node cluster, but will it support multi-subnet clustering or do we need Enterprise edition?
  3. What’s that HTDELETE wait type?
  4. Applying DELAYED_DURABILITY = FORCED on TEMPDB
  5. What events and columns are analyzed by SQL Server Upgrade Advisor from a SQL Trace .trc file?
  6. RESTORE VERIFYONLY: How does it check for available space on destination devices?
  7. Why is the compatibility level of the database that supports Data Collection set to 100 even in SQL Server 2012 or SQL Server 2014?
  8. What’s the optional_spid in sys.dm_exec_plan_attributes?
  9. Finding the list of invalidated plans in cache: Is that even possible?
  10. When doing a database restore, what actions is the engine taking after it gets past the 100%?
  11. NEWSEQUENTIALID() defect in SSMS. Wasn’t it fixed already in SQL 2012 RTM?
  12. Is SQL 2014 Buffer Pool Extension an Enterprise Edition only feature?
  13. Encrypted text in Profiler for unencrypted modules
  14. Is eXtreme Transaction Processing (ie In-Memory OLTP) Supported?
  15. Log initialization on Azure Blob

2013 1

  1. Premier TechTracks SQL Server just released

2012 50

  1. How to set the default transaction isolation level server wide?
  2. Even if some client tools in SQL seem to be forward compatible with later versions, you may be missing something important
  3. Are you one of those rare birds whom, while running batches that involve distributed queries, have encountered any of the following errors?
  4. Why does restoring a database needs TempDB?
  5. What’s the meaning of different States for error 9002?
  6. FAQ around sys.dm_db_index_usage_stats
  7. Inaccurate values for “Currently allocated space” and “Available free space” in the Shrink File dialog for TEMPDB only
  8. 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?
  9. How do you explain that sys.dm_os_spinlock_stats.backoffs keeps increasing and no sqlos.spinlock_backoff event is produced?
  10. What’s the maximum level of recursion for the hash iterator before forcing bail-out?
  11. Who, ME, an almighty Sysadmin getting a permission denied error?
  12. 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?
  13. GetDataSourceContents will always return null for the Password member of the instance of DataSourceDefinition it returns
  14. 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
  15. 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
  16. Be careful with setting the SessionTimeout system property to a value out of the [10, 2147483647] range (SSRS 2008 R2)
  17. Is this OperationNotSupportedNativeModeException exception expected?
  18. Understanding when, in a PowerShell script, the call of a method produces output upon return from the call and when it doesn’t
  19. The unexpected client impersonation scenario that seemed to be only justified by the effects of black magic
  20. The improved guide on How to Turn on or off the Report Server Web Service (SSRS 2008 R2)
  21. Where is the table cardinality taken from when that information doesn’t match in the different statistics associated to an object?
  22. About the transaction-level snapshot "start" time (for statement reconciliation)
  23. How does sys.dm_server_memory_dumps work in SQL Server 2008 R2?
  24. What does the DTC Calls performance counter from the Exec Statistics object accounts for?
  25. How does the ODBC driver for SQL handles conflicting security settings between DSN and connection string?
  26. SQL Server changes behavior of the file autolocation invoked during the planning phase of a full restore with REPLACE clause
  27. Farewell DBCC PRTIPAGE
  28. Misconceptions around connection pooling
  29. How the density and distribution of data in the leading key column of an index affects the degree of parallelism chosen for index operations targeting that index?
  30. DBCC DBRECOVER finally removed from SQL Server
  31. The seven reasons why Auto Update Stats event will not trigger despite how many modifications affect any of the tables involved in a compiled plan?
  32. How the query execution engine calculates the threshold it uses to detect inaccurate cardinality estimation and fire the inaccurate_cardinality_estimate extended event?
  33. Want your SQL Server to simply ignore the AUTO_CLOSE setting, for all open databases for which it has been enabled?
  34. What’s in Enterprise only? (Change Data Capture)
  35. What’s in Enterprise only? (Transparent Data Encryption)
  36. VDI Backups and Backup Compression Default
  37. What’s in Enterprise only? (Database Snapshot)
  38. What’s in Enterprise only? (Support for Data Compression & Vardecimal Storage Format)
  39. What’s in Enterprise only? (Up to 8 CPU sockets)
  40. Backup Compression and Checksum
  41. ALTER TABLE SWITCH fails with 4947 even when all the publicly exposed metadata for the indexes in source and target tables perfectly match
  42. What’s in Enterprise only? (Fuzzy Lookup Transformation in Integration Services)
  43. What’s in Enterprise only? (Hot add CPU)
  44. Glitch in SMO causes an error when you attempt to create an FTS Catalog/Table Population Schedule under very specific circumstances
  45. Reducing the number of queue readers via MAX_QUEUE_READERS parameter might take long time to take effect if there are many messages in the queue
  46. Duration of schema locks for certain DML operations might not work as expected
  47. Expected behavior of INSERT into views with Instead Of Insert Triggers
  48. What’s in Enterprise only? (View Substitution Implicitly Allowed)
  49. What’s in Enterprise only? (honor Lazy Schema Validation option in a Linked Server)
  50. What’s in Enterprise only? (Updatable Distributed Partitioned Views)

2011 28

  1. What’s in Enterprise only? (Online Index Operations)
  2. What’s in Enterprise only? (Partitioned Tables and Indexes)
  3. Why is the column named “Microsoft SQL Server 2005 XML Showplan” despite the version producing it is not 2005?
  4. Under which circumstances would Activity Monitor display duplicate rows in the Processes section?
  5. Clicking on the XML link containing a query plan doesn’t display the graphical plan but opens the XML editor instead
  6. What’s in Enterprise only? (Concurrent, Shared, or Merry-go-round Scans)
  7. How can you drop the witness of a mirroring session when the partners have been dismantled?
  8. The Read Ahead that doesn’t count as Read Ahead
  9. What’s in Enterprise only? (Log manager uses locked pages for log cache buffers)
  10. Programmatically enabling/disabling SQL Native Client Protocols
  11. What is the expected behavior from an attempt to enable a trace flag which is not defined in the targeted version of the product?
  12. What’s in Enterprise only? (Running DBCC CHECK* in parallel)
  13. Attempt to grow all files in one filegroup and not just the one next in the autogrowth chain using trace flag 1117
  14. What’s in Enterprise only? (Fast Recovery)
  15. How true is it that Missing Indexes feature cannot gather stats for more than 500 missing index groups?
  16. What’s in Enterprise only? (up to 2TB of memory)
  17. What’s in Enterprise only? (Backup Mirroring)
  18. Never ever set up a Database Mirroring session across different SQL Server Editions (SKU)
  19. Launching Database Mirroring Monitor without having to go through SSMS
  20. What’s in Enterprise only? (Online Restore)
  21. What happens to my orphan Profiler traces?
  22. What’s in Enterprise only? (Database Tuning Advisor support for recommending Indexed Views)
  23. What’s in Enterprise only? (SQL Server Audit)
  24. What’s in Enterprise only? (Fuzzy Grouping Transformation in Integration Services)
  25. What’s in Enterprise only? (Resource Governor)
  26. What’s in Enterprise only? (Large Memory Pages)
  27. What’s in Enterprise only? (Soft-Numa configuration)
  28. Understanding the value of the Enterprise Edition, one feature at a time

2010 11

  1. Expressions associated to the connection manager used by ASExecuteDDLTask (Analysis Services Execute DDL Task) are not evaluated as one would expect
  2. SQL Server Profiler 2008 (or 2008 R2) not showing expected columns for given events while setting up the definition of a new trace
  3. Microsoft.SqlServer.Management.Smo.PartitionFunction.ScriptCreate generates syntactically invalid statements for non-numeric data types
  4. Assumptions about SPIDs from where deadlock events will be produced
  5. How can you configure CLR Enabled setting from the UI?
  6. Watch your CREATE CERTIFICATE *_DATE clauses syntax order!
  7. How and when are the pages pushed down to the sparse files of a Database Snapshot?
  8. Reason why the SLOTS XECustomizableAttribute of package0.synchronous_bucketizer & package0.asynchronous_bucketizer seem to be interpreted incorrectly?
  9. How to tell from ShowPlan output if (optimize for (unknown)) was used?
  10. Weird behavior of transaction log growth in SQL Server 2005, 2008, and R2
  11. Starting with SQL Server 2008 DBCC PAGE displays INVALID COLUMN VALUE where it used to display the quiet not a number string representation (-1.#QNANs) in previous versions

2008 9

  1. TMP/TEMP/USERPROFILE environment variables: the closer they point to, the better
  2. Next week (Tue 26th/Wed 27th), you cannot miss it! See you at Microsoft TechDays {The Evolution Show} in Madrid
  3. What does it mean that BACKUP TABLE / RESTORE TABLE are not "yet" implemented?
  4. I just had to re-publish a previous post because I had made an important mistake
  5. A tale about solving "Cannot generate SSPI context" and later "[DBNETLIB][ConnectionRead(recv()).] General network error. Check your network documentation" in a shot
  6. How an apparently innocent antivirus could kill your SSIS package performance?
  7. SQL Server performance counters' library improvement
  8. Good News!
  9. Scared because SQL Server seems to be running a DBCC CHECKDB on some of your databases on every startup?

2007 39

  1. What's special with the BULK provider when loading LOB character columns using OPENROWSET?
  2. SQL Server 2005 Service Pack 2 failing to install due to an error in Do_sqlGroupMember custom action
  3. Is there any way you can execute an SSIS package whose TransactionOption property is set to Required, without having the MSDTC service running?
  4. Msg 1013: The object "%s" and "%s" in the FROM clause have the same exposed names. Use correlation names to distinguish them.
  5. Why my SQL Server performance counters are showing zeroes when current values are higher?
  6. SQL Server Import and Export Wizard blowing up due to an unexpected error
  7. Be careful with using RegEx on x64 bit boxes
  8. Would you like to be part of what is coming next with Connect?
  9. SQL Server Agent failing to start because "The EventLog service has not been started" (Second Part)
  10. Raw device support and trying to move the data files of a database to a local disk in failover clustered environments
  11. "Query timeout expired" when trying to Preview the data from the OLE DB Source Editor in BIDS
  12. Decreasing the size of a database file through the Management Studio GUI?
  13. SQL Server Agent failing to start because "The EventLog service has not been started";
  14. Is it possible to set MSDB's recovery model to Full?
  15. Are these IO statistics correct?
  16. Internationalization issue with the implementations of IErrorLookup::GetErrorDescription in SQLOLEDB and MSDASQL OLE/DB Providers
  17. Were you aware of the existence of Microsoft Connect portal?
  18. Cannot patch an instance of SQL Server 2005 if there is a mounted drive under the Data folder
  19. SQL Server 2005 setup fails if network name of the NIC includes a comma
  20. SQL Server 2005 hotfix, Cumulative Update or Service Pack installation fails with error 1053
  21. Conflict Viewer's UI becomes practically unusable when run from a French SQL installation on a French Operating System
  22. Would you like to share with us your experience with Microsoft's Customer Support Services?
  23. Error 22022: SQLServerAgent Error: Network problems are preventing connection to the Microsoft Exchange Server computer.
  24. "SQL Execution Error: Timeout expired. The timeout period elapsed..." while opening view from SSMS
  25. Another good reason why I think we should deprecate AutoRun setting from Command Processor
  26. Por acaso voçê nao esta a lêr o meu blog so porque nao esta traduzido ao português?
  27. ¿Hay alguien que esté dejando de leer este blog porque no está traducido al español?
  28. Is it possible to configure SQL Server Agent to connect to SQL Server using SQL Server authentication?
  29. Is SQL Agent manipulating the errors raised by SQL Server, before dumping them to the output file?
  30. Multiple Core and Hyper-Threading enabled CPUs and SQL Server 2005
  31. How much Virtual Address Space can my SQL Server 2000 process see when running under WOW in an x64 OS?
  32. ExecuteCaptureLog doesn't work with ErrorConfiguration
  33. Unexpected "Tuning indexed views requires SQL Server 2005 Enterprise Edition"?
  34. Flat File Provider and Decimal/Numeric data
  35. Order in which DTS Packages are displayed from SQL Server 2005 GUI
  36. Are you suffering from random AdomdConnectionException thrown when trying to connect to a named instance?
  37. You cannot alter one table using Visual Database Tools (VDT) unless you are either a member of db_dbowner or sysadmin
  38. Unable to connect to the registry on the Server (MyOLAPServer) or you are not a member of OLAP Administrators group
  39. I'm logging it!