Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL Script that list total database size on server Expand / Collapse
Author
Message
Posted Tuesday, September 13, 2005 3:24 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, March 28, 2014 8:31 AM
Points: 80, 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!!




Post #219378
Posted Wednesday, September 14, 2005 12:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, February 14, 2014 4:45 AM
Points: 42, 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
Post #219478
Posted Wednesday, September 14, 2005 1:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, July 20, 2014 9:06 PM
Points: 2,693, Visits: 1,202

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
Post #219492
Posted Wednesday, September 14, 2005 7:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 15, 2009 7:24 AM
Points: 30, 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

Post #219587
Posted Wednesday, September 14, 2005 7:22 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, March 28, 2014 8:31 AM
Points: 80, Visits: 89
Thanks to all for the info!!!!


Post #219598
Posted Wednesday, October 14, 2009 5:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 14, 2011 5:15 AM
Points: 1, 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'.
Post #802649
Posted Wednesday, October 14, 2009 11:37 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:43 AM
Points: 417, Visits: 1,087
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:

Post #802976
Posted Wednesday, August 4, 2010 10:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 20, 2012 11:57 PM
Points: 1, Visits: 16
This is the best script so far I have seem. Excellent work well thought out
Siraj
Post #963637
Posted Wednesday, August 4, 2010 12:12 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:43 AM
Points: 417, Visits: 1,087
Thanks for the comments!
Post #963725
Posted Thursday, March 3, 2011 9:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 4:55 PM
Points: 5, Visits: 142
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
Post #1073056
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse