Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server database is too large when compared to actual data

We are using SQL Server 2014. We have one database with the size of 12 GB. I used the following query and can see the file sizes as:

SELECT ((size * 8)/1024), * 
FROM sys.sysfiles

This shows the .mdf file is 3 GB, .ldf file is 9 GB.

Then I used the following query to see the actual tables size:

SELECT 
    t.NAME AS TableName,
    i.name as indexName,
    p.[Rows],
    SUM(a.total_pages) as TotalPages, 
    SUM(a.used_pages) as UsedPages, 
    SUM(a.data_pages) as DataPages,
    (SUM(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (SUM(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (SUM(a.data_pages) * 8) / 1024 as DataSpaceMB
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
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name, p.[Rows]
ORDER BY 
    5 desc

This shows the total size is just 40 MB.

But, we are wondering what is occupying the remaining space of 3 GB in the .mdf file.

Please help us to find out the objects the objects that are taking so much space in mdf and ldf files.

Thanks a lot for your time!

like image 370
Kishore .T Avatar asked Apr 26 '17 13:04

Kishore .T


People also ask

Why is my SQL Server database so big?

The most likely problem is that you have a very large transaction log file that's never shrinking because you don't take backups. This could be a huge problem if this is production data.

Why does SQL database size increase dramatically?

SQL Server expands the size of a database (data and log file) based on the auto growth setting of an individual file to avoid space issues in the existing transactions.

How does SQL Server handle large amounts of data?

The most recommended and best option is to have a STANDBY server, restore the backup of the production database on that server, and then run the DBCC command. If the consistency checks run ok on the standby database, the production database should be ok as it is the source of the standby.


1 Answers

Try the predefined stored procedure of "sp_spaceused" to check how much space your database is using.

USE <database_name>
EXEC sp_spaceused

It will generate an output that looks like this...

enter image description here

The database name is displayed in the first column of the first row.

Note that...

database_size = reserved space + unallocated space + log space

sp_spaceused does NOT display the log space amount, but this can be calculated from the results generated by sp_spaceused.

Refer to this DataRinger.com page on sp_spaceused for more details.

The following image from this page depicts the different SQL Server areas that make up the overall database size.

enter image description here

I suspect that your database's transaction log file is what is taking up the unaccounted for space in your database.

"Manage the Size of the Transaction Log File" is a Microsoft page that provides information about the log file. This may help you with your database.

like image 141
JohnH Avatar answered Oct 12 '22 11:10

JohnH