Solved

HELP - My Transaction Log is HUGE!

Posted on 2008-10-07
18
932 Views
Last Modified: 2012-05-05
First off I am not a SQL guy so I really need some assistance.  We are running a SBS2003 server with SQL 2005 Premium.  One of the databases is around 600MB and the transaction log is 125GB!!  I attempted to backup the database but this did not clear the transaction log.  I also tried backing up the transaction log but I did not have enough free space to back it up.  I'm not sure if backing up the transaction would truncate it anyhow.  Can anyone tell me how to get this back down to a normal size and how to prevent this from happening moving forward?

Thank you!
0
Comment
Question by:DennisDavis
  • 5
  • 3
  • 3
  • +5
18 Comments
 
LVL 14

Accepted Solution

by:
Jagdish Devaku earned 500 total points
ID: 22666429
HI,

Run the following query:

Dump Tran <db_name> with no_log
DBCCshrinkdatabase(<db_name>,30)

Before running the above query take the backup of the database....


Please let me know if you face any issues...

all the best... bye...
0
 
LVL 2

Expert Comment

by:simeonf
ID: 22666452
Hi Dennis,

You can clear the log by executing the following script.

Note this will not backup the logs at all, and it's not a best practice as it will just dump all records of transactions (not committed data though), so you will lose ability to recover from the current point in time.

If you want to be able revert back just in case, before doing this take the database offline and copy the database and log files together to someplace you have enough storage (eg a USB hard drive). Then bring it back online and perform the truncate. If you have any probs you can take offline and safely revert the files!

After the truncate you should schedule a regular database and transaction log backup to ensure it's being cleared out.

Cheers,
Simeon
BACKUP LOG <mydb> WITH TRUNCATE_ONLY

Open in new window

0
 
LVL 14

Expert Comment

by:Jagdish Devaku
ID: 22666469
HI,

Run the following query given in the code snippet.
Before running the query take the backup of the database....
Please let me know if you face any issues...

all the best... bye...
Dump Tran <db_name> with no_log

DBCCshrinkdatabase(<db_name>,30)

Open in new window

0
 
LVL 4

Expert Comment

by:Maxi84
ID: 22666830
As you've experiencedl, a regular backup doesn't truncate the log.  In the future, you should set up regular transaction log backups in addition to the full and incremental backups.  Since trans log backups really don't take much time nor resources to run, there's no harm in running them often, say every hour or so, or even more frequently if so desired.  That way, you would also minimize loss of data in the event of a system failure.

For databases where transaction log backup is not a requirement, e.g. development DBs, setting the recovery mode to 'simple' is the way to go.
0
 
LVL 1

Expert Comment

by:Digitasupport
ID: 22667132
Q :- Why are you running Full Recovery Mode?Do you need to do this , and if the MDF goes down are you capable of rebuilding it using the LDF?
If not , I would
a) Stop SQL Server service.
b) Backup or move out the LDF file from the data folder.
c) Start SQL server service , and SQL will generate a new log file.
d) Go into SMS or Enterprise Manager , and check the Options on the specific database , and make sure the recovery model is set to simple...
:)
Good luck!
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 22668366
BACKUP LOG <yourdb> WITH TRUNCATE_ONLY and switch to simple recovery mode
is the best thing to do on short term to give you time to inquire...You then need to determine what is causing and take compensatory action to control the growth....can be regular shrinks of the log file or allocating additional space for logs...
0
 
LVL 4

Expert Comment

by:ThorSG1
ID: 22668554
This will work also.  But this will target the log file only and not the log file and data file.

Run this to reorganize the log file:
dbcc shrinkfile (logical_logfile_name, 1, notruncate)
Then run this to truncate the log file:
dbcc shrinkfile (logical_logfile_name, 1, truncate)
0
 

Author Comment

by:DennisDavis
ID: 22670191
I ran the BACKUP LOG <yourdb> WITH TRUNCATE_ONLY command and it said it ran successfully however the .LDF file is still 125 GB in size.  Doesn't appear anything has changed.  As well this command ran almost instantaneously so seems a little odd.  Any thoughts?

If I  stop the SQL service and move the .LDF file out and restart sql will this fix the problem?  What is the risk?
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 22670268
<<Doesn't appear anything has changed.  >>
OK...Please confirm the current used size of file....Run....

dbcc showfilestats with no_infomsgs  

If the is full then attempt a shrinkfile...

<<As well this command ran almost instantaneously so seems a little odd.  Any thoughts?>>
That's normal...The command does run fast...

HTH
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 1

Expert Comment

by:Digitasupport
ID: 22670337
try moving it out , thats what i would do .
Stop SQL , move LDF , start SQl , SQL will regenerate LDF file.
Give it a shot , you can always reverse it if needbe.
cheers
0
 

Author Comment

by:DennisDavis
ID: 22670353
Problem is I dont' have any space to be moving it out to.  I just tried renaming with .old but db didn't like that when i started sql back up.
0
 

Author Closing Comment

by:DennisDavis
ID: 31504118
thank you.  This worked for me.  Although I did find out there had to be a space between DBCC and shrinkdatabase.  I also adjusted my database to Simple for now until I can figure out the best backup methodology here.  Thanks again for everyone's help!!  Very much appreciated!!!
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 22670463
<<Stop SQL , move LDF , start SQl , SQL will regenerate LDF file.>>
Can you confirm the used size before doing anything with instruction provided ?

HTH
0
 

Expert Comment

by:wcross75
ID: 23218804

So is the final solution here have two parts..
A. Fix the immediate problem (running out of storage space)
If I am following correctly, any *_log.LDF file can be moved to another drive and SQL will recreate it...
For example,  I have three large "_log.LDF" files all over 100MB...
  • Sharepoint_config{ ....} _log.LDF                     (560 mb)  
  • Wss_Content { ....}_log.LDF                             (110 mb)  
  • Sharepoint_AdminContent{ ....} _log.LDF      (110 mb)
So, I can do the following to get rid of all three large files(?)
  1. Stop SQL (this can be done in SMS)
  2. Move file (cut, paste all three listed above) to new drive...
  3. Start SQL (this can be done in SMS)
And when I restart SQL, all three files will be recreated by SQL?
B.  Fix or prevent logs from growing huge in the future
Part of my problem here was that I do not have SQL 2005 Manager Express or SQLCMD or SQLCLI installed on my server...AND, that default SQL 2005 install did not allow remote access using SQL 2005 Manager Express.

However, once I located and installed SQLCI and SQLCMD on my server (in this order), I was able to you a pipe connect string as follows :
SQLCMD - S \\ . \pipe\mssql$microsoft##ssee\sql\query -E
to get here ..
1> {prompt...}
What SQLCMD compatible commands can I use to:
1. Make recovery mode simple:
1>  USE [master]
2> GO
1> ALTER DATABASE [EE] SET RECOVERY SIMPLE WITH NO_WAIT
2> GO
1> ALTER DATABASE [EE] SET RECOVERY SIMPLE
2> GO
   Should [EE] above be [master] instead?
 2. Is this everything needed to prevent the problem inthe future?
Bill
0
 

Expert Comment

by:wcross75
ID: 23227992
Deleting Log files and expecting SQL server to recreate them did not work.
This did...
 
 
 
0
 

Expert Comment

by:wcross75
ID: 23228015
oops sorry....this link looks like it will work...
WSS 3.0 side-by-side on Small Business Server - How do I shrink Huge logs
 
Bill Ross
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 23228103
<<A. Fix the immediate problem (running out of storage space)>>
No need to stop the server.  Simply detach the files, copy the files and reattach them in their new drive.

<<Is this everything needed to prevent the problem inthe future?>>
Log growth is greatly reduced if the SIMPLE recovery mode is simple but you won't be able to recover at point in time. You will still however have to monitor growth and upon it when the percent log growth reaches a specific percentage. The instruction DBCC SQLPERF(LOGSPACE) allows to monitor log growth. Since you are in SIMPLE mode, you can perform perdiodically a BACKUP LOG WITH TRUNCATE_ONLY to contain the issue or BACKUP LOG to another drive if the FULL mode is reactivated.

HTH
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 23228135
Appart from that, keep in mind that log control is a not a trivial recurring administrative task that involves backup strategy considerations.  You may consider getting help from some DBA in your company if you have any.

HTH
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now