I need to know if there are any good tools available to defrag/reindex
SQL2000 databases? I have to manage my own server and I am not a SQL guru. I
run Diskkeeper to take care of the physical files, but I need an easy and
effective way to defrag/reindex the SQL databases.
Thanks,
DerekHi Derek,
You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment the
indexes. DBCC DBREINDEX will defragment as well, so you don't have to run
them both. DBRINDEX will take out locks on your tables though, so you can
only run it when your database is not in use or very quiet.
You can run DBCC DBREINDEX on all tables with:
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
And you can create a script to defrag all indexes with:
SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME (id)
+ ''''+ CHAR(13) +
'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
indid) + ')' + CHAR(13) +
'DBCC SHRINKFILE(1, TRUNCATEONLY)'
FROM sysindexes
WHERE indid BETWEEN 1 AND 254
AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
ORDER BY OBJECT_NAME(id), indid
--
Jacco Schalkwijk
SQL Server MVP
"Derek" <derekb@.nospamderekb.com> wrote in message
news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> I need to know if there are any good tools available to defrag/reindex
> SQL2000 databases? I have to manage my own server and I am not a SQL guru.
I
> run Diskkeeper to take care of the physical files, but I need an easy and
> effective way to defrag/reindex the SQL databases.
> Thanks,
> Derek
>|||A disk defrag doesn't really work with SQL Server, apart
from getting the files in one continuous chunk of disk
space.
However you can defrag the files themselves with the DBCC
INDEXDEFRAG command.
J
>--Original Message--
>I need to know if there are any good tools available to
defrag/reindex
>SQL2000 databases? I have to manage my own server and I
am not a SQL guru. I
>run Diskkeeper to take care of the physical files, but I
need an easy and
>effective way to defrag/reindex the SQL databases.
>Thanks,
>Derek
>
>.
>|||Thanks. Just to make sure, I would ut the following into Query Analyzer and
run:
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
Also, How do I make my databases offline to run?
Thanks much!
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...
> Hi Derek,
> You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment
the
> indexes. DBCC DBREINDEX will defragment as well, so you don't have to run
> them both. DBRINDEX will take out locks on your tables though, so you can
> only run it when your database is not in use or very quiet.
> You can run DBCC DBREINDEX on all tables with:
> EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> And you can create a script to defrag all indexes with:
> SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME
(id)
> + ''''+ CHAR(13) +
> 'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
> indid) + ')' + CHAR(13) +
> 'DBCC SHRINKFILE(1, TRUNCATEONLY)'
> FROM sysindexes
> WHERE indid BETWEEN 1 AND 254
> AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
> ORDER BY OBJECT_NAME(id), indid
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Derek" <derekb@.nospamderekb.com> wrote in message
> news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> > I need to know if there are any good tools available to defrag/reindex
> > SQL2000 databases? I have to manage my own server and I am not a SQL
guru.
> I
> > run Diskkeeper to take care of the physical files, but I need an easy
and
> > effective way to defrag/reindex the SQL databases.
> >
> > Thanks,
> > Derek
> >
> >
>|||Hi Derek,
SQL Server databases do not have to be taken offline to be reindexed. The
reindexing process will block other processes from accessing the tables and
indexes that are being reindexed though, which might be inconvenient if the
reindexing takes a long time.
If that is an issue, you can prevent users from connecting to the database
with (SQL Server 2000 only):
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and after the reindexing you can give them access again with:
ALTER DATABASE <database name> SET MULTI_USER
--
Jacco Schalkwijk
SQL Server MVP
"Derek" <derekb@.nospamderekb.com> wrote in message
news:%23PZdXPZ6DHA.2524@.TK2MSFTNGP11.phx.gbl...
> Thanks. Just to make sure, I would ut the following into Query Analyzer
and
> run:
> EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> Also, How do I make my databases offline to run?
> Thanks much!
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...
> > Hi Derek,
> >
> > You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment
> the
> > indexes. DBCC DBREINDEX will defragment as well, so you don't have to
run
> > them both. DBRINDEX will take out locks on your tables though, so you
can
> > only run it when your database is not in use or very quiet.
> >
> > You can run DBCC DBREINDEX on all tables with:
> > EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> >
> > And you can create a script to defrag all indexes with:
> > SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME
> (id)
> > + ''''+ CHAR(13) +
> > 'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
> > indid) + ')' + CHAR(13) +
> > 'DBCC SHRINKFILE(1, TRUNCATEONLY)'
> > FROM sysindexes
> > WHERE indid BETWEEN 1 AND 254
> > AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
> > AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
> > ORDER BY OBJECT_NAME(id), indid
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> >
> > "Derek" <derekb@.nospamderekb.com> wrote in message
> > news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> > > I need to know if there are any good tools available to defrag/reindex
> > > SQL2000 databases? I have to manage my own server and I am not a SQL
> guru.
> > I
> > > run Diskkeeper to take care of the physical files, but I need an easy
> and
> > > effective way to defrag/reindex the SQL databases.
> > >
> > > Thanks,
> > > Derek
> > >
> > >
> >
> >
>|||Thanks, I should have been more specific. Diskkeeper takes care of the
external files, I was referring to defrag internally. But I think Jacco has
me taken care of. Thanks anyway!
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:889901c3e97b$4907e810$a401280a@.phx.gbl...
> A disk defrag doesn't really work with SQL Server, apart
> from getting the files in one continuous chunk of disk
> space.
> However you can defrag the files themselves with the DBCC
> INDEXDEFRAG command.
> J
>
> >--Original Message--
> >I need to know if there are any good tools available to
> defrag/reindex
> >SQL2000 databases? I have to manage my own server and I
> am not a SQL guru. I
> >run Diskkeeper to take care of the physical files, but I
> need an easy and
> >effective way to defrag/reindex the SQL databases.
> >
> >Thanks,
> >Derek
> >
> >
> >.
> >|||Thanks! I will try this later tonight
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23Cwm3YZ6DHA.2952@.tk2msftngp13.phx.gbl...
> Hi Derek,
> SQL Server databases do not have to be taken offline to be reindexed. The
> reindexing process will block other processes from accessing the tables
and
> indexes that are being reindexed though, which might be inconvenient if
the
> reindexing takes a long time.
> If that is an issue, you can prevent users from connecting to the database
> with (SQL Server 2000 only):
> ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> and after the reindexing you can give them access again with:
> ALTER DATABASE <database name> SET MULTI_USER
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Derek" <derekb@.nospamderekb.com> wrote in message
> news:%23PZdXPZ6DHA.2524@.TK2MSFTNGP11.phx.gbl...
> > Thanks. Just to make sure, I would ut the following into Query Analyzer
> and
> > run:
> >
> > EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> >
> > Also, How do I make my databases offline to run?
> >
> > Thanks much!
> >
> >
> > "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> > news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...
> > > Hi Derek,
> > >
> > > You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to
defragment
> > the
> > > indexes. DBCC DBREINDEX will defragment as well, so you don't have to
> run
> > > them both. DBRINDEX will take out locks on your tables though, so you
> can
> > > only run it when your database is not in use or very quiet.
> > >
> > > You can run DBCC DBREINDEX on all tables with:
> > > EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> > >
> > > And you can create a script to defrag all indexes with:
> > > SELECT 'SELECT ''Defragging index ' + name + ' on table ' +
OBJECT_NAME
> > (id)
> > > + ''''+ CHAR(13) +
> > > 'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' +
CONVERT(VARCHAR,
> > > indid) + ')' + CHAR(13) +
> > > 'DBCC SHRINKFILE(1, TRUNCATEONLY)'
> > > FROM sysindexes
> > > WHERE indid BETWEEN 1 AND 254
> > > AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
> > > AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
> > > ORDER BY OBJECT_NAME(id), indid
> > >
> > > --
> > > Jacco Schalkwijk
> > > SQL Server MVP
> > >
> > >
> > > "Derek" <derekb@.nospamderekb.com> wrote in message
> > > news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
> > > > I need to know if there are any good tools available to
defrag/reindex
> > > > SQL2000 databases? I have to manage my own server and I am not a SQL
> > guru.
> > > I
> > > > run Diskkeeper to take care of the physical files, but I need an
easy
> > and
> > > > effective way to defrag/reindex the SQL databases.
> > > >
> > > > Thanks,
> > > > Derek
> > > >
> > > >
> > >
> > >
> >
> >
>
Showing posts with label available. Show all posts
Showing posts with label available. Show all posts
Sunday, March 11, 2012
Defrag Tool?
I need to know if there are any good tools available to defrag/reindex
SQL2000 databases? I have to manage my own server and I am not a SQL guru. I
run Diskkeeper to take care of the physical files, but I need an easy and
effective way to defrag/reindex the SQL databases.
Thanks,
DerekHi Derek,
You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment the
indexes. DBCC DBREINDEX will defragment as well, so you don't have to run
them both. DBRINDEX will take out locks on your tables though, so you can
only run it when your database is not in use or very quiet.
You can run DBCC DBREINDEX on all tables with:
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
And you can create a script to defrag all indexes with:
SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME (id)
+ ''''+ CHAR(13) +
'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
indid) + ')' + CHAR(13) +
'DBCC SHRINKFILE(1, TRUNCATEONLY)'
FROM sysindexes
WHERE indid BETWEEN 1 AND 254
AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
ORDER BY OBJECT_NAME(id), indid
Jacco Schalkwijk
SQL Server MVP
"Derek" <derekb@.nospamderekb.com> wrote in message
news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
I
run:
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
Also, How do I make my databases offline to run?
Thanks much!
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...
the
(id)
SQL Server databases do not have to be taken offline to be reindexed. The
reindexing process will block other processes from accessing the tables and
indexes that are being reindexed though, which might be inconvenient if the
reindexing takes a long time.
If that is an issue, you can prevent users from connecting to the database
with (SQL Server 2000 only):
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and after the reindexing you can give them access again with:
ALTER DATABASE <database name> SET MULTI_USER
Jacco Schalkwijk
SQL Server MVP
"Derek" <derekb@.nospamderekb.com> wrote in message
news:%23PZdXPZ6DHA.2524@.TK2MSFTNGP11.phx.gbl...
and
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23Cwm3YZ6DHA.2952@.tk2msftngp13.phx.gbl...
and
the
SQL2000 databases? I have to manage my own server and I am not a SQL guru. I
run Diskkeeper to take care of the physical files, but I need an easy and
effective way to defrag/reindex the SQL databases.
Thanks,
DerekHi Derek,
You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment the
indexes. DBCC DBREINDEX will defragment as well, so you don't have to run
them both. DBRINDEX will take out locks on your tables though, so you can
only run it when your database is not in use or very quiet.
You can run DBCC DBREINDEX on all tables with:
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
And you can create a script to defrag all indexes with:
SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME (id)
+ ''''+ CHAR(13) +
'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
indid) + ')' + CHAR(13) +
'DBCC SHRINKFILE(1, TRUNCATEONLY)'
FROM sysindexes
WHERE indid BETWEEN 1 AND 254
AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
ORDER BY OBJECT_NAME(id), indid
Jacco Schalkwijk
SQL Server MVP
"Derek" <derekb@.nospamderekb.com> wrote in message
news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
quote:
> I need to know if there are any good tools available to defrag/reindex
> SQL2000 databases? I have to manage my own server and I am not a SQL guru.
I
quote:|||Thanks. Just to make sure, I would ut the following into Query Analyzer and
> run Diskkeeper to take care of the physical files, but I need an easy and
> effective way to defrag/reindex the SQL databases.
> Thanks,
> Derek
>
run:
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
Also, How do I make my databases offline to run?
Thanks much!
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...
quote:
> Hi Derek,
> You can use DBCC DBREINDEX to reindex and DBCC INDEXDEFRAG to defragment
the
quote:
> indexes. DBCC DBREINDEX will defragment as well, so you don't have to run
> them both. DBRINDEX will take out locks on your tables though, so you can
> only run it when your database is not in use or very quiet.
> You can run DBCC DBREINDEX on all tables with:
> EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> And you can create a script to defrag all indexes with:
> SELECT 'SELECT ''Defragging index ' + name + ' on table ' + OBJECT_NAME
(id)
quote:|||Hi Derek,
> + ''''+ CHAR(13) +
> 'DBCC INDEXDEFRAG(0, ' + CONVERT(VARCHAR, id) + ', ' + CONVERT(VARCHAR,
> indid) + ')' + CHAR(13) +
> 'DBCC SHRINKFILE(1, TRUNCATEONLY)'
> FROM sysindexes
> WHERE indid BETWEEN 1 AND 254
> AND OBJECTPROPERTY(id, 'IsMSShipped') = 0
> AND INDEXPROPERTY(id, name, 'IsStatistics') = 0
> ORDER BY OBJECT_NAME(id), indid
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Derek" <derekb@.nospamderekb.com> wrote in message
> news:%239PFWyS6DHA.2628@.TK2MSFTNGP10.phx.gbl...
guru.[QUOTE]
> I
and[QUOTE]
>
SQL Server databases do not have to be taken offline to be reindexed. The
reindexing process will block other processes from accessing the tables and
indexes that are being reindexed though, which might be inconvenient if the
reindexing takes a long time.
If that is an issue, you can prevent users from connecting to the database
with (SQL Server 2000 only):
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and after the reindexing you can give them access again with:
ALTER DATABASE <database name> SET MULTI_USER
Jacco Schalkwijk
SQL Server MVP
"Derek" <derekb@.nospamderekb.com> wrote in message
news:%23PZdXPZ6DHA.2524@.TK2MSFTNGP11.phx.gbl...
quote:
> Thanks. Just to make sure, I would ut the following into Query Analyzer
and
quote:|||Thanks! I will try this later tonight
> run:
> EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'') WITH NO_INFOMSGS'
> Also, How do I make my databases offline to run?
> Thanks much!
>
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:uzS2ZQX6DHA.2496@.TK2MSFTNGP09.phx.gbl...
> the
run[QUOTE]
can[QUOTE]
> (id)
> guru.
> and
>
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:%23Cwm3YZ6DHA.2952@.tk2msftngp13.phx.gbl...
quote:
> Hi Derek,
> SQL Server databases do not have to be taken offline to be reindexed. The
> reindexing process will block other processes from accessing the tables
and
quote:
> indexes that are being reindexed though, which might be inconvenient if
the
quote:
> reindexing takes a long time.
> If that is an issue, you can prevent users from connecting to the database
> with (SQL Server 2000 only):
> ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> and after the reindexing you can give them access again with:
> ALTER DATABASE <database name> SET MULTI_USER
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Derek" <derekb@.nospamderekb.com> wrote in message
> news:%23PZdXPZ6DHA.2524@.TK2MSFTNGP11.phx.gbl...
> and
defragment[QUOTE]
> run
> can
OBJECT_NAME[QUOTE]
CONVERT(VARCHAR,[QUOTE]
defrag/reindex[QUOTE]
easy[QUOTE]
>
Saturday, February 25, 2012
Deferred constraints?
Is this on the list for Katmai? (BOL shows only what is available in the current CTP, so it is hard to know).
thanks
This is not planned for Katmai. -- Umachandar Jayachandran Microsoft SQL Server Performance Team SQL Server Engine Team Tips Blog at http://blogs.msdn.com/sqltips/default.aspx This posting is provided "AS IS" with no warranties, and confers no rights. "Lakusha"@.discussions.microsoft.com wrote on Thu, 12 Jul 2007 07:11:08 -0700: L> Is this on the list for Katmai? (BOL shows only what is available in L> the current CTP, so it is hard to know). L> thanksSunday, February 19, 2012
Default Value for a Queried Parameter
I have created a report parameter with a query for the list of available
values. This works well and you are able to see the values and select the
one you want and see the correct report lines. When I try to assign a
not-queried value to the default value the report preview acts like there is
nothing in the default value and you have to select the value from the drop
down list. I have made the default value entered is the correct case and
spelling. Any ideas?Have you tried using both the Lable and the value for the parameter?
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pl" <pl@.discussions.microsoft.com> wrote in message
news:F849BD60-1588-49D2-8727-083DDA5F7A56@.microsoft.com...
>I have created a report parameter with a query for the list of available
> values. This works well and you are able to see the values and select the
> one you want and see the correct report lines. When I try to assign a
> not-queried value to the default value the report preview acts like there
> is
> nothing in the default value and you have to select the value from the
> drop
> down list. I have made the default value entered is the correct case and
> spelling. Any ideas?
values. This works well and you are able to see the values and select the
one you want and see the correct report lines. When I try to assign a
not-queried value to the default value the report preview acts like there is
nothing in the default value and you have to select the value from the drop
down list. I have made the default value entered is the correct case and
spelling. Any ideas?Have you tried using both the Lable and the value for the parameter?
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pl" <pl@.discussions.microsoft.com> wrote in message
news:F849BD60-1588-49D2-8727-083DDA5F7A56@.microsoft.com...
>I have created a report parameter with a query for the list of available
> values. This works well and you are able to see the values and select the
> one you want and see the correct report lines. When I try to assign a
> not-queried value to the default value the report preview acts like there
> is
> nothing in the default value and you have to select the value from the
> drop
> down list. I have made the default value entered is the correct case and
> spelling. Any ideas?
Subscribe to:
Posts (Atom)