Global Temporary tables not returning space.

[Former Member]

Is there a way without shutting down the server and restarting to get space back from files created when I use global temporary tables? In this case I load 5 million rows into a global temporary table, work with it then delete it. I see a file created on my system that is about 500 meg, if I do the load and work again it's a gig and so on till I run out of space. I check to make sure that at some point the table has 0 rows in it but the size of the file used by the table is still the same. Is there a command/switch/flag I can set that will cause the file to give back the space and reduce in size without having to bounce the system?

Thank You Matthew

[Breck_Carter]

The file you speak of is called the "temporary dbspace". While the database is running, it will grow but not shrink. However, deletions should cause space to be freed up for reuse within the file, so when you delete the rows and then re-insert them, the file should not grow... I have verified this behavior with version 11.0.1.2587 (see below).

Show us the code you are using to create the table, and to delete the rows.

-- temporary file size 1M, free space 248k

CREATE GLOBAL TEMPORARY TABLE fat (
   fat VARCHAR ( 100 ) )
   ON COMMIT PRESERVE ROWS;

INSERT fat SELECT REPEAT ( 'x', 100 ) FROM sa_rowgenerator ( 1, 1000000, 1 );
COMMIT;

-- temporary file size 237M, free space 131M

DELETE fat;
COMMIT;

-- temporary file size 237M, free space 236M

INSERT fat SELECT REPEAT ( 'x', 100 ) FROM sa_rowgenerator ( 1, 1000000, 1 );
COMMIT;

-- temporary file size 237M, free space 131M 

[VolkerBarth]

More facts on that in this FAQ:

How to reduce the size of temporary files

[Former Member]

Thank you,

I'll try the solution above, one thing I wanted to point out is I didn't see a spot for it but I'm not on 11 I'm running on ASA 12.0.1 ebf 3942. The results you posted is what I thought I should be seeing but at this time am not. I see the space in tempdb free up but when I right to it a second time more space is added to tempdb. The free space isn't used. so the tempdb file grows and grows. Over time my server will go down. Now I only see this growing when I"m uinsg global temp tables if I use tempdb for any other reason it behaves normally.

Matthew

[Breck_Carter]

The code shown is not a "solution", just a demonstration that free space is re-used.

The test used V11 because you tagged your question "sa-11"... the results should be the same with V12.

To repeat, "Show us the code you are using to create the table, and to delete the rows"... without seeing your code, all responses are pointless guesswork.