SQL Server – Achieving minimally logged inserts into partitioned table with a computed column as the partitioning key

A while ago, I started a thread on SQLServerCentral discussing tactics to go about partitioning a huge table in respect to index alignment.  During the discussion, Steve Hall pointed out a great way of working with cycling partitions using a computed column that meant some of the maintenance aspects of partition archiving were a nice doddle.  I’ve since incorporated this tactic as a standard method to partition tables that have a rolling window of archiving, and for the most part it works very nicely.

Recently, we’ve had a requirement to move another large heap table into a partitioning structure to ease maintenance on the table, and ensure we aren’t just storing unneeded data forever.  Afterall, querying a 98GB heap with around half a billion rows just isn’t a good idea anyway!  In this instance, however, the old data needed migrating into the new table, which seemed to create a bit of a problem in terms of the logging level. Continue reading

Advertisements

SQL Server – Use Powershell to find what trace flags are running

Bit of a quick post this morning, continuing the theme of using powershell to standardise your SQL Server estate.  This one is on using PS to collect data on which global trace flags are running on your servers.

Continue reading