Limiting transaction Log file size

Limiting transaction Log file size

Post by WB » Wed, 19 Apr 2006 06:07:36


Please forgive my inability to fully comprehend this topic. I have posted
other messages regarding similar file issues and followed the
recommendations under the message topic SQL Server 2k transaction log file

I have a transaction file that is set to unlimited growth. as of now it is
about 8.5GB. I want the file to be smaller.

I have successfully backed up the log, shrunk the file down to 700MB, and
changed the log method to simple recovery.

The problem is the file allocation is still at the 8.5GB and when I try and
change the file growth restriction to say 2.5GB, SQL will not allow the
growth restriction to be less than the allocated space (8.5GB).

Is there a way to change the allocated space and/or the file growth
restriction size?

I hope my scenario is clear.

WB
 
 
 

Limiting transaction Log file size

Post by Tibor Kara » Wed, 19 Apr 2006 06:17:35

 
 
 

Limiting transaction Log file size

Post by WB » Wed, 19 Apr 2006 06:37:34

Thank you for you response. I have re-examined your link and still do not
find the answer. the information you provided relates to shrinking the
file, which I have completed.

I want to change the amount of space allocated in the transaction log file.
It is to big at 8.5GB and I want it to be a maximum of 2.5GB.

"Tibor Karaszi" < XXXX@XXXXX.COM > wrote in

shrinking transaction log

posted
file
is
and
and
 
 
 

Limiting transaction Log file size

Post by Tibor Kara » Wed, 19 Apr 2006 15:16:17

I'm afraid I don't follow you...

You say that file is 700MB. How did you determine that number?

You also say that the file allocation is 8.5GB. I fail to see how the allocation is larger than the
file size. Where did you see this number? Or did you mean the max file size?

For the above two numbers, use DBCC SQLPERF(LOGSPACE)

How do you try to change the max file size? You can try ALTER DATABASE ... MODIFY FILE, perhaps the
GUI is doing something strange...

--
Tibor Karaszi, SQL Server MVP
http://www.yqcomputer.com/
http://www.yqcomputer.com/
 
 
 

Limiting transaction Log file size

Post by Jayesh Ant » Wed, 19 Apr 2006 22:48:26

Its still not very clear what you have done so far. You can shrink the log
file, disable the auto grow and turn on "truncate log on checkpoint".
Jayesh
 
 
 

Limiting transaction Log file size

Post by WB » Thu, 20 Apr 2006 02:13:41

was deriving my numbers from Enterprise Manager by looking at the
properties of the database.

When I try your suggested method the result is the same. It shows the log
size in MB as being 8,415 and the % of space used as 8.7. This would seem
to translate to the numbers I provided.

Yes, what I want to do is change the max file size.

"Tibor Karaszi" < XXXX@XXXXX.COM > wrote in
message news:ObZ3b% XXXX@XXXXX.COM ...
allocation is larger than the
size?
MODIFY FILE, perhaps the
not
file.
in
on
news: XXXX@XXXXX.COM ...
it
try
the


 
 
 

Limiting transaction Log file size

Post by Tibor Kara » Thu, 20 Apr 2006 04:08:17

o the current size it 8,415 and utilization it 8.7%. To shrink the file size, see
http://www.karaszi.com/SQLServer/info_dont_shrink.asp. You can then set a max size, but is more than
that is needed, the modifications will fail.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/


"WB" <none> wrote in message news: XXXX@XXXXX.COM ...