| Author |
Topic |
|
Hitesh Shah
Yak Posting Veteran
80 Posts |
 Posted - 2007-05-17 : 06:30:50
|
| Our data files (.mdf ) and log files (.ldf) are increasing at an alarming rate. We will soon reach the hard disk limit. Considering the transactions , this kind of growth is not justified . What are options available to me to reclaim the space from logs and data file. I am not very expert on dbcc commands .So pointers to some other good threads also will help to me . |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-17 : 07:23:41
|
| First you need to find out why they are growing.You could be saving data that you don't need - i.e. materialising joins. That's the likely explanation of increasing growth rate.As to the log - that's probably the same cause. You could batch the data updates to let the log clear (also make more frequent log backups if you're not using simlpe model).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-05-17 : 07:26:50
|
| Also, check for the following possibilities for growth:1. Triggers which are storing audit information2. Missing conditions in JOINS cause CROSS JOINSHarsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-17 : 09:09:17
|
| This might help to see which tables are "big" and, if you run it periodically and keep & compare the results, which tables are growing.http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762"Our data files (.mdf ) and log files (.ldf) are increasing at an alarming rate."What is the size of your MDF and LDF files now?Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-17 : 09:56:51
|
| How often do you backup log? |
|
|
|
Hitesh Shah
Yak Posting Veteran
80 Posts |
Posted - 2007-05-18 : 01:34:29
|
| Thanks for ur valuable input . I would study the links in detail.Our scheduled backup is not working . Will try to resolve that. We had done full backup in JAN 2007 . I would do the same this weekend itself.It's set to grow automatically in 100 mb for data file and 200 mb for logfile unrestricted file growth . Recovery mode is full . Auto create and update statistics on. Autoshrink is on . Torn page detection on on. Data and log file are 10gb + 100 Gb respectively. Definitely backup will let me reclaim some space . If possible do give some pointers to do some step by step work for this with specific preferable options, it will help me a lot . |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-05-18 : 02:57:54
|
my 2 cents: autoshrink on is hardly ever desirable. if the db had to grow, and you shrink it, it will likely just grow again. you are very likely wasting cycles allocating and releasing extents in a never ending competition (since you have autogrow on as well)if you don't believe me, read this (paul randal knows what he's talking about, unlike me :) http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx
www.elsasoft.org |
|
|
|
Hitesh Shah
Yak Posting Veteran
80 Posts |
Posted - 2007-05-18 : 03:04:37
|
| As far as databases are concerned , I am novice . And I am more than willing to learn any practical perspectives on this . Sure I'll grow through the link provided by you. Thanks for ur response. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-19 : 07:29:14
|
| "Autoshrink is on ... log file (on 10gb MDF) is 100 Gb"Hmmm ... something wrong there for sure!"We had done full backup in JAN 2007"That may be why!Turn Autoshrink off, its a bad idea.Perform a one-time shrink of the database, check that the LDF file is down to "almost nothing"Monitor it and see how quickly it grows.Make sure you are backing up the Transaction log regualarly. I recommend every 15 minutes. If you think you have a good reason for backing it up LESS OFTEN than every 15 minutes I'm happy to debate it with you!If you do NOT need Transaction Log Backups change the Recovery Model to SIMPLE.See this link for more details:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Why%20is%20my%20LDF%20Log%20File%20so%20bigKristen |
|
|
|
Hitesh Shah
Yak Posting Veteran
80 Posts |
Posted - 2007-05-19 : 07:55:47
|
| Thanks for the input .I 'll get back to you with results ( which i am sure will be great with inputs from u) . |
|
|
|
Hitesh Shah
Yak Posting Veteran
80 Posts |
Posted - 2007-05-23 : 10:44:20
|
| Finally we could reclaim the space with all ur help . The pointers given by u were very helpful . Thanks a lot . |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-23 : 13:40:49
|
| Glad you got it sorted out, and thanks for coming back and letting us know.Kristen |
|
|
|
|
exponential growth of dbf and mdf files