Transaction log file too large

Question

Post reply

Transaction log file too large

Viewing 15 posts - 16 through 30 (of 40 total)

You must be logged in to reply to this topic. Login to reply

webrunner SSC-Dedicated Points: 31724 More actions · Answer 1

Just an update - the transaction log file size is now up to about 2 GB.

Is this OK?

Thanks again.

----

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Michael Carey-400169 Mr or Mrs. 500 Points: 512 More actions · Answer 2

This may be a very basic question, but do index rebuilds grow the transaction logs?

I have a nightly job that goes through these steps. First, DBCC, second Update Statistics, third organize indexes, fourth rebuild indexes.

To attempt to avoid the build up of the .ldf file, i switch the recovery model to bulk-logged before the job, and back to Full as the last step in the job.

I am suspecting that rebuilding indexes in this way causes a large number of transactions, which grows the transaction log, and probably the .ldf files.

Any comments appreciated.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Lynn Pettis

SSC Guru

Points: 442467

May 7, 2007 at 7:14 am

#704626

Short answer, yes.

Michael Carey-400169 Mr or Mrs. 500 Points: 512 More actions · Answer 4

Thanks, good to have confirmation on that. So. Perhaps the person that started this whole subject could look to his index-rebuilding sequences to see if that is why his transaction logs are growing.

aureolin Hall of Fame Points: 3520 More actions · Answer 5

I think the one thing you can get out of this thread is that there isn't a simple answer to this question. How big the transaction log gets depends on what's happening in the database, how often you do back ups, etc. 'Too big' really depends on how much disk space you have available, and if you're experiencing performance issues.

I'd strongly suggest that you need to take a hard look at your database activity and really understand what's happening and how your database is being used. Profiler is a great tool for this sort of thing.

Good luck!

Steve G.

webrunner SSC-Dedicated Points: 31724 More actions · Answer 6

Thanks! I think it turned out that the performance issue in this case was not due to the database, which supports your suggestion that the tlog size was not the culrpit.

Nevertheless, I will set up a profiler trace to watch what happens during the backup maintenance plan, which I do have set up to rebuild the indexes.

Is there any value in particular that will stand out if the rebuild is causing the transaction log to grow a lot?

Thanks again,

webrunner

---

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Charles Kincaid SSChampion Points: 13593 More actions · Answer 7

Check the database design. I had one coworker who put a clustered index on a table and included the last update time field. Every time a row got updated it moved.

I've also seen tables with temporary data in the main database. One app needed some complex reporting so the coder put data in a table and then deleted it when the report was done. The module ended with DELETE FROM [ProductionSummaryReport] WHERE UserID = 'Me' Thousands of inserts and deletes from that one table every hour per department. Introduced that developer to the magic of temp tables.

ATBCharles Kincaid

Deana Sanders Mr or Mrs. 500 Points: 568 More actions · Answer 8

I am running into a similar situation and need to figure out how best to proceed. The database houses GIS data and is large. There would be few users and few transactions during the day. There was no backup system in place before I came on board so I started by implementing a weekly full backup and daily transaction log backups. The transaction log file was set to unrestricted growth so I restricted it. Additionally, there is not enough space on the server drives to save the backup files for very long.

So, my question is this: what is the best way to handle this? The Monday evening transaction log file may run 21 GB and the Tues - Fri Tran logs may be less than 1 GB each. The ldf file is slowly growing, but the dbcc sqlperf(logspace) shows that the logfile is 0.01% used. The dbcc loginfo (?) shows ~500 rows, indicating ~500 VLFs. My last attempt as shrinking this failed.

Now what? Would the consistency checking during the full backup cause the Monday tran log backup to be so large?

Regards,

Deana

Steve Jones - SSC Editor SSC Guru Points: 734498 More actions · Answer 9

The log file might have been created that large. You can't shrink beyond the initial size.

Deana Sanders Mr or Mrs. 500 Points: 568 More actions · Answer 10

I'm not sure what the initial size was, but I have expanded the log size 2-3 times over the past 3 months. Your comment about shrinking the log is good to know, though.

Deana

Steve Jones - SSC Editor SSC Guru Points: 734498 More actions · Answer 11

There are two other things I thought up since the last post.

1. You might have a virtual log file at the end of your log file that prevents it from being shrunk. You can use a script like this one to help with that: http://www.sqlservercentral.com/scripts/Maintenance+and+Management/30026/

2. Be sure you're shrinking the log file and not the database. You do not want to shrink the database as it messes up your index ordering and introduces fragmentation.

Charles Kincaid SSChampion Points: 13593 More actions · Answer 12

Deana Sanders (10/1/2007)
I'm not sure what the initial size was, but I have expanded the log size 2-3 times over the past 3 months. Your comment about shrinking the log is good to know, though.
Deana

Deana you don't say if you are SQL 2005 or 2000. Is there a time that you could take the sever down for a bit? There is a sneaky thing that you can do in SQL 2005 that you can't do with other versions to get your initial log size very small.

On other thing puzzles me. You indicate that this is a mostly static database and yet you are reporting tremendous growth in the log.

There would be few users and few transactions during the day.

Does this mean that there is a heavy transaction load at night? I feel you are being honest about your situation and I don't want to probe into anything proprietary but I would like more details before giving you something to try. You do have a backup server for this database on which you can conduct experiments, right?

ATBCharles Kincaid

Deana Sanders Mr or Mrs. 500 Points: 568 More actions · Answer 13

Thanks for the offer of assistance. I checked this morning -- the mdf is 20GB and the ldf is 28GB. No heavy user transactions running at night except for the transaction log backups. I am running SQL Server 2005 with no SPs.

As for taking down the server, I would have to get permission from the customer to do that. Are you referring to detaching the database, deleting the ldf, and then retaching the database? I hadn't quite wanted to try that yet.

The server in question is a production server. The situation on the development server is similar, but I have a bit more room to save the tran logs on that server.

At this point I was going to try the backing up the log, doing a dbcc shrinkfile, and then trying altar database. Possilby tonight.

Regards,

Deana

Charles Kincaid SSChampion Points: 13593 More actions · Answer 14

Deana Sanders (10/1/2007) ... Are you referring to detaching the database, deleting the ldf, and then retaching the database? I hadn't quite wanted to try that yet.
... At this point I was going to try the backing up the log, doing a dbcc shrinkfile, and then trying altar database. Possilby tonight.
Regards,
Deana

Yes. I was quite shocked when one of my coworkers brought that procedure to mu attention. I does work. This is the reason that I asked about a development/test server. I learned something from a marketing guru that helps. "Practice, drill, rehearse." Use you development environment for your brute force playing. Take it from a developer, we can take it.

I like your idea of doing the shrinkfile first. Less brutal. One tip from SQL 2000 is that the GUI shrinkfile did more shrinking than the command line stuff. Might not be true in 2005 anymore. But this is, again, a reason to practice on the development box.

ATBCharles Kincaid

Deana Sanders Mr or Mrs. 500 Points: 568 More actions · Answer 15

How true, how true. I have a similar situation with the dev server so i'll practice first on it.

Thanks for your help.

Deana