Once again you should never consider truncating or shrinking data if you don’t have a working backup that can be restored. Now I know you followed the first step and backed up your MSDB. I found this great post on msdn that walks you through the script to truncate the sysmaintplan_logdetail table.ĪLTER TABLE.
I actually had to slap myself after typing this. We are not allowed to take the SQL Server database engine offline so we go with the next best option truncate the tables in question and shrink the database. Okay, we tried to using sp_maintplan_delete_log and it failed because the transaction log grew and consumed all the space on the drive for transactional log files. ORDER BY sum(a.total_pages) DESC, object_name(i.object_id) (sum(a.data_pages) * 8 ) / 1024 as dataSpaceMB (sum ( a.used_pages) * 8 ) / 1024 as usedSpaceMB, (sum(a.total_pages) * 8 ) / 1024 as totalSpaceMB, SELECT object_name(i.object_id) as objectName,
You can find this query written by Jeremy Kadlec in MSSQLTIP #1461 I also highly recommend reading that tip as it will provide some more helpful information to troubleshoot a large MSDB. I ran the following query to see the file size of all database objects within the MSDB database. Next, I needed to know why this database was so huge. You should also have a plan to restore the system database just in case you have to implement it. You should always make a backup before you plan on doing any changes with a system database. Backup MSDBįirst, step is to do a full backup of MSDB.
We actually reached the point where the data drive free space was consumed. This alone will create 10,000 rows daily when logging the results. This database server in particular has over 400 databases and hourly transactional logs and a full backup. We didn’t have a process on a server to delete records from the tables used to log SQL Server Agent job information. Unfortunately I am in a situation where I didn’t have another option. If you need more reasons check here (If you don’t like that example see the ones used in that post). I strongly recommend never shrinking database files. The following is the a walk-through guide towards how I resolved a problem with a MSDB that went wild.
0 kommentar(er)
