Post reply

SQL query help

  • 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.

  • May 11, 2010 at 4:47 am

    #1165443

    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

  • May 11, 2010 at 5:12 am

    #1165449

    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

    DBName Month GrowthInMB

    db1 Jan 1000

    db2 Jan 500

    db1 Feb 1200

    db2 Feb 800

  • May 11, 2010 at 5:34 am

    #1165459

    Use GROUP BY and SUM.

  • May 12, 2010 at 8:46 am

    #1166278

    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!

    😎

  • May 13, 2010 at 6:02 am

    #1166784

    wowwwww....

    thanks a lot.

  • May 13, 2010 at 6:41 am

    #1166806

    Ananda-292708 (5/13/2010)

    wowwwww....

    thanks a lot.

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

    C'est Pras :cool:!

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

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