Good Afternoon
I am using database maintenance plans in order to perform
Backups, including optimisation, index re-builds etc.
The problem is that I have discovered that the Maintenance
Plans do not always perform the Backup, although the rest
of the plans perform normally: -
Two Maintenance Plans performing Full Backup, one at
midday and one in the evening, monday to friday, deleting
old files, re-building indexes, re-sizing databases and
optimising unused space.
Two Maintenance Plans performing Transaction Log Backups,
one from 08:00 to 11:00 and the other 13:00 to 18:, monday
to friday, deleting old files and re-building indexes.
There is plenty of space on the Server to allow for the
Backups. E.g. The drive for the Transaction Log Backups
has over 5Gb of space available, the collective size of
the Backups never total more than 350Mb.
I created these Maintenance Plans, and I am registered on
the SQL Server as a System Administrator.
Thank You In Advance
Tony C.
Make sure that you have specified a report file for the maint plan and check that file for error messages.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Tony C" <anonymous@.discussions.microsoft.com> wrote in message news:20d201c4279b$33e423a0$a601280a@.phx.gbl...
> Good Afternoon
> I am using database maintenance plans in order to perform
> Backups, including optimisation, index re-builds etc.
> The problem is that I have discovered that the Maintenance
> Plans do not always perform the Backup, although the rest
> of the plans perform normally: -
> Two Maintenance Plans performing Full Backup, one at
> midday and one in the evening, monday to friday, deleting
> old files, re-building indexes, re-sizing databases and
> optimising unused space.
> Two Maintenance Plans performing Transaction Log Backups,
> one from 08:00 to 11:00 and the other 13:00 to 18:, monday
> to friday, deleting old files and re-building indexes.
> There is plenty of space on the Server to allow for the
> Backups. E.g. The drive for the Transaction Log Backups
> has over 5Gb of space available, the collective size of
> the Backups never total more than 350Mb.
> I created these Maintenance Plans, and I am registered on
> the SQL Server as a System Administrator.
> Thank You In Advance
>
> Tony C.
|||Thanks for this, the Maintenance Plans were producing the
Logs..
It would seem that one of the Applications we are using
can hold up other Transactions when it is left in a
certain state, I've therefore got a bit of debugging to
do!!
Tony C
>--Original Message--
>Make sure that you have specified a report file for the
maint plan and check that file for error messages.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Tony C" <anonymous@.discussions.microsoft.com> wrote in
message news:20d201c4279b$33e423a0$a601280a@.phx.gbl...[vbcol=seagreen]
perform[vbcol=seagreen]
Maintenance[vbcol=seagreen]
rest[vbcol=seagreen]
deleting[vbcol=seagreen]
Backups,[vbcol=seagreen]
monday[vbcol=seagreen]
on
>
>.
>
Showing posts with label maintenance. Show all posts
Showing posts with label maintenance. Show all posts
Monday, March 19, 2012
Database maintence plan
I have looked at the wizard for creating a database
maintenance plan and there are a lot of options. We have
30 databases on each of 2 clustered SQL2000 servers.
Originally I set up seperate backup jobs for each
database and transaction log and scheduled them to run
each night. This leaves to much room for error. I may
forget a backup or transaction log. I assume that the
maint. plan will backup databases and transaction logs
and truncate the transaction logs on all databases. There
is less chance of me forgetting to add a backup. What
options are recommended when running the database maint.
plan wizard?
Thank You
TomWe have quite a few databases on one of our servers. I threw together a
quick and simple way to back them up using sp_MSforeachdb:
If you want to see how it works on your server, replace
EXEC (@.sql)
with
PRINT @.sql
and run it on your server
You will probably have to change the backup location to fit your
environment.
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
Keith
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:137b01c47bc0$0d162970$a301280a@.phx.gbl...
> I have looked at the wizard for creating a database
> maintenance plan and there are a lot of options. We have
> 30 databases on each of 2 clustered SQL2000 servers.
> Originally I set up seperate backup jobs for each
> database and transaction log and scheduled them to run
> each night. This leaves to much room for error. I may
> forget a backup or transaction log. I assume that the
> maint. plan will backup databases and transaction logs
> and truncate the transaction logs on all databases. There
> is less chance of me forgetting to add a backup. What
> options are recommended when running the database maint.
> plan wizard?
> Thank You
> Tom
maintenance plan and there are a lot of options. We have
30 databases on each of 2 clustered SQL2000 servers.
Originally I set up seperate backup jobs for each
database and transaction log and scheduled them to run
each night. This leaves to much room for error. I may
forget a backup or transaction log. I assume that the
maint. plan will backup databases and transaction logs
and truncate the transaction logs on all databases. There
is less chance of me forgetting to add a backup. What
options are recommended when running the database maint.
plan wizard?
Thank You
TomWe have quite a few databases on one of our servers. I threw together a
quick and simple way to back them up using sp_MSforeachdb:
If you want to see how it works on your server, replace
EXEC (@.sql)
with
PRINT @.sql
and run it on your server
You will probably have to change the backup location to fit your
environment.
EXEC sp_MSforeachdb
'IF N''?'' NOT IN(''model'' ,''tempdb'', ''distribution'', ''Northwind'',
''pubs'')
BEGIN
DECLARE @.sql varchar(1000);
set @.sql = ''BACKUP DATABASE '' + (''?'') + '' TO DISK =''''D:\MSSQL2000\MSSQL\BACKUP\'' + (''?'') + ''.bak'''' WITH INIT''
EXEC (@.sql)
END'
Keith
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:137b01c47bc0$0d162970$a301280a@.phx.gbl...
> I have looked at the wizard for creating a database
> maintenance plan and there are a lot of options. We have
> 30 databases on each of 2 clustered SQL2000 servers.
> Originally I set up seperate backup jobs for each
> database and transaction log and scheduled them to run
> each night. This leaves to much room for error. I may
> forget a backup or transaction log. I assume that the
> maint. plan will backup databases and transaction logs
> and truncate the transaction logs on all databases. There
> is less chance of me forgetting to add a backup. What
> options are recommended when running the database maint.
> plan wizard?
> Thank You
> Tom
Database maintenance using DBCC DBREINDEX
Hi,
Currently, I use sp_msForEachTable to reindex every table in one of my
databases. However I did not find any information regarding this stored
procedure in BOL. Is it safe to use, or why isn't it documented?
EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
Yours sincerely,
Jo Segers.
Hi,
The procedure sp_MSforeachtable executes a set of given commands against all
the user tables in the current database.
Have a look into this link for detailed information and usage.
http://www.bstsoftware.com/tsug/Nov9...eachtable.html
Thanks
Hari
MCDBA
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>
|||Adding to the other posts:
That procedure only contains a cursor which loops all tables in the database and executes the specified
command against the tables. You can easily write such a cursor yourself. The procedure is likely to work the
same way in future service packs of SQL2K, and when Yukon comes, you want to look at these things anyhow.
Still, it is not documented, not supported, use at own "risk", which only you can assess.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>
|||Thanks,
Do you know if there is a similar stored procedure that is supported?
Jo.
"Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...[vbcol=seagreen]
> Hi Jo,
> The command sp_msForEachTable is no longer supported my
> Microsoft. This means on the next release of SQL Server it
> may or may not be part of it.
> If its not supported then there are no BOL's for it.
> J
>
> in one of my
> regarding this stored
> documented?
|||I'm sure there exists such on the Net, but if they are free, I doubt that you will have formal support form
the one who wrote it. AFAIK, there is no such in SQL Server. But, again, it is not difficult to write one
yourself, using a cursor, if you have a little bit if TSQL programming experience.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message news:%23lKZD$4JEHA.2624@.TK2MSFTNGP09.phx.gbl...
> Thanks,
> Do you know if there is a similar stored procedure that is supported?
> Jo.
> "Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
> news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
>
|||Ok,
Thanks for the information, I will write one myself. I wonder why they
didn't implement this sp in SQLserver 2000 because it is quite usefull.
Thanks for the support,
Jo.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schreef
in bericht news:uYzs9B5JEHA.208@.tk2msftngp13.phx.gbl...
> I'm sure there exists such on the Net, but if they are free, I doubt that
you will have formal support form
> the one who wrote it. AFAIK, there is no such in SQL Server. But, again,
it is not difficult to write one
> yourself, using a cursor, if you have a little bit if TSQL programming
experience.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23lKZD$4JEHA.2624@.TK2MSFTNGP09.phx.gbl...
>
|||Hi Jo,
If you want a way to run a command against all fragmented indexes in a
databases, check out example E in the Books Online for DBCC SHOWCONTIG. You
should not use sp_msForEachTable.
I'd like to go to the root of your problem, which is wanting to reindex all
the tables in one of your database. I take it you're doing this for
performance reasons. The only way you'll increase performance by doing this
is if the index (clustered or non-clustered) has logical fragmentation and
is used for a range scan. I'm guessing that not all the indexes you're
rebuilding are used in this fashion. Also, sometimes it can be better to run
DBCC INDEXDEFRAG instead of DBCC DBREINDEX.
I recommend you read the whitepaper on this topic at
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Let me know if you have any more questions.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>
|||Hi Paul,
I'm still reading the whitepaper, but the example in BOL helped a lot. I am
going to adjust the code in the example for use in my produktion database.
I am just curious: why isn't sp_msForEachTable supported anymore?
Yours sincerely,
Jo Segers.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> schreef in bericht
news:O2t8tk5JEHA.2376@.tk2msftngp13.phx.gbl...
> Hi Jo,
> If you want a way to run a command against all fragmented indexes in a
> databases, check out example E in the Books Online for DBCC SHOWCONTIG.
You
> should not use sp_msForEachTable.
> I'd like to go to the root of your problem, which is wanting to reindex
all
> the tables in one of your database. I take it you're doing this for
> performance reasons. The only way you'll increase performance by doing
this
> is if the index (clustered or non-clustered) has logical fragmentation and
> is used for a range scan. I'm guessing that not all the indexes you're
> rebuilding are used in this fashion. Also, sometimes it can be better to
run
> DBCC INDEXDEFRAG instead of DBCC DBREINDEX.
> I recommend you read the whitepaper on this topic at
>
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
> Let me know if you have any more questions.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
>
|||As far as I know there is no Microsoft official version
that does the same, sorry.
However you can get all the user tables from your database
by doing
select Name from sysobjects where xtype = 'U'
J
>--Original Message--
>Thanks,
>Do you know if there is a similar stored procedure that
is supported?
>Jo.
>"Julie" <anonymous@.discussions.microsoft.com> schreef in
bericht[vbcol=seagreen]
>news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
it[vbcol=seagreen]
table
>
>.
>
|||Thanks for the help.
"Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
news:216f01c427a7$9b1e6a60$a601280a@.phx.gbl...[vbcol=seagreen]
> As far as I know there is no Microsoft official version
> that does the same, sorry.
> However you can get all the user tables from your database
> by doing
> select Name from sysobjects where xtype = 'U'
> J
>
> is supported?
> bericht
> it
> table
Currently, I use sp_msForEachTable to reindex every table in one of my
databases. However I did not find any information regarding this stored
procedure in BOL. Is it safe to use, or why isn't it documented?
EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
Yours sincerely,
Jo Segers.
Hi,
The procedure sp_MSforeachtable executes a set of given commands against all
the user tables in the current database.
Have a look into this link for detailed information and usage.
http://www.bstsoftware.com/tsug/Nov9...eachtable.html
Thanks
Hari
MCDBA
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>
|||Adding to the other posts:
That procedure only contains a cursor which loops all tables in the database and executes the specified
command against the tables. You can easily write such a cursor yourself. The procedure is likely to work the
same way in future service packs of SQL2K, and when Yukon comes, you want to look at these things anyhow.
Still, it is not documented, not supported, use at own "risk", which only you can assess.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>
|||Thanks,
Do you know if there is a similar stored procedure that is supported?
Jo.
"Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...[vbcol=seagreen]
> Hi Jo,
> The command sp_msForEachTable is no longer supported my
> Microsoft. This means on the next release of SQL Server it
> may or may not be part of it.
> If its not supported then there are no BOL's for it.
> J
>
> in one of my
> regarding this stored
> documented?
|||I'm sure there exists such on the Net, but if they are free, I doubt that you will have formal support form
the one who wrote it. AFAIK, there is no such in SQL Server. But, again, it is not difficult to write one
yourself, using a cursor, if you have a little bit if TSQL programming experience.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message news:%23lKZD$4JEHA.2624@.TK2MSFTNGP09.phx.gbl...
> Thanks,
> Do you know if there is a similar stored procedure that is supported?
> Jo.
> "Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
> news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
>
|||Ok,
Thanks for the information, I will write one myself. I wonder why they
didn't implement this sp in SQLserver 2000 because it is quite usefull.
Thanks for the support,
Jo.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schreef
in bericht news:uYzs9B5JEHA.208@.tk2msftngp13.phx.gbl...
> I'm sure there exists such on the Net, but if they are free, I doubt that
you will have formal support form
> the one who wrote it. AFAIK, there is no such in SQL Server. But, again,
it is not difficult to write one
> yourself, using a cursor, if you have a little bit if TSQL programming
experience.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23lKZD$4JEHA.2624@.TK2MSFTNGP09.phx.gbl...
>
|||Hi Jo,
If you want a way to run a command against all fragmented indexes in a
databases, check out example E in the Books Online for DBCC SHOWCONTIG. You
should not use sp_msForEachTable.
I'd like to go to the root of your problem, which is wanting to reindex all
the tables in one of your database. I take it you're doing this for
performance reasons. The only way you'll increase performance by doing this
is if the index (clustered or non-clustered) has logical fragmentation and
is used for a range scan. I'm guessing that not all the indexes you're
rebuilding are used in this fashion. Also, sometimes it can be better to run
DBCC INDEXDEFRAG instead of DBCC DBREINDEX.
I recommend you read the whitepaper on this topic at
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Let me know if you have any more questions.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>
|||Hi Paul,
I'm still reading the whitepaper, but the example in BOL helped a lot. I am
going to adjust the code in the example for use in my produktion database.
I am just curious: why isn't sp_msForEachTable supported anymore?
Yours sincerely,
Jo Segers.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> schreef in bericht
news:O2t8tk5JEHA.2376@.tk2msftngp13.phx.gbl...
> Hi Jo,
> If you want a way to run a command against all fragmented indexes in a
> databases, check out example E in the Books Online for DBCC SHOWCONTIG.
You
> should not use sp_msForEachTable.
> I'd like to go to the root of your problem, which is wanting to reindex
all
> the tables in one of your database. I take it you're doing this for
> performance reasons. The only way you'll increase performance by doing
this
> is if the index (clustered or non-clustered) has logical fragmentation and
> is used for a range scan. I'm guessing that not all the indexes you're
> rebuilding are used in this fashion. Also, sometimes it can be better to
run
> DBCC INDEXDEFRAG instead of DBCC DBREINDEX.
> I recommend you read the whitepaper on this topic at
>
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
> Let me know if you have any more questions.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
>
|||As far as I know there is no Microsoft official version
that does the same, sorry.
However you can get all the user tables from your database
by doing
select Name from sysobjects where xtype = 'U'
J
>--Original Message--
>Thanks,
>Do you know if there is a similar stored procedure that
is supported?
>Jo.
>"Julie" <anonymous@.discussions.microsoft.com> schreef in
bericht[vbcol=seagreen]
>news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
it[vbcol=seagreen]
table
>
>.
>
|||Thanks for the help.
"Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
news:216f01c427a7$9b1e6a60$a601280a@.phx.gbl...[vbcol=seagreen]
> As far as I know there is no Microsoft official version
> that does the same, sorry.
> However you can get all the user tables from your database
> by doing
> select Name from sysobjects where xtype = 'U'
> J
>
> is supported?
> bericht
> it
> table
Database maintenance using DBCC DBREINDEX
Hi,
Currently, I use sp_msForEachTable to reindex every table in one of my
databases. However I did not find any information regarding this stored
procedure in BOL. Is it safe to use, or why isn't it documented?
EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
Yours sincerely,
Jo Segers.Hi,
The procedure sp_MSforeachtable executes a set of given commands against all
the user tables in the current database.
Have a look into this link for detailed information and usage.
http://www.bstsoftware.com/tsug/Nov99/sp_MSforeachtable.html
Thanks
Hari
MCDBA
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>|||Hi Jo,
The command sp_msForEachTable is no longer supported my
Microsoft. This means on the next release of SQL Server it
may or may not be part of it.
If its not supported then there are no BOL's for it.
J
>--Original Message--
>Hi,
>Currently, I use sp_msForEachTable to reindex every table
in one of my
>databases. However I did not find any information
regarding this stored
>procedure in BOL. Is it safe to use, or why isn't it
documented?
>EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
>Yours sincerely,
>Jo Segers.
>
>.
>|||Adding to the other posts:
That procedure only contains a cursor which loops all tables in the database and executes the specified
command against the tables. You can easily write such a cursor yourself. The procedure is likely to work the
same way in future service packs of SQL2K, and when Yukon comes, you want to look at these things anyhow.
Still, it is not documented, not supported, use at own "risk", which only you can assess.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>|||Thanks,
Do you know if there is a similar stored procedure that is supported?
Jo.
"Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
> Hi Jo,
> The command sp_msForEachTable is no longer supported my
> Microsoft. This means on the next release of SQL Server it
> may or may not be part of it.
> If its not supported then there are no BOL's for it.
> J
>
> >--Original Message--
> >Hi,
> >
> >Currently, I use sp_msForEachTable to reindex every table
> in one of my
> >databases. However I did not find any information
> regarding this stored
> >procedure in BOL. Is it safe to use, or why isn't it
> documented?
> >
> >EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> >
> >Yours sincerely,
> >
> >Jo Segers.
> >
> >
> >.
> >|||I'm sure there exists such on the Net, but if they are free, I doubt that you will have formal support form
the one who wrote it. AFAIK, there is no such in SQL Server. But, again, it is not difficult to write one
yourself, using a cursor, if you have a little bit if TSQL programming experience.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message news:%23lKZD$4JEHA.2624@.TK2MSFTNGP09.phx.gbl...
> Thanks,
> Do you know if there is a similar stored procedure that is supported?
> Jo.
> "Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
> news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
> > Hi Jo,
> >
> > The command sp_msForEachTable is no longer supported my
> > Microsoft. This means on the next release of SQL Server it
> > may or may not be part of it.
> >
> > If its not supported then there are no BOL's for it.
> >
> > J
> >
> >
> > >--Original Message--
> > >Hi,
> > >
> > >Currently, I use sp_msForEachTable to reindex every table
> > in one of my
> > >databases. However I did not find any information
> > regarding this stored
> > >procedure in BOL. Is it safe to use, or why isn't it
> > documented?
> > >
> > >EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> > >
> > >Yours sincerely,
> > >
> > >Jo Segers.
> > >
> > >
> > >.
> > >
>|||Ok,
Thanks for the information, I will write one myself. I wonder why they
didn't implement this sp in SQLserver 2000 because it is quite usefull.
Thanks for the support,
Jo.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schreef
in bericht news:uYzs9B5JEHA.208@.tk2msftngp13.phx.gbl...
> I'm sure there exists such on the Net, but if they are free, I doubt that
you will have formal support form
> the one who wrote it. AFAIK, there is no such in SQL Server. But, again,
it is not difficult to write one
> yourself, using a cursor, if you have a little bit if TSQL programming
experience.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23lKZD$4JEHA.2624@.TK2MSFTNGP09.phx.gbl...
> > Thanks,
> >
> > Do you know if there is a similar stored procedure that is supported?
> >
> > Jo.
> >
> > "Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
> > news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
> > > Hi Jo,
> > >
> > > The command sp_msForEachTable is no longer supported my
> > > Microsoft. This means on the next release of SQL Server it
> > > may or may not be part of it.
> > >
> > > If its not supported then there are no BOL's for it.
> > >
> > > J
> > >
> > >
> > > >--Original Message--
> > > >Hi,
> > > >
> > > >Currently, I use sp_msForEachTable to reindex every table
> > > in one of my
> > > >databases. However I did not find any information
> > > regarding this stored
> > > >procedure in BOL. Is it safe to use, or why isn't it
> > > documented?
> > > >
> > > >EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> > > >
> > > >Yours sincerely,
> > > >
> > > >Jo Segers.
> > > >
> > > >
> > > >.
> > > >
> >
> >
>|||Hi Jo,
If you want a way to run a command against all fragmented indexes in a
databases, check out example E in the Books Online for DBCC SHOWCONTIG. You
should not use sp_msForEachTable.
I'd like to go to the root of your problem, which is wanting to reindex all
the tables in one of your database. I take it you're doing this for
performance reasons. The only way you'll increase performance by doing this
is if the index (clustered or non-clustered) has logical fragmentation and
is used for a range scan. I'm guessing that not all the indexes you're
rebuilding are used in this fashion. Also, sometimes it can be better to run
DBCC INDEXDEFRAG instead of DBCC DBREINDEX.
I recommend you read the whitepaper on this topic at
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Let me know if you have any more questions.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>|||Hi Paul,
I'm still reading the whitepaper, but the example in BOL helped a lot. I am
going to adjust the code in the example for use in my produktion database.
I am just curious: why isn't sp_msForEachTable supported anymore?
Yours sincerely,
Jo Segers.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> schreef in bericht
news:O2t8tk5JEHA.2376@.tk2msftngp13.phx.gbl...
> Hi Jo,
> If you want a way to run a command against all fragmented indexes in a
> databases, check out example E in the Books Online for DBCC SHOWCONTIG.
You
> should not use sp_msForEachTable.
> I'd like to go to the root of your problem, which is wanting to reindex
all
> the tables in one of your database. I take it you're doing this for
> performance reasons. The only way you'll increase performance by doing
this
> is if the index (clustered or non-clustered) has logical fragmentation and
> is used for a range scan. I'm guessing that not all the indexes you're
> rebuilding are used in this fashion. Also, sometimes it can be better to
run
> DBCC INDEXDEFRAG instead of DBCC DBREINDEX.
> I recommend you read the whitepaper on this topic at
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> Let me know if you have any more questions.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > Currently, I use sp_msForEachTable to reindex every table in one of my
> > databases. However I did not find any information regarding this stored
> > procedure in BOL. Is it safe to use, or why isn't it documented?
> >
> > EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> >
> > Yours sincerely,
> >
> > Jo Segers.
> >
> >
>|||As far as I know there is no Microsoft official version
that does the same, sorry.
However you can get all the user tables from your database
by doing
select Name from sysobjects where xtype = 'U'
J
>--Original Message--
>Thanks,
>Do you know if there is a similar stored procedure that
is supported?
>Jo.
>"Julie" <anonymous@.discussions.microsoft.com> schreef in
bericht
>news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
>> Hi Jo,
>> The command sp_msForEachTable is no longer supported my
>> Microsoft. This means on the next release of SQL Server
it
>> may or may not be part of it.
>> If its not supported then there are no BOL's for it.
>> J
>>
>> >--Original Message--
>> >Hi,
>> >
>> >Currently, I use sp_msForEachTable to reindex every
table
>> in one of my
>> >databases. However I did not find any information
>> regarding this stored
>> >procedure in BOL. Is it safe to use, or why isn't it
>> documented?
>> >
>> >EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
>> >
>> >Yours sincerely,
>> >
>> >Jo Segers.
>> >
>> >
>> >.
>> >
>
>.
>|||Thanks for the help.
"Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
news:216f01c427a7$9b1e6a60$a601280a@.phx.gbl...
> As far as I know there is no Microsoft official version
> that does the same, sorry.
> However you can get all the user tables from your database
> by doing
> select Name from sysobjects where xtype = 'U'
> J
>
> >--Original Message--
> >Thanks,
> >
> >Do you know if there is a similar stored procedure that
> is supported?
> >
> >Jo.
> >
> >"Julie" <anonymous@.discussions.microsoft.com> schreef in
> bericht
> >news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
> >> Hi Jo,
> >>
> >> The command sp_msForEachTable is no longer supported my
> >> Microsoft. This means on the next release of SQL Server
> it
> >> may or may not be part of it.
> >>
> >> If its not supported then there are no BOL's for it.
> >>
> >> J
> >>
> >>
> >> >--Original Message--
> >> >Hi,
> >> >
> >> >Currently, I use sp_msForEachTable to reindex every
> table
> >> in one of my
> >> >databases. However I did not find any information
> >> regarding this stored
> >> >procedure in BOL. Is it safe to use, or why isn't it
> >> documented?
> >> >
> >> >EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> >> >
> >> >Yours sincerely,
> >> >
> >> >Jo Segers.
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Jo - It never was supported. It's always been an undocumented SP and so
liable to change or removal with no notice.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:edRau75JEHA.628@.TK2MSFTNGP11.phx.gbl...
> Hi Paul,
> I'm still reading the whitepaper, but the example in BOL helped a lot. I
am
> going to adjust the code in the example for use in my produktion database.
> I am just curious: why isn't sp_msForEachTable supported anymore?
> Yours sincerely,
> Jo Segers.
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> schreef in bericht
> news:O2t8tk5JEHA.2376@.tk2msftngp13.phx.gbl...
> > Hi Jo,
> >
> > If you want a way to run a command against all fragmented indexes in a
> > databases, check out example E in the Books Online for DBCC SHOWCONTIG.
> You
> > should not use sp_msForEachTable.
> >
> > I'd like to go to the root of your problem, which is wanting to reindex
> all
> > the tables in one of your database. I take it you're doing this for
> > performance reasons. The only way you'll increase performance by doing
> this
> > is if the index (clustered or non-clustered) has logical fragmentation
and
> > is used for a range scan. I'm guessing that not all the indexes you're
> > rebuilding are used in this fashion. Also, sometimes it can be better to
> run
> > DBCC INDEXDEFRAG instead of DBCC DBREINDEX.
> >
> > I recommend you read the whitepaper on this topic at
> >
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> >
> > Let me know if you have any more questions.
> >
> > Regards
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> > news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > >
> > > Currently, I use sp_msForEachTable to reindex every table in one of my
> > > databases. However I did not find any information regarding this
stored
> > > procedure in BOL. Is it safe to use, or why isn't it documented?
> > >
> > > EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> > >
> > > Yours sincerely,
> > >
> > > Jo Segers.
> > >
> > >
> >
> >
>|||Thanks for your help and time Paul, I appreciate it very much.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> schreef in bericht
news:uI%23gsb9JEHA.204@.TK2MSFTNGP10.phx.gbl...
> Jo - It never was supported. It's always been an undocumented SP and so
> liable to change or removal with no notice.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> news:edRau75JEHA.628@.TK2MSFTNGP11.phx.gbl...
> > Hi Paul,
> >
> > I'm still reading the whitepaper, but the example in BOL helped a lot. I
> am
> > going to adjust the code in the example for use in my produktion
database.
> >
> > I am just curious: why isn't sp_msForEachTable supported anymore?
> >
> > Yours sincerely,
> >
> > Jo Segers.
> >
> > "Paul S Randal [MS]" <prandal@.online.microsoft.com> schreef in bericht
> > news:O2t8tk5JEHA.2376@.tk2msftngp13.phx.gbl...
> > > Hi Jo,
> > >
> > > If you want a way to run a command against all fragmented indexes in a
> > > databases, check out example E in the Books Online for DBCC
SHOWCONTIG.
> > You
> > > should not use sp_msForEachTable.
> > >
> > > I'd like to go to the root of your problem, which is wanting to
reindex
> > all
> > > the tables in one of your database. I take it you're doing this for
> > > performance reasons. The only way you'll increase performance by doing
> > this
> > > is if the index (clustered or non-clustered) has logical fragmentation
> and
> > > is used for a range scan. I'm guessing that not all the indexes you're
> > > rebuilding are used in this fashion. Also, sometimes it can be better
to
> > run
> > > DBCC INDEXDEFRAG instead of DBCC DBREINDEX.
> > >
> > > I recommend you read the whitepaper on this topic at
> > >
> >
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> > >
> > > Let me know if you have any more questions.
> > >
> > > Regards
> > >
> > > --
> > > Paul Randal
> > > Dev Lead, Microsoft SQL Server Storage Engine
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > > "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> > > news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> > > > Hi,
> > > >
> > > > Currently, I use sp_msForEachTable to reindex every table in one of
my
> > > > databases. However I did not find any information regarding this
> stored
> > > > procedure in BOL. Is it safe to use, or why isn't it documented?
> > > >
> > > > EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> > > >
> > > > Yours sincerely,
> > > >
> > > > Jo Segers.
> > > >
> > > >
> > >
> > >
> >
> >
>
Currently, I use sp_msForEachTable to reindex every table in one of my
databases. However I did not find any information regarding this stored
procedure in BOL. Is it safe to use, or why isn't it documented?
EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
Yours sincerely,
Jo Segers.Hi,
The procedure sp_MSforeachtable executes a set of given commands against all
the user tables in the current database.
Have a look into this link for detailed information and usage.
http://www.bstsoftware.com/tsug/Nov99/sp_MSforeachtable.html
Thanks
Hari
MCDBA
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>|||Hi Jo,
The command sp_msForEachTable is no longer supported my
Microsoft. This means on the next release of SQL Server it
may or may not be part of it.
If its not supported then there are no BOL's for it.
J
>--Original Message--
>Hi,
>Currently, I use sp_msForEachTable to reindex every table
in one of my
>databases. However I did not find any information
regarding this stored
>procedure in BOL. Is it safe to use, or why isn't it
documented?
>EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
>Yours sincerely,
>Jo Segers.
>
>.
>|||Adding to the other posts:
That procedure only contains a cursor which loops all tables in the database and executes the specified
command against the tables. You can easily write such a cursor yourself. The procedure is likely to work the
same way in future service packs of SQL2K, and when Yukon comes, you want to look at these things anyhow.
Still, it is not documented, not supported, use at own "risk", which only you can assess.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>|||Thanks,
Do you know if there is a similar stored procedure that is supported?
Jo.
"Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
> Hi Jo,
> The command sp_msForEachTable is no longer supported my
> Microsoft. This means on the next release of SQL Server it
> may or may not be part of it.
> If its not supported then there are no BOL's for it.
> J
>
> >--Original Message--
> >Hi,
> >
> >Currently, I use sp_msForEachTable to reindex every table
> in one of my
> >databases. However I did not find any information
> regarding this stored
> >procedure in BOL. Is it safe to use, or why isn't it
> documented?
> >
> >EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> >
> >Yours sincerely,
> >
> >Jo Segers.
> >
> >
> >.
> >|||I'm sure there exists such on the Net, but if they are free, I doubt that you will have formal support form
the one who wrote it. AFAIK, there is no such in SQL Server. But, again, it is not difficult to write one
yourself, using a cursor, if you have a little bit if TSQL programming experience.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message news:%23lKZD$4JEHA.2624@.TK2MSFTNGP09.phx.gbl...
> Thanks,
> Do you know if there is a similar stored procedure that is supported?
> Jo.
> "Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
> news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
> > Hi Jo,
> >
> > The command sp_msForEachTable is no longer supported my
> > Microsoft. This means on the next release of SQL Server it
> > may or may not be part of it.
> >
> > If its not supported then there are no BOL's for it.
> >
> > J
> >
> >
> > >--Original Message--
> > >Hi,
> > >
> > >Currently, I use sp_msForEachTable to reindex every table
> > in one of my
> > >databases. However I did not find any information
> > regarding this stored
> > >procedure in BOL. Is it safe to use, or why isn't it
> > documented?
> > >
> > >EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> > >
> > >Yours sincerely,
> > >
> > >Jo Segers.
> > >
> > >
> > >.
> > >
>|||Ok,
Thanks for the information, I will write one myself. I wonder why they
didn't implement this sp in SQLserver 2000 because it is quite usefull.
Thanks for the support,
Jo.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schreef
in bericht news:uYzs9B5JEHA.208@.tk2msftngp13.phx.gbl...
> I'm sure there exists such on the Net, but if they are free, I doubt that
you will have formal support form
> the one who wrote it. AFAIK, there is no such in SQL Server. But, again,
it is not difficult to write one
> yourself, using a cursor, if you have a little bit if TSQL programming
experience.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23lKZD$4JEHA.2624@.TK2MSFTNGP09.phx.gbl...
> > Thanks,
> >
> > Do you know if there is a similar stored procedure that is supported?
> >
> > Jo.
> >
> > "Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
> > news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
> > > Hi Jo,
> > >
> > > The command sp_msForEachTable is no longer supported my
> > > Microsoft. This means on the next release of SQL Server it
> > > may or may not be part of it.
> > >
> > > If its not supported then there are no BOL's for it.
> > >
> > > J
> > >
> > >
> > > >--Original Message--
> > > >Hi,
> > > >
> > > >Currently, I use sp_msForEachTable to reindex every table
> > > in one of my
> > > >databases. However I did not find any information
> > > regarding this stored
> > > >procedure in BOL. Is it safe to use, or why isn't it
> > > documented?
> > > >
> > > >EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> > > >
> > > >Yours sincerely,
> > > >
> > > >Jo Segers.
> > > >
> > > >
> > > >.
> > > >
> >
> >
>|||Hi Jo,
If you want a way to run a command against all fragmented indexes in a
databases, check out example E in the Books Online for DBCC SHOWCONTIG. You
should not use sp_msForEachTable.
I'd like to go to the root of your problem, which is wanting to reindex all
the tables in one of your database. I take it you're doing this for
performance reasons. The only way you'll increase performance by doing this
is if the index (clustered or non-clustered) has logical fragmentation and
is used for a range scan. I'm guessing that not all the indexes you're
rebuilding are used in this fashion. Also, sometimes it can be better to run
DBCC INDEXDEFRAG instead of DBCC DBREINDEX.
I recommend you read the whitepaper on this topic at
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Let me know if you have any more questions.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>|||Hi Paul,
I'm still reading the whitepaper, but the example in BOL helped a lot. I am
going to adjust the code in the example for use in my produktion database.
I am just curious: why isn't sp_msForEachTable supported anymore?
Yours sincerely,
Jo Segers.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> schreef in bericht
news:O2t8tk5JEHA.2376@.tk2msftngp13.phx.gbl...
> Hi Jo,
> If you want a way to run a command against all fragmented indexes in a
> databases, check out example E in the Books Online for DBCC SHOWCONTIG.
You
> should not use sp_msForEachTable.
> I'd like to go to the root of your problem, which is wanting to reindex
all
> the tables in one of your database. I take it you're doing this for
> performance reasons. The only way you'll increase performance by doing
this
> is if the index (clustered or non-clustered) has logical fragmentation and
> is used for a range scan. I'm guessing that not all the indexes you're
> rebuilding are used in this fashion. Also, sometimes it can be better to
run
> DBCC INDEXDEFRAG instead of DBCC DBREINDEX.
> I recommend you read the whitepaper on this topic at
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> Let me know if you have any more questions.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > Currently, I use sp_msForEachTable to reindex every table in one of my
> > databases. However I did not find any information regarding this stored
> > procedure in BOL. Is it safe to use, or why isn't it documented?
> >
> > EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> >
> > Yours sincerely,
> >
> > Jo Segers.
> >
> >
>|||As far as I know there is no Microsoft official version
that does the same, sorry.
However you can get all the user tables from your database
by doing
select Name from sysobjects where xtype = 'U'
J
>--Original Message--
>Thanks,
>Do you know if there is a similar stored procedure that
is supported?
>Jo.
>"Julie" <anonymous@.discussions.microsoft.com> schreef in
bericht
>news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
>> Hi Jo,
>> The command sp_msForEachTable is no longer supported my
>> Microsoft. This means on the next release of SQL Server
it
>> may or may not be part of it.
>> If its not supported then there are no BOL's for it.
>> J
>>
>> >--Original Message--
>> >Hi,
>> >
>> >Currently, I use sp_msForEachTable to reindex every
table
>> in one of my
>> >databases. However I did not find any information
>> regarding this stored
>> >procedure in BOL. Is it safe to use, or why isn't it
>> documented?
>> >
>> >EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
>> >
>> >Yours sincerely,
>> >
>> >Jo Segers.
>> >
>> >
>> >.
>> >
>
>.
>|||Thanks for the help.
"Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
news:216f01c427a7$9b1e6a60$a601280a@.phx.gbl...
> As far as I know there is no Microsoft official version
> that does the same, sorry.
> However you can get all the user tables from your database
> by doing
> select Name from sysobjects where xtype = 'U'
> J
>
> >--Original Message--
> >Thanks,
> >
> >Do you know if there is a similar stored procedure that
> is supported?
> >
> >Jo.
> >
> >"Julie" <anonymous@.discussions.microsoft.com> schreef in
> bericht
> >news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
> >> Hi Jo,
> >>
> >> The command sp_msForEachTable is no longer supported my
> >> Microsoft. This means on the next release of SQL Server
> it
> >> may or may not be part of it.
> >>
> >> If its not supported then there are no BOL's for it.
> >>
> >> J
> >>
> >>
> >> >--Original Message--
> >> >Hi,
> >> >
> >> >Currently, I use sp_msForEachTable to reindex every
> table
> >> in one of my
> >> >databases. However I did not find any information
> >> regarding this stored
> >> >procedure in BOL. Is it safe to use, or why isn't it
> >> documented?
> >> >
> >> >EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> >> >
> >> >Yours sincerely,
> >> >
> >> >Jo Segers.
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||Jo - It never was supported. It's always been an undocumented SP and so
liable to change or removal with no notice.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:edRau75JEHA.628@.TK2MSFTNGP11.phx.gbl...
> Hi Paul,
> I'm still reading the whitepaper, but the example in BOL helped a lot. I
am
> going to adjust the code in the example for use in my produktion database.
> I am just curious: why isn't sp_msForEachTable supported anymore?
> Yours sincerely,
> Jo Segers.
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> schreef in bericht
> news:O2t8tk5JEHA.2376@.tk2msftngp13.phx.gbl...
> > Hi Jo,
> >
> > If you want a way to run a command against all fragmented indexes in a
> > databases, check out example E in the Books Online for DBCC SHOWCONTIG.
> You
> > should not use sp_msForEachTable.
> >
> > I'd like to go to the root of your problem, which is wanting to reindex
> all
> > the tables in one of your database. I take it you're doing this for
> > performance reasons. The only way you'll increase performance by doing
> this
> > is if the index (clustered or non-clustered) has logical fragmentation
and
> > is used for a range scan. I'm guessing that not all the indexes you're
> > rebuilding are used in this fashion. Also, sometimes it can be better to
> run
> > DBCC INDEXDEFRAG instead of DBCC DBREINDEX.
> >
> > I recommend you read the whitepaper on this topic at
> >
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> >
> > Let me know if you have any more questions.
> >
> > Regards
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> > news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > >
> > > Currently, I use sp_msForEachTable to reindex every table in one of my
> > > databases. However I did not find any information regarding this
stored
> > > procedure in BOL. Is it safe to use, or why isn't it documented?
> > >
> > > EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> > >
> > > Yours sincerely,
> > >
> > > Jo Segers.
> > >
> > >
> >
> >
>|||Thanks for your help and time Paul, I appreciate it very much.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> schreef in bericht
news:uI%23gsb9JEHA.204@.TK2MSFTNGP10.phx.gbl...
> Jo - It never was supported. It's always been an undocumented SP and so
> liable to change or removal with no notice.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> news:edRau75JEHA.628@.TK2MSFTNGP11.phx.gbl...
> > Hi Paul,
> >
> > I'm still reading the whitepaper, but the example in BOL helped a lot. I
> am
> > going to adjust the code in the example for use in my produktion
database.
> >
> > I am just curious: why isn't sp_msForEachTable supported anymore?
> >
> > Yours sincerely,
> >
> > Jo Segers.
> >
> > "Paul S Randal [MS]" <prandal@.online.microsoft.com> schreef in bericht
> > news:O2t8tk5JEHA.2376@.tk2msftngp13.phx.gbl...
> > > Hi Jo,
> > >
> > > If you want a way to run a command against all fragmented indexes in a
> > > databases, check out example E in the Books Online for DBCC
SHOWCONTIG.
> > You
> > > should not use sp_msForEachTable.
> > >
> > > I'd like to go to the root of your problem, which is wanting to
reindex
> > all
> > > the tables in one of your database. I take it you're doing this for
> > > performance reasons. The only way you'll increase performance by doing
> > this
> > > is if the index (clustered or non-clustered) has logical fragmentation
> and
> > > is used for a range scan. I'm guessing that not all the indexes you're
> > > rebuilding are used in this fashion. Also, sometimes it can be better
to
> > run
> > > DBCC INDEXDEFRAG instead of DBCC DBREINDEX.
> > >
> > > I recommend you read the whitepaper on this topic at
> > >
> >
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> > >
> > > Let me know if you have any more questions.
> > >
> > > Regards
> > >
> > > --
> > > Paul Randal
> > > Dev Lead, Microsoft SQL Server Storage Engine
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > > "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> > > news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> > > > Hi,
> > > >
> > > > Currently, I use sp_msForEachTable to reindex every table in one of
my
> > > > databases. However I did not find any information regarding this
> stored
> > > > procedure in BOL. Is it safe to use, or why isn't it documented?
> > > >
> > > > EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> > > >
> > > > Yours sincerely,
> > > >
> > > > Jo Segers.
> > > >
> > > >
> > >
> > >
> >
> >
>
Database maintenance using DBCC DBREINDEX
Hi Jo,
The command sp_msForEachTable is no longer supported my
Microsoft. This means on the next release of SQL Server it
may or may not be part of it.
If its not supported then there are no BOL's for it.
J
>--Original Message--
>Hi,
>Currently, I use sp_msForEachTable to reindex every table
in one of my
>databases. However I did not find any information
regarding this stored
>procedure in BOL. Is it safe to use, or why isn't it
documented?
>EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
>Yours sincerely,
>Jo Segers.
>
>.
>Thanks,
Do you know if there is a similar stored procedure that is supported?
Jo.
"Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...[vbcol=seagreen]
> Hi Jo,
> The command sp_msForEachTable is no longer supported my
> Microsoft. This means on the next release of SQL Server it
> may or may not be part of it.
> If its not supported then there are no BOL's for it.
> J
>
> in one of my
> regarding this stored
> documented?|||I'm sure there exists such on the Net, but if they are free, I doubt that yo
u will have formal support form
the one who wrote it. AFAIK, there is no such in SQL Server. But, again, it
is not difficult to write one
yourself, using a cursor, if you have a little bit if TSQL programming exper
ience.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message news:%23lKZD$4JEHA.2624@.TK2MSFTNGP09.ph
x.gbl...
> Thanks,
> Do you know if there is a similar stored procedure that is supported?
> Jo.
> "Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
> news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
>|||Ok,
Thanks for the information, I will write one myself. I wonder why they
didn't implement this sp in SQLserver 2000 because it is quite usefull.
Thanks for the support,
Jo.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schreef
in bericht news:uYzs9B5JEHA.208@.tk2msftngp13.phx.gbl...
> I'm sure there exists such on the Net, but if they are free, I doubt that
you will have formal support form
> the one who wrote it. AFAIK, there is no such in SQL Server. But, again,
it is not difficult to write one
> yourself, using a cursor, if you have a little bit if TSQL programming
experience.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23lKZD$4JEHA.2624@.TK2MSFTNGP09.phx.gbl...
>|||As far as I know there is no Microsoft official version
that does the same, sorry.
However you can get all the user tables from your database
by doing
select Name from sysobjects where xtype = 'U'
J
>--Original Message--
>Thanks,
>Do you know if there is a similar stored procedure that
is supported?
>Jo.
>"Julie" <anonymous@.discussions.microsoft.com> schreef in
bericht
>news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
it[vbcol=seagreen]
table[vbcol=seagreen]
>
>.
>|||Thanks for the help.
"Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
news:216f01c427a7$9b1e6a60$a601280a@.phx.gbl...[vbcol=seagreen]
> As far as I know there is no Microsoft official version
> that does the same, sorry.
> However you can get all the user tables from your database
> by doing
> select Name from sysobjects where xtype = 'U'
> J
>
> is supported?
> bericht
> it
> table
The command sp_msForEachTable is no longer supported my
Microsoft. This means on the next release of SQL Server it
may or may not be part of it.
If its not supported then there are no BOL's for it.
J
>--Original Message--
>Hi,
>Currently, I use sp_msForEachTable to reindex every table
in one of my
>databases. However I did not find any information
regarding this stored
>procedure in BOL. Is it safe to use, or why isn't it
documented?
>EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
>Yours sincerely,
>Jo Segers.
>
>.
>Thanks,
Do you know if there is a similar stored procedure that is supported?
Jo.
"Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...[vbcol=seagreen]
> Hi Jo,
> The command sp_msForEachTable is no longer supported my
> Microsoft. This means on the next release of SQL Server it
> may or may not be part of it.
> If its not supported then there are no BOL's for it.
> J
>
> in one of my
> regarding this stored
> documented?|||I'm sure there exists such on the Net, but if they are free, I doubt that yo
u will have formal support form
the one who wrote it. AFAIK, there is no such in SQL Server. But, again, it
is not difficult to write one
yourself, using a cursor, if you have a little bit if TSQL programming exper
ience.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message news:%23lKZD$4JEHA.2624@.TK2MSFTNGP09.ph
x.gbl...
> Thanks,
> Do you know if there is a similar stored procedure that is supported?
> Jo.
> "Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
> news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
>|||Ok,
Thanks for the information, I will write one myself. I wonder why they
didn't implement this sp in SQLserver 2000 because it is quite usefull.
Thanks for the support,
Jo.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schreef
in bericht news:uYzs9B5JEHA.208@.tk2msftngp13.phx.gbl...
> I'm sure there exists such on the Net, but if they are free, I doubt that
you will have formal support form
> the one who wrote it. AFAIK, there is no such in SQL Server. But, again,
it is not difficult to write one
> yourself, using a cursor, if you have a little bit if TSQL programming
experience.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23lKZD$4JEHA.2624@.TK2MSFTNGP09.phx.gbl...
>|||As far as I know there is no Microsoft official version
that does the same, sorry.
However you can get all the user tables from your database
by doing
select Name from sysobjects where xtype = 'U'
J
>--Original Message--
>Thanks,
>Do you know if there is a similar stored procedure that
is supported?
>Jo.
>"Julie" <anonymous@.discussions.microsoft.com> schreef in
bericht
>news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
it[vbcol=seagreen]
table[vbcol=seagreen]
>
>.
>|||Thanks for the help.
"Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
news:216f01c427a7$9b1e6a60$a601280a@.phx.gbl...[vbcol=seagreen]
> As far as I know there is no Microsoft official version
> that does the same, sorry.
> However you can get all the user tables from your database
> by doing
> select Name from sysobjects where xtype = 'U'
> J
>
> is supported?
> bericht
> it
> table
Database maintenance using DBCC DBREINDEX
Hi,
Currently, I use sp_msForEachTable to reindex every table in one of my
databases. However I did not find any information regarding this stored
procedure in BOL. Is it safe to use, or why isn't it documented?
EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
Yours sincerely,
Jo Segers.Hi,
The procedure sp_MSforeachtable executes a set of given commands against all
the user tables in the current database.
Have a look into this link for detailed information and usage.
http://www.bstsoftware.com/tsug/Nov...reachtable.html
Thanks
Hari
MCDBA
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>|||Adding to the other posts:
That procedure only contains a cursor which loops all tables in the database
and executes the specified
command against the tables. You can easily write such a cursor yourself. The
procedure is likely to work the
same way in future service packs of SQL2K, and when Yukon comes, you want to
look at these things anyhow.
Still, it is not documented, not supported, use at own "risk", which only yo
u can assess.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.
gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>|||Hi Jo,
If you want a way to run a command against all fragmented indexes in a
databases, check out example E in the Books Online for DBCC SHOWCONTIG. You
should not use sp_msForEachTable.
I'd like to go to the root of your problem, which is wanting to reindex all
the tables in one of your database. I take it you're doing this for
performance reasons. The only way you'll increase performance by doing this
is if the index (clustered or non-clustered) has logical fragmentation and
is used for a range scan. I'm guessing that not all the indexes you're
rebuilding are used in this fashion. Also, sometimes it can be better to run
DBCC INDEXDEFRAG instead of DBCC DBREINDEX.
I recommend you read the whitepaper on this topic at
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Let me know if you have any more questions.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>|||Hi Paul,
I'm still reading the whitepaper, but the example in BOL helped a lot. I am
going to adjust the code in the example for use in my produktion database.
I am just curious: why isn't sp_msForEachTable supported anymore?
Yours sincerely,
Jo Segers.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> schreef in bericht
news:O2t8tk5JEHA.2376@.tk2msftngp13.phx.gbl...
> Hi Jo,
> If you want a way to run a command against all fragmented indexes in a
> databases, check out example E in the Books Online for DBCC SHOWCONTIG.
You
> should not use sp_msForEachTable.
> I'd like to go to the root of your problem, which is wanting to reindex
all
> the tables in one of your database. I take it you're doing this for
> performance reasons. The only way you'll increase performance by doing
this
> is if the index (clustered or non-clustered) has logical fragmentation and
> is used for a range scan. I'm guessing that not all the indexes you're
> rebuilding are used in this fashion. Also, sometimes it can be better to
run
> DBCC INDEXDEFRAG instead of DBCC DBREINDEX.
> I recommend you read the whitepaper on this topic at
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
> Let me know if you have any more questions.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
>|||Jo - It never was supported. It's always been an undocumented SP and so
liable to change or removal with no notice.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:edRau75JEHA.628@.TK2MSFTNGP11.phx.gbl...
> Hi Paul,
> I'm still reading the whitepaper, but the example in BOL helped a lot. I
am
> going to adjust the code in the example for use in my produktion database.
> I am just curious: why isn't sp_msForEachTable supported anymore?
> Yours sincerely,
> Jo Segers.
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> schreef in bericht
> news:O2t8tk5JEHA.2376@.tk2msftngp13.phx.gbl...
> You
> all
> this
and[vbcol=seagreen]
> run
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
> rights.
stored[vbcol=seagreen]
>|||Thanks for your help and time Paul, I appreciate it very much.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> schreef in bericht
news:uI%23gsb9JEHA.204@.TK2MSFTNGP10.phx.gbl...
> Jo - It never was supported. It's always been an undocumented SP and so
> liable to change or removal with no notice.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> news:edRau75JEHA.628@.TK2MSFTNGP11.phx.gbl...
> am
database.[vbcol=seagreen]
SHOWCONTIG.[vbcol=seagreen]
reindex[vbcol=seagreen]
> and
to[vbcol=seagreen]
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
my[vbcol=seagreen]
> stored
>
Currently, I use sp_msForEachTable to reindex every table in one of my
databases. However I did not find any information regarding this stored
procedure in BOL. Is it safe to use, or why isn't it documented?
EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
Yours sincerely,
Jo Segers.Hi,
The procedure sp_MSforeachtable executes a set of given commands against all
the user tables in the current database.
Have a look into this link for detailed information and usage.
http://www.bstsoftware.com/tsug/Nov...reachtable.html
Thanks
Hari
MCDBA
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>|||Adding to the other posts:
That procedure only contains a cursor which loops all tables in the database
and executes the specified
command against the tables. You can easily write such a cursor yourself. The
procedure is likely to work the
same way in future service packs of SQL2K, and when Yukon comes, you want to
look at these things anyhow.
Still, it is not documented, not supported, use at own "risk", which only yo
u can assess.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.
gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>|||Hi Jo,
If you want a way to run a command against all fragmented indexes in a
databases, check out example E in the Books Online for DBCC SHOWCONTIG. You
should not use sp_msForEachTable.
I'd like to go to the root of your problem, which is wanting to reindex all
the tables in one of your database. I take it you're doing this for
performance reasons. The only way you'll increase performance by doing this
is if the index (clustered or non-clustered) has logical fragmentation and
is used for a range scan. I'm guessing that not all the indexes you're
rebuilding are used in this fashion. Also, sometimes it can be better to run
DBCC INDEXDEFRAG instead of DBCC DBREINDEX.
I recommend you read the whitepaper on this topic at
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Let me know if you have any more questions.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>|||Hi Paul,
I'm still reading the whitepaper, but the example in BOL helped a lot. I am
going to adjust the code in the example for use in my produktion database.
I am just curious: why isn't sp_msForEachTable supported anymore?
Yours sincerely,
Jo Segers.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> schreef in bericht
news:O2t8tk5JEHA.2376@.tk2msftngp13.phx.gbl...
> Hi Jo,
> If you want a way to run a command against all fragmented indexes in a
> databases, check out example E in the Books Online for DBCC SHOWCONTIG.
You
> should not use sp_msForEachTable.
> I'd like to go to the root of your problem, which is wanting to reindex
all
> the tables in one of your database. I take it you're doing this for
> performance reasons. The only way you'll increase performance by doing
this
> is if the index (clustered or non-clustered) has logical fragmentation and
> is used for a range scan. I'm guessing that not all the indexes you're
> rebuilding are used in this fashion. Also, sometimes it can be better to
run
> DBCC INDEXDEFRAG instead of DBCC DBREINDEX.
> I recommend you read the whitepaper on this topic at
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
> Let me know if you have any more questions.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
>|||Jo - It never was supported. It's always been an undocumented SP and so
liable to change or removal with no notice.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:edRau75JEHA.628@.TK2MSFTNGP11.phx.gbl...
> Hi Paul,
> I'm still reading the whitepaper, but the example in BOL helped a lot. I
am
> going to adjust the code in the example for use in my produktion database.
> I am just curious: why isn't sp_msForEachTable supported anymore?
> Yours sincerely,
> Jo Segers.
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> schreef in bericht
> news:O2t8tk5JEHA.2376@.tk2msftngp13.phx.gbl...
> You
> all
> this
and[vbcol=seagreen]
> run
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
> rights.
stored[vbcol=seagreen]
>|||Thanks for your help and time Paul, I appreciate it very much.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> schreef in bericht
news:uI%23gsb9JEHA.204@.TK2MSFTNGP10.phx.gbl...
> Jo - It never was supported. It's always been an undocumented SP and so
> liable to change or removal with no notice.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> news:edRau75JEHA.628@.TK2MSFTNGP11.phx.gbl...
> am
database.[vbcol=seagreen]
SHOWCONTIG.[vbcol=seagreen]
reindex[vbcol=seagreen]
> and
to[vbcol=seagreen]
>
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
my[vbcol=seagreen]
> stored
>
Database Maintenance question...
I set up a Database Maintenance plan for backing 3 databases, their
transaction logs, running optimizations, and to perform integrity checks.
I've been noticing that when I don't receive a notice when my jobs have
completed via email, the Status of the jobs are in "Performing completion
actions". The jobs will not re-run until I either restart SQL server or do a
reboot. Then it works for a while, then the status gets stuck again on
"Performing completion actions".
Does anyone know what could be causing this? It's happening on the database
and transaction log backups and the optimization job. Again, the maintenance
plan is configured for 3 databases, all of which are set to "Full" for their
recovery mode, and the total size of all 3 is around 7GB.
Should I maybe create a separate maintenance plan for each individual
database?
Thank you!
Hi
This may be an issue with sending the notification itself see the thread
http://tinyurl.com/yay4yj and
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b328197&Product=sql.
You could use SMTP emails instead see
http://www.sqldev.net/xp/xpsmtp.htminstead and use the job flow logic of the
job to either send a success or failure message.
John
"Saral6978" wrote:
> I set up a Database Maintenance plan for backing 3 databases, their
> transaction logs, running optimizations, and to perform integrity checks.
> I've been noticing that when I don't receive a notice when my jobs have
> completed via email, the Status of the jobs are in "Performing completion
> actions". The jobs will not re-run until I either restart SQL server or do a
> reboot. Then it works for a while, then the status gets stuck again on
> "Performing completion actions".
> Does anyone know what could be causing this? It's happening on the database
> and transaction log backups and the optimization job. Again, the maintenance
> plan is configured for 3 databases, all of which are set to "Full" for their
> recovery mode, and the total size of all 3 is around 7GB.
> Should I maybe create a separate maintenance plan for each individual
> database?
> Thank you!
|||Thank you, John for your response. However, can you double-check the last
link you posted? It doesn't seem to find the page.
Thanks!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> This may be an issue with sending the notification itself see the thread
> http://tinyurl.com/yay4yj and
> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b328197&Product=sql.
> You could use SMTP emails instead see
> http://www.sqldev.net/xp/xpsmtp.htminstead and use the job flow logic of the
> job to either send a success or failure message.
> John
> "Saral6978" wrote:
|||Hi
There should have been a space before instead try
http://www.sqldev.net/xp/xpsmtp.htm
John
"Saral6978" wrote:
[vbcol=seagreen]
> Thank you, John for your response. However, can you double-check the last
> link you posted? It doesn't seem to find the page.
> Thanks!
> "John Bell" wrote:
|||Thank you!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> There should have been a space before instead try
> http://www.sqldev.net/xp/xpsmtp.htm
> John
> "Saral6978" wrote:
transaction logs, running optimizations, and to perform integrity checks.
I've been noticing that when I don't receive a notice when my jobs have
completed via email, the Status of the jobs are in "Performing completion
actions". The jobs will not re-run until I either restart SQL server or do a
reboot. Then it works for a while, then the status gets stuck again on
"Performing completion actions".
Does anyone know what could be causing this? It's happening on the database
and transaction log backups and the optimization job. Again, the maintenance
plan is configured for 3 databases, all of which are set to "Full" for their
recovery mode, and the total size of all 3 is around 7GB.
Should I maybe create a separate maintenance plan for each individual
database?
Thank you!
Hi
This may be an issue with sending the notification itself see the thread
http://tinyurl.com/yay4yj and
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b328197&Product=sql.
You could use SMTP emails instead see
http://www.sqldev.net/xp/xpsmtp.htminstead and use the job flow logic of the
job to either send a success or failure message.
John
"Saral6978" wrote:
> I set up a Database Maintenance plan for backing 3 databases, their
> transaction logs, running optimizations, and to perform integrity checks.
> I've been noticing that when I don't receive a notice when my jobs have
> completed via email, the Status of the jobs are in "Performing completion
> actions". The jobs will not re-run until I either restart SQL server or do a
> reboot. Then it works for a while, then the status gets stuck again on
> "Performing completion actions".
> Does anyone know what could be causing this? It's happening on the database
> and transaction log backups and the optimization job. Again, the maintenance
> plan is configured for 3 databases, all of which are set to "Full" for their
> recovery mode, and the total size of all 3 is around 7GB.
> Should I maybe create a separate maintenance plan for each individual
> database?
> Thank you!
|||Thank you, John for your response. However, can you double-check the last
link you posted? It doesn't seem to find the page.
Thanks!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> This may be an issue with sending the notification itself see the thread
> http://tinyurl.com/yay4yj and
> http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b328197&Product=sql.
> You could use SMTP emails instead see
> http://www.sqldev.net/xp/xpsmtp.htminstead and use the job flow logic of the
> job to either send a success or failure message.
> John
> "Saral6978" wrote:
|||Hi
There should have been a space before instead try
http://www.sqldev.net/xp/xpsmtp.htm
John
"Saral6978" wrote:
[vbcol=seagreen]
> Thank you, John for your response. However, can you double-check the last
> link you posted? It doesn't seem to find the page.
> Thanks!
> "John Bell" wrote:
|||Thank you!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> There should have been a space before instead try
> http://www.sqldev.net/xp/xpsmtp.htm
> John
> "Saral6978" wrote:
Database Maintenance question...
I set up a Database Maintenance plan for backing 3 databases, their
transaction logs, running optimizations, and to perform integrity checks.
I've been noticing that when I don't receive a notice when my jobs have
completed via email, the Status of the jobs are in "Performing completion
actions". The jobs will not re-run until I either restart SQL server or do
a
reboot. Then it works for a while, then the status gets stuck again on
"Performing completion actions".
Does anyone know what could be causing this? It's happening on the database
and transaction log backups and the optimization job. Again, the maintenanc
e
plan is configured for 3 databases, all of which are set to "Full" for their
recovery mode, and the total size of all 3 is around 7GB.
Should I maybe create a separate maintenance plan for each individual
database?
Thank you!Hi
This may be an issue with sending the notification itself see the thread
http://tinyurl.com/yay4yj and
http://support.microsoft.com/defaul...smtp.htminstead and use the job flow logic of the
job to either send a success or failure message.
John
"Saral6978" wrote:
> I set up a Database Maintenance plan for backing 3 databases, their
> transaction logs, running optimizations, and to perform integrity checks.
> I've been noticing that when I don't receive a notice when my jobs have
> completed via email, the Status of the jobs are in "Performing completion
> actions". The jobs will not re-run until I either restart SQL server or d
o a
> reboot. Then it works for a while, then the status gets stuck again on
> "Performing completion actions".
> Does anyone know what could be causing this? It's happening on the databa
se
> and transaction log backups and the optimization job. Again, the maintena
nce
> plan is configured for 3 databases, all of which are set to "Full" for the
ir
> recovery mode, and the total size of all 3 is around 7GB.
> Should I maybe create a separate maintenance plan for each individual
> database?
> Thank you!|||Thank you, John for your response. However, can you double-check the last
link you posted? It doesn't seem to find the page.
Thanks!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> This may be an issue with sending the notification itself see the thread
> http://tinyurl.com/yay4yj and
> http://support.microsoft.com/defaul...smtp.htminstead and use the job flow logic of t
he
> job to either send a success or failure message.
> John
> "Saral6978" wrote:
>|||Hi
There should have been a space before instead try
http://www.sqldev.net/xp/xpsmtp.htm
John
"Saral6978" wrote:
[vbcol=seagreen]
> Thank you, John for your response. However, can you double-check the last
> link you posted? It doesn't seem to find the page.
> Thanks!
> "John Bell" wrote:
>|||Thank you!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> There should have been a space before instead try
> http://www.sqldev.net/xp/xpsmtp.htm
> John
> "Saral6978" wrote:
>
transaction logs, running optimizations, and to perform integrity checks.
I've been noticing that when I don't receive a notice when my jobs have
completed via email, the Status of the jobs are in "Performing completion
actions". The jobs will not re-run until I either restart SQL server or do
a
reboot. Then it works for a while, then the status gets stuck again on
"Performing completion actions".
Does anyone know what could be causing this? It's happening on the database
and transaction log backups and the optimization job. Again, the maintenanc
e
plan is configured for 3 databases, all of which are set to "Full" for their
recovery mode, and the total size of all 3 is around 7GB.
Should I maybe create a separate maintenance plan for each individual
database?
Thank you!Hi
This may be an issue with sending the notification itself see the thread
http://tinyurl.com/yay4yj and
http://support.microsoft.com/defaul...smtp.htminstead and use the job flow logic of the
job to either send a success or failure message.
John
"Saral6978" wrote:
> I set up a Database Maintenance plan for backing 3 databases, their
> transaction logs, running optimizations, and to perform integrity checks.
> I've been noticing that when I don't receive a notice when my jobs have
> completed via email, the Status of the jobs are in "Performing completion
> actions". The jobs will not re-run until I either restart SQL server or d
o a
> reboot. Then it works for a while, then the status gets stuck again on
> "Performing completion actions".
> Does anyone know what could be causing this? It's happening on the databa
se
> and transaction log backups and the optimization job. Again, the maintena
nce
> plan is configured for 3 databases, all of which are set to "Full" for the
ir
> recovery mode, and the total size of all 3 is around 7GB.
> Should I maybe create a separate maintenance plan for each individual
> database?
> Thank you!|||Thank you, John for your response. However, can you double-check the last
link you posted? It doesn't seem to find the page.
Thanks!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> This may be an issue with sending the notification itself see the thread
> http://tinyurl.com/yay4yj and
> http://support.microsoft.com/defaul...smtp.htminstead and use the job flow logic of t
he
> job to either send a success or failure message.
> John
> "Saral6978" wrote:
>|||Hi
There should have been a space before instead try
http://www.sqldev.net/xp/xpsmtp.htm
John
"Saral6978" wrote:
[vbcol=seagreen]
> Thank you, John for your response. However, can you double-check the last
> link you posted? It doesn't seem to find the page.
> Thanks!
> "John Bell" wrote:
>|||Thank you!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> There should have been a space before instead try
> http://www.sqldev.net/xp/xpsmtp.htm
> John
> "Saral6978" wrote:
>
Database Maintenance problem
Hi again,
i was wondering if anyone has encountered this problem:
I make a database maintenance plan (through the wizard) because i want to
backup the
server files to another computer on my network
I have made a map to the other computer (as G:),
and i then go to the tab "Complete Backup" and then i select "Use this
directory" option
to select the directory for the baclup files in the other computer.
But, the listbox presented at me there, displays only the locals C:\ and D:\
drives,
not the other mapped one (G:).
I manually enter the full path name and of cource the job cannot execute.
Has anyone any solution to that ?
anthonybI use UNC paths and it works fine for me. Remember that YOU are not going
to be executing this plan it will be SQL Server Agent so G may have
absolutely no meaning to it whatsoever.
Allan
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Anthony Boudouvas" <anthonyb@.mediatel.gr> wrote in message
news:eK88Tv0VDHA.2252@.TK2MSFTNGP10.phx.gbl...
> Hi again,
> i was wondering if anyone has encountered this problem:
> I make a database maintenance plan (through the wizard) because i want to
> backup the
> server files to another computer on my network
> I have made a map to the other computer (as G:),
> and i then go to the tab "Complete Backup" and then i select "Use this
> directory" option
> to select the directory for the baclup files in the other computer.
> But, the listbox presented at me there, displays only the locals C:\ and
D:\
> drives,
> not the other mapped one (G:).
> I manually enter the full path name and of cource the job cannot execute.
> Has anyone any solution to that ?
> anthonyb
>
i was wondering if anyone has encountered this problem:
I make a database maintenance plan (through the wizard) because i want to
backup the
server files to another computer on my network
I have made a map to the other computer (as G:),
and i then go to the tab "Complete Backup" and then i select "Use this
directory" option
to select the directory for the baclup files in the other computer.
But, the listbox presented at me there, displays only the locals C:\ and D:\
drives,
not the other mapped one (G:).
I manually enter the full path name and of cource the job cannot execute.
Has anyone any solution to that ?
anthonybI use UNC paths and it works fine for me. Remember that YOU are not going
to be executing this plan it will be SQL Server Agent so G may have
absolutely no meaning to it whatsoever.
Allan
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Anthony Boudouvas" <anthonyb@.mediatel.gr> wrote in message
news:eK88Tv0VDHA.2252@.TK2MSFTNGP10.phx.gbl...
> Hi again,
> i was wondering if anyone has encountered this problem:
> I make a database maintenance plan (through the wizard) because i want to
> backup the
> server files to another computer on my network
> I have made a map to the other computer (as G:),
> and i then go to the tab "Complete Backup" and then i select "Use this
> directory" option
> to select the directory for the baclup files in the other computer.
> But, the listbox presented at me there, displays only the locals C:\ and
D:\
> drives,
> not the other mapped one (G:).
> I manually enter the full path name and of cource the job cannot execute.
> Has anyone any solution to that ?
> anthonyb
>
Database Maintenance plans log
I receive every 30 min 5 event into my event viewer application log telling
me something like this
18265 :
Log backed up: Database: UserDatabases, creation date(time):
2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of dump
devices: 1, device information: (FILE=1, TYPE=DISK:
{'H:\SQLBackup\UserDatabases\UserDatabases_tlog_20 0505120800.TRN'}).
Is there a way to remove the log to event viewer from a maintenante plan?
First its not giving anything good and its hard to find error since my event
viewer is full of those information.
Hi
You might be logging it into an Event Viewer. Just try to update the same or
try to remove the logging
best Regards,
Chandra
http://chanduas.blogspot.com/
"fatp" wrote:
> I receive every 30 min 5 event into my event viewer application log telling
> me something like this
> 18265 :
> Log backed up: Database: UserDatabases, creation date(time):
> 2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of dump
> devices: 1, device information: (FILE=1, TYPE=DISK:
> {'H:\SQLBackup\UserDatabases\UserDatabases_tlog_20 0505120800.TRN'}).
>
> Is there a way to remove the log to event viewer from a maintenante plan?
> First its not giving anything good and its hard to find error since my event
> viewer is full of those information.
>
|||Short answer: No. These entries are not written by Maint Plan nor by Agent. They are written by SQL
Server. Each time SQL Server does a backup, it will write that to the eventlog. For other error
messages, you can configure whether they should be written to the eventlog or not, but that
configuration (sp_altermessage) is ignored for backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fatp" <fatp@.discussions.microsoft.com> wrote in message
news:AC8C5172-D6CA-4E33-8F36-D7D59B822692@.microsoft.com...
> I receive every 30 min 5 event into my event viewer application log telling
> me something like this
> 18265 :
> Log backed up: Database: UserDatabases, creation date(time):
> 2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of dump
> devices: 1, device information: (FILE=1, TYPE=DISK:
> {'H:\SQLBackup\UserDatabases\UserDatabases_tlog_20 0505120800.TRN'}).
>
> Is there a way to remove the log to event viewer from a maintenante plan?
> First its not giving anything good and its hard to find error since my event
> viewer is full of those information.
>
me something like this
18265 :
Log backed up: Database: UserDatabases, creation date(time):
2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of dump
devices: 1, device information: (FILE=1, TYPE=DISK:
{'H:\SQLBackup\UserDatabases\UserDatabases_tlog_20 0505120800.TRN'}).
Is there a way to remove the log to event viewer from a maintenante plan?
First its not giving anything good and its hard to find error since my event
viewer is full of those information.
Hi
You might be logging it into an Event Viewer. Just try to update the same or
try to remove the logging
best Regards,
Chandra
http://chanduas.blogspot.com/
"fatp" wrote:
> I receive every 30 min 5 event into my event viewer application log telling
> me something like this
> 18265 :
> Log backed up: Database: UserDatabases, creation date(time):
> 2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of dump
> devices: 1, device information: (FILE=1, TYPE=DISK:
> {'H:\SQLBackup\UserDatabases\UserDatabases_tlog_20 0505120800.TRN'}).
>
> Is there a way to remove the log to event viewer from a maintenante plan?
> First its not giving anything good and its hard to find error since my event
> viewer is full of those information.
>
|||Short answer: No. These entries are not written by Maint Plan nor by Agent. They are written by SQL
Server. Each time SQL Server does a backup, it will write that to the eventlog. For other error
messages, you can configure whether they should be written to the eventlog or not, but that
configuration (sp_altermessage) is ignored for backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fatp" <fatp@.discussions.microsoft.com> wrote in message
news:AC8C5172-D6CA-4E33-8F36-D7D59B822692@.microsoft.com...
> I receive every 30 min 5 event into my event viewer application log telling
> me something like this
> 18265 :
> Log backed up: Database: UserDatabases, creation date(time):
> 2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of dump
> devices: 1, device information: (FILE=1, TYPE=DISK:
> {'H:\SQLBackup\UserDatabases\UserDatabases_tlog_20 0505120800.TRN'}).
>
> Is there a way to remove the log to event viewer from a maintenante plan?
> First its not giving anything good and its hard to find error since my event
> viewer is full of those information.
>
Sunday, March 11, 2012
Database Maintenance plans log
I receive every 30 min 5 event into my event viewer application log telling
me something like this
18265 :
Log backed up: Database: UserDatabases, creation date(time):
2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of dump
devices: 1, device information: (FILE=1, TYPE=DISK:
{'H:\SQLBackup\UserDatabases\UserDatabases_tlog_200505120800.TRN'}).
Is there a way to remove the log to event viewer from a maintenante plan?
First its not giving anything good and its hard to find error since my event
viewer is full of those information.Hi
You might be logging it into an Event Viewer. Just try to update the same or
try to remove the logging
--
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"fatp" wrote:
> I receive every 30 min 5 event into my event viewer application log telling
> me something like this
> 18265 :
> Log backed up: Database: UserDatabases, creation date(time):
> 2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of dump
> devices: 1, device information: (FILE=1, TYPE=DISK:
> {'H:\SQLBackup\UserDatabases\UserDatabases_tlog_200505120800.TRN'}).
>
> Is there a way to remove the log to event viewer from a maintenante plan?
> First its not giving anything good and its hard to find error since my event
> viewer is full of those information.
>|||Short answer: No. These entries are not written by Maint Plan nor by Agent. They are written by SQL
Server. Each time SQL Server does a backup, it will write that to the eventlog. For other error
messages, you can configure whether they should be written to the eventlog or not, but that
configuration (sp_altermessage) is ignored for backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fatp" <fatp@.discussions.microsoft.com> wrote in message
news:AC8C5172-D6CA-4E33-8F36-D7D59B822692@.microsoft.com...
> I receive every 30 min 5 event into my event viewer application log telling
> me something like this
> 18265 :
> Log backed up: Database: UserDatabases, creation date(time):
> 2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of dump
> devices: 1, device information: (FILE=1, TYPE=DISK:
> {'H:\SQLBackup\UserDatabases\UserDatabases_tlog_200505120800.TRN'}).
>
> Is there a way to remove the log to event viewer from a maintenante plan?
> First its not giving anything good and its hard to find error since my event
> viewer is full of those information.
>
me something like this
18265 :
Log backed up: Database: UserDatabases, creation date(time):
2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of dump
devices: 1, device information: (FILE=1, TYPE=DISK:
{'H:\SQLBackup\UserDatabases\UserDatabases_tlog_200505120800.TRN'}).
Is there a way to remove the log to event viewer from a maintenante plan?
First its not giving anything good and its hard to find error since my event
viewer is full of those information.Hi
You might be logging it into an Event Viewer. Just try to update the same or
try to remove the logging
--
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"fatp" wrote:
> I receive every 30 min 5 event into my event viewer application log telling
> me something like this
> 18265 :
> Log backed up: Database: UserDatabases, creation date(time):
> 2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of dump
> devices: 1, device information: (FILE=1, TYPE=DISK:
> {'H:\SQLBackup\UserDatabases\UserDatabases_tlog_200505120800.TRN'}).
>
> Is there a way to remove the log to event viewer from a maintenante plan?
> First its not giving anything good and its hard to find error since my event
> viewer is full of those information.
>|||Short answer: No. These entries are not written by Maint Plan nor by Agent. They are written by SQL
Server. Each time SQL Server does a backup, it will write that to the eventlog. For other error
messages, you can configure whether they should be written to the eventlog or not, but that
configuration (sp_altermessage) is ignored for backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fatp" <fatp@.discussions.microsoft.com> wrote in message
news:AC8C5172-D6CA-4E33-8F36-D7D59B822692@.microsoft.com...
> I receive every 30 min 5 event into my event viewer application log telling
> me something like this
> 18265 :
> Log backed up: Database: UserDatabases, creation date(time):
> 2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of dump
> devices: 1, device information: (FILE=1, TYPE=DISK:
> {'H:\SQLBackup\UserDatabases\UserDatabases_tlog_200505120800.TRN'}).
>
> Is there a way to remove the log to event viewer from a maintenante plan?
> First its not giving anything good and its hard to find error since my event
> viewer is full of those information.
>
Database Maintenance plans log
I receive every 30 min 5 event into my event viewer application log telling
me something like this
18265 :
Log backed up: Database: UserDatabases, creation date(time):
2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of dum
p
devices: 1, device information: (FILE=1, TYPE=DISK:
& #123;'H:\SQLBackup\UserDatabases\UserDat
abases_tlog_200505120800.TRN'}).
Is there a way to remove the log to event viewer from a maintenante plan?
First its not giving anything good and its hard to find error since my event
viewer is full of those information.Hi
You might be logging it into an Event Viewer. Just try to update the same or
try to remove the logging
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"fatp" wrote:
> I receive every 30 min 5 event into my event viewer application log telli
ng
> me something like this
> 18265 :
> Log backed up: Database: UserDatabases, creation date(time):
> 2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of d
ump
> devices: 1, device information: (FILE=1, TYPE=DISK:
> & #123;'H:\SQLBackup\UserDatabases\UserDat
abases_tlog_200505120800.TRN'}).
>
> Is there a way to remove the log to event viewer from a maintenante plan?
> First its not giving anything good and its hard to find error since my eve
nt
> viewer is full of those information.
>|||Short answer: No. These entries are not written by Maint Plan nor by Agent.
They are written by SQL
Server. Each time SQL Server does a backup, it will write that to the eventl
og. For other error
messages, you can configure whether they should be written to the eventlog o
r not, but that
configuration (sp_altermessage) is ignored for backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fatp" <fatp@.discussions.microsoft.com> wrote in message
news:AC8C5172-D6CA-4E33-8F36-D7D59B822692@.microsoft.com...
> I receive every 30 min 5 event into my event viewer application log tellin
g
> me something like this
> 18265 :
> Log backed up: Database: UserDatabases, creation date(time):
> 2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of d
ump
> devices: 1, device information: (FILE=1, TYPE=DISK:
> & #123;'H:\SQLBackup\UserDatabases\UserDat
abases_tlog_200505120800.TRN'}).
>
> Is there a way to remove the log to event viewer from a maintenante plan?
> First its not giving anything good and its hard to find error since my eve
nt
> viewer is full of those information.
>
me something like this
18265 :
Log backed up: Database: UserDatabases, creation date(time):
2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of dum
p
devices: 1, device information: (FILE=1, TYPE=DISK:
& #123;'H:\SQLBackup\UserDatabases\UserDat
abases_tlog_200505120800.TRN'}).
Is there a way to remove the log to event viewer from a maintenante plan?
First its not giving anything good and its hard to find error since my event
viewer is full of those information.Hi
You might be logging it into an Event Viewer. Just try to update the same or
try to remove the logging
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"fatp" wrote:
> I receive every 30 min 5 event into my event viewer application log telli
ng
> me something like this
> 18265 :
> Log backed up: Database: UserDatabases, creation date(time):
> 2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of d
ump
> devices: 1, device information: (FILE=1, TYPE=DISK:
> & #123;'H:\SQLBackup\UserDatabases\UserDat
abases_tlog_200505120800.TRN'}).
>
> Is there a way to remove the log to event viewer from a maintenante plan?
> First its not giving anything good and its hard to find error since my eve
nt
> viewer is full of those information.
>|||Short answer: No. These entries are not written by Maint Plan nor by Agent.
They are written by SQL
Server. Each time SQL Server does a backup, it will write that to the eventl
og. For other error
messages, you can configure whether they should be written to the eventlog o
r not, but that
configuration (sp_altermessage) is ignored for backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fatp" <fatp@.discussions.microsoft.com> wrote in message
news:AC8C5172-D6CA-4E33-8F36-D7D59B822692@.microsoft.com...
> I receive every 30 min 5 event into my event viewer application log tellin
g
> me something like this
> 18265 :
> Log backed up: Database: UserDatabases, creation date(time):
> 2003/05/31(08:14:32), first LSN: 27:167:1, last LSN: 27:167:1, number of d
ump
> devices: 1, device information: (FILE=1, TYPE=DISK:
> & #123;'H:\SQLBackup\UserDatabases\UserDat
abases_tlog_200505120800.TRN'}).
>
> Is there a way to remove the log to event viewer from a maintenante plan?
> First its not giving anything good and its hard to find error since my eve
nt
> viewer is full of those information.
>
Database Maintenance Plans for Backup
Hello
We created maintenance plans for Backup, we configured as:
1. Backup set expires after 2 days. (but we still see backup files are at the location from day one)
2. There is Overwrite and Append in backup file settings. what eaxactly overwrites means, in case we set up expire the backup set after 2 days.
please advice. Thanks, Jay
If you are using a single backup device (whether it's a tape, file location...it's the same thing), overwrite means it will replace the backup on that device with the current backup. So that device will just hold the one backup. Append means it will add the current backup to the device. That device will hold multiple backups.
If you use a single device, set it to overwrite and the backup has not expired and you have selected the option to check the expiration date of the backup, you will get an error. It is designed to prevent you from accidentally overwritting a backup that you want to keep.
-Sue
database maintenance plans for 2005 Express
Hello:
I know how to create database maintenance plans (automated backups) in SQL
Server 2000 and 2005.
But, I have never done so in SQL Server 2005 Express Edition.
I tried right-clicking on maintenance plans in 2005 Express so that I could
choose to create new plans, but no such selection is available to choose.
So, how do you create maintenance plans in SQL Server 2005 Express Edition?
Thanks!
childofthe1980s
Express doesn't come with SQL Agent. Have a look at these:
http://blogs.msdn.com/rogerwolterblog/archive/2006/04/13/575974.aspx
Backing up Express
Automating Database maintenance in SQL 2005 Express Edition Part I
http://www.sqldbatips.com/showarticle.asp?ID=27
Automating Database maintenance in SQL 2005 Express Edition Part II
http://www.sqldbatips.com/showarticle.asp?ID=29
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:2FC46958-D2C0-4120-B6C1-583FCE607044@.microsoft.com...
> Hello:
> I know how to create database maintenance plans (automated backups) in SQL
> Server 2000 and 2005.
> But, I have never done so in SQL Server 2005 Express Edition.
> I tried right-clicking on maintenance plans in 2005 Express so that I
> could
> choose to create new plans, but no such selection is available to choose.
> So, how do you create maintenance plans in SQL Server 2005 Express
> Edition?
> Thanks!
> childofthe1980s
I know how to create database maintenance plans (automated backups) in SQL
Server 2000 and 2005.
But, I have never done so in SQL Server 2005 Express Edition.
I tried right-clicking on maintenance plans in 2005 Express so that I could
choose to create new plans, but no such selection is available to choose.
So, how do you create maintenance plans in SQL Server 2005 Express Edition?
Thanks!
childofthe1980s
Express doesn't come with SQL Agent. Have a look at these:
http://blogs.msdn.com/rogerwolterblog/archive/2006/04/13/575974.aspx
Backing up Express
Automating Database maintenance in SQL 2005 Express Edition Part I
http://www.sqldbatips.com/showarticle.asp?ID=27
Automating Database maintenance in SQL 2005 Express Edition Part II
http://www.sqldbatips.com/showarticle.asp?ID=29
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:2FC46958-D2C0-4120-B6C1-583FCE607044@.microsoft.com...
> Hello:
> I know how to create database maintenance plans (automated backups) in SQL
> Server 2000 and 2005.
> But, I have never done so in SQL Server 2005 Express Edition.
> I tried right-clicking on maintenance plans in 2005 Express so that I
> could
> choose to create new plans, but no such selection is available to choose.
> So, how do you create maintenance plans in SQL Server 2005 Express
> Edition?
> Thanks!
> childofthe1980s
database maintenance plans for 2005 Express
Hello:
I know how to create database maintenance plans (automated backups) in SQL
Server 2000 and 2005.
But, I have never done so in SQL Server 2005 Express Edition.
I tried right-clicking on maintenance plans in 2005 Express so that I could
choose to create new plans, but no such selection is available to choose.
So, how do you create maintenance plans in SQL Server 2005 Express Edition?
Thanks!
childofthe1980sExpress doesn't come with SQL Agent. Have a look at these:
http://blogs.msdn.com/rogerwolterbl.../13/575974.aspx
Backing up Express
Automating Database maintenance in SQL 2005 Express Edition Part I
http://www.sqldbatips.com/showarticle.asp?ID=27
Automating Database maintenance in SQL 2005 Express Edition Part II
http://www.sqldbatips.com/showarticle.asp?ID=29
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:2FC46958-D2C0-4120-B6C1-583FCE607044@.microsoft.com...
> Hello:
> I know how to create database maintenance plans (automated backups) in SQL
> Server 2000 and 2005.
> But, I have never done so in SQL Server 2005 Express Edition.
> I tried right-clicking on maintenance plans in 2005 Express so that I
> could
> choose to create new plans, but no such selection is available to choose.
> So, how do you create maintenance plans in SQL Server 2005 Express
> Edition?
> Thanks!
> childofthe1980s
I know how to create database maintenance plans (automated backups) in SQL
Server 2000 and 2005.
But, I have never done so in SQL Server 2005 Express Edition.
I tried right-clicking on maintenance plans in 2005 Express so that I could
choose to create new plans, but no such selection is available to choose.
So, how do you create maintenance plans in SQL Server 2005 Express Edition?
Thanks!
childofthe1980sExpress doesn't come with SQL Agent. Have a look at these:
http://blogs.msdn.com/rogerwolterbl.../13/575974.aspx
Backing up Express
Automating Database maintenance in SQL 2005 Express Edition Part I
http://www.sqldbatips.com/showarticle.asp?ID=27
Automating Database maintenance in SQL 2005 Express Edition Part II
http://www.sqldbatips.com/showarticle.asp?ID=29
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:2FC46958-D2C0-4120-B6C1-583FCE607044@.microsoft.com...
> Hello:
> I know how to create database maintenance plans (automated backups) in SQL
> Server 2000 and 2005.
> But, I have never done so in SQL Server 2005 Express Edition.
> I tried right-clicking on maintenance plans in 2005 Express so that I
> could
> choose to create new plans, but no such selection is available to choose.
> So, how do you create maintenance plans in SQL Server 2005 Express
> Edition?
> Thanks!
> childofthe1980s
database maintenance plans for 2005 Express
Hello:
I know how to create database maintenance plans (automated backups) in SQL
Server 2000 and 2005.
But, I have never done so in SQL Server 2005 Express Edition.
I tried right-clicking on maintenance plans in 2005 Express so that I could
choose to create new plans, but no such selection is available to choose.
So, how do you create maintenance plans in SQL Server 2005 Express Edition?
Thanks!
childofthe1980sExpress doesn't come with SQL Agent. Have a look at these:
http://blogs.msdn.com/rogerwolterblog/archive/2006/04/13/575974.aspx
Backing up Express
Automating Database maintenance in SQL 2005 Express Edition Part I
http://www.sqldbatips.com/showarticle.asp?ID=27
Automating Database maintenance in SQL 2005 Express Edition Part II
http://www.sqldbatips.com/showarticle.asp?ID=29
--
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:2FC46958-D2C0-4120-B6C1-583FCE607044@.microsoft.com...
> Hello:
> I know how to create database maintenance plans (automated backups) in SQL
> Server 2000 and 2005.
> But, I have never done so in SQL Server 2005 Express Edition.
> I tried right-clicking on maintenance plans in 2005 Express so that I
> could
> choose to create new plans, but no such selection is available to choose.
> So, how do you create maintenance plans in SQL Server 2005 Express
> Edition?
> Thanks!
> childofthe1980s
I know how to create database maintenance plans (automated backups) in SQL
Server 2000 and 2005.
But, I have never done so in SQL Server 2005 Express Edition.
I tried right-clicking on maintenance plans in 2005 Express so that I could
choose to create new plans, but no such selection is available to choose.
So, how do you create maintenance plans in SQL Server 2005 Express Edition?
Thanks!
childofthe1980sExpress doesn't come with SQL Agent. Have a look at these:
http://blogs.msdn.com/rogerwolterblog/archive/2006/04/13/575974.aspx
Backing up Express
Automating Database maintenance in SQL 2005 Express Edition Part I
http://www.sqldbatips.com/showarticle.asp?ID=27
Automating Database maintenance in SQL 2005 Express Edition Part II
http://www.sqldbatips.com/showarticle.asp?ID=29
--
Andrew J. Kelly SQL MVP
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:2FC46958-D2C0-4120-B6C1-583FCE607044@.microsoft.com...
> Hello:
> I know how to create database maintenance plans (automated backups) in SQL
> Server 2000 and 2005.
> But, I have never done so in SQL Server 2005 Express Edition.
> I tried right-clicking on maintenance plans in 2005 Express so that I
> could
> choose to create new plans, but no such selection is available to choose.
> So, how do you create maintenance plans in SQL Server 2005 Express
> Edition?
> Thanks!
> childofthe1980s
Database Maintenance Plans and shrink database
I have read that having databases auto shrink can cause performance
problems, timeouts and fragmentation. I was wondering if using the "Remove
unused space from database files" from the Optimizations tab in Database
Maintenance Plans had the same affect. Should I avaoid using those two
settings?
Thanks
Yes (although maintenance plans are usually scheduled out of core business
hours). Have a look at http://www.karaszi.com/sqlserver/info_dont_shrink.asp
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Alison Blackburn" <alison.blackburn@.thalerus.com> wrote in message
news:Ohn3boEFFHA.2756@.TK2MSFTNGP15.phx.gbl...
>I have read that having databases auto shrink can cause performance
>problems, timeouts and fragmentation. I was wondering if using the "Remove
>unused space from database files" from the Optimizations tab in Database
>Maintenance Plans had the same affect. Should I avaoid using those two
>settings?
> Thanks
>
problems, timeouts and fragmentation. I was wondering if using the "Remove
unused space from database files" from the Optimizations tab in Database
Maintenance Plans had the same affect. Should I avaoid using those two
settings?
Thanks
Yes (although maintenance plans are usually scheduled out of core business
hours). Have a look at http://www.karaszi.com/sqlserver/info_dont_shrink.asp
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Alison Blackburn" <alison.blackburn@.thalerus.com> wrote in message
news:Ohn3boEFFHA.2756@.TK2MSFTNGP15.phx.gbl...
>I have read that having databases auto shrink can cause performance
>problems, timeouts and fragmentation. I was wondering if using the "Remove
>unused space from database files" from the Optimizations tab in Database
>Maintenance Plans had the same affect. Should I avaoid using those two
>settings?
> Thanks
>
Database Maintenance Plans and shrink database
I have read that having databases auto shrink can cause performance
problems, timeouts and fragmentation. I was wondering if using the "Remove
unused space from database files" from the Optimizations tab in Database
Maintenance Plans had the same affect. Should I avaoid using those two
settings?
ThanksYes (although maintenance plans are usually scheduled out of core business
hours). Have a look at http://www.karaszi.com/sqlserver/info_dont_shrink.asp
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Alison Blackburn" <alison.blackburn@.thalerus.com> wrote in message
news:Ohn3boEFFHA.2756@.TK2MSFTNGP15.phx.gbl...
>I have read that having databases auto shrink can cause performance
>problems, timeouts and fragmentation. I was wondering if using the "Remove
>unused space from database files" from the Optimizations tab in Database
>Maintenance Plans had the same affect. Should I avaoid using those two
>settings?
> Thanks
>
problems, timeouts and fragmentation. I was wondering if using the "Remove
unused space from database files" from the Optimizations tab in Database
Maintenance Plans had the same affect. Should I avaoid using those two
settings?
ThanksYes (although maintenance plans are usually scheduled out of core business
hours). Have a look at http://www.karaszi.com/sqlserver/info_dont_shrink.asp
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Alison Blackburn" <alison.blackburn@.thalerus.com> wrote in message
news:Ohn3boEFFHA.2756@.TK2MSFTNGP15.phx.gbl...
>I have read that having databases auto shrink can cause performance
>problems, timeouts and fragmentation. I was wondering if using the "Remove
>unused space from database files" from the Optimizations tab in Database
>Maintenance Plans had the same affect. Should I avaoid using those two
>settings?
> Thanks
>
Database Maintenance Plans and shrink database
I have read that having databases auto shrink can cause performance
problems, timeouts and fragmentation. I was wondering if using the "Remove
unused space from database files" from the Optimizations tab in Database
Maintenance Plans had the same affect. Should I avaoid using those two
settings?
ThanksYes (although maintenance plans are usually scheduled out of core business
hours). Have a look at http://www.karaszi.com/sqlserver/info_dont_shrink.asp
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Alison Blackburn" <alison.blackburn@.thalerus.com> wrote in message
news:Ohn3boEFFHA.2756@.TK2MSFTNGP15.phx.gbl...
>I have read that having databases auto shrink can cause performance
>problems, timeouts and fragmentation. I was wondering if using the "Remove
>unused space from database files" from the Optimizations tab in Database
>Maintenance Plans had the same affect. Should I avaoid using those two
>settings?
> Thanks
>
problems, timeouts and fragmentation. I was wondering if using the "Remove
unused space from database files" from the Optimizations tab in Database
Maintenance Plans had the same affect. Should I avaoid using those two
settings?
ThanksYes (although maintenance plans are usually scheduled out of core business
hours). Have a look at http://www.karaszi.com/sqlserver/info_dont_shrink.asp
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Alison Blackburn" <alison.blackburn@.thalerus.com> wrote in message
news:Ohn3boEFFHA.2756@.TK2MSFTNGP15.phx.gbl...
>I have read that having databases auto shrink can cause performance
>problems, timeouts and fragmentation. I was wondering if using the "Remove
>unused space from database files" from the Optimizations tab in Database
>Maintenance Plans had the same affect. Should I avaoid using those two
>settings?
> Thanks
>
Database Maintenance Plans - query?
Is there an easier way to get a listing of all your
maintenance plans, what they do, and when they are
scheduled to occur - in a multiserver environment - aside
from pulling up enterprise manager for each server and
writing it all down? I would think these would be defined
in the system information and I could query it out but I
can't find anything about it in BOL or in searching KB and
newsgroups. Help please?
Thanks,
Kevin
Just one of the many problems with maintenance plans is that some of the
data is stored in the MP system tables, some is stored in Job system tables
and some is actually parsed from the command strings in the job steps. Here
is a query to list all jobs with their schedules, descriptions etc. You
could expand it a bit to join with the MP system tables to get just the ones
associated with the MP's.
Maintenance Plan system tables
sysdbmaintplan_databases
sysdbmaintplan_history
sysdbmaintplan_jobs
sysdbmaintplans
select a.[Name],a.[Enabled] AS [J_E],
b.[Enabled] AS [S_E],
case b.freq_type
when 1 then 'Run Once'
when 4 then 'Runs Daily'
when 8 then 'Every Week'
+ case freq_interval & 2 when 2 then ' on Mondays' else '' end
+ case freq_interval & 4 when 4 then ' on Tuesday' else '' end
+ case freq_interval & 8 when 8 then ' on Wednesday' else '' end
+ case freq_interval & 16 when 16 then ' on Thursday' else '' end
+ case freq_interval & 32 when 32 then ' on Friday' else '' end
+ case freq_interval & 64 when 64 then ' on Saturday' else '' end
+ case freq_interval & 1 when 1 then ' on Sunday' else '' end
when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval)
when 32 then 'Mthly ' + case freq_relative_interval
when 1 then 'Every First '
when 2 then 'Every Second '
when 4 then 'Every Third '
when 8 then 'Every Fourth '
when 16 then 'Every Last '
end
+ case b.freq_interval
when 1 then ' on Monday'
when 2 then ' on Tuesdsay'
when 3 then ' on Wednesday'
when 4 then ' on Thursday'
when 5 then ' on Friday'
when 6 then ' on Saturday'
when 7 then ' on Sunday'
when 8 then 'Day'
when 9 then 'Week day'
when 10 then 'Weekend day'
end
when 64 then 'Startup'
when 128 then 'Idle'
else 'Err'
end as schedule,
case b.freq_subday_type
when 1 then 'Runs at:'
when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + '
seconds'
when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + '
minutes'
when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + '
hours'
end as frequency,
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6),active_start_time), 6), 1, 2) + ':' +
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6),active_start_time), 6) ,3 ,2) + ':'+
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6),active_start_time),6) ,5 ,2)
as start_at,
case freq_subday_type
when 1 then NULL
else
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6),active_end_time), 6), 1, 2) + ':' +
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6), active_end_time),6) ,3 ,2) + ':' +
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6), active_end_time),6) ,5 ,2)
end as end_at,
a.[Description]
from [msdb].[dbo].[sysjobs] AS a INNER JOIN [msdb].[dbo].[sysjobschedules]
AS b
ON a.[Job_ID] = b.[Job_ID]
ORDER BY a.[Name]
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:12ecb01c44355$738f2a60$a001280a@.phx.gbl...
> Is there an easier way to get a listing of all your
> maintenance plans, what they do, and when they are
> scheduled to occur - in a multiserver environment - aside
> from pulling up enterprise manager for each server and
> writing it all down? I would think these would be defined
> in the system information and I could query it out but I
> can't find anything about it in BOL or in searching KB and
> newsgroups. Help please?
> Thanks,
> Kevin
maintenance plans, what they do, and when they are
scheduled to occur - in a multiserver environment - aside
from pulling up enterprise manager for each server and
writing it all down? I would think these would be defined
in the system information and I could query it out but I
can't find anything about it in BOL or in searching KB and
newsgroups. Help please?
Thanks,
Kevin
Just one of the many problems with maintenance plans is that some of the
data is stored in the MP system tables, some is stored in Job system tables
and some is actually parsed from the command strings in the job steps. Here
is a query to list all jobs with their schedules, descriptions etc. You
could expand it a bit to join with the MP system tables to get just the ones
associated with the MP's.
Maintenance Plan system tables
sysdbmaintplan_databases
sysdbmaintplan_history
sysdbmaintplan_jobs
sysdbmaintplans
select a.[Name],a.[Enabled] AS [J_E],
b.[Enabled] AS [S_E],
case b.freq_type
when 1 then 'Run Once'
when 4 then 'Runs Daily'
when 8 then 'Every Week'
+ case freq_interval & 2 when 2 then ' on Mondays' else '' end
+ case freq_interval & 4 when 4 then ' on Tuesday' else '' end
+ case freq_interval & 8 when 8 then ' on Wednesday' else '' end
+ case freq_interval & 16 when 16 then ' on Thursday' else '' end
+ case freq_interval & 32 when 32 then ' on Friday' else '' end
+ case freq_interval & 64 when 64 then ' on Saturday' else '' end
+ case freq_interval & 1 when 1 then ' on Sunday' else '' end
when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval)
when 32 then 'Mthly ' + case freq_relative_interval
when 1 then 'Every First '
when 2 then 'Every Second '
when 4 then 'Every Third '
when 8 then 'Every Fourth '
when 16 then 'Every Last '
end
+ case b.freq_interval
when 1 then ' on Monday'
when 2 then ' on Tuesdsay'
when 3 then ' on Wednesday'
when 4 then ' on Thursday'
when 5 then ' on Friday'
when 6 then ' on Saturday'
when 7 then ' on Sunday'
when 8 then 'Day'
when 9 then 'Week day'
when 10 then 'Weekend day'
end
when 64 then 'Startup'
when 128 then 'Idle'
else 'Err'
end as schedule,
case b.freq_subday_type
when 1 then 'Runs at:'
when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + '
seconds'
when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + '
minutes'
when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + '
hours'
end as frequency,
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6),active_start_time), 6), 1, 2) + ':' +
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6),active_start_time), 6) ,3 ,2) + ':'+
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6),active_start_time),6) ,5 ,2)
as start_at,
case freq_subday_type
when 1 then NULL
else
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6),active_end_time), 6), 1, 2) + ':' +
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6), active_end_time),6) ,3 ,2) + ':' +
substring(right(stuff(' ', 1, 1, '000000') +
convert(varchar(6), active_end_time),6) ,5 ,2)
end as end_at,
a.[Description]
from [msdb].[dbo].[sysjobs] AS a INNER JOIN [msdb].[dbo].[sysjobschedules]
AS b
ON a.[Job_ID] = b.[Job_ID]
ORDER BY a.[Name]
Andrew J. Kelly
SQL Server MVP
"Kevin" <anonymous@.discussions.microsoft.com> wrote in message
news:12ecb01c44355$738f2a60$a001280a@.phx.gbl...
> Is there an easier way to get a listing of all your
> maintenance plans, what they do, and when they are
> scheduled to occur - in a multiserver environment - aside
> from pulling up enterprise manager for each server and
> writing it all down? I would think these would be defined
> in the system information and I could query it out but I
> can't find anything about it in BOL or in searching KB and
> newsgroups. Help please?
> Thanks,
> Kevin
Subscribe to:
Posts (Atom)