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

Martin
8.6k114149237
accept rate: 14%

edited 25 Apr '13, 11:18

Mark%20Culp's gravatar image

Mark Culp
22.5k9129264


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
8.7k377106
accept rate: 40%

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?

or

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
26.8k420580826
accept rate: 20%

Your answer
toggle preview

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×17
×11

question asked: 25 Apr '13, 07:12

question was seen: 1,401 times

last updated: 26 Apr '13, 05:54