Solved

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

Posted on 2010-11-24
3
592 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
[X]
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
  • 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

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  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
remote desktop user rights 5 98
domain administrator and domain admin user 3 59
Sharepoint 2010 Site User Administration 6 30
msiexec won't run 4 32
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 article explains how to install and use the NTBackup utility that comes with Windows Server.
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 setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

752 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