Determining what is causing massive log file growth on a basically inactive SQL 2005 DB

views:

answers:

Determining what is causing massive log file growth on a basically inactive SQL 2005 DB

I have a SQL DB in a test environment that ist he back end datasource for a test web site. There is no activity on the website (because it is in our test environment) but every couple of days the DB log files grow extremely large causing issues with backups, etc..

This is a test environment so there is no activity that we don't have control over. Why are the logs growing at all? And why so large?

I am looking for some tips on how to track down what is causing this log growth on a basically inactive site/DB.

I have looked for open transactions using DBCC OPENTRAN with the result of no open trans.

Any other ideas?

TIA J

+1 A:

Do you have any jobs running on a timed basis such as backups or optimizations? Doing a full rebuild of indexes as part of the maintenance job can cause the log files to be larger than expected (among other things - that is one of the most common).

Places to look in SSMS for jobs:
Management - Maintenance Plans
Management - Legacy (if you were using older versions of SQL at some point)
SQL Server Agent - Jobs

ktharsis 2010-05-13 16:57:26

There is a job that Alters the index with REORGANIZE with LOB_Compaction = on.Not familiar with this process so will have to do some reading.

John 2010-05-13 19:13:29

There is also a rebuild job that does this:ALTER INDEX [PK_myTable] ON [dbo].[myTable] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )GO

John 2010-05-13 19:31:29

Most likely it is the rebuild. We use the simple recovery model on dev/test since it is never necessary to have a point in in time recovery. You could also take the rebuild out of the maintenance plan on dev/test (there isn't much use so the indexes won't get fragmented as fast as they do on production).Paul Randals blog (http://sqlskills.com/blogs/paul/) has more than you will ever want to know about SQL Server. Check out the indexing section. SQL Server help (Books Online) has some good info too.

ktharsis 2010-05-17 12:42:38

ansaurus

tags:

views:

answers:

Determining what is causing massive log file growth on a basically inactive SQL 2005 DB

related questions