March 20, 2012 at 7:51 am
Hi, everyone:
i met a very wierd problem this morning. i have a SSIS package, which clean a table, then run a SP to insert records to this table. the SSIS package will run every 1 min. this morning, i check the database, there are only 9 records in the table, but the size of table was 7GB!
after I drop the table and re-create it, let the SSIS package insert records again. the size of table dropped to 1 KB.
the database recovery mode is 'simple'.
can anyone tell me what's wrong here?
_______________________________________________________________
March 20, 2012 at 7:57 am
Does your table have a clustered index?
March 20, 2012 at 8:12 am
You say "clean a table." What does that mean? Are you deleting records? How many? Please post DDL for the table and DML for the job as suggested by Jeff Moden's article in my signature.
Jared
CE - Microsoft
March 20, 2012 at 8:57 am
thank you , guys:
1. there is no index on this table. because it has been deleted\inserted all the time, and only about total 10 records there, i didn't see any reason to place an index on this table.
2. 'clean table'. The ssis has a 'foreach loop container', which will delete from table by PO_No, one row each time; and then run a SP by PO_No, insert a new row; untill all row has been refreshed. again, total about 10 rows.
table definition:
CREATE TABLE [dbo].[PO_JDE](
[PO_No] [int] NOT NULL,
[PO_Amount] [money] NOT NULL,
[Vendor_ID] [int] NOT NULL,
[Document_Type_ID] [smallint] NOT NULL,
[Business_Unit] [varchar](7) NOT NULL,
[Budget_Hold_Ind] [char](1) NOT NULL,
[Budgeted_Ind] [char](1) NOT NULL,
[PO_Status_ID] [smallint] NOT NULL,
[User_Name] [varchar](10) NOT NULL
) ON [PRIMARY]
_______________________________________________________________
March 20, 2012 at 9:00 am
michael wang-201906 (3/20/2012)
thank you , guys:1. there is no index on this table. because it has been deleted\inserted all the time, and only about total 10 records there, i didn't see any reason to place an index on this table.
2. 'clean table'. The ssis has a 'foreach loop container', which will delete from table by PO_No, one row each time; and then run a SP by PO_No, insert a new row; untill all row has been refreshed. again, total about 10 rows.
table definition:
CREATE TABLE [dbo].[PO_JDE](
[PO_No] [int] NOT NULL,
[PO_Amount] [money] NOT NULL,
[Vendor_ID] [int] NOT NULL,
[Document_Type_ID] [smallint] NOT NULL,
[Business_Unit] [varchar](7) NOT NULL,
[Budget_Hold_Ind] [char](1) NOT NULL,
[Budgeted_Ind] [char](1) NOT NULL,
[PO_Status_ID] [smallint] NOT NULL,
[User_Name] [varchar](10) NOT NULL
) ON [PRIMARY]
And it runs every minute? So, 10 rows of data get deleted and the rewritten every minute? I'm really confused... Can you describe why this is being done?
Jared
CE - Microsoft
March 20, 2012 at 9:14 am
michael wang-201906 (3/20/2012)
Hi, everyone:i met a very wierd problem this morning. i have a SSIS package, which clean a table, then run a SP to insert records to this table. the SSIS package will run every 1 min. this morning, i check the database, there are only 9 records in the table, but the size of table was 7GB!
after I drop the table and re-create it, let the SSIS package insert records again. the size of table dropped to 1 KB.
the database recovery mode is 'simple'.
can anyone tell me what's wrong here?
Deleting from a table won't necessarily cause space allocated to a table to decrease. In order to do that, you have to do one of two things. Either drop the table and rebuild it or do a rebuild on the clustered index. Since your table is a heap, you can either use the drop method or you'll need to add a clustered index.
{EDIT}. You could also use TRUNCATE to clear the table at the end of the run. Truncate deallocates rows from the table.
--Jeff Moden
March 20, 2012 at 9:17 am
you are right, 10 rows been deleted insert every min.
the reason I do so is, i put all 'open' PO into this temp table, keep updating the status\details of each PO from 2 data source every min; when the PO has been 'closed', it will be removed from temp table.
_______________________________________________________________
March 20, 2012 at 9:27 am
...
Since your table is a heap, you can either use the drop method or you'll need to add a clustered index.
...
You could also use TRUNCATE to clear the table at the end of the run. Truncate deallocates rows from the table.
That's why the question about clustered index...
TRUNCATE should help in case of "heap" tables to reclaim the space...
March 20, 2012 at 9:29 am
michael wang-201906 (3/20/2012)
you are right, 10 rows been deleted insert every min.the reason I do so is, i put all 'open' PO into this temp table, keep updating the status\details of each PO from 2 data source every min; when the PO has been 'closed', it will be removed from temp table.
Why not just update it in the table where the data is permanently stored? There could be a good reason, but I find that these types of tables are rarely used for a good reason.
Jared
CE - Microsoft
March 20, 2012 at 9:40 am
the data source is on DB2, which i don't want to modify, that's why i download them to SQL by SSIS.
thank you everyone, i will try to create an clustered index to see if it help.
_______________________________________________________________
March 20, 2012 at 10:24 am
michael wang-201906 (3/20/2012)
the data source is on DB2, which i don't want to modify, that's why i download them to SQL by SSIS.thank you everyone, i will try to create an clustered index to see if it help.
Just creating the clustered index won't do it by itself. You need a scheduled job or some other code that will rebuild the clustered index (and, thus, the data itself) every once in a while.
--Jeff Moden
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply