Backup file size

select p.backup_size/POWER(2.0,30.0) ,p.database_name,p.[Server] as 'DBServer', p.physical_device_name,p.last_db_backup_date from (

SELECT

A.[Server],

A.last_db_backup_date,

B.backup_start_date,

B.expiration_date,

B.backup_size,

B.logical_device_name,

B.physical_device_name,

B.backupset_name,

B.description ,

b.database_name

FROM

(

SELECT

CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

msdb.dbo.backupset.database_name,

MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date

FROM msdb.dbo.backupmediafamily

INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE msdb..backupset.type = 'D'




  GROUP BY

msdb.dbo.backupset.database_name

) AS A




  LEFT JOIN

(

SELECT

CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,

msdb.dbo.backupset.database_name,

msdb.dbo.backupset.backup_start_date,

msdb.dbo.backupset.backup_finish_date,

msdb.dbo.backupset.expiration_date,

msdb.dbo.backupset.backup_size,

msdb.dbo.backupmediafamily.logical_device_name,

msdb.dbo.backupmediafamily.physical_device_name,

msdb.dbo.backupset.name AS backupset_name,

msdb.dbo.backupset.description

FROM msdb.dbo.backupmediafamily

INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id

WHERE msdb..backupset.type = 'D'

) AS B

ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]

and A.[last_db_backup_date] > '03/20/2017'

)

as p

where backup_size is not null

--and database_name = 'mydb' if to find size only for that db

order by database_name

Found the on sqlservercentral . Very cool demo to convert the size

* 250 Gb */
DECLARE @MY_NumberOfBytes NUMERIC(12,0) = 268435456000;
/* bytes */
SELECT
'Bytes' AS UNIT
,@MY_NumberOfBytes AS NUMBER
UNION ALL
/* Kilobytes, divide by 2^10 (1024) */
SELECT
'Kilobytes' AS UNIT
,@MY_NumberOfBytes / POWER(2.0,10.0) AS NUMBER
UNION ALL
/* Megabytes, divide by 2^20 (1048576) */
SELECT
'Megabytes' AS UNIT
,@MY_NumberOfBytes / POWER(2.0,20.0) AS NUMBER
UNION ALL
/* Gigabytes, divide by 2^30 (1073741824) */
SELECT
'Gigabytes' AS UNIT
,@MY_NumberOfBytes / POWER(2.0,30.0) AS NUMBER
UNION ALL
/* Terabytes, divide by 2^40 (1099511627776) */
SELECT
'Terabytes' AS UNIT
,@MY_NumberOfBytes / POWER(2.0,40.0) AS NUMBER;

Script: To find the database file where the file space is used more than 94 % of the current file size

--The historic data is logged in a table in every 4 hours and the following query in run in the table to find the current grown rate of the databse file

SELECT Database_Name,Space_Used_in_MB,File_Size_in_MB,(cast(Space_Used_in_MB as float)/cast(File_Size_in_MB as float)) * 100 as newval
FROM Monitor_db..SQLAdmin_Monitor_db_file_info
WHERE FILE_ID = 1 AND (cast(Space_Used_in_MB as float)/cast(File_Size_in_MB as float)) * 100 >= 95 AND Time_collected IN (SELECT MAX(time_collected) FROM hCFS_SQLMonitor..SQLAdmin_Monitor_db_file_info )
ORDER BY File_Size_in_MB

tempdb modify size, move file

Modify the size
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, SIZE = 1024 ).
The database will be resized when the server restarts next.
Move the file
ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev, FILENAME = 'x:\full\path\xxxx\tempdb\file\tempdb.mdf' /*, SIZE = .... */ ) the file will be created in the new path when the server starts next