A SQL Server database consists of at least two types of file on disk – a transaction log file, and at least one data file where the data and indexes persist. The best practice is to have one transaction log on a disk (or disks) dedicated to database transaction logs. For data files, it is strongly recommended leave the primary filegroup for database metadata operations, and create at least one secondary filegroup. This secondary filegroup should be set to be default so new data objects are created here, and having four datafiles within this filegroup would be a good start. These four datafiles should be identically sized, perhaps placed on several disks depending on the requirements of the application. All these files should have autogrowth enabled to allow SQL Server to extend as the database grows – the autogrowth setting should be the same across all the datafiles, and be proportionate for the size of the database.
As data is put into this database, proportional fill will spread the data relatively evenly over these evenly sized datafiles. A traditional challenge for a DBA is that if autogrowth is enabled, one datafile will extend – and the other three will not, leading to imbalance. And as the extended datafile has more free space, proportional fill will place more data in that datafile, and the imbalance will grow.
In SQL Server 2016 or later, you can set SQL to autogrow all datafiles to grow together – this is set at the individual filegroup level (not datafile) within a specified database. Unfortunately, it is not exposed within Management Studio in either SQL Server 2016 or SQL Server 2017.
ALTER DATABASE TEST MODIFY FILEGROUP SECONDARY AUTOGROW_ALL_FILES
The filegroup property ‘AUTOGROW_ALL_FILES’ has been set.
Can I truncate a table that is participating in transaction replication?
If you think about it conceptually, the table exists within the publication database, and the table is expected to exist on any databases that are subscribing to the publication. SQL Server Replication works by monitoring the database transaction log for inserts, updates, and deletes on objects that are replicated, and by transmitting those changes to subscribers. Truncate is a minimally logged transaction that essentially empties the table, but leaves the schema intact.
So, if I try to truncate a table that is in a published database by running TRUNCATE TABLE TRADES, I get an error:
Msg 4711, Level 16, State 1, Line 1
Cannot truncate table ‘trades’ because it is published for replication or enabled for Change Data Capture.
If I convert the truncate into a DELETE FROM TRADES statement, this works perfectly – and is replicated to any subscribers.
If I try the same TRUNCATE TABLE TRADES statement on a subscriber database, this also works perfectly.
MVP Gail Shaw has produced an excellent video tutorial on SQL Server Parameter Sniffing, available on the Pluralsight network. The video was released in August 2016, and covers SQL Server 2016. At 90 minutes long, it’s nicely concise – and it covers what parameter sniffing is, identifying and capturing parameter sniffing via techniques such as DMVs, Extended Events, and the Query Store; and naturally covers some of the options available to work
Microsoft Dynamics CRM makes extensive use of filtered views – these are standard views in the database, but they prevent unauthorised users (including DBAs) from seeing sensitive data by the way they are coded. During several migrations of Microsoft Dynamics CRM from both the 2011 and 2013 versions to 2015, my project teams have hit performance issues which center on filtered views and the new Cardinality Estimator in SQL Server 2014. The Cardinality Estimator is the part of the database engine that works out how to actually do the work for a particular query, and it was largely based on a code base written for SQL Server 7.0.
This week I found, by chance, that on a couple of servers when I tried to run a Standard Report such as Disk Usage in SQL Server Management Studio (SSMS), it would crash. Both SQL Servers were SQL Server 2014, running on top of Windows Server 2012 R2. The actual application crash was that the Visual Basic Command Line Compiler had stopped working.
I had to pause for thought – whilst I’m an experienced database specialist, I don’t normally have to troubleshoot application crashes.
I initially suspected it might be a permissions issue, but it turned out to be a bug with Windows Server 2012 R2 (together with Windows 8)
It’s a relatively new course from February 2016, and Kathi explains – and demonstrates – the double-hop problem on SQL Server 2014 in a Azure lab. It’s certainly true that SSRS is the most common occurence where database specialists hit the double-hop problem, but it’s not exclusively a SSRS issue. Kathi also outlines some possible security workarounds (and makes the caveats clear), and then Kathi runs through how NTLM and Kerberos work. My overall conclusion is that this course is a good, yet relatively brief, tutorial about Kerberos for SQL Server professionals
The March update for Visual Studio Dev Essentials has announced that SQL Server 2014 Developer Edition is now free for Dev Essentials members! It was always great value for non-production development and testing, but now – it really shows how Microsoft SQL Server is really gunning for Oracle’s database customers.
Exciting news! Starting today, SQL Server 2014 Developer Edition is available as a free download for Dev Essentials members. Build almost any kind of database application and unlock insights faster with a complete business intelligence solution. SQL Server 2014 was designed to work in hybrid environments, making it easier for you to build back-up and disaster recovery solutions with Azure, as well as delivering a consistent experience across on-premises and cloud.
I’ve had a process for several years that imports approximately 20 million rows into a staging table from a 3GB CSV flat file. As these things do, it has evolved over time. It started as a bulk import statement, but – as errors started to regularly kill the process – it evolvedinto a SSIS package. Without too much difficulty, I was able to set up a data flow task so that rows that generate errors would be diverted. Unfortunately, the package suddenly started failing with an DTS_W_MAXIMUMERRORCOUNTREACHED error.
The column delimiter for column “Column 15” was not found.
Error: 0xC0202055 at Import, Flat File Source : The column delimiter for column “Column 15” was not found.
Error: 0xC0202092 at Import, Flat File Source : An error occurred while processing file “E:\Data\csv-import.csv” on data row 1.
Error: 0xC0047038 at Import, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Flat File Source returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
In the run up to the Data Driven event, Microsoft stunned the SQL Server community by announcing that they’d be offering SQL Server on a Linux platform by 2017. There was a solid demonstration during the event, but in the following days it became clear that it would initially be just the core database engine, and none of that fancy Business Intelligence (BI) stuff.
Microsoft also announced their “Break free from Oracle” licensing offer – basically, if you sign up before June, you’ll potentially be able to get Microsoft SQL Server 2016 for free as a swap-out of your Oracle licences. Of course, the devil is in the detail – for instance, buying Software Assurance is a part of the offer.
Kalen Delaney is coming back to SQLBits XV in May in Liverpool – Kalen’s session will be on the thursday, and is called SQL Server Query Plans – Reuse or Recompile. In Kalen’s words, it will cover the plan cache, how and when SQL Server decides whether to reuse or recompile a plan, and how to influence SQL Server’s choice.
I saw Kalen give a session in 2015 in London at the Excel conference centre – she’s a great speaker, obviously knows her stuff, and is well worth going to see.