none
how to change the default path of .cache files in SQL Server 2005 and SQL Server 2008

    Question

  • I was running a query using query analyzer on a large database and the disk space on the c: drive was dropping and eventually went to 0. Available space on the c: drive is 15GB. I have checked that sql server using c:\documents and settings\administrator\local settings\temp for caching files and now i want to change this location. How can i modify default path of .cache files in SQL Server 2005 and SQL Server 2008?

     


    Kapil Khalas - Database Developer
    Friday, July 15, 2011 12:58 PM

Answers

  • Thanks for the all replies.

    Alex,
    We have not install any add-on for SSMS.

    Balmukund,
    I have checked your link and one query where i need to set new location in User variables (TEMP/TMP) or System variables (TEMP/TMP) ?

    Thanks,


    Kapil Khalas - Database Developer

    User Variable TEMP and TMP should take care of it.
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    • Marked as answer by kapil khalas Wednesday, July 20, 2011 10:24 AM
    Wednesday, July 20, 2011 9:59 AM
    Moderator

All replies

  • Hi Kapil,

    As far as I know, the cache file location for SSMS is not configurable. The strange thing is that the whole 15GB available space is consumed. Do you have have add-on installed for SSMS? You may use Process Monitor to capture which processes are accessing the C drive, more information see: http://technet.microsoft.com/en-us/sysinternals/bb896645.

    By the way, when you run the SELECT statements, please get only requirements columns and specific query predicates accordingly, especially for the tables with large data columns such as text, ntext, etc..


    Best Regards
    Alex Feng | Forum Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    Tuesday, July 19, 2011 6:58 AM
    Moderator
  • I blogged about this issue recently
    http://sqlserver-help.com/2011/07/18/help-select-query-filling-up-my-disk-space/

     


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Tuesday, July 19, 2011 7:24 AM
    Moderator
  • Thanks for the all replies.

    Alex,

    We have not install any add-on for SSMS.

    Balmukund,

    I have checked your link and one query where i need to set new location in User variables (TEMP/TMP) or System variables (TEMP/TMP) ?

    Thanks,


    Kapil Khalas - Database Developer
    Wednesday, July 20, 2011 9:41 AM
  • Thanks for the all replies.

    Alex,
    We have not install any add-on for SSMS.

    Balmukund,
    I have checked your link and one query where i need to set new location in User variables (TEMP/TMP) or System variables (TEMP/TMP) ?

    Thanks,


    Kapil Khalas - Database Developer

    User Variable TEMP and TMP should take care of it.
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    • Marked as answer by kapil khalas Wednesday, July 20, 2011 10:24 AM
    Wednesday, July 20, 2011 9:59 AM
    Moderator
  • Thanks Balmukun it works!
    Kapil Khalas - Database Developer
    Wednesday, July 20, 2011 10:20 AM
  • Thanks Balmukun it works!
    Kapil Khalas - Database Developer

    Great! Nice to hear that.
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    Wednesday, July 20, 2011 10:42 AM
    Moderator