Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Problem: I have a SQL Server database sitting on a 1 TB drive. The log will not grow past 5 MB even when the MAXSIZE is set much larger than 5 MB.

Error received:

The transaction log for database 'Temp' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Questions:

  1. How do I fix this?
  2. Why is this happening?

Code:

CREATE DATABASE Temp ON PRIMARY(
       NAME = Temp
       , FILENAME = 'C:\\Temp.mdf'
       , SIZE = 2MB
       , FILEGROWTH = 10%) 
LOG ON (
       NAME = Temp_Log
       , FILENAME = 'C:\\Temp.ldf'
       , SIZE = 1MB, MAXSIZE = 70MB
       , FILEGROWTH = 10%)

Result of select name, log_reuse_wait_desc from sys.databases where name = 'temp'; :

name, log_reuse_wait_desc
Temp, NOTHING

Additional Information:

  1. Sql Server manager says maxsize = 5mb and restricted growth
  2. Recovery mode Simple
  3. Database is handling hundreds of individual SQL Statements as it is for a data integration tool

Updated Addition information:

When the SQL statement is run directly in the SQL server manager it creates successfully and does not cause errors on implementation...On the other hand if the create database statement is run progmatically using System.Data.SqlClient and the exact same syntax it creates the Database with all defaults. I have to additionally supply alter database statements to achieve the proper behavior.

Code to run Queries:

public void AcuConvert()
        {
            using (DestD)
            {
                SqlCommand command = new SqlCommand();
                DestD.Open();
                command.Connection = DestD;
                foreach (var item in Entity.SqlDestinationQueries.ToList())
                {
                    command.CommandText = item.Query;
                    command.ExecuteNonQuery();
                }
                foreach (var item in Entity.SystemQueries.ToList())
                {
                    command.CommandText = item.Query.Replace("@Sys", SysD.Database);
                    command.ExecuteNonQuery();
                }
                foreach (var item in Entity.InsertQueries.ToList())
                {
                    command.CommandText = item.Query.Replace("@Source", SourceD.Database); ;
                    command.ExecuteNonQuery();
                }

            }
        }
share|improve this question

migrated from stackoverflow.com Oct 1 '12 at 14:10

This question came from our site for professional and enthusiast programmers.

1  
How do you know the log file is refusing to grow? What indication do you have of this? What makes you think that the log file needs to grow? –  Thomas Stringer Oct 1 '12 at 14:15
1  
select name, log_reuse_wait_desc from sys.databases where name = 'temp'; What is the output from that query? This is also probably not the case, but do you have available disk space on the drive that your transaction log file lives? –  Thomas Stringer Oct 1 '12 at 14:33
2  
@jvanh1 I was only able to duplicate this issue in my test environment by executing a statement that used more than 70MB in the log file. Can you include a creation script for the tables you are attempting to manipulate and a T-SQL script for your actual Insert/Update/Delete? –  Matt M Oct 1 '12 at 15:09
1  
What happens if you set the log file to unrestricted growth, or perhaps limit growth to 500GB or something far larger than 70MB. 70MB is really easy to outgrow, as @MattM alluded to above. –  Max Vernon Oct 1 '12 at 15:54
1  
Perhaps you have an issue that would be best directed at Microsoft Connect connect.microsoft.com/SQLServer/feedback or perhaps their SQLCat team at sqlcat.com –  Max Vernon Oct 1 '12 at 18:00

1 Answer 1

To fix the issue in the short term manually grow the log file since that's best practice anyway.

If the log file will end up being multiple GB grow it in 1 - 4 GB increments to get there.

If you don't know how big it's going to get I would recommend using at least 1 GB increments. This is a blocking operation so it should be done during a low use time if at all possible.

To discover why your problem is happening check for errors in the SQL error log, not just error message from the failed statement. There may be an error stating why it couldn't grow.

You can also look at activity monitor to see what the sessions that are having problems are waiting on. Right click on the instance in SQL Server Management Studio (SSMS) and click on "Activity Monitor." Once you have it open look at the blocking chain (Blocked By and Head Blocker) and the Wait Type to see what is causing the issue.

To determine why the DB is getting created with a MAXSIZE of 70 run Profiler to catch the CREATE DATABASE statement. That will tell you what query is getting run so you know if SQL is getting the correct query or not.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.