Link to home
Start Free TrialLog in
Avatar of dsummers801
dsummers801

asked on

How do I properly remove then recreate a SQL Log file that has gotten too large

LDF File for my SQL DB has approached the point of filling the disk it is on. What is the proper way to shrink it substantially OR remove the recreate it.

Best,
D Summers
Avatar of James Murrell
James Murrell
Flag of United Kingdom of Great Britain and Northern Ireland image

Avatar of Otana
Otana

If you want to remove and recreate it, right-click on the database in Enterprise Manager, All Tasks, detach database. Rename the LDF file, and the re-attach the database (right-click on databses, all tasks, attach database). You'll get a message saying LDF file can not be found, new one will be created. If attach worked, you can delete the old LDF file.
The link above involves database downtime. To just clear the log you can set the database recovery model to simple then use the "shrink files" option. And reset the recovery model to full. This could completely mess up your backups though!
Back it up.  Then use the GUI to shrink it.  Simplest solution with no downtime, no changes to the database.
I had this problem on my server - the underlying cause is the recoveryoptions that you have set.  You probably have it set so that it increments the log until backup, whereby the log would get truncated automatically, but I'm guessing you are not backing up the database, so the log file is just getting larger and larger...one solution is to create a Maintenance Plan (right click the DB -> All Tasks... in Enterprise Manager to create a backup plan.

Alternatively, along the lines that Martin suggests, change the Recovery Model to SIMPLE to eliminate the transaction log altogether ... though only good for datbases you don't care too much about !! :)

BOL entry: "When a database is created, it has the same recovery model as the model database. To alter the default recovery model, use ALTER DATABASE to change the recovery model of the model database. You set the recovery model with the RECOVERY clause of the ALTER DATABASE statement"
ASKER CERTIFIED SOLUTION
Avatar of PaultheBroker
PaultheBroker
Flag of Afghanistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial