April 26, 2011 at 12:51 pm
declare @l_db_name varchar(50)
,@l_sql_string varchar(1000)
set nocount on
if object_id('DB_Growth') is not null
truncate table CAPACITY_DB_Growth
declare db_name_cursor insensitive cursor
for
select name from master..sysdatabases
open db_name_cursor
fetch next from db_name_cursor into
@l_db_name
While (@@fetch_status = 0)
begin
select @l_sql_string = 'select ' + '''' + @l_db_name + '''' + ', name, ceiling((size * 8192)/(1024.0 * 1024.0)), case when status & 0x100000 = 0 then convert(varchar,ceiling((growth * 8192)/(1024.0*1024.0))) + '' MB''' + char(10)+char(13)
--+ 'else convert (varchar, growth) + '' Percent''' + char(10)+char(13)
+ 'else convert (varchar, growth) + '' Percent''' + char(10)+char(13)
+ 'end' + char(10)+char(13)
+ 'from ' + @l_db_name + '.dbo.sysfiles'
insert into TSTDB.dbo.CAPACITY_DB_Growth(Database_Name, Logical_File_Name, File_Size_MB, Growth_Factor)
exec (@l_sql_string)
fetch next from db_name_cursor into
@l_db_name
end
close db_name_cursor
deallocate db_name_cursor
--select timecollected, logicalfilename,filespaceusedmb from capacity_datafile_info order by logicalfilename with (nolock)
set nocount off
set ansi_warnings on
return
GO
I recieving the following error- Can you please help...
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
April 26, 2011 at 1:05 pm
Put a PRINT before the INSERT statement, like this:
PRINT @l_sql_string
insert into TSTDB.dbo.CAPACITY...
The last query printed will be the one which fails. Copy and paste it into a new query window to figure out what's going on. I'd suspect this...
select [status], status & 0x100000
from YourTable.dbo.sysfiles
is what's causing your problem.
Check the following statements - I don't think they're doing what you think they are:
if object_id('DB_Growth') is not null
truncate table CAPACITY_DB_Growth
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 26, 2011 at 1:40 pm
ChrisM@home (4/26/2011)
Put a PRINT before the INSERT statement, like this:PRINT @l_sql_string
insert into TSTDB.dbo.CAPACITY...
The last query printed will be the one which fails. Copy and paste it into a new query window to figure out what's going on. I'd suspect this...
select [status], status & 0x100000
from YourTable.dbo.sysfiles
is what's causing your problem.
Check the following statements - I don't think they're doing what you think they are:
if object_id('DB_Growth') is not null
truncate table CAPACITY_DB_Growth
I thought the same thing but was somewhat intrigued to see if I could figure out what was going on...
This took a bit of trial and error but the issue is that you have at least one somewhat large DB.
ceiling((size * 8192)/(1024.0 * 1024.0))
I suspect that what you ran into is that the calculation of size * 8192 exceeds the max size for an integer which will be the datatype used for this calculation because '8192' will be interpreted as an int and int is the datatype of the 'size' column in the view. Change to 8192.0 and you should be ok because the calculation will be performed on a numeric datatype which should give you plenty of room. 😎
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 26, 2011 at 2:02 pm
Thank you very much! It worked...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply