Backup Transaction log

[nnmmss]

I want to insert some record to my database but i will get this error
"The log file for database 'Report_Day' is full. Back up the transaction log for the database to free up some log space."

so i tried to backup the transaction log fileby this command

1- EXEC sp_addumpdevice 'disk', 'RepDay_log1', 'C:\Program Files\Microsoft SQL Server\MSSQL$NetSDK\LOG\RepDay_log1.bak'

2- BACKUP LOG Report_Day TO RepDay_log1

The i get this error
"The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE."

what should i do to backup the transaction log?

Thank you

 

[Corran007]

Is your transcation log turned on to allow autogrow for the database?

 

[gcole]

Go into the database properties and change the Recovery Model to 'Simple' and run the following in quesry analyzer:

use database name
dbcc shrinkfile (TranLogFileName_Log ,0)
go

Change the Recovery Model back to the previous setting. This is a quick fix if your back up plan is not often enough. By all means, set up a plan to purge the file periodically. It can be every 5 minutes on an extremly busy application.

 

[SQLSister]

Shrinking Databases and Logs - SQL 7 and Higher faq183-1534

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[SQLBill]

First as said above, make sure your database and log files are set to autogrow.

Then, since your database is set for simple, you can do a BACKUP LOG dbname WITH TRUNCATE_ONLY.

-SQLBill

Posting advice: FAQ481-4875

 

[nnmmss]

Thank you all for your attention

i did as you said

use Report_Day
dbcc shrinkfile (Report_Day_Log ,0)
go

BACKUP LOG Report_Day WITH TRUNCATE_ONLY

and then i could add one or 2 more series of data but then i got this error

"Could not allocate space for object 'RepTemp' in database 'Report_Day' because the 'PRIMARY' filegroup is full."

what does this mean?
Thank you again

 

[nigelrivett]

Your log file was full.
It couldn't release entries because it couldn't log the checkpoint.
Allowing it to expand cured this and allowed entries in the log to be re-used.
You now have a very large log file which sounds like it is flling up the disk.

Now you have added some more data and have a full data file. Either it has reached it's max size of the disk is full.
Have a look at the growth and size properties for the data file and also shrink the log file
see

http://www.mindsdoor.net/SQLAdmin/TransactionLogFileGrows_1.html

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.