March 29, 2010 at 7:31 am
I created a database to test some ideas on with initial filesizes of 50MB and 512MB for the data and the log files respectively. The database was set to Simple recovery mode. After doing to some work the data file was 150MB and the log file was +-414MB. Repeated [font="Courier New"]DBCC shrinkfile(2,0)[/font] commands brought it down to 127.8MB with 127.4MB free. Why can I not decrease the log filesize any further?
Kids: Please don't do what I tried to do in this paragraph :crying:
What am I missing here?
March 29, 2010 at 8:24 am
You cannot shrink a log file below the initial size it was created with.
I believe the steps you'll need to take are:
1) Detach the database
2) Rename/delete the old log file
3) Re-attach the mdf file, removing the original log file from the database details list (It will error if it's still in the list)
SQL will then automatically create a log file in the default location of the default initial size which you can then move/resize.
March 29, 2010 at 8:32 am
SQL will then automatically create a log file in the default location of the default initial size which you can then move/resize.
Thanks for the reply Howard. Is the default log file size you're talking about the server's default log file size?
Why can one not change the minimum log file size?
March 29, 2010 at 1:35 pm
HowardW (3/29/2010)
You cannot shrink a log file below the initial size it was created with.I believe the steps you'll need to take are:
1) Detach the database
2) Rename/delete the old log file
3) Re-attach the mdf file, removing the original log file from the database details list (It will error if it's still in the list)
SQL will then automatically create a log file in the default location of the default initial size which you can then move/resize.
Do not do this - unless you are willing to have your database corrupted. It may work, but it is not guaranteed to work all the time.
You can shrink the file to a size less than the initial size by specifying the size you want. The reason it doesn't shrink lower is because it cannot shrink the smaller than the active VLF. You can find that information by using DBCC LOGINFO in the database you are working with.
In simple recovery model, you can issue CHECKPOINT several times to roll to the next VLF. You may need to actually write to the database and then issue the checkpoint to roll over. Once you have rolled over to the beginning of the log file - then you can shrink.
In full recovery model, you need to backup the log to get it to rollover to the beginning.
Review the link in my signature on managing transaction log files for additional information.
BTW - what is the purpose of shrinking?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 30, 2010 at 12:41 am
Thanks Jeffrey. The point in shrinking the log file was that I didn't want to waste space with a large backup for a database that was just to test something. However, my interest in not being able to shrink the log file then turned to one of curiosity and learning.
Thanks for your answer.
If anyone else is interested, it appears that [font="Courier New"]DBCC LOGINFO[/font] "displays information about the number, sizes, and status of the virtual log files internally to the transaction log. It is an undocumented command and provides you information only." Source.
For more information regarding Virtual Log Files in SQL Server 2008, please see the MSDN documentation here.
March 30, 2010 at 1:33 am
WHy are doing below step ?
I've tried setting the recovery mode to Full, doing a log backup, DBCC shrinkfile (2,0,TRUNCATEONLY) and then putting it to simple mode, same thing.
set recovery model to Simple- this will remove the inactive log and will just keep active log. Then try to truncate log file to the size of active log. This is the minimum size you are truncate the file to.
Then take the backup
March 30, 2010 at 2:41 am
Jeffrey Williams-493691 (3/29/2010)
SQL will then automatically create a log file in the default location of the default initial size which you can then move/resize.
Do not do this - unless you are willing to have your database corrupted. It may work, but it is not guaranteed to work all the time.
I wouldn't suggest it for production databases, but in which circumstances would it lead to corruption?
When detatching a database (assuming it happened cleanly and didn't error), I was under the impression that transactions are rolled back/commited prior to detatching, so it's already consistent and the log is not required to roll forward/back transactions when it's next onlined?
March 30, 2010 at 7:14 am
thanks for the post...
March 30, 2010 at 9:55 am
as Jeffrey said DBCC LOGINFO and check for anything with a status of 2 near the bottom of the output. SQL server will not truncate or shrink (they are both different actions) past this point. Cycle the VLF's by checkpoint or multiple log backups, that's the approach i normally use!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 30, 2010 at 2:13 pm
HowardW (3/30/2010)
Jeffrey Williams-493691 (3/29/2010)
SQL will then automatically create a log file in the default location of the default initial size which you can then move/resize.
Do not do this - unless you are willing to have your database corrupted. It may work, but it is not guaranteed to work all the time.
I wouldn't suggest it for production databases, but in which circumstances would it lead to corruption?
When detatching a database (assuming it happened cleanly and didn't error), I was under the impression that transactions are rolled back/commited prior to detatching, so it's already consistent and the log is not required to roll forward/back transactions when it's next onlined?
The thing is - even with a clean shutdown there is no guarantee that the database will come back online without a log file. You are relying on the fact that there is nothing in the log file that would have to be rolled forward or back - but you can't guarantee that.
What would happen if you had a long running transaction that was in process when you performed the shutdown? When the system comes back up - it can't roll that back and you now have a problem.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
March 30, 2010 at 2:51 pm
Jeffrey Williams-493691 (3/30/2010)
The thing is - even with a clean shutdown there is no guarantee that the database will come back online without a log file. You are relying on the fact that there is nothing in the log file that would have to be rolled forward or back - but you can't guarantee that.What would happen if you had a long running transaction that was in process when you performed the shutdown? When the system comes back up - it can't roll that back and you now have a problem.
Remember that the database was detached (not just shutdown) cleanly - so there could not have been any active connections at that time. I am pretty sure that still does not guarantee recoverability in all possible cases - but still.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 30, 2010 at 3:02 pm
Perry Whittle (3/30/2010)
as Jeffrey said DBCC LOGINFO and check for anything with a status of 2 near the bottom of the output. SQL server will not truncate or shrink (they are both different actions) past this point. Cycle the VLF's by checkpoint or multiple log backups, that's the approach i normally use!
That is the method I typically use as well.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 30, 2010 at 3:05 pm
Jeffrey Williams-493691 (3/30/2010)
HowardW (3/30/2010)
Jeffrey Williams-493691 (3/29/2010)
SQL will then automatically create a log file in the default location of the default initial size which you can then move/resize.
Do not do this - unless you are willing to have your database corrupted. It may work, but it is not guaranteed to work all the time.
I wouldn't suggest it for production databases, but in which circumstances would it lead to corruption?
When detatching a database (assuming it happened cleanly and didn't error), I was under the impression that transactions are rolled back/commited prior to detatching, so it's already consistent and the log is not required to roll forward/back transactions when it's next onlined?
The thing is - even with a clean shutdown there is no guarantee that the database will come back online without a log file. You are relying on the fact that there is nothing in the log file that would have to be rolled forward or back - but you can't guarantee that.
What would happen if you had a long running transaction that was in process when you performed the shutdown? When the system comes back up - it can't roll that back and you now have a problem.
I see this as an absolute last ditch effort that should be avoided.
Shrinking the log file is not a reason to go to this extreme - IMO.
I had a jr try this once while I was out on vacation - same exact method. The database would not start. I had to come in off of vacation to get the database back online and shrink the log file. Shrinking the log file was successful and was successful in that scenario to below the original size as well. At which point I regrew the log file to defragment the VLFs.
Typically there is a transaction in the database or the database thinks that a transaction is still ongoing. You need to find out what is causing it to be held like that and then shrink the log file after fixing that issue.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 30, 2010 at 3:15 pm
This is all a bit academic now anyway - the cause and correct solution have been posted multiple times (VLFs).
edit: Actually, the one thing has hasn't been said is that you would need to generate some logging activity to move to the next VLF, but it is reasonably obvious.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 30, 2010 at 3:20 pm
Perry Whittle (3/30/2010)
as Jeffrey said DBCC LOGINFO and check for anything with a status of 2 near the bottom of the output. SQL server will not truncate or shrink (they are both different actions) past this point. Cycle the VLF's by checkpoint or multiple log backups, that's the approach i normally use!
Agreed - but the point I was trying to make is that even detaching/offlining/clean shutdown does not guarantee that there are no transactions that have to be rolled back. If you detach the database and force all connections to close before detaching, there could have been an open transaction that was terminated. Is that transaction guaranteed to be fully rolled back before the database is detached?
Either way - the risk is too high for me that it *might* not work, so my advice is to not take the chance. Deleting a log file should only be a last resort, and only if the log file has already been damaged to a point where you can't recover anyways.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply