locked
Query gets DB sizes but can't get sizes for system DBs RRS feed

  • Question

  • Hey everyone,

    I've got a query here to get a list of records with 2 columns for the instance it's run against. 1 for the DB name and 1 for it's size in megabytes. Here it is:

    SELECT D.NAME,ROUND(SUM(MF.SIZE) * 8 /1024,0) AS SIZE FROM SYS.MASTER_FILES MF
    INNER JOIN SYS.DATABASES D
    ON D.DATABASE_ID = MF.DATABASE_ID
    WHERE D.DATABASE_ID > 4
    GROUP BY D.NAME
    ORDER BY D.NAME
    

    and here is the output in the instance I'm using as an example:

    NAME	    SIZE
    Documentation	8

    This works great execpt for 1 problem. Here, it is easier to show you the problem. The following query shows the raw data I'm using:

    SELECT NAME, SIZE
    FROM SYS.MASTER_FILES

    and here is the output:

    NAME	SIZE
    master	512
    mastlog	160
    tempdev	1024
    templog	64
    modeldev	288
    modellog	96
    MSDBData	1632
    MSDBLog	712
    Documentation	896
    Documentation_log	160

    "Documentation" in this case is the name of the only user DB in this instance. As you can see this query gets a list of all DB files for the instance. The query above uses the names of these files to match them with the names of the databases in the SYS.DATABASES table (so that I don't get records for logs etc, and so that i can pair the results with the results of another query which returns the name). For user created databases this works great because the names match. But as you can see from the results of the 1st query the only record returned would be the 1 for the documentation database because as you can see MSDBData for iinstance is the MSDB data file, but it will not match the SYS.DATABASES record named MSDB, so it does not get picked up by the query.

    So my question is this. Am I able to modify this query (perhaps adjust the group by?) so that it will pick up the system DBs as well? Or is there a better way to do this? Keep in mind I'm using this as an SSIS source so I need to keep it all in 1 query and it needs to work against SQL 2000, 2005 and 2008. Cheers!

    Friday, February 18, 2011 2:36 PM

Answers

  • Remove that

    WHERE D.DATABASE_ID > 4

    • Proposed as answer by Naomi N Friday, February 18, 2011 4:11 PM
    • Marked as answer by Sweaty Chimp Wednesday, February 23, 2011 2:42 PM
    Friday, February 18, 2011 2:39 PM

All replies

  • Remove that

    WHERE D.DATABASE_ID > 4

    • Proposed as answer by Naomi N Friday, February 18, 2011 4:11 PM
    • Marked as answer by Sweaty Chimp Wednesday, February 23, 2011 2:42 PM
    Friday, February 18, 2011 2:39 PM
  • stefDBA is right

    By default system databases have DBID from 1 to 4

    master - 1; tempdb - 2; model - 3; msdb - 4

    specifying DATABASE_ID > 4 wont give the output relating to system databases.

    remove the line WHERE D.DATABASE_ID > 4

    hope this helps.......


    Regards, Tejas Patil
    Friday, February 18, 2011 3:50 PM
  • Hi,

    According to your description, you want to execute your query against SQL Server 2000, and above.  I recommend that you refer to the following statement:

    exec master.dbo.sp_msforeachdb "exec [?].dbo.sp_spaceused"
    
    

    The system view sys.master_files can be used in SQL Server 2005 or above. Please see:
    sys.master_files (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms186782(v=SQL.90).aspx

    Besides, we can create a stored procedure to get all database size, please see:
    http://forums.aspfree.com/microsoft-sql-server-14/asp-script-for-getting-size-of-database-125193.html

    Thanks,
    Ai-Hua Qiu


    Ai-hua Qiu[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 23, 2011 3:16 AM
  • LOL ok guys i feel like an idiot!

    stefDBA is right. Removing the where clause gave me system DBs in the list. Ai-Hua is right too though, I do want to run this against 2000 as well. Unfortunately stored procedures wont work for me because they can't be used as a data source in SSIS. The below query will work against both 2000 and 2005+ instances in SSMS but not as a data flow task source in SSIS because the metadata wont match up in all instances. Is there a good way to run the query below against all types of instances? It will need to be a single query:

    IF OBJECT_ID('SYS.DATABASES') IS NOT NULL
    BEGIN
    	--2005+ METHOD
    	SELECT D.NAME,ROUND(SUM(MF.SIZE) * 8 /1024,0) AS 
    	SIZE FROM SYS.MASTER_FILES MF
    	INNER JOIN SYS.DATABASES D
    	ON D.DATABASE_ID = MF.DATABASE_ID
    	GROUP BY D.NAME
    	ORDER BY D.NAME
    END
    ELSE
    BEGIN
    	--2000 METHOD
    	SELECT D.NAME,ROUND(SUM(MF.SIZE) * 8 /1024,0) AS 
    	SIZE FROM MASTER.DBO.SYSALTFILES MF
    	INNER JOIN MASTER.DBO.SYSDATABASES D
    	ON D.DBID = MF.DBID
    	GROUP BY D.NAME
    	ORDER BY D.NAME
    END
    
    Wednesday, February 23, 2011 2:45 PM