SQL Server Data File Autogrowth Management

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.


The filegroup property ‘AUTOGROW_ALL_FILES’ has been set.

Can I truncate a table that is participating in transaction replication?

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.