Cloud Cloud - 3 months ago 6
SQL Question

"tempdb.mdf" increasing to astronomical sizes (i.e. 800GB) due to SQL query

I have been trying to run the following SQL on my SQL Server. However, I keep coming across an error that is the result of the

tempdb.mdf
file increasing to such astronomical sizes that it occupies the entire hard disk (800GB). Is there anyway to resolve this?

The SQL query is as follows:

SELECT
t1.[mmsi], t1.[tagblock_timestamp], t1.[x] , t1.[y],
t1.[sog], t1.[Location Code],
DATEDIFF(second , MAX (t2.[tagblock_timestamp]),
t1.[tagblock_timestamp]) AS LengthOfRecord
INTO
[dbo]. [VehProcessed]
FROM
[dbo]. [VehMovement] t1
LEFT JOIN
[dbo]. [VehMovement] t2 ON t1. [mmsi] = t2 .[mmsi]
AND t2.[tagblock_timestamp] < t1.[tagblock_timestamp]
GROUP BY
t1.[mmsi], t1.[tagblock_timestamp], t1.[x], t1.[y], t1.[sog], t1.[Location Code];

Answer

Create an index on (mmsi, tagblock_timestamp DESC). This is a must.

The following query is not so efficient as LAG function (which is available since SQL Server 2012+), but it should be better than you have.

SELECT
    t1.[mmsi]
    ,t1.[tagblock_timestamp]
    ,t1.[x]
    ,t1.[y]
    ,t1.[sog]
    ,t1.[Location Code]
    ,DATEDIFF(second, A.[tagblock_timestamp], t1.[tagblock_timestamp]) AS LengthOfRecord
INTO
    [dbo].[VehProcessed]
FROM
    [dbo].[VehMovement] AS t1
    OUTER APPLY
    (
        SELECT TOP(1) t2.[tagblock_timestamp]
        FROM [dbo].[VehMovement] AS t2
        WHERE 
            t1.[mmsi] = t2.[mmsi]
            AND t2.[tagblock_timestamp] < t1.[tagblock_timestamp]
        ORDER BY t2.[tagblock_timestamp] DESC
    ) AS A
;

You want to calculate the difference between consecutive rows. It is very likely that on SQL Server 2008 the best method would be to use a simple cursor.