Alert error number 9002

Posted on 2006-05-31
Last Modified: 2012-05-05
Hi experts,

I juset check the Alert from SQL server agent folder and seen the fatal error message as following, my question is how can I free up the log space? Thanks.

"The log file for database '%.*ls' is full. Back up the transaction log for the database to free up some log space."
Question by:LIULIHUA
    LVL 142

    Accepted Solution

    well, reading the second sentence of the message tells you want to do.

    now, the sql server agent folder contains some error messages on which to react to, this does NOT mean the error actually occurred!
    LVL 28

    Expert Comment

    Run this statement in query analyzer to clear your log file.

    dump transaction yourdatabase with no_log
    LVL 13

    Assisted Solution

    Woww... Don't follow Imran's advice right off the bat!! That is great advice if you are -NOT- in Full or Bulk Logged recovery mode. It is fine advice if you -DO NOT- care about your data and being able to recover to a point in time.

    If you are in Full Recovery mode and you care about backup/restore. Just do a Transaction Log backup.

    You also need to look at why this happened. Do you have free disk space and you just bumped up against the max size (because autogrow is off or restrictred by a max size?) Are you in Full Mode but -NOT- doing regular transaction log backups? Do you even need to be in Full mode.. Etc Etc.
    LVL 13

    Expert Comment

    Also listen to Angel.. Are you just looking at the available alerts or actual alerts that happened? Look at your database properties.. What is your log file size currently? What is the space available in the Log device?

    How much free space exists on the disk?
    LVL 13

    Expert Comment

    I would suggest points split between angel and mikewalsh. we led him the right direction. Imran's advice is dangerous without any qualification or explanation on how logging works. In fact it runs quite contrary to MS Best Practices.
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    I Agree

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now