3541 The request to assign new PERMANENT space is invalid
Problem:
CREATE DATABASE "PRDLMDMerchandisingPilotT" FROM "PRDLMDMerchandising" AS
PERMANENT = 21474836480,
SPOOL = 0,
TEMPORARY = 0,
ACCOUNT = '$L$DL&S&D&H',
NO FALLBACK,
NO BEFORE JOURNAL,
NO AFTER JOURNAL;
Executed as Single statement. Failed [3541 : HY000] The request to assign new PERMANENT space is invalid.
Elapsed time = 00:00:00.025
STATEMENT 1: Create Database failed.
Solution:
Either decrease the PERMANENT space in the new database or increase the PERMANENT space in the parent database. Another Option is Create database with 0 Perm space and then move space from DBC or Other database.
Example:
SELECT DatabaseName, SUM(MaxPerm)/1024/1024/1024 as MaxPerm_GB, SUM(CurrentPerm)/1024/1024/1024 CurrentPerm,
((SUM (CurrentPerm))/ NULLIFZERO (SUM(MaxPerm)) * 100)
(FORMAT 'zz9.99%', TITLE 'Percent // Used')
FROM DBC.DiskSpaceV
where databasename='PRDLMDMerchandising'
GROUP BY 1
ORDER BY 4 DESC;
Here I can see the parent database has 0 Perm space. So, I have done the following steps:
1. Create the database with 0 perm space
2. Move Perm Space from DBC or Other database, Here I have move Perm Space to this database from a database named FREE_SPACE
1. Create the database with 0 perm space
CREATE DATABASE "PRDLMDMerchandisingPilotT" FROM "PRDLMDMerchandising" AS
PERMANENT = 0,
SPOOL = 0,
TEMPORARY = 0,
ACCOUNT = '$L$DL&S&D&H',
NO FALLBACK,
NO BEFORE JOURNAL,
NO AFTER JOURfreNAL;
2. Moving Perm Space from a database name “FREE_SPACE” to the newly created database PRDLMDMerchandisingPilotT From Teradata Studio. (Note: We created “FREE_SPACE” database to reserve available perm space so we can move these space to other database when required, this is not a Teradata default database)