Усечение базы MSDB

У меня MSDB занимала более 40Гб. Стандартно база не усекалась. Разбираемся..

Сначала смотрим, что именно занимает много места:

USE msdb
GO

SELECT TOP(10)
      o.[object_id]
    , obj = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
    , o.[type]
    , i.total_rows
    , i.total_size
FROM sys.objects o
JOIN (
    SELECT
          i.[object_id]
        , total_size = CAST(SUM(a.total_pages) * 8. / 1024 AS DECIMAL(18,2))
        , total_rows = SUM(CASE WHEN i.index_id IN (0, 1) AND a.[type] = 1 THEN p.[rows] END)
    FROM sys.indexes i
    JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
    JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
    WHERE i.is_disabled = 0
        AND i.is_hypothetical = 0
    GROUP BY i.[object_id]
) i ON o.[object_id] = i.[object_id]
WHERE o.[type] IN ('V', 'U', 'S')
ORDER BY i.total_size DESC

Потом запускаем:

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;

Потом усекаем:

DBCC SHRINKFILE (MSDBData, 1000)

И база стала занимать 3Гб вместе с журналом транзакций. Отлично.

TAG:

sysadmin, MSSQL, MSDB

Обсуждение

topgulfclub.blogspot.com, d.m.Y H:i

https://topgulfclub.blogspot.com

Hi theгe, I do believe your web site could be havging internet bгowser сompatibility issսes. Wһen I take a look at your sitre in Safari, it lоoks fine however, if opening inn I.E., it haѕ some overlapping isѕues. I just wԝanted to give you a quick heads up! Other than that, great website!

topgulfclub.blogspot.com, d.m.Y H:i

https://topgulfclub.blogspot.com

Hi there, Ι do believe your web site cⲟuld be having internet browser сompatiƅility issueѕ. When I tɑke a look at your site in Safari, it looks fine however, if opеning in I.E., it has some overlapping issues. I juѕt wanted to giive you a quick heads up! Other than that, great website!

AnimePorn, d.m.Y H:i

This design is wicked! You obviously know how to keep a reader amused. Between your wit and your videos, I was almost moved to start my own blog (well, almost…HaHa!) Fantastic job. I really loved what you had to say, and more than that, how you presented it. Too cool!

AnimePorn, d.m.Y H:i

This design is wicked! You obviously know how to keep a reader amused. Between your wit and your videos, I was almost moved to start my own blog (well, almost…HaHa!) Fantastic job. I really loved what you had to say, and more than that, how you presented it. Too cool!

crypto tutorial for beginners, d.m.Y H:i

I am curious to find out what blog platform you happen to be using? I'm having some small security problems with my latest blog and I would like to find something more secure. Do you have any solutions?

crypto tutorial for beginners, d.m.Y H:i

I am curious to find out what blog platform you happen to be using? I'm having some small security problems with my latest blog and I would like to find something more secure. Do you have any solutions?

تاسیس شرکت, d.m.Y H:i

Thanks for sharing your thoughts. I really appreciate your efforts and I am waiting for your next post thanks once again.

ثبت شرکت در تهران, d.m.Y H:i

Hi, the whole thing is going sound here and ofcourse every one is sharing information, that's in fact fine, keep up writing.

تاسیس شرکت در ایران, d.m.Y H:i

I really like what you guys tend to be up too. This kind of clever work and coverage! Keep up the wonderful works guys I've included you guys to my personal blogroll.

تاسیس شرکت در ایران, d.m.Y H:i

I really like what you guys tend to be up too. This kind of clever work and coverage! Keep up the wonderful works guys I've included you guys to my personal blogroll.

تاسیس شرکت در ایران, d.m.Y H:i

Hello, i think that i saw you visited my blog thus i came to “return the favor”.I am attempting to find things to enhance my website!I suppose its ok to use a few of your ideas!!

تاسیس شرکت در ایران, d.m.Y H:i

Hello, i think that i saw you visited my blog thus i came to “return the favor”.I am attempting to find things to enhance my website!I suppose its ok to use a few of your ideas!!

راهنمای ثبت شرکت در ایران, d.m.Y H:i

Right here is the right web site for anybody who really wants to understand this topic. You understand a whole lot its almost tough to argue with you (not that I actually will need to…HaHa). You certainly put a new spin on a topic that's been discussed for decades.

Excellent stuff, just excellent!

راهنمای ثبت شرکت در ایران, d.m.Y H:i

Right here is the right web site for anybody who really wants to understand this topic. You understand a whole lot its almost tough to argue with you (not that I actually will need to…HaHa). You certainly put a new spin on a topic that's been discussed for decades. Excellent stuff, just excellent!

سامانه ثبت شرکتها, d.m.Y H:i

My developer is trying to persuade me to move to .net from PHP.

I have always disliked the idea because of the expenses. But he's tryiong none the less. I've been using WordPress on a variety of websites for about a year and am anxious about switching to another platform. I have heard fantastic things about blogengine.net. Is there a way I can import all my wordpress content into it? Any help would be greatly appreciated!

سامانه ثبت شرکتها, d.m.Y H:i

My developer is trying to persuade me to move to .net from PHP. I have always disliked the idea because of the expenses. But he's tryiong none the less. I've been using WordPress on a variety of websites for about a year and am anxious about switching to another platform. I have heard fantastic things about blogengine.net. Is there a way I can import all my wordpress content into it? Any help would be greatly appreciated!

ثبت شرکت, d.m.Y H:i

Thank you for sharing your thoughts. I truly appreciate your efforts and I will be waiting for your further post thanks once again.

ثبت شرکت, d.m.Y H:i

Thank you for sharing your thoughts. I truly appreciate your efforts and I will be waiting for your further post thanks once again.

magicboxpro.flowcartz.com, d.m.Y H:i

https://magicboxpro.flowcartz.com/

My partner and I stumbled over here by a different page and thought I might as well check things out. I like what I see so now i'm following you. Look forward to checking out your web page for a second time.

magicboxpro.flowcartz.com, d.m.Y H:i

https://magicboxpro.flowcartz.com/

My partner and I stumbled over here by a different page and thought I might as well check things out. I like what I see so now i'm following you. Look forward to checking out your web page for a second time.

راهنمای ثبت شرکت در تهران, d.m.Y H:i

I have read so many posts on the topic of the blogger lovers however this post is really a good paragraph, keep it up.

راهنمای ثبت شرکت, d.m.Y H:i

Thanks a lot for sharing this with all people you actually know what you're speaking approximately! Bookmarked. Kindly additionally visit my website . We could have a link change contract between us

Полное имя:

Эл. адрес:

Веб-сайт:

Адрес:

Ваш комментарий. Вики-синтаксис разрешён:

Подписаться на комментарии