October 16, 2007 at 11:23 am
I have large ETL processes that can swell the transaction log to ~50GB. We have a seperate Log drive with 100GB of room (2 x normal capacity). Our data architect created the database and file groups, and for some unexplained reason the autogrowth on the log files is restricted to 2GB.
I've logged into the server remotely using the AD service account that runs the services on that box. The service account is a Local Server Admin and SQL Sysadmin on that box. Every time I reset the max limit for autogrowth, or try to set unlimited growth, the settings revert back to the 2GB limit.
This is a show stopper for our migration project from SQL 2000 to SQL 2005. I'm at a loss to explain it and could use some constructive suggestions.
Thanks,
Brandon Forest
Database Administrator
Data & Web Services Team
Sutter Connect Information Technology
October 16, 2007 at 2:19 pm
What build are you at? i.e.
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
Are you able to reproduce outside of management studio?
i.e.
USE [master]
GO
ALTER DATABASE [AdventureWorks] MODIFY FILE ( NAME = N'AdventureWorks_Data', FILEGROWTH = 10%)
GO
ALTER DATABASE [AdventureWorks] MODIFY FILE ( NAME = N'AdventureWorks_Log', FILEGROWTH = 10%)
GO
Tommy
Follow @sqlscribeOctober 16, 2007 at 4:16 pm
Brandon Forest (10/16/2007)
I have large ETL processes that can swell the transaction log to ~50GB. We have a seperate Log drive with 100GB of room (2 x normal capacity). Our data architect created the database and file groups, and for some unexplained reason the autogrowth on the log files is restricted to 2GB.I've logged into the server remotely using the AD service account that runs the services on that box. The service account is a Local Server Admin and SQL Sysadmin on that box. Every time I reset the max limit for autogrowth, or try to set unlimited growth, the settings revert back to the 2GB limit.
This is a show stopper for our migration project from SQL 2000 to SQL 2005. I'm at a loss to explain it and could use some constructive suggestions.
Thanks,
Brandon Forest
Database Administrator
Data & Web Services Team
Sutter Connect Information Technology
Brandon,
You are reading the number incorrectly it is actually 2TB not 2GB.
From BOL:
UNLIMITED
Specifies that the file grows until the disk is full. In SQL Server 2005, a log file specified with unlimited growth has a maximum size of 2 TB, and a data file has a maximum size of 16 TB.
Cheers,
* Noel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply