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 ««123»»

huge log file Expand / Collapse
Author
Message
Posted Friday, April 26, 2013 8:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:48 AM
Points: 14,802, Visits: 27,278
zi (4/26/2013)
(you may have to run a checkpoint or two before the log rolls over to the free space to allow shrinking) will work,

how to run a checkpoint?

Thanks


It's a t-sql command:

CHECKPOINT


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1447020
Posted Saturday, April 27, 2013 12:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:26 PM
Points: 5,961, Visits: 12,847
zi (4/26/2013)
(you may have to run a checkpoint or two before the log rolls over to the free space to allow shrinking) will work,

how to run a checkpoint?

Thanks


use [yourdb]
GO
checkpoint



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1447230
Posted Saturday, April 27, 2013 6:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, January 02, 2014 11:13 AM
Points: 51, Visits: 279
zi (4/25/2013)
Dear Experts

I have a 3 GB mdf file and 355 GB ldf file.
What should I do, I have taken a backup to the transaction log, should i delete the log file

Thanks


A sledgehammer approach is:

1)backup database
2)detach database
3)copy mdf just in case
4)rename ldf
5)single file attach the mdf.

End result:
You have 5-10 minutes downtime.
A log file that is small. Read the articles about what size it needs to be etc
No waiting until next xmas for the logfile to shrink


Suggest you practice this on a dev server as the permissions of the file can cause a slight hiccup
Yes you can use the gui or a tsql script

Just as an aside: how did it get into this condition? Many years ago I had a similar issue when some consultants came in took responsibility for a system and then no one bothered to tell us that the consultants had finished. 3 weeks later we had a 300GB log due to autogrow and no log backups. The term consultants at times is open to debate





Post #1447263
Posted Saturday, April 27, 2013 7:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:26 PM
Points: 5,961, Visits: 12,847
Surely you mean

Steve JP (4/27/2013)
1)switch to simple recovery
2)backup database
3)checkpoint database
4)shrink log


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1447268
Posted Saturday, April 27, 2013 9:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, January 02, 2014 11:13 AM
Points: 51, Visits: 279
Depends as the logfile doesn't always want to shrink. If it doesn't then like I said a single file attach is a sledgehammer approach, never said it was a preferred method
Post #1447276
Posted Monday, April 29, 2013 12:15 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:17 AM
Points: 4,379, Visits: 9,470
Steve JP (4/27/2013)
Depends as the log file doesn't always want to shrink. If it doesn't then like I said a single file attach is a sledgehammer approach, never said it was a preferred method


This may not actually work - and you end up having to attach with the current transaction log file. Much simpler to just switch the recovery model to simple, issue a checkpoint - validate which VLF's are in use and shrink the file until it is the size you want.

You may need to shrink a couple of times, depending on when the log rolls back to the beginning of the file - but this will work and the log will shrink. Unless there is something else causing the log to not be able to be truncated (e.g. replication).


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1447688
Posted Monday, April 29, 2013 12:36 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 35,978, Visits: 30,269
zi (4/26/2013)
(you may have to run a checkpoint or two before the log rolls over to the free space to allow shrinking) will work,

how to run a checkpoint?

Thanks


At this point, I'm going to recommend that you slowly back away from the computer and approach the phone to call a pro in for help. I understand that you're trying to fix this problem but you can really screw things up if you haven't done this a half dozen or more times before. Production is not the place to learn these fixes.

Seriously... get a pro in to help you.


--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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1447695
Posted Monday, April 29, 2013 12:43 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, January 02, 2014 11:13 AM
Points: 51, Visits: 279
Jeffrey Williams 3188 (4/29/2013)
Steve JP (4/27/2013)
Depends as the log file doesn't always want to shrink. If it doesn't then like I said a single file attach is a sledgehammer approach, never said it was a preferred method


This may not actually work - and you end up having to attach with the current transaction log file. Much simpler to just switch the recovery model to simple, issue a checkpoint - validate which VLF's are in use and shrink the file until it is the size you want.

You may need to shrink a couple of times, depending on when the log rolls back to the beginning of the file - but this will work and the log will shrink. Unless there is something else causing the log to not be able to be truncated (e.g. replication).


When you attach a database file, if the log file cant be found then it will recreate a default sized log file at the correct location. I think the new size is based on the model.ldf file size.

True its not ideal, hence my comment that it is a sledgehammer approach, unless you see a sledgehammer as a panacea in which case.......
But, if you only have a tiny maintenance window and the shrink doesn't work then yes the single file attach db is a method that does work.

In the old days (7 & 2000) the only other method to force a log file shrink was to move the active part of the log to the front before a dbcc shrinkfile was successful. It wouldn't resize to anything less that the last offset of the active virtual log file. And yes, to move the active part meant having a loop to consume a massive amount of log space with many log file backups and diff backups to maintain the SLAs. Updating a dumb row in a dumb table a million times with a replicate ('a' , 8000 ) does nudge the active part of the log file

On a personal note I wouldnt want to start flipping the recovery state to simple on a business critical system unless it was during a maintenance window. All depends on the SLAs of course, but I have seen too many good intentions and "it will only be for a minute" going awry to risk it.
Post #1447699
Posted Monday, April 29, 2013 2:03 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, April 17, 2014 11:17 AM
Points: 4,379, Visits: 9,470
Steve JP (4/29/2013)
[quote]When you attach a database file, if the log file cant be found then it will recreate a default sized log file at the correct location. I think the new size is based on the model.ldf file size.

True its not ideal, hence my comment that it is a sledgehammer approach, unless you see a sledgehammer as a panacea in which case.......
But, if you only have a tiny maintenance window and the shrink doesn't work then yes the single file attach db is a method that does work.

In the old days (7 & 2000) the only other method to force a log file shrink was to move the active part of the log to the front before a dbcc shrinkfile was successful. It wouldn't resize to anything less that the last offset of the active virtual log file. And yes, to move the active part meant having a loop to consume a massive amount of log space with many log file backups and diff backups to maintain the SLAs. Updating a dumb row in a dumb table a million times with a replicate ('a' , 8000 ) does nudge the active part of the log file

On a personal note I wouldnt want to start flipping the recovery state to simple on a business critical system unless it was during a maintenance window. All depends on the SLAs of course, but I have seen too many good intentions and "it will only be for a minute" going awry to risk it.


There is the possibility that you will not be able to attach that database without the log file. The database has to be shutdown cleanly, with no open or in flight transactions that would have to be rolled back/rolled forward. If that situation occurs, the database will not create a new log file - and you then have to put the old log file back.

The ideal solution is to perform a log backup, but in this type of case - the ideal solution probably wouldn't work. So, changing the recovery model will truncate the log. Once the log has been truncated, you can then shrink the file to the active VLF...

Issue a checkpoint - maybe a couple - to get the log to roll back to the beginning and shrink again. Repeat until you have the log file at the appropriate size.

Once you have that, switch recovery model back to full - take a full backup - and restart your transaction log backups.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1447753
Posted Monday, April 29, 2013 8:15 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:26 PM
Points: 5,961, Visits: 12,847
Jeffrey Williams 3188 (4/29/2013)
switch recovery model back to full - take a full backup - and restart your transaction log backups.

Taking a diff backup instead of a full maybe quicker and will still restore the log chain.


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1447844
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse