« octobre 2007 | Main | février 2008 »
janvier 23, 2008
msdb is huge!
On our SQL Server 2005 DB, I found out that the msdb.dbo.sysmaintplan_logdetail table was more than 6 GB, so I ran the following SQL code to reduce it to 60 MB. It took about 10 minutes to run the query, so don't panic.
I mainly post it here to remember what to run next time I have this problem, but it could be useful to someone else. :-)
ALTER TABLE [dbo].[sysmaintplan_log] DROP CONSTRAINT [FK_sysmaintplan_log_subplan_id]; ALTER TABLE [dbo].[sysmaintplan_logdetail] DROP CONSTRAINT [FK_sysmaintplan_log_detail_task_id]; truncate table msdb.dbo.sysmaintplan_logdetail; truncate table msdb.dbo.sysmaintplan_log; ALTER TABLE [dbo].[sysmaintplan_log] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_subplan_id] FOREIGN KEY([subplan_id]) REFERENCES [dbo].[sysmaintplan_subplans] ([subplan_id]); ALTER TABLE [dbo].[sysmaintplan_logdetail] WITH CHECK ADD CONSTRAINT [FK_sysmaintplan_log_detail_task_id] FOREIGN KEY([task_detail_id]) REFERENCES [dbo].[sysmaintplan_log] ([task_detail_id]) ON DELETE CASCADE;
From: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2573257&SiteID=1
Posted by gfk at 10:29 AM | Comments (0) | TrackBack