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.
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 Mbunallocated space = 81.62 Mbreserved = 2644360 KBdata = 2457536 KBindex_size = 181384 KBunused = 5440 KBRunning the same stored proc after the change and subsequently nightly inserts over a few days returned the following,database_size = 4872.44 Mbunallocated space = 1.30 Mbreserved = 3023472 KBdata = 2504888 KBindex_size = 182064 KBunused = 336520 KBCan anybody please help me understand why the sudden jump and what should be my course of action.Regards, |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
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] |
 |
|
|
|
|
|
|