November 15, 2008 at 1:29 am
The LOG file is about 20 GB,and it had 0 freespace in it.So I've done a LOG backup and the freespace in it is 19 GB now.I tried to shrink it.But its not shrinking.
What is the reason for the LOG file not shrinking even if it has free space in it....
I finally had to change the DB to SIMPLE recovery model and shrink and reverted it back to FULL(since this is not a production DB).
November 15, 2008 at 4:05 am
Hello,
May be this article will help: http://www.builderau.com.au/program/sqlserver/soa/Help-My-SQL-Server-Log-File-is-too-big-/0,339028455,339292404,00.htm
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
November 15, 2008 at 4:24 am
SQL Reddy (11/15/2008)
The LOG file is about 20 GB,and it had 0 freespace in it.So I've done a LOG backup and the freespace in it is 19 GB now.I tried to shrink it.But its not shrinking.What is the reason for the LOG file not shrinking even if it has free space in it....
I finally had to change the DB to SIMPLE recovery model and shrink and reverted it back to FULL(since this is not a production DB).
Can you specify how did you try to shrink it? Also did you get an error message and if you did can you specify the error message?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 15, 2008 at 4:29 am
I tried to shrink it with management studio wizard.....database-->tasks-->shrink-->log file
Did'nt get any error message ...but the free space is still the same
November 15, 2008 at 4:59 am
Hello,
You could try the DBCC command and see if it reports anything e.g. DBCC SHRINKFILE (logfilename, 1000) would try to shrink it to 1GB.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
November 15, 2008 at 12:15 pm
although you ran a log backup there could be an active transaction occupying the end of the log file
run
dbcc opentran to check for open transactions
then
dbcc loginfo
a status of 2 will indicate that truncation canot occur beyond this point, my guess is there'll be an entry near the bottom of the results with a status of 2. Try running another log backup then shrink again
why shrink the file, is it an unexpected growth
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 17, 2008 at 6:21 am
SQL Reddy (11/15/2008)
I tried to shrink it with management studio wizard.....database-->tasks-->shrink-->log fileDid'nt get any error message ...but the free space is still the same
When you try to shrink the log/DB in SSMS there is an option to shrink the file to an explicit size. As a default this is set to the current allocated size, so if you don't change this it won't actually shrink it further (i.e. if the log is 305 Mb and it has 8% or 27 mb free it will default set it to shrink to 305 still...)
The reason you are not getting error messages is because SQL is doing exactly what you asked to, you just haven't specified to shrink it any further than the value given.
I recommend using the DBCC SHRINKFILE(LogName) command as other posters has recommended as SSMS is a bit clunky for this imo.
November 17, 2008 at 2:53 pm
Perry Whittle (11/15/2008)
although you ran a log backup there could be an active transaction occupying the end of the log filerun
dbcc opentran to check for open transactions
then
dbcc loginfo
a status of 2 will indicate that truncation canot occur beyond this point, my guess is there'll be an entry near the bottom of the results with a status of 2. Try running another log backup then shrink again
why shrink the file, is it an unexpected growth
This is the correct answer, with the most important question, why shrink it? It is likely that it will just re-grow to this size again. Are you doing regular log backups to keep it from growing? If you do not need to be able to restore to a point in time why not leave it in SIMPLE recovery?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply