Very Large master.mdf I''d like to remedy it but I''m sure if I can.

Question

Very Large master.mdf I''d like to remedy it but I''m sure if I can.

Dan Murray

Right there with Babe

Points: 779
More actions
May 24, 2006 at 8:52 am

#115222

My master.mdf file has grown to 7 Gigs on the Server and repoerts it currently has 11% free space. This master services 16 other databases 4 of which tend to grow rapidly and have required constant attention to keep them in bounds, truncating their logs ect..
Though I'm not out of room yet this doesn't look good for future andI'm not sure what tack to take.
Help is appreciated.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Dan,

Do you know what is it that takes so much space in Master? Did you try to run sp_spaceused to see where does the space go?

Regards,Yelena Varsha

SQLBill SSC Guru Points: 51440 More actions · Answer 2

Are you doing backups on all your databases? Are you doing transaction log backups on any databases where the recovery mode is set to FULL?

I agree that's a lot of space used in Master. I would guess that you/your developers/your users are using Master to store their stored procedures and other scripts. Could someone be creating tables in Master (a no-no).

-SQLBill

Dan Murray Right there with Babe Points: 779 More actions · Answer 3

Thanks to you both Yelana and SqlBill!

I used both of your suggestions to find the problem.

First when looking into using sp_spaceused per Yelena's suggestion I tested each table in the Master database for size and usage. They were all miniscule except for one called Reasons. Puzzled I remembered SqlBill's admonition that someone may have committed the unmentionable sin of using the Master database and having checked the ownership, sure enough it was a user table.

The data in it appeared to be a year's worth of backup data from a very large table we refresh daily. The person in charge of this backup started about a year ago and has been admonished about adding unauthorized and unchecked work to the database.

When she comes in tomorrow I going to admonish her reeeeaaalll good again!

Thanks for you help.

Dan

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

Dan,

🙂 Don't be too hard.

I would start with her DBA. It looks like your backup person does not have a lot of DBA skills so I would think a DBA should be assigned to her to monitor what she is doing.

Regards,Yelena Varsha