Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Cannot shrink log Expand / Collapse
Author
Message
Posted Thursday, April 25, 2013 6:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:46 AM
Points: 147, Visits: 361
My Transaction log is about 3GB and I can't shrink it.

It is using a FULL backup and I try over and over again to back and shrink it and it won't do it.

DECLARE @DatabaseName VARCHAR(50);
SET @DatabaseName = 'myDatabase'

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = @DatabaseName

This gives me:

LOG_BACKUP in the log_reuse_wait_desc.

I have been all over the web looking for an answer but anything I try still leaves it the same size. I tried to change the inital size to 600MB but that didn't work.

Any ideas what I am missing?

Thanks,

Tom
Post #1446757
Posted Thursday, April 25, 2013 6:55 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 8, 2013 7:16 PM
Points: 221, Visits: 132
What version of SQL are you running? I know that different versions of SQL run shrinking the transaction log differently. When was the last time you did a transaction log backup? Here is the Microsoft article on shrinking the transaction log:

http://msdn.microsoft.com/en-us/library/ms178037(v=sql.105).aspx


--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/
You can also follow my twitter account to get daily updates: @BLantz2455
Post #1446762
Posted Thursday, April 25, 2013 8:16 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
FULL backup takes care only about data pages from data files.
It does not touch TRN log file at all.

You need to release some sppace in log file by doing BACKUP LOG, and only then you'll be able to shrink it.

But it's not that simple.
You can only shrink TRN file if latest LSN entries (Virtual Log Files) are "inactive" (check Status returned by DBCC LOGINFO, "0" is iactive, "2" is active).

1st BACKUP log will inactivate all the VLF's up to the latest one(s) whcih are in use by the current transactions.
You need to wait until SQL Server "loops over" and starts recording new transactions from the beginning of the LOG file. You will see Status=2 appear for the VLF's at the beginning of the TRN file.
Then you can run BACKUP LOG again, and if the VLF's at the end (check againg with DBCC LOGINFO) have become inactive then you can shrink the file.

Post #1446772
Posted Friday, April 26, 2013 4:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:32 AM
Points: 7,135, Visits: 12,745
Sergiy (4/25/2013)
FULL backup takes care only about data pages from data files.
It does not touch TRN log file at all.

That's not entirely accurate. You are right in that when in FULL recovery mode a Database Backup will not mark portions of the log inactive, however Database backups (a.k.a. Full Backups) actually do interact with the Transaction Log. Please read this article for more info:

More on how much transaction log a full backup includes By Paul Randal



@tshad:

The real question you should be asking is, why is it that I am thinking of shrinking my transaction log? Your database is in FULL recovery mode which means whoever set it up that way either did not answer the question "do I need point in time recovery?" and did not realize the implications of setting up the database in FULL recovery, or, they answered "yes". You need to find out which one. If they answered "yes" then you need to start managing your transaction logs properly.

If they did not know they were choosing the FULL recovery mode, many inexperienced DBAs do not, and you do not need point in time recovery on this database then you can switch the recovery mode to SIMPLE which would allow you to shrink your log file and relieve you of the need to worry about taking transaction log backups ever again.

Please read this article in its entirety: Managing Transaction Logs By Gail Shaw

Post questions you have about it, or any responses you have received on this thread so far, and we will try to safely help you out of the mess you are in.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1447194
Posted Friday, April 26, 2013 7:38 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
Are you in some kind of competition for a stupidiest advice ever?

Is it what you suggest your poor customers: switch recovery mode on all databases to SIMPLE???
So they do not worry about transaction log ever again?
Post #1447212
Posted Friday, April 26, 2013 10:44 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:32 AM
Points: 7,135, Visits: 12,745
For a database that does not need point in time recovery, yes, I would recommend changing to Simple recovery mode.

Try to keep it professional Sergiy.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1447218
Posted Saturday, April 27, 2013 2:18 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
opc.three (4/26/2013)
For a database that does not need point in time recovery,

Which means - can afford to lose data.
Which is not the case by default.

yes, I would recommend changing to Simple recovery mode.

You did not identify that condition in your statement, and still recommended this "solution".
Very "professional".
OP might be a newbie, might be unaware of proper ways to support databases.
So we suppose to teach him proper ways, not to add another cowboy hack to his arsenal of tools.

Try to keep it professional Sergiy.

I believe, my evaluation of your suggestion was absolutely professional.
Post #1447234
Posted Saturday, April 27, 2013 5:40 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:25 AM
Points: 20,801, Visits: 32,725
Sergiy (4/27/2013)


Try to keep it professional Sergiy.

I believe, my evaluation of your suggestion was absolutely professional.


No Sergiy, I would have to agree with opc.three, you weren't being professional in your response to him.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1447253
Posted Saturday, April 27, 2013 8:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:32 AM
Points: 7,135, Visits: 12,745
Sergiy (4/27/2013)
opc.three (4/26/2013)
For a database that does not need point in time recovery,

Which means - can afford to lose data.
Which is not the case by default.

Agreed. If you reread my post you'll see that I put forth a question and a set of possible responses the OP should ask and expect to determine if they should consider changing the recovery mode to simple.


yes, I would recommend changing to Simple recovery mode.

You did not identify that condition in your statement, and still recommended this "solution".
Very "professional".
OP might be a newbie, might be unaware of proper ways to support databases.
So we suppose to teach him proper ways, not to add another cowboy hack to his arsenal of tools.

Again, if you refer to my post you'll notice that I welcomed the OP to ask further questions to us if they had doubts about anything that wad said on this thread, or in Gail's article. Please consider your respond before polluting this thread any further, and allow the OP to ask questions if needed.

Try to keep it professional Sergiy.

I believe, my evaluation of your suggestion was absolutely professional.

If you are not only trying to make inflammatory comments and you truly believe you were being professional in your comments then I would be surprised if it were not extremely difficult for you to function in any of the business environments where I have contributed. I am not sure what it is like where you live and with the people you work with, but you have not met any of the criteria for professionalism in any of the circles I have operated in.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1447270
Posted Monday, April 29, 2013 11:42 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 35,554, Visits: 32,150
tshad (4/25/2013)
My Transaction log is about 3GB and I can't shrink it.

It is using a FULL backup and I try over and over again to back and shrink it and it won't do it.

DECLARE @DatabaseName VARCHAR(50);
SET @DatabaseName = 'myDatabase'

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = @DatabaseName

This gives me:

LOG_BACKUP in the log_reuse_wait_desc.

I have been all over the web looking for an answer but anything I try still leaves it the same size. I tried to change the inital size to 600MB but that didn't work.

Any ideas what I am missing?

Thanks,

Tom


Hi Tom,

There's a ton to learn about the Log File (LDF, usually).

In the area of performance, the initial settings are horrible. It'll grow the log file in a very fragmented way which can have a great impact on performance. It'll also allocate some pretty small "VLF's" or "Virtual Log Files". Think of it kind of like having a disk with a super small sector size. Not very effective. For more on how to "right size" the underlying VLF's in the Sql Server log file, please see the following URL.
http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

That brings us to the next point. How big is your database and how big is the largest table in the database? There are a few more aspects to database maintenance that use the log file and that you must consider when trying to right size the log file. Index maintenance and the rebuilding of statistics. Both are extremely import to performance especially when you have nightly jobs that affect or read from many rows and both are logged in the log file when they occur just in case something goes wrong. The link to Gail's article that you were previously provided will help there quite a bit. I use the rule of thumb that the log file should be about 1.5 times the size of the largest table to start with and then should be managed for growth to control the size of the VLFs that I previously spoke of. Log file growth should never be a surprise (should never happen automatically) in my humble opinion. Neither should the data file growth.

Another thing to consider is how important your data is. Way too many people will set a database to "Simple" recovery mode because it's a small database and they do nightly full backups and think that's enough. If the data is important, nothing could be further from the truth. Ask yourself how many minutes or hours of data your company can afford to lose and then setup log backups to run in no more than that amount of time. Personally, I won't tolerate the loss of any data so I do "Point-in-Time" backups on my production databases every 15 minutes. That also helps keep the log size down on busy systems. "Point-in-Time" backups require that only the FULL recovery mode be used. If you go to the Bulk Logged mode, then every backup taken while you're in that mode must be used in it's entirety or not at all during a restore... and you can't skip logs. That's why they call it a "log chain".

Getting back to your original problem, step 1 is to visit the articles I recommended above and figure out what the correct size log file should actually be. Even if you decide it's too small and needs to grow, if the initial settings were left at the default, consider blowing away the log and starting over.

Step 1 would be to take a full backup.
Step 2 would be to take a log file backup.
Step 3 would be to change the recovery model to the SIMPLE mode when not much is happening so that if something does go wrong during this timeframe, you won't lose much.
Step 4 is to shrink the log file to 0 bytes. Don't shrink the database. Shrink only the log file. This will clear out all the "bad stuff" with the incorrect sized VLFs.
Step 5 is to change the recovery model back to FULL.
Step 6 is to change the initial size of the log file to the size you planned on like I recommmended earlier. Also change the growth setting to grow in MB rather than percent just in case there's acccidental growth. The number of MBs should be with what size you want the VLF's to be if growth occurs.
Step 7 would be to take either a differential backup (smaller and quicker than a full backup) or another full backup to restablish the log chain.
Step 8 would be to turn on a maintenance plan or custom code to do log file backups every x number of minutes as previously discussed.
Step 9 would be to ensure that regular tape backups are being taken of all of your SQL Server backups.
Step 10 would be to setup a plan to delete the backups from disk after they've been backed up to tape and after some period of time has elapsed. I keep 2 to 3 days of backups on disk for quick recoveries if anything goes wrong and it has saved my bacon more than once (especially when you have developers and other folks that have too many privs in prod).

To emphasize, the only time that I'd ever set a database to SIMPLE recovery on a permannent basis is when I truly didn't give a damn what was in it. Those could be staging databases, scratchpad databases (similar to Temp DB), and maybe even experimental "sandbox" databases. I would never permanently set a production database to anything other than the FULL recovery mode.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1447882
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse