January 22, 2016 at 4:43 am
Here's a weird one that I cannot figure out. I have a bit of code that monitors my database growth. It was working fine up until yesterday when I set one of my databases OFFLINE. Then it failed every time I ran it because of this error:
[SQLSTATE 01003] (Message 8153) Cannot insert the value NULL into column 'DBFileName', table 'tempdb.dbo.#dbsize_____________________________________________________________________________________________________________00000001B7DC'; column does not allow nulls. INSERT fails. [SQLSTATE 23000] (Error 515) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed
Here's the code that that I was running and I bolded the section where it was failing.
------------------------------Data file size----------------------------
IF (SELECT OBJECT_ID('tempdb..#dbsize')) IS NOT NULL
DROP TABLE #dbsize;
CREATE TABLE #dbsize
(Dbname SYSNAME,driveletter char(1), DBFileName SYSNAME, dbstatus VARCHAR(50),
Recovery_Model VARCHAR(40) DEFAULT ('NA'), file_Size_MB DECIMAL(30,2)DEFAULT (0),
Space_Used_MB DECIMAL(30,2)DEFAULT (0),Free_Space_MB DECIMAL(30,2) DEFAULT (0));
GO
INSERT INTO #dbsize(Dbname, DBFileName, driveletter,dbstatus,Recovery_Model,
file_Size_MB,Space_Used_MB,Free_Space_MB)
EXEC sp_msforeachdb
'use [?];
SELECT DB_NAME() AS DbName, name AS DBFileName, SUBSTRING(physical_name,1,1),
CONVERT(VARCHAR(20),DatabasePropertyEx(''?'',''Status'')) ,
CONVERT(VARCHAR(20),DatabasePropertyEx(''?'',''Recovery'')),
sum(size)/128.0 AS File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB
FROM sys.database_files WHERE type=0 group by type, name, physical_name';
GO
-------------------log size--------------------------------------
IF (SELECT OBJECT_ID('tempdb..#logsize') ) IS NOT NULL
DROP TABLE #logsize;
CREATE TABLE #logsize
(Dbname SYSNAME, DriveLetter char(1), Log_File_Size_MB DECIMAL(38,2)DEFAULT (0),
log_Space_Used_MB DECIMAL(30,2)DEFAULT (0),log_Free_Space_MB DECIMAL(30,2)DEFAULT (0));
GO
INSERT INTO #logsize(Dbname, DriveLetter,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB)
EXEC sp_msforeachdb
'use [?];
SELECT DB_NAME() AS DbName, SUBSTRING(physical_name,1,1),
sum(size)/128.0 AS Log_File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB
FROM sys.database_files WHERE type=1 group by type, physical_name';
GO
--------------------------------database free size
IF (SELECT OBJECT_ID('tempdb..#dbfreesize') ) IS NOT NULL
DROP TABLE #dbfreesize;
CREATE TABLE #dbfreesize
(name SYSNAME, database_size VARCHAR(50), Freespace VARCHAR(50)DEFAULT (0.00));
INSERT INTO #dbfreesize(name,database_size,Freespace)
EXEC sp_msforeachdb
'use [?];SELECT database_name = db_name()
,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize)
+ convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'')
,''unallocated space'' = ltrim(str((
CASE
WHEN dbsize >= reservedpages
THEN (convert(DECIMAL(15, 2), dbsize)
- convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576
ELSE 0
END
), 15, 2) + '' MB'')
FROM (
SELECT dbsize = sum(convert(BIGINT, CASE
WHEN type = 0
THEN size
ELSE 0
END))
,logsize = sum(convert(BIGINT, CASE
WHEN type <> 0
THEN size
ELSE 0
END))
FROM sys.database_files
) AS files
,(
SELECT reservedpages = sum(a.total_pages)
,usedpages = sum(a.used_pages)
,pages = sum(CASE
WHEN it.internal_type IN (
202
,204
,211
,212
,213
,214
,215
,216
)
THEN 0
WHEN a.type <> 1
THEN a.used_pages
WHEN p.index_id < 2
THEN a.data_pages
ELSE 0
END)
FROM sys.partitions p
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
LEFT JOIN sys.internal_TABLEs it
ON p.object_id = it.object_id
) AS partitions';
-----------------------------------
IF (SELECT OBJECT_ID('tempdb..#alldbstate') ) IS NOT NULL
DROP TABLE #alldbstate;
CREATE TABLE #alldbstate
(dbname SYSNAME, DBstatus VARCHAR(55), R_model VARCHAR(30));
INSERT INTO #alldbstate (dbname,DBstatus,R_model)
SELECT name,CONVERT(VARCHAR(20),DATABASEPROPERTYEX(name,'status')),
recovery_model_desc
FROM sys.databases;
--SELECT * FROM #dbsize
INSERT INTO #dbsize(Dbname,dbstatus,Recovery_Model)
SELECT dbname,dbstatus,R_model FROM #alldbstate WHERE DBstatus <> 'online';
SELECT dbname,dbstatus,R_model FROM #alldbstate WHERE DBstatus <> 'online';
INSERT INTO #logsize(Dbname)
SELECT dbname FROM #alldbstate WHERE DBstatus <> 'online';
INSERT INTO #dbfreesize(name)
SELECT dbname FROM #alldbstate WHERE DBstatus <> 'online';
INSERT INTO CreditDBA_Admin.dbo.DBSizeMonitor (DBName, DBFileName,
DriveLetter, DBStatus, Recovery_Model, DBSize, File_Size_MB,
Space_Used_MB, Free_Space_MB, Log_File_Size_MB, Log_Space_Used_MB,
Log_Free_Space_MB, DB_FreeSpace, LogDateTime)
SELECT d.Dbname,d.DBFileName, d.driveletter, d.dbstatus,d.Recovery_Model,
(file_size_mb + log_file_size_mb) as DBsize,
d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB,
l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,
fs.Freespace as DB_Freespace, GETDATE() AS LogDateTime
FROM #dbsize d
INNER JOIN #logsize l
ON d.Dbname=l.Dbname
INNER JOIN #dbfreesize fs
ON d.Dbname=fs.name
order by d.driveletter DESC, d.Free_Space_MB DESC, d.Dbname;
Now that code has not ever changed. It was running fine until 10:30 a.m. yesterday, at which point it failed every time it ran. Setting a database OFFLINE should not have caused any issues, because the data was still pulling without NULL values.
I ran SELECT dbname,dbstatus,R_model FROM #alldbstate WHERE DBstatus <> 'online'; several times to be sure that it wasn't pulling NULL values. I looked over the temp table #dbsize to make sure. I even added the zeroes for the DEFAULT columns and it was still failing. As a test, I put the OFFLINE database back ONLINE and the code worked like a treat because it wasn't pulling any non-ONLINE database info, so skipped that section of code entirely.
Took the database back OFFLINE and failure again. So I finally narrowed down the issue to column "driveletter", which is not at all what the error was complaining about. I changed this section of code:
CREATE TABLE #alldbstate
(dbname SYSNAME, DBstatus VARCHAR(55), R_model VARCHAR(30));
INSERT INTO #alldbstate (dbname,DBstatus,R_model)
SELECT name,CONVERT(VARCHAR(20),DATABASEPROPERTYEX(name,'status')),
recovery_model_desc
FROM sys.databases;
--SELECT * FROM #dbsize
INSERT INTO #dbsize(Dbname,dbstatus,Recovery_Model)
SELECT dbname,dbstatus,R_model FROM #alldbstate WHERE DBstatus <> 'online';
To THIS:
CREATE TABLE #alldbstate
(dbname SYSNAME, driveletter CHAR(1), DBstatus VARCHAR(55), R_model VARCHAR(30));
INSERT INTO #alldbstate (dbname,driveletter,DBstatus,R_model)
SELECT sd.name,SUBSTRING(df.physical_name,1,1), CONVERT(VARCHAR(20),DATABASEPROPERTYEX(sd.name,'status')),sd.recovery_model_desc
FROM sys.databases sd
INNER JOIN sys.database_files df
ON sd.name = df.name;
--SELECT * FROM #dbsize
INSERT INTO #dbsize(Dbname,driveletter,DBFileName,Recovery_Model)
SELECT dbname,driveletter,dbstatus,R_model FROM #alldbstate WHERE DBstatus <> 'online';
And suddenly the code is working again. The thing is, I don't know why it's working because I can't figure out why it failed. Driveletter is a nullable column because I never told it to be NOT NULL.
Can someone shed some light on this particular bit of strange code interaction?
January 22, 2016 at 5:20 am
Quick suggestion, change the #dbsize create script
😎
CREATE TABLE #dbsize
(
Dbname SYSNAME NOT NULL
,driveletter char(1) NULL
,DBFileName SYSNAME NOT NULL
,dbstatus VARCHAR(50) NOT NULL
,Recovery_Model VARCHAR(40) NOT NULL DEFAULT ('NA')
,file_Size_MB DECIMAL(30,2) NOT NULL DEFAULT (0)
,Space_Used_MB DECIMAL(30,2) NOT NULL DEFAULT (0)
,Free_Space_MB DECIMAL(30,2) NOT NULL DEFAULT (0)
);
GO
January 22, 2016 at 5:56 am
Eirikur Eiriksson (1/22/2016)
Quick suggestion, change the #dbsize create script😎
CREATE TABLE #dbsize(
Dbname SYSNAME NOT NULL
,driveletter char(1) NULL
,DBFileName SYSNAME NOT NULL
,dbstatus VARCHAR(50) NOT NULL
,Recovery_Model VARCHAR(40) NOT NULL DEFAULT ('NA')
,file_Size_MB DECIMAL(30,2) NOT NULL DEFAULT (0)
,Space_Used_MB DECIMAL(30,2) NOT NULL DEFAULT (0)
,Free_Space_MB DECIMAL(30,2) NOT NULL DEFAULT (0)
);
GO
I would rather not make any more script changes until I understand exactly what caused the problem in the first place.
EDIT: It's frustrating knowing that I've fixed the problem without understanding exactly what the problem is.
January 22, 2016 at 7:24 am
Brandie Tarvin (1/22/2016)
Eirikur Eiriksson (1/22/2016)
Quick suggestion, change the #dbsize create script😎
CREATE TABLE #dbsize(
Dbname SYSNAME NOT NULL
,driveletter char(1) NULL
,DBFileName SYSNAME NOT NULL
,dbstatus VARCHAR(50) NOT NULL
,Recovery_Model VARCHAR(40) NOT NULL DEFAULT ('NA')
,file_Size_MB DECIMAL(30,2) NOT NULL DEFAULT (0)
,Space_Used_MB DECIMAL(30,2) NOT NULL DEFAULT (0)
,Free_Space_MB DECIMAL(30,2) NOT NULL DEFAULT (0)
);
GO
I would rather not make any more script changes until I understand exactly what caused the problem in the first place.
EDIT: It's frustrating knowing that I've fixed the problem without understanding exactly what the problem is.
You should always specify the null-ability of a column, although the default is null the server uses hints such as source column not being null or an isnull statement to overwrite those. Suggest you ask Jeff Moden, he's done some nifty things with this.
😎
January 22, 2016 at 8:00 am
You're using sysname as datatype for your columns. Sysname is by default NOT NULL.
You're effectively not inserting any null values explicitly, but you were missing the column in your insert statement. Once you included it, the error was gone. This only happened when you had an offline database because of the where clauses of those statements.
I hope that I made myself clear.
January 22, 2016 at 8:27 am
Luis Cazares (1/22/2016)
You're using sysname as datatype for your columns. Sysname is by default NOT NULL.You're effectively not inserting any null values explicitly, but you were missing the column in your insert statement. Once you included it, the error was gone. This only happened when you had an offline database because of the where clauses of those statements.
I hope that I made myself clear.
Luis is correct here. Another way to fix it is to change DBFileName from sysname to NVARCHAR(128), the base type for sysname, and allow nulls there.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 22, 2016 at 8:46 am
Luis and Jack, you are both right in the terms of suppressing the problem, preventing it is in my mind a better approach.
😎
January 22, 2016 at 9:11 am
Eirikur Eiriksson (1/22/2016)
Luis and Jack, you are both right in the terms of suppressing the problem, preventing it is in my mind a better approach.😎
Fixing the whole code would be an even better approach, which would include the correct definition of the table and the correct insertion of values. 😎
January 22, 2016 at 10:00 am
Eirikur Eiriksson (1/22/2016)
Luis and Jack, you are both right in the terms of suppressing the problem, preventing it is in my mind a better approach.😎
As Luis and Jack explained, the issue is that the DBFileName column is defined as a sysname, which by default is a NOT NULL column. The insert statement is not inserting into this column, so a NULL is being attempted to be inserted into this column. The insert fails since the column can't be NULL.
To prevent this issue:
1. Change the definition of the table to either:
CREATE TABLE #dbsize
(
Dbname sysname,
driveletter CHAR(1),
DBFileName NVARCHAR(128),
dbstatus VARCHAR(50),
Recovery_Model VARCHAR(40) DEFAULT ('NA'),
file_Size_MB DECIMAL(30, 2) DEFAULT (0),
Space_Used_MB DECIMAL(30, 2) DEFAULT (0),
Free_Space_MB DECIMAL(30, 2) DEFAULT (0)
);
or
CREATE TABLE #dbsize
(
Dbname sysname,
driveletter CHAR(1),
DBFileName sysname NULL,
dbstatus VARCHAR(50),
Recovery_Model VARCHAR(40) DEFAULT ('NA'),
file_Size_MB DECIMAL(30, 2) DEFAULT (0),
Space_Used_MB DECIMAL(30, 2) DEFAULT (0),
Free_Space_MB DECIMAL(30, 2) DEFAULT (0)
);
or
2. Change the below code:
INSERT INTO #dbsize(Dbname,dbstatus,Recovery_Model)
SELECT dbname,dbstatus,R_model
FROM #alldbstate
WHERE DBstatus <> 'online';
to insert into the DBFileName column:
INSERT INTO #dbsize(Dbname,dbstatus,Recovery_Model, DBFileName)
SELECT dbname,dbstatus,R_model , ''
FROM #alldbstate
WHERE DBstatus <> 'online';
My preference is to change the data type of the column to sysname NULL.
Note that this code will not work as expected if you are on a case-sensitive collation.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 22, 2016 at 10:37 am
I understand what you're saying about the SYSNAME, and I agree I need to fix the code.
But if that's the case, why did fixing only the driveletter column, which is definitively NOT SYSNAME, fix the code? It worked as soon as I added driveletter to the other temp table and then inserted the record with that additional column.
That's the part that's driving me nuts. I fixed a different other column than indicated in the error message and the error goes away. Poof. I still don't get why doing that would circumvent the "sysname datatypes aren't nullable" rule.
January 22, 2016 at 10:39 am
Luis Cazares (1/22/2016)
You're using sysname as datatype for your columns. Sysname is by default NOT NULL.You're effectively not inserting any null values explicitly, but you were missing the column in your insert statement. Once you included it, the error was gone. This only happened when you had an offline database because of the where clauses of those statements.
I hope that I made myself clear.
You made yourself clear, but allow me to point out that the DBName was absolutely included in the insert and wasn't a null value (I ran the SELECT by itself to make sure). It was the addition of a CHAR(1) column to that temp table that fixed the problem.
January 22, 2016 at 10:41 am
Brandie Tarvin (1/22/2016)
Luis Cazares (1/22/2016)
You're using sysname as datatype for your columns. Sysname is by default NOT NULL.You're effectively not inserting any null values explicitly, but you were missing the column in your insert statement. Once you included it, the error was gone. This only happened when you had an offline database because of the where clauses of those statements.
I hope that I made myself clear.
You made yourself clear, but allow me to point out that the DBName was absolutely included in the insert and wasn't a null value (I ran the SELECT by itself to make sure). It was the addition of a CHAR(1) column to that temp table that fixed the problem.
DBName was included but DBFileName wasn't.
EDIT to make the differences more evident.
INSERT INTO #dbsize(Dbname, dbstatus, Recovery_Model) --Original
INSERT INTO #dbsize(Dbname,driveletter, DBFileName,Recovery_Model) --Modified
January 22, 2016 at 10:52 am
Luis Cazares (1/22/2016)
Brandie Tarvin (1/22/2016)
Luis Cazares (1/22/2016)
You're using sysname as datatype for your columns. Sysname is by default NOT NULL.You're effectively not inserting any null values explicitly, but you were missing the column in your insert statement. Once you included it, the error was gone. This only happened when you had an offline database because of the where clauses of those statements.
I hope that I made myself clear.
You made yourself clear, but allow me to point out that the DBName was absolutely included in the insert and wasn't a null value (I ran the SELECT by itself to make sure). It was the addition of a CHAR(1) column to that temp table that fixed the problem.
DBName was included but DBFileName wasn't.
EDIT to make the differences more evident.
INSERT INTO #dbsize(Dbname, dbstatus, Recovery_Model) --Original
INSERT INTO #dbsize(Dbname,driveletter, DBFileName,Recovery_Model) --Modified
Ummm. That is actually a typo. Should be status, not file name. Thank you for pointing that out.
January 22, 2016 at 11:01 am
Okay. This is driving me batty.
New code (with datatypes fixed) works fine ....
------------------------------Data file size----------------------------
IF (SELECT OBJECT_ID('tempdb..#dbsize')) IS NOT NULL
DROP TABLE #dbsize;
CREATE TABLE #dbsize
(Dbname SYSNAME NOT NULL,driveletter CHAR(1) NULL, DBFileName SYSNAME NOT NULL, dbstatus VARCHAR(50),Recovery_Model VARCHAR(40) NULL DEFAULT ('NA'),
file_Size_MB DECIMAL(30,2) NULL DEFAULT (0), Space_Used_MB DECIMAL(30,2) NULL DEFAULT (0),Free_Space_MB DECIMAL(30,2) NULL DEFAULT (0));
GO
INSERT INTO #dbsize(Dbname, DBFileName, driveletter,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB)
EXEC sp_msforeachdb
'use [?];
SELECT DB_NAME() AS DbName, name AS DBFileName, SUBSTRING(physical_name,1,1),
CONVERT(VARCHAR(20),DatabasePropertyEx(''?'',''Status'')) ,
CONVERT(VARCHAR(20),DatabasePropertyEx(''?'',''Recovery'')),
sum(size)/128.0 AS File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB
FROM sys.database_files WHERE type=0 group by type, name, physical_name';
GO
-------------------log size--------------------------------------
IF (SELECT OBJECT_ID('tempdb..#logsize') ) IS NOT NULL
DROP TABLE #logsize;
CREATE TABLE #logsize
(Dbname SYSNAME NOT NULL, DriveLetter CHAR(1) NULL, Log_File_Size_MB DECIMAL(38,2) NULL DEFAULT (0),
log_Space_Used_MB DECIMAL(30,2) NULL DEFAULT (0), log_Free_Space_MB DECIMAL(30,2) NULL DEFAULT (0));
GO
INSERT INTO #logsize(Dbname, DriveLetter,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB)
EXEC sp_msforeachdb
'use [?];
SELECT DB_NAME() AS DbName, SUBSTRING(physical_name,1,1),
sum(size)/128.0 AS Log_File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB
FROM sys.database_files WHERE type=1 group by type, physical_name';
GO
--------------------------------database free size
IF (SELECT OBJECT_ID('tempdb..#dbfreesize') ) IS NOT NULL
DROP TABLE #dbfreesize;
CREATE TABLE #dbfreesize
(name SYSNAME NOT NULL, database_size VARCHAR(50) NOT NULL, Freespace VARCHAR(50) NOT NULL DEFAULT (0.00));
INSERT INTO #dbfreesize(name,database_size,Freespace)
EXEC sp_msforeachdb
'use [?];SELECT database_name = db_name()
,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'')
,''unallocated space'' = ltrim(str((
CASE
WHEN dbsize >= reservedpages
THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576
ELSE 0
END
), 15, 2) + '' MB'')
FROM (
SELECT dbsize = sum(convert(BIGINT, CASE
WHEN type = 0
THEN size
ELSE 0
END))
,logsize = sum(convert(BIGINT, CASE
WHEN type <> 0
THEN size
ELSE 0
END))
FROM sys.database_files
) AS files
,(
SELECT reservedpages = sum(a.total_pages)
,usedpages = sum(a.used_pages)
,pages = sum(CASE
WHEN it.internal_type IN (
202
,204
,211
,212
,213
,214
,215
,216
)
THEN 0
WHEN a.type <> 1
THEN a.used_pages
WHEN p.index_id < 2
THEN a.data_pages
ELSE 0
END)
FROM sys.partitions p
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
LEFT JOIN sys.internal_TABLEs it
ON p.object_id = it.object_id
) AS partitions';
-----------------------------------
IF (SELECT OBJECT_ID('tempdb..#alldbstate') ) IS NOT NULL
DROP TABLE #alldbstate;
CREATE TABLE #alldbstate
(dbname SYSNAME NOT NULL, driveletter CHAR(1) NULL, DBstatus VARCHAR(55) NOT NULL, R_model VARCHAR(30) NOT NULL);
--SELECT * FROM sys.master_files
INSERT INTO #alldbstate (dbname,driveletter,DBstatus,R_model)
SELECT sd.name,SUBSTRING(df.physical_name,1,1), CONVERT(VARCHAR(20),DATABASEPROPERTYEX(sd.name,'status')),sd.recovery_model_desc
FROM sys.databases sd
INNER JOIN sys.database_files df
ON sd.name = df.name;
--SELECT * FROM #dbsize
INSERT INTO #dbsize(Dbname,driveletter,dbstatus,Recovery_Model)
SELECT dbname,driveletter,dbstatus,R_model FROM #alldbstate WHERE DBstatus NOT IN ('ONLINE','RESTORING');
INSERT INTO #logsize(Dbname)
SELECT dbname FROM #alldbstate WHERE DBstatus NOT IN ('ONLINE','RESTORING');
INSERT INTO #dbfreesize(name)
SELECT dbname FROM #alldbstate WHERE DBstatus NOT IN ('ONLINE','RESTORING');
INSERT INTO CreditDBA_Admin.dbo.DBSizeMonitor (DBName, DBFileName, DriveLetter, DBStatus, Recovery_Model, DBSize, File_Size_MB,
Space_Used_MB, Free_Space_MB, Log_File_Size_MB, Log_Space_Used_MB, Log_Free_Space_MB, DB_FreeSpace, LogDateTime)
SELECT d.Dbname,d.DBFileName, d.driveletter, d.dbstatus,d.Recovery_Model,
(file_size_mb + log_file_size_mb) as DBsize,
d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB,
l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,fs.Freespace as DB_Freespace, GETDATE() AS LogDateTime
FROM #dbsize d
INNER JOIN #logsize l
ON d.Dbname=l.Dbname
INNER JOIN #dbfreesize fs
ON d.Dbname=fs.name
ORDER BY d.driveletter DESC, d.Free_Space_MB DESC, d.Dbname;
...until I remove that damn driveletter column.
------------------------------Data file size----------------------------
IF (SELECT OBJECT_ID('tempdb..#dbsize')) IS NOT NULL
DROP TABLE #dbsize;
CREATE TABLE #dbsize
(Dbname SYSNAME NOT NULL,driveletter CHAR(1) NULL, DBFileName SYSNAME NOT NULL, dbstatus VARCHAR(50),Recovery_Model VARCHAR(40) NULL DEFAULT ('NA'),
file_Size_MB DECIMAL(30,2) NULL DEFAULT (0), Space_Used_MB DECIMAL(30,2) NULL DEFAULT (0),Free_Space_MB DECIMAL(30,2) NULL DEFAULT (0));
GO
INSERT INTO #dbsize(Dbname, DBFileName, driveletter,dbstatus,Recovery_Model,file_Size_MB,Space_Used_MB,Free_Space_MB)
EXEC sp_msforeachdb
'use [?];
SELECT DB_NAME() AS DbName, name AS DBFileName, SUBSTRING(physical_name,1,1),
CONVERT(VARCHAR(20),DatabasePropertyEx(''?'',''Status'')) ,
CONVERT(VARCHAR(20),DatabasePropertyEx(''?'',''Recovery'')),
sum(size)/128.0 AS File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS Free_Space_MB
FROM sys.database_files WHERE type=0 group by type, name, physical_name';
GO
-------------------log size--------------------------------------
IF (SELECT OBJECT_ID('tempdb..#logsize') ) IS NOT NULL
DROP TABLE #logsize;
CREATE TABLE #logsize
(Dbname SYSNAME NOT NULL, DriveLetter CHAR(1) NULL, Log_File_Size_MB DECIMAL(38,2) NULL DEFAULT (0),
log_Space_Used_MB DECIMAL(30,2) NULL DEFAULT (0), log_Free_Space_MB DECIMAL(30,2) NULL DEFAULT (0));
GO
INSERT INTO #logsize(Dbname, DriveLetter,Log_File_Size_MB,log_Space_Used_MB,log_Free_Space_MB)
EXEC sp_msforeachdb
'use [?];
SELECT DB_NAME() AS DbName, SUBSTRING(physical_name,1,1),
sum(size)/128.0 AS Log_File_Size_MB,
sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT))/128.0 as log_Space_Used_MB,
SUM( size)/128.0 - sum(CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT))/128.0 AS log_Free_Space_MB
FROM sys.database_files WHERE type=1 group by type, physical_name';
GO
--------------------------------database free size
IF (SELECT OBJECT_ID('tempdb..#dbfreesize') ) IS NOT NULL
DROP TABLE #dbfreesize;
CREATE TABLE #dbfreesize
(name SYSNAME NOT NULL, database_size VARCHAR(50) NOT NULL, Freespace VARCHAR(50) NOT NULL DEFAULT (0.00));
INSERT INTO #dbfreesize(name,database_size,Freespace)
EXEC sp_msforeachdb
'use [?];SELECT database_name = db_name()
,database_size = ltrim(str((convert(DECIMAL(15, 2), dbsize) + convert(DECIMAL(15, 2), logsize)) * 8192 / 1048576, 15, 2) + ''MB'')
,''unallocated space'' = ltrim(str((
CASE
WHEN dbsize >= reservedpages
THEN (convert(DECIMAL(15, 2), dbsize) - convert(DECIMAL(15, 2), reservedpages)) * 8192 / 1048576
ELSE 0
END
), 15, 2) + '' MB'')
FROM (
SELECT dbsize = sum(convert(BIGINT, CASE
WHEN type = 0
THEN size
ELSE 0
END))
,logsize = sum(convert(BIGINT, CASE
WHEN type <> 0
THEN size
ELSE 0
END))
FROM sys.database_files
) AS files
,(
SELECT reservedpages = sum(a.total_pages)
,usedpages = sum(a.used_pages)
,pages = sum(CASE
WHEN it.internal_type IN (
202
,204
,211
,212
,213
,214
,215
,216
)
THEN 0
WHEN a.type <> 1
THEN a.used_pages
WHEN p.index_id < 2
THEN a.data_pages
ELSE 0
END)
FROM sys.partitions p
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
LEFT JOIN sys.internal_TABLEs it
ON p.object_id = it.object_id
) AS partitions';
-----------------------------------
IF (SELECT OBJECT_ID('tempdb..#alldbstate') ) IS NOT NULL
DROP TABLE #alldbstate;
CREATE TABLE #alldbstate
(dbname SYSNAME NOT NULL, DBstatus VARCHAR(55) NOT NULL, R_model VARCHAR(30) NOT NULL);
--SELECT * FROM sys.master_files
INSERT INTO #alldbstate (dbname,DBstatus,R_model)
SELECT sd.name, CONVERT(VARCHAR(20),DATABASEPROPERTYEX(sd.name,'status')),sd.recovery_model_desc
FROM sys.databases sd
--SELECT * FROM #dbsize
INSERT INTO #dbsize(Dbname,dbstatus,Recovery_Model)
SELECT dbname,dbstatus,R_model FROM #alldbstate WHERE DBstatus NOT IN ('ONLINE','RESTORING');
INSERT INTO #logsize(Dbname)
SELECT dbname FROM #alldbstate WHERE DBstatus NOT IN ('ONLINE','RESTORING');
INSERT INTO #dbfreesize(name)
SELECT dbname FROM #alldbstate WHERE DBstatus NOT IN ('ONLINE','RESTORING');
INSERT INTO CreditDBA_Admin.dbo.DBSizeMonitor (DBName, DBFileName, DriveLetter, DBStatus, Recovery_Model, DBSize, File_Size_MB,
Space_Used_MB, Free_Space_MB, Log_File_Size_MB, Log_Space_Used_MB, Log_Free_Space_MB, DB_FreeSpace, LogDateTime)
SELECT d.Dbname,d.DBFileName, d.driveletter, d.dbstatus,d.Recovery_Model,
(file_size_mb + log_file_size_mb) as DBsize,
d.file_Size_MB,d.Space_Used_MB,d.Free_Space_MB,
l.Log_File_Size_MB,log_Space_Used_MB,l.log_Free_Space_MB,fs.Freespace as DB_Freespace, GETDATE() AS LogDateTime
FROM #dbsize d
INNER JOIN #logsize l
ON d.Dbname=l.Dbname
INNER JOIN #dbfreesize fs
ON d.Dbname=fs.name
ORDER BY d.driveletter DESC, d.Free_Space_MB DESC, d.Dbname;
Where it fails on that DBFileName error again. (and yes, I deliberately left the DEFAULT columns as NULLABLE for the moment).
I still can't figure out why having the driveletter column "fixes" the code when I'm totally ignoring the DBFileName column in both versions. <headdesk>.
The easy fix is to through DBFileName into the code properly. But I really want to know why SQL is acting the way it is.
January 22, 2016 at 11:12 am
The sysname columns are defined as not nullable.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply