Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2010-11-24
3
588 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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Exchange 2007 3 55
Dell PowerEdge T610 PERC 6i Drivers... 6 28
SQL Recursion schedule 13 14
Stored Procedure needs owner to execute 5 15
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
You might have come across a situation when you have Exchange 2013 server in two different sites (Production and DR). After adding the Database copy in ECP console it displays Database copy status unknown for the DR exchange server. Issue is strange…
This tutorial will give a short introduction and overview of Backup Exec 2012 and how to navigate and perform basic functions. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as conne…
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…

856 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