?
Solved

Log Files

Posted on 2012-08-28
18
Medium Priority
?
816 Views
Last Modified: 2014-08-02
Hello Experts,

Is there a way to shrink a large log file? I was asked to look into why a .log file from an SQL Server 2005-08 DB was large. I thought at first that maybe it's holding data that was never written or commited to the .mdf file. But I'm not sure if there is a way that I can view .log file data. So I guess I need to determine if the data in the .log file is needed before I shrink it. Can someone tell me the proper way to go about this?

Thanks in advance!!
0
Comment
Question by:asp_net2
  • 7
  • 4
  • 2
  • +2
16 Comments
 
LVL 1

Expert Comment

by:JohnBannon
ID: 38340935
The log file would be used to recover database. if it is not needed, you can reduce the size by setting the recovery type to simple.

In sql 2005 Management Studio, right click database and select properties, options and change recovery model to simple. Then right click database, tasks, shrink, database.
0
 
LVL 4

Author Comment

by:asp_net2
ID: 38340952
Ok, I hate to ask this and sound so STUPID but what does the .log file actually do anyway? Do I need to backup the log file before I do what you suggested?
0
 
LVL 1

Expert Comment

by:JohnBannon
ID: 38340982
Normally if there is a problem with a transaction sql will use the log file to rollback the data and rebuild the database. if you have no problems with the database you can follow the above to reduce logfile size and then set it back to Full, and it will start logging transaction data from then on.

For more detail see:

http://www.simple-talk.com/sql/learn-sql-server/managing-transaction-logs-in-sql-server/
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 4

Author Comment

by:asp_net2
ID: 38341043
Ok, I have another question. I'm basically asking for someone else. The two questions are below.

1.) How do you lower initial log file size while DB is running?
2.) Is there a reason why I cannot lower log file size? It will not allow me to lower the log file size, it will only allow me to increase it but not lower it.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1000 total points
ID: 38342106
Assuming you are using the default Full Recovery Model:
1) By making frequent Transaction Log backups you will prevent the Transaction Log from growing.
2) Because you have not run a Transaction Log backup.

Once you have run a Transaction Log backup you can then shrink it down to a more manageable size as follows:
USE YourDatabase
DBCC SHRINKFILE(2, 4000)          -- Assuming that you only have 1 Transaction Log file and that you want to reduce it to 4000 MB

Shrinking data files should only be done when critically needed, never every schedule it.
0
 
LVL 4

Author Comment

by:asp_net2
ID: 38342307
I need to shrink it because I only have 4gb of space left :(

My network admin said he tried all of your solutions he wants to know if there is anything else he can do.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 38342537
"Network admin"?

I take it you don't have a DBA.  That makes it tough to deal with more complex aspects of SQL Server.
0
 
LVL 4

Author Comment

by:asp_net2
ID: 38342605
It sure does... He is good though so any help you can provide would be greatly appreciated.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38343363
My network admin said he tried all of your solutions
Did you back up the Transaction Log?  In other words I am not talking about a Full Database backup, but a Transaction Log backup.  If you are not sure the following code should show the last time you did a Transaction Log backup
SELECT  MAX(backup_finish_date)
FROM    dbo.backupset b
WHERE   database_name = 'YourDatabaseNameGoesHere'
        AND [type] = 'L'

Open in new window

How big is the Transaction Log file?
0
 
LVL 4

Author Comment

by:asp_net2
ID: 38343541
The transaction log file is 63.4GB.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38343693
It sounds like a Transaction Log backup has never been done and you have transactions from a very long time ago.  If you are not prepared to do Transaction Log backups, perhaps you should just change the Recovery Model to Simple.  Just understand that by doing that you lose point-in-time restores, but if you are not doing Transaction Log backups, that is not an option.
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 38343844
Just one word to add to all the other good advice here. You and your Network Admin should find the nearest SQL Server user group and join (look on http://www.sqlpass.org/), You can also get lots of valuable info at www.sswug.org. Good luck.
0
 
LVL 4

Author Comment

by:asp_net2
ID: 38343895
@acperkins,

Could you tell me what steps I should take? Also, does it matter if the server is online or offline while performing the steps you explain?

I need to be able to do the following below. Some stuff the network admin said he had tried but with no luck.

1.) I need to shrink the .log file to whatever you believe is best. It's currently 63.4GB. So far the network admin told me that he has performed a full DB back and also performed a backup of the log file itself but the size does not decrease. He said that he also right clicked on the DB chose properties and tried to shrink the log file that way but it does not give him the option to decrease but only to increase the size.

Im starting to wonder if it's best to backup and remove the log file and start over with providing different size settings. What is your thought in that?
0
 
LVL 16

Assisted Solution

by:DcpKing
DcpKing earned 1000 total points
ID: 38344054
asp_net,
You should understand that data and log files (.mdf and .ldf files) go together. The log file is where each instruction is written out just before it's executed - so if something terrible happens you can replay the log and get back all the alterations since the last time the database was backed up. Once you've backed up the database you don't need to keep the log up to that moment.

Your log is probably so huge because backups can be done without chopping off the bit of the log that itn't needed any more ('cos you've got the results in the data backup).

To get things back under control you should take the database offline (i.e. make sure nobody is using it).  Then do a backup of it. Once that's done you should right-click the database name in SSMS and select Properties. This will give you a dialog window with several tabs. On one of them, almost at the top, you'll find a field that says "Recovery Mode". It is almost certainly set to "Full". Change it to "Simple".

SQL Server writes to the log in batches called checkpoints; with the recovery mode set to Simple the log will be truncated for you every time a checkpoint is completed, which is normally every few minutes.

hth

Mike
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38345162
Could you tell me what steps I should take?
Not without some feedback to the following questions.
1.  Please confirm you are using Full Recovery Model.  As In:
SELECT name, recovery_model_desc
FROM sys.databases d
WHERE name = 'YourDatabaseName'

Open in new window

2.  When was the last time the Transaction Log was backed up?  This is not when your network admin said he did it but rather running the following query:
SELECT  MAX(backup_finish_date)
FROM    dbo.backupset b
WHERE   database_name = 'YourDatabaseNameGoesHere'
        AND [type] = 'L' 

Open in new window

0
 
LVL 4

Author Comment

by:asp_net2
ID: 38345239
@acperkins,

Stand by, I just got into the office and will be running those shortly once I get some infor from the Network Admin.

Thank you!!!
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

749 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