Trace Flag 1117 Growth and Contention

In this video I talk about Trace Flag 1117 and how it was designed to help keep data file growth with multiple data files in TempDB consistent.  You’ll see how growth occurs if the files are disproportionate and also the effect it has on TempDB contention with SQL Server special pages (PFS, GAM, SGAM). For…

0

SQL Server Workgroup Cluster FCM Errors

Background One of the new features of SQL Server 2016 is the ability to use SQL Server with Failover Cluster in a workgroup rather than joined to Active Directory. When working with SQL Server and Failover Clustering in a workgroup, many of the abilities that are normally used with Active Directory are no longer available,…

0

SQL 2016 - Temporal Tables - Triggers and Historical Data Tidbits

Hello again and welcome back to the series on Temporal Tables! Today we will take a look at two common questions.  What happens when I put a trigger on a Temporal Table and can I back populate the historical table?   Setup First thing’s first, let’s setup a demo.   Triggers Now, let’s add a…

1

Troubleshooting CDC enabling failure – Part 2

  In previous blog, I discussed the CDC failure due to guest user disabled in MSDB. My customer also experienced another issue at the same time:   Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 622 Could not update the metadata that indicates table [dbo].[Table_Name] is enabled for Change Data Capture. The failure occurred…


Troubleshooting CDC enabling failure – Part 1

My customer had recently experienced 2 interesting CDC issues and I would like to share the troubleshooting process and root cause for these issues today.   The issue: During the weekend, my customer tried to promote some changes to production server and while they tried to enable CDC on production server, they first encountered a…


SSISDB Reporting with Power BI

Back in 2012 when the project deployment model came out for SSIS, many questions were asked regarding its advantages vs the old legacy deployment model. I still see quite a few customers today who use the old legacy package deployment model. The upgrade/conversion process from package deployment to project is quite straight-forward, and I encourage…


Quick Tip - Shift+Alt for multiple line edits

We’ve all been in the position where we are doing research and need to add commas or single quotes around a list and it’s a pain to go through one line at a time to add them.   While working with Scott Epperly, Support Engineer with Microsoft, I saw him quickly add a comma before about 10…

2

Hidden Gem TRY_CONVERT to troubleshoot data type conversion

Many of us have experienced a dreaded data type conversion error when doing tasks like importing files, converting to new database schemas, etc. Which record caused the problem? I have used a few tricks in the past for this, but TRY_CONVERT makes it easy. This was added in SQL 2012, but recently I have recommended…

1

SSIS: Capturing PerfMon Counters During Package Execution

While troubleshooting SSIS package performance, having the ability to view the performance monitor counters can be handy for determining where bottlenecks may be occurring. Usually, however, this is a very reactive request. The package is slow, so perfmon counters are enabled. The package is then run and *hopefully* the issue reoccurs. A lesser known feature…