Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2012
    Posts
    10

    Unanswered: MS Sql 2008 tempdb

    I have a database thrown out many deadlock recently. It seems it use a lot of tempdb. I have several questions about tempdb.

    So far the tempdb is located

    tempdev
    E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

    templog
    F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data

    They are auto growth.

    I see a lot of activities going on on tempdb. But, I am not able to catch them. So, how do I capture the activities like what queries used in tempdb?

    It is a good idea to split tempdb into muitl, how? sliting tempdb means only spiting log or data? I google some articles, it seems saying Generally you want to ensure you have as many tempdb data files as there are CPU's or vCPU's.
    You'll only need one log file. Make sure both data and log files are not set to the standard autogrowth...
    Is this correct?

    My next question is how to creating Multiple Files?

    I am using the following query to do that

    USE master;
    GO
    ALTER DATABASE [tempdb]
    ADD FILE ( NAME = 'tempdev1',
    FILENAME = 'f:\tempdb1.ndf' ,
    SIZE = 8192KB , FILEGROWTH = 10%)

    If I want to separate into 16 tempdb, does it mean I need tempdev1.mdf...tempdev16.mdf? and 16 tempdb1.ndf ...tempdb16.ndf

    or I just need 16 tempdev1.mdf...tempdev16.mdf but only one tempdb.ndf?
    Also, all the separate tempdb should be equal in size, correct?

    I have another question

    How to move the SQL Server tempdb to new Drive ? For example, I have a new drive K, Do I just need to change like below??

    from
    tempdev
    E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

    templog
    F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data

    to

    tempdev
    K:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

    templog
    K:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data

    Final question about tempdb


    For better performance, is this correct? why?
    This ‘tempdb’ is used for temp table...etc. Ideally this should not be in same drive as data-drive.

  2. #2
    Join Date
    Aug 2008
    Posts
    147
    Have you identified the code with the problem? When you do, analyse the order of the transactions? Can you apply more BEGIn TRANS..COMMIT TRANS around the sub steps, to release locks?
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can only have 1 MDF file per database, so you'll be adding NDF's. But don't do it for TEMPDB unless you know that you have 4 4-channel disk controllers (or 8 2-channel, etc.)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Nov 2010
    Posts
    1
    You will need to detach the database first before SQL Server allowes you to move the file.

    Separate drive divides DISK I/O activity, thus performance is improved.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •