Huge msdb - not backup logs
-
Hi all
My SQL Server 2000 has a msdb database approaching 4GB. Backup logs are truncated everyday. I did a query to find out where all the space was and at least half of it appears to be in a table called rtblclassdefs. Being a SQL Server newby I have no idea what that is or does. Anyone any thoughts on how (or even if) I can reduce the size of the MSDB database.
Thanks in advance.
Tom.
-
It's related with API repository. BOL ref:
mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\repospr.chm::/rrsqlschema_2m79.htm
You are using API's right? I guess the versioning information is stored on that table.
-
Thanks for the reply Zubehir. No, I don't believe we are using APIs.
-
Hi Tom,
first we need to find out what table really causes the problem. Run this script then according to the results we can certainly find the big tables in msdb.
Zubeyir
use msdb
go
ALTER FUNCTION dbo.fnTableSize
(
@table_owner varchar
(10),@table_name varchar
(200))RETURNS
NUMERIC(10,3)AS
BEGIN
DECLARE @Table_Size NUMERIC(10,3)SELECT @Table_Size = CONVERT(NUMERIC(10,3),((SELECT sum(sidx.dpages)FROM dbo.sysindexes sidxWHERE sidx.indid < 2 and sidx.id = tbl.id)+(SELECT isnull(sum(sidx.used), 0)FROM dbo.sysindexes sidxWHERE sidx.indid = 255 and sidx.id = tbl.id)))FROM dbo.sysobjects AS tblINNER JOIN sysusers AS stbl ON stbl.uid = tbl.uidWHERE ((tbl.type='U' or tbl.type='S'))and stbl.name= @table_ownerand tbl.name = @table_nameRETURN @Table_Size * 8192 / 1024.0 / 1024.0 -- Returns the size in MBsEND
go
select
U.name,O
.name,dbo
.fnTableSize(U.name, O.name) [Size]from
sysobjects Ojoin
sysusers U on U.uid = O.uidwhere
O.type = 'U'order
by [Size] desc -
Hi Zubeyir
I get this error when I run that script:
Server: Msg 208, Level 16, State 6, Procedure fnTableSize, Line 31
Invalid object name 'dbo.fnTableSize'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.fnTableSize'.
However I ran a script that we have which I hope acheives a similar thing. The top results are (I hope they reproduce OK!):
Rec_id
Table_name
Nbr_of_rows
Data_space
Index_space
Total_size
Percent_of_db
Db_size
33
[dbo].[RTblClassDefs]
537
2504.00
56.00
2560.00
50.078247261346
5112.00
26
[dbo].[RTblRelships]
6922
776.00
376.00
1152.00
22.535211267606
5112.00
24
[dbo].[RTblVersions]
2333
160.00
128.00
288.00
5.633802816901
5112.00
8
[dbo].[RTblNamedObj]
2196
120.00
128.00
248.00
4.851330203443
5112.00
5
[dbo].[RTblVersionAdminInfo]
2333
176.00
16.00
192.00
3.755868544601
5112.00
7
[dbo].[RTblIfaceHier]
3349
96.00
16.00
112.00
2.190923317684
5112.00
32
[dbo].[RTblIfaceDefs]
453
32.00
56.00
88.00
1.721439749609
5112.00
30
[dbo].[RTblPropDefs]
797
56.00
16.00
72.00
1.408450704225
5112.00
31
[dbo].[RTblRelColDefs]
320
16.00
48.00
64.00
1.251956181534
5112.00
28
[dbo].[RTblProps]
393
16.00
48.00
64.00
1.251956181534
5112.00
1
[dbo].[RTblIfaceMem]
1189
48.00
16.00
64.00
1.251956181534
5112.00
29
[dbo].[RTblRelshipProps]
28
8.00
32.00
40.00
.782472613459
5112.00
16
[dbo].[RTblUMLProps]
0
.00
.00
.00
.000000000000
5112.00
17
[dbo].[RTblUMXProps]
0
.00
.00
.00
.000000000000
5112.00
18
[dbo].[RTblSIMProps]
0
.00
.00
.00
.000000000000
5112.00
19
[dbo].[RTblGENProps]
0
.00
.00
.00
.000000000000
5112.00
20
[dbo].[RTblDTMProps]
0
.00
.00
.00
.000000000000
5112.00
21
[dbo].[RTblDBMProps]
0
.00
.00
.00
.000000000000
5112.00
22
[dbo].[RTblEQMProps]
0
.00
.00
.00
.000000000000
5112.00
Thanks for your help.
Tom.
-
Hi Tom,
Sorry for the broken script. I didn't replace "alter" with "create". Anyway, you have the statistics.
I think that (and also what I've found via net) your DTS packages caused this table to fill up. Can you try
DBCC SQLPERF(logspace)
to find out what percentage is really being used? Then you may need to truncate the log file.
Hope that helps.
Zubeyir.
-
Hi Zubeyir
I appreciate your help. I ran the dbcc sqlperf (logspace) and have posted some of the results below ordered by log size. Nothing really strikes me as significant though. But notice the last entry in the table - I'm not sure what that is all about. I think that db was restored from backup.
DatabaseName
LogSizeMB
LogSpaceUsed%
Status
AbacBill
492.49219
26.741283
0
db4128
209.67969
5.3222456
0
quotes
199.99219
5.6828394
0
db4688
106.74219
11.490888
0
db4167
106.17969
67.104149
0
db4739
95.742188
9.98878
0
db4283
80.429688
10.636838
0
db5060_bak
73.117188
10.495245
0
db5060_bak1
70.179688
9.8595963
0
db4238
64.367188
10.542845
0
db4853
61.617188
11.554615
0
tempdb
56.304688
54.613571
0
db4121
52.554688
11.766947
0
db4837
50.054688
13.764242
0
db5138
46.679688
15.445606
0
db5322
46.117188
13.065391
0
db4121_1
45.367188
12.592561
0
db5358
43.367188
15.230364
0
model
0.4921875
83.531746
0
db5286_copy
6.2500954E-2
1.1789507E+15
0
Cheers,
Tom.
-
Hi Tom,
for the last shot
since your msdb is the problem database we can shrink it to get rid of the unused (or uncleared) space. Could you run thisuse master go checkpoint dump tran msdb with no_log dump tran msdb with truncate_only
Sorry Tom it took too long. Hope that this helps.
Zubeyir
-
should I be doing that to master or msdb?
-
checkpoint is a general command so master is good. dump can be applied inside or outside of the target database. It's just my habit to run maintenance commands on master db.
-
Thanks - I don't mean to doubt you, I just get nervous dicking with things I don't really understand!
I ran that command - it took about 1 second to complete and the size is pretty much the same. It's probably hard for you to say, but do you think an msdb approaching 4GB is normal? There are a couple of hundred active databases on that machine ... mostly only small though.
Thanks again
Tom
-
Never mind, it's good to ask always.
I must admin that I'm a bit confused. Let's take a look at BOL for mdsb;
The msdb database is used by SQL Server Agent for scheduling alerts and jobs, and recording operators.
Database file
Physical file name
Default size, typical setup
msdb primary data
Msdbdata.mdf
12.0 MB
msdb log
Msdblog.ldf
2.25 MB
So, it does not look normal to have a 4GB msdb database to me. According to your first post the problem table was "rtblclassdefs" right? That table is supposed to store instance data for ClassDef objects that you define. But I couldn't make a predict why it is huge. When I opened a few new databases the size for this table didn't changed, neighter when I added some DTS packages.
There must be some thing that we miss which makes msdb became too large.
Zubeyir
-
Are you sure about the units are on the queries you ran? I checked a couple of my SQL 2000 servers and msdb.dbo.RTblClassDefs was exactly the same size as yours, but in KB. I agree that 4GB is suspiciously large for msdb, but it's an amazing coincidence that your RTblClassDefs appears to have the same number of rows but is 1000 times larger.
USE
msdb
EXEC sp_spaceused RTblClassDefs
name rows reserved data index_size unused
RTblClassDefs 537 2616 KB 2504 KB 56 KB 56 KB
You might try running "DBCC UPDATEUSAGE ('msdb') WITH COUNT_ROWS" before checking object sizes, that might fix any funny values that are reported.
One thing that can grow in msdb is obsolete DTS package definitions if you edit them frequently and don't delete old versions. They might be in the RTbl... tables if you are saving them in Meta Data Services.
Another thing lurking in msdb to trip the unwary is backup history. It must be deleted manually using "sp_delete_backuphistory '<oldest date>'". This procedure is notoriously slow, especially if you have never run it. After the pain of running it the first time, the best thing to do is run it every week or so as a scheduled job.
-
Hi chaps ... thanks for the info. Scott, you are right, I ran both those commands and the rtblclassdefs shows as 56k. Which makes me more confused than ever! According to the other script I ran, that table is consuming 50% of the db space and the db is deffinately 4GB and it won't shrink ... see below:
I think there has been some editing of DTS packages by our devs. How would I go about checking and deleting old versions?
Cheers
Tom.
-
If you right-click on a package name in Enterprise Manager, one of the options is "Versions".
You can summarize the size and versions for all packages with this query:
SELECT
ISNULL(name, 'Totals:') AS name, COUNT(*) AS Versions,
REPLACE(CONVERT(varchar, CAST(SUM(DATALENGTH(packagedata)) AS money), 1), '.00', '') AS TotalSize
FROM msdb.dbo.sysdtspackages
GROUP BY name WITH ROLLUP
ORDER BY SUM(DATALENGTH(packagedata)) DESC
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply