tempDB queries

  • Hi,

    I just wanted to ask, if we add several data files to tempDB, will it use all of the files at the same time by default to share it's workload?

    If I added only one additional data file and wanted tempDB to use just my newly added file, how could I do that?

    Also if I wanted to increase the size of tempDB would I increase the initial size? Or would I increase the size of the data and log files using an ALTER statement?

    Thanks.

  • zedtec (11/25/2014)


    I just wanted to ask, if we add several data files to tempDB, will it use all of the files at the same time by default to share it's workload?

    Yes. Best to make them all the same size

    If I added only one additional data file and wanted tempDB to use just my newly added file, how could I do that?

    You can't.

    Best you could do is make the mdf tiny and disable autogrow, but I don't see any reason to do that.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Ok thanks for your help.

  • I've also noticed when I go into my tempDB database properties through SSMS I get the following error:

    ERROR 1222 : Lock request time out period exceeded.

    I killed a session which was accessing tempDB but I still keep getting the same error.

    Is there any way I can find out what is causing this?

  • zedtec (11/25/2014)


    I've also noticed when I go into my tempDB database properties through SSMS I get the following error:

    ERROR 1222 : Lock request time out period exceeded.

    I killed a session which was accessing tempDB but I still keep getting the same error.

    Is there any way I can find out what is causing this?

    Since tempdb is always busy, you need to get info on tempdb using line commands and not the GUI. Here are some sample line commands to get info for tempdb:

    SELECT * FROM tempdb.sys.database_files;

    EXEC sp_helpdb 'tempdb';

    SELECT * FROM sys.databases WHERE name = 'tempdb';

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Thanks, I've checked that data and tempdb has over 50% of free space left in it.

    Would I be able to get the queries causing this issue by running a Profiler trace?

  • Probably easier to look in the transaction and lock DMVs

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Will do thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

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