Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

My transaction log is full!!! 1

Status
Not open for further replies.
LonnieJohnson

LonnieJohnson

Programmer
Apr 16, 2001
2,628
US
How do I resolve this? I am not able to do any inserts. We are crippled. Is there a step by step process?

ProDev, MS Access Applications
Visit me at ==> Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!
 
Sort by date Sort by votes
Backup the transaction log, this will automatically truncate it.

Hope This Helps!

Ecobb
- I hate computers!
 
Upvote 0 Downvote
To elaborate, this is take from SQL Server BOL:

"When SQL Server finishes backing up the transaction log, it automatically truncates the inactive portion of the transaction log. This inactive portion contains completed transactions and so is no longer used during the recovery process. Conversely, the active portion of the transaction log contains transactions that are still running and have not yet completed. SQL Server reuses this truncated, inactive space in the transaction log instead of allowing the transaction log to continue to grow and use more space."

It's a good idea to set up a scheduled Transaction Log backup, not only to keep the log from growing, but to also help in disaster recovery.

Hope This Helps!

Ecobb
- I hate computers!
 
Upvote 0 Downvote
We have a db maint job that backs it up regularly, but it fails a lot. We were going to detach the db, delete the log file, then reattach and have it to create a new one.

ProDev, MS Access Applications
Visit me at ==> Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!
 
Upvote 0 Downvote
For now, truncate the log:

BACKUP LOG dbname WITH TRUNCATE_ONLY

Do you need the transaction logs? If not, set the recovery mode to SIMPLE. If you might need them (you can't do a 'point-in-time' recovery without them), start backing them up.

-SQLBill
 
Upvote 0 Downvote
And after you truncate the log, immediately do a FULL backup.

-SQLBill
 
Upvote 0 Downvote
We inadvertantly deleted the log file and now we cannot do an "database attachment" or recovery.

We are lost.

ProDev, MS Access Applications
Visit me at ==> Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!
 
Upvote 0 Downvote
Well, you could just manually backup/truncate the log and save yourself some trouble, but ok.

Hope This Helps!

Ecobb
- I hate computers!
 
Upvote 0 Downvote
Can't truncate the log, it has been inadvertantly deleted. This missing log seems to currently be our problem.

ProDev, MS Access Applications
Visit me at ==> Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!
 
Upvote 0 Downvote
Okay, take a deep breath, don't panic...there may be a solution:

Go to the Books OnLine (Start>Programs>Microsoft SQL Server>Books OnLine or using Query Analyzer select HELP).

Use the Index tab and enter sp_attach_single_file_db

That command lets you attach a database when you only have the .MDF file.

Are you getting any errors?

-SQLBill
 
Upvote 0 Downvote
Could not open new database 'Client_KS'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'D:\kvcDBs\CLIENT_KS_log.LDF' may be incorrect.


ProDev, MS Access Applications
Visit me at ==> Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!
 
Upvote 0 Downvote
Here's something that MIGHT help.

First to give proper credit: I got this from another site ( it was in the Administration forum and the subject was: LDF File has been deleted - Help!

This was posted by cchitanu as a solution and it appeared to help the person with the problem. I make no promises as I have never used this.

-SQLBill
------------------------------------------

Restoring databases when only data file available

• shutdown sql

• move the current database file or rename it

• restart sql server

• create a new database of the same name and log file and location as the old database and log file

• get rid of the old database(you may be able to right click delete it in this situation or used sp_removedb)

• create a new database of the right size and shape with correct log and data file locations

• stop sql

• rename the new databases.mdf or delete it if you don't have enough space - do not touch the .ldf

• move back in the old database .mdf file or rename it back again

• restart sql server(it should come up suspect)
-------------------------------------------------------------------------------------------------------------------
• From a query window, set the status so that you can update the system tables by running
the following query:

use Master
go
sp_configure "allow", 1
go
reconfigure with override
go

• Then set the status of the DB that is giving you the problem (XXXXX) into Emergency Mode by running the following query:

update sysdatabases set status = 32768 where name = '<DBName>'
go
checkpoint
go
shutdown with nowait
go

• Go into the data directory (MSSQL7\DATA) and rename the log file associated the DB in question (XXXX.ldf) to some temporary name, such as XXXX.TMP.

• Exit the query window.




• Start up SQL Server from a DOS command window by issuing:
sqlservr -c -T3608 -T4022.

• Bring up another query window and verify that the DB is in emergency mode by issuing:
select Name, Status from Sysdatabases where name = '<DB_Name>'

• Verify that the status is 32768. If it is, then issue the query:

dbcc traceon(3604)
dbcc rebuild_log('<DB_Name>','<log_filename>') <--- You will need the quotation marks

REBUILD_LOG should take less than 5 minutes even on a very large database. It should complete with the message “DBCC execution completed”

• Take the database out of bypass recovery mode by issuing the command :

update sysdatabases set status = 0 where name = '<DBName>'

• Exit the query window and then shutdown (Ctrl-C in the DOS window) and

• restart SQL server.

• Verify the status of the database by running DBCC NEWALLOC and DBCC CHECKDB on the database.

Edited by - cchitanu on 11/17/2003 12:47:00 PM
----------------------------------------------------
 
Upvote 0 Downvote
Yes Bill. That error came after using sp_attach_single_file_db. Totally perplexing. We even called Microsoft. They are telling us to restore it to another SQL Server instance. That is not complete yet. I feel like that was 245 bucks wasted.

I will look at this. Thanks again. I really appreciate your help and think that you are the king.

ProDev, MS Access Applications
Visit me at ==> Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!
 
Upvote 0 Downvote
Well thank you. I really hope you get SOMETHING to work for you. This issue seems to be a 'fluke' because that stored procedure was created for just this issue/problem. But it's something MS should look into fixing because you aren't the only one having trouble attaching a single file database.

-SQLBill
 
Upvote 0 Downvote
He is a King and its not good if we do not give him red star because if these kings are not here then we have lot problems. These kings are helping us all the time.

Knowledge is a key of success.



 
Upvote 0 Downvote
Thanks everyone for you help. We resolved the issue with the solution given by MS Access. They instructed our NetAdmin to Resotore the dB to another Server and then move it over to it's original location from there. This worked.

ProDev, MS Access Applications
Visit me at ==> Contact me at ==>lonniejohnson@prodev.us

May God bless you beyond your imagination!!!
 
Upvote 0 Downvote
I have a question about backup transaction log.
My transaction log file is 18MB and I did backup log file under SQL enterprise manager. After that, my backup file is 18MB and I still see the transaction log file also 18MB. Then I did backup log file again and my backup file is only 135KB and when I check the transaction log file that is still 18MB.
Please tell me it is correct or some wrong in my SQL server or I did something wrong in the backup log file

Thanks in advance!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

GCL2007
  • Locked
  • Question
Simple Recovery model - Huge Trans Log
Replies
0
Views
243
GCL2007
GCL2007
CodeWell
  • Locked
  • Question
SQL Server 2017 Install Issue - Database Not in Configuration Manager
Replies
0
Views
350
CodeWell
CodeWell
JScannell
  • Locked
  • Question
SQL login on Classic ASP localhost site
Replies
0
Views
254
JScannell
JScannell
RRinTetons
  • Locked
  • Question
SQL Server 2008 R2 login auditing
Replies
0
Views
240
RRinTetons
RRinTetons
RRinTetons
  • Locked
  • Question
Logging into a database using SSMS from non-domain machine
Replies
1
Views
321
fredericofonseca
fredericofonseca

Part and Inventory Search

Sponsor

Back
Top