Here is one that I wrote after not finding what I was looking for online...
The following script has the code for the table creates, view creates, two stored procedures and a job (2005 syntax).
you can drop it into a query window and run the code which should create all objects (you will need to change out the DB_UTILS reference to your base DB)
Once you get the objects created it is as easy as running the two procedures
1. to generate the report information
2. to email the report
The report shows both drive and db space reports separated out
Drive (size, used, free, % free)
DB (db name, db file name, db drive letter, file size, space used, space free, % free)
change out references to DB_UTILS and make sure you put an email address in the second stored procedure and it should work off the bat...
USE master
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ole Automation Procedures';
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO
--------------------------------------------------------
-- create work tables
--------------------------------------------------------
IF EXISTS (SELECT * FROM DB_UTILS.dbo.sysobjects WHERE name = 'tb_Databases')
BEGIN
DROP TABLE DB_UTILS.dbo.tb_Databases
PRINT 'DROPPED tb_Databases From DB_UTILS'
END
CREATE TABLE DB_UTILS.dbo.tb_Databases
(
row_id INT IDENTITY(1,1),
dbname SYSNAME,
db_size INT, -- Size of database, in kilobytes.
remarks VARCHAR(254)
)
----------------------------------------------------------
IF EXISTS (SELECT * FROM DB_UTILS.dbo.sysobjects WHERE name = 'tb_DBShowFileStats')
BEGIN
DROP TABLE DB_UTILS.dbo.tb_DBShowFileStats
PRINT 'DROPPED tb_DBShowFileStats From DB_UTILS'
END
CREATE TABLE DB_UTILS.dbo.tb_DBShowFileStats
(
row_id INT IDENTITY(1,1),
dbname NVARCHAR(255),
fileid INT,
filegroup INT,
totalextents INT,
usedextents INT,
name VARCHAR(255),
filename VARCHAR(255)
)
----------------------------------------------------------
IF EXISTS (SELECT * FROM DB_UTILS.dbo.sysobjects WHERE name = 'tb_FixedDriveSpace')
BEGIN
DROP TABLE DB_UTILS.dbo.tb_FixedDriveSpace
PRINT 'DROPPED tb_FixedDriveSpace From DB_UTILS'
END
CREATE TABLE DB_UTILS.dbo.tb_FixedDriveSpace
(
drive_name CHAR(1) PRIMARY KEY,
free_space DECIMAL(10,3) NULL,
total_size DECIMAL(10,3) NULL
)
--------------------------------------------------------
-- create views
--------------------------------------------------------
USE DB_UTILS
GO
IF EXISTS (SELECT * FROM DB_UTILS.sys.views WHERE name = 'vw_FixedDriveSpace')
BEGIN
DROP VIEW vw_FixedDriveSpace
PRINT 'DROPPING View vw_FixedDriveSpace'
END
GO
CREATE VIEW vw_FixedDriveSpace AS
SELECT TOP 100 PERCENT
a.drive_name + ':\' as [Drive],
STR(SUM(a.total_size), 10, 2) as [Drive_Size],
STR(SUM((a.total_size - a.free_space)), 10, 2) as [Space_Used],
STR(SUM(a.free_space), 10, 2) as [Space_Free],
STR((a.free_space * 100 / a.total_size), 10, 2) as [Pct_Free]
FROM DB_UTILS.dbo.tb_FixedDriveSpace as a
GROUP BY a.drive_name, a.free_space, a.total_size
ORDER BY (a.free_space * 100 / a.total_size), a.drive_name
GO
----------------------------------------------------------
IF EXISTS (SELECT * FROM DB_UTILS.sys.views WHERE name = 'vw_DBFreeSpace')
BEGIN
DROP VIEW vw_DBFreeSpace
PRINT 'DROPPING View vw_DBFreeSpace'
END
GO
CREATE VIEW vw_DBFreeSpace AS
SELECT TOP 100 PERCENT
SUBSTRING(a.dbname, 1, 26) as [Name],
SUBSTRING(b.name, 1, 26) as [FileName],
LEFT(b.filename, 3) as [Drive],
STR(SUM((b.totalextents * 64.0) / 1024.0), 10, 2) as [DB_File_Size],
STR(SUM((b.usedextents * 64.0) / 1024.0), 10, 2) as [Space_Used],
STR(SUM((b.totalextents - b.usedextents) * 64.0 / 1024.0), 10, 2) as [Space_Free],
STR(SUM((((b.totalextents - b.usedextents) * 64.0) / 1024.0 * 100.0 /
((b.totalextents * 64.0) / 1024.0))), 10, 2) as [Pct_Free]
FROM DB_UTILS.dbo.tb_Databases as a
INNER JOIN DB_UTILS.dbo.tb_DBShowFileStats as b on a.dbname = b.dbname
GROUP BY a.dbname, b.name, b.filename, b.totalextents, b.usedextents
ORDER BY (((b.totalextents - b.usedextents) * 64.0) / 1024.0 * 100.0 / ((b.totalextents * 64.0) / 1024.0)),
a.dbname,
b.name
GO
USE DB_UTILS
GO
IF EXISTS (SELECT * FROM DB_UTILS.sys.objects WHERE name = 'bp_DBandServerSpaceReport' AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE bp_DBandServerSpaceReport
PRINT 'DROPPING bp_DBandServerSpaceReport'
END
GO
CREATE PROCEDURE bp_DBandServerSpaceReport AS
SET NOCOUNT ON
-- work variables
-----------------
DECLARE @SQLCmd NVARCHAR(MAX),
@Result INT,
@FSO INT,
@DriveNameOut INT,
@TotalSizeOut VARCHAR(20),
@MB NUMERIC
SET @MB = 1048576
-- clear work tables
--------------------
TRUNCATE TABLE DB_UTILS.dbo.tb_Databases
TRUNCATE TABLE DB_UTILS.dbo.tb_DBShowFileStats
TRUNCATE TABLE DB_UTILS.dbo.tb_FixedDriveSpace
----------------------------------------------------------
-- load database table with database names
-----------------------------------------------------------
SET @SQLCmd = 'master..sp_databases'
INSERT INTO DB_UTILS.dbo.tb_Databases (dbname, db_size, remarks) EXEC sp_executesql @SQLCmd
-- loop through databases and load file stats table with information for each database
--------------------------------------------------------------------------------------
DECLARE @dbname VARCHAR(200)
SET @dbname = ''
WHILE @dbname IS NOT NULL
BEGIN
SELECT @dbname = MIN(dbname)
FROM DB_UTILS.dbo.tb_Databases
WHERE dbname > @dbname
IF @dbname IS NOT NULL
BEGIN
SELECT @SQLCmd = 'USE [' + @dbname + ']; DBCC SHOWFILESTATS'
INSERT INTO DB_UTILS.dbo.tb_DBShowFileStats (fileid, filegroup, totalextents, usedextents, name, filename) EXEC sp_executesql @SQLCmd
UPDATE DB_UTILS.dbo.tb_DBShowFileStats
SET dbname = @dbname
WHERE dbname IS NULL
END
END
-- loop through databases and load file stats table with information for each database
--------------------------------------------------------------------------------------
INSERT DB_UTILS.dbo.tb_FixedDriveSpace(drive_name, free_space) EXEC master.dbo.xp_fixeddrives
EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @FSO OUT
IF @Result <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FSO
END
-- loop through and get drive metadata for each drive on the server
-------------------------------------------------------------------
DECLARE @drive_name VARCHAR(50)
SET @drive_name = ''
WHILE @drive_name IS NOT NULL
BEGIN
SELECT @drive_name = MIN(drive_name)
FROM DB_UTILS.dbo.tb_FixedDriveSpace
WHERE drive_name > @drive_name
IF @drive_name IS NOT NULL
BEGIN
-- get drive information
------------------------
EXEC @Result = sp_OAMethod @FSO, 'GetDrive', @DriveNameOut OUT, @drive_name
-- error handling
-----------------
IF @Result <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FSO
END
-- get drive size
-----------------
EXEC @Result = sp_OAGetProperty @DriveNameOut, 'TotalSize', @TotalSizeOut OUT
-- error handling
-----------------
IF @Result <> 0
BEGIN
EXEC sp_OAGetErrorInfo @DriveNameOut
END
-- update temp table with values
--------------------------------
UPDATE DB_UTILS.dbo.tb_FixedDriveSpace
SET total_size = @TotalSizeOut / @MB
WHERE drive_name = @drive_name
END
END
-- destroy the fso
------------------
EXEC @Result = sp_OADestroy @FSO
-- error handling
-----------------
IF @Result <> 0
BEGIN
EXEC sp_OAGetErrorInfo @FSO
END
GO
-- create procedure that will send mail
---------------------------------------
IF EXISTS (SELECT * FROM DB_UTILS.sys.objects WHERE name = 'bp_DBandServerSpaceReport_SendEmail' AND type in (N'P', N'PC'))
BEGIN
DROP PROCEDURE bp_DBandServerSpaceReport_SendEmail
PRINT 'DROPPING bp_DBandServerSpaceReport_SendEmail'
END
GO
CREATE PROCEDURE bp_DBandServerSpaceReport_SendEmail AS
-- send report
--------------
-- email variables
------------------
DECLARE @EmailAddress VARCHAR(30),
@EmailSubject VARCHAR(200),
@EmailImportance VARCHAR(10),
@EmailQuery VARCHAR(4000),
@EmailMessage VARCHAR(500),
@EmailFormat VARCHAR(20),
@EmailResultsWidth INT
-- drive space query
--------------------
SELECT @EmailAddress = 'NEED TO ENTER YOUR EMAIL ADDRESS HERE',
@EmailSubject = 'Database Size Report - ' + @@SERVERNAME,
@EmailMessage = 'The System Drive Space Results Are As Follows:' + CHAR(10) +
'----------------------------------------------',
@EmailQuery =
'SET NOCOUNT ON;' + CHAR(10) +
'PRINT ''''' + CHAR(10) +
'SELECT Drive as [Drive],' + CHAR(10) +
' Drive_Size as [Drive Size (MB)],' + CHAR(10) +
' Space_Used as [Space Used (MB)],' + CHAR(10) +
' Space_Free as [Space Free (MB)],' + CHAR(10) +
' Pct_Free as [Pct. Free]' + CHAR(10) +
'FROM DB_UTILS.dbo.vw_FixedDriveSpace' + CHAR(10) +
'ORDER BY Pct_Free, Drive' + CHAR(10) +
'PRINT ''''' + CHAR(10) +
'PRINT ''''' + CHAR(10) +
'SELECT '' '' as ''The Database Space Results Are As Follows:''' + CHAR(10) +
'SELECT Name as [DB Name],' + CHAR(10) +
' FileName as [DB File Name],' + CHAR(10) +
' Drive as [Drive],' + CHAR(10) +
' DB_File_Size as [DB File Size],' + CHAR(10) +
' Space_Used as [Space Used (MB)],' + CHAR(10) +
' Space_Free as [Space Free (MB)],' + CHAR(10) +
' Pct_Free as [Pct. Free]' + CHAR(10) +
'FROM DB_UTILS.dbo.vw_DBFreeSpace' + CHAR(10) +
'ORDER BY Pct_Free, Name, FileName',
@EmailFormat = 'TEXT',
@EmailImportance = 'NORMAL',
@EmailResultsWidth = 150
-- Send Mail
------------
EXEC msdb..sp_send_dbmail
@profile_name = @@SERVERNAME,
@recipients = @EmailAddress,
@subject = @EmailSubject,
@body = @EmailMessage,
@query = @EmailQuery,
@body_format = @EmailFormat,
@query_result_width = @EmailResultsWidth,
@importance = @EmailImportance
GO
-- create SQL job
------------------
USE [msdb]
GO
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'DB_UTILS - Server and DB Space Check')
EXEC msdb.dbo.sp_delete_job @job_name = N'DB_UTILS - Server and DB Space Check', @delete_unused_schedule=1
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DB_UTILS - Server and DB Space Check',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Compiles A Size Report On All Drives And All Databases Reporting Space In-Use, Free Space, And Total Space.',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa',
@notify_email_operator_name=N'SqlAdmin',
@job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Run DB Space Report',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=1,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC DB_UTILS.dbo.bp_DBandServerSpaceReport',
@database_name=N'DB_UTILS',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Email DB Space Report',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=1,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC DB_UTILS.dbo.bp_DBandServerSpaceReport_SendEmail',
@database_name=N'DB_UTILS',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Weekly',
@enabled=1,
@freq_type=8,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20011115,
@active_end_date=99991231,
@active_start_time=41500,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave: