Space allocated to the databases by disk letters

Question

Space allocated to the databases by disk letters

Evgeny Garaev

SSCertifiable

Points: 7422
More actions
April 19, 2021 at 10:16 pm

#3873284

Comments posted to this topic are about the item Space allocated to the databases by disk letters

Viewing 4 posts - 1 through 3 (of 3 total)

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

mikestahr Newbie Points: 1 More actions · Answer 1

CREATE PROCEDURE spDbFileAllocation
   @db VARCHAR(255) = ''
AS BEGIN
   DECLARE @qry VARCHAR(MAX) = '';

   SET @qry = CONCAT(
      ';WITH tbl AS (
         SELECT *
         FROM (SELECT [dbName]    = ''',@db,''',
                      [DiskDrive] = SUBSTRING(physical_name, 1, 3), 
                      [GB]        = (8.0*max(size))/1024/1024, 
                      name 
              FROM sys.master_files GROUP BY SUBSTRING(physical_name, 1, 3), name) x
         PIVOT (
            max(GB) 
            FOR name IN ([',@db,'], [',@db,'_log]) 
         ) p
      ) SELECT [dbName]    = dbName,
               [Drive]     = DiskDrive,
               [Database]  = ',@db,', 
               [LogFile]   = ',@db,'_log, 
               [TotalSize] = ',@db,' + ',@db,'_log
        FROM tbl');
   EXEC(@qry)
END

Great script! Thanks. Used it to create a generic procedure. Pivoted the data as well. I'm sure this could be improved on but it is doing the job for me.

db-solutions@harbingergroup.com Grasshopper Points: 20 More actions · Answer 2

Make sure you want to move all database or particular database.

For all database above query is fine but for particular database can we use below queries,

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name,
(size * 8) / 1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'database name'

or

use  databsename
exec sp_spaceused

Thom A SSC Guru Points: 99126 More actions · Answer 3

mikestahr wrote:

Great script! Thanks. Used it to create a generic procedure. Pivoted the data as well. I'm sure this could be improved on but it is doing the job for me.

That one is dangerous, the object names are being injected insecurely. @db should be properly parametrised for the literal strings (which should also be an nvarchar), which is impossible with syntax like EXEC(@SQL) instead of using sys.sp_executesql, and it should be injected securely for the dynamic objects with QUOTENAME. Also, the parameter for the proc should be a sysname; no need to open the injection attack more with an varchar(255).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk