Log Files

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!!
LVL 4
asp_net2Asked:
Who is Participating?
 
Anthony PerkinsCommented:
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
 
JohnBannonIT ManagerCommented:
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
 
asp_net2Author Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
JohnBannonIT ManagerCommented:
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
 
asp_net2Author Commented:
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
 
asp_net2Author Commented:
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
 
Scott PletcherSenior DBACommented:
"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
 
asp_net2Author Commented:
It sure does... He is good though so any help you can provide would be greatly appreciated.
0
 
Anthony PerkinsCommented:
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
 
asp_net2Author Commented:
The transaction log file is 63.4GB.
0
 
Anthony PerkinsCommented:
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
 
DcpKingCommented:
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
 
asp_net2Author Commented:
@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
 
DcpKingCommented:
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
 
Anthony PerkinsCommented:
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
 
asp_net2Author Commented:
@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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.