In my SQL Server 2000 msdb database I have several DTS and a few jobs. I also do backups daily. All this should make the msdb grow but I've removed most of the logs and histories and, still, the database is almost 1Gb big. The log file is almost at 0. It is the data file that ocupies the whole size.
I'm thinking it must be some kind of corruption of a table so I ran dbcc checkdb, checktable, cleantable, checkalloc, dbreindex and indexdefrag.
Nothing seems to work in order to make it smaller. Any ideas? Thanks
MSDB can grow by many reasons...
(a) Backup/restore history table not cleared over a period of time - To clear this sp_delete_backuphistory
(b) Job history pilled up
(d) DTS Versioning
(e) Log shipping
Madhu
|||Madhu, I checked the first three before and I don't use log shipping. Any other idea? Thanks|||Hi Marcos,
After you deleted all the history.
You will need to shrink the data file if you want to make your database smaller in size.
You can use DBCC Shrinkfile command for the above.
regards
Jag
|||Jag, I did that, but anyway, there's no available space to shrink the database. Although there is almost no data in the database it reports to be full and it won't shrink any smaller than the size it thinks it requires to keep the data it believes to have. Thanks|||BTW , what command u used to get the size of your database ?
Madhu
|||I'm using the Enterprise Manager and double checked it with the sp_helpdb command.|||to confirm it once more just runEXEC sp_spaceused @.updateusage = N'TRUE';
if still u gets the same error, why don't u run profiler and see the database activities on MSDB. Use column filter database name like MSDB and run the profiler... see the activities... you will get a clear picture
Madhu
|||Hi Marcos,
Could you please post the output of the following commands:
use msdb
go
sp_spaceused @.updateusage='true'
go
dbcc showfilestats
go
sp_helpdb msdb
go
cheers
Jag
|||Here are the results of the script.
sp_spaceused returned the same as previous results.
The profiler returned normal job work.
|||Hi,
now u find out the table which consumes the space
Use MSDB
select 'exec sp_spaceused '+name From sysobjects where xtype in ('u')
this query will give u the script for each usre table. Copy paste to QA and run ... post the result
You have not answered the other question .... Why don't u try SQL Profiler ? ... Its very helpful in such scenario
Madhu
|||Madhu,
I had tried a similar script that brought back the number of rows. I never thought that sysdtspackages rows where so heavy. In fact that table had all the weight in the database.
You were right from the begining, I hadn't cleaned old DTS versioning. I thought I had, but I had only removed the execution logs of the DTS.
Here's the script I used to clean all DTS versions other than the last one. I had to many to do it by hand. I hope you find it usefull for yourself.
declare @.name sysname,
@.cmdSQL varchar(8000)
@.CreateDate datetime
select @.name = min(name)
from sysdtspackages
while @.name is not null
begin
print '@.name: ' + @.name
select @.CreateDate = min(CreateDate)
from sysdtspackages sp
where name = @.name and
createdate < (select max(createdate)
from sysdtspackages sp2
where sp2.id = sp.id)
while @.CreateDate is not null
begin
select @.cmdSQL = 'exec sp_drop_dtspackage NULL, NULL, "' + cast(VersionID as varchar(8000))+ '"'
from sysdtspackages
where name = @.name and
createdate = @.createdate
print @.cmdSQL
exec (@.cmdSQL)
select @.CreateDate = min(CreateDate)
from sysdtspackages sp
where name = @.name and
createdate < (select max(createdate)
from sysdtspackages sp2
where sp2.id = sp.id) and
CreateDate > @.CreateDate
end
select @.name = min(name)
from sysdtspackages
where name > @.name
end
Thanks a lot
No comments:
Post a Comment