Move tempdb log file

  • Running SQL 7.0 sp3a (soon to be SQL 8.0)

    We want to move the tempdb log file to a different logical volume

    (ie. from F: to D: drive)

    Attempt to delete old tempdb log file fails even after deleting contents:

    DBCC SHRINKFILE (templog,EMPTYFILE) \\this works OK

    ALTER DATABASE tempdb REMOVE FILE templog \\this fails

    with an error "The primary data or log file cannot be

    removed from a database"

    Even if new log file "templog1" is created on D: drive the primary log

    fie cannot be removed.

    Attempt to backup tempdb prior to restoring with a move for the templog

    file also fails:

    "Backup and restore operations are not allowed on

    database tempdb. [SQLSTATE 42000] (Error 3147)"

    Is it possible to avoid reinstalling the operating system and SQL Server

    just to move the tempdb log file to a different drive?

  • Use ALTER DATABASE as explained in

    http://support.microsoft.com/default.aspx?kbid=224071

    --Jonathan



    --Jonathan

  • Thanks Jonathan, this works a treat.

    Steve

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply