Post reply

tempDB queries

  • November 25, 2014 at 5:09 am

    #292736

    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.

  • November 25, 2014 at 5:17 am

    #1760748

    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

  • November 25, 2014 at 7:02 am

    #1760787

    Ok thanks for your help.

  • November 25, 2014 at 9:18 am

    #1760857

    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?

  • November 25, 2014 at 9:52 am

    #1760866

    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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • November 26, 2014 at 2:27 am

    #1761006

    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?

  • November 26, 2014 at 2:31 am

    #1761008

    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

  • November 26, 2014 at 4:04 am

    #1761037

    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