May 28, 2008 at 4:25 am
Hi All,
I have this database in Production which doesn't contain any user tables.It is used from just messaging queue. But recentlly i have noticed that the database size is increasing rapidly over 100gb and even the backup size is very high over 100gb.
kindly let me know what could be occupying so much space.. i checked all the internal_table bu they are ok.
May 28, 2008 at 9:18 am
In order to check if a table is causing this problem, run the following query against your database:
sp_msforeachtable 'exec sp_spaceused ''?'''
Wilfred
The best things in life are the simple things
May 28, 2008 at 10:49 am
What is "ok"?
The data size grow because:
1. Someone increases it.
2. Data is added and autogrow is on.
That's it. It doesn't shrink because you don't have data. This isn't like a text file. You set the size first, however large you want to make it.
May 28, 2008 at 12:45 pm
What is the recovery model for that database? If it is full recovery model, how often are you backing up the transaction log?
What are the mdf and ldf file sizes?
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 28, 2008 at 1:52 pm
Did you setup the autogrowth option or it is in unrestricted?
MCP, MCTS (GDBA/EDA)
May 28, 2008 at 2:03 pm
One last thought, perhaps you are backing up to the same file (appending) as well. Are you using INIT in the backups?
May 29, 2008 at 2:06 am
Hi all,
thanks for all your replys,
Database has set as unrestricted growth and every time increases by 50MB,
Full Backup is scheduled every night and Logfiles every 15 minutes
Recovery Mode is set as "Full"
MDF file is 107 gb and Ldf is 1 Gb.
Pleast let me know what could be the problem
thanks again.
May 29, 2008 at 7:04 am
If you would know how much data will grow then probably I would recommend you to restrict the autogrowth option. In unrestricted file growth you can not controll anything.
MCP, MCTS (GDBA/EDA)
May 29, 2008 at 7:15 am
thanks manoj,
it is basically used as queue servce database..no user table/data resides here, as a result it is difficult to know how data would be there..moreover even i am not able to understand what has occupied so much space in it.
any idea about how to make it out?
thanks again.
May 29, 2008 at 7:28 am
Is there a lot of historical data in it that can be archived or simply deleted? I think you need to get a better handle on the application and make a decision as to what you can do to lighten the amount of data in the database. A 100GB data is not excessively large, unless you're nearly out of disk space. As suggested, take a look at your larger tables, see if the data is reasonable for the application and decide how best to proceed based on your business needs. Also, autogrowing by 50 MB on a 100GB database, depending on volume of transactions, is going to lead to some fragmentation. You may want to consider increasing the autogrow size, again depending on how often it grows, or manually setting enough free space to limit the frequency of growths. Lastly, your backup strategy seems adequate for a "full recovery" database.
-- You can't be late until you show up.
May 29, 2008 at 7:33 am
I agree with Terry. Doesn't sound like there is anything wrong.
In order to effectively manage this database you need a better handle on the application that writes to this database and the business' requirements for retaining this data. Once you have that you can implement an appropriate grooming, purging or archiving strategy.
Rob
May 29, 2008 at 7:47 am
I fully agree with Terry. I would like to add one more thing. If possible try to do the partioning on it. It would also give you some sort of relief.
MCP, MCTS (GDBA/EDA)
May 29, 2008 at 11:15 am
santhu (5/29/2008)
Hi all,thanks for all your replys,
Database has set as unrestricted growth and every time increases by 50MB,
Full Backup is scheduled every night and Logfiles every 15 minutes
Recovery Mode is set as "Full"
MDF file is 107 gb and Ldf is 1 Gb.
Pleast let me know what could be the problem
thanks again.
Okay, run the standard report from SSMS called Disk Usage by Table. To get to that report, right-click on the database, Reports, Standard Reports (I hope you are on SP2 or later, if not - the report is still there, just have to get to it differently).
You can export the results to Excel and post back here if you need additional help. This report will show you what table(s) to focus on.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 29, 2008 at 11:56 pm
Thanks Everybody.. i will try to do as said and get back with the results.
Many Thanks
Santhu
May 30, 2008 at 8:59 am
santhu (5/29/2008)
it is basically used as queue servce database..no user table/data resides here, as a result it is difficult to know how data would be there..moreover even i am not able to understand what has occupied so much space in it.
What do you mean by "Queue Service Database"? Do you mean Service Broker queue's?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply