Tlog Fills Up in SQL 2005 But Not in SQL 2000 1

[fuzzyocelot]

Hi everyone!

I have a problem that’s starting to drive me batty. We have a SQL 2000 database where 60 tables are refreshed every morning from an Oracle database. This is done via 3 DTS packages which we are running as DTSRun commands through an SQL job. The SQL 2000 database is about 4 GB in size. The recovery model for the database is simple. The database is also backed up nightly on the dev server. I created a 2005 maintenance plan for the database. I’m guessing my plan is probably wrong and needs tweaking (see info below).

We are in the process of migrating to SQL 2005 (sp2). So I copied and restored this database onto our dev server where we have a 2005 instance installed. Note: This development server is 32-bit. We will be migrating to 64-bit soon (oh joy).

I changed the compatibility level to 90 and all seemed well. I copied the corresponding DTS packages over as DTS packages under Legacy (note: we installed the 2000 DTS Components for SQL2005). I then changed the connection information without any problems. I used the dtsrunui utility and re-generated the encrypted DTSRun commands which I inserted into a SQL job. I scheduled this job to run nightly to import the data into the database for testing.

The problem I’m having is that the second package keeps failing because the transaction log fills up. This is on the dev server. Everything has been running fine on the 2000 production server and I’ve tried to re-do everything that pertains to this database on the 2005 dev server. I’m stumped as to why the transaction log keeps filling up when we don’t have this problem on the prod server.

On the production server, the initial size for the log file is 612 MB and it’s restricted to 920 MB. Autogrowth is turned on with file growth set to 10%.

On the dev server, the initial size for the log file is currently at 3739 MB and it’s restricted to 4000 MB. Autogrowth is turned on with file growth set to 10%. I keep increasing the restriction, truncating the log, and it keeps filling up. Note, we have 100+ databases on both the dev and production servers. I’ve read that quite a few DBAs don’t set a restriction on the growth, but its our policy to do that here because we have so many databases on one server. I’m just stumped as to why there is no problem on the production server but the transaction log keeps filling up on the dev server. Below are the maintenance plan info for both servers:

Production Maintenance Plan (just for this database) – SQL 2000 (sp3a):
1. Optimizations:
a. Reorganize data and index pages - change percentage of free space per page to 10%
b. Remove unused space from database files
i. Shrink database when it grows beyond 50 MB
ii. Amount of free space to remain after shrinking: 10%
c. Runs once a month
2. Integrity check:
a. Check database integrity – include indexes
b. Runs weekly

Development Maintenance Plan (just for this database) – SQL 2005 (sp2):
1. Check database integrity – include indexes
2. Shrink database
a. limit: 50 MB
b. free space: 10%
3. Rebuild index
a. Object: tables and views
b. 10% of free space
4. Update statistics
a. Object: tables and views
b. All existing statistics
c. Scan type: Full scan
5. Clean up history
a. History type: backup, job, maintenance plan
b. Age: older than 4 weeks

As a test I ran the backup job, then the maintenance plan, and then the import job. It keeps failing and the error log indicates the transaction log is full. I’ve run the DTS package manually by opening it and executing it. That showed me it fails when it comes to one table in particular. That table contains over 6 million records.

At the beginning of the package the log is truncated (BACKUP LOG MyDatabase WITH TRUNCATE_ONLY), the table is truncated, and the indexes are dropped (DROP INDEX…). Next is the data transformation tasks. Then the log is truncated, the indexes are re-created (one clustered, one non-clustered), and the log is truncated yet again. I’m not the one who originally set this up but it’s been working in production for years.

In production, the fill factor is not set on these indexes. The server-wide fill factor setting is 0.

I haven’t rewritten the DTS packages in SSIS because they’re complicated and would take me a while to do. I tried migrating them a while back but ran into errors. So I’m hoping to at least get the DTS working for now. Does anyone have any ideas what’s wrong? It’s probably something very obvious or very simple.

I feel like I've Googled this to death. Thanks for any ideas or if there are any threads or web sites I missed that would help!!!!

 

[mrdenny]

Is it failing while loading the table, or while building the index?

Have you verified that the recovery model of the SQL 2005 database is also SIMPLE?

Have you verified that the DTS package is batching the data load to keep the size of the transactions small?

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)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[fuzzyocelot]

Thanks for replying, mrdenny!

1. It's failing on the table load. I have confirmed that by opening up the package and running it manually several times.

2. I just verified that the recovery model of the SQL 2005 database is also SIMPLE.

3. I don't think it's batching the data load. On the Transform Data Task Properties, I went to the Options tab. The "insert batch size" is 0. Is this what you're talking about? Under "data movement", the "fetch buffer size" is also 0. I've never messed with these settings before. Could this be the problem? If so, why would it work fine this way in 2000 but not in 2005?

Thanks!

 

[mrdenny]

Yes, this could be the problem. SQL 2005 may be doing so additional logging for some reason.

I recommend setting these settings to 1000. When these settings are set to 0 SQL takes all the rows and commits them as a single transaction. When you change the settings SQL writes the rows in batches so that SQL can remove the entries from the log after each 1000 records are commited. If you are moving a very large number of records you may actually see your load time decrease.

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)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[fuzzyocelot]

mrdenny... you are da man! It worked perfectly! Thank you so very much!!!

 

[mrdenny]

No problem.

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)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com