November 9, 2005 at 6:50 am
Hello there,
We recently had a problem(nov 2) with our ERP, which forced us to restore our SQL Server DB from the previous nights backup. Since then we've had some problems with replication, and I think I have this issue pretty much wrapped up. The new issue I'm seeing is that the LDF file for our production database is huge. We have 2 files relating to our production database residing in C:\MSSQL\Data. We have our Production file which is 15 gigs and we have Production_log file which is 30 gigs. We tried using the shrink function on this log file and it took about 30 minutes to run but the difference was negligable. My question is
a) What is inside this Production_Log file
b) What possible reasons could make it grow so large
c) How do we shrink it.
Any help would be much appreciated. Thanks in advance.
Parag
November 9, 2005 at 7:19 am
Your log file won't shrink if it has uncommitted transactions in it.
Any mass inserts, deletes, updates will cause the log file to grow significantly.
November 9, 2005 at 7:28 am
Is there a way to find out if there are any uncommitted transactions in it? When we first tried to reestablish replication I believe the publisher was set up wrong as we didn't have an active Log Reader Agent. The snapshot worked fine and the Distribution agent ran, but it didn't seem to be synchronized. I stopped the Distribution Agent and we dropped the Publisher. We're going to re-add the Publisher and then go through the replication estblishment process.
November 10, 2005 at 6:57 am
There is probably an uncommitted transaction in it. I don't know of a way to look into the log without using a third party product like Log Explorer. I think your problem is that you need to clear out the committed transactions by backing up the transaction log. Once you do that, you should be able to shrink the log down to an appropriate size. I don't know if you normally do a transaction log backup, but a normal database backup will not remove the committed transactions from the log.
November 10, 2005 at 7:52 am
Hello all thanks a bunch for your help. Here's what I found and it worked, so if any of you guys out there find that your LDF file has become a monster and its much bigger than the MDF file here's what you do.
First you detach your database using the following command
EXEC sp_detach_db 'yourdatabasename', 'true'
Then you find you obiest LDF file and Delete it...Do not delete the MDF file I repeat DO NOT DELETE THE MDF FILE...if you do you're SOL.
Then you reattach the MDF file to your database using the following command
EXEC sp_attach_db @dbname= 'yourdatabasename',
@filename1 = N'MSSQL\Data\yourdatabasename_data.mdf'
This reattaches the MDF file and creates a new blank log file. Hope this helps people in the future.
November 10, 2005 at 8:06 am
Hi Parag,
I have some suggestions to your questions
a) What is inside this Production_Log file
b) What possible reasons could make it grow so large
c) How do we shrink it.
Inside the production_log are committed and uncommitted transactions.
Possible reason for large log is hung transactions, in your case with replication use this looks very much possible. For 15-gig data file, log size of 30-gig suggest hung transactions and problem with log file.
From the given scenario I assume that you have bulk inserts or high number of transactions taking place. Regular log backup [say hourly or 2 hourly] is required
If you have that plan in place then great, else better go for it.
Whenever you take full db backup better go through scheduled job
Preferably once in a day, here is steps in that job that will help resolve to
your log file problem now and in future.
Job1: Database Backup [Every Night]
Step1: Truncate Log File
Backup Log Production With Truncate_Only
DBCC Shrinkfile(Production_log, 200)
Step 2: Backup Database
Backup Database Production
To ProductionBackup
With Init, Name = ' Production Backup'
Step 3: Initiate Log File
Backup Log Production
To ProductionLog
With Init, Name = ' Production Log'
First time when you execute this job it will take sometime as your log size is big.
From next time onwards you will find with this job that your log is not growing this big.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
November 10, 2005 at 8:18 am
There is a Risk of loosing log data associated with
Detach--Delete Log -- Attach Datafile approach.
Think, once you Attach mdf and have fresh ldf
before next full db backup if for any reason your mdf file goes bad/corrrupt
then only thing you can do is to restore db from last good backup ,
it can be yesterday, last week or whatever your schedule is.
which is hard for businesses to deal with.
Parag, in your case I suggest you to take immediate full db backup.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
November 10, 2005 at 8:54 am
I agree with Sameer. You usually want to do this as a last resort. You need to manage the growth of the log on a daily basis, so this does not happen again. On databases that there is no need to take an hourly transaction log backup, for example, I still do a transaction log backup at night before I do my full backup. This clears out all committed transactions and allows the TL to stay a managable size.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply