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 Thursday, April 25, 2013 9:23 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 96, Visits: 262
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
Post #1446781
Posted Thursday, April 25, 2013 9:43 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 2:03 PM
Points: 2,278, Visits: 3,806
zi (4/25/2013)
Dear Experts

should i delete the log file

Thanks


Please don't try this ever.

Just try this.
select log_reuse_wait_desc,* from sys.databases where name='dbname'
-- see what it is waiting on

also run this dbcc sqlperf(logspace)
-- see log space utilization


Mohammed Moinudheen
Post #1446782
Posted Friday, April 26, 2013 12:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 96, Visits: 262
can i shrink the log file
I want to solve the space problem
Post #1446813
Posted Friday, April 26, 2013 3:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 96, Visits: 262
any idea how to solve this problem

is it correct to make backup log then shrink the log file

Thanks
Post #1446884
Posted Friday, April 26, 2013 4:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:52 AM
Points: 2,689, Visits: 4,748
What is the recovery model of your database? Is suspect it to be FULL.

If your recovery model is FULL, follow the below steps
1. Take a full database backup
2. Shrink the log file to an appropriate size
3. Take a full database backup again
4. Schedule transaction log backups to avoid such issues in the future

You can also check the below mentioned article for more information on Managing Transaction Logs
http://www.sqlservercentral.com/articles/Administration/64582/



Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1446888
Posted Friday, April 26, 2013 4:16 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 14,037, Visits: 28,407
After you either backup your log or switch your database to simple recovery, you can shrink the log file. But you need to do one or the other. I have a blog post up the explains the problem and the best solutions.

----------------------------------------------------
"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
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 #1446891
Posted Friday, April 26, 2013 7:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 96, Visits: 262
is it correct to convert the recovery model to simple then shrink the log file

Thanks
Post #1446962
Posted Friday, April 26, 2013 7:26 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 20,815, Visits: 32,748
zi (4/26/2013)
is it correct to convert the recovery model to simple then shrink the log file

Thanks


Read the blog post that Grant provided you a link to above.

Your t-log is growing because you are in full recovery model with, most likely, no regularly scheduled transaction log backups to maintain th size of the transaction log.



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 #1446981
Posted Friday, April 26, 2013 7:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 14,037, Visits: 28,407
zi (4/26/2013)
is it correct to convert the recovery model to simple then shrink the log file

Thanks


That question is best answered by the people who use your database. How much data can they afford to lose? How much down time can they have?

As a quick fix converting to simple and then shrinking the log file (you may have to run a checkpoint or two before the log rolls over to the free space to allow shrinking) will work, but leaving the database in the simple model means no point in time recovery.


----------------------------------------------------
"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
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 #1446987
Posted Friday, April 26, 2013 7:53 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 96, Visits: 262
(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
Post #1447007
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse