Tombstoned records
-
We have an environment running SQL 7 SP3. We have a job for reports that rebuilds a table for this report. The job does a delete from tablename at the beginning to clear out the table. The user that runs this "job" cannot perform truncate table or drop and create the table, so delete is how this is done. Anyway, we noticed that the table's allocated space was increasing dramatically over time. sysobjects table showed the space was not released after deleting the rows. When we tested with SP4 the allocated space was not remaining. There is nothing in the MS fix list for SP4 related to this. We cannot go to SP4 in a production emvironment yet. Anyone know of this issue and how to get rid of these tombstoned records?
-
I have heard of this issue. From what I remember, the reorg tool in SQL Server 7 does not function correctly. When pages are freed up via deletion, the pages are not released back to the system. However, this was a guess from MS Tech Support..Not an official answer.
This issue came about when a SQL 7 DB was moved to SQL2000 and the SQL2000 reorg found the empty pages and reduced a 30Gb down to 20Gb...So they had 10Gb of empty pages!!
I don't recall seeing a fix listed. You could possibly try running a reorg in your maintenence plan to see if that reduces the load.
Ahhh found it..! This has info on the un-used page issue within SQL7
http://www.sql-server-performance.com/lost_data_sql_server.asp
Not much of an answer but an interesting *feature* of SQL Server 7!!!
Clive Strong
-
Try a clustered index rebuild. Might not be releasing pages. Haven't seen this, but I don't think I have anything like this in production.
Steve Jones
-
You may also want to try creating a stored proc that TRUNCATE's the table, and is called instead of the delete's.
Make sure the owner is the same for the stored proc as it is for the table.
"Keep Your Stick On the Ice" ..Red Green -
Reorgs are done weekly. There are indexes on the table, but no clustered indexes.
quote:
Try a clustered index rebuild. Might not be releasing pages. Haven't seen this, but I don't think I have anything like this in production.Steve Jones
http://www.sqlservercentral.com/columnists/sjones
-
A clustered index will physically move pages, so this might help. Maybe create one and then delete it.
A stored proc will not work for truncate. If you don't have rights, you don't have rights.
Steve Jones
-
Maybe the topic of this thread is what we are experiencing.
We have a SQL7 database that has a total data size of 2.2GB of which 1.0GB is used.
The transaction log, for the same database, is allocated 2.5GB of which only 35 MB is used! We actually have several such databases and they are chewing up our free disk space (let alone getting on my nerves).
The database mdf/ldf were originally allocated at 10MB each.
I believe one of the contributors indicated that Sql Sp4 may address this issue. It’s sure time to find out.
Bill
-
Bill,
In my testing, SP4 has seemed to "fix" this "bug" although the tombstoned records are left in the DB. I have yet to test a reorg of the DB with SP4 to see if this will free up the space.
Thanks Microsoft for keeping this dirty little secret.
quote:
Maybe the topic of this thread is what we are experiencing.We have a SQL7 database that has a total data size of 2.2GB of which 1.0GB is used.
The transaction log, for the same database, is allocated 2.5GB of which only 35 MB is used! We actually have several such databases and they are chewing up our free disk space (let alone getting on my nerves).
The database mdf/ldf were originally allocated at 10MB each.
I believe one of the contributors indicated that Sql Sp4 may address this issue. It’s sure time to find out.
Bill
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply