SQL query help

Question

Post reply

SQL query help

Ananda-292708

Ten Centuries

Points: 1339
More actions
May 11, 2010 at 4:33 am

#94900

I need help to get data in proper format.
I have a table named db_size_Hist with below columns
DatabaseName(varchar30)HistDate(datetime)SizeMB (int)
One of my process daily pings the server and gets the database size in megabytes.
Now i need to write a query which will show the monthly growth of all databases from the table.
How do i write the sql query.

Viewing 7 posts - 1 through 6 (of 6 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

t-sql to get db size

for one db

SELECT [NAME] ,(Size * 8 /1024) AS SizeMB FROM sys.database_files

for all dbs

SELECT [NAME] ,(Size * 8 /1024) AS SizeMB FROM sys.master_files

Ananda-292708 Ten Centuries Points: 1339 More actions · Answer 2

Steve i actually have a store proc which queries the metadata daily and gets the information in a history table. from this table i need to write a query which will show the monthly growth of each database .

say for e.g i want it like this

DBNameMonthGrowthInMB

db1Jan1000

db2Jan500

db1Feb1200

db2Feb800

prakash144 SSC Journeyman Points: 78 More actions · Answer 3

prakash144

SSC Journeyman

Points: 78

May 11, 2010 at 5:34 am

#1165459

Use GROUP BY and SUM.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

Friend, you have not provided the sample data for us to work on. I have created the sample data, the sample DDLs and have coded a piece. Please revert back if that's what you were after!

The tables and the sample data:

IF OBJECT_ID('TEMPDB..#Temp') IS NOT NULL

DROP TABLE #Temp

CREATE TABLE #Temp

(

DBNAME VARCHAR(3),

DATE DATETIME,

SizeInMB INT

)

INSERT INTO #Temp (DBNAME,DATE,SizeInMB)

SELECT 'DB1','2010-01-31', 10 UNION ALL

SELECT 'DB2','2010-01-31', 11 UNION ALL

SELECT 'DB3','2010-01-31', 12 UNION ALL

SELECT 'DB1','2010-02-28', 20 UNION ALL

SELECT 'DB2','2010-02-28', 25 UNION ALL

SELECT 'DB3','2010-02-28', 26 UNION ALL

SELECT 'DB1','2010-03-31', 19 UNION ALL

SELECT 'DB2','2010-03-31', 24 UNION ALL

SELECT 'DB3','2010-03-31', 25

Lets see the data:

SELECT DBNAME,DATE,SizeInMB,

ROW_NUMBER() OVER(PARTITION BY DBNAME, DATEPART(MM,DATE) ORDER BY DBNAME, DATEPART(MM,DATE) ) RN FROM #Temp

produces:

DBNAME DATE SizeInMB RN

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

DB1 2010-01-31 00:00:00.000 10 1

DB1 2010-02-28 00:00:00.000 20 1

DB1 2010-03-31 00:00:00.000 19 1

DB2 2010-01-31 00:00:00.000 11 1

DB2 2010-02-28 00:00:00.000 25 1

DB2 2010-03-31 00:00:00.000 24 1

DB3 2010-01-31 00:00:00.000 12 1

DB3 2010-02-28 00:00:00.000 26 1

DB3 2010-03-31 00:00:00.000 25 1

The code:

;WITH CTE AS

(

SELECT DBNAME,DATEPART(MM,DATE) PART_DATE , DATE ,SizeInMB,

ROW_NUMBER() OVER(PARTITION BY DBNAME ORDER BY DBNAME, DATEPART(MM,DATE)) RN FROM #Temp

)

SELECT A.DBNAME, DATENAME(MM,B.DATE) [FROM], DATENAME(MM,A.DATE) [TO], A.SizeInMB - B.SizeInMB Change_In_MB

FROM CTE A

CROSS JOIN CTE B

WHERE A.RN = B.RN+1

AND A.DBNAME = B.DBNAME

AND A.DATE <> B.DATE

Output:

DBNAME FROM TO CHANGE

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

DB1 January February 10

DB1 February March -1

DB2 January February 14

DB2 February March -1

DB3 January February 14

DB3 February March -1

Hope this helps you! Please tell us if that worked for you!

😎

Ananda-292708 Ten Centuries Points: 1339 More actions · Answer 5

Ananda-292708

Ten Centuries

Points: 1339

May 13, 2010 at 6:02 am

#1166784

wowwwww....

thanks a lot.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

Ananda-292708 (5/13/2010)
wowwwww....
thanks a lot.

wow, thanks for that! You're welcome! 😉

C'est Pras :cool:!