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'
then when I check the log maxsize I still get it limited:
exec sp_helpdb 'junofinance'
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
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)