Getting SQL Server Database Size using Python on Windows

Pre-requisites:

1.       Install SQL Server or an Azure SQL Database: I have tested it with SQL Server 2012 installation

2.       Get Python: https://www.python.org/downloads/

3.       Get Microsoft ODBC Driver 11 or 13 for SQL Server Windows:  

https://www.microsoft.com/en-us/download/details.aspx?id=50420

4.       Install 'Pyodbc' library using pip or any other package manager of your choice

 

Here are the main parts of Python Script that fetches DB size - 

•  Import Pyodbc library

import pyodbc

• Define database variables

server = <ServerName>
database = <DBName>
username = <UserName>
password = <Password>

•  Define Database Connection String

cnxn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};SERVER='+server+'; 

                                        DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()   

• Define query to calculate DB size in SQL Server

cursor.execute( 

"SELECT [TYPE]= A.TYPE_DESC

       ,[FILE_Name] = A.name

       ,[FILEGROUP_NAME] = fg.name

       ,[File_Location] = A.PHYSICAL_NAME

       ,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0)

       ,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0)  
- CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0))

       ,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)

       ,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME,    'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)

       ,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -'

        WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted' ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END + CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END

        FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id order by A.TYPE desc, A.NAME;")

• Fetch result and print Database size

row = cursor.fetchone() 

print(row) 

print("File Size is '%s' MB" % row[4]) 

print("Used Spac is '%s' MB" % row[5]) 

print("Free Space is '%s' MB" % row[6])