SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Script that list total database size on server


SQL Script that list total database size on server

Author
Message
chubbsm
chubbsm
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 89

I'm looking for a sql script that will list all the used database space on a server vs. what is allocated. I thought I'd find something in the script archive but there doesn't appaer to be anything listing the "Total" space used, only per database.

How would you list the total space used (other than adding up per DB) on a server that may have upwars of 100 DB's?

Thanks!!





Viking-204480
Viking-204480
SSC-Enthusiastic
SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)SSC-Enthusiastic (150 reputation)

Group: General Forum Members
Points: 150 Visits: 235

Chubb

I have a script which I use to find the used and free space in each datafile and here it goes

/******************************************************/


USE TEMPDB
CREATE TABLE FileDetails (

DbName varchar(100), FileId int , FileGroupName Varchar(50), TotalExtents int , UsedExtents int , [MaxSize] int,
Name nvarchar( 128 ) , FileName nvarchar( 500 ) ,
TotalSize AS ( ( TotalExtents * 64.0 ) / 1024 ) ,
UsedSize AS ( ( UsedExtents * 64.0 ) / 1024 )

)


/** This script will let you find the database size in TotalExtents & UsedExtents **/
BEGIN

/* Get data file(s) size */
DECLARE @db VARCHAR(50), @cmd VARCHAR(2000), @SQL01 NVARCHAR (400)
DECLARE dcur CURSOR LOCAL FAST_FORWARD

FOR
SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA WHERE CATALOG_NAME NOT IN ('Northwind','Pubs','Model','Master','MSDB')


OPEN dcur
FETCH NEXT FROM dcur INTO @db
WHILE @@FETCH_STATUS=0
BEGIN
/** Creation of Temporary Table in TempDB**/

CREATE TABLE #FileDetails (
FileId int , FileGroupId int , TotalExtents int , UsedExtents int ,
Name nvarchar( 128 ) , FileName nvarchar( 500 ) ,
TotalSize AS ( ( TotalExtents * 64.0 ) / 1024 ) ,
UsedSize AS ( ( UsedExtents * 64.0 ) / 1024 )
)

SET @cmd = 'use ' + @db + ' DBCC showfilestats'

INSERT INTO #FileDetails (FileId , FileGroupId , TotalExtents , UsedExtents , Name , Filename)
EXEC(@cmd)

SET @SQL01 =
'INSERT INTO FileDetails (DBName, FileId , FileGroupName , TotalExtents , UsedExtents , Name , Filename)
SELECT ' + '''' + @db + '''' + ', A.FileId , B.GroupName , A.TotalExtents , A.UsedExtents ,
A.Name , A.Filename FROM #FileDetails A Left Outer Join ' + @db + '..SysFileGroups B On A.FileGroupId = B.GroupId'

EXEC SP_EXECUTESQL @SQL01
DROP TABLE #FileDetails


FETCH NEXT FROM dcur INTO @db
END
END

UPDATE FileDetails SET [MaxSize] = B.[MaxSize]
FROM FileDetails A
INNER Join Master..Sysaltfiles B
ON A.FileId = B.FileId and A.[Name] = B.[Name]

CLOSE dcur
DEALLOCATE dcur

/*********************************************************/

You may want to customize the resultset queries of the FileDetails to get what you want!




Thanks!

Viking
philcart
philcart
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9733 Visits: 1441

Use this bit to get the results without using a cursor. Replace <<mydb>> with a database that exists on your server.

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FileDetails]') 
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE dbo.FileDetails (     
FileId int
, FileGroupId int
, TotalExtents int
, UsedExtents int
, Name nvarchar( 128 )
, FileName nvarchar( 500 )
, TotalSize AS ( ( TotalExtents * 64.0 ) / 1024 )
, UsedSize AS ( ( UsedExtents * 64.0 ) / 1024 )
)
END
exec master..sp_MSforeachdb 'USE ?
INSERT INTO <<mydb>>.dbo.FileDetails (FileId, FileGroupId, TotalExtents, UsedExtents, Name, Filename)
EXEC(''DBCC showfilestats'')'
SELECT * FROM <<mydb>>.dbo.FileDetails



Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
Mike Clousing
Mike Clousing
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 Visits: 23

I use the following procedure that lists space used for each database by space free per file group and space used per table. I also have it raise an error if the number of extents remaining on the data file group starts running low.

CREATE procedure checksizes as

-- Create the temp table for further querying
CREATE TABLE #TableTemp(
acquired datetime,
DatabaseName varchar(50),
rec_id int IDENTITY (1, 1),
table_name varchar(128),
nbr_of_rows int,
data_space decimal(15,2),
index_space decimal(15,2),
total_size decimal(15,2),
db_size decimal(15,2))

declare @dbname varchar(25)
declare @sql varchar(1000)
declare dbcursor cursor for select name from master..sysdatabases where dbid >4
EXEC sp_MSforeachdb @command1="print '?' DBCC updateusage ('?')"
open dbcursor
fetch next from dbcursor into @dbname
while @@fetch_status = 0
begin

-- Get all tables, names, and sizes
set @sql = 'USE '+@DBNAME+' EXEC sp_msforeachtable @command1="insert into #TableTemp(nbr_of_rows, data_space, index_space) exec sp_mstablespace ''' + '?'+''''+'",@command2="update #TableTemp set table_name = '''+'?'+''' where rec_id = (select max(rec_id)
from #TableTemp)"'
exec (@sql)
update #TableTemp set databasename = @dbname, acquired=(select getdate()),total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #TableTemp where databasename is null) where databasename is null
fetch next from dbcursor into @dbname
end
close dbcursor
deallocate dbcursor

insert into tablesizes SELECT * FROM #TableTemp where databasename + table_name + convert(varchar(12), nbr_of_rows) not in (select databasename+ table_name+ convert(varchar(12),nbr_of_rows) from tablesizes)

DROP TABLE #TableTemp
insert into dbsizes select getdate(), d.name, f.name, f.filename, f.size*8/1024, f.growth*8/1024, f.maxsize*8/1024, Extents = case when growth = 0 then 0 else (maxsize - size)/growth end, f.groupid,-1 from master..sysdatabases d, master..sysaltfiles f where f.dbid = d.dbid and f.dbid > 4
declare @groupid int
declare @extents int
declare @filename varchar(200)
declare @dbid int
declare @growth int
declare spaceused cursor for select name, dbname from dbsizes where spaceused = -1
open spaceused
fetch next from spaceused into @filename, @dbname
while @@fetch_status = 0
begin
set @sql = 'USE '+@dbname + ' update sqlmanager..dbsizes set spaceused = (SELECT FILEPROPERTY('''+@filename+''', ''spaceused'')*8/1024) where name = '''+@filename+''' and spaceused =-1'
exec (@sql)

fetch next from spaceused into @filename, @dbname
end


close spaceused
deallocate spaceused

declare groupcursor cursor for select groupid, dbname , sum(extents)
from dbsizes
group by groupid, dbname, acquired
having sum(extents) < 4
and sum(spaceused) > 0 and convert(decimal (9,2),sum(spaceused))/convert(decimal (9,2),sum(size))>.9
and convert(varchar(10),getdate(),101)=convert(varchar(10), acquired, 101)
open groupcursor
fetch next from groupcursor into @groupid, @dbname, @extents
while @@fetch_status = 0
begin
set @growth = (select max(growth) from dbsizes where dbname = @dbname and groupid = @groupid and size <> maxsize)
set @sql = 'File Group '+convert(varchar(2),@groupid)+ ' on database '+@@servername+'/'+@dbname+' has '+convert(varchar(2),@extents)+' extents of '+ convert(varchar(5),@growth) + 'MB and less than 10percent free'
raiserror(@sql,16,1) with log
--print @sql
fetch next from groupcursor into @groupid, @dbname, @extents
end
close groupcursor
deallocate groupcursor




--drop table #dbtemp

GO


chubbsm
chubbsm
SSC Veteran
SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)SSC Veteran (294 reputation)

Group: General Forum Members
Points: 294 Visits: 89
Thanks to all for the info!!!!



s.obermeyer
s.obermeyer
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 34
can you pls. post the definition of the tables dbsizes and tablesizes?
Msg 208, Level 16, State 1, Procedure checksizes, Line 37
Ungültiger Objektname 'dbsizes'.
Leeland
Leeland
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2060 Visits: 1331
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:


siraj-1108026
siraj-1108026
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 16
This is the best script so far I have seem. Excellent work well thought out
Siraj
Leeland
Leeland
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2060 Visits: 1331
Thanks for the comments!
Teutenberg
Teutenberg
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 145
I use this script to retrieve database space information.

DECLARE @TempFiles TABLE (
[Name] [nvarchar](128) NULL,
[DatabaseID] [int] NULL,
[Type] [nvarchar](60) NULL,
[State] [nvarchar](60) NULL,
[SizeMB] [float] NULL,
[SizeUsedMB] [float] NULL,
[MaxSizeMB] [float] NULL,
[AutoGrowSize] [float] NULL,
[PercentGrowth] [bit] NULL,
[ReadOnly] [bit] NULL,
[FilesystemPath] [nvarchar](260) NULL)

INSERT INTO @TempFiles (
[Name],[DatabaseID],[Type],[State],[SizeMB],[SizeUsedMB]
,[MaxSizeMB],[AutoGrowSize],[PercentGrowth],[ReadOnly],[FilesystemPath])
EXEC sp_msforeachdb 'USE [?]; SELECT [name],
DB_ID() as [DatabaseID],
[type_desc] as [Type],
[state_desc] as [State],
[size]/128.00 as [SizeMB],
fileproperty([name],''SpaceUsed'')/128.00 as [SizeUsedMB],
CASE WHEN [max_size] = -1 then [max_size] ELSE [max_size]/128.00 END as [MaxSizeMB],
CASE WHEN [is_percent_growth] = 1 THEN [growth] ELSE [growth]/128.00 END as [AutoGrowSize],
[is_percent_growth] as [PercentGrowth],
CASE WHEN [is_media_read_only] = 1 OR [is_read_only] = 1 THEN 1 ELSE 0 END as [ReadOnly],
[physical_name] as [FilesystemPath]
FROM sys.database_files'

SELECT * FROM @TempFiles
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search