Post reply

How to Delete Temporary Tables from the MSCS_CatalogScratch Database

  • April 17, 2009 at 8:09 am

    #72010

    Is enyone knows the script, that I can run weekly to

    Delete Temporary Tables from the MSCS_CatalogScratch Database

  • April 17, 2009 at 8:26 am

    #979704

    Did you check below link.....

    http://msdn.microsoft.com/en-us/library/bb520958.aspx

  • April 17, 2009 at 8:48 am

    #979724

    Yes, but I need to set up the job to run script:put db off line run script, put db back on line. I want to know if anyone familiar with it and can help me to do it. Thank you

  • April 17, 2009 at 10:51 am

    #979831

    temporay tables should be removed when a session is disconnected for local temporary table or the last connect is disconnected for global table. If you use a user table as a temporary table, you can use

    DROP TABLE yourTable

    then, add it to your scheduled job.

    May I understand your question clearly.

  • April 17, 2009 at 11:09 am

    #979849

    Yes, I have all the permissions,but I am looking for the full script how to do this job, can you please provide it to me.Thank you

  • April 17, 2009 at 11:22 am

    #979860

    i would use the script that Austin_123 pointed you to, straight from Microsoft;

    one of the nice things it says is that if you DON'T take the catalog off line, if a temp table is in use it simply doesn't delete it...

    If you use the script while a Commerce site is running, the temporary tables that are used by the open SQL connections will not be deleted.

    that's exactly the behaviour I'd want so that i never need to take my db off line.

    go back and read the article in detail, I'm pretty sure it does exactly what you are looking for.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 17, 2009 at 11:32 am

    #979872

    So, I don't need to delete temp tables?

  • April 17, 2009 at 11:49 am

    #979886

    yes you need to delete temporary tables.

    MS recommends at least every week.

    you can use the script provided and never bring your database offline.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 18, 2009 at 6:17 am

    #980166

    There is note under script:

    Note:

    Use the CatalogCleanup.vbs script when your Commerce site is not running to prevent performance impact and to make sure that all temporary tables are deleted. If you use the script while a Commerce site is running, the temporary tables that are used by the open SQL connections will not be deleted.

    So temp tables don't get deleted!

  • April 18, 2009 at 7:01 am

    #980179

    yulichka (4/18/2009)

    There is note under script:

    Note:

    Use the CatalogCleanup.vbs script when your Commerce site is not running to prevent performance impact and to make sure that all temporary tables are deleted. If you use the script while a Commerce site is running, the temporary tables that are used by the open SQL connections will not be deleted.

    So temp tables don't get deleted!

    read it again, slowly. some temp tables will not. so what! some process is using them!

    most will be deleted, because their session expired, but they still exist.

    you don't WANT to delete temp tables that are in use. They are performing a needed function.

    again, this is exactly what you'd want to do.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 18, 2009 at 11:50 am

    #980235

    Thank you

  • April 20, 2009 at 9:32 am

    #980805

    How can I check if the script ran, if the db got smaller or what else got better? Thank you

  • April 20, 2009 at 12:37 pm

    #980961

    well the db will not get smaller... a db stays at a certain size in order to save space for future work...but the space used/space free will change.

    run sp_spaceused on your database before the script, then run the script, then run it again...

    the space used should show more free space than before you ran the script, and less used space:

    database_name database_size unallocated space

    KHC900 310.63 MB 0.00 MB

    reserved data index_size unused

    297232 KB 120560 KB 44720 KB 131952 KB

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 13 posts - 1 through 12 (of 12 total)

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