There are, unfortunately, MANY things that can cause poor indexing
performance. You're going to have to do some analysis. You don't tell
us how large your database is, but obviously the more data you have,
the larger the indexes will be, thus the more work this reindexing
process will have to do. Things to check:
- physical disk fragmentation of your database and log files
- I/O utilization of your disk hardware
- other processes running on the server causing additional load?
- other processes blocking the reindex?
- is your database configured to auto-grow when needed?
- are you shrinking your databases, either with a scheduled job or the
"autoshrink" DB option? You should not shrink a production database
unless you have concerns about disk space. The combination of
shrinking, then growing, then shrinking, then growing, will lead to
severe fragmentation of your database file, causing increasingly poor
performance.
There are other factors that will affect indexing speed, but these are
the common ones.
Maury Markowitz wrote:
> I used to have this function turned on in our maint plan, running every
> Sunday night. For the last two months or so it would never complete, and t
he
> server would be non-responsive Monday morning.
> Does anyone know what causes this to happen?
> Maury"Tracy McKibben" wrote:
> There are, unfortunately, MANY things that can cause poor indexing
> performance.
This isn't a performance issue, although I should have pointed this out the
first time.
If I run a complete reindex by hand it takes perhaps 3 minutes at the worst,
and averages about 1.5 minutes. The delay in question is eight hours. It is
not that it is proceeding slowly, it simply isn't proceeding at all.
However, the system offers no indication whatsoever that there is a problem,
let alone what the problem is.
> - other processes blocking the reindex?
This is the only thing that I think could be the issue. However, it is not
clear to me how one would debug this. There is no indication in any of the
logs I have found that a lock is causing a problem, even when I return to
work Monday and see the system is "frozen".
Is there some way I can guarentee that locks are released? My users often
leave their Access apps open "forever", so it is entirely possible (and not
that uncommon) for there to be locking issues.
> - is your database configured to auto-grow when needed?
Yes.
> - are you shrinking your databases, either with a scheduled job or the
> "autoshrink" DB option? You should not shrink a production database
> unless you have concerns about disk space.
OK, but like I said, this isn't a performance issue. I will turn this off
anyway.
> shrinking, then growing, then shrinking, then growing, will lead to
> severe fragmentation of your database file
The fact that NT allows this has always made me scratch my head.
Maury|||Maury Markowitz wrote:
> This is the only thing that I think could be the issue. However, it is not
> clear to me how one would debug this. There is no indication in any of the
> logs I have found that a lock is causing a problem, even when I return to
> work Monday and see the system is "frozen".
When you leave on Friday, open a Query Analyzer connection to the
server, and leave it open. When you come in on Monday, run "sp_who2"
in that session to see if there is blocking. The reason for leaving
the QA session open on Friday is so that you aren't locked out of SQL.
Also, if you connect to the server with RDP, or can physically access
the console, take a look at Perfmon, look at things like CPU %, Disk
Queue Length, etc., these will tell you if the machine is doing
anything, and what parts of it are "bound up".
Now that you've described things a little more, I really think blocking
is your problem. It could have been the auto-shrink setting, if SQL
decides to shrink the database while you're reindexing, well that would
be bad. Access is also a common thorn in the side for causing DB
problems.|||I used to have this function turned on in our maint plan, running every
Sunday night. For the last two months or so it would never complete, and the
server would be non-responsive Monday morning.
Does anyone know what causes this to happen?
Maury|||There are, unfortunately, MANY things that can cause poor indexing
performance. You're going to have to do some analysis. You don't tell
us how large your database is, but obviously the more data you have,
the larger the indexes will be, thus the more work this reindexing
process will have to do. Things to check:
- physical disk fragmentation of your database and log files
- I/O utilization of your disk hardware
- other processes running on the server causing additional load?
- other processes blocking the reindex?
- is your database configured to auto-grow when needed?
- are you shrinking your databases, either with a scheduled job or the
"autoshrink" DB option? You should not shrink a production database
unless you have concerns about disk space. The combination of
shrinking, then growing, then shrinking, then growing, will lead to
severe fragmentation of your database file, causing increasingly poor
performance.
There are other factors that will affect indexing speed, but these are
the common ones.
Maury Markowitz wrote:
> I used to have this function turned on in our maint plan, running every
> Sunday night. For the last two months or so it would never complete, and t
he
> server would be non-responsive Monday morning.
> Does anyone know what causes this to happen?
> Maury|||"Tracy McKibben" wrote:
> There are, unfortunately, MANY things that can cause poor indexing
> performance.
This isn't a performance issue, although I should have pointed this out the
first time.
If I run a complete reindex by hand it takes perhaps 3 minutes at the worst,
and averages about 1.5 minutes. The delay in question is eight hours. It is
not that it is proceeding slowly, it simply isn't proceeding at all.
However, the system offers no indication whatsoever that there is a problem,
let alone what the problem is.
> - other processes blocking the reindex?
This is the only thing that I think could be the issue. However, it is not
clear to me how one would debug this. There is no indication in any of the
logs I have found that a lock is causing a problem, even when I return to
work Monday and see the system is "frozen".
Is there some way I can guarentee that locks are released? My users often
leave their Access apps open "forever", so it is entirely possible (and not
that uncommon) for there to be locking issues.
> - is your database configured to auto-grow when needed?
Yes.
> - are you shrinking your databases, either with a scheduled job or the
> "autoshrink" DB option? You should not shrink a production database
> unless you have concerns about disk space.
OK, but like I said, this isn't a performance issue. I will turn this off
anyway.
> shrinking, then growing, then shrinking, then growing, will lead to
> severe fragmentation of your database file
The fact that NT allows this has always made me scratch my head.
Maury|||Maury Markowitz wrote:
> This is the only thing that I think could be the issue. However, it is not
> clear to me how one would debug this. There is no indication in any of the
> logs I have found that a lock is causing a problem, even when I return to
> work Monday and see the system is "frozen".
When you leave on Friday, open a Query Analyzer connection to the
server, and leave it open. When you come in on Monday, run "sp_who2"
in that session to see if there is blocking. The reason for leaving
the QA session open on Friday is so that you aren't locked out of SQL.
Also, if you connect to the server with RDP, or can physically access
the console, take a look at Perfmon, look at things like CPU %, Disk
Queue Length, etc., these will tell you if the machine is doing
anything, and what parts of it are "bound up".
Now that you've described things a little more, I really think blocking
is your problem. It could have been the auto-shrink setting, if SQL
decides to shrink the database while you're reindexing, well that would
be bad. Access is also a common thorn in the side for causing DB
problems.|||"Tracy McKibben" wrote:
> When you leave on Friday, open a Query Analyzer connection to the
> server, and leave it open. When you come in on Monday, run "sp_who2"
> in that session to see if there is blocking. The reason for leaving
> the QA session open on Friday is so that you aren't locked out of SQL.
Ok, I'll give this a try.
> Now that you've described things a little more, I really think blocking
> is your problem.
Me too. I also have "random" locks on what appear to be read-only requests,
so this certainly could be the issue here too.
Maury|||"Tracy McKibben" wrote:
> When you leave on Friday, open a Query Analyzer connection to the
> server, and leave it open. When you come in on Monday, run "sp_who2"
> in that session to see if there is blocking. The reason for leaving
> the QA session open on Friday is so that you aren't locked out of SQL.
Ok, I'll give this a try.
> Now that you've described things a little more, I really think blocking
> is your problem.
Me too. I also have "random" locks on what appear to be read-only requests,
so this certainly could be the issue here too.
Maury
Showing posts with label unfortunately. Show all posts
Showing posts with label unfortunately. Show all posts
Wednesday, March 7, 2012
Saturday, February 25, 2012
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.
>
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.
>
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...[vbcol=seagreen]
> See inline:
> --
> Andrew J. Kelly SQL MVP
>
> "JP" <jnospamperelst@.optnospamonline.net> wrote in message
> news:e3l%23xWbhFHA.4000@.TK2MSFTNGP12.phx.gbl...
please.
> 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[vbcol=seagreen]
> liek a scratch db.
>
about
> Try running DBCC UPDATEUSAGE and see if the values change. See
BooksOnLine[vbcol=seagreen]
> for the details of what it does.
the
> 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.
>
> Essentially NO. But see BooksOnLine under "minimally logged bulk copy"
for
> details on how to do minimally logged loads.
>
>
> Again, set to SIMPLE recovery mode.
>
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...[vbcol=seagreen]
> See inline:
> --
> Andrew J. Kelly SQL MVP
>
> "JP" <jnospamperelst@.optnospamonline.net> wrote in message
> news:e3l%23xWbhFHA.4000@.TK2MSFTNGP12.phx.gbl...
please.
> 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[vbcol=seagreen]
> liek a scratch db.
>
about
> Try running DBCC UPDATEUSAGE and see if the values change. See
BooksOnLine[vbcol=seagreen]
> for the details of what it does.
the
> 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.
>
> Essentially NO. But see BooksOnLine under "minimally logged bulk copy"
for
> details on how to do minimally logged loads.
>
>
> Again, set to SIMPLE recovery mode.
>
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...
please.[vbcol=seagreen]
> 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.
>
about[vbcol=seagreen]
> Try running DBCC UPDATEUSAGE and see if the values change. See
BooksOnLine
> for the details of what it does.
>
the[vbcol=seagreen]
> 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.
>
> Essentially NO. But see BooksOnLine under "minimally logged bulk copy"
for
> details on how to do minimally logged loads.
>
>
> Again, set to SIMPLE recovery mode.
>
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...
please.[vbcol=seagreen]
> 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.
>
about[vbcol=seagreen]
> Try running DBCC UPDATEUSAGE and see if the values change. See
BooksOnLine
> for the details of what it does.
>
the[vbcol=seagreen]
> 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.
>
> Essentially NO. But see BooksOnLine under "minimally logged bulk copy"
for
> details on how to do minimally logged loads.
>
>
> Again, set to SIMPLE recovery mode.
>
Subscribe to:
Posts (Atom)