6

I need to get a list of database sizes for a SQL server migration project. The SQL Server server we're migrating from has three installed instances, one default and and two named. It's running SQL Server 2008 R2, on Server 2008 R2, if it matters.

To get this list, I dusted off the old T-SQL "script" I used at my last position as the defacto/accidental DBA, and it works as expected on the default instance, but returns no data on either of the named instances.

SELECT [Database Name] = DB_NAME(database_id),

   [Type] = CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'

                 WHEN Type_Desc = 'LOG'  THEN 'Log File(s)'

                 ELSE Type_Desc END,

   [Size in MB] = CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) )

FROM   sys.master_files

GROUP BY      GROUPING SETS

          (

                 (DB_NAME(database_id), Type_Desc),

                 (DB_NAME(database_id))

          )

ORDER BY      DB_NAME(database_id), Type_Desc DESC

GO

Can someone tell me why (and how to fix it), or suggest a better solution?

(It's gotta be the FROM sys.master_files bit, because what else could it be, but I'm not sure what I should be using instead on a named instance.)

EDIT: Below is what I'm seeing on my named instances, anyway... so if this code does/should work on named instances, thoughts on why it doesn't work on these would be much appreciated.

Screnshot

10

If you just need the size of the entire database then below script will help you :

SELECT  @@servername as server_name,
        d.name,
        CONVERT(DECIMAL(10,2),(SUM(size * 8.00) / 1024.00)) Size_MBs
FROM    sys.master_files mf
        INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE   d.database_id > 4 -- Skip system databases
AND     d.state_desc = 'ONLINE'-- ONLY get size for ONLINE database.
GROUP BY d.name
ORDER BY d.name

If you want to automate your migration of databases from one server to another, I highly recommend using sqlmigration - a powershell script that will migrate your databases, jobs, logins, etc from one server to another. Also, be aware of the pre and post migration steps if you are moving from one version of sql server to higher (2012 or 2014) version.

Depending on your size of databases and the amount of downtime, there are ways that you can implement to minimize the downtime for your entire migration - e.g. using logshipping or mirroring and then during cutover, just failover to the new server, etc.

Always prepare and test your migration and rollback procedures.

Edit: From your screenshot, it seems that you do not have permissions. Can you just do a select * from sys.master_files

  • Do you have databases on your named instance that you are running this script on? I ran it on named instance and it works. Also, Your script works on named instance. – Kin Shah Oct 27 '15 at 18:47
  • Sure do. If I didn't, I'd hardly need a script to list sizes of nothing. :) Anyway, I put a screenclip and minor edit in my post. – HopelessN00b Oct 27 '15 at 18:54
  • 3
    From your screenshot, it seems that you might not have permissions. Can you just do a select * from sys.master_files – Kin Shah Oct 27 '15 at 18:57
  • Ugh, I feel like such a dumbass. That's the issue. I assumed I could because I can check all the database properties through the GUI. If you want to update your answer with that, I'll be happy to accept and upvote it. – HopelessN00b Oct 27 '15 at 19:00
  • @HopelessN00b update my answer with some more info - since you are doing migration, I added bits that will help you. – Kin Shah Oct 27 '15 at 20:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.