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'
but that is not the problem, as after I few times trying these scripts I got:
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)
Best Answer
If you open BOL article sys.database_files (Transact-SQL)
you'll find that
268435456
= Log file will grow to a maximum size of2 TB
2 Tb
is the max size of SQL Server databaselog file
, if you need it more than2Tb
you add the secondlog file
.Here you can find Maximum Capacity Specifications for SQL Server that states that
2Tb
is the maximumlog file
size: