?
Solved

Huge msdb database

Posted on 2009-12-17
12
Medium Priority
?
597 Views
Last Modified: 2012-05-08
I have a client that is on SQL Server 2000 Standard with service pack 4.  Although they have very few SQL jobs, their msdb database is huge.  It is over 7.50 GB!

First, why would this database get so big?  Secondly what is the cleanest way to shrink this database?

Normally, I would not be so concerned about this database?  But, I stumbled upon this after discovering that their nightly backup job was unable to run due to low disk space.  I was able to free up a ton of space.  So, they are "good" now.  I think,  though, that it might be a good idea to reduce the size of this database and see what can be done to prevent it from getting that big again.
0
Comment
Question by:apitech
  • 4
  • 4
  • 2
  • +2
12 Comments
 
LVL 13

Expert Comment

by:sameer2010
ID: 26073824
Hi,

Did you check the size of LOGs? That could be one of the reasons for growing size.
Other reason could be inefficient space allocation to DB, Tables.
Also, check "select * from dbo.sysfiles", "select * from sys.allocation_units" to check the sizes used and growth set.

Thanks,
Sam
0
 
LVL 1

Author Comment

by:apitech
ID: 26074124
When I run select * from dbo.sysfiles, the size of the MSDBData file is 957248, while the size of the MSDBLog file is only 288.  Running select * from sys.allocation_units brought up an error saying that that is an invalid table.

Anyway, what can I do to fix this?  There is over 40GB of space on the C drive where this database sits and over 200GB of space on the backup drive.  But, I want to reduce the database's size before this becomes an issue.
0
 
LVL 13

Expert Comment

by:sameer2010
ID: 26074217
So, based on the above it appears that the size of actual db is pretty less. How about the size of virtual memory allocated? Do you have N number of backup images of the database? May be something to clean up?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26074357
More than likely the Job History table is the cause, unfortunately until you have identified the actual table(s) that are the problem there is not much we can go by.
0
 
LVL 1

Author Comment

by:apitech
ID: 26074699
How do I identify the problem tables?
0
 
LVL 1

Author Comment

by:apitech
ID: 26076902
How do I identify the problem tables?
0
 
LVL 35

Expert Comment

by:David Todd
ID: 26077194
Hi,

In Enterprise Manager, right-click on SQL Server Agent and select properties. On the job tab, check that Limit size of job history log is selected.

The entries for Maximum Job History size (rows) is quite small.

HTH
  David
0
 
LVL 4

Accepted Solution

by:
deepajannu earned 2000 total points
ID: 26078414
There may be many history tables in the msdb which is occupying the data and due to which the msdb is very huge. please find the tables which are very huge by running the below script.

--------------------------------------------
Declare @SourceDB varchar(50)
SET @SourceDB = 'msdb'
set nocount on
declare @sql varchar(128)
create table #tables(name varchar(128))
select @sql = 'insert #tables select TABLE_NAME from ' + @SourceDB + '.INFORMATION_SCHEMA.TABLES where TABLE_TYPE = ''BASE TABLE'''
exec (@sql)
create table #SpaceUsed (name varchar(128), rows varchar(11), reserved varchar(18), data varchar(18), index_size varchar(18), unused varchar(18))
declare @name varchar(128)
select @name = ''
while exists (select * from #tables where name > @name)
begin
select @name = min(name) from #tables where name > @name
select @sql = 'exec ' + @SourceDB + '..sp_executesql N''insert #SpaceUsed exec sp_spaceused ' + @name + ''''
exec (@sql)
end
select * from #SpaceUsed


---------------------------------

Once you run this script you will come to know which table is using some space and accordingly you can purge the data.



drop table #tables
drop table #SpaceUsed
0
 
LVL 1

Author Comment

by:apitech
ID: 26091362
Thanks, for this information on finding which tables are the large ones.  
Please let me know if this is the correct syntax on getting rid of the tables:

drop table 'tables name'

I just want to make sure that I'm doing this correctly.  Also, can this be done while people are in the system?  Is this something that will take days to run?  Will backup jobs still run, while this runs?
0
 
LVL 35

Expert Comment

by:David Todd
ID: 26091562
Hi,

dropping a table is very fast. I'd be surprised if it takes more than minutes to run.

I don't think you can run a backup during this. But since its only for minutes so that shouldn't be a problem.

HTH
  David
0
 
LVL 13

Expert Comment

by:sameer2010
ID: 26095838
Yes, you cannot run backup jobs while issuing DCL commands that modify/create the structures.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question