January 26, 2003 at 5:10 pm
Hello,
I need to create a script that will gather the name, size, space available, and number of tables in each db on multiple servers (about 12) grouped by server name. I also need the size and space available on the each server's D drive.
This data needs to be automatically inserted into an excel spreadsheet that is used by managerial staff (not having SQL Server knowledge/access).
Thanks in advance for your attention and help!
January 26, 2003 at 6:32 pm
Use DTS to run this query on all servers and export to a Excel sheet. Run a separate transform in serial for each server.
Or you could use linked servers and run it centrally from one server.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 29, 2003 at 8:21 am
Edited by - ub2b on 01/29/2003 08:24:40 AM
January 29, 2003 at 8:21 am
Does anyone already have script that gathers some or all of this info? I need some assistance getting started. Help would be greatly appreciated.
Thank you very much
January 29, 2003 at 10:25 am
Well I have an SP I am running on most servers, then replicate the results back.
Not quite ready for prime time though, and thanks to the WORM I'm kind busy.
I'll pull it together and see if I can post it later.
KlK, MCSE
KlK
January 31, 2003 at 7:05 am
I use linked servers to manage my other servers. You can use linked servers on one server to run sp' into a local temp table
Most SP's will allow you to run SQL.MASTER.DBO.sp????
January 31, 2003 at 1:05 pm
Have you checked out sp_spaceused in the BOL? Also, there is an 'undocumented' procedure called sp_MSforeachdb which executes up to 3 commands for every db on the system (from Guru's Guide to Transact-SQL by Ken Henderson). The two commands together might get you what you are looking for.
-SQLBill
February 3, 2003 at 5:46 am
ub2b,
I've written such a script (a while ago) but it's not yet done, it still has several errors, but it is a startingpoint.
If you feel like debugging it and returning me a result (if succesfull) please let me know (via this thread).
Steven.
February 3, 2003 at 9:24 am
You could try this - I didn't take time to add error checking or anything and just use the same routines as sp_spaceused to get database allocation.
This procedure should exist in the master database on the servers and the servers should be listed in either linked or remote
--DatabaseSummaryInfo.sql - Quick summary of Database usage
CREATE PROCEDURE DatabaseSummaryInfo (
@dbOnlysysname = NULL
) AS
DECLARE
@dbNamesysname,--Name of the database being reported
@dbSizeINT,--Total allocated size for the database
@logSizeINT,--Total size used by the logs
@usedSizeINT,--Number of pages used in the database
@pagesperMBdec(15,0),--Pages in this system per megabyte
@tblCountINT,--Number of tables in the database
@sSQLNVARCHAR(2000)--For creating the sql call
SET NOCOUNT ON
--Get the number of pages per megabyte
SELECT @pagesperMB = 1048576.0 / low
FROM spt_values
WHERE number = 1 AND type = 'E'
--Table for the output
CREATE TABLE #tmpDBInfo (
ServerNamesysname,
DBNamesysname,
DBSizeDEC(15,2) DEFAULT 0.0,
DBAvailDEC(15,2) DEFAULT 0.0,
TableCntINT DEFAULT 0
)
--Prepare to walk through the Databases
IF @dbOnly IS NOT NULL AND @dbOnly <> ''
DECLARE dbCurs CURSOR FAST_FORWARD
FOR SELECT name FROM sysdatabases
WHERE name = @dbOnly
ELSE
DECLARE dbCurs CURSOR FAST_FORWARD
FOR SELECT name FROM sysdatabases
WHERE name NOT IN ( 'master', 'msdb', 'model', 'pubs', 'tempdb', 'Northwind' )
OPEN dbCurs
FETCH NEXT FROM dbCurs INTO @dbName
--FOR EACH DB
WHILE @@FETCH_STATUS = 0
BEGIN
--Get the size of the database and log
SELECT @sSQL = 'SELECT @dbSize = SUM( CASE WHEN (status & 64) = 0' +
' THEN size ELSE 0 END ), @logSize = SUM( CASE WHEN' +
' (status & 64 = 0) THEN 0 ELSE size END ) FROM ' + @dbName +
'..sysfiles'
--Actually perform the SELECT
EXEC sp_executesql @sSQL, N'@dbSize INT OUTPUT, @logSize INT OUTPUT',
@dbSize OUTPUT, @logSize OUTPUT
--Get the amount of space used
SELECT @sSQL = 'SELECT @usedSize = SUM( reserved ) FROM ' + @dbName +
'.dbo.sysindexes WHERE indid IN ( 0, 1, 255 )'
--Perform the SELECT
EXEC sp_executesql @sSQL, N'@usedSize INT OUTPUT', @usedSize OUTPUT
--Get the number of tables
SELECT @sSQL = 'SELECT @tblCount = COUNT(*) FROM ' + @dbName +
'.dbo.sysobjects (NOLOCK) WHERE type = ''U'' AND status > 0'
--Perform the SELECT
EXEC sp_executesql @sSQL, N'@tblCount INT OUTPUT', @tblCount OUTPUT
--Save the information so far
INSERT INTO #tmpDBInfo ( ServerName, DBName, DBSize, DBAvail, TableCnt )
VALUES ( @@SERVERNAME, @dbName,
CONVERT( DEC( 15, 2 ), ( @dbSize + @logSize) / @pagesperMB ),
CONVERT( DEC( 15, 2 ), ( @dbSize - @usedSize ) / @pagesperMB ),
@tblCount)
--Try the next database
FETCH NEXT FROM dbCurs INTO @dbName
END
--Close and deallocate
CLOSE dbCurs
DEALLOCATE dbCurs
--Reveal the results
SELECT * FROM #tmpDBInfo
RETURN 0
-- NOW - you can use this routine from the machine that is going to gather the data
--ServerDBSummaryInfo.sql - Quick summary of Database usage - on all linked servers
CREATE PROCEDURE ServerDBSummaryInfo
AS
DECLARE
@srvNamesysname,--Name of the server being reported
@sSQLNVARCHAR(2000)--For creating the sql call
SET NOCOUNT ON
--Table for the output
CREATE TABLE #tmpAllInfo (
ServerNamesysname,
DBNamesysname,
DBSizeDEC(15,2) DEFAULT 0.0,
DBAvailDEC(15,2) DEFAULT 0.0,
TableCntINT DEFAULT 0
)
--Prepare to walk through the Servers
DECLARE srvCurs CURSOR FAST_FORWARD
FOR SELECT srvname FROM sysservers
OPEN srvCurs
FETCH NEXT FROM srvCurs INTO @srvName
--FOR EACH Server
WHILE @@FETCH_STATUS = 0
BEGIN
--Create the execute string
SELECT @sSQL = 'EXEC ' + @srvName + '.master.dbo.DatabaseSummaryInfo'
--Actually perform the SELECT
INSERT INTO #tmpAllInfo
EXEC sp_executesql @sSQL
--Try the next database
FETCH NEXT FROM srvCurs INTO @srvName
END
--Close and deallocate
CLOSE srvCurs
DEALLOCATE srvCurs
--Reveal the results
SELECT * FROM #tmpAllInfo
ORDER BY ServerName, DBName
RETURN 0
For whatever it's worth...
Guarddata-
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply