How to Delete Temporary Tables from the MSCS_CatalogScratch Database

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

    Delete Temporary Tables from the MSCS_CatalogScratch Database

  • Did you check below link.....

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

  • 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

  • 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.

  • 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

  • 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!

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

  • 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!

  • 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!

  • 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!

  • Thank you

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

  • 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