SQL Server Performance Forum – Threads Archive

DBA Monitoring Tool

Hi, I have got around 20 Sql Servers and 500+ Databases, What i have to do is to Write an Stored Proc which will go to each of the databases in all servers and Get the Datafile size and Log file size and insert into a Temp table in Monitoring DATABASE. and this SP will be run after every 1/2 Hour. so, i need to keep in mind the speed and locks on the database aswell. Can anybody plz help me in that???????[?] Regards,
Narra

Speed shouldn’t be a problem. you can use dbcc sqlperf(logspace) for the logs which reports on all databases on a server in one go. For the datafiles either use sp_spaceused, or run Pofiler against Eneterprise Manager when it’s preparing the taskpad view and see what it does, something a bit neater I seem to remember. It will be a relatively straighforward task to collect this information in tables in a central database. I should be achievable within 30 minutes so it could be run that frequently. Tom Pullen
DBA, Oxfam GB

Thanks Tom Pullen, But how do i run "dbcc sqlperf(logspace) and sp_spaceused" on other servers from my SP, becoz these commands takes the server on which we are running those commands. My output of the SP should be somthing like below: ***************************************************************************************
ServerName DBNAME RecordDateTime FileType Size Used Free %Used %Free
———- ——– ————— ——– —- —- —– —– —–
team9 Vasu 2004-11-18 14:56 Data1 0 1 62.50 37.50
team9 Vasu 2004-11-18 14:56 Log1 0 132.81 67.19
*************************************************************************************** I hope got got my Problem!!!!

You can create stored procedures on the remote server to execute these commands, then call them from the central server. E.g. on remote server (say, SERVER2, in the pubs database):- CREATE PROCEDURE prLogSpace
AS EXECUTE(dbcc sqlperf(logspace))
GO then schedule on the central server EXEC SERVER2.pubs.dbo.prLogSpace
you can do something similar for sp_spaceused, maybe use a cursor to loop through all the databases by looking up their names in sysdatabases. I shall leave the formatting and details up to you….
Tom Pullen
DBA, Oxfam GB

You can take help of SP_MSforeachdb which undocumented stored procedure to get the details, http://www.sql-server-performance.com/ac_sql_server_2000_undocumented_sp.asp
sp_MSforeachdb
Sometimes, you need to perform the same actions for all tables in the database. You can create cursor for this purpose, or you can also use sp_MSforeachdb stored procedure to accomplish the same goal with less work. For example, you can use this stored procedure to run a CHECKDB for all the databases on your server. EXEC sp_MSforeachdb @command1="print ‘?’ DBCC CHECKDB (‘?’)"
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I do something similar to this with a perl script. It uses a file in which I have a list of the servers to be monitored. Within a for loop, the script makes a connection to each server, runs a select on sysprocesses, and writes the result to a table on another server at the end. [In this case I am looking to see which servers are available, and how many connections are on each server.] You could simply replace the "select spid, dbid from sysprocesses " with a command of your choice. All you need to maintain is the file with the list of servers in, so that all of your servers are monitored. =============================================================================================== #!d:/perl/bin/perl.exe -w # for documentation on MSSQL:<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />Blib see use MSSQL:<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />Blib; use strict; # basic error handling sub error_handler; dberrhandle ("main::error_handler"); # Get the filenames from the file serverlist.txt # Generate Filehandle – change as required my $infile ="d:/apache/cgi-bin/serverlist.txt"; open (OF, "$infile") || die "$!"; # Get row data elements into array my @datain=<OF>; # Close Filehandle close OF; # How many line are we dealing with – may be useful later my $linecount [email protected] ; my @countrows; #use this a a simple error check my @serveravail; # a simple counter my $counter =0; #set up connection parameters my $status = dbsetlogintime(10); # these are for storing info returned from the server # have as many of these as you need my $spid; my $dbid; # Log into each server for ($counter=0; $counter < $linecount; $counter++){ $serveravail[$counter]=1; chomp $datain[$counter]; my $server =$datain[$counter]; my $dbh = MSSQL:<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />Blib->dblogin(‘MonitorUser’,"MonitorPassword", $server, ‘master’); if ($serveravail[$counter]==1) { # use your choice of command here # maybe you want to query sysfiles $dbh->dbcmd("SELECT spid, dbid FROM sysprocesses"); $dbh->dbsqlexec; $dbh->dbresults; # run trough all the rows to extract any info you need while (my %result = $dbh->dbnextrow(1)){ # you can access the fields in your query like this: # $spid=$result{spid}; # $dbid=$result{dbid}; } # you don’t need this, bit I am interested in how many connections # there are on each server $countrows[$counter] = $dbh->DBCOUNT; # does what is says on the can $dbh->dbclose; } } sub error_handler { # simply set notavail flag $serveravail[$counter]=99; } my $srvstatus="NA"; my $conns =0; # write values to monitor db my $dbh2 = MSSQL:<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />Blib->dblogin(‘MonitorUser’,"MonitorPassword","MonitorServer", ‘MonitorDB’); $dbh2->dbcmd("delete from Monitor.dbo.tblStatus"); $dbh2->dbsqlexec; $dbh2->dbresults; # I knew it would be a good idea to know how many servers we are dealing with for ($counter=0; $counter < $linecount; $counter++){ my $server =’"’.$datain[$counter].’"’; if ($serveravail[$counter]==99){ $srvstatus=’"DOWN"’; $conns=’"na"’; } else { $srvstatus=’"UP"’; $conns=$countrows[$counter]; } $dbh2->dbcmd("INSERT into Monitor.dbo.tblStatus VALUES (".$server.", ".$srvstatus.", ".$conns.")"); $dbh2->dbsqlexec; $dbh2->dbresults; } $dbh2->dbclose; =========================================================================================================== You will need to create a login on each server to be monitored called monitoruser or similar, and make sure it has read access to the relevant tables (sysprocesses, sysfiles etc…) I have other scripts do display the results as web pages through cgi, but this is a good gambit for collecting your database metadata.

But those 20 Servers are my customer servers, and i’m monitoring those servers from one of my central server "Team9" and thr is a server table which consists of this 20 server name ,now My main goal is, i’ll run a SP or a Query from "TEAM9", it should pick up the the server name from server table and get data from the respective servers. Plz HELP!!!!

You need to execute the above script using OSQL session to connect all the servers and schedule it as a job from Team9 server. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

DECLARE ServerCursor CURSOR
FOR SELECT ServerName FROM ##ServerNameTemp OPEN ServerCursor FETCH NEXT FROM ServerCursor INTO @ServerName WHILE @@FETCH_STATUS = 0 BEGIN
SET @CmdStr = ‘osql -S ‘ + @ServerName + ‘ -E -n -h-1 -i"d:IT_DatabaseGetdblist.sql"’ INSERT INTO ##ServerDBNameTemp EXEC master..xp_cmdshell @CmdStr DELETE FROM ##ServerDBNameTemp
WHERE dbname IS NULL
OR SUBSTRING (dbname, 2, 1) = ‘ ‘ INSERT INTO DatabaseInfoMaster (ServerName,DatabaseName)
SELECT @ServerName,ltrim(rtrim(DBname)) from ##ServerDBNameTemp
–UPDATE DatabaseInfoMaster FETCH NEXT FROM ServerCursor INTO @ServerName END CLOSE Servercursor
DEALLOCATE ServerCursor
—————————— Satya, The problem is with output,it is not foramatted so i’m not able to take the output into table

]]>