SQL2K Database Sizing Problem

[SupGuy]

Hi folks,

I'm a bit new to SQL, so I'm in 'absorption' mode when it comes to this topic, but I have a client who has a database that has been growing exponentially with no known application explanation. The .mdf file is roughly 2 gig or so, but the transaction log (*.ldf) has been growing like crazy. It currently sits at around 14 gig and has grown by 7 gig in just the last 3 weeks.

I've found instructions on truncating the log and shrinking the database, but my primary concern is finding out what is causing the growth.

From what I can tell, there isn't an easy way to see what's in the log in hopes of getting a clue as to the cause of the growth. I have found 'log viewers' out there which apparently do this, but wasn't sure if they'd help.

I've also been told that going to simple recovery mode might resolve the issue, but I've also heard that enabling that is somehow limiting when it comes to your recovery options and may not be recommended.

I'm looking for any tips on how I might tackle the topic of finding what's causing this log file growth.

Thanks in advance.

SupGuy

 

[tlbroadbent]

You can trace server activity using SQL Profiler. Profiler can be launched from the SQL Server Program group or from the tools menu of Enterprise Manager. Make sure you read about traces and Profiler in SQL BOL.

Lumigent Log Explorer is the tool I use to read the log file. It is an excellent product. There are other log reader programs available but I'm not familiar with them.

You can use DBCC LOG to view the log. This undocumented command is not as helpful as a 3rd-party log reader but may be useful. Open the following link to get an explanation of its usage.

http://www.sql-server-performance.com/ac_sql_server_2000_undocumented_dbcc.asp

Have you read faq183-1534 - "Shrinking Databases and Logs - SQL 7 and Higher" and faq183-1784 - "How Do I Develop a Backup and Restore Strategy?"

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[SupGuy]

Hi Terry,

Thanks for all the information. I'll check out the FAQs and information you provided. I really appreciate the tips.

SupGuy

 

[SupGuy]

Secondary question...since I have no experience to build off, is it 'usual' for the transaction log file to grow by the amount of size in my original post, assuming that they may not be doing the proper database or transaction log backups, etc? Or would the 7 gig growth in 3 weeks on a relatively quiet database surely indicate some type of transaction gone awry?

SupGuy

 

[SQLSister]

If there are a lot of data imports running this might not be unusual. Particularly if they are scheduled on a daily basis. The growth is probably related to the fact that they are not performing transaction log backups. One would hope that a 2 gig databse would have a good backup plan in place, but one would all too often be wrong. Certainly I would look at their backup schedule.

Read Terry's FAQ on Backups for help in this regard.
How Do I Develop a Backup and Restore Strategy?
faq183-1784

 

[tlbroadbent]

Many things can cause a transaction log file to grow much bigger than the data file even if you have a good backup scheudle. For example, we rebuild indexes on some of our databases once per week. The first transaction log after the rebuild is about the same size as the data file. A large number of deletes can reduce the database size and increase the log file size, at least until the log is truncated and the file is shrunk.

At this point, I don't think you have observed anything out of the ordinary. However, you should get onto a reasonable backup schedule and then measure log growth over the next few weeks to see if there is any extraordinary growth identified.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm