The forum will be unavailable for maintenance at some point from Friday, April 13 at 19:00 EDT until Sunday, April 15 at 23:59 EDT. Downtime will be minimized but the exact timing is unknown.

How to (online) reduce the size of the temporary files used by SQLA during runtime.

asked 25 Apr '13, 07:12

Martin's gravatar image

accept rate: 14%

edited 25 Apr '13, 11:18

Mark%20Culp's gravatar image

Mark Culp

Temporary files cannot shrink at runtime: they are only deleted when the associated database is stopped.

Are you looking for how to avoid large temp files to begin with?

permanent link

answered 25 Apr '13, 10:28

John%20Smirnios's gravatar image

John Smirnios
accept rate: 39%

You mean the max_temp_space option?

(25 Apr '13, 12:02) Martin
Replies hidden

Well, that allows you to put a limit on each connection but I'm trying to remove the ambiguity in your question. Are you asking:

1) I have queries that use lots of temp file space. I understand why and I'm okay with that but I want to shrink the temp files without shutting down the database. Is that possible and, if so, how?


2) Why are my temp files from getting so darn big and how do I prevent it from happening?

I provided an answer to #1. #2 was more long winded and didn't think it was what you were asking. If it is, let me know :)

(25 Apr '13, 12:11) John Smirnios

oh I see, for me it's number 1) I am ok with it, but would like to shrink it without restart

(26 Apr '13, 02:40) Martin

You cannot shrink the temporary file without bouncing the database.

IMO there is no technical reason to do so... there are technical reasons why you might not want the temporary file to grow in the first place, but if those reasons don't apply, then leave well enough alone.

There may be emotional reasons to shrink the temporary file ... but ... emotions ... are ... illogical ... :)

permanent link

answered 26 Apr '13, 05:54

Breck%20Carter's gravatar image

Breck Carter
accept rate: 21%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 25 Apr '13, 07:12

question was seen: 1,873 times

last updated: 26 Apr '13, 05:54