CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Cloud
Cloud

Reputation: 399

"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];

Upvotes: 3

Views: 162

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32675

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.

Upvotes: 3

Related Questions