Solved

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

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

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.
A procedure for exporting installed hotfix details of remote computers using powershell
This tutorial will give a an overview on how to deploy remote agents in Backup Exec 2012 to new servers. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as connecting to a remote Back…
This tutorial will walk an individual through locating and launching the BEUtility application and how to execute it on the appropriate database. Log onto the server running the Backup Exec database. In a larger environment, this would generally be …

708 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

12 Experts available now in Live!

Get 1:1 Help Now