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

How to clean up Log file automaticaly

Hi,

I use a SQL express server 2005 and the log file going bigger and bigger.
How to automate the clean up of the log file?
(In SQL 2000 my log was clean up by the maintenance procedure)

Thanks
0
meciab
Asked:
meciab
  • 4
  • 2
  • 2
  • +2
4 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I use a SQL express server 2005 and the log file going bigger and bigger.

perform regular transaction log backups

-- or --

change the database recovery mode to SIMPLE

Be aware of the consequences of the choices
0
 
meciabAuthor Commented:
That's my problem, I don't find how to schedule transaction log backups

Could you explain a bit more the 'ecovery mode'

thanks
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Create a job containing the following statements

BACKUP LOG urDbName WITH TRUNCATE ONLY
DBCC SHRINKFILE ('urLogfileName', EMPTYFILE)

On how to choose the recovery model read this

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\adminsql.chm::/ad_bkprst_4l83.htm
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
meciabAuthor Commented:
"Create a job" Where?

And is there no agent in express? able to schedule backup (and overwrite the old one at a selected time)?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Recovery Mode:
 check out the properties of your database, you will find a setting called recovery mode.
 
Schedules:
  IN SQL Server 2000, the folder in Enterprise Manager was call "SQL Server Agent" \ "Jobs"
  IN SQL Server 2005, I have to look up in the Management Studio if it is called the same name,
0
 
Chris MangusDatabase AdministratorCommented:
There is no agent with SQL Express.  You'd have to set up a SQL batch to run at the command line via SQLCMD.exe and use Windows Scheduler to run it.  This topic was covered in depth at the November 2005 SQL Server Connections conference.  Many people were very upset that there is no job or task scheduling available in SQL Express.
0
 
imran_fastCommented:
>>log file going bigger and bigger.


Right Click your database. Go to options and change the recovery mode to simple.

create a job with this step

Dump transaction YourDatabase with No_log
0
 
meciabAuthor Commented:
thanks
0
 
imran_fastCommented:
Still need assistance?
0
 
meciabAuthor Commented:
Oups sorry
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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