April 21, 2011 at 4:01 pm
Hello,
I have created the table with the following columns and I have the data as well. I need to generate the report all the columns with monthly db growth. Please help me with the calculations. previously the other dba used to do it manualy as previous month dbsize-current dbsize = Total growth
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
Monthly_Growth ???
April 22, 2011 at 12:12 pm
Can you please provide:
1) DDL to create your test tables
2) DML to create some sample data
3) The queries you have tried so far
4) the desired output
If you do not understand why I am asking please read this article; http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 22, 2011 at 12:19 pm
How are you populating data into this table?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 22, 2011 at 11:53 pm
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
,((
SELECT SUM(FILE_SIZE) FROM DBINFO TMP2 WHERE TMP2.TYPE_DESC = 'ROWS' AND TMP2.DBID=TMP1.DBID
AND TMP2.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_DATA_SIZE
,((
SELECT SUM(FILE_SIZE) FROM DBINFO TMP2 WHERE TMP2.TYPE_DESC = 'ROWS' AND TMP2.DBID=TMP1.DBID
AND TMP2.SIZE_DATE BETWEEN CAST(CONVERT(VARCHAR(100),GETDATE()-1,111) AS VARCHAR(100))+' 00:00:00'
AND CAST(CONVERT(VARCHAR(100),GETDATE()-1,111) AS VARCHAR(100))+' 23:59:59'
)*8)/1024 AS LAST_DAY_DATA_SIZE
,((
SELECT SUM(FILE_SIZE) FROM DBINFO TMP2 WHERE TMP2.TYPE_DESC = 'ROWS' AND TMP2.DBID=TMP1.DBID
AND TMP2.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 TMP2 WHERE TMP2.TYPE_DESC = 'ROWS' AND TMP2.DBID=TMP1.DBID
AND TMP2.SIZE_DATE BETWEEN CAST(CONVERT(VARCHAR(100),GETDATE()-1,111) AS VARCHAR(100))+' 00:00:00'
AND CAST(CONVERT(VARCHAR(100),GETDATE()-1,111) AS VARCHAR(100))+' 23:59:59'
)*8)/1024 AS PER_DAY_INCREMENT
,((
SELECT SUM(FILE_SIZE) FROM DBINFO TMP2 WHERE TMP2.TYPE_DESC = 'ROWS' AND TMP2.DBID=TMP1.DBID
AND TMP2.SIZE_DATE BETWEEN CAST(CONVERT(VARCHAR(100),GETDATE()-30,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 TMP2 WHERE TMP2.TYPE_DESC = 'ROWS' AND TMP2.DBID=TMP1.DBID
AND TMP2.SIZE_DATE BETWEEN CAST(CONVERT(VARCHAR(100),GETDATE()-60,111) AS VARCHAR(100))+' 00:00:00'
AND CAST(CONVERT(VARCHAR(100),GETDATE()-30,111) AS VARCHAR(100))+' 23:59:59'
)*8)/1024 AS PER_MONTH_INCREMENT
FROM DBINFO TMP1
WHERE DBID IN(SELECT DISTINCT DBID FROM DBINFO)
Check this script if you have any problem or modification required then tell me
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 23, 2011 at 9:53 pm
Syed Jahanzaib Bin hassan (4/22/2011)
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
Where's the rest of the stuff to meet the OP's request? 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
April 24, 2011 at 1:54 am
At a guess, something like this
WITH cte ([ServerName],[Database Name],[Month],[DB-Size])
AS (
SELECT [ServerName],[Database Name],DATEDIFF(month,GETDATE(),[Execution date]) AS [Month],[DB-Size]
FROM
WHERE [Execution date] >= DATEADD(month,DATEDIFF(month,0,GETDATE())-12,0)
AND [Execution date] < DATEADD(month,DATEDIFF(month,0,GETDATE()),1)
AND DAY([Execution date]) = 1
)
SELECT a.[ServerName],a.[Database Name],
MAX(CASE WHEN a.[Month]= 0 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [0],
MAX(CASE WHEN a.[Month]= -1 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-1],
MAX(CASE WHEN a.[Month]= -2 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-2],
MAX(CASE WHEN a.[Month]= -3 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-3],
MAX(CASE WHEN a.[Month]= -4 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-4],
MAX(CASE WHEN a.[Month]= -5 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-5],
MAX(CASE WHEN a.[Month]= -6 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-6],
MAX(CASE WHEN a.[Month]= -7 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-7],
MAX(CASE WHEN a.[Month]= -8 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-8],
MAX(CASE WHEN a.[Month]= -9 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-9],
MAX(CASE WHEN a.[Month]=-10 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-10],
MAX(CASE WHEN a.[Month]=-11 THEN a.[DB-Size] - b.[DB-Size] ELSE 0 END) AS [-11]
FROM cte a
JOIN cte b ON b.[Month]=a.[Month]-1
GROUP BY a.[ServerName],a.[Database Name]
ORDER BY a.[ServerName],a.[Database Name] ASC
This will show last 12 months DB file growth, substitute DB-Used for data growth.
*EDITED to fix errors
Far away is close at hand in the images of elsewhere.
Anon.
April 24, 2011 at 2:44 am
This is also posted at http://www.sqlservercentral.com/Forums/Topic1094538-391-1.aspx
Far away is close at hand in the images of elsewhere.
Anon.
April 24, 2011 at 3:24 am
Check this script if you have any problem or modification required then tell me
Did you read this statement in the end of my post
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 24, 2011 at 6:29 am
Syed Jahanzaib Bin hassan (4/24/2011)
Did you read this statement in the end of my post
Yes but why did you post code to generate data when the poster already had data but did not give any answer to the question?
Far away is close at hand in the images of elsewhere.
Anon.
April 24, 2011 at 3:24 pm
Syed Jahanzaib Bin hassan (4/24/2011)
Did you read this statement in the end of my post
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
http://www.aureus-salah.com[/quote%5D
Yep. So I'm asking... where's the rest of the solution? 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 25, 2011 at 9:45 am
Thank you so much for your help- I will try and let you know. Thanks!
April 25, 2011 at 10:30 am
hydbadrose (4/25/2011)
Thank you so much for your help- I will try and let you know. Thanks!
Here's how I tackle the problem. Read the backup history and using the logged size figure out when I'm going to run out of space. You could modify to get monthly growth quite easily...
IF OBJECT_ID('tempdb..#dbs') > 0
DROP TABLE #dbs
IF OBJECT_ID('tempdb..#Drives') > 0
DROP TABLE #Drives
IF OBJECT_ID('tempdb..#Results') > 0
DROP TABLE #Results
CREATE TABLE #dbs
(
DBNAME sysname
, DBID INT
, [Total Size in MB] INT
, [Available Space In MB] INT
, DriveLetter CHAR(1)
)
INSERT INTO
#dbs
(
DBNAME
, DBID
, [Total Size in MB]
, [Available Space In MB]
, DriveLetter
)
EXEC sp_MSforeachdb '
USE [?];
SELECT
DB_NAME() As DBNAME
, DB_ID() AS DBID
, SUM(size / 128) AS ''Total Size in MB''
, SUM(size / 128 - CAST(FILEPROPERTY(name , ''SpaceUsed'') AS int) / 128) AS ''Available Space In MB''
, LEFT(physical_name, 1) AS DriveLetter
FROM
[?].sys.database_files
WHERE
type_desc = ''ROWS''
GROUP BY LEFT(physical_name, 1) '
CREATE TABLE #Drives
(
DriverLetter CHAR(1) PRIMARY KEY CLUSTERED
, FreeMBs INT NOT NULL
, FreeGBs AS CONVERT(DECIMAL(18 , 2) , FreeMBs / 1024.0)
)
INSERT INTO
#Drives ( DriverLetter , FreeMBs )
EXEC xp_fixeddrives
--
--SELECT
-- DB_NAME() As DBNAME
-- , DB_ID() AS DBID
-- , SUM(size / 128) AS 'Total Size in MB'
-- , SUM(size / 128 - CAST(FILEPROPERTY(name , 'SpaceUsed') AS int) / 128) AS 'Available Space In MB'
--FROM
-- sys.database_files
--WHERE
-- type_desc = 'ROWS'
--Rémi : I deleted 4 logging tables I had build on March 25th, hence the ±350 MB drop.
;
WITH CTE_Backups ( database_name, BackupDate, MinutesForBackup, GB_backup_size, seqFirst, seqLast )
AS (
SELECT
bs.database_name
, DATEADD(D , 0 , DATEDIFF(D , 0 , bs.backup_start_date)) AS BackupDate
, CONVERT(DECIMAL(18 , 1) , DATEDIFF(s , bs.backup_start_date ,
bs.backup_finish_date)
/ 60.0) AS MinutesForBackup
, CONVERT(DECIMAL(18 , 3) , bs.backup_size / 1024 / 1024 / 1024) AS GB_backup_size
, ROW_NUMBER() OVER ( PARTITION BY bs.database_name ORDER BY bs.backup_start_date ) AS seqFirst
, ROW_NUMBER() OVER ( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC ) AS seqLast
FROM
msdb.dbo.backupset bs
WHERE
name IS NULL
AND bs.[type] = 'D'
)
SELECT
CONVERT(INT , dtBackups.[Available Space In GB]
/ CASE WHEN dtBackups.GB_ExpectedDailyGrowth <> 0
THEN dtBackups.GB_ExpectedDailyGrowth
ELSE 0.0001
END) AS DaysUntillDBGrowth
, *
-- INTO
-- #Results
FROM
(
SELECT
a.database_name
, dbs.DriveLetter
, drv.FreeGBs AS FreeGBs_Drive
, CONVERT(DECIMAL(18 , 1) , ( drv.FreeGBs * 0.85 )
/ CONVERT(DECIMAL(18 , 3) , ( b.GB_backup_size - a.GB_backup_size )
/ DATEDIFF(dd , a.BackupDate , b.BackupDate) * 30.468)) AS FreeGBs_Drive_InMonths_WithExpected_DB_Growth
, a.BackupDate AS BackupDate_First
, b.BackupDate AS BackupDate_Last
, DATEDIFF(dd , a.BackupDate , b.BackupDate) AS DaysPeriod
, a.MinutesForBackup AS MinutesForBackup_First
, b.MinutesForBackup AS MinutesForBackup_Last
, b.MinutesForBackup - a.MinutesForBackup AS MinutesForBackup_Delta
, a.GB_backup_size AS GB_backup_size_First
, b.GB_backup_size AS GB_backup_size_Last
, b.GB_backup_size - a.GB_backup_size AS GB_BackupGrowth
--, a.seqLast - a.seqFirst AS QtyofBackups
, CONVERT(DECIMAL(18 , 3) , ( b.GB_backup_size - a.GB_backup_size )
/ DATEDIFF(dd , a.BackupDate , b.BackupDate)) AS GB_ExpectedDailyGrowth
, CONVERT(DECIMAL(18 , 3) , ( b.GB_backup_size - a.GB_backup_size )
/ DATEDIFF(dd , a.BackupDate , b.BackupDate) * 365.256) AS GB_ExpectedAnnualGrowth
, CONVERT(DECIMAL(18 , 3) , dbs.[Total Size in MB] / 1024.0) AS [Total Size in GB]
, CONVERT(DECIMAL(18 , 3) , dbs.[Available Space In MB] / 1024.0) AS [Available Space In GB]
FROM
CTE_Backups a
INNER JOIN CTE_Backups b
ON a.seqFirst = b.seqLast
AND a.seqLast = b.seqFirst
AND a.database_name = b.database_name
INNER JOIN #dbs dbs
ON b.database_name = dbs.DBNAME
INNER JOIN #Drives drv
ON dbs.DriveLetter = drv.DriverLetter
WHERE
a.seqFirst = 1
) dtBackups
ORDER BY
database_name
... needless to say that you need to keep your backuphistory. So if you have that scheduled it needs to be turned off...
I've never had log file issues so I don't track those... can't help you there. My script could be adapted to get those as well. Could tell you what kind of log size you need for normal activities. Then adjust accordingly.
April 25, 2011 at 10:36 am
Jeff Moden (4/24/2011)
Syed Jahanzaib Bin hassan (4/24/2011)
Check this script if you have any problem or modification required then tell meDid you read this statement in the end of my post
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
http://www.aureus-salah.com[/quote%5D
Yep. So I'm asking... where's the rest of the solution? 😉
You're the 3rd one here pointing that out to him in the last few days. Maybe he'll get it THIS time.
April 25, 2011 at 10:38 am
Ninja's_RGR'us (4/25/2011)
You're the 3rd one here pointing that out to him in the last few days. Maybe he'll get it THIS time.
You, me, Jeff, Lowell, Tara (from SQLTeam) at least.
Shall I buy you an abacus for your birthday? 😉 😀
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 25, 2011 at 10:47 am
GilaMonster (4/25/2011)
Ninja's_RGR'us (4/25/2011)
You're the 3rd one here pointing that out to him in the last few days. Maybe he'll get it THIS time.You, me, Jeff, Lowell, Tara (from SQLTeam) at least.
Shall I buy you an abacus for your birthday? 😉 😀
Only if I can work with you for a week :w00t:.
I'm trying to be as polite and professional as I can with him... not an easy task! I'm seriously wondering how the heck he got all those certs, in how many tries, how many years back and if he actually got them. Option Z would be where did he buy them but that could be insulting so I'll refrain :hehe:?
He clearly knows sql stuff, but he doesn't seem to have applied knowledge and experience. That would explain why he offers so many dangerous / wrong solutions.
I checked out his blog and excluding the ms white papers and books online exerts, he doesn't have much if any, useful info in there... out of 100+ posts.
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply