Looping through sysDatabases to perform maintenance

from sql 2000:
I need to generate some metrics on the size of transaction logs on all
databases for a given server. I was thinking I could loop through
sysDatabases to get the name of every database. However, when I try to do
something like:
...
While @.@.Fetch_Status = 0 Begin
Use @.cDBName
--Do some stuff here...
Fetch next From curDBList Into @.cDBName
End
the server throws a "Incorrect syntax near '@.cDBName'.". Is there a way
where I can dynamically specifiy the name of the DB with the USE command?
thanks in advance.mystical potato (mysticalpotato@.discussions.microsoft.com) writes:
> I need to generate some metrics on the size of transaction logs on all
> databases for a given server. I was thinking I could loop through
> sysDatabases to get the name of every database. However, when I try to do
> something like:
> ...
> While @.@.Fetch_Status = 0 Begin
> Use @.cDBName
> --Do some stuff here...
> Fetch next From curDBList Into @.cDBName
> End
> the server throws a "Incorrect syntax near '@.cDBName'.". Is there a way
> where I can dynamically specifiy the name of the DB with the USE command?
The easiest is probably to to use sp_MSforeachdb. Here is a fairly
stupid example:
sp_MSforeachdb 'SELECT ''?'', COUNT(*) FROM sysobjects'
Note that sp_MSforeachdb is not a documented function, and thus not
supported. Nevertheless, it's fairly popular.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi
A different approach may be to use the Perfmon counters to track this,
especially if you wish to pinpoint a sudden increase. A step further along
the system monitoring approach would be to look into using MOM to gather and
report this for you.
John
"mystical potato" <mysticalpotato@.discussions.microsoft.com> wrote in
message news:D5DE41B3-6065-422E-8C02-DFFE9167C213@.microsoft.com...
> from sql 2000:
> I need to generate some metrics on the size of transaction logs on all
> databases for a given server. I was thinking I could loop through
> sysDatabases to get the name of every database. However, when I try to do
> something like:
> ...
> While @.@.Fetch_Status = 0 Begin
> Use @.cDBName
> --Do some stuff here...
> Fetch next From curDBList Into @.cDBName
> End
> the server throws a "Incorrect syntax near '@.cDBName'.". Is there a way
> where I can dynamically specifiy the name of the DB with the USE command?
> thanks in advance.|||Try using:
dbcc sqlperf(logspace)
AMB
"mystical potato" wrote:

> from sql 2000:
> I need to generate some metrics on the size of transaction logs on all
> databases for a given server. I was thinking I could loop through
> sysDatabases to get the name of every database. However, when I try to do
> something like:
> ...
> While @.@.Fetch_Status = 0 Begin
> Use @.cDBName
> --Do some stuff here...
> Fetch next From curDBList Into @.cDBName
> End
> the server throws a "Incorrect syntax near '@.cDBName'.". Is there a way
> where I can dynamically specifiy the name of the DB with the USE command?
> thanks in advance.