2

I have been trying to set the autogrowth of one of my databases to unlimited but despite whatever I do it remains the same.

First thing I have checked if something is holding any locks to my database:

select radhe='kill ' + cast(DTL.request_session_id as varchar(3))
      ,DTL.request_session_id
      ,DTL.resource_type
      ,DTL.request_type
      ,DTL.request_status
      ,DEST.TEXT
      , SP.spid
      , SP.blocked
      , SP.status
      , SP.loginame 
from sys.dm_tran_locks DTL
INNER JOIN sys.sysprocesses SP  
       ON DTL.request_session_id = SP.spid   
   --INNER JOIN sys.[dm_exec_requests] AS SDER ON SP.[spid] = [SDER].[session_id] 
   CROSS APPLY sys.dm_exec_sql_text(SP.sql_handle) AS DEST  
where 1=1
  and resource_type = 'database' 
  and resource_database_id = db_id('junofinance')
  and request_type = 'LOCK' 
  and request_status = 'GRANT'

but there was nothing holding any database locks. so I went ahead and ran the following:

use master
ALTER DATABASE [JUNOFinance] 
MODIFY FILE ( NAME = N'JUNOFinance_log', MAXSIZE = UNLIMITED)
GO

when I check about the log reuse I get the following:

select log_reuse_wait_desc, * from sys.databases
where name = 'junofinance'

enter image description here

but that is not the problem, as after I few times trying these scripts I got: enter image description here

then when I check the log maxsize I still get it limited:

exec sp_helpdb 'junofinance'

enter image description here

I have even tried some silly things like disabling the autogrowth and setting it to UNLIMITED

USE [master]
GO

GO
ALTER DATABASE [JUNOFinance] MODIFY FILE ( NAME = N'JUNOFinance_log', MAXSIZE = UNLIMITED, FILEGROWTH = 0)
GO

then it worked fine, go figure!

but then when I re-enabled it, it went back to the limitation:

USE [master]
GO

GO
ALTER DATABASE [JUNOFinance] MODIFY FILE ( NAME = N'JUNOFinance_log', FILEGROWTH = 10240KB )
GO

enter image description here

On this server in particular, I cannot upgrade it, so we are on:

Microsoft SQL Server 2016 (SP2-CU7) (KB4495256) - 13.0.5337.0 (X64)
May 16 2019 02:24:21 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Datacenter 6.3 (Build 9600: ) (Hypervisor)

  • Is it the only database presenting this behaviour on the instance? – Ronaldo Oct 18 at 11:48
  • I thought it was, as I only recently had to add a filegroup to this database and noticed this feature. However, now I realise that they're all behaving in the same way. – Marcello Miorelli Oct 18 at 11:52
3

If you open BOL article sys.database_files (Transact-SQL)

you'll find that 268435456 = Log file will grow to a maximum size of 2 TB

2 Tb is the max size of SQL Server database log file, if you need it more than 2Tb you add the second log file.

Here you can find Maximum Capacity Specifications for SQL Server that states that 2Tb is the maximum log file size:

enter image description here

  • The max capacity link, also says "The following table specifies the maximum sizes and numbers of various objects that were tested in the SQL Server Utility." & "Log files per database" = 1, if you need more then 2TB of log, is adding a second log file, really a good idea? - Maybe I should post this as a new question? – James Jenkins Oct 18 at 11:49
  • @James Jenkins This is the only way to have your db log size > 2Tb. You just cannot specify more than 2Tb for log file as you'll get 5150 error: The size of a single log file must not be greater than 2 TB. – sepupic Oct 18 at 11:56
  • 1
    New question SQL log file 2TB is not enough, now what? – James Jenkins Oct 18 at 12:05
  • But this is then a new feature as I have many databases showing the good old unlimited there – Marcello Miorelli Oct 18 at 12:25
  • >>>But this is then a new feature<<< I don't think so, maybe it was old SSMS that just interpreted this number as "unlimited"? I have only 2014 and 2016 here, both of them shows 268435456, I did not pay attention to this in old versions as I always used sys.database_files to check sizes and it shows max size as -1 for both log and data files – sepupic Oct 18 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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