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!
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.
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.
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.
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...
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With