locked
Does SQL Server write to anywhere other than the mdf and ldf files? RRS feed

  • Question

  • I have two databases which hold sensitive data, so the four files (two mdfs and two ldfs) are on a flash drive. With the database files on a flash drive, will the data ever be on the hard drive at any point? I know it could be in the page file but other than that?
    Tuesday, July 7, 2009 8:09 PM

Answers

  • Yes, you can change where tempdb has its database files. Below is straight from Books Online:

    A. Moving the tempdb database
    The following example moves the tempdb data and log files to a new location as part of a planned relocation.

    Note: 
    Because tempdb is re-created each time the instance of SQL Server is started, you do not have to physically move the data and log files. The files are created in the new location when the service is restarted in step 3. Until the service is restarted, tempdb continues to use the data and log files in existing location.
     


    Determine the logical file names of the tempdb database and their current location on the disk.

     Copy Code
    SELECT name, physical_name AS CurrentLocation
    FROM sys.master_files
    WHERE database_id = DB_ID(N'tempdb');
    GO
     

    Change the location of each file by using ALTER DATABASE.

     Copy Code
    USE master;
    GO
    ALTER DATABASE tempdb
    MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
    GO
    ALTER DATABASE tempdb
    MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');
    GO
     

    Stop and restart the instance of SQL Server.


    Verify the file change.

     Copy Code
    SELECT name, physical_name AS CurrentLocation, state_desc
    FROM sys.master_files
    WHERE database_id = DB_ID(N'tempdb');
     

    Delete the tempdb.mdf and templog.ldf files from the original location.

     


    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, July 8, 2009 2:10 PM

All replies

  • Not at all. The data goes to memory, get's written to the log, and committed to the database during checkpoint
    bass_player http://bassplayerdoc.blogspot.com/
    • Proposed as answer by Nimit Parikh Wednesday, July 8, 2009 12:38 AM
    • Unproposed as answer by ScottyDoesKnow Wednesday, July 8, 2009 2:21 PM
    Tuesday, July 7, 2009 10:58 PM
  • ... except for tempdb, of course. But one have to be pretty knowledgeable and lucky to get anything out of tempdb.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, July 8, 2009 8:33 AM
  • That's what I was worried about. Why does SQL Server need a temporary db? Is it an option that can be turned off?
    Wednesday, July 8, 2009 1:09 PM
  • Tempdb is used fror valurs things, like table variables, #temptables etc, but also internal work tables used while proceccing varios queries. No option to turn this off...
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, July 8, 2009 2:00 PM
  • Is it possible to set a different location so I could store the tempdb wherever my other database files are?
    Wednesday, July 8, 2009 2:06 PM
  • Yes, you can change where tempdb has its database files. Below is straight from Books Online:

    A. Moving the tempdb database
    The following example moves the tempdb data and log files to a new location as part of a planned relocation.

    Note: 
    Because tempdb is re-created each time the instance of SQL Server is started, you do not have to physically move the data and log files. The files are created in the new location when the service is restarted in step 3. Until the service is restarted, tempdb continues to use the data and log files in existing location.
     


    Determine the logical file names of the tempdb database and their current location on the disk.

     Copy Code
    SELECT name, physical_name AS CurrentLocation
    FROM sys.master_files
    WHERE database_id = DB_ID(N'tempdb');
    GO
     

    Change the location of each file by using ALTER DATABASE.

     Copy Code
    USE master;
    GO
    ALTER DATABASE tempdb
    MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
    GO
    ALTER DATABASE tempdb
    MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');
    GO
     

    Stop and restart the instance of SQL Server.


    Verify the file change.

     Copy Code
    SELECT name, physical_name AS CurrentLocation, state_desc
    FROM sys.master_files
    WHERE database_id = DB_ID(N'tempdb');
     

    Delete the tempdb.mdf and templog.ldf files from the original location.

     


    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, July 8, 2009 2:10 PM
  • Beautiful. Thanks a lot. So it looks like this works for the entire SQL Server instance. So if I put the tempdb on a flash drive, I have to set the path back to the original before I remove the flash drive right? If I want the Server to work while the flash drive is out of course.
    Wednesday, July 8, 2009 2:21 PM
  • YEs, that is correct. Note, though, that this isn't dynamic, so you'd have to re-start tempdb for the change to be in effect. And since re-starting SQL Server will re-create tempdb anyhow, you can just as well only re-start...
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, July 8, 2009 6:50 PM