I have a SQL Server instance running 2008R2. For some reason the MSDB database is set to Simple Recovery model (as per default), but the Transaction Log file is >7GB.
The transaction log file (MSDBLog)'s default size is set to 7626MB.
I have run the following T-SQL to ascertain disk usage:
SELECT object_name(i.object_id) as objectName,
i.[name] as indexName,
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.indexes i
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
GROUP BY i.object_id, i.index_id, i.[name]
ORDER BY sum(a.total_pages) DESC, object_name(i.object_id)
This returns the following results (Top 15):
There doesn't seem to be any reason for the log file being that large (I dont really understand why it is in use at all given that the DB is in SIMPLE RECOVERY)?
I have tried performing SHRINK DATABASE and SHRINK FILE commands, however these will not allow me to shrink the file below 7GB.
What can I do to identify what the 7GB is being used for and also what actions can I take to reduce the size of the log file?
Thanks in advance.