• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 519
  • Last Modified:

transaction log full in sql server 2005

Hi,
I am getting this error and cannot find a solution for this problem even thought i shrunk the log fle several times.  here is the error:
Error: 9002, Severity: 17, State: 2
The transaction log for database '%.*ls' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
0
karinos57
Asked:
karinos57
  • 3
  • 3
  • 3
  • +2
1 Solution
 
Ephraim WangoyaCommented:
Check if your are running ouf disk space or you set the maximum size for the log file.
0
 
karinos57Author Commented:
i am getting disk space error and also it is set to max size for the log file but  i tried to change the log file to grow automatically but it won't let me do anything.  pls advise
0
 
rkannan2000Commented:
Try COMMIT or ROLLBACK explicitly for the previous transactions(if any)...I hope this should fix the issue..
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
karinos57Author Commented:
i am new to sql server, can u tell me how to commit or rollback
0
 
rkannan2000Commented:
This link might help you understand the root cause and solution.

http://support.microsoft.com/kb/317375
0
 
expert_dharamCommented:
Do the following when users are not accessing the DB:

1. Detach the database
2. Locate the folder where your DBs .mdf and .ldf files are located.
3. Move(Cut-Paste) the .ldf file for the respective DB to another PC.
4. Go back to SSMS and attach the DB back by selecting the .mdf file.

This will create a new .ldf file automatically.
Keep log file setting to 'Auto Grow 10%'
(Make sure you backup your DB before performing the steps above.)

Best of luck!!
0
 
Ephraim WangoyaCommented:

@expert_dharam

Thats not a solution
0
 
Ephraim WangoyaCommented:
Try and get some free space on your disk by deleting temporary files

run the following commands

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE YOURDATABASE
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 2 MB.
DBCC SHRINKFILE (YOURDATABASELOG, 2);
GO
-- Reset the database recovery model.
ALTER DATABASE YOURDATABASE
SET RECOVERY FULL;

Now this may just solve your problem temporarily until you run out of disk space again. Install some more disk space or move your database to a server with more space
0
 
GSGDBACommented:
Yes, ewangoya is correct.
0
 
expert_dharamCommented:
@ewangoya: Thanks for correcting me.

@karinos57: Did you tried it yet?
0
 
karinos57Author Commented:
thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now