Turn off transaction logging? - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Administration (2000)

Turn off transaction logging?

Author

Topic

JimJudge
Starting Member

7 Posts

Posted - 2003-08-29 : 10:38:31

An error in a script caused a table in my DB (SQL 2000 running on Win 2000 Server) to grow until I have only 28 KB left on the hard drive. I would like to just delete the 29 million records that caused the problem but I don't have enough space to update the transaction log.

So, I was hoping I could turn off transaction logging and then delete the records, but I can't find anything information about turning off transaction logging. Is it possible?

Maybe there's a different way to solve my problem?

Thanks,
Jim Judge

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-08-29 : 11:08:21

restore a backup from before your mistake

Jay White
{0}

monkeybite
Posting Yak Master

152 Posts

Posted - 2003-08-29 : 12:23:08

TRUNCATE TABLE deletes every record in a table without logging.
You can also set the database recovery model to SIMPLE then use DELETE.

Dunno if either is possible with a full drive like that.
Both are dangerous.

Look this stuff up in BOL before proceeding.

~ monkey

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-08-29 : 14:35:21

DELETE Just a few rows at a time, until you get enough space to kill them all. Something like this:


/*
Change the rowcount depending on your disk space.
Initally, you'll want it small.  You should probably delete some and then shrink the database.  Keep doing this until you have enough space to delete larger and larger sets of rows
SET ROWCOUNT 10 

'Delete statement here

SET ROWCOUNT 0

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-29 : 14:48:54

Here's another example:


DECLARE @Count INT

SELECT @Count = COUNT(*)
FROM SomeTable
WHERE SomeColumn = 0

SET ROWCOUNT 10000

WHILE @Count > 0
BEGIN
	BEGIN TRAN
	
	DELETE
	FROM SomeTable
	WHERE SomeColumn = 0

		
	IF @@ERROR = 0
		COMMIT TRAN
	ELSE
		ROLLBACK TRAN		

	CHECKPOINT

	SELECT @Count = COUNT(*)
	FROM SomeTable
	WHERE SomeColumn = 0

END

SET ROWCOUNT 0

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources