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.

Looking for 'upsizing' from Access/PivotCharts

Hi All!

I'm doing analysis of system performance metrics recorded from a web application performance test. I have approximately 15 tables, each with unique system and test metrics, ranging in size from 6 thousand to 6 million rows. E.g:

iostats: time, host, disk, read_bps, write_bps, ...
netstats: time, host, active_conns, xmit_errs, resets, ...
teststats: time, test_name, virtual_users, ...
timerstats: time, script_name, timer_name, elapsed_time, ...

Last year i used MS Access + pivot tables to do the analysis of this information, but Access isn't cutting it this year. It frequently goes out to lunch and never returns whenever I try to build the charts i'm looking for (most of which use PivotChart views)

I've spent a few days trying to get my head around using SSAS for this purpose, but have run into a few stumbling blocks

1 - The information in the 15 tables is loosely correlated on time of day, some have one sample per second, others one sample every thirty seconds, and still others are whenever an update decides to roll in. I'm having a very difficult time figuring out how to make a usable 'time' dimension that i can use to chart the information.

2 - I seem to constantly run into 'missing attribute value' errors when trying to build and deploy my analysis services projects that contain cubes. Frequently this seems to be due to cases where i don't have specific information on a specific dimension (e.g. host / cpu / etc) for a given time period.

3 - I'm not sure how to visualize some of the output from the cube interactions that do work. I can get a table working in the cube browser, but how do i chart that? Is the Excel AS plugin the only approach available? I tried to view the tables via access linked tables, but a) Access (2002) complains it can't modify tables against SQL 2005, and b) Access doesn't pull the entire set of data back, just the first 50k records or so.

4 - If i want to calculate disk utilization on a per disk per host basis, would that be a valid use of a dimension hierarchy?

I'm kind of running out of time. My game plan for today is to recalculate all tables to exactly one minute sample intervals and try again, hoping that the reduced row count will make things a little more smooth.

Thanks for any advice!!!

Bob

Several ideas for you:

Try to see if Office 2007 beta is going to work for you. Install Office2007 beta and try if you can fit your data sizes there.

Second. If you are using Analysis Services Excel add-in is not the only choice. You can use Excel Pivot Tables to connect to Analysis Services. You can use many other client applications to diplay Analysis Services data.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Hi Edward!

Thanks for the response. I may take a look at office 2007 today to see how it goes.