Huge Master.db 49 gig mistery

[MisterMo]

we've had problem with our production database and after some investigatigation I notice the the master.db is very big.

I have also noticed that 3 of our production tables are in the master.db schema with a disproportionate amount of records compared to the original one

eg. our production database has 3 main tables

tbl_ticket 1 million rows
tbl_ticktet_linea 5 milions row
and tbl_payment 2 milions

in the master.db the same tables have something in the sort of

tbl_ticket 10 million rows
tbl_ticktet_linea 250 milions row
and tbl_payment 25 milions

I don't know when, how and why this is happened but is killing our server.

I need to srink the database and ensure that it doesn't get that big ever again!!!!!

oh just to clarify I am not a DBA so is/was not my fault....

let me know if you need more info.


-Mo

 

[ptheriault]

Mo,
You need to get those tables out of your master database. It looks like the application is using the tables in the master and not the tables in your user db. You can merge the data from the tables in the master db with the tables in your user db with a query. Then drop the tables in master. You should also revoke the applications permissions to master. You should never have an application creating objects in your master database.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Catadmin]

Mo,

Do as Paul suggests. User tables should NEVER be in the system databases. Remove them immediately, putting them back where they belong, and fix your application ASAP.

Don't automatically blame the DBA for this problem either. It could have been caused by several people, including the developers who wrote the code to point to the Master DB, anyone who had access to the SA password, or some crank who gave out SysAdmin permissions willy-nilly to whomever requested it.

Blame, at this point, is irrelevant. The issue is obvious. Security at your workplace is very badly implemented. Someone needs to sit down with the whole crew, (Developers, DBAs and Boss) to discuss the wonderful implications of people being able to screw with the system DBs. If, once you get this particular problem resolved, security is not tightened and fixed, you can expect this problem to raise its ugly head again at some point in the near future.

Or you can expect to lose your production data to the next hack who comes along.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

well said Cat!

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[MisterMo]

hi guys thans for the pointers, I am still not sure of what has happened.

1) there is no DBA here (tenerife) lovely place to live but difficult to find experts when you need them

2) it fall down to me to try and improve the system but as I stated before I'm not a DBA, I have some experience but that is as far as it goes.

3) back to those tables, I would like to know how it happened and why so many millions of rows!!!!!

4) the application is using the production database I check that on a daily basis so something else is writing on the master.db

5) is it safe to simply remove the 3 tables from the master without breaking the lot?

-Mo

 

[Catadmin]

I have no idea. It depends on your setup. Are there jobs or triggers or SSIS / DTS packages writing to Master? WHo owns the tables? Do you need the data within them?

You're going to have to go over your configuration with a fine tooth comb to figure this one out. Or you can just backup everything, delete the tables and cross your fingers.

Personally, I prefer the "research first" method. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

If you drop those tables I can't say won't break anything. If you are unsure where the data is coming from run SQL profiler for transactions on the master db. Also, what account has access to the master? is it just 'sa'. If you app is using 'sa'. you need to change that, now. Create an account for your app with the least permissions needed.

- Paul
- If at first you don't succeed, find out if the loser gets anything.