January 21, 2014 at 3:47 am
Hi
I want store the data for history table but throwing error in this script, what is wrong my codeing?
Msg 8152, Level 16, State 2, Line 1
String or binary data would be truncated.
The statement has been terminated.
create table Table_size (
RecordDate datetime default getdate(),
Database_id int,
Database_name varchar(150),
[schema_name]varchar(10),
TableName varchar(250),
TableSize_MB int,
RowCounts int,
DataSize_MB int,
IndexSize_MB int,
FreeMB int)
INSERT into Table_size (Database_ID,
Database_Name,[Schema_Name],TableName,TableSize_MB,RowCounts,
DataSize_MB,IndexSize_MB,FreeMB)
SELECT Database_ID = DB_ID()
, Database_Name = DB_NAME()
, Schema_Name = a3.name
, TableName = a2.name
, TableSize_MB = (a1.reserved + ISNULL(a4.reserved,0)) / 128
, RowCounts = a1.rows
, DataSize_MB = a1.data / 128
, IndexSize_MB = (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data
THEN (a1.used + ISNULL(a4.used,0)) - a1.data
ELSE 0
END) /128
, Free_MB = (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used
THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used
ELSE 0
END) / 128
FROM (SELECT ps.object_id
, [rows] = SUM(CASE
WHEN (ps.index_id < 2) THEN row_count
ELSE 0
END)
, reserved = SUM(ps.reserved_page_count)
, data = SUM(CASE
WHEN (ps.index_id < 2)
THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END)
, used = SUM (ps.used_page_count)
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1
INNER JOIN sys.all_objects a2 ON a1.object_id = a2.object_id
INNER JOIN sys.schemas a3 ON a2.schema_id = a3.schema_id
LEFT JOIN (SELECT it.parent_id
, reserved = SUM(ps.reserved_page_count)
, used = SUM(ps.used_page_count)
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON it.object_id = ps.object_id
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON a4.parent_id = a1.object_id
WHERE a2.type <> 'S' and a2.type <> 'IT'
--AND a2.name IN ('spt_values')
ORDER BY a1.reserved desc
go
January 21, 2014 at 4:28 am
[schema_name] is too small to handle what you are trying to put into it. I increased to size to 128 and it worked.
http://technet.microsoft.com/en-us/library/ms191240(v=sql.105).aspx
(Sysname datatype)
January 21, 2014 at 5:06 am
Thank you for identified the issues.. as you suggested i used sysname datatype on schema_name column and its worked..
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply