June 17, 2008 at 7:50 am
Hello,
I've having a situation that does not seem normal. I've got a Database than in one month had a growth of 30%.
In earlier months the DB didn't get more than a 1% growth and at these strange month nothing extraodinary happened.
Any advice on what might be causing this?
Thank you.
June 17, 2008 at 8:23 am
have there been any addition SSIS/DTS Imports taken place, have you created/rebuilt any indexes. Does your db grow automtically? if so could the automatic expeansion have taiken this space... i.e. set to grow by 30%?
Gethyn Elliswww.gethynellis.com
June 17, 2008 at 7:35 pm
Is it data only or log file? Check the data file and log file sizes on disk. Sometimes log file growth rate is high when it has not been backed up. Is your database in SIMPLE recovery mode or FULL?
June 18, 2008 at 2:04 am
Ellis RemoteDBA (6/17/2008)
have there been any addition SSIS/DTS Imports taken place, have you created/rebuilt any indexes. Does your db grow automtically? if so could the automatic expeansion have taiken this space... i.e. set to grow by 30%?
Nothing that you mention was done. the DB it's not set to grow by 30%. One Data File is set to grow by 10% (by default).
SveG (6/17/2008)
Is it data only or log file? Check the data file and log file sizes on disk. Sometimes log file growth rate is high when it has not been backed up. Is your database in SIMPLE recovery mode or FULL?
DB is in FULL Recovery Mode (All DBs are and they don't show this abnormality).
Still need to confirm the older logs to check for inconsistencies. Will also try to do a SQL Server Profiler for a couple of days.
Thanks.
June 19, 2008 at 4:24 am
maybe you've got a process running a bit too often. We had the same problem a few weeks back where a process was running every few minutes and jamming up the transaction log, once we turned it off it was fine. Check the profiler.
June 19, 2008 at 5:50 am
Does this database have a front end application? Are users able to change any settings (ie. how long to keep data, ability to turn on/off trackable data, etc)
We had this problem with a network monitoring database. Turns out that one of the users found an option to change the length of time data was kept from 7 days to 90 days and the database grew about 10x the size quite quickly.
June 19, 2008 at 5:56 am
I'm afraid I can't run a Profiler on this Production DB. Just too big and I can't afford slowing down the processes at this time.
Will try to check the back logs.
June 19, 2008 at 6:30 am
If it is in FULL recovery mode, are the backups occurring? If you don't back it up regularly, the log gets really big in a system that gets used a lot. Maybe the backup job is failing for some reason?
June 19, 2008 at 7:03 am
jjssilva (6/19/2008)
I'm afraid I can't run a Profiler on this Production DB. Just too big and I can't afford slowing down the processes at this time.Will try to check the back logs.
You should run profiler from a separate machine pointing to your production server. Minimal impact on performance on the production server. Be aware, the number of events you track can cause the trace file to grow quickly. I ran it once for a "perceived" small production app but ran it for a week. Could not open a 35GB trace file so it was wasted time and effort......:pinch:
-- You can't be late until you show up.
June 19, 2008 at 7:46 am
You can run a server side trace.
Also What is the Data and Log File growths/Sizes.
Here is a rule, as your Files Grow in Size, there Growth% should be Decreased likewise.
Eg. if your Datafile is 20Gb and you have 10% growth set on the File:
[A]. it is going to get a performance hit when it tries to grow your Data File 10% i.e. 2GB Approx.
The Transaction Log grows Faster on a BIG [VLDB] Database.
Do a full backup and then SET DB to and Truncate your Transaction Log.
All the above suggestions in other REPLIES are good ones and try to see if you can take them step by step and surely you cna figure out what the issue is in a couple of hours.
Maninder
www.dbanation.com
June 19, 2008 at 7:51 am
And, very important, after you truncate the log, do another full backup
-- You can't be late until you show up.
June 19, 2008 at 2:26 pm
Check out the size of the 10 largest tables. Do any of these have TEXT columns? How much unused space do these tables have? Use sp_spaceused . Does this amount of unused space seem excessive?
I have had this same issue and the this article helped: http://support.microsoft.com/kb/924947
Francis
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply