SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


huge log file


huge log file

Author
Message
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40321 Visits: 32665
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20108 Visits: 17244
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" ;-)
Steve JP
Steve JP
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 328
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 :-)
Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20108 Visits: 17244
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" ;-)
Steve JP
Steve JP
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 328
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 :-P
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7791 Visits: 9971
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 :-P


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 opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86988 Visits: 41107
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Steve JP
Steve JP
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 328
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 :-P


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.
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7791 Visits: 9971
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 opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Perry Whittle
Perry Whittle
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20108 Visits: 17244
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" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search