Capacity Planning in Database World  
Author Message
CDavid





PostPosted: Tue Jan 29 01:35:06 CST 2008 Top

SQL Server Developer >> Capacity Planning in Database World

Does anyone have a script that generates how much space data files are using
on a volume and the max size of each volume?

SQL Server136  
 
 
Uri





PostPosted: Tue Jan 29 01:35:06 CST 2008 Top

SQL Server Developer >> Capacity Planning in Database World Hi

This stored procedure was written by Greg Larsen

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[DBSTATS]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DBSTATS]
GO
USE DBA -- Database for Table
go
CREATE TABLE [dbo].[DBSTATS] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[RECORD_TYPE] [int] NOT NULL ,
[DBNAME] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATA_SIZE] [decimal](9, 2) NULL ,
[DATA_USED] [decimal](9, 2) NULL ,
[LOG_SIZE] [decimal](9, 2) NULL ,
[LOG_USED] [decimal](9, 2) NULL ,
[STAT_DATE] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DBSTATS] ADD
CONSTRAINT [DF_DBSTATS_STAT_DATE] DEFAULT (getdate()) FOR [STAT_DATE]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[usp_get_dbstats]') and OBJECTPROPERTY(id, N'IsProcedure')
= 1)
drop procedure [dbo].[usp_get_dbstats]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


CREATE procedure usp_get_dbstats as
--
-- This stored procedure was written by Greg Larsen
-- Date: 05/21/2001
--
-- Description:
-- This stored procedure generates SQL commands that uses DBCC SQLPERF and
-- undocumented DBCC SHOWFILESTATS. These generated commands are used
-- to gather database disk usage information. The code generated will take
the
-- statistics generated by the DBCC commands and store them in TEMPDB
tables.
-- The statistics in the TEMPDB tables are then selected, manipulated and
-- eventually placed in table DBSTATS, which is in a database specified by

-- The DBSTATS table then can be used to
-- perform space usage trend analysis.
--

-- Declare variable to hold default database were growth statics table
DBSTATS will be stored.



--
-- Begin "Gather Log Space Usage Information" Section
--

-- Declare variable to hold DBCC command


-- If temporary table to hold transaction log size information for all
databases
-- exists drop
print 'if exists (select * from tempdb..sysobjects where id =
object_id(N' +
char(39) + '[tempdb]..[#tmplg]' + char(39) + '))'
print 'drop table #tmplg'

-- Create temporary table to hold transaction log size information for all
databases
Print 'CREATE TABLE #tmplg'
print '('
print 'DBName varchar(32),'
print 'LogSize real,'
print 'LogSpaceUsed real,'
print 'Status int'
print ')'

-- Build command string to get transaction log size information


-- Execute command to get transaction log size information and records
returned are to be
-- inserted into temporary table #Tmplg


--
-- End "Gather Log Space Usage Information" Section
--

--
-- Begin "Create Temporary Statistics Holding Table" Section
--

-- Drop temporary table for holding data and log size information if it
exists
print 'if exists (select * from tempdb..sysobjects where id =
object_id(N' +
char (39) + '[tempdb]..[#tmp_stats]' + char(39 ) + '))'
print 'drop table #tmp_stats'


-- Create temporary table for holding data and log size information
print 'create table #tmp_stats ('
print 'totalextents int, '
print 'usedextents int,'
print 'dbname varchar(40),'
print 'logsize real,'
print 'logspaceused real'
print ')'
print 'go' --
--End "Create Temporary Statistics Holding Table" Section
--

--
--Begin "Generate DBCC SHOWFILESTATS Commands" Section
--

-- Declare cursor for holding the names of all databases on the server
DECLARE AllDatabases CURSOR FOR

-- Get the names of all databases
SELECT name FROM master..sysdatabases

-- Open the cursor that holds the names of all databases on the server
OPEN AllDatabases

-- Declare variable for holding the name of the current database being
processed


-- Get the name of the first database


-- Process all databases until there are no more


BEGIN
-- switch to the database where stats will be gathered

print 'go'

-- drop temporary table that will store data space used information if it
exists
print 'if exists (select * from tempdb..sysobjects where id =
object_id(N' +
char(39) + '[tempdb]..[#tmp_sfs]' + char(39) + '))'
print 'drop table #tmp_sfs'


-- create temporary table that will store data space used information
print 'create table #tmp_sfs ('
print 'fileid int,'
print 'filegroup int, '
print 'totalextents int, '
print 'usedextents int,'
print 'name varchar(1024),'
print 'filename varchar(1024)'
print ')'
print 'go'

-- Declare variable to hold the DBCC command


-- Set up command for undocumented DBCC SHOWFILESTATS command


-- execute DBCC SHOWFILESTATS command and place records returned into
temporary table


-- Declare variables to hold total log sizes for a database



-- Get the logsize of a database

@DB + char(39)

-- Calculate the megabytes of space used in the log .


-- build command to insert current space usages record for database into
temporary table

+'
print ' ' + char(39) +
'(totalextents,usedextents,dbname,logsize,logspaceused)' + char(39) + ' +'
print ' ' + char(39) + ' select sum(totalextents), sum(usedextents),'
+

char(39) + ',' + char(39) + ' + '


print ' ' + char(39) + ' from #tmp_sfs' + char(39)

-- Insert current space usage record for database being process into
temporary table


-- Get next database to process



--
--End "Generate DBCC SHOWFILESTATS Commands" Section
--

--
--Begin "Save Space Usage Statistics to DBSTATS Table" Section
--

-- Insert a space usage statistics record for each database into the
-- historical space usage table DBSTATS

print ' (RECORD_TYPE, DBNAME, DATA_SIZE, DATA_USED, LOG_SIZE, LOG_USED)'
print ' SELECT 1,dbname,totalextents*64/1024 , usedextents*64/1024 ,'
print ' logsize ,logspaceused from #tmp_stats'

--
--End "Save Space Usage Statistics to DBSTATS Table" Section
--

-- close and deallocate cursor to hold name of all databases on server
CLOSE AllDatabases
DEALLOCATE AllDatabases

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Disk Usage Estimate = ((SUM(ADB1+ADB2+ADB3+...+ADBn) +
SUM(JDB1+JDB2+JDB3+...+JDBn))/2) * 18

Where Disk Usage Estimate is your calculated estimate of
the amount of disk space needed for the next 18 months,
ADBx is the amount of space used in August for DBx where x is a value
between 1 and n,
where n represent a different space usage amount for each database on your
server.
JDBx is the amount of space used in June for DBx where x is a value between
1 and n, where n represents a different space
usage amount for each database on your server.





> Does anyone have a script that generates how much space data files are
> using
> on a volume and the max size of each volume?