Records Deleted But DB Size Remains Same

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.

All Forums

General SQL Server Forums

Data Corruption Issues

Records Deleted But DB Size Remains Same

Author

Topic

trusted4u
Posting Yak Master

109 Posts

Posted - 2009-02-28 : 04:35:03

Hello everybody,

I have a database of size 20gb that had 1 big table with appox. 20416512 rows, 3 days before I was shocked to know that this table is empty and the strange thing is that there is no change in the mdf nor ldf sizes at all. Anyhow the database was restored with the recent backup. I really don't know what went wrong, first I presumed that somebody deleted it so I created a Trace through SQL Profiler, although I am not familiar with DBA tools ( but still wanted to catch the culprit ) and added filter of the database name and table name in the trace. But unfortunately again the same thing repeated, table GOT EMPTY, NO change in the database size and above all NOTHING related to this table can be seen in the Trace.

Please help me..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-28 : 06:21:35

1. You can't trace a delete after it happened. You have to use a log examine tool for that.
2. TRUNCATE statement is minimally logged.
3. Databases are normally not in autoshrink mode. It they are, it's a bad practice.
4. Do you have SQL Server 2005? Create a database trigger logging all DELETE and TRUNCATE statements.
5. Do you have SQL Server 2000? Create a table trigger logging all DELETE activity.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-28 : 06:24:37

[code]CREATE TABLE tSample
(
i INT
)
GO
INSERT tSample
SELECT 1
GO
CREATE TABLE tLog
(
dt DATETIME,
un VARCHAR(20)
)
GO
CREATE TRIGGER trg_tSample_Delete
ON tSample
AFTER DELETE
AS

INSERT tLog
SELECT GETDATE(),
SUSER_SNAME()
GO
SELECT * FROM tLog
GO
DELETE FROM tSample
GO
SELECT * FROM tLog
GO
DROP TABLE tSample,
tLog
GO[/code]

E 12°55'05.63"
N 56°04'39.26"

trusted4u
Posting Yak Master

109 Posts

Posted - 2009-02-28 : 07:26:58

Thankyou PESO for your quick response, really appreciated.

trusted4u
Posting Yak Master

109 Posts

Posted - 2009-03-02 : 02:00:20

Hello everybody,

I am working on SQL Server 2000. I created the Delete trigger but unfortunately someone is dropping the trigger also. And moreover, the same problem of rows getting deleted is happening again and again. The biggest problem now is, as we don't have any DBA, all the developers are using SA login. Is there anyway I can identify the root cause and resolve this problem ?

Please help...

Thanks,
- Marjo.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-02 : 06:32:56

You could always DENY DELETE on table and wait to see which developer complains first...

E 12°55'05.63"
N 56°04'39.26"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-03-02 : 09:55:36

"all the developers are using SA login"...and move away from this Worst practice as well.

you now have evidence of 1 part of the cost of working this way. build up a balanced arguement and submit same to senior management.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources