Log Files

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.

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?

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/

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.

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.

"Network admin"?

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

It sure does... He is good though so any help you can provide would be greatly appreciated.

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'

Select allOpen in new window

How big is the Transaction Log file?

The transaction log file is 63.4GB.

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.

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.

@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?

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'

Select allOpen 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' 

Select allOpen in new window

@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!!!