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


huge log file


huge log file

Author
Message
zi
zi
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1211 Visits: 338
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
M&M
M&M
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12432 Visits: 3919
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

M&M
zi
zi
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1211 Visits: 338
can i shrink the log file
I want to solve the space problem
zi
zi
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1211 Visits: 338
any idea how to solve this problem

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

Thanks
Kingston Dhasian
Kingston Dhasian
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11458 Visits: 5331
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/
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: General Forum Members
Points: 224383 Visits: 33576
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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
zi
zi
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1211 Visits: 338
is it correct to convert the recovery model to simple then shrink the log file

Thanks
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)SSC Guru (226K reputation)

Group: General Forum Members
Points: 226377 Visits: 40443
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.

Cool
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)
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: General Forum Members
Points: 224383 Visits: 33576
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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
zi
zi
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1211 Visits: 338
(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
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