locked
Transactions that they cant commit due to space issue RRS feed

  • Question

  • I have 100 GB total space in drive and 50 GB is size looking in Txn log file , 50 GB size is free still in event viewer the error is coming for transactions  that they cant commit due to space issue...What should I do...
    Monday, August 16, 2010 12:24 PM

Answers

  • Look in the errorlog to see where the space runs out. Perhaps you have a limit on the database for growth. Check with:
    EXEC sp_helpdb dbname

    Also check if tempDB is on another drive wich is full.

    If it's the SQL Server Express edition, there is a 4GB limit on the size of the database


    Regards Marten Rune Microsoft Certified IT Professional Database Administrator/Developer 2008
    Monday, August 16, 2010 12:30 PM
  • Look at: 'E. Modify a file' in: http://msdn.microsoft.com/en-us/library/aa275464(SQL.80).aspx
    Please note it uses the logical filename. As mentioned above, you can get these by typing sp_helpdb DBName. Look at name Column, this is the logical filename for your physical files.

    The log file increases automatically in steps that you determine when you create the database. Until disk runs out, or it hits it's maxvalue. Ie if growth is set to 1024MB, maxsize is 3.5GB and current filesize is 3GB. Then it cant grow since 3GB + 1024MG is bigger than maxsize of 3.5GB


    Regards Marten Rune Microsoft Certified IT Professional Database Administrator/Developer 2008

    Tuesday, August 17, 2010 7:24 AM
  • 1) BACKUP LOG file

    2) DBCC SHRINKFILE that log file

    PS. Preallocate enough space for log file, means do not rely on auto grow event, increase the size based on your testing...


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, August 17, 2010 7:43 AM

All replies

  • Look in the errorlog to see where the space runs out. Perhaps you have a limit on the database for growth. Check with:
    EXEC sp_helpdb dbname

    Also check if tempDB is on another drive wich is full.

    If it's the SQL Server Express edition, there is a 4GB limit on the size of the database


    Regards Marten Rune Microsoft Certified IT Professional Database Administrator/Developer 2008
    Monday, August 16, 2010 12:30 PM
  • Thanks,

    Is there any restricted/Unrestricted growth setting of Txn log file???

    Or from where /How by command I can set or increase the size of Txn log file???

    Tuesday, August 17, 2010 4:23 AM
  • Either in the SSMS, right click the Txn database, choose files and alter growth options.

    Or with T-Sql commands. Then use logical filename (sp_helpdb Txn), and ALTER Database. Look in BOL


    Regards Marten Rune Microsoft Certified IT Professional Database Administrator/Developer 2008
    Tuesday, August 17, 2010 5:44 AM
  • Look at: 'E. Modify a file' in: http://msdn.microsoft.com/en-us/library/aa275464(SQL.80).aspx
    Please note it uses the logical filename. As mentioned above, you can get these by typing sp_helpdb DBName. Look at name Column, this is the logical filename for your physical files.

    The log file increases automatically in steps that you determine when you create the database. Until disk runs out, or it hits it's maxvalue. Ie if growth is set to 1024MB, maxsize is 3.5GB and current filesize is 3GB. Then it cant grow since 3GB + 1024MG is bigger than maxsize of 3.5GB


    Regards Marten Rune Microsoft Certified IT Professional Database Administrator/Developer 2008

    Tuesday, August 17, 2010 7:24 AM
  • 1) BACKUP LOG file

    2) DBCC SHRINKFILE that log file

    PS. Preallocate enough space for log file, means do not rely on auto grow event, increase the size based on your testing...


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, August 17, 2010 7:43 AM
  • 1) BACKUP LOG file

    2) DBCC SHRINKFILE that log file

    PS. Preallocate enough space for log file, means do not rely on auto grow event, increase the size based on your testing...


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, August 17, 2010 7:44 AM
  • Hi Uri Dimant

    I think you might have read a bit too fast. The problem is not a huge logfile, in that case your suggestion is correct. The problem is the oposit. There is probably a fixed space set for datafile and/or logfile and it seems to have reached it's limit.

    Thats why I suggest, GUI in SSMS for altering the maxsize of the files, alternatively doing it by code in a ALTER DATABASE statement.

    I often read your comments and can clearly see that you are a highly skilled SQL Professional. Im NOT critisizing you in any way. The sole reason for me to write this is to help forsqlserver to  realize that your comment does not apply in this case.

    Best regards Marten


    Regards Marten Rune Microsoft Certified IT Professional Database Administrator/Developer 2008
    Tuesday, August 17, 2010 9:37 AM