Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 606
  • Last Modified:

SQL Database ( 2005 & 2008) datafile doubled in size "automatically"

HI guys -

I have a database on SQL2005 and it doubled in size overnight. Initial file size is 55g, file growth is 1mb/unrestricted. Log file initial size is 4mb growth is 10% restricted growth to 2,097,152.

I have a process that runs at night inserting a few records..maybe 20k max. Last night the data file was 57gb and the log file 4mb (~12k rows were inserted). I checked this morning and the data file size is now 117gb and the log file 5mb.

I have a copy of this database running on SQL2008 on a different server and the same process runs there as well. The initial file size/growth are the same on this server as they are on the 2005 one. The data file did the same thing when the same dataset was inserted. (They're running in parallel for debugging reasons currently, not for production reasons). The log file remained nearly unchanged.

Both are on Windows Server 2008

I shrunk both of them and they went back to their normal size. No problem with that at all.

Is there any logical reason why this would happen? Anything I can do to make sure it doesn't happen again?

Thanks!
0
rmm2001
Asked:
rmm2001
  • 2
1 Solution
 
LowfatspreadCommented:
sounds unusual, however it would depend on what exactly your processes are doing...

do the processes involve

truncation of tables...
creation/dropping of tables/indexes....
is replication involved with any of the tables in the database...
are there triggers on any of the tables....
are indexed views manipulated during the processing....

can you post the basic script involved in the overnight process...

have you experienced this in your development/test system?
have you tried profiling/running a trace on the system to observe this...?

when in the process are your database/log backed up?
what recovery model are you using..?

0
 
rmm2001Author Commented:
I'll answer in line

truncation of tables...
nope..no deleting either
creation/dropping of tables/indexes...
nope
is replication involved with any of the tables in the database...
none at all
are there triggers on any of the tables....
yes there are. on insert of one table a call to a function joins 2 tables together and updates a row in another table with a varchar that has some logic to make it. only 52 rows went into this table
are indexed views manipulated during the processing....
nope

can you post the basic script involved in the overnight process...
eeah..it's a series of queries that go hit a few other databases and use business logic to insert it into this warehouse. it's just basic "SELECT ID, LTRIM(RTRIM(Field)), Code FROM SourceTable st LEFT JOIN DestinationTable dt ON dt.Code = st.Code WHERE dt.ID IS NULL" .. 17 times. Some tables have 0 records inserted - others have a couple thousand


have you experienced this in your development/test system?
it's bad practice but this is both dev and test
have you tried profiling/running a trace on the system to observe this...?
nope - it just happened that one time

when in the process are your database/log backed up?
yes
what recovery model are you using..?
simple
0
 
rmm2001Author Commented:
Issue is still outstanding. I'll open the question for discussion if it happens again and if I figure it out so it can be searchable.
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now