Hi,
Is there a way to automate a process that export the database strucuture once a day !
All the objects - Tables, Indexes, Procedures, Views Etc..
Any Help I apreciate !
Thank's
You could run a sql agent job that uses SQL-DMO to script your database.
Here is an article that I wrote that might help:
http://www.dbazine.com/larsen4.shtml
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Carrasco" <Carrasco@.discussions.microsoft.com> wrote in message
news:5964A3E3-E149-4E4D-812D-B6B62FE9C878@.microsoft.com...
> Hi,
> Is there a way to automate a process that export the database strucuture
once a day !
> All the objects - Tables, Indexes, Procedures, Views Etc..
> Any Help I apreciate !
> Thank's
>
sql
Showing posts with label indexes. Show all posts
Showing posts with label indexes. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Database snapshot
I drop and recreate a database snapshot for reporting purposes at the end of a DW loading process.
I need to create some indexes to improve query performance.
Where I should create the index, on the originale db or on the snapshot db ?
Cosimo
If I remember from an article, you can not do this on a snapshot database. So I guess you should do this on the source DB. Please check it though.|||SOLVED
I create the index on the source db.
Sunday, March 25, 2012
Database size vs total table and index size
I use SQL Server 2000.
My database is 1912.69 MB with no available free space.
My logfile is 1 MB.
The size of all tables and indexes add up to 200 MB.
I have no diagrams, two views, fifty stored procedures, six users, ten
roles, no rules, no defaults, no user defined data types, no user
defined functions.
Autoshrink is set to true.
My question:
How can the database be almost 2 GB when the tables and indexes add up
to only 200MB?
When I try to shrink manually in SQL Enterprise Manager I get no error
message, but no shrinking occurs.
I am grateful for any help.
Regards,
Jan Nordgreen
Reply"damezumari" wrote:
> I use SQL Server 2000.
> My database is 1912.69 MB with no available free space.
> My logfile is 1 MB.
> The size of all tables and indexes add up to 200 MB.
> I have no diagrams, two views, fifty stored procedures, six users, ten
> roles, no rules, no defaults, no user defined data types, no user
> defined functions.
> Autoshrink is set to true.
> My question:
> How can the database be almost 2 GB when the tables and indexes add up
> to only 200MB?
> When I try to shrink manually in SQL Enterprise Manager I get no error
> message, but no shrinking occurs.
> I am grateful for any help.
> Regards,
> Jan Nordgreen
This sounds VERY unusual: database sizes that are 10 times bigger than the
actual datasize are absolutely normal and there's a lot of reasons for that
-
but they usually have something to do with the TA-Log. Please verify that
your transaction log file is really that tiny, and publish the syntax of you
r
shrink statement ...|||First, I don=B4t think that this is TA related, because your TA size is
1MB which is really , really small.
Your issue could be based on several things like:
You can=B4t shrink the database size under the initial size, so if the
initial size was 2GB (which isn=B4t unusal and not that big) you can=B4t
shrink it with DBCC Shrinkdatabase. Look in the BOL for more
information:
"The target size for data and log files as calculated by DBCC
SHRINKDATABASE can never be smaller than the minimum size of a file.
The minimum size of a file is the size specified when the file was
originally created, or the last explicit size set with a file size
changing operation, such as DBCC SHRINKFILE."
You can shrink the database using DBCC Shrinkfile, where you can
specify a new size of a single file. Look in the BOL for more
information.
Anyway, shrinking your file to a smaller size than 2GB could decrease
performance if your database is growing and gaining automatically new
space. So shrinking the database to 200MB would cause a halt if the
size has to be extended, causing waiting processes to be stopped until
the new size is aquired from the OS:
HTH, Jens Suessmeyer.
My database is 1912.69 MB with no available free space.
My logfile is 1 MB.
The size of all tables and indexes add up to 200 MB.
I have no diagrams, two views, fifty stored procedures, six users, ten
roles, no rules, no defaults, no user defined data types, no user
defined functions.
Autoshrink is set to true.
My question:
How can the database be almost 2 GB when the tables and indexes add up
to only 200MB?
When I try to shrink manually in SQL Enterprise Manager I get no error
message, but no shrinking occurs.
I am grateful for any help.
Regards,
Jan Nordgreen
Reply"damezumari" wrote:
> I use SQL Server 2000.
> My database is 1912.69 MB with no available free space.
> My logfile is 1 MB.
> The size of all tables and indexes add up to 200 MB.
> I have no diagrams, two views, fifty stored procedures, six users, ten
> roles, no rules, no defaults, no user defined data types, no user
> defined functions.
> Autoshrink is set to true.
> My question:
> How can the database be almost 2 GB when the tables and indexes add up
> to only 200MB?
> When I try to shrink manually in SQL Enterprise Manager I get no error
> message, but no shrinking occurs.
> I am grateful for any help.
> Regards,
> Jan Nordgreen
This sounds VERY unusual: database sizes that are 10 times bigger than the
actual datasize are absolutely normal and there's a lot of reasons for that
-
but they usually have something to do with the TA-Log. Please verify that
your transaction log file is really that tiny, and publish the syntax of you
r
shrink statement ...|||First, I don=B4t think that this is TA related, because your TA size is
1MB which is really , really small.
Your issue could be based on several things like:
You can=B4t shrink the database size under the initial size, so if the
initial size was 2GB (which isn=B4t unusal and not that big) you can=B4t
shrink it with DBCC Shrinkdatabase. Look in the BOL for more
information:
"The target size for data and log files as calculated by DBCC
SHRINKDATABASE can never be smaller than the minimum size of a file.
The minimum size of a file is the size specified when the file was
originally created, or the last explicit size set with a file size
changing operation, such as DBCC SHRINKFILE."
You can shrink the database using DBCC Shrinkfile, where you can
specify a new size of a single file. Look in the BOL for more
information.
Anyway, shrinking your file to a smaller size than 2GB could decrease
performance if your database is growing and gaining automatically new
space. So shrinking the database to 200MB would cause a halt if the
size has to be extended, causing waiting processes to be stopped until
the new size is aquired from the OS:
HTH, Jens Suessmeyer.
Database size vs total table and index size
I use SQL Server 2000.
My database is 1912.69 MB with no available free space.
My logfile is 1 MB.
The size of all tables and indexes add up to 200 MB.
I have no diagrams, two views, fifty stored procedures, six users, ten
roles, no rules, no defaults, no user defined data types, no user
defined functions.
Autoshrink is set to true.
My question:
How can the database be almost 2 GB when the tables and indexes add up
to only 200MB?
When I try to shrink manually in SQL Enterprise Manager I get no error
message, but no shrinking occurs.
I am grateful for any help.
Regards,
Jan Nordgreen
Reply
"damezumari" wrote:
> I use SQL Server 2000.
> My database is 1912.69 MB with no available free space.
> My logfile is 1 MB.
> The size of all tables and indexes add up to 200 MB.
> I have no diagrams, two views, fifty stored procedures, six users, ten
> roles, no rules, no defaults, no user defined data types, no user
> defined functions.
> Autoshrink is set to true.
> My question:
> How can the database be almost 2 GB when the tables and indexes add up
> to only 200MB?
> When I try to shrink manually in SQL Enterprise Manager I get no error
> message, but no shrinking occurs.
> I am grateful for any help.
> Regards,
> Jan Nordgreen
This sounds VERY unusual: database sizes that are 10 times bigger than the
actual datasize are absolutely normal and there's a lot of reasons for that -
but they usually have something to do with the TA-Log. Please verify that
your transaction log file is really that tiny, and publish the syntax of your
shrink statement ...
|||First, I don=B4t think that this is TA related, because your TA size is
1MB which is really , really small.
Your issue could be based on several things like:
You can=B4t shrink the database size under the initial size, so if the
initial size was 2GB (which isn=B4t unusal and not that big) you can=B4t
shrink it with DBCC Shrinkdatabase. Look in the BOL for more
information:
"The target size for data and log files as calculated by DBCC
SHRINKDATABASE can never be smaller than the minimum size of a file.
The minimum size of a file is the size specified when the file was
originally created, or the last explicit size set with a file size
changing operation, such as DBCC SHRINKFILE."
You can shrink the database using DBCC Shrinkfile, where you can
specify a new size of a single file. Look in the BOL for more
information.
Anyway, shrinking your file to a smaller size than 2GB could decrease
performance if your database is growing and gaining automatically new
space. So shrinking the database to 200MB would cause a halt if the
size has to be extended, causing waiting processes to be stopped until
the new size is aquired from the OS:
HTH, Jens Suessmeyer.
My database is 1912.69 MB with no available free space.
My logfile is 1 MB.
The size of all tables and indexes add up to 200 MB.
I have no diagrams, two views, fifty stored procedures, six users, ten
roles, no rules, no defaults, no user defined data types, no user
defined functions.
Autoshrink is set to true.
My question:
How can the database be almost 2 GB when the tables and indexes add up
to only 200MB?
When I try to shrink manually in SQL Enterprise Manager I get no error
message, but no shrinking occurs.
I am grateful for any help.
Regards,
Jan Nordgreen
Reply
"damezumari" wrote:
> I use SQL Server 2000.
> My database is 1912.69 MB with no available free space.
> My logfile is 1 MB.
> The size of all tables and indexes add up to 200 MB.
> I have no diagrams, two views, fifty stored procedures, six users, ten
> roles, no rules, no defaults, no user defined data types, no user
> defined functions.
> Autoshrink is set to true.
> My question:
> How can the database be almost 2 GB when the tables and indexes add up
> to only 200MB?
> When I try to shrink manually in SQL Enterprise Manager I get no error
> message, but no shrinking occurs.
> I am grateful for any help.
> Regards,
> Jan Nordgreen
This sounds VERY unusual: database sizes that are 10 times bigger than the
actual datasize are absolutely normal and there's a lot of reasons for that -
but they usually have something to do with the TA-Log. Please verify that
your transaction log file is really that tiny, and publish the syntax of your
shrink statement ...
|||First, I don=B4t think that this is TA related, because your TA size is
1MB which is really , really small.
Your issue could be based on several things like:
You can=B4t shrink the database size under the initial size, so if the
initial size was 2GB (which isn=B4t unusal and not that big) you can=B4t
shrink it with DBCC Shrinkdatabase. Look in the BOL for more
information:
"The target size for data and log files as calculated by DBCC
SHRINKDATABASE can never be smaller than the minimum size of a file.
The minimum size of a file is the size specified when the file was
originally created, or the last explicit size set with a file size
changing operation, such as DBCC SHRINKFILE."
You can shrink the database using DBCC Shrinkfile, where you can
specify a new size of a single file. Look in the BOL for more
information.
Anyway, shrinking your file to a smaller size than 2GB could decrease
performance if your database is growing and gaining automatically new
space. So shrinking the database to 200MB would cause a halt if the
size has to be extended, causing waiting processes to be stopped until
the new size is aquired from the OS:
HTH, Jens Suessmeyer.
Database size vs total table and index size
I use SQL Server 2000.
My database is 1912.69 MB with no available free space.
My logfile is 1 MB.
The size of all tables and indexes add up to 200 MB.
I have no diagrams, two views, fifty stored procedures, six users, ten
roles, no rules, no defaults, no user defined data types, no user
defined functions.
Autoshrink is set to true.
My question:
How can the database be almost 2 GB when the tables and indexes add up
to only 200MB?
When I try to shrink manually in SQL Enterprise Manager I get no error
message, but no shrinking occurs.
I am grateful for any help.
Regards,
Jan Nordgreen
Reply"damezumari" wrote:
> I use SQL Server 2000.
> My database is 1912.69 MB with no available free space.
> My logfile is 1 MB.
> The size of all tables and indexes add up to 200 MB.
> I have no diagrams, two views, fifty stored procedures, six users, ten
> roles, no rules, no defaults, no user defined data types, no user
> defined functions.
> Autoshrink is set to true.
> My question:
> How can the database be almost 2 GB when the tables and indexes add up
> to only 200MB?
> When I try to shrink manually in SQL Enterprise Manager I get no error
> message, but no shrinking occurs.
> I am grateful for any help.
> Regards,
> Jan Nordgreen
This sounds VERY unusual: database sizes that are 10 times bigger than the
actual datasize are absolutely normal and there's a lot of reasons for that -
but they usually have something to do with the TA-Log. Please verify that
your transaction log file is really that tiny, and publish the syntax of your
shrink statement ...|||First, I don=B4t think that this is TA related, because your TA size is
1MB which is really , really small.
Your issue could be based on several things like:
You can=B4t shrink the database size under the initial size, so if the
initial size was 2GB (which isn=B4t unusal and not that big) you can=B4t
shrink it with DBCC Shrinkdatabase. Look in the BOL for more
information:
"The target size for data and log files as calculated by DBCC
SHRINKDATABASE can never be smaller than the minimum size of a file.
The minimum size of a file is the size specified when the file was
originally created, or the last explicit size set with a file size
changing operation, such as DBCC SHRINKFILE."
You can shrink the database using DBCC Shrinkfile, where you can
specify a new size of a single file. Look in the BOL for more
information.
Anyway, shrinking your file to a smaller size than 2GB could decrease
performance if your database is growing and gaining automatically new
space. So shrinking the database to 200MB would cause a halt if the
size has to be extended, causing waiting processes to be stopped until
the new size is aquired from the OS:
HTH, Jens Suessmeyer.
My database is 1912.69 MB with no available free space.
My logfile is 1 MB.
The size of all tables and indexes add up to 200 MB.
I have no diagrams, two views, fifty stored procedures, six users, ten
roles, no rules, no defaults, no user defined data types, no user
defined functions.
Autoshrink is set to true.
My question:
How can the database be almost 2 GB when the tables and indexes add up
to only 200MB?
When I try to shrink manually in SQL Enterprise Manager I get no error
message, but no shrinking occurs.
I am grateful for any help.
Regards,
Jan Nordgreen
Reply"damezumari" wrote:
> I use SQL Server 2000.
> My database is 1912.69 MB with no available free space.
> My logfile is 1 MB.
> The size of all tables and indexes add up to 200 MB.
> I have no diagrams, two views, fifty stored procedures, six users, ten
> roles, no rules, no defaults, no user defined data types, no user
> defined functions.
> Autoshrink is set to true.
> My question:
> How can the database be almost 2 GB when the tables and indexes add up
> to only 200MB?
> When I try to shrink manually in SQL Enterprise Manager I get no error
> message, but no shrinking occurs.
> I am grateful for any help.
> Regards,
> Jan Nordgreen
This sounds VERY unusual: database sizes that are 10 times bigger than the
actual datasize are absolutely normal and there's a lot of reasons for that -
but they usually have something to do with the TA-Log. Please verify that
your transaction log file is really that tiny, and publish the syntax of your
shrink statement ...|||First, I don=B4t think that this is TA related, because your TA size is
1MB which is really , really small.
Your issue could be based on several things like:
You can=B4t shrink the database size under the initial size, so if the
initial size was 2GB (which isn=B4t unusal and not that big) you can=B4t
shrink it with DBCC Shrinkdatabase. Look in the BOL for more
information:
"The target size for data and log files as calculated by DBCC
SHRINKDATABASE can never be smaller than the minimum size of a file.
The minimum size of a file is the size specified when the file was
originally created, or the last explicit size set with a file size
changing operation, such as DBCC SHRINKFILE."
You can shrink the database using DBCC Shrinkfile, where you can
specify a new size of a single file. Look in the BOL for more
information.
Anyway, shrinking your file to a smaller size than 2GB could decrease
performance if your database is growing and gaining automatically new
space. So shrinking the database to 200MB would cause a halt if the
size has to be extended, causing waiting processes to be stopped until
the new size is aquired from the OS:
HTH, Jens Suessmeyer.
Thursday, March 22, 2012
Database Size
Dear All,
I have been asked to disk space / show growth rates for
our database, including Tables, Indexes both Clustered and
Non clustered, plus anything else I can think of.
I can easily calculate the size of the table / page
however I cannot find out how to calculate the size of an
index. Can anyone help with either the formula or point me
to a resourse.
Thanks in Advance
JimHave a look at this:
http://www.sql-server-performance.c...p?TOPIC_ID=4924
/Magnus
"Jimbo" wrote:
> Dear All,
> I have been asked to disk space / show growth rates for
> our database, including Tables, Indexes both Clustered and
> Non clustered, plus anything else I can think of.
> I can easily calculate the size of the table / page
> however I cannot find out how to calculate the size of an
> index. Can anyone help with either the formula or point me
> to a resourse.
> Thanks in Advance
> Jim
>|||Thanks Magnus.
Jim
>--Original Message--
>Have a look at this:
>http://www.sql-server-performance.com/forum/topic.asp?
TOPIC_ID=4924
>/Magnus
>"Jimbo" wrote:
>
and[vbcol=seagreen]
an[vbcol=seagreen]
me[vbcol=seagreen]
>.
>sql
I have been asked to disk space / show growth rates for
our database, including Tables, Indexes both Clustered and
Non clustered, plus anything else I can think of.
I can easily calculate the size of the table / page
however I cannot find out how to calculate the size of an
index. Can anyone help with either the formula or point me
to a resourse.
Thanks in Advance
JimHave a look at this:
http://www.sql-server-performance.c...p?TOPIC_ID=4924
/Magnus
"Jimbo" wrote:
> Dear All,
> I have been asked to disk space / show growth rates for
> our database, including Tables, Indexes both Clustered and
> Non clustered, plus anything else I can think of.
> I can easily calculate the size of the table / page
> however I cannot find out how to calculate the size of an
> index. Can anyone help with either the formula or point me
> to a resourse.
> Thanks in Advance
> Jim
>|||Thanks Magnus.
Jim
>--Original Message--
>Have a look at this:
>http://www.sql-server-performance.com/forum/topic.asp?
TOPIC_ID=4924
>/Magnus
>"Jimbo" wrote:
>
and[vbcol=seagreen]
an[vbcol=seagreen]
me[vbcol=seagreen]
>.
>sql
Database Size
Dear All,
I have been asked to disk space / show growth rates for
our database, including Tables, Indexes both Clustered and
Non clustered, plus anything else I can think of.
I can easily calculate the size of the table / page
however I cannot find out how to calculate the size of an
index. Can anyone help with either the formula or point me
to a resourse.
Thanks in Advance
JimHave a look at this:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=4924
/Magnus
"Jimbo" wrote:
> Dear All,
> I have been asked to disk space / show growth rates for
> our database, including Tables, Indexes both Clustered and
> Non clustered, plus anything else I can think of.
> I can easily calculate the size of the table / page
> however I cannot find out how to calculate the size of an
> index. Can anyone help with either the formula or point me
> to a resourse.
> Thanks in Advance
> Jim
>|||Thanks Magnus.
Jim
>--Original Message--
>Have a look at this:
>http://www.sql-server-performance.com/forum/topic.asp?
TOPIC_ID=4924
>/Magnus
>"Jimbo" wrote:
>> Dear All,
>> I have been asked to disk space / show growth rates for
>> our database, including Tables, Indexes both Clustered
and
>> Non clustered, plus anything else I can think of.
>> I can easily calculate the size of the table / page
>> however I cannot find out how to calculate the size of
an
>> index. Can anyone help with either the formula or point
me
>> to a resourse.
>> Thanks in Advance
>> Jim
>.
>
I have been asked to disk space / show growth rates for
our database, including Tables, Indexes both Clustered and
Non clustered, plus anything else I can think of.
I can easily calculate the size of the table / page
however I cannot find out how to calculate the size of an
index. Can anyone help with either the formula or point me
to a resourse.
Thanks in Advance
JimHave a look at this:
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=4924
/Magnus
"Jimbo" wrote:
> Dear All,
> I have been asked to disk space / show growth rates for
> our database, including Tables, Indexes both Clustered and
> Non clustered, plus anything else I can think of.
> I can easily calculate the size of the table / page
> however I cannot find out how to calculate the size of an
> index. Can anyone help with either the formula or point me
> to a resourse.
> Thanks in Advance
> Jim
>|||Thanks Magnus.
Jim
>--Original Message--
>Have a look at this:
>http://www.sql-server-performance.com/forum/topic.asp?
TOPIC_ID=4924
>/Magnus
>"Jimbo" wrote:
>> Dear All,
>> I have been asked to disk space / show growth rates for
>> our database, including Tables, Indexes both Clustered
and
>> Non clustered, plus anything else I can think of.
>> I can easily calculate the size of the table / page
>> however I cannot find out how to calculate the size of
an
>> index. Can anyone help with either the formula or point
me
>> to a resourse.
>> Thanks in Advance
>> Jim
>.
>
Wednesday, March 21, 2012
Database Size
Dear All,
I have been asked to disk space / show growth rates for
our database, including Tables, Indexes both Clustered and
Non clustered, plus anything else I can think of.
I can easily calculate the size of the table / page
however I cannot find out how to calculate the size of an
index. Can anyone help with either the formula or point me
to a resourse.
Thanks in Advance
Jim
Have a look at this:
http://www.sql-server-performance.co...?TOPIC_ID=4924
/Magnus
"Jimbo" wrote:
> Dear All,
> I have been asked to disk space / show growth rates for
> our database, including Tables, Indexes both Clustered and
> Non clustered, plus anything else I can think of.
> I can easily calculate the size of the table / page
> however I cannot find out how to calculate the size of an
> index. Can anyone help with either the formula or point me
> to a resourse.
> Thanks in Advance
> Jim
>
|||Thanks Magnus.
Jim
>--Original Message--
>Have a look at this:
>http://www.sql-server-performance.com/forum/topic.asp?
TOPIC_ID=4924[vbcol=seagreen]
>/Magnus
>"Jimbo" wrote:
and[vbcol=seagreen]
an[vbcol=seagreen]
me
>.
>
sql
I have been asked to disk space / show growth rates for
our database, including Tables, Indexes both Clustered and
Non clustered, plus anything else I can think of.
I can easily calculate the size of the table / page
however I cannot find out how to calculate the size of an
index. Can anyone help with either the formula or point me
to a resourse.
Thanks in Advance
Jim
Have a look at this:
http://www.sql-server-performance.co...?TOPIC_ID=4924
/Magnus
"Jimbo" wrote:
> Dear All,
> I have been asked to disk space / show growth rates for
> our database, including Tables, Indexes both Clustered and
> Non clustered, plus anything else I can think of.
> I can easily calculate the size of the table / page
> however I cannot find out how to calculate the size of an
> index. Can anyone help with either the formula or point me
> to a resourse.
> Thanks in Advance
> Jim
>
|||Thanks Magnus.
Jim
>--Original Message--
>Have a look at this:
>http://www.sql-server-performance.com/forum/topic.asp?
TOPIC_ID=4924[vbcol=seagreen]
>/Magnus
>"Jimbo" wrote:
and[vbcol=seagreen]
an[vbcol=seagreen]
me
>.
>
sql
Database shrink operation
Hi,
I am confused whether database shrinking operation also does reindexing or
rebuilding of indexes in background when it runs because, shrink operation
affects the data page location on disk caused by their movement.
Thanks
ManuManu,
Shrinking a database does not redo the indexes, it just moves pages around.
One result is that the shrink can hurt performance since the data needed for
a search may no longer be contiguous, which increases disk activity.
If this is a concern (and normally it should be), shrink first, then rebuild
the indexes. Rebuilding the indexes will give up some of space savings, but
it will make your users happier with the performance.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu|||manu
Start with
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu
I am confused whether database shrinking operation also does reindexing or
rebuilding of indexes in background when it runs because, shrink operation
affects the data page location on disk caused by their movement.
Thanks
ManuManu,
Shrinking a database does not redo the indexes, it just moves pages around.
One result is that the shrink can hurt performance since the data needed for
a search may no longer be contiguous, which increases disk activity.
If this is a concern (and normally it should be), shrink first, then rebuild
the indexes. Rebuilding the indexes will give up some of space savings, but
it will make your users happier with the performance.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu|||manu
Start with
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu
Labels:
background,
database,
indexes,
microsoft,
mysql,
operation,
oracle,
orrebuilding,
reindexing,
runs,
server,
shrink,
shrinking,
sql
Database shrink operation
Hi,
I am confused whether database shrinking operation also does reindexing or
rebuilding of indexes in background when it runs because, shrink operation
affects the data page location on disk caused by their movement.
Thanks
ManuManu,
Shrinking a database does not redo the indexes, it just moves pages around.
One result is that the shrink can hurt performance since the data needed for
a search may no longer be contiguous, which increases disk activity.
If this is a concern (and normally it should be), shrink first, then rebuild
the indexes. Rebuilding the indexes will give up some of space savings, but
it will make your users happier with the performance.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu|||manu
Start with
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu
I am confused whether database shrinking operation also does reindexing or
rebuilding of indexes in background when it runs because, shrink operation
affects the data page location on disk caused by their movement.
Thanks
ManuManu,
Shrinking a database does not redo the indexes, it just moves pages around.
One result is that the shrink can hurt performance since the data needed for
a search may no longer be contiguous, which increases disk activity.
If this is a concern (and normally it should be), shrink first, then rebuild
the indexes. Rebuilding the indexes will give up some of space savings, but
it will make your users happier with the performance.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu|||manu
Start with
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu
Labels:
background,
database,
indexes,
microsoft,
mysql,
operation,
oracle,
rebuilding,
reindexing,
runs,
server,
shrink,
shrinking,
sql
Database shrink operation
Hi,
I am confused whether database shrinking operation also does reindexing or
rebuilding of indexes in background when it runs because, shrink operation
affects the data page location on disk caused by their movement.
Thanks
Manu
Manu,
Shrinking a database does not redo the indexes, it just moves pages around.
One result is that the shrink can hurt performance since the data needed for
a search may no longer be contiguous, which increases disk activity.
If this is a concern (and normally it should be), shrink first, then rebuild
the indexes. Rebuilding the indexes will give up some of space savings, but
it will make your users happier with the performance.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu
|||manu
Start with
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu
sql
I am confused whether database shrinking operation also does reindexing or
rebuilding of indexes in background when it runs because, shrink operation
affects the data page location on disk caused by their movement.
Thanks
Manu
Manu,
Shrinking a database does not redo the indexes, it just moves pages around.
One result is that the shrink can hurt performance since the data needed for
a search may no longer be contiguous, which increases disk activity.
If this is a concern (and normally it should be), shrink first, then rebuild
the indexes. Rebuilding the indexes will give up some of space savings, but
it will make your users happier with the performance.
RLF
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu
|||manu
Start with
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"manu" <manu@.discussions.microsoft.com> wrote in message
news:8DD71F01-C93E-4533-B947-1A14A1BE891D@.microsoft.com...
> Hi,
> I am confused whether database shrinking operation also does reindexing or
> rebuilding of indexes in background when it runs because, shrink operation
> affects the data page location on disk caused by their movement.
> Thanks
> Manu
sql
Labels:
background,
database,
indexes,
microsoft,
mysql,
operation,
oracle,
orrebuilding,
reindexing,
runs,
server,
shrink,
shrinking,
sql
Friday, February 17, 2012
Database performance improvement
People:
I work with IBM DB2 and there is a tool called REORGCHK and REORG.
It is used to eveluate the status of tables and indexes(REORGCHK) and in case we run the REORG to fix any fragmentation problem in tables and indexes...
The question is Is there something similar in SQL Server?
I have deleted lots of records and looks like after that the performance of the database is not very good.
Thankswhat version of SQLserver?
Sounds like a rebuild of the index may be needed, it can't hurt to rebuild
after a large delete.
At a low level you could run
DBCC INDEXDEFRAG
Via Enterprise (SQLserver 2000) you can create a maintenance plan to cleanup space and indexes. Just run the wizard to create the optimisation job and run it straight away.
Have Fun|||I have SQL 2000 Server.
Can I run the dbcc command during the day??|||Hi,
If you using SQLserver 2000, the easiest solution is to create a maintenance plan to do this periodically eg once per week.
In Enterprise Manager choose the database -> management
right click on database maintenance plans -> choose new plan
Follow the wizard to create an optimisation maintenance plan.
I tend to create a plan for optimisation and integrity checking and a separate one for back of database / logs.
If your database is really under the weather running the DBCC (or a maintenance doing the same thing) would the highest priority. So running during the day is justified.
Have Fun
I work with IBM DB2 and there is a tool called REORGCHK and REORG.
It is used to eveluate the status of tables and indexes(REORGCHK) and in case we run the REORG to fix any fragmentation problem in tables and indexes...
The question is Is there something similar in SQL Server?
I have deleted lots of records and looks like after that the performance of the database is not very good.
Thankswhat version of SQLserver?
Sounds like a rebuild of the index may be needed, it can't hurt to rebuild
after a large delete.
At a low level you could run
DBCC INDEXDEFRAG
Via Enterprise (SQLserver 2000) you can create a maintenance plan to cleanup space and indexes. Just run the wizard to create the optimisation job and run it straight away.
Have Fun|||I have SQL 2000 Server.
Can I run the dbcc command during the day??|||Hi,
If you using SQLserver 2000, the easiest solution is to create a maintenance plan to do this periodically eg once per week.
In Enterprise Manager choose the database -> management
right click on database maintenance plans -> choose new plan
Follow the wizard to create an optimisation maintenance plan.
I tend to create a plan for optimisation and integrity checking and a separate one for back of database / logs.
If your database is really under the weather running the DBCC (or a maintenance doing the same thing) would the highest priority. So running during the day is justified.
Have Fun
Subscribe to:
Posts (Atom)