How to justify the amount of disk space required

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

How to justify the amount of disk space required

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2008-04-02 : 08:11:54

Hello All,

I’m in the process of buying more space for my database, my question is how would I justify on how big the disk space should be? I thought about taking a month’s worth of historical data to see how much data I collect but once I get that information, how would I convey that into space usage? Is there a better way to analyze how much space is been used on a daily basis? Please advice.

Thanks.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-02 : 12:54:28

check this out:

http://www.sqlservercentral.com/articles/Administering/monitoringdiskspaceandsendingalertswithtsql/1114/

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-04-02 : 14:31:47

This will insert into a table and track the amount of file size changes with each run. I run it weekly so I know my growth from week to week. Whichever way you go, it's important to know (and project) what is being added to your disk. You sure don't want to run out!!

CREATE PROCEDURE usp_DBGrowthRate
AS
SELECT SD.NAME AS DBNAME,
MF.NAME AS FILENAME,
MF.DBID,
FILEID,
SIZE
INTO #TEMPDBSIZE2
FROM MASTER..SYSDATABASES SD
JOIN MASTER..SYSALTFILES MF
ON SD.DBID = MF.DBID
ORDER BY MF.DBID,
SD.NAME

IF EXISTS (SELECT DISTINCT DBNAME
FROM #TEMPDBSIZE2
WHERE DBNAME IN (SELECT DISTINCT DBNAME
FROM DBGROWTHRATE))
AND CONVERT(VARCHAR(10),GETDATE(),101) > (SELECT DISTINCT CONVERT(VARCHAR(10),MAX(METRICDATE),101) AS METRICDATE
FROM DBGROWTHRATE)
BEGIN
INSERT INTO DBO.DBGROWTHRATE
(DBNAME,
DBID,
NUMPAGES,
ORIGSIZE,
CURSIZE,
GROWTHAMT,
METRICDATE)
(SELECT TDS.DBNAME,
TDS.DBID,
SUM(TDS.SIZE) AS NUMPAGES,
DGR.CURSIZE AS ORIGSIZE,
CONVERT(DECIMAL(10,2),(((SUM(CONVERT(DECIMAL(10,2),TDS.SIZE)) * 8000) / 1024) / 1024)) AS CURSIZE,
CONVERT(VARCHAR(100),(CONVERT(DECIMAL(10,2),(((SUM(CONVERT(DECIMAL(10,2),TDS.SIZE)) * 8000) / 1024) / 1024)) - DGR.CURSIZE)) + ' MB' AS GROWTHAMT,
GETDATE() AS METRICDATE
FROM #TEMPDBSIZE2 TDS
JOIN DBGROWTHRATE DGR
ON TDS.DBID = DGR.DBID
WHERE DBGROWTHID = (SELECT DISTINCT MAX(DBGROWTHID)
FROM DBGROWTHRATE
WHERE DBID = DGR.DBID)
GROUP BY TDS.DBID,TDS.DBNAME,DGR.CURSIZE)
END
ELSE
IF NOT EXISTS (SELECT DISTINCT DBNAME
FROM #TEMPDBSIZE2
WHERE DBNAME IN (SELECT DISTINCT DBNAME
FROM DBGROWTHRATE))
BEGIN
INSERT INTO DBO.DBGROWTHRATE
(DBNAME,
DBID,
NUMPAGES,
ORIGSIZE,
CURSIZE,
GROWTHAMT,
METRICDATE)
(SELECT TDS.DBNAME,
TDS.DBID,
SUM(TDS.SIZE) AS NUMPAGES,
CONVERT(DECIMAL(10,2),(((SUM(CONVERT(DECIMAL(10,2),TDS.SIZE)) * 8000) / 1024) / 1024)) AS ORIGSIZE,
CONVERT(DECIMAL(10,2),(((SUM(CONVERT(DECIMAL(10,2),TDS.SIZE)) * 8000) / 1024) / 1024)) AS CURSIZE,
'0.00 MB' AS GROWTHAMT,
GETDATE() AS METRICDATE
FROM #TEMPDBSIZE2 TDS
WHERE TDS.DBID NOT IN (SELECT DISTINCT DBID
FROM DBGROWTHRATE
WHERE DBNAME = TDS.DBID)
GROUP BY TDS.DBID,TDS.DBNAME)
END
GO

Terry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources