Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 618
  • Last Modified:

Alert error number 9002

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."
0
LIULIHUA
Asked:
LIULIHUA
  • 3
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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!
0
 
imran_fastCommented:
Run this statement in query analyzer to clear your log file.

dump transaction yourdatabase with no_log
0
 
MikeWalshCommented:
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.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
MikeWalshCommented:
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?
0
 
MikeWalshCommented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I Agree
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now