delete data without writing to the log file

I will be deleting a large amount of data from our database , is there a way
to prevent this from writing to the log file?
Thanks in advance.As long as your tables do not have foreign keys AND you want to delete =all the data from a specific table you could use TRUNCATE. Otherwise =deleting in batches and then clearing the transaction log is your best =method.
-- Keith
"Fulya Erol" <ferol@.no.nospam.mezun.com> wrote in message =news:uaeNC11uDHA.1596@.TK2MSFTNGP10.phx.gbl...
> I will be deleting a large amount of data from our database , is there =a way
> to prevent this from writing to the log file?
> > Thanks in advance.
> >|||Hi
You could use the simple recovery model, but this is not advisable for a
production system.
John
"Fulya Erol" <ferol@.no.nospam.mezun.com> wrote in message
news:uaeNC11uDHA.1596@.TK2MSFTNGP10.phx.gbl...
> I will be deleting a large amount of data from our database , is there a
way
> to prevent this from writing to the log file?
> Thanks in advance.
>|||That won't prevent the data from being written to the log. SQL Server will
auto-truncate the log when the checkpoint process wakes up... but
transactional integrity is maintianed in SIMPLE which means the entirer
transaction and all it's IO still goes to the log...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:k86Ab.110$NX6.916578@.news-text.cableinet.net...
> Hi
> You could use the simple recovery model, but this is not advisable for a
> production system.
> John
> "Fulya Erol" <ferol@.no.nospam.mezun.com> wrote in message
> news:uaeNC11uDHA.1596@.TK2MSFTNGP10.phx.gbl...
> > I will be deleting a large amount of data from our database , is there a
> way
> > to prevent this from writing to the log file?
> >
> > Thanks in advance.
> >
> >
>

delete data

Hello I have a little problem with deleting data from database. I have to tables

Category:

CategoryID,CategoryName,CategoryImageID - collumns

CategoryImage - table

CategoryImageID,CategoryImage - collumns

I have foreign key between Category.CategoryImageID and CategoryImage.CategoryImageID

How can I delete category from Category table.

Due to the foreign key relationship, wouldn't you have to delete first the record on CategoryImage and THEN on Category

|||

Did you mean?

ALTER PROCEDURECategory_Delete

(@.CategoryIDint,

@.CategoryImageIDint)

AS

DELETE FROMCategoryImage

WHERECategoryImageID = @.CategoryImageID

DELETE FROMCategory

WHERECategoryID = @.CategoryID

Or how to write it ?

|||

Maybe like this one?

ALTER PROCEDURECategory_Delete

@.CategoryIDint

AS

DELETE FROMCategoryImage

WHERECategoryImageID in (select CategoryImageID from Category where CategoryID = @.CategoryID)

DELETE FROMCategory

WHERECategoryID = @.CategoryID

|||

The DELETE statement conflicted with the REFERENCE constraint "FK_Category_CategoryImage". The conflict occurred in database "AmoFurnitureDatabase", table "dbo.Category", column 'CategoryImageID'.

The statement has been terminated.

(1 row(s) affected)

(0 row(s) returned)

@.RETURN_VALUE = -6

It deleted only category but not categoryimage

|||

looks that you had opposite relations than I thought so you have to delete first from categories and next from images but it is not good so better is to modify your foreign key. What is you key definition?

|||

Yes It looks like and what worse it is. I have primary key in categoryImageID in CategoryImage table but in Category I have only categoryImageID without set primary key and I think I need primary keys on both because I cant set opposite foreign key. Will it be ok to set primary keys in both tables?

|||

yes you can do this, primary key is table specific and you can have only one in table and few foreign keys if you need them.

Good luck

|||

Thanks

delete all tables

Hi folk,

whats the SQL syntax of deleting all user tables of a specific database on a Microsoft SQL server?

thanks in advance,

mulata

use yourTable;
go
EXEC sp_MSforeachtable @.command1 = "DROP TABLE ?"

--"sp_MSforeachtable" is one of the undocumented SQL Server system stored procedures.

|||

hm sorry I don"t understand your solution.

Is there a way that you do that in a "one line SQL statement" ,

something like: "drop table (select * from usertables) "

thanks

|||I agree with limon's solution, which is really straight and easy to use. It will go through each user table in current database and perform same action on each of them.?Since?DROP TABLE command can only be followed bytablename, I don't think you can drop all tables within one single statement except using such stored procedure.

Deinstall by deleting resistry keys

I have tried to deinstall my SQL Server Enterprise Evaluation Edition so I can reinstall my newly purchased Developer Edition License. I originally tried to upgrade, but the upgrade failed. Attempts to deinstall the from the Windows Add/Delete Programs applet have also failed
I followed the instructions at http://support.microsoft.com/?id=290991 including backing up my system state and deleting regisitry keys. I went as far as I could removing all known instances and using full text removal. When I try to reinstall, my old deleted instance name is the only one that appears in Enterprise Manager and of course I cannot see the instance I tried to install
Deinstalling the instance I just triesd to install, I went back to look at the registry again. I noticed a registry folder under LOCAL_MACHINE>SOFTWARE>MICROSOFT that was named SQL_REDIST containing a folder named 1.00.000 and one named Setup
Should these also be deleted
Thanks!I seem to have reinstalled successfully
I went back through the new install procedure and made sure to name a new instance instead of taking the default instance in the wizard. I named the new instance the exact same name as I had on the Evaluation Edition instance and everything now seems to be working.sql

Deinstall by deleting resistry keys

I have tried to deinstall my SQL Server Enterprise Evaluation Edition so I can reinstall my newly purchased Developer Edition License. I originally tried to upgrade, but the upgrade failed. Attempts to deinstall the from the Windows Add/Delete Programs ap
plet have also failed.
I followed the instructions at http://support.microsoft.com/?id=290991 including backing up my system state and deleting regisitry keys. I went as far as I could removing all known instances and using full text removal. When I try to reinstall, my old del
eted instance name is the only one that appears in Enterprise Manager and of course I cannot see the instance I tried to install.
Deinstalling the instance I just triesd to install, I went back to look at the registry again. I noticed a registry folder under LOCAL_MACHINE>SOFTWARE>MICROSOFT that was named SQL_REDIST containing a folder named 1.00.000 and one named Setup.
Should these also be deleted?
Thanks!
I seem to have reinstalled successfully.
I went back through the new install procedure and made sure to name a new instance instead of taking the default instance in the wizard. I named the new instance the exact same name as I had on the Evaluation Edition instance and everything now seems to b
e working.

Deinstall by deleting resistry keys

I have tried to deinstall my SQL Server Enterprise Evaluation Edition so I c
an reinstall my newly purchased Developer Edition License. I originally trie
d to upgrade, but the upgrade failed. Attempts to deinstall the from the Win
dows Add/Delete Programs ap
plet have also failed.
I followed the instructions at http://support.microsoft.com/?id=290991 inclu
ding backing up my system state and deleting regisitry keys. I went as far a
s I could removing all known instances and using full text removal. When I t
ry to reinstall, my old del
eted instance name is the only one that appears in Enterprise Manager and of
course I cannot see the instance I tried to install.
Deinstalling the instance I just triesd to install, I went back to look at t
he registry again. I noticed a registry folder under LOCAL_MACHINE>SOFTWARE
>MICROSOFT that was named SQL_REDIST containing a folder named 1.00.000 and
one named Setup.
Should these also be deleted?
Thanks!I seem to have reinstalled successfully.
I went back through the new install procedure and made sure to name a new in
stance instead of taking the default instance in the wizard. I named the new
instance the exact same name as I had on the Evaluation Edition instance an
d everything now seems to b
e working.