|
Subject:
|
backup transaction log file
|
|
Posted By:
|
rohit_sant
|
Post Date:
|
6/7/2005 8:43:37 AM
|
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.
|
|
Reply By:
|
Sunil_thomas
|
Reply Date:
|
6/9/2005 5:03:34 PM
|
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
|
|
Reply By:
|
happygv
|
Reply Date:
|
6/10/2005 5:04:19 AM
|
quote:
i do not want to increase the size of the transaction log file
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
|
|
Reply By:
|
mornao
|
Reply Date:
|
9/26/2006 11:09:58 AM
|
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.
|
