The simplest way to check your SQL Server database size is using the sp_spaceused stored procedure:

USE YourDatabaseName;
EXEC sp_spaceused;

This returns two key metrics:

• database_size: Total allocated space (including unused space)
• unallocated space: Available space within the allocation

For a granular view of storage distribution by object, use this query:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceKB DESC;

To check transaction log size and usage:

DBCC SQLPERF(LOGSPACE);

Or for more details:

SELECT 
    name AS [Database Name],
    size/128.0 AS [Total Size in MB],
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS [Available Space In MB]
FROM 
    sys.database_files
WHERE 
    type_desc = 'LOG';

To see physical file distribution:

SELECT 
    name AS [File Name], 
    physical_name AS [Physical Path],
    size/128.0 AS [Size in MB],
    CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS [Used in MB],
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS [Free in MB],
    CONVERT(DECIMAL(5,2), 
        (CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0) / (size/128.0) * 100
    ) AS [Percent Used]
FROM 
    sys.database_files;

Create a stored procedure for regular monitoring:

CREATE PROCEDURE dbo.usp_GetDatabaseSizeSummary
AS
BEGIN
    -- Database summary
    EXEC sp_spaceused;
    
    -- File-level details
    SELECT 
        DB_NAME() AS DatabaseName,
        name AS FileName,
        type_desc AS FileType,
        size/128.0 AS SizeMB,
        CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS UsedMB,
        size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeMB
    FROM 
        sys.database_files;
END

The most straightforward way to get your database size is using the system stored procedure sp_spaceused:

EXEC sp_spaceused;

This returns:

database_name   database_size   unallocated space
--------------  --------------  -----------------
YourDBName      1250.50 MB      245.25 MB

For a more granular view of where your data is actually stored:

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceKB DESC;

To specifically check transaction log size:

DBCC SHOWFILESTATS;

Or for more detailed log info:

SELECT 
    name AS [File Name], 
    physical_name AS [Physical Name],
    size/128.0 AS [Total Size in MB],
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB]
FROM 
    sys.database_files
WHERE 
    type_desc = 'LOG';

For a comprehensive view including all database objects:

SELECT 
    OBJECT_NAME(i.object_id) AS ObjectName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    s.used_page_count * 8 AS UsedSizeKB,
    s.reserved_page_count * 8 AS ReservedSizeKB
FROM 
    sys.dm_db_partition_stats s
INNER JOIN 
    sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
ORDER BY 
    s.reserved_page_count DESC;

Here's a query I frequently use to identify tables consuming the most space:

WITH TableSizes AS (
    SELECT
        t.name AS TableName,
        s.Name AS SchemaName,
        p.rows AS RowCounts,
        SUM(a.total_pages) * 8 AS TotalSpaceKB
    FROM 
        sys.tables t
    INNER JOIN      
        sys.indexes i ON t.OBJECT_ID = i.object_id
    INNER JOIN 
        sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
    INNER JOIN 
        sys.allocation_units a ON p.partition_id = a.container_id
    LEFT OUTER JOIN 
        sys.schemas s ON t.schema_id = s.schema_id
    GROUP BY 
        t.Name, s.Name, p.Rows
)
SELECT TOP 10
    TableName,
    SchemaName,
    RowCounts,
    TotalSpaceKB,
    CAST(TotalSpaceKB / 1024.0 AS DECIMAL(10,2)) AS TotalSpaceMB,
    (TotalSpaceKB * 100.0) / (SELECT SUM(TotalSpaceKB) FROM TableSizes) AS PercentOfTotal
FROM 
    TableSizes
ORDER BY 
    TotalSpaceKB DESC;