Orphaned Sessions and locks: out to expand tempdb log file

I have a problem cause by an extensive sql statement that causes the tempdb
log file to be too small for execution. how can i extend the size of the log
file '
is
ALTER DATABASE tempdb
MODIFY FILE (
NAME=templog,
FILENAME='E:\tempdb.ldf',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB )
a safe bet from the default size?
thanksThis is a multi-part message in MIME format.
--020102060703060100090302
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
It all depends on the amount of "temp" work your server does (joins,
temp tables, temp variables, etc.). Although a tempdb log that starts
at 5MB and can autogrow by 1MB to a max size of 10MB is the smallest
tempdb log file I've ever seen. My small-to-medium sized production
servers have tempdb log files that range from 100MB to 2GB.
You've got the right syntax for the ALTER DATABASE statement, although
you don't need to specify the filename (that's an optional in this
case), but why set such a low max size? Are you a bit strapped for space?
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
David Tucker wrote:
>I have a problem cause by an extensive sql statement that causes the tempdb
>log file to be too small for execution. how can i extend the size of the log
>file '
>is
>ALTER DATABASE tempdb
>MODIFY FILE (
>NAME=templog,
>FILENAME='E:\tempdb.ldf',
>SIZE = 5MB,
>MAXSIZE = 10MB,
>FILEGROWTH = 1MB )
>
>a safe bet from the default size?
>
>thanks
>
>
>
--020102060703060100090302
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>It all depends on the amount of "temp" work your server does
(joins, temp tables, temp variables, etc.). Although a tempdb log that
starts at 5MB and can autogrow by 1MB to a max size of 10MB is the
smallest tempdb log file I've ever seen. My small-to-medium sized
production servers have tempdb log files that range from 100MB to 2GB. 
 
You've got the right syntax for the ALTER DATABASE statement, although
you don't need to specify the filename (that's an optional in this
case), but why set such a low max size? Are you a bit strapped for
space?</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
David Tucker wrote:
<blockquote cite="midOS3S0DhdFHA.1504@.TK2MSFTNGP15.phx.gbl" type="cite">
<pre wrap="">I have a problem cause by an extensive sql statement that causes the tempdb
log file to be too small for execution. how can i extend the size of the log
file '
is
ALTER DATABASE tempdb
MODIFY FILE (
NAME=templog,
FILENAME='E:\tempdb.ldf',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB )
a safe bet from the default size?
thanks
</pre>
</blockquote>
</body>
</html>
--020102060703060100090302--

Friday, March 23, 2012

out to expand tempdb log file

No comments:

Post a Comment

Orphaned Sessions and locks

Blog Archive

About Me