October 23, 2009 at 7:44 am
I have a SQL2000 database that pulls data via DTS packages ever night. I am having a problem with the size it keeps growing pass the limit and causes my DTS packages to fail.
Current Size: 2691.31MB
Space Available: 485.66MB
Data: Set to automatically grow by 10% and restrict file growth by 3072MB.
Transaction log: Set to automatically grow by 10% and restrict file growth by 3072MB.
On Monday I had this same problem so I increased the maximum file size from 2GB to 3GB. Does anyone know what might be going on?
Chad
October 23, 2009 at 8:03 am
How many rows of data are you importing using the DTS package. Just like you say, it is running out of space. It could be either the Log file or the Data file. Also you have capped the growth of the log and data file.
-Roy
October 23, 2009 at 8:11 am
SQL 2005 has a default trace that includes database growth events. Look in there, find out how often the database grows. Most likely, you're getting more than one growth event per day or something like that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 23, 2009 at 8:13 am
I am on using 2000 SQL.
October 23, 2009 at 8:29 am
You posted in the 2005 forum. That's why I assumed 2005.
You could create a trace to track that in the future, but that won't get you answers about the past.
You'll just need to look at how much growth the nightly load needs, and make sure it has room for that, both in the database files, the log files, and the physical hard drive space.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 23, 2009 at 8:33 am
Sorry about posting in the wrong place! Thanks for your help?
Chad
October 23, 2009 at 9:10 am
Is it your log or data file that is growing too big.
If your not sure there should be a message in the SQL error Logs.
I would guess it is the TLog.
Is your database in Full or Bulk-Logged recovery mode?
October 23, 2009 at 9:31 am
Full
October 23, 2009 at 9:37 am
If you can't change the recovery model and don't have space in your log file can you break down the package a bit.
Is it all just insert statements. Are you adding to multiple tables at the same time?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply