Tag Archives: Microsoft

Business Central / Dynamics NAV DateFormula SQL

The DateFormula DataType in BC / NAV allows calculates to date to be stored in Business Central. For example “5D” = “5 Days from date X”

These are not easily available in SQL due to how Microsoft stores the data.

Using this, I’ve created the following Function to convert BC/NAV’s DateFormula using SQL. This function allows you to pass in the date, plus the dateformula. This then returns the value using similar function to what is used in Business Central / Dynamics NAV.

CREATE FUNCTION dbo.CalcDate (@date date, @datefilter nvarchar(32))
RETURNS date

BEGIN

SET @datefilter = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@datefilter,  CHAR(1), 'C'), CHAR(2), 'D'),CHAR(3), 'WD'),CHAR(4), 'W'),CHAR(5), 'M'), CHAR('6'), 'Q'), CHAR('7'), 'Y')


declare @returneddate date = null;

declare @cleanedfilter nvarchar(35) = '';

SET @cleanedfilter = STUFF(REPLACE(REPLACE(CASE
  WHEN LEFT(@datefilter, 1) NOT IN ('+', '-') THEN '+'
  ELSE ''
END + @datefilter
, '+', '|+'
)
, '-', '|-'
)
, 1, 1, ''
) + '|||';


declare @p1 nvarchar(10) = '';
declare @p2 nvarchar(10) = '';
declare @p3 nvarchar(10) = '';


SELECT
  @p1 = LEFT(@cleanedfilter, CHARINDEX('|', @cleanedfilter, 0) - 1)
 ,@p2 = SUBSTRING(@cleanedfilter
  , CHARINDEX('|', @cleanedfilter, 0) + 1
  , (CHARINDEX('|', @cleanedfilter, CHARINDEX('|', @cleanedfilter, 0) + 1) - 1) - (CHARINDEX('|', @cleanedfilter, 0))
  )
 ,@p3 = REPLACE(SUBSTRING(@cleanedfilter
  , CHARINDEX('|', @cleanedfilter, CHARINDEX('|', @cleanedfilter, 0) + 1) + 1
  , 999
  )
  , '|'
  , ''
  );

--Calculate Value

SET @returneddate = (SELECT
    CAST(v3.retp3 AS DATE) 
  FROM (SELECT
      @p1 AS p1
     ,@p2 AS p2
     ,@p3 AS p3) p
  OUTER APPLY (VALUES (
  CASE
    WHEN SUBSTRING(p1, 2, 1) = 'C'                -- <Prefix><Unit>
    THEN CASE SUBSTRING(p1, 3, 2)
        WHEN 'D' THEN @date
        WHEN 'WD' THEN @date
        WHEN 'W' THEN DATEADD(WEEK, DATEDIFF(WEEK, 0, @date) +
          CASE
            WHEN LEFT(p1, 1) = '+' THEN 1
            ELSE 0
          END, 0)
        WHEN 'M' THEN CASE
            WHEN LEFT(p1, 1) = '+' THEN eomonth(@date)
            ELSE DATEADD(DAY, 1, eomonth(@date, -1))
          END
        WHEN 'Q' THEN DATEADD(DAY, CASE
            WHEN LEFT(p1, 1) = '+' THEN -1
            ELSE 0
          END, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, @date) +
          CASE
            WHEN LEFT(p1, 1) = '+' THEN 1
            ELSE 0
          END, 0))
        WHEN 'Y' THEN DATEADD(YEAR, DATEDIFF(YEAR, 0, @date) +
          CASE
            WHEN LEFT(p1, 1) = '+' THEN 1
            ELSE 0
          END, 0)
        ELSE ''
      END
    WHEN ISNUMERIC(SUBSTRING(p1, 2, 1)) = 1       -- <Number><Unit>
    THEN CASE
        WHEN RIGHT(p1, 2) = 'WD' THEN DATEADD(DAY, CAST(REPLACE(p1, 'WD', '') AS INT), @date)
        ELSE CASE RIGHT(p1, 1)
            WHEN 'D' THEN DATEADD(DAY, CAST(REPLACE(p1, 'D', '') AS INT), @date)
            WHEN 'W' THEN DATEADD(WEEK, CAST(REPLACE(p1, 'W', '') AS INT), @date)
            WHEN 'M' THEN DATEADD(MONTH, CAST(REPLACE(p1, 'M', '') AS INT), @date)
            WHEN 'Q' THEN DATEADD(QUARTER, CAST(REPLACE(p1, 'Q', '') AS INT), @date)
            WHEN 'Y' THEN DATEADD(YEAR, CAST(REPLACE(p1, 'Y', '') AS INT), @date)
          END
      END
    WHEN ISNUMERIC(SUBSTRING(p1, 2, 1)) = 0       -- <Unit><Number>
    THEN CASE
        WHEN SUBSTRING(p1, 2, 2) = 'WD' THEN DATEADD(DAY, RIGHT(p1, 1) - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, @date) -
          CASE
            WHEN LEFT(p1, 1) = '-' THEN 1
            ELSE 0
          END, 0))
        ELSE CASE SUBSTRING(p1, 2, 1)
            WHEN 'D' THEN DATEADD(DAY, ABS(CAST(REPLACE(p1, 'D', '') AS INT)) - 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @date) +
              CASE
                WHEN ABS(CAST(REPLACE(p1, 'D', '') AS INT)) < DAY(@date) THEN 1
                ELSE 0
              END +
              CASE
                WHEN SIGN(CAST(REPLACE(p1, 'D', '') AS INT)) = -1 THEN -1
                ELSE 0
              END, 0))
            WHEN 'W' THEN DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(WEEK, ABS(CAST(REPLACE(p1, 'W', '') AS INT)) - 1, datefromparts(YEAR(@date) +
              CASE
                WHEN ABS(CAST(REPLACE(p1, 'W', '') AS INT)) <= DATEPART(WEEK, @date) THEN 1
                ELSE 0
              END +
              CASE
                WHEN SIGN(CAST(REPLACE(p1, 'W', '') AS INT)) = -1 THEN -1
                ELSE 0
              END, 1, 1))), 0)
            WHEN 'M' THEN datefromparts(YEAR(@date) +
              CASE
                WHEN ABS(CAST(REPLACE(p1, 'M', '') AS INT)) <= MONTH(@date) THEN 1
                ELSE 0
              END +
              CASE
                WHEN SIGN(CAST(REPLACE(p1, 'M', '') AS INT)) = -1 THEN -1
                ELSE 0
              END, ABS(CAST(REPLACE(p1, 'M', '') AS INT)), 1)
            WHEN 'Q' THEN datefromparts(YEAR(@date) +
              CASE
                WHEN ABS(CAST(REPLACE(p1, 'Q', '') AS INT)) <= DATEPART(QUARTER, @date) THEN 1
                ELSE 0
              END +
              CASE
                WHEN SIGN(CAST(REPLACE(p1, 'Q', '') AS INT)) = -1 THEN -1
                ELSE 0
              END, ((ABS(CAST(REPLACE(p1, 'Q', '') AS INT)) - 1) * 3) + 1, 1)
            WHEN 'Y' THEN datefromparts(ABS(CAST(REPLACE(p1, 'Y', '') AS INT)), 1, 1)
          END
      END
    ELSE ''
  END
  )
  ) AS v1 (retp1)
  OUTER APPLY (VALUES (
  CASE RIGHT(p2, 1)
    WHEN 'D' THEN DATEADD(DAY, CAST(REPLACE(REPLACE(p2, 'W', ''), 'D', '') AS INT), retp1)
    WHEN 'W' THEN DATEADD(DAY, CAST(REPLACE(p2, 'W', '') AS INT) * 7, retp1)
    WHEN 'M' THEN DATEADD(MONTH, CAST(REPLACE(p2, 'M', '') AS INT), retp1)
    WHEN 'Q' THEN DATEADD(QUARTER, CAST(REPLACE(p2, 'Q', '') AS INT), retp1)
    WHEN 'Y' THEN DATEADD(YEAR, CAST(REPLACE(p2, 'Y', '') AS INT), retp1)
    ELSE retp1
  END
  )
  ) AS v2 (retp2)
  OUTER APPLY (VALUES (
  CASE RIGHT(p3, 1)
    WHEN 'D' THEN DATEADD(DAY, CAST(REPLACE(REPLACE(p3, 'W', ''), 'D', '') AS INT), retp2)
    WHEN 'W' THEN DATEADD(DAY, CAST(REPLACE(p3, 'W', '') AS INT) * 7, retp2)
    WHEN 'M' THEN DATEADD(MONTH, CAST(REPLACE(p3, 'M', '') AS INT), retp2)
    WHEN 'Q' THEN DATEADD(QUARTER, CAST(REPLACE(p3, 'Q', '') AS INT), retp2)
    WHEN 'Y' THEN DATEADD(YEAR, CAST(REPLACE(p3, 'Y', '') AS INT), retp2)
    ELSE retp2
  END
  )
  ) AS v3 (retp3));

return
  @returneddate


END


GO

This function can be used as follows (today is the 9th January).

select dbo.calcdate(getdate(),'-6D')

SQL – Check When All Databases on Server Last Auto Grow

Running out of disk space? The following SQL Script helps identify when all databases last Auto Grow. (Script adapted from here)

DECLARE @current_tracefilename VARCHAR(500);
DECLARE @0_tracefilename VARCHAR(500);
DECLARE @indx INT;
DECLARE @database_name SYSNAME;
SELECT
  @current_tracefilename = path
FROM sys.traces
WHERE is_default = 1;
SET @current_tracefilename = REVERSE(@current_tracefilename);
SELECT
  @indx = PATINDEX('%\%', @current_tracefilename);
SET @current_tracefilename = REVERSE(@current_tracefilename);
SET @0_tracefilename = LEFT(@current_tracefilename, LEN(@current_tracefilename) - @indx) + '\log.trc';
SELECT
  DatabaseName
 ,Filename
 ,(Duration / 1000) AS 'TimeTaken(ms)'
 ,StartTime
 ,EndTime
 ,(IntegerData * 8.0 / 1024) AS 'ChangeInSize MB'
 ,ApplicationName
 ,HostName
 ,LoginName
FROM ::fn_trace_gettable(@0_tracefilename, DEFAULT) t
LEFT JOIN sys.databases AS d
  ON (d.NAME = @database_name)
WHERE EventClass >= 92
AND EventClass <= 95
AND ServerName = @@servername
ORDER BY t.StartTime DESC;

Migrating Servers to Azure – Activating Windows

Recently I have migrated several servers from Vultr to Microsoft Azure.

As part of this migration, windows became un-activated – most likely because Vultr runs it’s own KMS service and it is no longer able to contact the server:

In order to resolve this, I changed the product key to Microsoft’s KMS key.

Then the KMS server was changed to Microsoft’s, and activation was forced: (run from administrative command prompt)

cscript c:\windows\system32\slmgr.vbs /ipk <product key>
cscript c:\windows\system32\slmgr.vbs /skms kms.core.windows.net:1688
cscript c:\windows\system32\slmgr.vbs /ato

SQL Server – Identify Unused Indexes

Within SQL Server an index is used to optimise the reading of data from the database.

However the side effect of having such an index is reduced write speed for inserts and updates. This is because SQL Server will need to update and maintain the index.

Running the below SQL will highlight indexes which have not been used recently, allowing you to remove these indexes (if these are no longer required). This is sorted by the number of updates, as tables with high number of updates will most likely benefit from removal of this index.

SELECT TOP 200 o.NAME AS ObjectName
	,i.NAME AS IndexName
	,i.index_id AS IndexID
	,dm_ius.user_seeks AS UserSeek
	,dm_ius.user_scans AS UserScans
	,dm_ius.user_lookups AS UserLookups
	,dm_ius.user_updates AS UserUpdates
	,p.TableRows
	,'DROP INDEX ' + QUOTENAME(i.NAME) + ' ON ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id
	AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (
	SELECT SUM(p.rows) TableRows
		,p.index_id
		,p.OBJECT_ID
	FROM sys.partitions p
	GROUP BY p.index_id
		,p.OBJECT_ID
	) p ON p.index_id = dm_ius.index_id
	AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID, 'IsUserTable') = 1
	AND dm_ius.database_id = DB_ID()
	AND i.type_desc = 'nonclustered'
	AND i.is_primary_key = 0
	AND i.is_unique_constraint = 0
	AND dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups = 0
ORDER BY UserUpdates DESC

The following line can also be altered in order to include indexes which are rarely used (The code above is for unused), as there may be some indexes which are expensive to maintain, but only used once a day for example!

AND dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups = 0

Example of result from this is shown below:

Windows 10 – Missing Taskbar Icons

Had a very odd issue with Windows 10 and taskbar menu items missing:

After lots of research, I’ve partially fixed this by running the following from an elevated command prompt:

for /R "%APPDATA%\Microsoft\Windows\Start Menu\Programs\" %f in (.lnk) do copy /b "%f"+,, "%f" 1>nul
for /R "%ALLUSERSPROFILE%\Microsoft\Windows\Start Menu\Programs\" %f in (
.lnk) do copy /b "%f"+,, "%f" 1>nul
taskkill /IM explorer.exe /F
explorer.exe

What this does is:

  • Update “Last Update Date” attribute on Start Menu Links
  • Kill Explorer process
  • Start Explorer process

The result being:

The one missing icon is for Microsoft Edge (also other Microsoft Store Apps have missing icons too, such as Calculator etc).

After lots of research, investigation and trial-and-error, I solved the problem by uninstalling “Google Drive File Stream” (plus I dont really use this anyways):

Tada Fixed:

Azure SQL Database View Running Transactions / Blocking

With the Azure SQL Database, sometimes you want to view the current running transactions. Unfortunately there is no GUI as such like there is with normal SQL Server (right click, “Activity Monitor”), however it can be grabbed by executing sys.dm_exec_requests.

The following SQL is very useful as it shows details of all running transactions within the database, including highlighting if the transaction is blocked, or is causing blocking.

SELECT r.session_id
	,CASE 
		WHEN r.session_id IN (
				SELECT DISTINCT (blocking_session_id)
				FROM sys.dm_exec_requests
				)
			THEN 'Yes'
		ELSE ''
		END AS blocking
	,r.blocking_session_id
	,r.request_id
	,r.start_time
	,r.STATUS
	,r.command
	,r.database_id
	,r.user_id
	,r.wait_type
	,r.wait_time
	,r.last_wait_type
	,r.wait_resource
	,r.total_elapsed_time
	,r.cpu_time
	,CASE r.transaction_isolation_level
		WHEN 0
			THEN 'Unspecified'
		WHEN 1
			THEN 'ReadUncommitted'
		WHEN 2
			THEN 'ReadCommitted'
		WHEN 3
			THEN 'Repeatable'
		WHEN 4
			THEN 'Serializable'
		WHEN 5
			THEN 'Snapshot'
		END AS transaction_isolation_level
	,r.row_count
	,r.percent_complete
	,st.TEXT AS sql
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
GROUP BY r.session_id
	,r.blocking_session_id
	,r.request_id
	,r.start_time
	,r.STATUS
	,r.command
	,r.database_id
	,r.user_id
	,r.wait_type
	,r.wait_time
	,r.last_wait_type
	,r.wait_resource
	,r.total_elapsed_time
	,r.cpu_time
	,r.transaction_isolation_level
	,r.row_count
	,r.percent_complete
	,st.TEXT
ORDER BY r.total_elapsed_time

Azure SQL Database Index Size

Recently I’ve been doing some work on optimising indexes within Azure SQL Database. The following SQL from Daniel is very useful in showing the status of all the current indexes within a SQL Database. The SQL works for both SQL Server 2014 onwards, plus Azure SQL Database. I have not tested it on any other versions.

This code returns the following:

  • Object Type
  • Object Name
  • Index Name
  • Index Type
  • Partition (if any)
  • Compression
  • Data Space
  • Fill Factor
  • Rows
  • Reserved MB
  • In Row Used MB
  • Row Overflow Used MB
  • Out of Row Used MB
  • Total Used MB
SELECT --- Schema, type and name of object and index:
	       REPLACE(obj.type_desc, '_', ' ') AS objectType
	,       sch.[name] + '.' + obj.[name] AS objectName
	,       ISNULL(ix.[name], '') AS indexName
	,       ix.type_desc AS indexType
	,       --- Partition number, if there are partitions:
	      (CASE COUNT(*) OVER (
				PARTITION BY ps.[object_id]
				,ps.index_id
				)             
			WHEN 1
				THEN ''             
			ELSE CAST(ps.partition_number AS VARCHAR(10))             
			END) AS [partition]
	,       --- Storage properties:
	       p.data_compression_desc AS [compression]
	,       ds.[name] + ISNULL('(' + pc.[name] + ')', '') AS dataSpace
	,       STR(ISNULL(NULLIF(ix.fill_factor, 0), 100), 4, 0) + '%' AS [fillFactor]
	,       --- The raw numbers:
	       ps.row_count AS [rows]
	,       STR(1.0 * ps.reserved_page_count * 8 / 1024, 12, 2) AS reserved_MB
	,       STR(1.0 * ps.in_row_used_page_count * 8 / 1024, 12, 2) AS inRowUsed_MB
	,       STR(1.0 * ps.row_overflow_used_page_count * 8 / 1024, 12, 2) AS RowOverflowUsed_MB
	,       STR(1.0 * ps.lob_used_page_count * 8 / 1024, 12, 2) AS outOfRowUsed_MB
	,       STR(1.0 * ps.used_page_count * 8 / 1024, 12, 2) AS totalUsed_MB
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.partitions AS p ON     ps.[partition_id] = p.[partition_id]
INNER JOIN sys.objects AS obj ON     ps.[object_id] = obj.[object_id]
INNER JOIN sys.schemas AS sch ON     obj.[schema_id] = sch.[schema_id]
LEFT JOIN sys.indexes AS ix ON     ps.[object_id] = ix.[object_id]
	AND     ps.index_id = ix.index_id
--- Data space is either a file group or a partition function:
LEFT JOIN sys.data_spaces AS ds ON     ix.data_space_id = ds.data_space_id
--- This is the partitioning column:
LEFT JOIN sys.index_columns AS ixc ON     ix.[object_id] = ixc.[object_id]
	AND     ix.index_id = ixc.index_id
	AND     ixc.partition_ordinal > 0
LEFT JOIN sys.columns AS pc ON     pc.[object_id] = obj.[object_id]
	AND     pc.column_id = ixc.column_id
--- Not interested in system tables and internal tables:
WHERE obj.[type] NOT IN (
		'S'
		,'IT'
		)
ORDER BY sch.[name]
	,obj.[name]
	,ix.index_id
	,p.partition_number;

Example Results:

Delete Duplicate Records – Leave Only 1 Behind MS SQL

Recently I’ve had to delete duplicate records from the Record Link table in Microsoft Dynamics Nav.

Using the following SQL, (which uses the OVER clause for partitioning) this deletes all duplicate records, leaving the first unique item behind.

The SQL can be updated easily to function for other tables / scenarios. Currently it uses the following fields to identify duplicates:

  • Description
  • URL 1

The SQL does not look at the Notes, which is stored as a BLOB.

DELETE
FROM [Record Link]
WHERE [Link ID] IN (
		SELECT [Link ID]
		FROM (
			SELECT *
				,ROW_NUMBER() OVER (
					PARTITION BY [Record ID]
					,[Description]
					,[URL1] ORDER BY [Link ID]
					) AS [ItemNumber]
			FROM [Record Link]
			WHERE [Note] IS NULL
			) a
		WHERE ItemNumber > 1
		)

Suggesting MS SQL Index Creations Using sys.dm_db_missing_index_details

Within SQL Server, sys.dm_db_missing_index_details returns indexes which it believes are required by the Query Optimiser. Basically as queries are run in the background, the Query Optimiser makes a record of any optimisations it feels are necessary. Restarting the SQL server resets all these stats.

Using some SQL it is possible to identify potentially missing indexes, create sample SQL to build these indexes. I’ve found the following SQL useful:

SELECT mid.statement
	,migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure
	,'CREATE INDEX [missing_index_' + CONVERT(VARCHAR, mig.index_group_handle) + '_' + CONVERT(VARCHAR, mid.index_handle) + '_' + LEFT(PARSENAME(mid.statement, 1), 32) + ']' + ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') + CASE 
		WHEN mid.equality_columns IS NOT NULL
			AND mid.inequality_columns IS NOT NULL
			THEN ','
		ELSE ''
		END + ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
	,migs.*
	,mid.database_id
	,mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

This produces results as follows:

Columns include:

  • statement – The table affected
  • improvement_measure – A generated measure of improvement (based on user cost, impact, seeks etc)
  • create_index_statement – A cut and paste friendly statement to add the index
  • group_handle – Identifies a group of missing indexes. This identifier is unique across the server.
  • unique_compiles – Number of compilations and recompilations that would benefit from this missing index group
  • user_seeks – Number of seeks caused by user queries that the recommended index in the group could have been used for
  • user_scans – Number of scans caused by user queries that the recommended index in the group could have been used for.
  • last_user_seek – Date and time of last seek caused by user queries that the recommended index in the group could have been used for.
  • last_user_scan – Date and time of last scan caused by user queries that the recommended index in the group could have been used for.
  • avg_total_user_cost – Average cost of the user queries that could be reduced by the index in the group.
  • avg_user_impact – Average percentage benefit that user queries could experience if this missing index group was implemented.
  • system_seeks – Number of seeks caused by system queries, such as auto stats queries, that the recommended index in the group could have been used for.
  • last_system_seek – Date and time of last system seek caused by system queries that the recommended index in the group could have been used for.
  • last_system_scan – Date and time of last system scan caused by system queries that the recommended index in the group could have been used for.
  • avg_total_system_cost – Average cost of the system queries that could be reduced by the index in the group.
  • avg_system_impact – Average percentage benefit that system queries could experience if this missing index group was implemented.
  • database_id – The ID number of the database
  • object_id – The ID number of the object

Microsoft Dynamics NAV – View All Active Sessions

With Microsoft Dynamics Nav, there are various ways of viewing all the active sessions within the system. The easiest of which is the “Sessions” page within the software itself:

sessions-menu-option

sessions-window

The downside of this, is that it only shows active sessions on the tier which the user is connected. This is ok for solutions where the system has a single tier, however it is common to have multiple tiers in order to spread load. Checking this way is time consuming as you need to check multiple tiers in order to get a session count.

However using SQL can be an easy way to get the number of active sessions:

SELECT [User ID]
	,[Server Instance Name]
	,[Server Computer Name]
	,[Database Name]
	,[Client Computer Name]
	,[Login Datetime]
FROM [dbo].[Active Session]

It is worth nothing that Nav tidies up this table automatically, but in some cases it may be incorrect. For example if a middle tier crashes out, it could be left with orphaned records until the tier starts up again (or another tier prunes the records down).