Space Problems

Question

Post reply

Space Problems

M. Gamito

SSC Veteran

Points: 219
More actions
December 11, 2014 at 5:58 am

#292882

Hello.
I'm having some problems with my database usage space.
I have a database with atogrowth off and limited size to 150GB.
When users try to import files, large databases or other massive transaction this message appears:
"Could not allocate a new page for database 'DB_PRODUCAO' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup."
What I don't understand is why this error happens. This database have free space as you can see on the numbers above:
FileSizeMB-----------UsedSpaceMB-----------UnusedSpaceMB-----------DBFileName
140025.31------------21102.19----------------118923.13------------------db_Producao
1082.44-------------------21.31------------------1061.13------------------db_Producao_log
There is almost 119GB of free space for use but the database don't use it.
Autogrowth can't set on because the server polices.
Can someone help me with this question?

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

dbafromthecold@gmail.com SSChampion Points: 10160 More actions · Answer 1

Is there only the one filegroup (PRIMARY) in the database?

M. Gamito SSC Veteran Points: 219 More actions · Answer 2

M. Gamito

SSC Veteran

Points: 219

December 11, 2014 at 6:59 am

#1764037

Yes.

Just "PRIMARY" and log.

M. Gamito SSC Veteran Points: 219 More actions · Answer 3

DBA From The Cold (12/11/2014)
Is there only the one filegroup (PRIMARY) in the database?

Yes.

Just "PRIMARY" and log.

dbafromthecold@gmail.com SSChampion Points: 10160 More actions · Answer 4

Hmm, I'm wondering if the free space is actually free space within existing objects. Does the database have data bulk inserted and then removed often?

dbafromthecold@gmail.com SSChampion Points: 10160 More actions · Answer 5

Have a look at this script:-

CREATE TABLE #TableSize

([Name]VARCHAR(255),

[rows]INT,

[Reserved]VARCHAR(255),

[Data]VARCHAR(255),

[Index Size]VARCHAR(255),

[Unused]VARCHAR(255));

CREATE TABLE #ConvertedSizes

([Name]VARCHAR(255),

[Rows]INT,

[Reserved (KB)]INT,

[Data (KB)]INT,

[Index Size (KB)]INT,

[Unused (KB)]INT);

EXEC sp_MSforeachtable @Command1="insert into #TableSize EXEC sp_spaceused '?'";

INSERT INTO #ConvertedSizes ([Name], [Rows], [Reserved (KB)], [Data (KB)], [Index Size (KB)], [Unused (KB)])

SELECT

[Name],

[Rows],

SUBSTRING([Reserved], 0, LEN([Reserved])-2),

SUBSTRING([Data], 0, LEN([Data])-2),

SUBSTRING([Index Size], 0, LEN([Index Size])-2),

SUBSTRING([Unused], 0, LEN([Unused])-2)

FROM

#TableSize;

SELECT

[Name],

[Rows],

[Data (KB)]/1024 AS [Data (MB)],

[Index Size (KB)],

[Unused (KB)]

FROM

#ConvertedSizes

ORDER BY

[Data (KB)] DESC;

DROP TABLE #TableSize;

DROP TABLE #ConvertedSizes;

GO

It's based on sp_spaceused to show you all the tables in the database and their free space. It will give you a more detailed view of the space consumed within your database.

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 6

M. Gamito (12/11/2014)
Hello.
I'm having some problems with my database usage space.
I have a database with atogrowth off and limited size to 150GB.
When users try to import files, large databases or other massive transaction this message appears:
"Could not allocate a new page for database 'DB_PRODUCAO' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup."
What I don't understand is why this error happens. This database have free space as you can see on the numbers above:
FileSizeMB-----------UsedSpaceMB-----------UnusedSpaceMB-----------DBFileName
140025.31------------21102.19----------------118923.13------------------db_Producao
1082.44-------------------21.31------------------1061.13------------------db_Producao_log
There is almost 119GB of free space for use but the database don't use it.
Autogrowth can't set on because the server polices.
Can someone help me with this question?

It's very likely that the import is exceeding the space available and then SQL Server does a rollback... which would cause the space to revert back to "Unused". You need to monitor the space used DURING the import to se what it has consumed in the process.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

M. Gamito SSC Veteran Points: 219 More actions · Answer 7

DBA From The Cold (12/11/2014)
Hmm, I'm wondering if the free space is actually free space within existing objects. Does the database have data bulk inserted and then removed often?

This database had some tables imported from another db and after the use those tables were dropped.

Could this action be the cause this problem?

M. Gamito SSC Veteran Points: 219 More actions · Answer 8

Jeff Moden (12/11/2014)
M. Gamito (12/11/2014)
Hello.
I'm having some problems with my database usage space.
I have a database with atogrowth off and limited size to 150GB.
When users try to import files, large databases or other massive transaction this message appears:
"Could not allocate a new page for database 'DB_PRODUCAO' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup."
What I don't understand is why this error happens. This database have free space as you can see on the numbers above:
FileSizeMB-----------UsedSpaceMB-----------UnusedSpaceMB-----------DBFileName
140025.31------------21102.19----------------118923.13------------------db_Producao
1082.44-------------------21.31------------------1061.13------------------db_Producao_log
There is almost 119GB of free space for use but the database don't use it.
Autogrowth can't set on because the server polices.
Can someone help me with this question?
It's very likely that the import is exceeding the space available and then SQL Server does a rollback... which would cause the space to revert back to "Unused". You need to monitor the space used DURING the import to se what it has consumed in the process.

Thanks for answer my question.

The file I'm trying to import have 10GB. I should have no problems with this one right?

M. Gamito SSC Veteran Points: 219 More actions · Answer 9

DBA From The Cold (12/11/2014)
Have a look at this script:-
CREATE TABLE #TableSize
([Name]VARCHAR(255),
[rows]INT,
[Reserved]VARCHAR(255),
[Data]VARCHAR(255),
[Index Size]VARCHAR(255),
[Unused]VARCHAR(255));
CREATE TABLE #ConvertedSizes
([Name]VARCHAR(255),
[Rows]INT,
[Reserved (KB)]INT,
[Data (KB)]INT,
[Index Size (KB)]INT,
[Unused (KB)]INT);
EXEC sp_MSforeachtable @Command1="insert into #TableSize EXEC sp_spaceused '?'";
INSERT INTO #ConvertedSizes ([Name], [Rows], [Reserved (KB)], [Data (KB)], [Index Size (KB)], [Unused (KB)])
SELECT
[Name],
[Rows],
SUBSTRING([Reserved], 0, LEN([Reserved])-2),
SUBSTRING([Data], 0, LEN([Data])-2),
SUBSTRING([Index Size], 0, LEN([Index Size])-2),
SUBSTRING([Unused], 0, LEN([Unused])-2)
FROM
#TableSize;
SELECT
[Name],
[Rows],
[Data (KB)]/1024 AS [Data (MB)],
[Index Size (KB)],
[Unused (KB)]
FROM
#ConvertedSizes
ORDER BY
[Data (KB)] DESC;
DROP TABLE #TableSize;
DROP TABLE #ConvertedSizes;
GO
It's based on sp_spaceused to show you all the tables in the database and their free space. It will give you a more detailed view of the space consumed within your database.

Thanks for the script.

I checked the numbers and averything apparently to be normal.

The used space match with the tables.

Another idea ?

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 10

M. Gamito (12/11/2014)
Jeff Moden (12/11/2014)
M. Gamito (12/11/2014)
Hello.
I'm having some problems with my database usage space.
I have a database with atogrowth off and limited size to 150GB.
When users try to import files, large databases or other massive transaction this message appears:
"Could not allocate a new page for database 'DB_PRODUCAO' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup."
What I don't understand is why this error happens. This database have free space as you can see on the numbers above:
FileSizeMB-----------UsedSpaceMB-----------UnusedSpaceMB-----------DBFileName
140025.31------------21102.19----------------118923.13------------------db_Producao
1082.44-------------------21.31------------------1061.13------------------db_Producao_log
There is almost 119GB of free space for use but the database don't use it.
Autogrowth can't set on because the server polices.
Can someone help me with this question?
It's very likely that the import is exceeding the space available and then SQL Server does a rollback... which would cause the space to revert back to "Unused". You need to monitor the space used DURING the import to se what it has consumed in the process.
Thanks for answer my question.
The file I'm trying to import have 10GB. I should have no problems with this one right?

Correct. There should be no problems here. I guess we need to see the code being used to help any further.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

M. Gamito SSC Veteran Points: 219 More actions · Answer 11

Jeff Moden (12/11/2014)
M. Gamito (12/11/2014)
Jeff Moden (12/11/2014)
M. Gamito (12/11/2014)
Hello.
I'm having some problems with my database usage space.
I have a database with atogrowth off and limited size to 150GB.
When users try to import files, large databases or other massive transaction this message appears:
"Could not allocate a new page for database 'DB_PRODUCAO' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup."
What I don't understand is why this error happens. This database have free space as you can see on the numbers above:
FileSizeMB-----------UsedSpaceMB-----------UnusedSpaceMB-----------DBFileName
140025.31------------21102.19----------------118923.13------------------db_Producao
1082.44-------------------21.31------------------1061.13------------------db_Producao_log
There is almost 119GB of free space for use but the database don't use it.
Autogrowth can't set on because the server polices.
Can someone help me with this question?
It's very likely that the import is exceeding the space available and then SQL Server does a rollback... which would cause the space to revert back to "Unused". You need to monitor the space used DURING the import to se what it has consumed in the process.
Thanks for answer my question.
The file I'm trying to import have 10GB. I should have no problems with this one right?
Correct. There should be no problems here. I guess we need to see the code being used to help any further.

It's a file, I'm not using a command. I'm using the Import Export Interface.

Here I have more information there can be usefull to resolve this problem.

SELECT name, size, max_size FROM sys.database_files

name---------------size--------------max_size

db_Producao--------14704024------19200000

db_Producao_log---384-------------268435456

What don't make sense to me is the fact of the db is using just 21GB and have more 130 avaible space, but when I check the the database file usage information I see this 147GB, and I locked autogrowth at 150GB. This can be the reason of the problem, but I don't know why this is happening.

I was trying to do a shrink database on this DB, but it was taking so long and I aborted it.

Any idea?

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 12

How many indexes are on the file. I would try dropping the indexes from the file and retry. Is this a new process? I would also, if you can, test how much space the import uses on a test server, set file to auto and see how big it gets and then see what's used after import has completed. You might have to batch the process so it commits more often. How long does it take before failure. Is the logfile growing at all. dbcc sqlperf(logspace) when you're running this.

Tom

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 13

I forgot to ask, what datatypes are in the table being inserted into.

Tom

M. Gamito SSC Veteran Points: 219 More actions · Answer 14

M. Gamito (12/12/2014)
Jeff Moden (12/11/2014)
M. Gamito (12/11/2014)
Jeff Moden (12/11/2014)
M. Gamito (12/11/2014)
Hello.
I'm having some problems with my database usage space.
I have a database with atogrowth off and limited size to 150GB.
When users try to import files, large databases or other massive transaction this message appears:
"Could not allocate a new page for database 'DB_PRODUCAO' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup."
What I don't understand is why this error happens. This database have free space as you can see on the numbers above:
FileSizeMB-----------UsedSpaceMB-----------UnusedSpaceMB-----------DBFileName
140025.31------------21102.19----------------118923.13------------------db_Producao
1082.44-------------------21.31------------------1061.13------------------db_Producao_log
There is almost 119GB of free space for use but the database don't use it.
Autogrowth can't set on because the server polices.
Can someone help me with this question?
It's very likely that the import is exceeding the space available and then SQL Server does a rollback... which would cause the space to revert back to "Unused". You need to monitor the space used DURING the import to se what it has consumed in the process.
Thanks for answer my question.
The file I'm trying to import have 10GB. I should have no problems with this one right?
Correct. There should be no problems here. I guess we need to see the code being used to help any further.
It's a file, I'm not using a command. I'm using the Import Export Interface.
Here I have more information there can be usefull to resolve this problem.
SELECT name, size, max_size FROM sys.database_files
name---------------size--------------max_size
db_Producao--------14704024------19200000
db_Producao_log---384-------------268435456
What don't make sense to me is the fact of the db is using just 21GB and have more 130 avaible space, but when I check the the database file usage information I see this 147GB, and I locked autogrowth at 150GB. This can be the reason of the problem, but I don't know why this is happening.
I was trying to do a shrink database on this DB, but it was taking so long and I aborted it.
Any idea?

It's a simple xls file. I import files like this everyday on other databases. I will do the test on another DB and will check the log file to see how is it growing.

But this is a strange problem, don't know why this is happening.