Solved

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

Posted on 2007-11-30
6
1,544 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:dsummers801
6 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 20381348
0
 
LVL 11

Expert Comment

by:Otana
ID: 20381356
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.
0
 
LVL 3

Expert Comment

by:Martin-Smith
ID: 20381375
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!
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 21

Expert Comment

by:Kevin3NF
ID: 20381885
Back it up.  Then use the GUI to shrink it.  Simplest solution with no downtime, no changes to the database.
0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20382549
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"
0
 
LVL 6

Accepted Solution

by:
PaultheBroker earned 125 total points
ID: 20382777
So what I'm saying is that if you have a backup schedule, then the log file should automatically truncate itself, so you shouldn't need to worry about this, and if you don't have a backup schedule, then you probably don't need the log file anyway (if your system goes down, you are f***d anyway).

And I think just running a backup on the database should clear out the log file, so that is the only thing you need do....what am I missing guys???
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql 2014,  lock limit 5 34
sql select record as one long string 21 25
SQL view 2 28
Merge two rows in SQL 4 19
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…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

825 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