Solved

Transaction log full, simple recovery in SQL 2005 (with 80 compatibilty)

Posted on 2008-06-19
6
1,655 Views
Last Modified: 2008-07-01
Hi Experts.
the DBA left without notice, we just found out that a database was in Simple recovery mode.. the log just got maxed out (2 gig space)...
The database has not been backed up for three weeks, and never a log back up
I just gave it abother gig of space, but I would like to do a full backup and make sure the log is (included/backed-up/truncated) (by incuded i mean to make sure the log is written in the database before truncating, or is my job)
I am a little freaked out, since it has been a long time, and the fact that log shouldn't have been maxed-out, I know there was some major imports happening, but doesn't check-point make that space available for the log to be used again?

anyway, please let me know, how to backup, and truncate safely... TIA
0
Comment
Question by:sir-rob
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 8

Expert Comment

by:sbagireddi
ID: 21824914
When large transactions take place the log will fill up, until the transaction is committed, at that point the log is truncated.
0
 
LVL 32

Expert Comment

by:bhess1
ID: 21824951
Try these commands in a query window:

BACKUP LOG MyDatabase
  WITH TRUNCATE_ONLY -- truncate log

BACKUP DATABASE MyDatabase                   -- backup database
TO DISK='E:\My\Backup\Disk\Mydatabase.bak'
WITH init, skip
0
 
LVL 32

Expert Comment

by:bhess1
ID: 21824959
Also, i'm a bit surprised things grew that much in SIMPLE mode.  How much of the log space is free?  If none, what is holding open a transaction for this long?
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

Author Comment

by:sir-rob
ID: 21824989
how can I tell how much space is avaiable?

Also, when I do (WITH TRUNCATE_ONLY -- truncate log), would that still back up everything safely (no loss of data)
0
 
LVL 32

Accepted Solution

by:
bhess1 earned 500 total points
ID: 21825009
Yes - no loss of data.

As to free space - Right-Click on the database, and select Tasks - Shrink - Files.  Selecting the file type in the dropdown (data or log) will show how much free space is available.  You can SHRINK the log file if you need the space.
0
 

Author Comment

by:sir-rob
ID: 21825081
ok, so I did "Right-Click on the database, and select Tasks - Shrink - Files." the space has not been released yet... so the extra gig now is being used up, so I need to (please correct me if I am wrong)
* first find out why the log is not  being released (how do I do that?)
then there is an option in the same (shrink file window) that says
* released unused space, or reorganize pages before releasing spce, or rmpty in another file...
My question is can I release the space here, and get it over with? (I should first figyre out why the log keeps getting bigger though, no?)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question