Solved

Log file very very big

Posted on 2003-11-24
11
1,103 Views
Last Modified: 2012-06-27
Hi all xperts,
I'm runnig Sql Server 2000 and my log file suddenly grew up to 17GB!!!. I have ran the Shrink Database command via Enterprise Manager and  the Backup Log too (with Truncate_Only option). After that the log file shrunk down 3 GB, now it has 14 GB. What should I do???. Need help please.

Thanks in advance
EB
0
Comment
Question by:EBatista
11 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 9813276
See what transactions that are running making the log file big (sp_who or sp_who2 in query analyzer).  If you don't need point in time recovery, change the database recovery mode to simple or bulk logged.

Brett
0
 

Assisted Solution

by:kauf
kauf earned 50 total points
ID: 9813792
if the transaction log is primary you probably wont have a chance to shrink it. (as far as i got)
did you try following?
BACKUP LOG [DATABASE] WITH TRUNCATE_ONLY

and after that shrinking it? (this will clear the t-log and allow you to resize, but resize is not allowed on primary t-logs, just an secondaries)

the last time i had this problem i had to detach db, and re-attach after moving the t-log somewhere else (probably not a good method to do :)).
0
 
LVL 10

Author Comment

by:EBatista
ID: 9813797
albert, the sp_who dont show nothing strange, everything seems to be ok.
and yes,  I need point in time recovery.
I cant be sure right now if this issue come suddenly or it was a more slower process that I dont realize.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 34

Expert Comment

by:arbert
ID: 9813832
Do you have any DTS jobs or import functions that happen on your database?  Any big deletes or inserts?

You can also run Profiler to capture SQL traffic for a while to figure out what's going on.

Brett
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 9815257
Please let me know if you need help maintianing this old open question:

http://www.experts-exchange.com/Databases/MS_Access/Q_20729987.html

Thanks,
Anthony
0
 
LVL 10

Author Comment

by:EBatista
ID: 9817250
kauf, I have also done the Backup log with truncate_only method and then shrink the database, after that  nothing happen with the t-log. Should I detach the db exactly how you did. I cant think there isnt any other cleanest way.

arbert: I ran a DTS package when I migrated from DBFs data files to SQL server, but it was several month ago and I have never used it again.

EB
0
 
LVL 34

Accepted Solution

by:
arbert earned 75 total points
ID: 9817765
Well, if you really don't know why the log file is growing, I would run profiler for a while and see what's causing the transactions.

0
 

Expert Comment

by:rsrsm
ID: 9818761
Have you configured any replications...or as Arbert has said capture the events in the profiler and check. Also "truncate at check point" option will truncate the log.
0
 
LVL 34

Expert Comment

by:arbert
ID: 9819008
rsrsm, "truncate on check point" is a sql server 7.0 option....Doesn't apply to 2000 (bulk logged or simple recovery mode is about the same)....
0
 
LVL 5

Expert Comment

by:snimmaga
ID: 9819575
There is a simple problem with 'truncate at checkpoint' option or bulk logged or simple recovery options if you will.

The problem is a Checkpoint does not occur until the atomic transaction is completed.  In other words, the atomic transaction that is building the log size will not be truncated with this option.  So, it does not matter what you do - your log size is going to grow.

A simple example - Update all rows of a 5 million row table.  It is an implicit transaction and the checkpoint does not occur until this update is completed.  Even if it occurs, the checkpoint is put prior to this transaction and the log is truncated only till that part.

In other words, you don't have choice but to look for the culprit SQL that is filling up the log.

Good luck..
Srini
0
 
LVL 10

Author Comment

by:EBatista
ID: 9820196
ok guys I did it, finally my T-Log is now 4MB again, this is what I did:
First I ran BACKUP LOG [DATABASE] WITH TRUNCATE_ONLY option from Query Analizer,
then I made a complete database backup,
then I made a normal backup to the T-Log file within Enterprise Manager (the resulting backup file was 180MB),
and finally I ran the Shrink Database command with "Move page to beginning of file before shrinking" option checked, and this time I have selected only the T-Log file for shrink, in "Shrink file to" I wrote the minimum value suggested for this file.

That was all, I dont know if all that steps are necessary or not, I just write what I did,  but I this issue really stressed me a lot.

Thanks you for your time
EB
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to share SSIS Package? 6 37
transaction in asp.net, sql server 6 33
Create snapshot on MSSQL 2012 3 18
access query to sql server 3 20
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

777 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