Solved

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

Posted on 2010-11-24
3
581 Views
Last Modified: 2012-05-10
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
Comment
Question by:rmm2001
  • 2
3 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34208379
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
 
LVL 7

Accepted Solution

by:
rmm2001 earned 0 total points
ID: 34208438
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
 
LVL 7

Author Closing Comment

by:rmm2001
ID: 34626777
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

OfficeMate Freezes on login or does not load after login credentials are input.
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This tutorial will show how to push an installation of Backup Exec to an additional server in both 2012 and 2014 versions of the software. Click on the Backup Exec button in the upper left corner. From here, select Installation and Licensing, then I…
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now