database with HUGE ldf file

[modglin]

We have a canned program that recently went to SQL. They apparently did not do a good job of indexing everything before and it definately did not get any better.
They even separated out the indexes in the database so we have Program.mdf (28 gb)Program_indexes.mdf (21 gb) and Program_log.ldf (5 gb).
When people are updating information in the database it is locking files so that people running reports are having problems. I created a DTS package yesterday that right now is only taking 4 of the tables and copying them into a new database that the people running reports can use it (it will be a day behind in data, but that will be fine).
When I created the new database the mdf is now Product_Reports.mdf (4.9 gb) and Product_Reports_log.ldf (9.9 gb). I am thinking it is NOT normal to have an ldf about twice the size of the actual mdf.
Is there anything that I can do in the DTS package that would possibly reduce the size of the ldf file? (by the way we are on SQL2000)

 

[mrdenny]

You need to change the recovery model of the database from FULL to SIMPLE. You can then shrink the log.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[ptheriault]

Look up DBCC SHRINKFILE in BOL.

DBCC SHRINKFILE (<log_name>, truncateonly)

make sure you create backups for your log file or it will just grow out of control. You'll also need them to recover your db to a point in time.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[modglin]

Thanks - forgot to change the new database to simple! That helped that one.
The original databae is simple and still has the issues that it has. We are working with the company that created the program, however, they are saying it is our system that is the problem not their program. Funny - we don't have issues on the other programs that we have. They switched from Oracle to SQL last year and the issues started when they made their switch.

 

[SQLWilts]

How often are the indexes rebuilt? You could look up the the MSForEachTable undocumented procedure (although I wouldn't normally recommend an undocumented SP as they can be removed without notice) and DBCC DBReIndex and sp_updatestats procs. Also, if you can use profiler you can trap long runnig queries and run them through the DB tuning wizard to see if you need to create more stats or additional indexes.

Also, you could look at replication (snapshot once a day sounds about right) to replace the DTS packages if they start failing - although, if it ain't broke...