Re: many inserts results in a massive reserved space for table

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Jackie (jackiesmith_3_at_hotmail.com)
Date: 10/06/04 

Date: 6 Oct 2004 05:47:26 -0700

Well, I don't understand at all!!

It was true that the tables did not have indexes or primary keys on -
this was simply because I was running a test and I thought it would
run more quickly - again my not understanding SQL Server properly (not
because I think it's a good idea in terms of design!).

After your latest message - here's what I did (sorry if this is
longwinded).

I loaded the indexes / PKs onto the relevent tables - although I
notice there are no clustered indexes on any of the 5 tables which I
am inserting data into - please bear in mind though that I have no
influence over the design of the database...

I re-ran my routine against THE SAME SET OF DATA - this time the data
loaded in a 100th of the time, and took up approx 350Mb per table!!
(prior to this the tables had 27Gb of reserved space, with maybe 3.5Gb
of data) - HOW CAN THIS BE SO DIFFERENT? (total size of database was
61Gb and now is 3Gb !!)

So, then I tried to run my same routine loading data into a different
database on a different server which already had the indexes on (same
data model). This server only had 12Gb of free space (the total data
in my first db took up 1.9Gb). Afetr a very short space of time the
database ran out of disk space after only a fraction of the data had
been inserted. Looking at SP_SPACEUSED, the reserved space was way out
of sync with the data figure as before.

I ran DBCC UPDATEUSAGE, truncated the 5 tables and re-ran my routine,
now the data is inserting happily and is taking up 353Mb per table
again!

I have no more servers to play with!!

Is the data "behaving properly" the 2nd time around simply because it
is the 2nd time the same routine has been run?
Or is the fact that each time I have run DBCC UPDATEUSAGE relevent?

Surely there must be a way of achieving the proper data figures during
the first time this routine is run (I clearly can't keep running the
same routines and running out of disk space when I come to run this
against production databases).

For reference - here is an example of one of the tables I am inserting
data into (results from sp_help) -

BookingPayment dbo user table 2004-08-24 11:48:44.210

BooRefNo int no 4 10 0 no (n/a) (n/a) NULL
PayID int no 4 10 0 no (n/a) (n/a) NULL
BpyDate datetime no 8 no (n/a) (n/a) NULL
BpyAmount money no 8 19 4 no (n/a) (n/a) NULL
BpyTzoName varchar no 3
        yes no no SQL_Latin1_General_CP1_CI_AS
BpyDateUTC datetime no 8 yes (n/a) (n/a) NULL
BpyPayAmount money no 8 19 4 yes (n/a) (n/a) NULL
BpyExrRate float no 8 53 NULL yes (n/a) (n/a) NULL
WrkID varchar no 20 yes no no SQL_Latin1_General_CP1_CI_AS
UseID varchar no 20 yes no no SQL_Latin1_General_CP1_CI_AS
BpyCreatedWkgID varchar no 3
        yes no no SQL_Latin1_General_CP1_CI_AS
BpyCreatedUgrID varchar no 3
        yes no no SQL_Latin1_General_CP1_CI_AS
BpyCreatedProID varchar no 5
        yes no no SQL_Latin1_General_CP1_CI_AS
BpyCreatedPrgID varchar no 3
        yes no no SQL_Latin1_General_CP1_CI_AS
BpyInvStatus tinyint no 1 3 0 yes (n/a) (n/a) NULL

PaymentKey nonclustered, unique located on PRIMARY PayID, BooRefNo
PK___3__21 nonclustered, unique, primary key located on
PRIMARY BooRefNo, PayID, BpyDate

PRIMARY KEY (non-clustered) PK___3__21 (n/a) (n/a) (n/a) (n/a) BooRefNo,
PayID, BpyDate

there is a difference in 27Gb (!!!) in total space used between the
1st and 2nd times I insert data into this table...

Relevant Pages

  • Re: Issue with inserting an Access database to Word
    ... are you inserting your table as a field. ... I select the database and it returns to the "Database" dialogue box. ... Database is in a shared folder on a Windows server. ... it to various locations on our server (where I have various permissions ...
    (microsoft.public.word.docmanagement)
  • SQL Server 2000/VB 6 Insert speed
    ... We recently upgraded the database server from 1x2.0GHz ... where the new server is inserting about 20 rows/sec. ...
    (microsoft.public.sqlserver.programming)
  • This is going straight to the pool room
    ... or not the client has privilege to do what they're trying to do, ... The server environment is this: ... 3GL User action Routines that Tier3 will execute on your behalf during the ... Routine Name: USER_INIT ...
    (comp.os.vms)
  • Re: Goes to Error Label even though err.number = 0
    ... Okay, if you are *certain* there is no way the logic could jump into the error handler of the procedure without an error, and that nothing else could be clearing the error, then I have to assume there is a corruption in the VBA. ... Compact the database to get rid of this junk: ... As a result, the routine runs without error, but continues down into the ... At the end of the caluculation the job form is ...
    (microsoft.public.access.formscoding)
  • Re: VB6 Login Form to SQL Server
    ... > 3) In the Change Event, call a routine that will validate that an octet ... >> What I have done is within the server, username, and password Change ... coded a routine to see if there is data in the textbox. ... Attempting to open a connection without knowing with certainty that the ...
    (microsoft.public.vb.general.discussion)