SQL 2005 MDF File Increasing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

SQL 2005 MDF File Increasing

Author

Topic

luke.simpson
Starting Member

2 Posts

Posted - 2011-02-22 : 20:25:17

Please excuse me if this is posted in the wrong section or has been answered before. I am new to this site and SQL in general for that matter.

I have developed an SQL 2005 database and data is populated into the sales table via a DTS batch. Previously the rows per batch size was around 3k, but in order to correctly redirect error output, I reduced this to 1 row per batch, so that each row would be handled individually and moved to the flat file error output if the insert failed.

The destination table has a clustered index of three columns to prevent duplicates.

Upon making this change to limit the rows per batch to 1 in the DTS package, the MDF file has started growing dramatically. I noticed the change from the nightly backup.

Running sp_spaceused on the database before the change gave the following

database_size =4582.44 Mb
unallocated space = 81.62 Mb

reserved = 2644360 KB
data = 2457536 KB
index_size = 181384 KB
unused = 5440 KB

Running the same stored proc after the change and subsequently nightly inserts over a few days returned the following,

database_size = 4872.44 Mb
unallocated space = 1.30 Mb

reserved = 3023472 KB
data = 2504888 KB
index_size = 182064 KB
unused = 336520 KB

Can anybody please help me understand why the sudden jump and what should be my course of action.

Regards,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-22 : 20:40:21

I don't think the information you posted is enough for us to answer your question. You are adding data to the database, so it would be natural for the file to expand if it needs extra space.

Is the rate of growth different?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

luke.simpson
Starting Member

2 Posts

Posted - 2011-02-22 : 20:49:24

Thankyou for the quick response. Apologies for the lact of info, as I said I am new to SQL Server.

The DTS batch has been running daily for about 2 months and each day about 4000 new rows are added to the sales table. The growth of the database over this time was very gradual. Only a few MB each day.

The change to the DTS batch was made on Friday and in only 4 days it has growen by almost 1Gb. The reading I have been doing leads me to believe that it is related to the indexin on the sales table, becuase we are talking about the MDF file.

If it is any help, I have scripted the index and put it below.

/****** Object: Index [idx_store_docket_linenum] Script Date: 02/23/2011 12:48:53 ******/
CREATE UNIQUE NONCLUSTERED INDEX [idx_store_docket_linenum] ON [dbo].[hl_sales]
(
[Store] ASC,
[Docket] ASC,
[LineNumber] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources