The transaction log for database 'xxx' is full when creating stored procedures

Question

I have a database that is used only to contain extended properties that are used to automatically generate documentation, so it is a mirror of another database, but all its tables are empty.  I have been using the database involved for months.  It is set to SIMPLE recovery mode, and there is 930 GB of free space on the drive (SAN) that the data and logs are stored on.  The database is unused by anyone but me and I am SA.  I am running SQL Server 2008 R2 on Windows Server 2008.

I have started getting the error below when I create or alter some, but not all, stored procedures on the doc database and the real database does not have any problems with exactly the same stored procedures.  The error has not yet occurred often enough to see if there is a pattern, but the last stored procedure that had the issue was a straight-up single table select.

Msg 9002, Level 17, State 4, Procedure usp_s_VGC_ORDER_C, Line 39

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

The log_reuse_wait_des column from sys.databases is NOTHING for the database.

I used the DBCC command DBCC CHECKDB(giftcard_doc) to fix the issue twice, but it recurred in less than an hour with a different stored procedure.

Any suggestions would be greatly appreciated.

Answer 1

Hi,

Please can you run the following to collect some of your settings:

USE [xxx]
SELECT
 CONVERT(DECIMAL(15,3),(FILEPROPERTY(sf.[name],'SpaceUsed')*CONVERT(FLOAT,8)/1024)) [SpaceUsed_MiB]
,CONVERT(DECIMAL(15,3),((sf.[size]-FILEPROPERTY(sf.[name],'SpaceUsed'))*CONVERT(FLOAT,8)/1024)) [AvailableSpace_MiB]
,LTRIM(CASE mf.[is_percent_growth] WHEN 1 THEN STR(mf.[growth]) +' %' ELSE STR(mf.[growth]*CONVERT(FLOAT,8)/1024)+' MiB' END) [AutoGrowth]
,CASE WHEN mf.[max_size]=-1 THEN -1 ELSE mf.[max_size] * CONVERT(FLOAT,8) END [MaxSize]
,mf.[type_desc] [FileType]
,sf.[name] [LogicalName]
,mf.[file_id] [FileID]
,RIGHT(mf.[physical_name],CHARINDEX('\',REVERSE (mf.[physical_name]))-1) [FileName]
,sf.[filename] [Path]
FROM sys.master_files mf
INNER JOIN sys.sysfiles sf ON mf.[file_id] = sf.[fileid] AND mf.[database_id] = DB_ID()
LEFT JOIN sys.sysfilegroups fg ON sf.[groupid] = fg.[groupid]
ORDER BY mf.[file_id]
GO
SELECT [log_reuse_wait_desc] FROM sys.databases WHERE [database_id] = DB_ID()--Double check :P
GO
EXEC xp_fixeddrives--Double check :P

---

Jon

Answer 2

do you have a cap on the size your transaction log can grow to?

---

Thanks,

Andrew Bainbridge
SQL Server DBA

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Answer 3

Thanks for the quick response.  Below are the results of the query you provided.

SpaceUsed_MiB AvailableSpace_MiB AutoGrowth MaxSize FileType LogicalName FileID FileName Path

--------------------------------------- --------------------------------------- -------------- ---------------------- ------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

26.625 0.938 5 % -1 ROWS simon_prod_Data 1 simon_new.MDF E:\MSSQL2008\MSSQL10_50.SIMON_ODS\MSSQL\DATA\simon_new.MDF

0.359 0.891 5 % -1 LOG simon_prod_Log 2 simon_new_4.LDF E:\MSSQL2008\MSSQL10_50.SIMON_ODS\MSSQL\DATA\simon_new_4.LDF

0.375 0.625 10 % -1 ROWS ftrow_CAT_MALL_FAM 3 simon_new_1.ndf E:\MSSQL2008\MSSQL10_50.SIMON_ODS\MSSQL\DATA\simon_new_1.ndf

1.688 0.000 10 % -1 ROWS ftrow_CAT_TENANT_FAM 4 simon_new_2.ndf E:\MSSQL2008\MSSQL10_50.SIMON_ODS\MSSQL\DATA\simon_new_2.ndf

0.125 0.875 10 % -1 ROWS ftrow_CAT_FAM_LOCATION 5 simon_new_3.ndf E:\MSSQL2008\MSSQL10_50.SIMON_ODS\MSSQL\DATA\simon_new_3.ndf

log_reuse_wait_desc

------------------------------------------------------------

NOTHING

drive MB free

----- -----------

C 7670

D 22333

E 593961

Answer 4

As your transaction log is around 1 Megabyte could you manually grow this to at least 8192KB (8 Megabytes) and see if you continue to get an issue.

Example syntax:

ALTER DATABASE [simon_prod] MODIFY FILE (NAME = N'simon_prod_Log', SIZE = 8192KB)

---

Jon