Hi All,
I have a DB of transaction log grown to 19GB. I have some questions.
1. Is there any way that we can minimize the entries to transaction log.
2. If i use Transactions in my stored procedure will it be usefull to automatically remove the transaction entries in transaction log once after the commit transaction is given.
3. Is there a way to reduce my current transaction log and not to grown in such a manner futher.
Thanks in Advance
Sri1. No. Every DML and DDL operation is logged.
2. Again, everthing is logged, whether you use explicit transactions (BEGIN TRAN... COMMIT TRAN) or implicit transactions.
3. Yes. You can back up the database on scheduled intervals. This will not shrink the physical file, but it will mark "old" backed up virtual log files as reusable, which allows SQL Server to reuse them insted of expanding the physical log file.
You could also set the recovery model to "Simple". This will allow SQL Server to reuse virtual log files once all transactions are committed/rolled back and the data in the data pages referred to by all transactions are written to disk.
Take a look at "transaction logs [SQL Server], architecture" to better understand what transaction logs are and how SQL Server uses them.
I have just given you a very simplified summary on how it works.
Showing posts with label minimize. Show all posts
Showing posts with label minimize. Show all posts
Wednesday, March 21, 2012
Minimizing Fragmentation of table having Non-clustered Index.
Hi All,
i am working on SQL Server 2000 EE. I want to know that, is there any
way to rebuild Non clustered index. How can we minimize fragmentation
of table having non clustered index. can we defrag non clustred index.
Thanks & Regards,
Sajid C.Sure, you can defrag the indexes themselves, but (someone correct me if
I'm wrong here), you can't defrag the TABLE data with a single statement
(like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
One way to do it would be to create and then drop a clustered index on
the table. That would effectively defragment the table's underlying
data. BUT... If you're doing a lot of scanning on the table (to the
extent that it really, really matters how the data is physically
ordered) you might want to consider putting a clustered index on the
table anyway...
-Dave
csajid@.gmail.com wrote:
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index.
You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
index using DBCC DBREINDEX. These apply to both clustered and non-clustered
indexes. See the Books Online for usage information.
> How can we minimize fragmentation
> of table having non clustered index.
You can avoid page splits between between index reorgs by specifying a lower
FILLFACTOR. The downside is that it reduces the page density, which can
negatively affect scan performance and buffer efficiency. In my opinion,
it's usually best to use the default FILLFACTOR so that splits allocate free
space when and where needed.
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||On Mar 10, 8:59 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the enti
re
> index using DBCC DBREINDEX. These apply to both clustered and non-cluster
ed
> indexes. See the Books Online for usage information.
>
> You can avoid page splits between between index reorgs by specifying a low
er
> FILLFACTOR. The downside is that it reduces the page density, which can
> negatively affect scan performance and buffer efficiency. In my opinion,
> it's usually best to use the default FILLFACTOR so that splits allocate fr
ee
> space when and where needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csa...@.gmail.com> wrote in message
> news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
>
>
>
>
> - Show quoted text -
Dear All,
Thanks for your reply.
Thanks & Regards,
Sajid C.|||(With all due respect) you're wrong - you can use DBCC INDEXDEFRAG or DBCC
DBREINDEX on the clustered index, which will remove fragmentation from the
table data. As far as INDEXDEFRAG (which I wrote) is concerned, there's no
difference between a clustered or non-clustered index. DBREINDEX only
differs in the locking it takes for the two types of index.
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"David Markle" <newsdm@.markleconsulting.c0m> wrote in message
news:%23F4gZ1yYHHA.2320@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Sure, you can defrag the indexes themselves, but (someone correct me if
> I'm wrong here), you can't defrag the TABLE data with a single statement
> (like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
> One way to do it would be to create and then drop a clustered index on the
> table. That would effectively defragment the table's underlying data.
> BUT... If you're doing a lot of scanning on the table (to the extent that
> it really, really matters how the data is physically ordered) you might
> want to consider putting a clustered index on the table anyway...
> -Dave
> csajid@.gmail.com wrote:
i am working on SQL Server 2000 EE. I want to know that, is there any
way to rebuild Non clustered index. How can we minimize fragmentation
of table having non clustered index. can we defrag non clustred index.
Thanks & Regards,
Sajid C.Sure, you can defrag the indexes themselves, but (someone correct me if
I'm wrong here), you can't defrag the TABLE data with a single statement
(like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
One way to do it would be to create and then drop a clustered index on
the table. That would effectively defragment the table's underlying
data. BUT... If you're doing a lot of scanning on the table (to the
extent that it really, really matters how the data is physically
ordered) you might want to consider putting a clustered index on the
table anyway...
-Dave
csajid@.gmail.com wrote:
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index.
You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
index using DBCC DBREINDEX. These apply to both clustered and non-clustered
indexes. See the Books Online for usage information.
> How can we minimize fragmentation
> of table having non clustered index.
You can avoid page splits between between index reorgs by specifying a lower
FILLFACTOR. The downside is that it reduces the page density, which can
negatively affect scan performance and buffer efficiency. In my opinion,
it's usually best to use the default FILLFACTOR so that splits allocate free
space when and where needed.
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||On Mar 10, 8:59 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the enti
re
> index using DBCC DBREINDEX. These apply to both clustered and non-cluster
ed
> indexes. See the Books Online for usage information.
>
> You can avoid page splits between between index reorgs by specifying a low
er
> FILLFACTOR. The downside is that it reduces the page density, which can
> negatively affect scan performance and buffer efficiency. In my opinion,
> it's usually best to use the default FILLFACTOR so that splits allocate fr
ee
> space when and where needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csa...@.gmail.com> wrote in message
> news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
>
>
>
>
> - Show quoted text -
Dear All,
Thanks for your reply.
Thanks & Regards,
Sajid C.|||(With all due respect) you're wrong - you can use DBCC INDEXDEFRAG or DBCC
DBREINDEX on the clustered index, which will remove fragmentation from the
table data. As far as INDEXDEFRAG (which I wrote) is concerned, there's no
difference between a clustered or non-clustered index. DBREINDEX only
differs in the locking it takes for the two types of index.
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
"David Markle" <newsdm@.markleconsulting.c0m> wrote in message
news:%23F4gZ1yYHHA.2320@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Sure, you can defrag the indexes themselves, but (someone correct me if
> I'm wrong here), you can't defrag the TABLE data with a single statement
> (like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
> One way to do it would be to create and then drop a clustered index on the
> table. That would effectively defragment the table's underlying data.
> BUT... If you're doing a lot of scanning on the table (to the extent that
> it really, really matters how the data is physically ordered) you might
> want to consider putting a clustered index on the table anyway...
> -Dave
> csajid@.gmail.com wrote:
Minimizing Fragmentation of table having Non-clustered Index.
Hi All,
i am working on SQL Server 2000 EE. I want to know that, is there any
way to rebuild Non clustered index. How can we minimize fragmentation
of table having non clustered index. can we defrag non clustred index.
Thanks & Regards,
Sajid C.
Sure, you can defrag the indexes themselves, but (someone correct me if
I'm wrong here), you can't defrag the TABLE data with a single statement
(like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
One way to do it would be to create and then drop a clustered index on
the table. That would effectively defragment the table's underlying
data. BUT... If you're doing a lot of scanning on the table (to the
extent that it really, really matters how the data is physically
ordered) you might want to consider putting a clustered index on the
table anyway...
-Dave
csajid@.gmail.com wrote:
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>
|||> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index.
You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
index using DBCC DBREINDEX. These apply to both clustered and non-clustered
indexes. See the Books Online for usage information.
> How can we minimize fragmentation
> of table having non clustered index.
You can avoid page splits between between index reorgs by specifying a lower
FILLFACTOR. The downside is that it reduces the page density, which can
negatively affect scan performance and buffer efficiency. In my opinion,
it's usually best to use the default FILLFACTOR so that splits allocate free
space when and where needed.
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1173540813.502373.320030@.j27g2000cwj.googlegr oups.com...
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>
|||On Mar 10, 8:59 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
> index using DBCC DBREINDEX. These apply to both clustered and non-clustered
> indexes. See the Books Online for usage information.
>
> You can avoid page splits between between index reorgs by specifying a lower
> FILLFACTOR. The downside is that it reduces the page density, which can
> negatively affect scan performance and buffer efficiency. In my opinion,
> it's usually best to use the default FILLFACTOR so that splits allocate free
> space when and where needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csa...@.gmail.com> wrote in message
> news:1173540813.502373.320030@.j27g2000cwj.googlegr oups.com...
>
>
>
> - Show quoted text -
Dear All,
Thanks for your reply.
Thanks & Regards,
Sajid C.
|||(With all due respect) you're wrong - you can use DBCC INDEXDEFRAG or DBCC
DBREINDEX on the clustered index, which will remove fragmentation from the
table data. As far as INDEXDEFRAG (which I wrote) is concerned, there's no
difference between a clustered or non-clustered index. DBREINDEX only
differs in the locking it takes for the two types of index.
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"David Markle" <newsdm@.markleconsulting.c0m> wrote in message
news:%23F4gZ1yYHHA.2320@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Sure, you can defrag the indexes themselves, but (someone correct me if
> I'm wrong here), you can't defrag the TABLE data with a single statement
> (like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
> One way to do it would be to create and then drop a clustered index on the
> table. That would effectively defragment the table's underlying data.
> BUT... If you're doing a lot of scanning on the table (to the extent that
> it really, really matters how the data is physically ordered) you might
> want to consider putting a clustered index on the table anyway...
> -Dave
> csajid@.gmail.com wrote:
i am working on SQL Server 2000 EE. I want to know that, is there any
way to rebuild Non clustered index. How can we minimize fragmentation
of table having non clustered index. can we defrag non clustred index.
Thanks & Regards,
Sajid C.
Sure, you can defrag the indexes themselves, but (someone correct me if
I'm wrong here), you can't defrag the TABLE data with a single statement
(like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
One way to do it would be to create and then drop a clustered index on
the table. That would effectively defragment the table's underlying
data. BUT... If you're doing a lot of scanning on the table (to the
extent that it really, really matters how the data is physically
ordered) you might want to consider putting a clustered index on the
table anyway...
-Dave
csajid@.gmail.com wrote:
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>
|||> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index.
You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
index using DBCC DBREINDEX. These apply to both clustered and non-clustered
indexes. See the Books Online for usage information.
> How can we minimize fragmentation
> of table having non clustered index.
You can avoid page splits between between index reorgs by specifying a lower
FILLFACTOR. The downside is that it reduces the page density, which can
negatively affect scan performance and buffer efficiency. In my opinion,
it's usually best to use the default FILLFACTOR so that splits allocate free
space when and where needed.
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1173540813.502373.320030@.j27g2000cwj.googlegr oups.com...
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>
|||On Mar 10, 8:59 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
> index using DBCC DBREINDEX. These apply to both clustered and non-clustered
> indexes. See the Books Online for usage information.
>
> You can avoid page splits between between index reorgs by specifying a lower
> FILLFACTOR. The downside is that it reduces the page density, which can
> negatively affect scan performance and buffer efficiency. In my opinion,
> it's usually best to use the default FILLFACTOR so that splits allocate free
> space when and where needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csa...@.gmail.com> wrote in message
> news:1173540813.502373.320030@.j27g2000cwj.googlegr oups.com...
>
>
>
> - Show quoted text -
Dear All,
Thanks for your reply.
Thanks & Regards,
Sajid C.
|||(With all due respect) you're wrong - you can use DBCC INDEXDEFRAG or DBCC
DBREINDEX on the clustered index, which will remove fragmentation from the
table data. As far as INDEXDEFRAG (which I wrote) is concerned, there's no
difference between a clustered or non-clustered index. DBREINDEX only
differs in the locking it takes for the two types of index.
Thanks
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"David Markle" <newsdm@.markleconsulting.c0m> wrote in message
news:%23F4gZ1yYHHA.2320@.TK2MSFTNGP03.phx.gbl...[vbcol=seagreen]
> Sure, you can defrag the indexes themselves, but (someone correct me if
> I'm wrong here), you can't defrag the TABLE data with a single statement
> (like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
> One way to do it would be to create and then drop a clustered index on the
> table. That would effectively defragment the table's underlying data.
> BUT... If you're doing a lot of scanning on the table (to the extent that
> it really, really matters how the data is physically ordered) you might
> want to consider putting a clustered index on the table anyway...
> -Dave
> csajid@.gmail.com wrote:
Minimizing Fragmentation of table having Non-clustered Index.
Hi All,
i am working on SQL Server 2000 EE. I want to know that, is there any
way to rebuild Non clustered index. How can we minimize fragmentation
of table having non clustered index. can we defrag non clustred index.
Thanks & Regards,
Sajid C.Sure, you can defrag the indexes themselves, but (someone correct me if
I'm wrong here), you can't defrag the TABLE data with a single statement
(like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
One way to do it would be to create and then drop a clustered index on
the table. That would effectively defragment the table's underlying
data. BUT... If you're doing a lot of scanning on the table (to the
extent that it really, really matters how the data is physically
ordered) you might want to consider putting a clustered index on the
table anyway...
-Dave
csajid@.gmail.com wrote:
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index.
You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
index using DBCC DBREINDEX. These apply to both clustered and non-clustered
indexes. See the Books Online for usage information.
> How can we minimize fragmentation
> of table having non clustered index.
You can avoid page splits between between index reorgs by specifying a lower
FILLFACTOR. The downside is that it reduces the page density, which can
negatively affect scan performance and buffer efficiency. In my opinion,
it's usually best to use the default FILLFACTOR so that splits allocate free
space when and where needed.
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||On Mar 10, 8:59 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> > i am working on SQL Server 2000 EE. I want to know that, is there any
> > way to rebuild Non clustered index.
> You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
> index using DBCC DBREINDEX. These apply to both clustered and non-clustered
> indexes. See the Books Online for usage information.
> > How can we minimize fragmentation
> > of table having non clustered index.
> You can avoid page splits between between index reorgs by specifying a lower
> FILLFACTOR. The downside is that it reduces the page density, which can
> negatively affect scan performance and buffer efficiency. In my opinion,
> it's usually best to use the default FILLFACTOR so that splits allocate free
> space when and where needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csa...@.gmail.com> wrote in message
> news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
>
> > Hi All,
> > i am working on SQL Server 2000 EE. I want to know that, is there any
> > way to rebuild Non clustered index. How can we minimize fragmentation
> > of table having non clustered index. can we defrag non clustred index.
> > Thanks & Regards,
> > Sajid C.- Hide quoted text -
> - Show quoted text -
Dear All,
Thanks for your reply.
Thanks & Regards,
Sajid C.|||(With all due respect) you're wrong - you can use DBCC INDEXDEFRAG or DBCC
DBREINDEX on the clustered index, which will remove fragmentation from the
table data. As far as INDEXDEFRAG (which I wrote) is concerned, there's no
difference between a clustered or non-clustered index. DBREINDEX only
differs in the locking it takes for the two types of index.
Thanks
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"David Markle" <newsdm@.markleconsulting.c0m> wrote in message
news:%23F4gZ1yYHHA.2320@.TK2MSFTNGP03.phx.gbl...
> Sure, you can defrag the indexes themselves, but (someone correct me if
> I'm wrong here), you can't defrag the TABLE data with a single statement
> (like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
> One way to do it would be to create and then drop a clustered index on the
> table. That would effectively defragment the table's underlying data.
> BUT... If you're doing a lot of scanning on the table (to the extent that
> it really, really matters how the data is physically ordered) you might
> want to consider putting a clustered index on the table anyway...
> -Dave
> csajid@.gmail.com wrote:
>> Hi All,
>> i am working on SQL Server 2000 EE. I want to know that, is there any
>> way to rebuild Non clustered index. How can we minimize fragmentation
>> of table having non clustered index. can we defrag non clustred index.
>>
>> Thanks & Regards,
>> Sajid C.
i am working on SQL Server 2000 EE. I want to know that, is there any
way to rebuild Non clustered index. How can we minimize fragmentation
of table having non clustered index. can we defrag non clustred index.
Thanks & Regards,
Sajid C.Sure, you can defrag the indexes themselves, but (someone correct me if
I'm wrong here), you can't defrag the TABLE data with a single statement
(like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
One way to do it would be to create and then drop a clustered index on
the table. That would effectively defragment the table's underlying
data. BUT... If you're doing a lot of scanning on the table (to the
extent that it really, really matters how the data is physically
ordered) you might want to consider putting a clustered index on the
table anyway...
-Dave
csajid@.gmail.com wrote:
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index.
You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
index using DBCC DBREINDEX. These apply to both clustered and non-clustered
indexes. See the Books Online for usage information.
> How can we minimize fragmentation
> of table having non clustered index.
You can avoid page splits between between index reorgs by specifying a lower
FILLFACTOR. The downside is that it reduces the page density, which can
negatively affect scan performance and buffer efficiency. In my opinion,
it's usually best to use the default FILLFACTOR so that splits allocate free
space when and where needed.
Hope this helps.
Dan Guzman
SQL Server MVP
<csajid@.gmail.com> wrote in message
news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
> Hi All,
> i am working on SQL Server 2000 EE. I want to know that, is there any
> way to rebuild Non clustered index. How can we minimize fragmentation
> of table having non clustered index. can we defrag non clustred index.
>
> Thanks & Regards,
> Sajid C.
>|||On Mar 10, 8:59 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> > i am working on SQL Server 2000 EE. I want to know that, is there any
> > way to rebuild Non clustered index.
> You can defrag index leaf nodes using DBCC INDEXDEFRAG or rebuild the entire
> index using DBCC DBREINDEX. These apply to both clustered and non-clustered
> indexes. See the Books Online for usage information.
> > How can we minimize fragmentation
> > of table having non clustered index.
> You can avoid page splits between between index reorgs by specifying a lower
> FILLFACTOR. The downside is that it reduces the page density, which can
> negatively affect scan performance and buffer efficiency. In my opinion,
> it's usually best to use the default FILLFACTOR so that splits allocate free
> space when and where needed.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <csa...@.gmail.com> wrote in message
> news:1173540813.502373.320030@.j27g2000cwj.googlegroups.com...
>
> > Hi All,
> > i am working on SQL Server 2000 EE. I want to know that, is there any
> > way to rebuild Non clustered index. How can we minimize fragmentation
> > of table having non clustered index. can we defrag non clustred index.
> > Thanks & Regards,
> > Sajid C.- Hide quoted text -
> - Show quoted text -
Dear All,
Thanks for your reply.
Thanks & Regards,
Sajid C.|||(With all due respect) you're wrong - you can use DBCC INDEXDEFRAG or DBCC
DBREINDEX on the clustered index, which will remove fragmentation from the
table data. As far as INDEXDEFRAG (which I wrote) is concerned, there's no
difference between a clustered or non-clustered index. DBREINDEX only
differs in the locking it takes for the two types of index.
Thanks
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"David Markle" <newsdm@.markleconsulting.c0m> wrote in message
news:%23F4gZ1yYHHA.2320@.TK2MSFTNGP03.phx.gbl...
> Sure, you can defrag the indexes themselves, but (someone correct me if
> I'm wrong here), you can't defrag the TABLE data with a single statement
> (like DBCC DBREINDEX OR DBCC INDEXDEFRAG).
> One way to do it would be to create and then drop a clustered index on the
> table. That would effectively defragment the table's underlying data.
> BUT... If you're doing a lot of scanning on the table (to the extent that
> it really, really matters how the data is physically ordered) you might
> want to consider putting a clustered index on the table anyway...
> -Dave
> csajid@.gmail.com wrote:
>> Hi All,
>> i am working on SQL Server 2000 EE. I want to know that, is there any
>> way to rebuild Non clustered index. How can we minimize fragmentation
>> of table having non clustered index. can we defrag non clustred index.
>>
>> Thanks & Regards,
>> Sajid C.
minimize width
hi there,I have in my report header
text1
text2
text3
one of this text can be blank at run time I need to minimize the width of the section to the width of the textboxes thet are not blank (like supress when empty of the section) can anyone tell me how can I do it...Put a text box in the header, and then drag each field into the text box.|||But in this case the section will still have the same width,I need to minimize its width to the width of the non-empty text boxes..|||Sorry,my ques shoud be about the height and not the width of a section...Sorrysql
text1
text2
text3
one of this text can be blank at run time I need to minimize the width of the section to the width of the textboxes thet are not blank (like supress when empty of the section) can anyone tell me how can I do it...Put a text box in the header, and then drag each field into the text box.|||But in this case the section will still have the same width,I need to minimize its width to the width of the non-empty text boxes..|||Sorry,my ques shoud be about the height and not the width of a section...Sorrysql
minimize transaction logging
We use SQL PE 2000 in our lab to store sampled data (lots of data collected at high sampling rate) in real-time. i know this may be an uncommon use of SQL server. For this particular application, performance is much more important than data recoverability. In other words, once-in-a-while data loss is acceptable. Actually we have never used the transaction log for recovery since we started using the SQL PE in this way a few years ago. Certain transaction logging such as bulk deletion is particularly wasteful of the resource. As my understanding from reading the online book and some threads on this forum, the best thing we can do is using bulk-logged recovery model. I am not sure if that helps us at all because it seems that bulk deletion (i.e. DELETE FROM MyTable WHERE ..., may affect hundreds of thousands of records) is not on the list of minimally logged transactions.
I am wondering if anyone could share some good advice.
Thanks in advance!
Correct Deletes (or any normal DML command) are fully logged transactions. There is no way to disable this behavor in Microsoft SQL Server.
In order to reduce logging when deleting large amounts of data do the deletes in smaller batches. Something like this should work.
Code Snippet
set rowcount 1000
select count(*) from sysdatabases --This is just to get the loop started
while @.@.ROWCOUNT <> 0
BEGIN
delete from table
where something = somethingelse
END
This will do small deletes of 1000 records at a time which while still logged will be done in small batches so with the log in simple recovery mode shouldn't get very large.
Doing deletes in this method will take longer than a single large delete, but you don't have to worry about a large transaction log.
|||Denny, thanks for clarifying this.
I am wondering if reducing the transactio log file size helps the performance. My understanding is that the same amount of logging still takes place. We are not concerned about storage space that we have plenty. We truncate the log file periodically.
|||Reducing the log file size shouldn't effect performance (unless you shrink the file to small and SQL has to grow the file often). It is recommended to pre-allocate the file size so that SQL Server doesn't ever have to grow the file.Minimize (not hide) Parameters
Hello,
I am wondering if RS has the capability to minimize the parameter portion on the top. I see that parameter can be hidden, but I would need the capability to just minimize and if the user needs to change the parameter, they can still click on a button to un-minimize.
Does that make sense?
-Lawrence
If by RS you mean the Report Manager, the toolbar cannot be customized. If you are report-enabling a custom application the ReportViewer controls support customizing the toolbar.
|||If you are accessing your reports over the web, then if you use URL access to the report server you have the capability of launching a report with the parameters area collapsed. You do this by adding rc
arameters=collapsed to the URL query string.
So your URL might look like:
http://localhost/reportserver/?%2freport+directory%2freport+name&rs:Command=Render&rcarameters=collapsed
This is exactly what I was looking for.
Thanks a bunch!
Minimize (not hide) Parameters
Hello,
I am wondering if RS has the capability to minimize the parameter portion on the top. I see that parameter can be hidden, but I would need the capability to just minimize and if the user needs to change the parameter, they can still click on a button to un-minimize.
Does that make sense?
-Lawrence
If by RS you mean the Report Manager, the toolbar cannot be customized. If you are report-enabling a custom application the ReportViewer controls support customizing the toolbar.
|||If you are accessing your reports over the web, then if you use URL access to the report server you have the capability of launching a report with the parameters area collapsed. You do this by adding rcarameters=collapsed to the URL query string.
So your URL might look like:
http://localhost/reportserver/?%2freport+directory%2freport+name&rs:Command=Render&rcarameters=collapsed
This is exactly what I was looking for.
Thanks a bunch!
Subscribe to:
Posts (Atom)