Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jar21
51 Posts |
 Posted - 2010-03-19 : 10:15:55
|
| recently I've been viewing my backups very carefully and they appear to be growing faster every day. in october of 2009 my third aprty vendor explained to me I needed to turn of auto-shrink for optimal usage. I complied after they assured me thats exactly what I needed to do, however I'm beginning to think that was not in my best interest. its growing at almost 100MB every 3-5 days and appears to be increasing even faster as time goes on. this may not seem like a lot however with the data entry going on, those numbers just dont match up, it is a small business and something doesn't seem right.can anyone offer some advice towards attacking this problem? was turning off autoshrink truly a good idea?I have looked at the following links but have not implemented anything yet.http://msdn.microsoft.com/en-us/library/ms190488.aspxhttp://technet.microsoft.com/en-us/library/ms141819.aspxThanks for any assistance!!~Aaron |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-19 : 10:25:47
|
" third aprty vendor explained to me I needed to turn of auto-shrink for optimal usage."Well they are definitely right on that point, but they may have read it in a book and not know why, or what to do correctly  I suspect that your database is set for Full Recovery Model and the Transaction Log is not being backed up.Can you find the LDF and MDF files for the database?Is the LDF much bigger than the MDF? (Have a look again in 24 hours and see if the LDF is growing faster than the MDF)If so I'll lay money on there being no TLog backups |
 |
|
|
jar21
51 Posts |
Posted - 2010-03-19 : 10:35:53
|
| I have transactional backups every hour for 13 hours a day (one before anyone gets in and one after everyone leaves) I'll edit this post when I find the LDF and MDF (Novice, praise search engines) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-19 : 10:40:29
|
"I have transactional backups every hour for 13 hours a day"Blast! There goes my easy, quick-fix, answer!Fire up Windows Explorer and get it to search (the server where your database is) for *.MDF and *.LDF. Should be a pair of those for each database on the server (there are system databases called things like MASTER, MODEL, TEMPDB and MSDB - they should be relatively small). It would help to know the size of the MDF and LDF files.As a rule of thumb LDF sized at up to 120% of MDF is OK. It may be a lot smaller.Are you competent/comfortable to fire up the SQL tools and run some queries? Just giving you some SQL to run, to investigate things, would be easiest for us - but obviously it would help if you know how to execute raw SQL statements  |
|
|
|
jar21
51 Posts |
Posted - 2010-03-19 : 10:58:07
|
| so it appears that the current LDF size is:146,304KBand the current MDF size is4,701,248KBin comparison this sounds completely different than what you described, however I see my transactional backups and have used them before (to test the backups to make sure they were infact working...)Note: I am looking on the drive G:/PATH/MYSQL/Data to find this, there are multiple other mdf and ldf files however I'm fairly sure the ones I picked at the ones that we want to check out.another sidenote, my backups are currently at 4.31GBmy feet are wet, however my knees are dry when it comes to T-SQL that being said after going through it if I can understand what its doing I am willing to give it a try though, always more to learn. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-19 : 13:01:21
|
| Its fine that your LDF is much smaller than your MDF - it is only of possible concern when LDF > 120% * MDF size.Your LDF being that small might imply that it is being shrunk regularly (although you have said you have removed that) or you do not have Index Rebuild and Update Statistics running - those usually generate huge transactions logs. You may want to check that that housekeeping is happening, but no urgent cause for concern."my backups are currently at 4.31GB"that's in line with having 4.48GB MDF and 0.14GB LDF. So that confirms it (the date on the files will help, but they will be the date of the last SQL Server restart, or the date of the most recent expansion of the file, so may not be "really recent")So lets assume that the system is genuinely adding 100MB every few days ... I make that 9GB per year? Seems a lot.Possibilities:All historical data is being retained, maybe there is a process you need to run to purge data that is no longer required.Maybe detailed logs are recorded of user actions - to facilitate diagnosing problems. Perhaps that should be being deleted after, say, 7 days - and is not.Maybe an Audit is held of all data changes, and that can be purged (maybe you have not been using it long enough for the purge to start - e.g. if purge is "delete if older than 6 months" for example).My only suggestion is to run a query of the number of rows in each table in the DB and see which ones are growing over time - that will give you a feel for what data is being collected at such a prodigious rate.So, in summary, looks like the size increase is real data being added - and not some housekeeping snafu or somesuch.Table size script - shows number of rows, and MB used, for each table.http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762 |
|
|
|
jar21
51 Posts |
Posted - 2010-03-19 : 15:11:14
|
| Thanks for the tips and info Kristen! I'll update this post if I find anything on Monday. |
|
|
|
|
|
|
|
|
database growing at an accerlerated rate