Re-Org/Compress?

I'm a developer, not a DBA, and really know nothing about SQL Server Admin.
Unfortunately, I have no one to support the SQL Server installation that I'm
using for some development work (internal politics) and need help please.
The stuff I'm developing involves a lot of volume loads and deletes of data.
Typically, I'm loading 5,000 or 10,000 records of about 1K worth of data
each (using SQL inserts in a VB program that's processing the data), working
with the data for a while (updates to the rows that were bulk loaded), a
bunch of reporting (using Crystal), and then deleting all the rows (through
a standard SQL DELETE). I might do that 3 or 4 times a day (or more).
The Enterprise Manager shrink function says that the database is at about
16gig with about 300meg of free space. I definitely do not have 16gig worth
of data in the database. I doubt it's 1gig even with the indexes.
Here are my questions:
1. Is there some sort of re-org or compress that I need to do to get the
database back down to a reasonable size?
2. I think that the transaction log is running -- is there some way to turn
it off?
3. Is there a way to clear the log? I certainly don't need it.
Thanks in advance.See inline:
--
Andrew J. Kelly SQL MVP
"JP" <jnospamperelst@.optnospamonline.net> wrote in message
news:e3l%23xWbhFHA.4000@.TK2MSFTNGP12.phx.gbl...
> I'm a developer, not a DBA, and really know nothing about SQL Server
> Admin.
> Unfortunately, I have no one to support the SQL Server installation that
> I'm
> using for some development work (internal politics) and need help please.
> The stuff I'm developing involves a lot of volume loads and deletes of
> data.
> Typically, I'm loading 5,000 or 10,000 records of about 1K worth of data
> each (using SQL inserts in a VB program that's processing the data),
> working
> with the data for a while (updates to the rows that were bulk loaded), a
> bunch of reporting (using Crystal), and then deleting all the rows
> (through
> a standard SQL DELETE). I might do that 3 or 4 times a day (or more).
You should think about usign BULK INSERT or BCP to load large amounts of
data instead of doing individual Inserts. Use TRUNCATE TABLE instead of
delete and make sure the recovery mode is set to SIIMPLE mode as this sounds
liek a scratch db.
> The Enterprise Manager shrink function says that the database is at about
> 16gig with about 300meg of free space. I definitely do not have 16gig
> worth
> of data in the database. I doubt it's 1gig even with the indexes.
Try running DBCC UPDATEUSAGE and see if the values change. See BooksOnLine
for the details of what it does.
> Here are my questions:
> 1. Is there some sort of re-org or compress that I need to do to get the
> database back down to a reasonable size?
See above plus chances are you have the recovery mode set to FULL and most
of this is log usage. Again setting to SIMPLE will solve that.
> 2. I think that the transaction log is running -- is there some way to
> turn
> it off?
Essentially NO. But see BooksOnLine under "minimally logged bulk copy" for
details on how to do minimally logged loads.
> 3. Is there a way to clear the log? I certainly don't need it.
Again, set to SIMPLE recovery mode.|||THANKS -- that brought the log file down to 3 meg from something over 10gig.
I can't use the BCP in this case because -- in practice -- the VB code will
be running constantly to load transactions into this reporting/audit
database as they occur in real life (about 1 every 10 seconds), and what I'm
mostly testing is the VB code.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eFLM6tbhFHA.2644@.TK2MSFTNGP09.phx.gbl...
> See inline:
> --
> Andrew J. Kelly SQL MVP
>
> "JP" <jnospamperelst@.optnospamonline.net> wrote in message
> news:e3l%23xWbhFHA.4000@.TK2MSFTNGP12.phx.gbl...
> > I'm a developer, not a DBA, and really know nothing about SQL Server
> > Admin.
> > Unfortunately, I have no one to support the SQL Server installation that
> > I'm
> > using for some development work (internal politics) and need help
please.
> >
> > The stuff I'm developing involves a lot of volume loads and deletes of
> > data.
> > Typically, I'm loading 5,000 or 10,000 records of about 1K worth of data
> > each (using SQL inserts in a VB program that's processing the data),
> > working
> > with the data for a while (updates to the rows that were bulk loaded), a
> > bunch of reporting (using Crystal), and then deleting all the rows
> > (through
> > a standard SQL DELETE). I might do that 3 or 4 times a day (or more).
> You should think about usign BULK INSERT or BCP to load large amounts of
> data instead of doing individual Inserts. Use TRUNCATE TABLE instead of
> delete and make sure the recovery mode is set to SIIMPLE mode as this
sounds
> liek a scratch db.
>
> > The Enterprise Manager shrink function says that the database is at
about
> > 16gig with about 300meg of free space. I definitely do not have 16gig
> > worth
> > of data in the database. I doubt it's 1gig even with the indexes.
> Try running DBCC UPDATEUSAGE and see if the values change. See
BooksOnLine
> for the details of what it does.
> >
> > Here are my questions:
> >
> > 1. Is there some sort of re-org or compress that I need to do to get
the
> > database back down to a reasonable size?
> See above plus chances are you have the recovery mode set to FULL and most
> of this is log usage. Again setting to SIMPLE will solve that.
>
> > 2. I think that the transaction log is running -- is there some way to
> > turn
> > it off?
> Essentially NO. But see BooksOnLine under "minimally logged bulk copy"
for
> details on how to do minimally logged loads.
>
> > 3. Is there a way to clear the log? I certainly don't need it.
> Again, set to SIMPLE recovery mode.
>