Need Urgent Help with Capacity Report Script

Question

Need Urgent Help with Capacity Report Script

hydbadrose

SSCarpal Tunnel

Points: 4164
More actions
April 16, 2011 at 2:52 pm

#235794

I really need help with SQL 2008 Script on generating the Capacity Report with following columns;
- Execution date as getdate()
- ServerName
-Database Name
-DB-Size in MB
-Log-Size in MB
-DB-Used space in Mb
-Log-Used space in Mb
-DB- Available free space
-Log- Available free Space
- Quartery growth in MB
-1 year Forcast growth in GB
I need the store procedure that creates the table if not exists and load the above information from systems table so that I can call that store procedure via sql agent job on daily basis. Please some help me with the script-Thanks in advance!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

Most of this is available in system tables. What have you tried so far?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

hydbadrose SSCarpal Tunnel Points: 4164 More actions · Answer 2

I need help on quarterly growth and 1 year forcase. I created the store pro cedure with all the other information. If you can help me out in formula to generate the quarterly growth and 1 year forecast that would be great. Thanks!

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 3

You should watch for 2 days to find increase size per day

Example

on the First day your database size at 1:00 pm is 80.78MB and on the Second day database size at 1:00 pm is 95.67MB

Per Day database size = Second day - First Day

= 95.67 - 80.78

= 14.89

=15

Database size in a month = 15 * 30(days in a month)

=450

Database size in 3 months or quarterly = 450 * 3 =1350 MB = 1.3 GB

Regards,

Syed Jahanzaib Bin Hassan

MCTS|MCITP|OCA|OCP|OCE|SCJP|IBMCDBA

My Blog

http://www.aureus-salah.com

My Blog
www.aureus-salah.com

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

If you do backups on a regular basis (and you SHOULD), you can check the backup history tables in MSDB to get some data to determine growth over a fairly long period of time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

Well, in order to project growth, you need at least two measurements. Let's assume a week apart. Then take the difference and project it in a straight line, multiply by 52 for a full year and 12 for a quarter. But, even better is to record the values once a week for a month or more, save the data in a table, and then generate a report to show the recorded values over time.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 6

CREATE TABLE DBINFO(ID INT IDENTITY(1,1) PRIMARY KEY

,DBNAME VARCHAR(200)

,DBID INT

,SIZE_DATE DATETIME

,FILE_NAME VARCHAR(200)

,FILE_PHYSICAL_NAME VARCHAR(200)

,FILE_SIZE FLOAT

,TYPE_DESC VARCHAR(100))

----------------------------------------------------------------------------------

DECLARE @DBNAME VARCHAR(200)

DECLARE @FILENAME VARCHAR(200)

DECLARE @DBID INT

DECLARE @QUERY VARCHAR(200)

DECLARE _CURSOR CURSOR FOR

SELECT NAME FROM SYS.DATABASES

WHERE STATE_DESC = 'ONLINE'

OPEN _CURSOR

FETCH NEXT FROM _CURSOR INTO @DBNAME

DELETE FROM DBINFO WHERE CONVERT(VARCHAR(100),SIZE_DATE,111) = CONVERT(VARCHAR(100),GETDATE(),111)

WHILE @@FETCH_STATUS = 0

BEGIN

SET @QUERY = 'SELECT NAME,PHYSICAL_NAME,SIZE,TYPE_DESC FROM '+@DBNAME+'.SYS.DATABASE_FILES'

PRINT @QUERY

INSERT INTO DBINFO(FILE_NAME,FILE_PHYSICAL_NAME,FILE_SIZE,TYPE_DESC)

EXEC(@QUERY)

UPDATE DBINFO SET DBNAME = @DBNAME,DBID = DB_ID(@DBNAME),SIZE_DATE = GETDATE()

WHERE DBNAME IS NULL

FETCH NEXT FROM _CURSOR INTO @DBNAME

END

CLOSE _CURSOR

DEALLOCATE _CURSOR

---- For Testing this script

--SELECT * INTO #DBINFO_2 FROM #DBINFO

--UPDATE #DBINFO_2 SET SIZE_DATE=GETDATE()-1,FILE_SIZE = FILE_SIZE -50

--INSERT INTO #DBINFO

--SELECT DBNAME,DBID,SIZE_DATE,FILE_NAME,FILE_PHYSICAL_NAME,FILE_SIZE,TYPE_DESC

--FROM #DBINFO_2

--SELECT * FROM #DBINFO

--SELECT * FROM #DBINFO_2

---------------------------For Testing this Script---------------------------------

SELECT distinct DBNAME AS DATABASENAME

,((SELECT SUM(FILE_SIZE) FROM DBINFO TMP3 WHERE TMP3.TYPE_DESC = 'LOG' AND TMP3.DBID=TMP1.DBID

AND SIZE_DATE BETWEEN CAST(CONVERT(VARCHAR(100),GETDATE(),111) AS VARCHAR(100))+' 00:00:00'

AND CAST(CONVERT(VARCHAR(100),GETDATE(),111) AS VARCHAR(100))+' 23:59:59')*8)/1024 AS CURRENT_LOG_SIZE

,((SELECT SUM(FILE_SIZE) FROM DBINFO TMP2 WHERE TMP2.TYPE_DESC = 'ROWS' AND TMP2.DBID=TMP1.DBID

AND SIZE_DATE BETWEEN CAST(CONVERT(VARCHAR(100),GETDATE(),111) AS VARCHAR(100))+' 00:00:00'

AND CAST(CONVERT(VARCHAR(100),GETDATE(),111) AS VARCHAR(100))+' 23:59:59')*8)/1024

+((SELECT SUM(FILE_SIZE) FROM DBINFO TMP3 WHERE TMP3.TYPE_DESC = 'LOG' AND TMP3.DBID=TMP1.DBID

AND SIZE_DATE BETWEEN CAST(CONVERT(VARCHAR(100),GETDATE(),111) AS VARCHAR(100))+' 00:00:00'

AND CAST(CONVERT(VARCHAR(100),GETDATE(),111) AS VARCHAR(100))+' 23:59:59')*8)/1024 AS TOTAL_DATABASE_SIZE

,((