« Mon premier Haïku | Main | Yes we can »

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 janvier 23, 2008 10:29 AM

Trackback Pings

TrackBack URL for this entry:
http://guillaume.filion.org/blog/mt-tb.cgi/17

Comments

Post a comment

Thanks for signing in, . Now you can comment. (sign out)

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)


Remember me?