October 22, 2010 at 10:52 am
I have inherited a process that receives several hundred Access .mdb files throughout the day. All of the data is loaded into our SQL 'staging' database. Each morning, the staging db is backed up and restored into production, the staging data is then truncated and the process starts all over again.
Recently we started experiencing unexplained growth in the transaction log. Running the process on our test servers with actual data results in a transaction log under 10 MB. Currently our production transaction logs are growing through the 15 GB available on the drive (I am forced to shrink the DB throughout the day to ensure that the process doesn't fail).
The staging db is not set up to publish, logging format is 'Simple'. This is on SQL Server 2000.
I have been through this document http://support.microsoft.com/kb/317375/#7 and nothing seems to apply. Running DBCC OPENTRAN shows the oldest transaction to be within a minute (usually) and is tied to the running load process. Additionally, there are no data queries other than to get a tables schema (select * where 1 = 0) for a c# data table.
The DBCC OPENTRAN output (executed at ~12:49 PM EST) is:
Transaction information for database <database name here>
Oldest active transaction:
SPID (server process ID) : 54
UID (user ID) : 1
Name : DML
LSN : (27791887:26628:1)
Start Time : Oct 22 2010 12:48:29:190PM
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Has anyone run into a situation like this before? I am out of ideas, other than dropping and recreating the DB altogether and *hoping* that the issue does not persist.
Any and all advice would be appreciated.
October 23, 2010 at 3:13 am
Duplicate post. No replies to this thread please. Direct replies to:http://www.sqlservercentral.com/Forums/FindPost1009289.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply