Limiting transaction Log file size

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.
WBSee http://www.karaszi.com/SQLServer/info_dont_shrink.asp for details on shrinking transaction log
files.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"WB" <none> wrote in message news:eQ9vVNmYGHA.1200@.TK2MSFTNGP03.phx.gbl...
> 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
>|||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" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%231j0aRmYGHA.3424@.TK2MSFTNGP02.phx.gbl...
> See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for details on
shrinking transaction log
> files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "WB" <none> wrote in message news:eQ9vVNmYGHA.1200@.TK2MSFTNGP03.phx.gbl...
> > 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
> >
> >
>|||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.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"WB" <none> wrote in message news:eN$AGemYGHA.3516@.TK2MSFTNGP03.phx.gbl...
> 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" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%231j0aRmYGHA.3424@.TK2MSFTNGP02.phx.gbl...
>> See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for details on
> shrinking transaction log
>> files.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "WB" <none> wrote in message news:eQ9vVNmYGHA.1200@.TK2MSFTNGP03.phx.gbl...
>> > 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
>> >
>> >
>|||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
"WB" <none> wrote in message news:eQ9vVNmYGHA.1200@.TK2MSFTNGP03.phx.gbl...
> 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
>|||I 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" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ObZ3b%23qYGHA.4760@.TK2MSFTNGP03.phx.gbl...
> 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.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "WB" <none> wrote in message news:eN$AGemYGHA.3516@.TK2MSFTNGP03.phx.gbl...
> > 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" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:%231j0aRmYGHA.3424@.TK2MSFTNGP02.phx.gbl...
> >> See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for details
on
> > shrinking transaction log
> >> files.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "WB" <none> wrote in message
news:eQ9vVNmYGHA.1200@.TK2MSFTNGP03.phx.gbl...
> >> > 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
> >> >
> >> >
> >>
> >
> >
>|||So 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:uvT8UvwYGHA.1888@.TK2MSFTNGP02.phx.gbl...
>I 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" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:ObZ3b%23qYGHA.4760@.TK2MSFTNGP03.phx.gbl...
>> 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.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "WB" <none> wrote in message news:eN$AGemYGHA.3516@.TK2MSFTNGP03.phx.gbl...
>> > 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" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
>> > message news:%231j0aRmYGHA.3424@.TK2MSFTNGP02.phx.gbl...
>> >> See http://www.karaszi.com/SQLServer/info_dont_shrink.asp for details
> on
>> > shrinking transaction log
>> >> files.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "WB" <none> wrote in message
> news:eQ9vVNmYGHA.1200@.TK2MSFTNGP03.phx.gbl...
>> >> > 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
>> >> >
>> >> >
>> >>
>> >
>> >
>