SSISDB getting large – failure of Retention Window Cleanup

The SSIS Catalog Rentention Period (days) defaults to 365.   The SSIS database can quickly become very large (as a function of SSIS activity and logging levels).

A stored procedure SSISDB.internal.cleanup_server_retention_window is executed by a job SSIS Server Maintenance Job on a nightly schedule at 00:00:00.

(All defaults observed in 2012, v.11.0.5058.0)


This stored procedure deletes rows from internal.operations table that are older than the retention period.  One row in internal.operations represents a package (?) invocation – this table won’t grow too large,  but related detail tables can;  internal.event_messages, internal.event_message_context, internal.operation_messages, internal.executable_statistics being the largest.

The cleanup stored procedure deletes rows from internal.operations tables.  Cascading foreign key constraints delete rows from other tables – some indirectly via internal.executions.  This is operational part of the procedure:

 WHILE (@rows_affected = @delete_batch_size)
     DELETE TOP (@delete_batch_size)
       FROM [internal].[operations] 
       WHERE ( [end_time] <= @temp_date
       OR ([end_time] IS NULL AND [status] = 1 AND [created_time] <= @temp_date ))
     SET @rows_affected = @@ROWCOUNT

Procedure continually fails to complete

The default for @delete_batch_size is 1,000 – and herein lies the problem.  The deletion of a single internal.operations row will cascade to many thousands of rows.  But internal.operations itself is relatively low cardinality.  Assembling such a large deletion set is a time consuming, log-expanding and blocking operation which can fail – and fail continually thereafter.  Although the deletion is sensibly in a loop, 1000 is a large proportion of  internal.operations  rows. As can be seen from the 16GB example above there are only 2.8K rows in the table to start with, so the loop would barely exceed a couple of iterations.    Naturally all this depends on the activity and retention period of the SSIS instance.  But 1,000 seems a lot in any case.

Some posts state that  @delete_batch_size is 10, though not in the versions I have checked.   If the procedure continually fails the solution is to run a copy the code with a smaller batch size to clear the backlog.   I am tempted to reduce the setting in the stored procedure itself – should a backlog of deletions accrue again for whatever reason the deletions will again fail.

The following chart shows the table sizes once the retention period has been reached – in this case, 2.8K rows in internal.operations were reduced to 827.  The projected results on the other large tables is significant.

View Change Tracking Settings

Statements to reveal Change Tracking settings.  This query does not require VIEW DATABASE STATE permission, which is required to display the database properties page in SSMS.

	[database_name] = DB_NAME(database_id) 
from  sys.change_tracking_databases


SSRS Linked Reports

Is it worth the effort?

  • Advantages
    • Can encapsulate a set of defaults. Pre-configurations of a very general report, for example.
  • Disadvantages
    • Links between reports take users to the master source folder.
    • (!) Dynamic parameter defaults don’t survive linking.  Instead the value of those defaults at the time the linked report is created will be saved as literal values.

It is useful to keep “master” reports (.rdl) files in a restricted area and present reports as linked reports for users to browse.  The linked report mechanism allows distinct parameter defaults to be set.

There are some challenges:


Permissions are those of the folder that is linked from.

If the master report contains a subreport, the subreport will be referenced from its original location.  Permissions are not transferred by the linking mechanism to the subreport.  Hence it is necessary to give the user View report permissions (only?) on the subreport.  Do this with a new ‘View report’ user role.

Open Report Action

Linked Reports are also run from their original location.   (Proof:  create a set of reports with Open Report actions established between them.  Create a link from another folder to one only of these reports.  Open the linked report and click the Open Report action object:  the click-through will work, and the new report is being served from the master location.

Sql Server Service Broker (SSSB)

SSSB Endpoint Authentication Certificate

Don’t forget to give this a long lifetime (if security policy allows) by using EXPIRY_DATE, otherwise the certificate expires after a year.

CREATE CERTIFICATE [BrokerCertificateDec15]
  WITH SUBJECT = N'certificate_subject',
       EXPIRY_DATE = N'2025-12-31';

Expiry of the certificate causes the following log entries with source ‘Logon’:

Service Broker login attempt failed with error: ‘Connection handshake failed. The certificate used by this endpoint was not found: Certificate expired. Use DBCC CHECKDB in master database to verify the metadata integrity of the endpoints. State 85.’.  [CLIENT: …

Check the contents of sys.certificates (from master)  for the expired culprit.


  • Create a new certificate, e.g. using the code above. Hence the date in the name.
  • Alter the broker endpoint to use the new certificate:
    ALTER ENDPOINT [Broker] 
    	AUTHENTICATION = CERTIFICATE [BrokerCertificateDec15]


  • This should leave all other endpoint settings unchanged.  Note that the endpoint name is variable.
  • If successful the following log entries appear:
    • The Service Broker protocol transport has stopped listening for connections.
    • Server is listening on [ ….[IP] <ipv4> 4022].
    • The Service Broker protocol transport is now listening for connections.
  • At this stage the broker should be running correctly again.


This page could eventually save hours.


  • Open Report Data toolbar.  CTRL+ALT+D.  (also on view menu if toolbar not in focus)
  • View Hidden Datasets. Right-click the Datasets node.

Performance Monitoring using typeperf.exe


Avoid all use of the Performance Monitor interface by using typeperf.exe (the oddest of names).


The Baselining articles above are thorough.  These are notes on my implementation.

The aim of the articles is to log to a database and then run reports from the database.  A change to the -o switch will log the output to a file instead.

Command Line

Simplest example:

TYPEPERF  -c "\System\Processor Queue Length" -si 5 -sc 4

Emits the “System\Processor Queue Length” counter, four times with 5 second intervals (including 5 seconds before the first counter is emitted).

Which counters are available?

TYPEPERF -q > c:\BitBucket\TypePerfAvailableCounters.txt

Outputs a list of all counters available on the machine to a handy text file.

The only intelligent approach is to collect the counters you are interested in into a text file.  This is the list, aimed at SQL performance monitoring, that I am currently using.

Note on the wildcards.  …PhysicalDisk(*)..  will return counters for all disks (0 C:, 1 E, …) plus an aggregated counter named _Total.  I don’t want the _Total counter; the expression …PhysicalDisk(* *) matches all counters with a space in the identifier – which the disk names have but that the _Total name doesn’t.  Unfortunately the same hack doesn’t work for SQL Server:Locks(*) since most of the lock identifiers don’t have spaces.

\Memory\Available MBytes
\PhysicalDisk(* *)\% Disk Time
\PhysicalDisk(* *)\Current Disk Queue Length
\PhysicalDisk(* *)\Disk Transfers/sec
\PhysicalDisk(* *)\Disk Bytes/sec
\PhysicalDisk(* *)\Disk Reads/sec
\PhysicalDisk(* *)\Disk Writes/sec
\PhysicalDisk(* *)\Avg. Disk sec/Read
\PhysicalDisk(** *)\Avg. Disk sec/Write
\Processor(* *)\% Processor Time
\Processor(* *)\% Privileged Time
\System\Processor Queue Length
\System\Context Switches/sec
\Network Interface(* *)\Bytes Total/sec
\SQLServer:Access Methods\FreeSpace Scans/sec
\SQLServer:Access Methods\Full Scans/sec
\SQLServer:Buffer Manager\Buffer cache hit ratio
\SQLServer:Buffer Manager\Free pages
\SQLServer:Buffer Manager\Page life expectancy
\SQLServer:Latches\Total Latch Wait Time (ms)
\SQLServer:Locks(*)\Lock Timeouts/sec
\SQLServer:Locks(*)\Lock Wait Time (ms)
\SQLServer:Locks(*)\Number of Deadlocks/sec
\SQLServer:Memory Manager\Memory Grants Pending
\SQLServer:Memory Manager\Target Server Memory (KB)
\SQLServer:Memory Manager\Total Server Memory (KB)
\SQLServer:SQL Statistics\Batch Requests/sec
\SQLServer:SQL Statistics\SQL Re-Compilations/sec
\SQLServer:SQL Statistics\SQL Compilations/sec
\SQLServer:General Statistics\User Connections

TYPEPERF  -cf "C:\PerformanceMonitoring\CounterList.txt" -si 15 -sc 4  is a command that will use a CounterList file.

TYPEPERF  -cf "C:\PerformanceMonitoring\CounterList.txt" -si 15 -sc 4 -f SQL -o SQL:DatabaseStatisticsDS!Log1 will log to database using System DSN “DatabaseStatisticsDS” (creating the database if necessary).

You can gather counters from another server by using the -s option.  I haven’t attempted to do that yet; so far I am running typeperf against the local machine and logging the output to a distant one.


User must be member of Performance Log Users group.  No errors raised if they aren’t but some (not all) counters will be zero.


The Baselining SQL Server article Part 1 covers TypePerformance errors (“No valid counters” !) in detail.

Column Naming Conventions

Column Naming Convention

I like lower case with underscores:  company_idfirst_name, telephone_nbr, booking_ref

This is Uncle Joe Celko’s convention as stated in SQL Programming Style (Morgan Kaufmann, 2005), and I accept his justification that Invoices.invoice_nbr  is more readable than Invoices.InvoiceNbr in a dense melee of Sql code.

However, pragmatically, PascalCase is more popular and I find I use it more commonly from choice.

Id/Key column Names

  • Use the singular version of the table followed by Id / Key / Nbr
  • e.g.  table Customers has column CustomerId .

There are a number of reasons for this:  having a bunch of columns all named ‘Id’ (as one popular approach has it) is never helpful.  Another reason is that it makes it practically impossible to search for references.   Searching for ‘CustomerId’ will find definite references; searching for ‘Id’ in a schema full of Ids  is useless.  The same applies to Name, Date, Cost etc used as column names.

Column Name Suffixes

The suffix is a type-and-content indicator.  Probably all columns with the same suffix will be of the similar data type and size and have similar constraints. This is reasonably easy to assert and check using a script or even by eye in a data dictionary.

Column Name Suffixes
Suffix Type Remarks
_abbrv short char or varchar an abbreviation for the item; normally for display where space is limited.
_flag char(1) Constrained to accept only uppercase Y or N.  (if an undefined value is required use _triflag)
_date any date type date or date and time
_id int numeric surrogate key – often the primary key. based on table name: table Companies has company_id
_name varchar proper name e.g. first_name. Use sparingly. name should never be used on its own for a column!
_key normally short varchar or char if natural key is of fixed length (e.g. ISO country codes) natural (normally textual) key. Normally unique except for versioned tables (i.e. tables with effective and obsolete dates).
_ref short char or varchar short textual reference – e.g. initials; company reference code.  Need not be unique.
_perc decimal(5 4) percentage scale 1.  (type specification allows maximum of 999.99%)
_token GUID a row identifier – e.g. case_guid. used for security in HTML links; etc
_triflag char(1) Constrained to accept only uppercase Y or  N or  U  (= Unknown).

 Alternative Conventions

Some other conventions that I have advocated or endured.

The Table-Prefix (c_name) Convention

This was the first convention I adopted (largely in MSAccess) and it has some attractive qualities.

  • All column names are prefixed by (normally) a single letter plus underscore.  Thus all columns in table tblCompany are prefixed with c_ e.g. c_idc_name.
  • Sometimes it isn’t possible to keep the single letter convention (what do you do once you have tblProperty, tblPerson, tblProduct?), so two or even three letter prefixes are acceptable: pn_FirstName etc.
  • Foreign key columns are named differently to the column they relate to.  E.g. tblPerson.pn_CompanyId is foreign key to tblCompany.c_Id.


  1. Every column has a unique name.  If you don’t use fully qualified column names, adding a column can break a view if the column name already exists in one of the other tables referenced – or if someone has used Select *.
  2. It’s easy to see which table you’re referring to if you don’t use fully qualified column names:  c_id is the Company ID and p_id is the Product ID.
  3. You rarely have to alias similarly named columns:  pn_address is distinct from c_address.

It works satisfactorily in MS Access and is quick to use.   Working with recordsets in code it is clear which column is being referenced – you never see an anonymous rst!Address.  Because no aliasing is necessary there is no risk of inconsistent aliases: the column name you see in the code is the name it has in the table.

I wouldn’t choose this convention for a new database; however it has caused no problems in any database where I have used it.  With the advent of Sql Data Tools it is now possible to enforce fully qualified column names and prohibit Select * so database-unique column names are less important.  Intellisense in SSDT and SSMS makes full names (with table aliases) easier to write.  Neither of these are available for Sql in Access.

Prefixing keys with PK_ and FK_

I don’t think this helps.  The primary key is indicated in metadata. The naming convention is that it’s the singularised name of the table with a suffix. Customers.customer_id.  The foreign key is again indicated in metadata.  I don’t think confusing them is common.

I think it has disadvantages

  • It burns column properties into the column. Foreign keys might be created or dropped; the table name is unlikely to change.
  • When including the column in a view the output will be FK_customer_id or PK_customer_id – which you might be tempted to alias.  It’s not relevant to the output that the column is a FK or PK.  In fact, PK_customer_id would be confusing if it were not the PK of the rowset being returned.
  • Many to Many tables:  columns can be both PK and FK at the same time.  The conention just confuses here.
  • It obscures the fact that these columns represent the same attribute.

ORM-Generated Tables with Standard Column Names

I have worked with databases where every lookup table had exactly the same column names: twenty plus tables with the columns NameDescriptionKeyEffectiveDate, and so on.  (It was generated using ORM modelling in Visio for Enterprise Architects.  Which of us wouldn’t be flattered by the name of that program?  Unfortunately it put visual effects above good design – another bete noir; thankfully Microsoft dropped Visio for EA after 2005 and put its energies into the grown-up SSDT, which has no visuals at all!)

In addition to the lookup tables, most fact tables in the database also had a Name and Description column.  Almost every Select from more than one table would require the designer to alias columns  (or forget to – Sql Server  is happy to output two columns with the same name; applications are rarely happy to receive them!).

It seems obvious that there will be less confusion if a column name seen by the application is the same as in the table, and if a column always has the same name wherever it appears.    If a column regularly has to be aliased there is a high risk that different aliases will be used.  Why even raise the possibility?

  • Give a unique name to all columns that are likely to appear together in a Select statement.
  • Never use Name as a column name!  (there are a number of other prohibited names).

I’m happy for multiple tables to have effective_date and obsolete_date, for example, because these will normally only be used in where clauses, where full names will be enforced; they won’t often be returned for multiple tables in a view.

Naturally enough it was this database that suffered an ambiguous column reference following the addition of a column to a table.  This could never have happened with the Table-Prefix convention: perhaps that is a good reason to use it, in spite of the aversion to its Access appearance!


Syntax Formatting for blogs

I imagine that it’s a well trodden path seeking out a satisfactory code or syntax highlighter for wordpress.

You can, just about, paste colour-formatted code from some editors into the wp text editor  (e.g. SSMS to Outlook Email to WordPress), but it creates a mess of the HTML.  Not pretty at all.

Current preference:

  • Crayon Syntax Highlighter.   Nice to use;  line numbers; expansion to new window; code-block size limits.  The main drawback seems to be that the colouring is rather random.