June 25, 2015 at 9:59 am
We are running into the following error while changing a column data type from nvarchar (1200) to varchar(8000)
"Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object 'dbo.TBL1 '.'PK_CL_ID' in database 'Client01' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
The statement has been terminated."
Now tried to change the filegrowth of the log file to unlimited
ALTER DATABASE Client01
MODIFY FILE ( NAME = Client01_log, MAXSIZE = unlimited);
The query executes without error but I do not see the auto growth as unrestricted. It's still 2GB
June 25, 2015 at 10:03 am
It'll be 2TB, not 2GB and that's the max size that a transaction log file can reach, so it's the same as unlimited.
btw, the error was telling you the data file is full, nothing to do with the log file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 25, 2015 at 10:21 am
Guras (6/25/2015)
We are running into the following error while changing a column data type from nvarchar (1200) to varchar(8000)"Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object 'dbo.TBL1 '.'PK_CL_ID' in database 'Client01' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
The statement has been terminated."
Now tried to change the filegrowth of the log file to unlimited
ALTER DATABASE Client01
MODIFY FILE ( NAME = Client01_log, MAXSIZE = unlimited);
The query executes without error but I do not see the auto growth as unrestricted. It's still 2GB
Post the output of the following query
select name, physical_name, size / 128, max_size /128,
case is_percent_growth
when 0 then growth / 128
when 1 then growth
end
from sys.master_files
where database_id = db_id('Client01')
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 25, 2015 at 10:26 am
client01 5720 0 500
client01_log 2097152 2724 100
June 25, 2015 at 10:27 am
GilaMonster (6/25/2015)
It'll be 2TB, not 2GB and that's the max size that a transaction log file can reach, so it's the same as unlimited.btw, the error was telling you the data file is full, nothing to do with the log file.
Ah! But the data file is set to unlimited growth so why is it saying full?
June 25, 2015 at 10:35 am
Guras (6/25/2015)
But the data file is set to unlimited growth so why is it saying full?
Possibly because it couldn't grow fast enough, possibly because there isn't space on the disk, possibly because the growth took too long, etc.
You generally should grow files manually and leave autogrow to catch times when you didn't notice the file was almost out of space.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 25, 2015 at 3:39 pm
possibly because there isn't space on the disk
+1. That is what I would check. Make sure a disk has not filled up.
Joie Andrew
"Since 1982"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply