Cannot Shrink Log File

I have a Db w no open transactions, whose log file is 23GB and I cannot
shrink it below its initial size. Perhaps I need to know how to reduce the
initial size of the log. I am trying everything, including DBCC SHRINKFILE
(MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size in
Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
2005 SP2 x64 running in 64-bit mode). Thanks.Try doing a
backup log <db_name>
with Truncate_only
and then try shrinking the file.
--
MG
"MarkGsch" wrote:
> I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce the
> initial size of the log. I am trying everything, including DBCC SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.|||Do you mean that you created it at 23GB to begin with. How many VLF's are in
it? You may want to have a look at this section of BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2eb39bb3-f889-40be-9ca7-daf7d5107c46.htm
Andrew J. Kelly SQL MVP
"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce
> the
> initial size of the log. I am trying everything, including DBCC
> SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size
> in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.|||"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce
> the
> initial size of the log. I am trying everything, including DBCC
> SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size
> in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.
Sounds like you're in FULL RECOVERY mode and no transaction log backups are
being made.
I'd try to make a transaction log backup first.
barring that, do a BACKUP LOG <dbname> with TRUNCATE_ONLY
Then immediately do a FULL backup and then start doing your transaction log
backups.
(if you don't care about transaction logs, you can avoid this problem in the
future by setting the database to SIMPLE RECOVERY.)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||You are at the mercy of the next break point of the virtual log file you are
currently writing to. Try adding in a bunch of data, then doing the backup
log ... with truncate thing then trying to shrink. I can take a LOT data to
get this to work.
Also, if it is a single file database, can you detach and reattach it, thus
getting the default tlog created? If so, make sure you immediately size the
tlog correctly, and set an appropriate growth rate.
--
TheSQLGuru
President
Indicium Resources, Inc.
"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce
> the
> initial size of the log. I am trying everything, including DBCC
> SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size
> in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.|||In addition to the other posts:
Some additional details here: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce the
> initial size of the log. I am trying everything, including DBCC SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.|||Hi MarkGsch,
Just check with you to see if you need further assistance on this issue.
To reduce the size of the log, you can run:
BACKUP LOG ... WITH TRUNCATE_ONLY
and then run DBCC SHRINKFILE to shrink the file to the size that you want.
If you have any other questions or concerns, please feel free to post back.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================