May 13, 2003 at 10:20 am
Hello,
I would greatly appreciate some help with this script.
I need to make a spreadsheet in excel with the output of the script, that will be used by managers. The script goes out and gathers some general info about the databases on a particular server (I also need data from many servers). Although many sections are functional, the entire script has never worked.
Thank you very much for your time,
(this has given me a couple of headaches)
Bryan Burke
Here is the Script
--DatabaseSummaryInfo.sql - Quick summary of Database usage
CREATE PROCEDURE DatabaseSummaryInfo (
@dbOnly sysname = NULL
) AS
DECLARE
@dbName sysname, --Name of the database being reported
@dbSize INT, --Total allocated size for the database
@logSize INT, --Total size used by the logs
@usedSize INT, --Number of pages used in the database
@pagesperMB dec(15,0), --Pages in this system per megabyte
@tblCount INT, --Number of tables in the database
@sSQL NVARCHAR(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 (
ServerName sysname,
DBName sysname,
DBSize DEC(15,2) DEFAULT 0.0,
DBAvail DEC(15,2) DEFAULT 0.0,
TableCnt INT 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
--ServerDBSummaryInfo.sql - Quick summary of Database usage - on all linked servers
CREATE PROCEDURE ServerDBSummaryInfo
AS
DECLARE
@srvName sysname, --Name of the server being reported
@sSQL NVARCHAR(2000) --For creating the sql call
SET NOCOUNT ON
--Table for the output
CREATE TABLE #tmpAllInfo (
ServerName sysname,
DBName sysname,
DBSize DEC(15,2) DEFAULT 0.0,
DBAvail DEC(15,2) DEFAULT 0.0,
TableCnt INT 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
May 14, 2003 at 12:44 am
my first impression :
"SELECT @sSQL = 'EXEC ' + @srvName + '.master.dbo.DatabaseSummaryInfo'" would mean you have defined all @srvName-servers as linked server on the server where you run the script.
I run a (sqlserver-scheduled) vb-app with sql-dmo that gathers these statistics into a statitics-db, so I can monitor db-growth over time.
I'll look a bit more later on ....
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 28, 2003 at 12:38 am
"sysservers" does not mean all SQL servers, it should be called sysLINKEDservers, which relates to alzdba's point.
You would also have to define the procedure DatabaseSummaryInfo on each server.
I have recently seen that you can list all servers on the LAN with the command:
isql -L
Probably easier just to have you own table to list servers and enter them manually.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply