primary file group run out of free space??

Hi all
I got the following error massage when tried to delete some 3 GB records
from a 26 GB records table - 'tblName':
'Could not allocate space for object 'tblName' in database 'dbName' because
the primary
filegroup is full'.
At the time of this error there were some 4 GB free disk space on the disk
where the datababse data and log file were.
Also these files were not limitted in grow size.
My questions are:
1) why does a 'delete' operation involves allocation of space for the table
that is being deleted'
Does this operation involves creating image data for the table that is being
deleted, in the transaction log, where the deletion actually occures and
than commited back to the original table'
So if i am deleting data from a 26GB table, there should be this amount of
free space on the disk where the log's file group is located?
2) Where can i see the state of the primary filegroup and how can i increase
its size?
Thanks for your attention
ReaRea
delete is a logged operation , so have you seen the log file during the
deletion?
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
--Perfrom your DELETION (TRUNCATION would be more efficient)
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT
END
END
SET ROWCOUNT 0
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:%23ZeKwl$YEHA.2016@.TK2MSFTNGP09.phx.gbl...
> Hi all
> I got the following error massage when tried to delete some 3 GB records
> from a 26 GB records table - 'tblName':
> 'Could not allocate space for object 'tblName' in database 'dbName'
because
> the primary
> filegroup is full'.
> At the time of this error there were some 4 GB free disk space on the disk
> where the datababse data and log file were.
> Also these files were not limitted in grow size.
> My questions are:
> 1) why does a 'delete' operation involves allocation of space for the
table
> that is being deleted'
> Does this operation involves creating image data for the table that is
being
> deleted, in the transaction log, where the deletion actually occures and
> than commited back to the original table'
> So if i am deleting data from a 26GB table, there should be this amount of
> free space on the disk where the log's file group is located?
> 2) Where can i see the state of the primary filegroup and how can i
increase
> its size?
>
> Thanks for your attention
> Rea
>|||Thanks alot!
So how much disk space should a deletion of 3 GB from a 26GB table consume'
Is there a way to estimate the amount of disk space deletion operations
consume from
the transaction log's disk'
2) in your code below: what is the edvantage of doing deletions this way'
Thanks again
Rea
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e8FMLCAZEHA.3564@.TK2MSFTNGP11.phx.gbl...
> Rea
> delete is a logged operation , so have you seen the log file during the
> deletion?
> SET ROWCOUNT 1000
> WHILE 1 = 1
> BEGIN
> --Perfrom your DELETION (TRUNCATION would be more efficient)
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT
> END
> END
> SET ROWCOUNT 0
> "Rea Peleg" <rea_p@.afek.co.il> wrote in message
> news:%23ZeKwl$YEHA.2016@.TK2MSFTNGP09.phx.gbl...
> > Hi all
> > I got the following error massage when tried to delete some 3 GB records
> > from a 26 GB records table - 'tblName':
> >
> > 'Could not allocate space for object 'tblName' in database 'dbName'
> because
> > the primary
> > filegroup is full'.
> >
> > At the time of this error there were some 4 GB free disk space on the
disk
> > where the datababse data and log file were.
> > Also these files were not limitted in grow size.
> >
> > My questions are:
> > 1) why does a 'delete' operation involves allocation of space for the
> table
> > that is being deleted'
> > Does this operation involves creating image data for the table that is
> being
> > deleted, in the transaction log, where the deletion actually occures and
> > than commited back to the original table'
> > So if i am deleting data from a 26GB table, there should be this amount
of
> > free space on the disk where the log's file group is located?
> >
> > 2) Where can i see the state of the primary filegroup and how can i
> increase
> > its size?
> >
> >
> > Thanks for your attention
> > Rea
> >
> >
>|||Rea
I divide a long/big transaction into a small one.
CHECKPOINT flows a data from transaction log into the disk to remove an
inactive portions (btw you can also perform BACKUP LOG operation)
With that way you don't lock others by running your big deletion and also
keep a log file with an appropriate size.
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:OPOU01AZEHA.3716@.TK2MSFTNGP11.phx.gbl...
> Thanks alot!
> So how much disk space should a deletion of 3 GB from a 26GB table
consume'
> Is there a way to estimate the amount of disk space deletion operations
> consume from
> the transaction log's disk'
> 2) in your code below: what is the edvantage of doing deletions this way'
> Thanks again
> Rea
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e8FMLCAZEHA.3564@.TK2MSFTNGP11.phx.gbl...
> > Rea
> > delete is a logged operation , so have you seen the log file during the
> > deletion?
> >
> > SET ROWCOUNT 1000
> > WHILE 1 = 1
> > BEGIN
> > --Perfrom your DELETION (TRUNCATION would be more efficient)
> > IF @.@.ROWCOUNT = 0
> > BEGIN
> > BREAK
> > END
> > ELSE
> > BEGIN
> >
> > CHECKPOINT
> > END
> > END
> >
> > SET ROWCOUNT 0
> > "Rea Peleg" <rea_p@.afek.co.il> wrote in message
> > news:%23ZeKwl$YEHA.2016@.TK2MSFTNGP09.phx.gbl...
> > > Hi all
> > > I got the following error massage when tried to delete some 3 GB
records
> > > from a 26 GB records table - 'tblName':
> > >
> > > 'Could not allocate space for object 'tblName' in database 'dbName'
> > because
> > > the primary
> > > filegroup is full'.
> > >
> > > At the time of this error there were some 4 GB free disk space on the
> disk
> > > where the datababse data and log file were.
> > > Also these files were not limitted in grow size.
> > >
> > > My questions are:
> > > 1) why does a 'delete' operation involves allocation of space for the
> > table
> > > that is being deleted'
> > > Does this operation involves creating image data for the table that is
> > being
> > > deleted, in the transaction log, where the deletion actually occures
and
> > > than commited back to the original table'
> > > So if i am deleting data from a 26GB table, there should be this
amount
> of
> > > free space on the disk where the log's file group is located?
> > >
> > > 2) Where can i see the state of the primary filegroup and how can i
> > increase
> > > its size?
> > >
> > >
> > > Thanks for your attention
> > > Rea
> > >
> > >
> >
> >
>