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 |
|
DGMelkin
Starting Member
24 Posts |
 Posted - 2002-04-01 : 10:11:24
|
| We have a job that deletes everything from a table and has data inserted from a view every minute. This table is then used in a larger query/view. (We're doing this for performance reasons as the initial view is quite complicated and while it is fairly optimized, it still takes longer than people want for the front end to populate and we're trying to emulate real time information.)We're noticing problems in some clients where the table size keeps growing. These clients are running SQL 7.0 with SP3. (I'm checking on some of our other clients to see if they're set up differently and if they're having the same problem, although I haven't seen this on my personal edition of SQL 2K and on the development 7.0 that isn't service packed.) For example, the estimated size of the table is 263 b and it doesn't have indexes. With several hundred rows, it is 5MB in the morning and 400 MB by the end of the day, filling up their data file. Truncating the table seems to help this problem; however, we are running transaction log backups and while I know the log backup jobs don't fail, I haven't had a chance to test to see if those logs are still good for point in time recovery scenarios. The databases are live and have mission critical information in them, so I prefer being extra cautious about backups. (I know this wouldn't be a problem with SQL2K recovery modes, but we're not running that right now.) However, the problem is the data file filling and not the transaction logs.Hopefully, this gives you enough of a background. So, my question is: why would DELETE FROM table cause the data size of the table to grow indefinitely? What options do we have to prevent this problem?Thanks in advance!--D |
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-04-01 : 10:51:39
|
| Does this table has text/image columns? SQL7 had a problem reclaiming text/image pages after data was deleted. Check KB article Q272220 for a fix:http://support.microsoft.com/default.aspx?scid=kb;en-us;Q272220---------------Strong SQL Developer wanted in the Boston area. Please e-mail if interested. |
 |
|
|
DGMelkin
Starting Member
24 Posts |
Posted - 2002-04-01 : 10:54:59
|
| The table has a couple of varchar and char fields, but no text or image. Most of the columns are numeric(28,14) or integers.I'll check out the KB anyway. What else should we look at? |
|
|
|
|
|
|
|
|
table size after delete