How to List database size for all servers into a local table
-
I trying to implement an automatic task that list all databases size from all my servers and then insert it into a table on my local server.
I´m using remote servers but I have to link server1 on server2 and server2 on server1. I have about 50 servers that's what I'm looking for another option.
To get a database size list I'm using sp_msforeach. Something like this.
Running on my local server (SERVER1):
EXEC SERVER2.MSDB.dbo.SP_MSFOREACHDB 'USE ? INSERT INTO SERVER1.MON_DBS.DBO.LIST_DBS (SERVER, DB, FILE, SIZE, DATE)
SELECT @@SERVERNAME , ''?'' , ''FILE'' = CASE GROUPID
WHEN ''0'' THEN ''LOG''
WHEN ''1'' THEN ''DATA''
ELSE ''OTHER''
END,
convert(int, ceiling((sum(convert(float, size)) * 8192/1024) / 1024)) ,
GETDATE()
FROM SYSFILES
GROUP BY GROUPID, NAME
ORDER BY GROUPID DESC'
I'm thinkimg to use DTS task to setup all my sql server ant then create steps retrieving first the sp_msforeachdb into a temp table and then insert into my local server.
What is the best way to do this?
Thanks.
-
I publish such information locally to a web page and then put all the pages centrally.
The issue with using remote calls to collect information is when a server doesn't exist ( for one reason or another ) - this tends to break the process. You might consider putting the output to a text file and having each server push its info to a central location where you pick it up. Push is better than pull in my opinion!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
I kind of follow Colin's approach. I tend to have a user database on all servers I manage called dbasys and I drop any tables/sprocs/functions I need for day-to-day maintenance/data capture in there.
I also use this database for capturing database sizes on each server daily, I set up a local job to fire off a stored procedure (see dba_spdbsize below) to capture the information.
I then threw together a quick ASP application (with a configurable config file holding connection strings for each server) sometime ago that connects to each database and returns the historical sizes to me. A developer friend of mine then used some jazzy graphics to give me a view of each database on the server similar to that in EM, along with a historical line graph of size/time. The table definition and sproc I use to capture the information is below (please test before production use):
/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Script Name: dba_dbsize_objects_create.sql
Description: Script to create database dbasys with associated Stored Procedure 'dba_spdbsize' and Table 'dbsize' for
capturing Database and Transaction Log Size/Space Used
Application: DBA Tools
$Workfile: $
$Header: $
Revision History:
$History: $
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
-----------------------------------------------------
-------- Start create database dbsys -----------
-----------------------------------------------------
--Edit Filename locations as required
USE master
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'dbasys')
DROP DATABASE [dbasys]
GO
CREATE DATABASE dbasys
ON
( NAME = N'dbasys_Data',
--FILENAME = N'D:\Data\dbasys_Data.MDF',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\dbasys_Data.MDF',
--FILENAME = N'F:\Data\dbasys_Data.MDF',
SIZE = 10,
FILEGROWTH = 10%)
LOG ON
( NAME = N'dbasys_Log',
--FILENAME = N'D:\Data\dbasys_Log.LDF',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\dbasys_Log.LDF',
--FILENAME = N'E:\Logs\dbasys_Log.LDF',
SIZE = 1,
FILEGROWTH = 10%)
GO
-----------------------------------------------------
-------- Finish create database dbsys -----------
-----------------------------------------------------
USE dbasys
-----------------------------------------------------
-------- Start create dbsize -----------
-----------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbsize]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbsize]
GO
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dbsize]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbsize] (
[ds_id] [int] IDENTITY (1, 1) NOT NULL ,
[ds_server] [varchar] (50) NULL ,
[ds_dbname] [varchar] (50) NULL ,
[ds_dbLogSizeTotal] [decimal](10, 2) NULL ,
[ds_dbLogSizeUsed] [decimal](10, 2) NULL ,
[ds_dbDataSizeTotal] [decimal](10, 2) NULL ,
[ds_dbDataSizeUsed] [decimal](10, 2) NULL ,
[ds_capturedDateTime] [datetime] NULL
) ON [PRIMARY]
END
GO
-----------------------------------------------------
-------- Finish create dbsize -----------
-----------------------------------------------------
-----------------------------------------------------
-------- Start create dba_spdbsize ------------
-----------------------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[dba_spdbsize]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[dba_spdbsize]
GO
/*--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Stored Procedure Name: dba_spdbsize
Description: Returns database Log Size, Log Space Used, Data Size and Data Space Used for
a given database or all databases on the server.
Application:
$Workfile: $
$Header: $
Revision History:
$History: $
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/
CREATE PROCEDURE dba_spdbsize
@sysDbName sysname = NULL
AS
DECLARE @dbLogSizeTotal float,
@dbLogSizePercentageUsed float,
@dbLogSizeUsed float,
@dbDataSizeTotal float,
@dbDataSizeUsed float,
@dbName varchar(50),
@dbNameValid bit,
@svrName varchar(50),
@sSvrVersion varchar(128),
@fSvrVersion float
SET @dbNameValid = 0
SET @svrName = @@SERVERNAME
SET NOCOUNT ON
--Identify SQL Server version and create a Temp table to hold list of all databases on
-- the current server (column list returned with sp_helpdb differes between versions) .
SELECT @sSvrVersion = @@VERSION
CREATE TABLE #dba_sphelpdb (
dbname nvarchar(24) NULL,
db_size nvarchar(13) NULL,
owner nvarchar(24) NULL,
dbid smallint NULL,
created char(11) NULL,
status varchar(340) NULL
 
--Determin SQL Server version, drop everything before the dash and everything after the open bracket
SET @sSvrVersion = SUBSTRING( @sSvrVersion, CHARINDEX( '-', @sSvrVersion )+1,
CHARINDEX( '(', @sSvrVersion ) - CHARINDEX( '-', @sSvrVersion ) - 1 )
SET @sSvrVersion = LTRIM(RTRIM( @sSvrVersion ))
SET @fSvrVersion = CAST( LEFT( @sSvrVersion, 4 ) AS FLOAT )
IF @fSvrVersion = 6.00
BEGIN
PRINT 'MS SQL Server 6.0'
END
ELSE
IF @fSvrVersion = 6.50
BEGIN
PRINT 'MS SQL Server 6.5'
END
ELSE
IF @fSvrVersion = 7.00
BEGIN
PRINT 'MS SQL Server 7.0'
END
ELSE
IF @fSvrVersion = 8.00
BEGIN
--PRINT 'MS SQL Server 2000'
--Adds compatibility_level column for SQL Server 2000 to match sp_helpdb results
--EXEC tempdb.dbo.sp_help #dba_sphelpdb
ALTER TABLE #dba_sphelpdb ADD compatibility_level tinyint NULL
--EXEC tempdb.dbo.sp_help #dba_sphelpdb
END
ELSE
BEGIN
PRINT 'MS SQL Server (Unknown version)'
END
INSERT INTO #dba_sphelpdb
EXEC sp_helpdb
--SELECT * FROM #dba_sphelpdb
DECLARE dbselect_cursor CURSOR FOR
SELECT dbname FROM #dba_sphelpdb
OPEN dbselect_cursor
FETCH NEXT FROM dbselect_cursor
INTO @dbName
--Identify whether to query a given database or all server databases
IF @sysDbName <> NULL
BEGIN
--Return db size info for 1 specific db only
WHILE @@FETCH_STATUS = 0 AND @dbNameValid = 0
BEGIN
IF @sysDbName = CAST(@dbName AS sysname)
BEGIN
SET @dbNameValid = 1
DELETE FROM #dba_sphelpdb
WHERE dbname <> @sysDbName
END
FETCH NEXT FROM dbselect_cursor
INTO @dbName
END
END
SET @dbName = NULL
CLOSE dbselect_cursor
DEALLOCATE dbselect_cursor
--Declare cursor on temp table of all databases to collect size info on
DECLARE dblist_cursor CURSOR FOR
SELECT dbname FROM #dba_sphelpdb
OPEN dblist_cursor
FETCH NEXT FROM dblist_cursor
INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
--Get Transaction Log total size and space used
CREATE TABLE #dba_DBLogSpace (
database_name nvarchar(50) NULL,
database_log_size varchar(50) NULL,
database_log_space_used_percentage varchar(50) NULL,
database_status int NULL
 
INSERT INTO #dba_DBLogSpace
EXEC ('DBCC SQLPERF(LOGSPACE)')
SELECT @dbLogSizeTotal = database_log_size, @dbLogSizePercentageUsed = database_log_space_used_percentage FROM #dba_DBLogSpace
WHERE database_name = @dbName
--SELECT @dbLogSizeTotal AS 'Log Size (MB)'
--SELECT @dbLogSizePercentageUsed AS 'Log Space Used (%)'
-- SET @dbLogSizeUsed = CAST(((@dbLogSizeTotal/100) * @dbLogSizePercentageUsed) AS decimal(4,2))
SET @dbLogSizeUsed = (@dbLogSizeTotal/100) * @dbLogSizePercentageUsed
--SELECT @dbLogSizeUsed AS 'Log Space Used (MB)'
DROP TABLE #dba_DBLogSpace
--Get total size and space used for Data
SELECT @dbDataSizeTotal = LEFT(db_size, (LEN(db_size) - 2)) - @dbLogSizeTotal FROM #dba_sphelpdb
WHERE dbname = @dbName
--Best guess attempt made for required datatypes taken from datatypes used within sysdatabases, sysfiles db's
CREATE TABLE #dba_DBDataSpaceUsed (
fileid int,
groupid int,
TotalExtents int,
UsedExtents int,
LogFileName nvarchar(128),
PhyFileName nvarchar(260)
 
INSERT #dba_DBDataSpaceUsed
EXEC ('USE ' + '"' + @dbName + '"' + ' DBCC SHOWFILESTATS')
--SELECT * FROM #dba_DBDataSpaceUsed
SELECT @dbDataSizeUsed = (UsedExtents*64)/1024 FROM #dba_DBDataSpaceUsed
--SELECT @dbDataSizeUsed AS 'Data Space Used (MB)'
DROP TABLE #dba_DBDataSpaceUsed
--SELECT @dbName AS 'DB Name', @dbLogSizeTotal AS 'Log Size (MB)', @dbLogSizeUsed AS 'Log Space Used (MB)', @dbDataSizeTotal AS 'Data Size (MB)', @dbDataSizeUsed AS 'Data Space Used (MB)'
--SELECT @dbName AS 'DB Name', CAST(@dbLogSizeTotal AS decimal(10,2)) AS 'Log Size (MB)', CAST(@dbLogSizeUsed AS decimal(10,2)) AS 'Log Space Used (MB)', CAST(@dbDataSizeTotal AS decimal(10,2)) AS 'Data Size (MB)', CAST(@dbDataSizeUsed AS decimal(10,2)) AS 'Data Space Used (MB)'
--Insert database size values in to dbaSys table
--Float values are CAST to decimal(10,2) to scale to 2 significant figures, precision value of 10 supports
--CASTing of original calculated float values up to 9999999999 (i.e. database/log sizes up to 9765624GB (9999999999/1024))
INSERT dbsize VALUES (@svrName, @dbName, CAST(@dbLogSizeTotal AS decimal(10,2)), CAST(@dbLogSizeUsed AS decimal(10,2)), CAST(@dbDataSizeTotal AS decimal(10,2)), CAST(@dbDataSizeUsed AS decimal(10,2)), getdate())
FETCH NEXT FROM dblist_cursor
INTO @dbName
END
CLOSE dblist_cursor
DEALLOCATE dblist_cursor
DROP TABLE #dba_sphelpdb
PRINT '--------- Database and Transaction Log Size/Space Used values captured ---------'
PRINT '--------- To view captured results: SELECT * FROM dbo.dbsize ---------'
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-----------------------------------------------------
-------- Finish create dba_spdbsize -----------
-----------------------------------------------------
-----------------------------------------------------
-------- Start create dbsize Login/User -----------
-----------------------------------------------------
USE master
EXEC sp_addlogin 'dbasys', 'pa55w0rd'
USE dbasys
EXEC sp_grantdbaccess 'dbasys', 'dbasys'
EXEC sp_addrolemember 'db_datareader', 'dbasys'
EXEC sp_addrolemember 'db_datawriter', 'dbasys'
-----------------------------------------------------
-------- Finish create dbsize Login/User -----------
-----------------------------------------------------
-
Sorry I forgot to mention it would be easy to get the daily local job on each server to publish to a central location as well, you could even use replication if yuo're feeling brave.
I agree with Colin's comment about remotely querying servers, this way at least each servers process can run individually so is not as easily broken.
-
I tried to create the procedure but looks like there is error in some parts of script..
status varchar(340) NULL
--Determin SQL Server version, drop everything before the dash and everything after the open bracket
SET @sSvrVersion = SUBSTRING( @sSvrVersion, CHARINDEX( '-', @sSvrVersion )+1,
CHARINDEX( '(', @sSvrVersion ) - CHARINDEX( '-', @sSvrVersion ) - 1 )
Message is ...
Server: Msg 156, Level 15, State 1, Procedure dba_spdbsize, Line 58
Incorrect syntax near the keyword 'SET'.
##########
and also in ...
WHILE @@FETCH_STATUS = 0
BEGIN
--Get Transaction Log total size and space used
CREATE TABLE #dba_DBLogSpace (
database_name nvarchar(50) NULL,
database_log_size varchar(50) NULL,
database_log_space_used_percentage varchar(50) NULL,
database_status int NULL
INSERT INTO #dba_DBLogSpace
EXEC ('DBCC SQLPERF(LOGSPACE)')
message is ..
Server: Msg 156, Level 15, State 1, Procedure dba_spdbsize, Line 141
Incorrect syntax near the keyword 'INSERT'.
##########
CREATE TABLE #dba_DBDataSpaceUsed (
fileid int,
groupid int,
TotalExtents int,
UsedExtents int,
LogFileName nvarchar(128),
PhyFileName nvarchar(260)
INSERT #dba_DBDataSpaceUsed
EXEC ('USE ' + '"' + @dbName + '"' + ' DBCC SHOWFILESTATS')
Error message is ...
Server: Msg 156, Level 15, State 1, Procedure dba_spdbsize, Line 168
Incorrect syntax near the keyword 'INSERT'.
Thanks
Sushma
-
I would like to
create a table in your central control box;
Input all server names you would like to scan;
Use a cursor to run this stored procedure in a loop.
I think the sp_foreachdb is a built-in stored procedure in master. Am I right?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply