I had this error in one of the databases and I was trying different possible steps to fix it.
I tried to take a transactional log back up - no luck
I tried to change the recovery model to simple - no luck
The database does not have replication but it does have CDC enabled. So I tried to disable the CDC I got another error related to metadata
I tried to change the db owner to sa - no luck still error
Then the following helped me to get rid of the error that was saying replication
EXEC sp_repldone @xactid = NULL, @xact_sgno = NULL, @numtrans = 0, @time = 0, @reset = 1;
I tried to change the db owner to sa after running the above - success
I tried to disable the CDC - success
I expanded the log file - success
This is how I was able to resolve the issue.
This blog is created to add all the information and experiences that I have learnt while working on MS sql server as a DBA. The blog is also updated frequently with the real world problem that I encounter hands on at work and the resolutions to resolve them. I hope this blog will be of some use to you and you will revisit. Thank you for stopping by and you are welcome to leave comments.
Wednesday, August 30, 2017
Friday, June 9, 2017
MSDB too large
C drive was growing very fast and then saw that the msdb data file and log file was huge.
I shrank the msdb log file first.
I ran the following query to see the top higly populated table
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)
GO
Once running the query on MSDB , I found the follwing large tables
I ran the follwing to clear the history
use msdb
GO
exec sp_maintplan_delete_log @oldest_time = '05/12/2017'
This expands the log file so make sure you shrink file
DBCC SHRINKFILE(MSDBLog, 50) --50MB
GO
Then the following maintenance plan was scheduled to run every week.
I shrank the msdb log file first.
I ran the following query to see the top higly populated table
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)
GO
Once running the query on MSDB , I found the follwing large tables
I ran the follwing to clear the history
use msdb
GO
exec sp_maintplan_delete_log @oldest_time = '05/12/2017'
This expands the log file so make sure you shrink file
DBCC SHRINKFILE(MSDBLog, 50) --50MB
GO
Then the following maintenance plan was scheduled to run every week.
Subscribe to:
Posts (Atom)