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.
>
Showing posts with label dba. Show all posts
Showing posts with label dba. Show all posts
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...[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.
>
Reorg select tables
Hi,
I′m not a expert DBA, please need your help!!! I have to defrag my database
because poor performance, but can′t. Our database have around 1TB and don′
t
have disk space and window time to defrag all database at one time.
I need to make this excluing bigest tables and make reorg index others
tables. How can I make this? Any idea?Fabio Rebelo wrote:
> Hi,
> I′m not a expert DBA, please need your help!!! I have to defrag my databa
se
> because poor performance, but can′t. Our database have around 1TB and don
′t
> have disk space and window time to defrag all database at one time.
> I need to make this excluing bigest tables and make reorg index others
> tables. How can I make this? Any idea?
Consider using this script of mine:
http://realsqlguy.com/twiki/bin/vie...IndexesAsNeeded
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy, thanks for help. Your script will help me very much, but i need
another little thing:
I need select ours biggest tables (top 30) to exclude it from script to
reorg all database. How can i make this?
If you could, please show me a script to select this tables and the sintaxe
to reorg all database without this 30 tables. After we let′s run reorg one
big table a time.
Thans your help again, and sorry my english.
"Tracy McKibben" wrote:
> Fabio Rebelo wrote:
> Consider using this script of mine:
> http://realsqlguy.com/twiki/bin/vie...IndexesAsNeeded
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||On Mon, 31 Jul 2006 11:26:02 -0700, Fabio Rebelo
<FabioRebelo@.discussions.microsoft.com> wrote:
>Hi Tracy, thanks for help. Your script will help me very much, but i need
>another little thing:
>I need select ours biggest tables (top 30) to exclude it from script to
>reorg all database. How can i make this?
Just change his one statement from:
SELECT @.Command = 'USE ' + RTRIM(@.DBName) + ' SELECT table_name FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'''
to:
SELECT @.Command = 'USE ' + RTRIM(@.DBName)
+ ' SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_TYPE = ''BASE TABLE'' AND table_name NOT IN (''bigtable1'',
''bigtable2'', ... ''bigtable30'')'
You might get other ideas along the same lines!
Good luck.
Josh
I′m not a expert DBA, please need your help!!! I have to defrag my database
because poor performance, but can′t. Our database have around 1TB and don′
t
have disk space and window time to defrag all database at one time.
I need to make this excluing bigest tables and make reorg index others
tables. How can I make this? Any idea?Fabio Rebelo wrote:
> Hi,
> I′m not a expert DBA, please need your help!!! I have to defrag my databa
se
> because poor performance, but can′t. Our database have around 1TB and don
′t
> have disk space and window time to defrag all database at one time.
> I need to make this excluing bigest tables and make reorg index others
> tables. How can I make this? Any idea?
Consider using this script of mine:
http://realsqlguy.com/twiki/bin/vie...IndexesAsNeeded
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy, thanks for help. Your script will help me very much, but i need
another little thing:
I need select ours biggest tables (top 30) to exclude it from script to
reorg all database. How can i make this?
If you could, please show me a script to select this tables and the sintaxe
to reorg all database without this 30 tables. After we let′s run reorg one
big table a time.
Thans your help again, and sorry my english.
"Tracy McKibben" wrote:
> Fabio Rebelo wrote:
> Consider using this script of mine:
> http://realsqlguy.com/twiki/bin/vie...IndexesAsNeeded
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||On Mon, 31 Jul 2006 11:26:02 -0700, Fabio Rebelo
<FabioRebelo@.discussions.microsoft.com> wrote:
>Hi Tracy, thanks for help. Your script will help me very much, but i need
>another little thing:
>I need select ours biggest tables (top 30) to exclude it from script to
>reorg all database. How can i make this?
Just change his one statement from:
SELECT @.Command = 'USE ' + RTRIM(@.DBName) + ' SELECT table_name FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'''
to:
SELECT @.Command = 'USE ' + RTRIM(@.DBName)
+ ' SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_TYPE = ''BASE TABLE'' AND table_name NOT IN (''bigtable1'',
''bigtable2'', ... ''bigtable30'')'
You might get other ideas along the same lines!
Good luck.
Josh
Reorg select tables
Hi,
I´m not a expert DBA, please need your help!!! I have to defrag my database
because poor performance, but can´t. Our database have around 1TB and don´t
have disk space and window time to defrag all database at one time.
I need to make this excluing bigest tables and make reorg index others
tables. How can I make this? Any idea?Fabio Rebelo wrote:
> Hi,
> I´m not a expert DBA, please need your help!!! I have to defrag my database
> because poor performance, but can´t. Our database have around 1TB and don´t
> have disk space and window time to defrag all database at one time.
> I need to make this excluing bigest tables and make reorg index others
> tables. How can I make this? Any idea?
Consider using this script of mine:
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy, thanks for help. Your script will help me very much, but i need
another little thing:
I need select ours biggest tables (top 30) to exclude it from script to
reorg all database. How can i make this?
If you could, please show me a script to select this tables and the sintaxe
to reorg all database without this 30 tables. After we let´s run reorg one
big table a time.
Thans your help again, and sorry my english.
"Tracy McKibben" wrote:
> Fabio Rebelo wrote:
> > Hi,
> >
> > I´m not a expert DBA, please need your help!!! I have to defrag my database
> > because poor performance, but can´t. Our database have around 1TB and don´t
> > have disk space and window time to defrag all database at one time.
> >
> > I need to make this excluing bigest tables and make reorg index others
> > tables. How can I make this? Any idea?
> Consider using this script of mine:
> http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||On Mon, 31 Jul 2006 11:26:02 -0700, Fabio Rebelo
<FabioRebelo@.discussions.microsoft.com> wrote:
>Hi Tracy, thanks for help. Your script will help me very much, but i need
>another little thing:
>I need select ours biggest tables (top 30) to exclude it from script to
>reorg all database. How can i make this?
Just change his one statement from:
SELECT @.Command = 'USE ' + RTRIM(@.DBName) + ' SELECT table_name FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'''
to:
SELECT @.Command = 'USE ' + RTRIM(@.DBName)
+ ' SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_TYPE = ''BASE TABLE'' AND table_name NOT IN (''bigtable1'',
''bigtable2'', ... ''bigtable30'')'
You might get other ideas along the same lines!
Good luck.
Josh
I´m not a expert DBA, please need your help!!! I have to defrag my database
because poor performance, but can´t. Our database have around 1TB and don´t
have disk space and window time to defrag all database at one time.
I need to make this excluing bigest tables and make reorg index others
tables. How can I make this? Any idea?Fabio Rebelo wrote:
> Hi,
> I´m not a expert DBA, please need your help!!! I have to defrag my database
> because poor performance, but can´t. Our database have around 1TB and don´t
> have disk space and window time to defrag all database at one time.
> I need to make this excluing bigest tables and make reorg index others
> tables. How can I make this? Any idea?
Consider using this script of mine:
http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy, thanks for help. Your script will help me very much, but i need
another little thing:
I need select ours biggest tables (top 30) to exclude it from script to
reorg all database. How can i make this?
If you could, please show me a script to select this tables and the sintaxe
to reorg all database without this 30 tables. After we let´s run reorg one
big table a time.
Thans your help again, and sorry my english.
"Tracy McKibben" wrote:
> Fabio Rebelo wrote:
> > Hi,
> >
> > I´m not a expert DBA, please need your help!!! I have to defrag my database
> > because poor performance, but can´t. Our database have around 1TB and don´t
> > have disk space and window time to defrag all database at one time.
> >
> > I need to make this excluing bigest tables and make reorg index others
> > tables. How can I make this? Any idea?
> Consider using this script of mine:
> http://realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||On Mon, 31 Jul 2006 11:26:02 -0700, Fabio Rebelo
<FabioRebelo@.discussions.microsoft.com> wrote:
>Hi Tracy, thanks for help. Your script will help me very much, but i need
>another little thing:
>I need select ours biggest tables (top 30) to exclude it from script to
>reorg all database. How can i make this?
Just change his one statement from:
SELECT @.Command = 'USE ' + RTRIM(@.DBName) + ' SELECT table_name FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'''
to:
SELECT @.Command = 'USE ' + RTRIM(@.DBName)
+ ' SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_TYPE = ''BASE TABLE'' AND table_name NOT IN (''bigtable1'',
''bigtable2'', ... ''bigtable30'')'
You might get other ideas along the same lines!
Good luck.
Josh
Subscribe to:
Posts (Atom)