Table space allocation problem
-
We copy data from a Progress database on a nightly basis. This is done using DTS via an ODBC connection to the Progress database. The data is transferred to staging table on SQL Server. Then delete\update\insert procedures are run to update the production Database. Both the staging and production databases are on the same server.
Recently we've encountered a problem where space for one of the 40 odd tables in the staging database gets grossly over allocated. It doesn't happen every night and not always the same table.
The structure of one of the affected tables is below, along with space statistics. As you can see there is quite a discrepancy.
CREATE TABLE [dbo].[el-postcode] (
[pcode] [varchar] (20) NOT NULL ,
[suburb] [varchar] (60) NULL ,
[ISO3-country] [varchar] (6) NOT NULL ,
[state-code] [varchar] (12) NULL ,
[con-value-ref] [decimal](17, 2) NOT NULL
)
After Copy
name
el-postcode
rows
14,278
data_reserved
2,157,960 KB
data_used
269,744 KB
index_used
24 KB
total_size
269,768 KB
free_space
1,888,192 KB
percent_of_db
23.06%
Adding and removing an index corrects the space allocation, but as this is a staging table we don't want to have it indexed because it slows down the data transfer.
After Fixing
name
el-postcode
rows
14,278
data_reserved
712 KB
data_used
680 KB
index_used
16 KB
total_size
696 KB
free_space
16 KB
percent_of_db
0.08%
Any ideas would be welcome.
--------------------
Colt 45 - the original point and click interface
-
You could place this one table in a filegroup so that it is in a different data file and use DBCC ShrinkFile on it. While this may not normally be your first choice, it should work.
-
The problem with that suggestion is that, as per my original post, it's not always the same table.
Last night this same table loaded without any problems, but another much larger table (approx 5mil rows) ended up taking all available space on the disk at which point the loading process crashed.
--------------------
Colt 45 - the original point and click interface
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply