hi,
i want to delete all the records from all the tables in the database. hence i started with the EM in SQL SERVER and started to type the queries from the query window in EM. after deleting some of the records from the table i got an error msg stating "The transaction log file is full. Please backup the transaction log file to free up some space". i do not want to increase the size of the transaction log file. but i need to understand how to accomplish the task mentioned in the error msg. how to backup the transaction log file. is there any query to do or a wizard or an option in menu under EM to do.
is there any other better way to do this job.
thanks.

use Truncate table instead of delete

Truncate is non logged operation and your transaction log will not be full

Backing up transaction log do the following

1.select * from sysfiles -this will give you the log file name
2.Backup database use truncate only option
3.Issue a DBCC shring file

Hope this will help

Why is that you are against it? Then you got to do something that can afford to use the space of the tran log, and that doesn't let the tranlog grow in size, which you cannot control.

Transaction log is a rough work space used by the sql server. You never know which transaction fired by you would use what amount of space in the transaction log. So in that case, you should always have set the transaction log grow automatically and periodically clean up or back up the transaction log based on the necessity. This is how you manage your db server. When you say you dont want to increase its size, you should do something that doesn't eat up much space on the tran log.

Doing a "dump tran" on db "with no_log" will clean up the space for you, if you prefer not to back up the log.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Use the commandline OSQL utility

OSQL -E -n -Q "sp_dboption <database name>, 'trunc. log on chkpt.', 'true'"

Then

OSQL -E -n -Q "BACKUP DATABASE <database name> TO DISK = 'c:\<database name>.bak'"

Then

osql -E -n -Q "use <database name>;dbcc shrinkdatabase (<database name>, TRUNCATEONLY) "

This in order is to place the database in Simple mode, create a backup (which commits all transactions to the database) then shrinks the database and leaves the log in a truncated mode (very small)

OSQL -E -n -Q "sp_dboption <database name>, 'trunc. log on chkpt.', 'false'"

Puts the database into Full backup mode.