reduce databse size

[Fred]

Hi, my database size has grown out of control and I need help with the
following issues. (I am very new to databases)

I am storing financial tick data in one of the tables and after two months
the database has grown to 30GB. I do not need a permanent record of this
tick data after it has been processed and tried to remove all rows from this
table (delete from Tickdata), however sql does not take kindly to removing
millions of rows and the operation seams to time out. The only solution I
could come up with was to delete the table.

Secondly, after managing to clear out these tables I have noticed that the
database size is still 30GB, despite 29GB being available. Is there any way
to reduce the size of the database from 30GB. I tried the shrink database
option but it does not do anything. Any ideas?

Thanks.

[Oscar Santiesteban Jr.]

Re: reduce databse size

Have you tried a TRUNCATE Tickdata?
Look at dbcc shrinkfile in the Books online in order to make the db smaller.


"Fred" <Fred@hotmail.c om> wrote in message
news:4178493c$1 @duster.adelaid e.on.net...[color=blue]
> Hi, my database size has grown out of control and I need help with the
> following issues. (I am very new to databases)
>
> I am storing financial tick data in one of the tables and after two months
> the database has grown to 30GB. I do not need a permanent record of this
> tick data after it has been processed and tried to remove all rows from[/color]
this[color=blue]
> table (delete from Tickdata), however sql does not take kindly to removing
> millions of rows and the operation seams to time out. The only solution I
> could come up with was to delete the table.
>
> Secondly, after managing to clear out these tables I have noticed that the
> database size is still 30GB, despite 29GB being available. Is there any[/color]
way[color=blue]
> to reduce the size of the database from 30GB. I tried the shrink database
> option but it does not do anything. Any ideas?
>
> Thanks.
>
>[/color]

[Jerry Kurtz]

Re: reduce databse size

"Fred" <Fred@hotmail.c om> wrote in message news:<4178493c$ 1@duster.adelai de.on.net>...[color=blue]
> Hi, my database size has grown out of control and I need help with the
> following issues. (I am very new to databases)
>
> I am storing financial tick data in one of the tables and after two months
> the database has grown to 30GB. I do not need a permanent record of this
> tick data after it has been processed and tried to remove all rows from this
> table (delete from Tickdata), however sql does not take kindly to removing
> millions of rows and the operation seams to time out. The only solution I
> could come up with was to delete the table.
>
> Secondly, after managing to clear out these tables I have noticed that the
> database size is still 30GB, despite 29GB being available. Is there any way
> to reduce the size of the database from 30GB. I tried the shrink database
> option but it does not do anything. Any ideas?
>
> Thanks.[/color]

Here's what I use:

backup log SQLData with TRUNCATE_ONLY
dbcc shrinkfile (2,20,TRUNCATEO NLY)

The first paramater in shrinkfile is the file number. To get a list
of filenumbers, issue the following query:

select * from sysfiles

The 2nd parameter is the number of pages. If you specify a number
less than the database uses, it will use the number of pages that it
requires.

"SQLData" is the name of the database. Replace that with your
database.

I do these to help manage log size. You may need to toy around with
these to get exactly what you're looking for.

[Metal Dave]

Re: reduce databse size

On Fri, 22 Oct 2004, Fred wrote:
[color=blue]
> Hi, my database size has grown out of control and I need help with the
> following issues. (I am very new to databases)
>
> I am storing financial tick data in one of the tables and after two months
> the database has grown to 30GB. I do not need a permanent record of this
> tick data after it has been processed and tried to remove all rows from this
> table (delete from Tickdata), however sql does not take kindly to removing
> millions of rows and the operation seams to time out. The only solution I
> could come up with was to delete the table.[/color]

The reason it times out is most likely the logging of each delete. Most
operations in SQL Server are logged (I'm not qualified to give a complete
explanation of logging, but in short as it pertains to this problem it has
to do with being able to to restore the database to a previous point and
also it is much slower than a logged operation, plus log size can grow
very large depending on your setup) The truncate table command (see BOL)
is much like a delete from without any where criteria, except that in most
cases this is a non logged operation and will thus complete much much
faster (this non logged behavior however depends on some database options
- the recovery mode in SQL 2000, some other option I can't remember in SQL
7.0. See BOL). Depending on your backup solution non logged operations may
or may not be appropriate.

Alternately, you can only delete a smaller subset of rows at once and
iterate until everything you want deleted has been deleted, which is
probably less efficient overall but may help with any timeout / GUI
responsivness issues you are having. I took this approach once for a quick
and dirty solution to a similar problem.

Dave

[Erland Sommarskog]

Re: reduce databse size

Metal Dave (metal@spam.spa m) writes:[color=blue]
> The truncate table command (see BOL) is much like a delete from without
> any where criteria, except that in most cases this is a non logged
> operation and will thus complete much much faster[/color]

TRUNCATE TABLE is logged, however minimally. When you delete rows, each
row is logged. With TRUNCATE TABLE, only the page deallocation is logged.

Note that TRUNCATE TABLE is not permitted on tables referenced by foreign
keys.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Metal Dave]

Re: reduce databse size

On Tue, 26 Oct 2004, Erland Sommarskog wrote:
[color=blue]
> Metal Dave (metal@spam.spa m) writes:[color=green]
> > The truncate table command (see BOL) is much like a delete from without
> > any where criteria, except that in most cases this is a non logged
> > operation and will thus complete much much faster[/color]
>
> TRUNCATE TABLE is logged, however minimally. When you delete rows, each
> row is logged. With TRUNCATE TABLE, only the page deallocation is logged.
>
> Note that TRUNCATE TABLE is not permitted on tables referenced by foreign
> keys.[/color]

Oops, sorry for oversimplificat ion. I was just trying to stick with "ask a
question, answer a question" much like the "take a penny, leave a penny"
at your local 7-11. Thanks for clarifying. The OP may find it useful
anyway though, as it's still much faster and advanced restore didn't not
sound like a priority to him/her.

Dave