October 22, 2010 at 9:20 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.
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 22, 2010 at 9:32 am
If you can access the database while it's growing, you can check the sys.databases table and look at the log_reuse_wait_desc column to see why log space can't be reused (and is subsequently growing to allocate new space).
With Simple recovery, the system should run an automatic Checkpoint on the log every minute or so which would release space from any committed transactions. You could try manually running CHECKPOINT during the process too to see if that frees up log space...I suppose it's possible that automatic checkpoints aren't happening during your process, although I've never seen it.
edit: Sorry, didn't see that your staging server is SQL 2k...there is no sys.databases table, and no log_reuse_wait_desc equivalent in 2000.
October 22, 2010 at 9:32 am
What's the exact output of DBCC OPENTRAN?
Please post SQL 2000 questions in the SQL 2000 forums in future. Posted in the 2005 forums and you'll get answers that use 2005 features, wastes your time, wastes my time.
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
October 22, 2010 at 10:53 am
Apologies on posting this in the SQL Server 2005 forum. I missed the header when I started the thread. I have opened a post in the SQL 2K forum for this.
Just in case you were wondering, here is the output from the DBCC OPENTRAN command
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.
October 22, 2010 at 10:57 am
I'm not clear on a few things (but that's just me)
"... the staging db is backed up and restored into production ..."
Does this mean you do RESTORE DATABASE from your staging database to overwrite your production database ?
If so, then your production database is also SIMPLE recovery ?
October 22, 2010 at 11:35 am
Unfortunately, yes.
The whole DB is generated from scratch each day, so, apparantly the powers that be decided Simple logging was the way to go.
As I said in the beginning, I have inherited all of this and in all honesty, this load process is one of many things I would love to change.
October 22, 2010 at 1:13 pm
Just to double check...in your production database after it gets restored, did you manually check to see if it's still set to Simple?
Can you try running dbcc sqlperf(logspace) and see if it's actually using all of the space it's allocating?
Can you run CHECKPOINT and see if the free space from the query above changes after that?
October 23, 2010 at 3:06 am
rgreenva (10/22/2010)
Apologies on posting this in the SQL Server 2005 forum. I missed the header when I started the thread. I have opened a post in the SQL 2K forum for this.
I'll redirect that new thread here, as I had already asked a mod to move this thread.
This would be sooo much easier to investigate on SQL 2005. Can you put something in place to monitor when the log is growing, what's happening(profiler/trace) while it's growing and what OpenTran looks like at the time?
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
October 25, 2010 at 6:43 am
Just in case there is any confusion as to the process in place, here is (hopefully) a better description.
- Process Starts
--- backup PROD db
--- backup STAGING db
--- restore PROD db from STAGING backup
--- truncate tables in STAGING db (leave reference tables)
--- load STAGING db with data from Access files ( > 17 million records)
--- Send out reports
- Process Ends
The PROD db is set up for SIMPLE logging, and is in that state.
I have run the DBCC sqlperf(logspace) command several times over the < > and here are the results:
Log Size (MB) Log Space Used (%)
------------- --------------------
984.36719 92.778809
1082.8047 94.585281
1191.1172 91.057869
Running the command more frequently shows the % usage increasing gradually to 99.xxxx and then is autosizes.
October 25, 2010 at 6:56 am
After running the CHECKPOINT command, the following sqlperf data was returned:
Log Size (MB) Log Space Used (%)
------------- -------------------
1310.2422 1.4911813
1310.2422 5.0758076
1310.2422 10.075722
*note* - All of the sqlperf commands were run within 5 minutes from start to finish.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply