SQL Server 2012 – What Happens When the Database Log File is Full?

sql serversql-server-2012

Recently we found out that .ldf file of our Microsoft SQL Server 2012 database was growing large. We have set limit as follows:

Now we have a question: What will happen when log .ldf file reaches its size limit?

Thank you

Best Answer

If the log is full, your database will fail any transaction that requires something written to the log (insert,update,delete, schema change, etc.).

From your screenshot, are you really planning to limit it to 15MB? How big is the database? How big is the largest table in the database? You also mention that it is growing very large. How do you define very large?

Generally speaking, you need your log file to be at least as big as your largest index, so that if you rebuild it, it won't fail. If the log has grown, then that's usually because you need a bigger log file. The only time you'd want to shrink the log file is if you know that a 1 off operation happened that will never happen again (e.g. some sort of data load/export). If your log file is growing significantly larger than your data files, you may want to check your recovery model - if it's full then you need to be taking log backups.

Related Solutions

Sql-server – When was second log file created for database

This will query the default trace for any changes to the database, which will include the addition of files (but it will also include other things that you can't differentiate, like changing a database's compatibility level). It will also only include data that is currently in the default trace files (they age away; see this answer for details, but basically, how far back the history goes depends largely on how many trace events are captured), and requires that the default trace has not been disabled.

With all those caveats out of the way, this may at least narrow down the set of logins that have made changes to your database recently, and then you can ask them directly (assuming you can map logins to humans):

DECLARE @path NVARCHAR(260);

SELECT @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM sys.trace WHERE is_default = 1;

SELECT LoginName, NTUserName, HostName, StartTime
  FROM sys.fn_trace_gettable(@path, DEFAULT)
  WHERE EventClass = 164                 -- Object:Altered
  AND DatabaseName = N'my_database_name' -- ***CHANGE THIS***
  AND ObjectID IS NULL                   -- not an object
  AND ObjectType = 16964                 -- database
  AND EventSubClass = 1                  -- only need one of the row pair
  ORDER BY StartTime DESC;

Sql-server – Shirking of Log file on Primary Server of Transaction Log shipping

I have a database with size 200 GB Total(mdf+ldf) and Log file(ldf) with 50 GB and 99% unallocated space in ldf file i.e. not being used at all.

Leave it as it is, the space would eventually be utilized without any autogrowth and that would really help your SQL Server transactions, so just DONT shrink.

FYI

You can shrink log files on primary server
DONT change the recovery model this would break logshipping. Yes you can shrink without changing recovery model
The log file will eventually again grow so there is no sense in shrinking.
You may use NO truncate but normal shrinking would just do fine. This is just to answer your queries I would still say do not shrink.

Further reading Why Does Transaction Log file Growing and Runs out of the Space

Best Answer

Related Solutions

Sql-server – When was second log file created for database

Sql-server – Shirking of Log file on Primary Server of Transaction Log shipping

Related Question