Rendering report: February 2012

Saturday, February 25, 2012

reorganize data files

Just added a new datafile to my database. How do I distribute all the
data evenly across the two files now?
I tried dbcc dbreindex on all tables, this results in 75%/25%
I also tried creating 2 new files and emptying the main file dbcc
shrinkdb. I could not empty the main file completely though so had to
empty the 3rd file again and drop it.. this gave me a 40%/60%
Anyway to reorganize the datafiles and have 50/50 distribution?The cleanest way to ensure even distribution between all files is to export
the data, truncate the tables and import it back in again. The reason none
of the techniques worked is that SQL Server adds data to individual files
within the same file group by the percentage of free space in the file. So
if you start with 4 files of the same size and empty all the existing data
you have 4 files with equal amounts of free space. Then when you insert the
data it will use the proportional fill algorithm to fill them in in an even
manor. But when some of the files already have data in them the
distribution will be uneven due to the uneven amount of free space.
Eventually the amount of free space will even out. So if you run DBCC
DBREINDEX on all the tables many times it will eventually even out. The key
is to ensure all the files are the same size with PLENTY of free space in
each file. That way Autogrow does not kick in and mess with the even sizes.
Andrew J. Kelly SQL MVP
"Gordon Cowie" <gordy@.dynamicsdirect.com> wrote in message
news:uz%23WtZzBGHA.676@.TK2MSFTNGP10.phx.gbl...
> Just added a new datafile to my database. How do I distribute all the data
> evenly across the two files now?
> I tried dbcc dbreindex on all tables, this results in 75%/25%
> I also tried creating 2 new files and emptying the main file dbcc
> shrinkdb. I could not empty the main file completely though so had to
> empty the 3rd file again and drop it.. this gave me a 40%/60%
> Anyway to reorganize the datafiles and have 50/50 distribution?|||running dbreindex twice evened it out nicely, thanks!
Andrew J. Kelly wrote:
> The cleanest way to ensure even distribution between all files is to expor
t
> the data, truncate the tables and import it back in again. The reason none
> of the techniques worked is that SQL Server adds data to individual files
> within the same file group by the percentage of free space in the file. S
o
> if you start with 4 files of the same size and empty all the existing data
> you have 4 files with equal amounts of free space. Then when you insert th
e
> data it will use the proportional fill algorithm to fill them in in an eve
n
> manor. But when some of the files already have data in them the
> distribution will be uneven due to the uneven amount of free space.
> Eventually the amount of free space will even out. So if you run DBCC
> DBREINDEX on all the tables many times it will eventually even out. The k
ey
> is to ensure all the files are the same size with PLENTY of free space in
> each file. That way Autogrow does not kick in and mess with the even size
s.
>

Reorganize data and index pages option

We have a 40GB database and we, on a weekly basis, run a maintainance plan
with the option "Reorganize data and index pages" I think it is creating
a 20GB log file. The sql docs say that that option "Cause table indexes in
the database to be dropped and re-created with a new fill factor". I guess
that would explain it.
Is there a less expensive operation that can be performed? Like a DBCC
dbreindex but for all tables? I guess I could write a script to do that.
Any thoughts?
-alan>Is there a less expensive operation that can be
performed? Like a DBCC
>dbreindex but for all tables?
Same thing. See dbcc indexdefrag.
>--Original Message--
>We have a 40GB database and we, on a weekly basis, run a
maintainance plan
>with the option "Reorganize data and index pages" I
think it is creating
>a 20GB log file. The sql docs say that that
option "Cause table indexes in
>the database to be dropped and re-created with a new fill
factor". I guess
>that would explain it.
>Is there a less expensive operation that can be
performed? Like a DBCC
>dbreindex but for all tables? I guess I could write a
script to do that.
>Any thoughts?
>-alan
>
>.
>|||Sounds like you need the DBCC INDEXDEFRAG I put in SQL Server 2000. Look in
BOL for DBCC SHOWCONTIG and I included a script that will defrag all the
indexes in your database based on a fragmentation threshold you set.
Also see our new whitepaper on this topic at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alan Berezin" <aberezin@.drillinginfo.com> wrote in message
news:eRunmZ7TDHA.2312@.TK2MSFTNGP12.phx.gbl...
> We have a 40GB database and we, on a weekly basis, run a maintainance plan
> with the option "Reorganize data and index pages" I think it is
creating
> a 20GB log file. The sql docs say that that option "Cause table indexes
in
> the database to be dropped and re-created with a new fill factor". I
guess
> that would explain it.
> Is there a less expensive operation that can be performed? Like a DBCC
> dbreindex but for all tables? I guess I could write a script to do that.
> Any thoughts?
> -alan
>

Reorganize data and index pages

Hello all,

SQL 2000 on Windows 2000. If I go into all tasks, maintenance plan, it
gives me an option to reorganize data and index pages. When I check on
it, it populates the line "change free space per page percentage to" and
puts in 10 in there. Is this the default for free space? Is it the
data pages that will have 10% free space or just the index pages? Are
data and index on the same pages?

Thanks,
Raziq.

*** Sent via Developersdex http://www.developersdex.com ***Hi

If you profile the maintainance plan is seems that choosing causes a call to
DBCC DBREINDEX which is documented in books online. The 10% free space value
is reversed to be a 90% fillfactor as described by:

fillfactor
Is the percentage of space on each index page to be used for storing data
when the index is created. fillfactor replaces the original fillfactor as
the new default for the index and for any other nonclustered indexes rebuilt
because a clustered index is rebuilt. When fillfactor is 0, DBCC DBREINDEX
uses the original fillfactor specified when the index was created.

If you look at the Table and Index Architecture section of books online you
will see how clustered and non-clustered indexes are composed.
or at:
http://msdn.microsoft.com/library/d...ar_da2_8sit.asp
http://msdn.microsoft.com/library/d...ar_da2_1tbn.asp
http://msdn.microsoft.com/library/d...ar_da2_75mb.asp

You may want to get "Inside SQL Server 2000" by Kalen Delany ISBN
0-7356-0998-5 which talks about this sort of thing in detail.

John

"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message
news:rF97e.5$Nk2.347@.news.uswest.net...
> Hello all,
> SQL 2000 on Windows 2000. If I go into all tasks, maintenance plan, it
> gives me an option to reorganize data and index pages. When I check on
> it, it populates the line "change free space per page percentage to" and
> puts in 10 in there. Is this the default for free space? Is it the
> data pages that will have 10% free space or just the index pages? Are
> data and index on the same pages?
> Thanks,
> Raziq.
>
> *** Sent via Developersdex http://www.developersdex.com ***

Reorganize data and index pages

I have a couple of questions here so I will break them out.
1. I have used the Database Maintenance plan to schedule a Reorganization of
the data and index pages that runs every weekend. I lef the free space per
page percentage to 10%.
-A: Do I need to do anything special to make sure the statistics are
updated such as sp_updatestats? Can I trust that by doing that alone my
indexes are in the best shape and that my data is defragmented the best way?
Isn't this like a DBCC REINDEX or maybe a complete rebuild of the indexes?
-B: It seems that from what I've read that the 10% varies on what your
database is used for. Is that correct?
2. If I need to defrag databases that aren't on that plan, I'll normally
just do the following when I see fragmentation above 20 percent:
USE Database
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
USE Database
DBCC INDEXDEFRAG (Database, table, index)
GO
3. I notice that the sysindexes table has an index called tsysindexes that
has logical scan fragmentation at 99, but it's small at 119 pages. Is that a
concern? I don't ever manually defrag the system tables as I believe I'm not
supposed to, but I'm not sure if the weekend job gets it.
Thanks
JasonHi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't have
to worry about updating the statistics as it's done right after indexes are
rebuilt. however you may have to update the statistics if you are using DBCC
INDEXDEFRAG.
You can find some good tips and scripts related to this topic @.
<WWW.SQLCOMMUNITY.COM>
<http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
<http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
<http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
<http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
<http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
Hope this helps.
--
Thank you,
<WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server Professionals)
SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
Forums, etc
"jason7655" wrote:
> I have a couple of questions here so I will break them out.
> 1. I have used the Database Maintenance plan to schedule a Reorganization of
> the data and index pages that runs every weekend. I lef the free space per
> page percentage to 10%.
> -A: Do I need to do anything special to make sure the statistics are
> updated such as sp_updatestats? Can I trust that by doing that alone my
> indexes are in the best shape and that my data is defragmented the best way?
> Isn't this like a DBCC REINDEX or maybe a complete rebuild of the indexes?
> -B: It seems that from what I've read that the 10% varies on what your
> database is used for. Is that correct?
> 2. If I need to defrag databases that aren't on that plan, I'll normally
> just do the following when I see fragmentation above 20 percent:
> USE Database
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> USE Database
> DBCC INDEXDEFRAG (Database, table, index)
> GO
> 3. I notice that the sysindexes table has an index called tsysindexes that
> has logical scan fragmentation at 99, but it's small at 119 pages. Is that a
> concern? I don't ever manually defrag the system tables as I believe I'm not
> supposed to, but I'm not sure if the weekend job gets it.
> Thanks
> Jason|||Thanks.
I probably should have mentioned that we are on SQL Server 2000.
"SQLGurus" wrote:
> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't have
> to worry about updating the statistics as it's done right after indexes are
> rebuilt. however you may have to update the statistics if you are using DBCC
> INDEXDEFRAG.
> You can find some good tips and scripts related to this topic @.
> <WWW.SQLCOMMUNITY.COM>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
> Hope this helps.
> --
> Thank you,
> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server Professionals)
> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
> Forums, etc
>
> "jason7655" wrote:
> > I have a couple of questions here so I will break them out.
> >
> > 1. I have used the Database Maintenance plan to schedule a Reorganization of
> > the data and index pages that runs every weekend. I lef the free space per
> > page percentage to 10%.
> > -A: Do I need to do anything special to make sure the statistics are
> > updated such as sp_updatestats? Can I trust that by doing that alone my
> > indexes are in the best shape and that my data is defragmented the best way?
> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the indexes?
> > -B: It seems that from what I've read that the 10% varies on what your
> > database is used for. Is that correct?
> >
> > 2. If I need to defrag databases that aren't on that plan, I'll normally
> > just do the following when I see fragmentation above 20 percent:
> > USE Database
> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> >
> > USE Database
> > DBCC INDEXDEFRAG (Database, table, index)
> > GO
> >
> > 3. I notice that the sysindexes table has an index called tsysindexes that
> > has logical scan fragmentation at 99, but it's small at 119 pages. Is that a
> > concern? I don't ever manually defrag the system tables as I believe I'm not
> > supposed to, but I'm not sure if the weekend job gets it.
> >
> > Thanks
> > Jason|||The commands Kevin gave were for SQL2000.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:FE53F490-A4CF-4345-B974-4D9B411F9943@.microsoft.com...
> Thanks.
> I probably should have mentioned that we are on SQL Server 2000.
> "SQLGurus" wrote:
>> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't
>> have
>> to worry about updating the statistics as it's done right after indexes
>> are
>> rebuilt. however you may have to update the statistics if you are using
>> DBCC
>> INDEXDEFRAG.
>> You can find some good tips and scripts related to this topic @.
>> <WWW.SQLCOMMUNITY.COM>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
>> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
>> Hope this helps.
>> --
>> Thank you,
>> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server
>> Professionals)
>> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
>> Forums, etc
>>
>> "jason7655" wrote:
>> > I have a couple of questions here so I will break them out.
>> >
>> > 1. I have used the Database Maintenance plan to schedule a
>> > Reorganization of
>> > the data and index pages that runs every weekend. I lef the free space
>> > per
>> > page percentage to 10%.
>> > -A: Do I need to do anything special to make sure the statistics are
>> > updated such as sp_updatestats? Can I trust that by doing that alone my
>> > indexes are in the best shape and that my data is defragmented the best
>> > way?
>> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the
>> > indexes?
>> > -B: It seems that from what I've read that the 10% varies on what your
>> > database is used for. Is that correct?
>> >
>> > 2. If I need to defrag databases that aren't on that plan, I'll
>> > normally
>> > just do the following when I see fragmentation above 20 percent:
>> > USE Database
>> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
>> >
>> > USE Database
>> > DBCC INDEXDEFRAG (Database, table, index)
>> > GO
>> >
>> > 3. I notice that the sysindexes table has an index called tsysindexes
>> > that
>> > has logical scan fragmentation at 99, but it's small at 119 pages. Is
>> > that a
>> > concern? I don't ever manually defrag the system tables as I believe
>> > I'm not
>> > supposed to, but I'm not sure if the weekend job gets it.
>> >
>> > Thanks
>> > Jason|||The links were specific to 2005, and that's what I was referring to.
Although one of them mentions a script for 2000.
Wasn't taking anything away from it...just mentioning that I left off an
important piece of information b/c 2005 goes a long way in helping one
monitor that sort of thing.
"Andrew J. Kelly" wrote:
> The commands Kevin gave were for SQL2000.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "jason7655" <jason7655@.discussions.microsoft.com> wrote in message
> news:FE53F490-A4CF-4345-B974-4D9B411F9943@.microsoft.com...
> > Thanks.
> >
> > I probably should have mentioned that we are on SQL Server 2000.
> >
> > "SQLGurus" wrote:
> >
> >> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't
> >> have
> >> to worry about updating the statistics as it's done right after indexes
> >> are
> >> rebuilt. however you may have to update the statistics if you are using
> >> DBCC
> >> INDEXDEFRAG.
> >>
> >> You can find some good tips and scripts related to this topic @.
> >> <WWW.SQLCOMMUNITY.COM>
> >>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
> >> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
> >>
> >> Hope this helps.
> >> --
> >> Thank you,
> >> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server
> >> Professionals)
> >> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
> >> Forums, etc
> >>
> >>
> >> "jason7655" wrote:
> >>
> >> > I have a couple of questions here so I will break them out.
> >> >
> >> > 1. I have used the Database Maintenance plan to schedule a
> >> > Reorganization of
> >> > the data and index pages that runs every weekend. I lef the free space
> >> > per
> >> > page percentage to 10%.
> >> > -A: Do I need to do anything special to make sure the statistics are
> >> > updated such as sp_updatestats? Can I trust that by doing that alone my
> >> > indexes are in the best shape and that my data is defragmented the best
> >> > way?
> >> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the
> >> > indexes?
> >> > -B: It seems that from what I've read that the 10% varies on what your
> >> > database is used for. Is that correct?
> >> >
> >> > 2. If I need to defrag databases that aren't on that plan, I'll
> >> > normally
> >> > just do the following when I see fragmentation above 20 percent:
> >> > USE Database
> >> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> >> >
> >> > USE Database
> >> > DBCC INDEXDEFRAG (Database, table, index)
> >> > GO
> >> >
> >> > 3. I notice that the sysindexes table has an index called tsysindexes
> >> > that
> >> > has logical scan fragmentation at 99, but it's small at 119 pages. Is
> >> > that a
> >> > concern? I don't ever manually defrag the system tables as I believe
> >> > I'm not
> >> > supposed to, but I'm not sure if the weekend job gets it.
> >> >
> >> > Thanks
> >> > Jason
>|||Sorry I didn't click on the links.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:B87939FC-6CAB-48DC-8ECB-129B76AB5486@.microsoft.com...
> The links were specific to 2005, and that's what I was referring to.
> Although one of them mentions a script for 2000.
> Wasn't taking anything away from it...just mentioning that I left off an
> important piece of information b/c 2005 goes a long way in helping one
> monitor that sort of thing.
> "Andrew J. Kelly" wrote:
>> The commands Kevin gave were for SQL2000.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "jason7655" <jason7655@.discussions.microsoft.com> wrote in message
>> news:FE53F490-A4CF-4345-B974-4D9B411F9943@.microsoft.com...
>> > Thanks.
>> >
>> > I probably should have mentioned that we are on SQL Server 2000.
>> >
>> > "SQLGurus" wrote:
>> >
>> >> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you
>> >> don't
>> >> have
>> >> to worry about updating the statistics as it's done right after
>> >> indexes
>> >> are
>> >> rebuilt. however you may have to update the statistics if you are
>> >> using
>> >> DBCC
>> >> INDEXDEFRAG.
>> >>
>> >> You can find some good tips and scripts related to this topic @.
>> >> <WWW.SQLCOMMUNITY.COM>
>> >>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
>> >> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
>> >>
>> >> Hope this helps.
>> >> --
>> >> Thank you,
>> >> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server
>> >> Professionals)
>> >> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events,
>> >> SQL
>> >> Forums, etc
>> >>
>> >>
>> >> "jason7655" wrote:
>> >>
>> >> > I have a couple of questions here so I will break them out.
>> >> >
>> >> > 1. I have used the Database Maintenance plan to schedule a
>> >> > Reorganization of
>> >> > the data and index pages that runs every weekend. I lef the free
>> >> > space
>> >> > per
>> >> > page percentage to 10%.
>> >> > -A: Do I need to do anything special to make sure the statistics
>> >> > are
>> >> > updated such as sp_updatestats? Can I trust that by doing that alone
>> >> > my
>> >> > indexes are in the best shape and that my data is defragmented the
>> >> > best
>> >> > way?
>> >> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the
>> >> > indexes?
>> >> > -B: It seems that from what I've read that the 10% varies on what
>> >> > your
>> >> > database is used for. Is that correct?
>> >> >
>> >> > 2. If I need to defrag databases that aren't on that plan, I'll
>> >> > normally
>> >> > just do the following when I see fragmentation above 20 percent:
>> >> > USE Database
>> >> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
>> >> >
>> >> > USE Database
>> >> > DBCC INDEXDEFRAG (Database, table, index)
>> >> > GO
>> >> >
>> >> > 3. I notice that the sysindexes table has an index called
>> >> > tsysindexes
>> >> > that
>> >> > has logical scan fragmentation at 99, but it's small at 119 pages.
>> >> > Is
>> >> > that a
>> >> > concern? I don't ever manually defrag the system tables as I believe
>> >> > I'm not
>> >> > supposed to, but I'm not sure if the weekend job gets it.
>> >> >
>> >> > Thanks
>> >> > Jason
>>

reorganization percent -- ?

Hi All ,
i am using a 3rd s/w(Idera SQL DM to be exact) to perform diagonistics on
my SQL 2000 Server. from the alert log , i got some of the following messages
:
able managed_object (1445580188) in Database Altiris_Incidents (6) has a
reorganization percent of 27%
Table wu_custom_instance (2146106686) in Database Altiris_Incidents (6) has
a reorganization percent of 33%
Table wuci_history (254623950) in Database Altiris_Incidents (6) has a
reorganization percent of 25%
Table tb_Stock (1063674837) in Database SidataSQL_DC (7) has a
reorganization percent of 31%
Table tb_StockInfo (110623437) in Database SidataSQL_DC (7) has a
reorganization percent of 38%
Table tb_Supplier (142623551) in Database SidataSQL_DC (7) has a
reorganization percent of 25%
Table tb_StockCons (1735677231) in Database SidataSQL_DC (7) has a
reorganization percent of 24%
Table tb_Transaction (174623665) in Database SidataSQL_DC (7) has a
reorganization percent of 39%
Table tb_Purchase (292196091) in Database SidataSQL_DC (7) has a
reorganization percent of 48%
what does the organization percent really means ? the same as the fill factor
(i am using the default which is 100%) and what does it mean for my SQL
Server ?
appreciate any advise
tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1Sounds like a question for Idera not SQL Server. Those are their own
custom messages and I assume they mean fragmentation % but who knows. I
think they need to come up with a better alert message than that<g>.
--
Andrew J. Kelly SQL MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:593d6713d72ac@.uwe...
> Hi All ,
> i am using a 3rd s/w(Idera SQL DM to be exact) to perform diagonistics
> on
> my SQL 2000 Server. from the alert log , i got some of the following
> messages
> :
> able managed_object (1445580188) in Database Altiris_Incidents (6) has a
> reorganization percent of 27%
> Table wu_custom_instance (2146106686) in Database Altiris_Incidents (6)
> has
> a reorganization percent of 33%
> Table wuci_history (254623950) in Database Altiris_Incidents (6) has a
> reorganization percent of 25%
> Table tb_Stock (1063674837) in Database SidataSQL_DC (7) has a
> reorganization percent of 31%
> Table tb_StockInfo (110623437) in Database SidataSQL_DC (7) has a
> reorganization percent of 38%
> Table tb_Supplier (142623551) in Database SidataSQL_DC (7) has a
> reorganization percent of 25%
> Table tb_StockCons (1735677231) in Database SidataSQL_DC (7) has a
> reorganization percent of 24%
> Table tb_Transaction (174623665) in Database SidataSQL_DC (7) has a
> reorganization percent of 39%
> Table tb_Purchase (292196091) in Database SidataSQL_DC (7) has a
> reorganization percent of 48%
>
> what does the organization percent really means ? the same as the fill
> factor
> (i am using the default which is 100%) and what does it mean for my SQL
> Server ?
> appreciate any advise
> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||tks Andrew i'l check wif Idera then but on further checks it as per wat you
have mentioned(i.e fragmentation %) as the system is allowing me to whether
to do a defrag or not .
but in SQL Server , which DBCC or other commands can allow me to do a defrag
on the tables
tks & rdgs
Andrew J. Kelly wrote:
>Sounds like a question for Idera not SQL Server. Those are their own
>custom messages and I assume they mean fragmentation % but who knows. I
>think they need to come up with a better alert message than that<g>.
>> Hi All ,
>[quoted text clipped - 39 lines]
>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||This is a multi-part message in MIME format.
--050700080400090100010001
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
In SQL 2000:
* DBCC SHOWCONTIG will show you the fragmentation stats on indexes.
* DBCC DBREINDEX & DBCC INDEXDEFRAG will rebuild & defragment your
indexes respectively.
In SQL 2005:
* Querying the dynamic management view
sys.dm_db_index_physical_stats will show you the index fragmentation
* use the ALTER INDEX statement to rebuild or defragment your indexes
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
maxzsim via SQLMonster.com wrote:
>tks Andrew i'l check wif Idera then but on further checks it as per wat you
>have mentioned(i.e fragmentation %) as the system is allowing me to whether
>to do a defrag or not .
>but in SQL Server , which DBCC or other commands can allow me to do a defrag
>on the tables
>tks & rdgs
>Andrew J. Kelly wrote:
>
>>Sounds like a question for Idera not SQL Server. Those are their own
>>custom messages and I assume they mean fragmentation % but who knows. I
>>think they need to come up with a better alert message than that<g>.
>>
>>Hi All ,
>>
>>[quoted text clipped - 39 lines]
>>
>>tks & rdgs
>>
>
>
--050700080400090100010001
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>In SQL 2000: 
</tt>
<ul>
<li><tt>DBCC SHOWCONTIG will show you the fragmentation stats on
indexes. 
</tt></li>
<li><tt>DBCC DBREINDEX & DBCC INDEXDEFRAG will rebuild &
defragment your indexes respectively.</tt></li>
</ul>
<tt>In SQL 2005: 
</tt>
<ul>
<li><tt>Querying the dynamic management view
sys.dm_db_index_physical_stats will show you the index fragmentation</tt></li>
<li><tt>use the ALTER INDEX statement to rebuild or defragment your
indexes 
</tt></li>
</ul>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
maxzsim via SQLMonster.com wrote:
<blockquote cite="mid593db7aed34e0@.uwe" type="cite">
<pre wrap="">tks Andrew i'l check wif Idera then but on further checks it as per wat you
have mentioned(i.e fragmentation %) as the system is allowing me to whether
to do a defrag or not .
but in SQL Server , which DBCC or other commands can allow me to do a defrag
on the tables
tks & rdgs
Andrew J. Kelly wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Sounds like a question for Idera not SQL Server. Those are their own
custom messages and I assume they mean fragmentation % but who knows. I
think they need to come up with a better alert message than that<g>.
</pre>
<blockquote type="cite">
<pre wrap="">Hi All ,
</pre>
</blockquote>
<pre wrap="">[quoted text clipped - 39 lines]
</pre>
<blockquote type="cite">
<pre wrap="">tks & rdgs
</pre>
</blockquote>
</blockquote>
<pre wrap=""><!-->
</pre>
</blockquote>
</body>
</html>
--050700080400090100010001--|||tks Mike
Mike Hodgson wrote:
>In SQL 2000:
> * DBCC SHOWCONTIG will show you the fragmentation stats on indexes.
> * DBCC DBREINDEX & DBCC INDEXDEFRAG will rebuild & defragment your
> indexes respectively.
>In SQL 2005:
> * Querying the dynamic management view
> sys.dm_db_index_physical_stats will show you the index fragmentation
> * use the ALTER INDEX statement to rebuild or defragment your indexes
>--
>*mike hodgson*
>blog: http://sqlnerd.blogspot.com
>>tks Andrew i'l check wif Idera then but on further checks it as per wat you
>>have mentioned(i.e fragmentation %) as the system is allowing me to whether
>[quoted text clipped - 24 lines]
>>
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1

Reorganization and logs

I have a db maintenance plan set up that reorganizes my data once a week. I
noticed that the SQL Server Log does not indicate that this has run.
However, if I use the option to 'Write report to a text file in directory:',
a .txt file is created that indicates the reorganization has run. Why does
this not show up in the SQL Server Log as my reindexing and backups do? I'm
running SQL 2000.
The backups really shouldn't be in there either in my opinion. The log is
mainly for error info and a successful backup can be found in the job
history. If you want to know if and when it ran you can check the Job
History and the MP history.
Andrew J. Kelly SQL MVP
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:B604E4EE-8C77-4B5D-8B05-EBFC42BE0023@.microsoft.com...
> I have a db maintenance plan set up that reorganizes my data once a week.
I
> noticed that the SQL Server Log does not indicate that this has run.
> However, if I use the option to 'Write report to a text file in
directory:',
> a .txt file is created that indicates the reorganization has run. Why
does
> this not show up in the SQL Server Log as my reindexing and backups do?
I'm
> running SQL 2000.

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.
>

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.
>

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.
>

Reorg specific table?

Is there a way to reorg(reorganize) a specific table instead of using
maintenance plan against all tables in SQL Server 2000? How about SQL Server
2005?
Maintenance plan for Reorg is able to reorg a specific table in SQL Server.
Best regards,
Do.
Message posted via http://www.droptable.com
Check your Books Online documentation:
For SQL 2000, read about DBCC INDEXDEFRAG.
For SQL 2005, read about ALTER INDEX... REORGANIZE
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Do Park via droptable.com" <u3287@.uwe> wrote in message
news:724686abc2070@.uwe...
> Is there a way to reorg(reorganize) a specific table instead of using
> maintenance plan against all tables in SQL Server 2000? How about SQL
> Server
> 2005?
> Maintenance plan for Reorg is able to reorg a specific table in SQL
> Server.
> Best regards,
> Do.
> --
> Message posted via http://www.droptable.com
>
|||I am talking about table, Not Index.
Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG TABLE
function (especially reorg specific table) in SQL Server.
Best regards,
Do.
Message posted via http://www.droptable.com
|||There's no such functionality. If the table is a clustered index, then the index and the table is
the same. If not, there is no organization between the rows, so you would have to explain what such
a reorg would do.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Do Park via droptable.com" <u3287@.uwe> wrote in message news:7246f2cadebfc@.uwe...
>I am talking about table, Not Index.
> Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG TABLE
> function (especially reorg specific table) in SQL Server.
> Best regards,
> Do.
> --
> Message posted via http://www.droptable.com
>
|||Hi Do
Only indexes can be REORG'd in SQL Server. However, if you have a clustered
index, the leaf level IS the table data, so reorg'ing the clustered index
will reorg the table.
Reorg'ing basically means getting the logical order and physical order to be
the same. A table without a clustered index has no logical order (it is a
heap) so reorg has no meaning.
If you tell us exactly what REORG TABLE does in DB2, we might be able to
tell you how to get the same behavior in SQL Server.
You also might want to take a look at this whitepaper:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Do Park via droptable.com" <u3287@.uwe> wrote in message
news:7246f2cadebfc@.uwe...
>I am talking about table, Not Index.
> Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG
> TABLE
> function (especially reorg specific table) in SQL Server.
> Best regards,
> Do.
> --
> Message posted via http://www.droptable.com
>

Reorg specific table?

Is there a way to reorg(reorganize) a specific table instead of using
maintenance plan against all tables in SQL Server 2000? How about SQL Server
2005?
Maintenance plan for Reorg is able to reorg a specific table in SQL Server.
Best regards,
Do.
Message posted via http://www.droptable.comCheck your Books Online documentation:
For SQL 2000, read about DBCC INDEXDEFRAG.
For SQL 2005, read about ALTER INDEX... REORGANIZE
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Do Park via droptable.com" <u3287@.uwe> wrote in message
news:724686abc2070@.uwe...
> Is there a way to reorg(reorganize) a specific table instead of using
> maintenance plan against all tables in SQL Server 2000? How about SQL
> Server
> 2005?
> Maintenance plan for Reorg is able to reorg a specific table in SQL
> Server.
> Best regards,
> Do.
> --
> Message posted via http://www.droptable.com
>|||I am talking about table, Not Index.
Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG TABLE
function (especially reorg specific table) in SQL Server.
Best regards,
Do.
Message posted via http://www.droptable.com|||There's no such functionality. If the table is a clustered index, then the i
ndex and the table is
the same. If not, there is no organization between the rows, so you would ha
ve to explain what such
a reorg would do.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Do Park via droptable.com" <u3287@.uwe> wrote in message news:7246f2cadebfc@.uwe...agreen">
>I am talking about table, Not Index.
> Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG TAB
LE
> function (especially reorg specific table) in SQL Server.
> Best regards,
> Do.
> --
> Message posted via http://www.droptable.com
>|||Hi Do
Only indexes can be REORG'd in SQL Server. However, if you have a clustered
index, the leaf level IS the table data, so reorg'ing the clustered index
will reorg the table.
Reorg'ing basically means getting the logical order and physical order to be
the same. A table without a clustered index has no logical order (it is a
heap) so reorg has no meaning.
If you tell us exactly what REORG TABLE does in DB2, we might be able to
tell you how to get the same behavior in SQL Server.
You also might want to take a look at this whitepaper:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Do Park via droptable.com" <u3287@.uwe> wrote in message
news:7246f2cadebfc@.uwe...
>I am talking about table, Not Index.
> Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG
> TABLE
> function (especially reorg specific table) in SQL Server.
> Best regards,
> Do.
> --
> Message posted via http://www.droptable.com
>

Reorg specific table?

Is there a way to reorg(reorganize) a specific table instead of using
maintenance plan against all tables in SQL Server 2000? How about SQL Server
2005?
Maintenance plan for Reorg is able to reorg a specific table in SQL Server.
Best regards,
Do.
--
Message posted via http://www.sqlmonster.comCheck your Books Online documentation:
For SQL 2000, read about DBCC INDEXDEFRAG.
For SQL 2005, read about ALTER INDEX... REORGANIZE
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Do Park via SQLMonster.com" <u3287@.uwe> wrote in message
news:724686abc2070@.uwe...
> Is there a way to reorg(reorganize) a specific table instead of using
> maintenance plan against all tables in SQL Server 2000? How about SQL
> Server
> 2005?
> Maintenance plan for Reorg is able to reorg a specific table in SQL
> Server.
> Best regards,
> Do.
> --
> Message posted via http://www.sqlmonster.com
>|||I am talking about table, Not Index.
Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG TABLE
function (especially reorg specific table) in SQL Server.
Best regards,
Do.
--
Message posted via http://www.sqlmonster.com|||There's no such functionality. If the table is a clustered index, then the index and the table is
the same. If not, there is no organization between the rows, so you would have to explain what such
a reorg would do.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Do Park via SQLMonster.com" <u3287@.uwe> wrote in message news:7246f2cadebfc@.uwe...
>I am talking about table, Not Index.
> Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG TABLE
> function (especially reorg specific table) in SQL Server.
> Best regards,
> Do.
> --
> Message posted via http://www.sqlmonster.com
>|||Hi Do
Only indexes can be REORG'd in SQL Server. However, if you have a clustered
index, the leaf level IS the table data, so reorg'ing the clustered index
will reorg the table.
Reorg'ing basically means getting the logical order and physical order to be
the same. A table without a clustered index has no logical order (it is a
heap) so reorg has no meaning.
If you tell us exactly what REORG TABLE does in DB2, we might be able to
tell you how to get the same behavior in SQL Server.
You also might want to take a look at this whitepaper:
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Do Park via SQLMonster.com" <u3287@.uwe> wrote in message
news:7246f2cadebfc@.uwe...
>I am talking about table, Not Index.
> Other RDMS like DB2, it has REORG TABLE utility. I could not see REORG
> TABLE
> function (especially reorg specific table) in SQL Server.
> Best regards,
> Do.
> --
> Message posted via http://www.sqlmonster.com
>

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

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

reorg PK clustered index in VLDB

My data warehouse is now 4 years old and the size is about
600 GB od data.
We had only data insert no update in this duration. PKs
are all clustered indexes and in chronological order.
Some of tables are as large as 100 GB.
I wanted to check if the PKs are in good figure and ran
DBCC showcontig against many of large tables and 80 % had
bad rate for scan density , such as 50 %, 30 %.
I may need to reorganise PK.
I BOL it says comparing the values of Extent Switches and
Extents Scanned is a way to know how much fragmented. But
it says this method does not work if the index spans
multiple files. I presume all VLDB exploit multiple files
for one table in order to gain physical disk I/O.
My question: how can I check fragmentation rate of my
large tables which span multiple files (up to 4 to 6
files)?
What is the best way to reorganise clustered index which
are PK ? I have to drop all FK in order to reorganise PK,
don't I !
I hope to hear your idea!!!When you say you presume your db spans multiple files, does the database use
more than one file other than the MDF? DBCC DBreindex on the clustered key
should reindex your data tables and automatically reindex your other
nonclustered indexes.
Some links:
http://www.microsoft.com/technet/co...ql/sql0326.mspx
http://www.microsoft.com/technet/co...ql/sql1014.mspx
http://www.sqlservercentral.com/scr...butions/721.asp
Ray Higdon MCSE, MCDBA, CCNA
--
"didi" <anonymous@.discussions.microsoft.com> wrote in message
news:140c01c40b37$dd2c95d0$3501280a@.phx.gbl...
> My data warehouse is now 4 years old and the size is about
> 600 GB od data.
> We had only data insert no update in this duration. PKs
> are all clustered indexes and in chronological order.
> Some of tables are as large as 100 GB.
> I wanted to check if the PKs are in good figure and ran
> DBCC showcontig against many of large tables and 80 % had
> bad rate for scan density , such as 50 %, 30 %.
> I may need to reorganise PK.
> I BOL it says comparing the values of Extent Switches and
> Extents Scanned is a way to know how much fragmented. But
> it says this method does not work if the index spans
> multiple files. I presume all VLDB exploit multiple files
> for one table in order to gain physical disk I/O.
> My question: how can I check fragmentation rate of my
> large tables which span multiple files (up to 4 to 6
> files)?
> What is the best way to reorganise clustered index which
> are PK ? I have to drop all FK in order to reorganise PK,
> don't I !
> I hope to hear your idea!!!|||MDF file is used only for system table in all of my
databases. (especially when dealing with VLDB).
The database is over 600GB, and each table could be nearly
100GB,
Would DBreindex a good solution ?
This will copy the whole table into different location
without asking !

>--Original Message--
>When you say you presume your db spans multiple files,
does the database use
>more than one file other than the MDF? DBCC DBreindex on
the clustered key
>should reindex your data tables and automatically reindex
your other
>nonclustered indexes.
>Some links:
>http://www.microsoft.com/technet/co...chats/trans/sql
/sql0326.mspx
>http://www.microsoft.com/technet/co...chats/trans/sql
/sql1014.mspx
>http://www.sqlservercentral.com/scr...tributions/721.
asp
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"didi" <anonymous@.discussions.microsoft.com> wrote in
message
>news:140c01c40b37$dd2c95d0$3501280a@.phx.gbl...
about
had
and
But
files
PK,
>
>.
>|||Did those links help?
Ray Higdon MCSE, MCDBA, CCNA
--
"didi" <anonymous@.discussions.microsoft.com> wrote in message
news:148601c40b4b$a0c711b0$3a01280a@.phx.gbl...
> MDF file is used only for system table in all of my
> databases. (especially when dealing with VLDB).
> The database is over 600GB, and each table could be nearly
> 100GB,
> Would DBreindex a good solution ?
> This will copy the whole table into different location
> without asking !
>
> does the database use
> the clustered key
> your other
> /sql0326.mspx
> /sql1014.mspx
> asp
> message
> about
> had
> and
> But
> files
> PK,|||Links were very good! Thank you very much!
Especially Index Defrag Best Practices.
So, according to the article I should use fragmentation
level by logical scan fragmentation.
Still I am not very sure about using DBCC INDEXDEFRAG.
Because when a table is 100GB, and do this operation, how
large the log should be allocated ? 200 GB, 300 GB ?
Usually for copying data it takes about 2.5 times of data
size consumed in log before the data is inserted into.
Would DBCC INDEXDEFRAG be a best way in VLDB environment ?

>--Original Message--
>Did those links help?
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"didi" <anonymous@.discussions.microsoft.com> wrote in
message
>news:148601c40b4b$a0c711b0$3a01280a@.phx.gbl...
nearly
on
reindex
>http://www.microsoft.com/technet/co...chats/trans/sql
>http://www.microsoft.com/technet/co...chats/trans/sql
>http://www.sqlservercentral.com/scr...tributions/721.
PKs
ran
which
>
>.
>|||Depends on the needed uptime of your DB, you can write scripts to defrag in
chunks. Here is an example of using dbreindex (you can alter to use index
defrag) and backing up the log when needed, think I got this from MVP Andrew
Kelly but not 100% sure:
-- Reindexing the tables --
SET NOCOUNT ON
DECLARE @.TableName VARCHAR(100), @.Counter INT
SET @.Counter = 1
DECLARE curTables CURSOR STATIC LOCAL
FOR
SELECT Table_Name
FROM Information_Schema.Tables
WHERE Table_Type = 'BASE TABLE'
OPEN curTables
FETCH NEXT FROM curTables INTO @.TableName
SET @.TableName = RTRIM(@.TableName)
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT 'Reindexing ' + @.TableName
DBCC DBREINDEX (@.TableName)
SET @.Counter = @.Counter + 1
-- Backup the Log every so often so as not to fill the log
IF @.Counter % 10 = 0
BEGIN
BACKUP LOG [Presents] TO [DD_Presents_Log] WITH NOINIT , NOUNLOAD
,
NAME = N'Presents Log Backup', NOSKIP , STATS = 10,
NOFORMAT
END
FETCH NEXT FROM curTables INTO @.TableName
END
CLOSE curTables
DEALLOCATE curTables
Ray Higdon MCSE, MCDBA, CCNA
--
"didi" <anonymous@.discussions.microsoft.com> wrote in message
news:159401c40c21$2fc43b60$3a01280a@.phx.gbl...
> Links were very good! Thank you very much!
> Especially Index Defrag Best Practices.
> So, according to the article I should use fragmentation
> level by logical scan fragmentation.
> Still I am not very sure about using DBCC INDEXDEFRAG.
> Because when a table is 100GB, and do this operation, how
> large the log should be allocated ? 200 GB, 300 GB ?
> Usually for copying data it takes about 2.5 times of data
> size consumed in log before the data is inserted into.
> Would DBCC INDEXDEFRAG be a best way in VLDB environment ?
>
> message
> nearly
> on
> reindex
> PKs
> ran
> which

Reorg indexes w/o log growing - how?

Hello:
is there a way to reorg indexes without the log growing to the size of the
whole db and more?
regards1. backup the log frequently
2. Change DB to "Simple Recovery" Mode
Greg Jackson
PDX, Oregon|||You could try DBCC INDEXDEFRAG instead of DBREINDEX. Sometimes, it will
produce less log records. But the mileage does vary. Also, you can consider
bulk logged recovery mode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Vadim Rapp" <vr@.myrealbox.nospam.com> wrote in message
news:O%23k6hXGEEHA.2576@.TK2MSFTNGP11.phx.gbl...
> Hello:
> is there a way to reorg indexes without the log growing to the size of the
> whole db and more?
> regards
>

Reorg indexes & remove unused space - known problem??

Using the DB Maint Wizard, we set up maint jobs to reorg indexes and remove unused space. But the database files are allocated twice as large as the required data afterwards (i.e. allocated at 10G but only using 5G). My theory is that the shrink either
doesn't work or it is happening before the rebuild of the indexes. And the additional space is being allocated to be used temporarily to rebuild the indexes but not released back to the OS.
My question is: Is Microsoft doing these 2 things in the wrong order or does the remove unused space option simply not work when reindexing? And, more importantly, is this a known issue? If so, what number? How do they recommend we get around this?
Stephanie,
These two steps are scheduled independently. Just check the sequence for the two steps and you will
see what order they are scheduled. If you want to change that order, just change the schedule for
the jobs. Also, I don't recommend regular shrinking, see:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:E86B9392-3ACF-429D-842B-A91BF1F42787@.microsoft.com...
> Using the DB Maint Wizard, we set up maint jobs to reorg indexes and remove unused space. But the
database files are allocated twice as large as the required data afterwards (i.e. allocated at 10G
but only using 5G). My theory is that the shrink either doesn't work or it is happening before the
rebuild of the indexes. And the additional space is being allocated to be used temporarily to
rebuild the indexes but not released back to the OS.
> My question is: Is Microsoft doing these 2 things in the wrong order or does the remove unused
space option simply not work when reindexing? And, more importantly, is this a known issue? If so,
what number? How do they recommend we get around this?
|||Stephanie,
These two steps are scheduled independently. Just check the sequence for the two steps and you will
see what order they are scheduled. If you want to change that order, just change the schedule for
the jobs. Also, I don't recommend regular shrinking, see:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:E86B9392-3ACF-429D-842B-A91BF1F42787@.microsoft.com...
> Using the DB Maint Wizard, we set up maint jobs to reorg indexes and remove unused space. But the
database files are allocated twice as large as the required data afterwards (i.e. allocated at 10G
but only using 5G). My theory is that the shrink either doesn't work or it is happening before the
rebuild of the indexes. And the additional space is being allocated to be used temporarily to
rebuild the indexes but not released back to the OS.
> My question is: Is Microsoft doing these 2 things in the wrong order or does the remove unused
space option simply not work when reindexing? And, more importantly, is this a known issue? If so,
what number? How do they recommend we get around this?
|||Tibor - Thanks for the response. I started out with a database with multiple data files that I shrunk to 1% free because some of the data files were 5G. Then I ran the maint job. Afterwards, I looked in the file that was logged from the maintenance job
and the reindex is happening before the removal of unused space. But I now have a database with about 50% fill in the data files. The time on the removal of unused space is 1 second for each database when one of the databases is 13G. The removal of un
used space cannot possibly be working successfully. Could it be possible that SQL Server is unaware of the additional space being allocated during the rebuild when it does the remove?
"Tibor Karaszi" wrote:

> Stephanie,
> These two steps are scheduled independently. Just check the sequence for the two steps and you will
> see what order they are scheduled. If you want to change that order, just change the schedule for
> the jobs. Also, I don't recommend regular shrinking, see:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:E86B9392-3ACF-429D-842B-A91BF1F42787@.microsoft.com...
> database files are allocated twice as large as the required data afterwards (i.e. allocated at 10G
> but only using 5G). My theory is that the shrink either doesn't work or it is happening before the
> rebuild of the indexes. And the additional space is being allocated to be used temporarily to
> rebuild the indexes but not released back to the OS.
> space option simply not work when reindexing? And, more importantly, is this a known issue? If so,
> what number? How do they recommend we get around this?
>
>
|||Tibor - Thanks for the response. I started out with a database with multiple data files that I shrunk to 1% free because some of the data files were 5G. Then I ran the maint job. Afterwards, I looked in the file that was logged from the maintenance job
and the reindex is happening before the removal of unused space. But I now have a database with about 50% fill in the data files. The time on the removal of unused space is 1 second for each database when one of the databases is 13G. The removal of un
used space cannot possibly be working successfully. Could it be possible that SQL Server is unaware of the additional space being allocated during the rebuild when it does the remove?
"Tibor Karaszi" wrote:

> Stephanie,
> These two steps are scheduled independently. Just check the sequence for the two steps and you will
> see what order they are scheduled. If you want to change that order, just change the schedule for
> the jobs. Also, I don't recommend regular shrinking, see:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:E86B9392-3ACF-429D-842B-A91BF1F42787@.microsoft.com...
> database files are allocated twice as large as the required data afterwards (i.e. allocated at 10G
> but only using 5G). My theory is that the shrink either doesn't work or it is happening before the
> rebuild of the indexes. And the additional space is being allocated to be used temporarily to
> rebuild the indexes but not released back to the OS.
> space option simply not work when reindexing? And, more importantly, is this a known issue? If so,
> what number? How do they recommend we get around this?
>
>
|||I suspect you're doing a TRUNCATEONLY shrink which doesn't move any data but
just releases any unallocated space at the end of the data file back to the
OS.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:CB411573-0C42-4DE0-8AD7-CF1E1852C154@.microsoft.com...
> Tibor - Thanks for the response. I started out with a database with
multiple data files that I shrunk to 1% free because some of the data files
were 5G. Then I ran the maint job. Afterwards, I looked in the file that
was logged from the maintenance job and the reindex is happening before the
removal of unused space. But I now have a database with about 50% fill in
the data files. The time on the removal of unused space is 1 second for
each database when one of the databases is 13G. The removal of unused space
cannot possibly be working successfully. Could it be possible that SQL
Server is unaware of the additional space being allocated during the rebuild
when it does the remove?[vbcol=seagreen]
> "Tibor Karaszi" wrote:
the two steps and you will[vbcol=seagreen]
just change the schedule for[vbcol=seagreen]
remove unused space. But the[vbcol=seagreen]
afterwards (i.e. allocated at 10G[vbcol=seagreen]
it is happening before the[vbcol=seagreen]
be used temporarily to[vbcol=seagreen]
or does the remove unused[vbcol=seagreen]
this a known issue? If so,[vbcol=seagreen]
|||When I issued my shrink 3 days ago because I saw how large the data files were, I used DBCC SHRINKDATABASE ('<dbname>', 1) to leave 1% free space, which it did just fine leaving me with a 5G data file with about 50M available among other data files. Grea
t! Not a lot of wasted space. I'm happy.
Then, I used the Database Maintenance Wizard and scheduled a job that rebuilt the indexes and removed unused space. As you know, it uses xp_sqlmaint with '-RmUnusedSpace 50 10' as a parameter to remove the unused space. What command it is actually issui
ng, (SHRINKDATABASE with TRUNCATEONLY, etc.) I do not know. After this job ran, I was back to having a 10G data file with only 5G used. Again, I'm assuming it needed the space for the rebuild of the indexes. But the Database Maintenance Wizard-based 'r
emove unused space' parameter did not help. What suggestions do you have? I want to rebuild indexes but I do not want twice as much space taken up afterwards as I need.
"Paul S Randal [MS]" wrote:

> I suspect you're doing a TRUNCATEONLY shrink which doesn't move any data but
> just releases any unallocated space at the end of the data file back to the
> OS.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:CB411573-0C42-4DE0-8AD7-CF1E1852C154@.microsoft.com...
> multiple data files that I shrunk to 1% free because some of the data files
> were 5G. Then I ran the maint job. Afterwards, I looked in the file that
> was logged from the maintenance job and the reindex is happening before the
> removal of unused space. But I now have a database with about 50% fill in
> the data files. The time on the removal of unused space is 1 second for
> each database when one of the databases is 13G. The removal of unused space
> cannot possibly be working successfully. Could it be possible that SQL
> Server is unaware of the additional space being allocated during the rebuild
> when it does the remove?
> the two steps and you will
> just change the schedule for
> remove unused space. But the
> afterwards (i.e. allocated at 10G
> it is happening before the
> be used temporarily to
> or does the remove unused
> this a known issue? If so,
>
>
|||Reindexing on a as needed basis is not a problem. But it is recommended you
have free space in the database at least 1.2 times the size of the table and
indexes (for that table) you are reindexing. Any less than that and you will
most assuredly auto grow the database. If you are even close to that little
free you will most likely not end up with tables that are contiguous on
disk. If this is a strictly OLTP operation that may not be a problem. If
your doing range scans it may. See here:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
But why do you care how much free space is in the files? If it needs more
than that and doesn't have it the files must grow. This is an expensive
process. The shrinking is even more expensive. Both of these affect user
performance. The other problem with keeping a minimum amount of file size
is that you may (or someone else) use that free space on the disk for
something else. Then when you do need it (for say a reindex) it won't be
there and your stuck. There is no penalty for having too much free space
but there is a big one for not enough. I am not saying you need twice the
amount but it has to be enough so that normal activities such as reindexing
and data loads do not autogrow the files. Also remember that SQL Server
inserts new data into files using a proportional fill algorythm that is
based on the amount of free space in the files. So if you have multiple
files in the filegroup and they are of different sizes or different % full
you may not get peak performance on writes or reads as you might if they
were spread evenly across the files.
One more comment. If you are going to shrink you should use DBCC SHRINKFILE
and not SHRINKDATABASE. The latter works on both the log and data files and
you don't have much say in how it breaks it all up.
Andrew J. Kelly SQL MVP
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:23D1318B-9F67-49A6-BD4C-5387D2468ACA@.microsoft.com...
> The bottom line is that I need to reindex on an as needed basis. I also
do not want more than 10% space available in these large data files. Can
someone PLEASE give me some specific positive feedback on best practices for
achieving this goal? I would really appreciate it.[vbcol=seagreen]
> "Andrew J. Kelly" wrote:
remove[vbcol=seagreen]
the[vbcol=seagreen]
what[vbcol=seagreen]
there[vbcol=seagreen]
files[vbcol=seagreen]
I'm[vbcol=seagreen]
unused[vbcol=seagreen]
having[vbcol=seagreen]
space[vbcol=seagreen]
Wizard-based[vbcol=seagreen]
have?[vbcol=seagreen]
data[vbcol=seagreen]
to[vbcol=seagreen]
with[vbcol=seagreen]
data[vbcol=seagreen]
file[vbcol=seagreen]
before[vbcol=seagreen]
fill[vbcol=seagreen]
for[vbcol=seagreen]
unused[vbcol=seagreen]
SQL[vbcol=seagreen]
sequence[vbcol=seagreen]
order,[vbcol=seagreen]
message[vbcol=seagreen]
indexes[vbcol=seagreen]
allocated[vbcol=seagreen]
|||Okay, all good points. Thank you for taking the time to answer. Let's say I need to come up with a single automated maintenance plan for multpile environments, multiple servers and multiple OLTP databases that's easy to implement. I need to incorporate
reindexing on a regular basis. I also need to keep the database and log files at a good capacity (i.e. 20% free space). I was going to use a single Database Maintenance Plan but that doesn't seem to be working for me since the free space after a dbcc r
eindex is close to 50%. What would you suggest?
Right now I have 3 DB Maint Plan jobs running on all databases: one to check db integrity on Saturday evening, one to rebuild the indexes early on Sunday, and one to remove unused space (mostly from the reindex) keeping 20% that runs Sunday thru Thursday
nights.
My concern is that people restore databases to some of the development servers that have 50% free space. We don't have room for multiple copies of these databases so I wanted to perform some shrinks on a regular basis, 20% is better.
Could you please give me some suggestions for improvement of this design?
"Andrew J. Kelly" wrote:

> Reindexing on a as needed basis is not a problem. But it is recommended you
> have free space in the database at least 1.2 times the size of the table and
> indexes (for that table) you are reindexing. Any less than that and you will
> most assuredly auto grow the database. If you are even close to that little
> free you will most likely not end up with tables that are contiguous on
> disk. If this is a strictly OLTP operation that may not be a problem. If
> your doing range scans it may. See here:
> http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
> But why do you care how much free space is in the files? If it needs more
> than that and doesn't have it the files must grow. This is an expensive
> process. The shrinking is even more expensive. Both of these affect user
> performance. The other problem with keeping a minimum amount of file size
> is that you may (or someone else) use that free space on the disk for
> something else. Then when you do need it (for say a reindex) it won't be
> there and your stuck. There is no penalty for having too much free space
> but there is a big one for not enough. I am not saying you need twice the
> amount but it has to be enough so that normal activities such as reindexing
> and data loads do not autogrow the files. Also remember that SQL Server
> inserts new data into files using a proportional fill algorythm that is
> based on the amount of free space in the files. So if you have multiple
> files in the filegroup and they are of different sizes or different % full
> you may not get peak performance on writes or reads as you might if they
> were spread evenly across the files.
> One more comment. If you are going to shrink you should use DBCC SHRINKFILE
> and not SHRINKDATABASE. The latter works on both the log and data files and
> you don't have much say in how it breaks it all up.
> --
> Andrew J. Kelly SQL MVP
>
> "Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
> news:23D1318B-9F67-49A6-BD4C-5387D2468ACA@.microsoft.com...
> do not want more than 10% space available in these large data files. Can
> someone PLEASE give me some specific positive feedback on best practices for
> achieving this goal? I would really appreciate it.
> remove
> the
> what
> there
> files
> I'm
> unused
> having
> space
> Wizard-based
> have?
> data
> to
> with
> data
> file
> before
> fill
> for
> unused
> SQL
> sequence
> order,
> message
> indexes
> allocated
>
>
|||Stephanie,
I don't recommend using a maintenance plan created via the wizard. I
recommend you create your own scheduled jobs and that way you can customize
it any way you see fit. The wizard uses SHRINKDATABASE and you should be
using SHRINK FILE so you can control it better. That way you can shrink the
log separate from the data files. But if it keeps giving you 50% free space
after a reindex then the amount of free space before the reindex is too
small. Part of this is probably due to the fact it grows multiple times and
you probably have the autogrow set to 10%. If these are large files then
maybe setting a size in MB is better. 10% of 50GB is a 5GB growth. But it
all boils down to not having enough before you start the process. Even if
you manually grew the files before you started the reindex the shrinking
process will destroy a lot of what you just accomplished. It's almost a
wasted effort. Maybe you can try DBCC INDEXDEFRAG instead. This may cause
more logging to the tran log but might keep the data files smaller. It's
MUCH easier to shrink the log file than the data file. Bottom line is that
your impacting the performance of a production server to accommodate
development servers. It's better to get more disk space on the devel
servers. Even if you get enough room to restore the a copy of the
production db and then shrink it on the devel server would be better than
the current way if possible.
Andrew J. Kelly SQL MVP
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:51F36354-285B-4E71-BAC7-E4BD44559CED@.microsoft.com...
> Okay, all good points. Thank you for taking the time to answer. Let's
say I need to come up with a single automated maintenance plan for multpile
environments, multiple servers and multiple OLTP databases that's easy to
implement. I need to incorporate reindexing on a regular basis. I also
need to keep the database and log files at a good capacity (i.e. 20% free
space). I was going to use a single Database Maintenance Plan but that
doesn't seem to be working for me since the free space after a dbcc reindex
is close to 50%. What would you suggest?
> Right now I have 3 DB Maint Plan jobs running on all databases: one to
check db integrity on Saturday evening, one to rebuild the indexes early on
Sunday, and one to remove unused space (mostly from the reindex) keeping 20%
that runs Sunday thru Thursday nights.
> My concern is that people restore databases to some of the development
servers that have 50% free space. We don't have room for multiple copies of
these databases so I wanted to perform some shrinks on a regular basis, 20%
is better.[vbcol=seagreen]
> Could you please give me some suggestions for improvement of this design?
> "Andrew J. Kelly" wrote:
you[vbcol=seagreen]
and[vbcol=seagreen]
will[vbcol=seagreen]
little[vbcol=seagreen]
If[vbcol=seagreen]
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx[vbcol=seagreen]
more[vbcol=seagreen]
user[vbcol=seagreen]
size[vbcol=seagreen]
be[vbcol=seagreen]
space[vbcol=seagreen]
the[vbcol=seagreen]
reindexing[vbcol=seagreen]
Server[vbcol=seagreen]
full[vbcol=seagreen]
SHRINKFILE[vbcol=seagreen]
and[vbcol=seagreen]
also[vbcol=seagreen]
Can[vbcol=seagreen]
for[vbcol=seagreen]
the[vbcol=seagreen]
grow[vbcol=seagreen]
at[vbcol=seagreen]
system[vbcol=seagreen]
data[vbcol=seagreen]
space,[vbcol=seagreen]
space.[vbcol=seagreen]
that[vbcol=seagreen]
to[vbcol=seagreen]
the[vbcol=seagreen]
you[vbcol=seagreen]
taken up[vbcol=seagreen]
any[vbcol=seagreen]
back[vbcol=seagreen]
no[vbcol=seagreen]
message[vbcol=seagreen]
database[vbcol=seagreen]
50%[vbcol=seagreen]
second[vbcol=seagreen]
that[vbcol=seagreen]
the[vbcol=seagreen]
that[vbcol=seagreen]
data[vbcol=seagreen]
doesn't[vbcol=seagreen]
wrong[vbcol=seagreen]

Saturday, February 25, 2012

reorganize data files

Reorganize data and index pages option

Reorganize data and index pages

Reorganize data and index pages

reorganization percent -- ?

reorganization percent -- ?

reorganization percent -- ?

Reorganization and logs

Re-Org/Compress?

Re-Org/Compress?

Re-Org/Compress?

Reorg specific table?

Reorg specific table?

Reorg specific table?

Reorg select tables

Reorg select tables

reorg PK clustered index in VLDB

Reorg indexes w/o log growing - how?

Reorg indexes & remove unused space - known problem??

Rendering report

Blog Archive

About Me