Solved

SQL Server Log File Space

Posted on 2016-10-18
6
33 Views
Last Modified: 2016-11-17
I am somewhat of a novice SQL Server user and have some general questions about SQL Server log (.ldf) files. When I create a database in Management Studio (SQL Server 2012), 2 files are automatically created - a data file (.mdf) and a log file (.ldf). My database is now quite large as I have imported millions of rows into it. The data file is approximately 45 GB. The log file is about the same size. What I don't understand is why the log file is as big as the data file and whether there is a way to significantly reduce it. I've tried shrinking it but that only gives back about 8% of the space.

I guess I really don't understand a log file's purpose. I thought it was built to ensure that a database would be "rolled back" to a starting point if a big import query failed three fourths of the way through. If that is what a log file is for, why do they have to stick around after the query runs successfully?

Maybe that's really not their purpose at all.

Can someone help me to better understand them? i've read a few things that I've found on the web but most of them are over my head.

Thanks in advance for your help.
0
Comment
Question by:cbridgman
6 Comments
 
LVL 3

Expert Comment

by:Daniel Jones
ID: 41849477
Transaction log file records all transactions and the database modifications made by each transaction. Internally it is split into the smaller parts called Virtual Log Files (VLFs)

If there is a system failure, you will need that log to bring your database back to a consistent state. Never delete or move this log unless you fully understand the ramifications of doing this.

Log record can deleted from the transaction log if:
> Transaction of which it is part has committed
> Database pages it changed have all been written to disk by a checkpoint
> Log record is not needed for a backup (full, differential, or log)
> Log record is not needed for database mirroring or transaction replication

One command that is extremely helpful in understanding how much of the transaction log is being used is DBCC SQLPERF(logspace).  This one command will give you details about the current size of all of your database transaction logs as well as the percent currently in use.

The transaction log must be truncated regularly to keep it from filling! Some factors can delay log truncation, so monitoring log size matters. Some operations can be minimally logged to reduce their impact on transaction log size.

Your goal with transaction log maintenance should be to maintain it a a reasonable size. You do this by frequently backing up the log. Once you have log backups set up and running, then do a ONE-TIME shrink of the log file to a reasonable size using DBCC SHRINKFILE.

The reason you don't want to shrink the log file repeatedly is:
> Log file growths require locking the log file. This means that all running queries have to be paused.
> Repeatedly shrinking and auto-growing log files causes a high number of Virtual Log Files  which can drastically affect performance of any activity that scans the log file

SQL Server recovery models is critical to this log file growth problem
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 41849674
My database is now quite large as I have imported millions of rows into it. The data file is approximately 45 GB. The log file is about the same size.
That's because the database recovery model for your database is set to Full Recovery Model.
With this type of recovery model it will require that you perform a transaction log backup regularly to truncate the logs and keep the file small.
If you are new to SQL Server then the best way is to create a Maintenance Plan using the wizard.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
ID: 41850527
You need to decide which recovery model you want to operate the db under.

In FULL (or BULK-LOGGED) mode, you can recover to any point in time, but you must back up the log file periodically and keep those backups.

In SIMPLE mode, you can only recover to full or differential backup points, but you do not have to back up the log file.

Which to use is primarily a business decision, since either is doable in technical terms.

For now, determine what the current recovery model is, and what is keeping the existing trans log from truncating, if anything.  If "NOTHING", then you can shrink the log file.  The "is...on" columns are just to allow you to also verify that both are 0/OFF; if either is on, you need to turn it off immediately.

SELECT name, recovery_model_desc, log_reuse_wait_desc, compatibility_level, is_auto_close_on, is_auto_shrink_on
FROM sys.databases
WHERE name = 'your_db_name'

--DBCC SHRINKFILE(2, 8192) /*shrink (primary) log file to 8GB*/
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41871689
cbridgman, is your question answered?
If so, please mark the comment or comments that answered your question so this question can be closed properly.
Cheers
0
 

Author Closing Comment

by:cbridgman
ID: 41891463
Thanks for everyone's help on this. Sorry for the delay in closing this. It kind of got pushed to the bottom of the pile and I forgot about the question. At the end of the day, however, choosing simple recovery did the trick for us. With the database that I was referring to we really had no need for anything more than the ability to recover to the most recent backup
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now