December 7, 2011 at 6:40 am
I have multiple SQL Server 2005 Instances where i run the following SQL every week or so to capture Database sizes
which I then copy the results to an EXCEL worksheet:
/**************************************************************************************************/
DECLARE @sysdb BIT -- 1 for include sysdatabases ,0 for Not include sysdatabases
DECLARE @order int -- set the order of columns
DECLARE @orderby bit -- 0 for asc, 1 for desc
set nocount on
SET @sysdb = 0
SET @order = 1
SET @orderby = 1
declare @id int
,@type character(2)
,@pages bigint
,@dbname sysname
,@dbsize bigint
,@logsize bigint
,@reservedpages bigint
,@sqlstring varchar(2000)
create table #spacetemp (
id int identity(1,1)
,DBname varchar(500)
,DBcreationDate datetime
,DBsize decimal(10,2)
,Avispace decimal(10,2)
,DBStatusid bigint
,DBStatus varchar(200)
)
create table #stemp (
sid int identity(1,1)
,mdfpath varchar(200)
,ldfpath varchar(200)
,ds bigint
,dl bigint
)
insert into #spacetemp (DBname,DBcreationDate,DBStatusid,DBStatus)
select [name],crdate, status,case
when status=1 then 'autoclose'
when status=4 then 'select into/bulkcopy'
when status=8 then 'trunc'
when status=16 then 'torn page detection'
when status=32 then 'loading'
when status=64 then 'pre recovery'
when status=128 then 'recovering'
when status=256 then 'not recovered'
when status=512 then 'offline'
when status=1024 then 'read only'
when status=2048 then 'dbo use only'
when status=4096 then 'single user'
when status=32768 then 'emergency mode'
when status=4194304 then 'autoshrink'
when status=1073741824 then 'cleanly shutdown'
end
from master..sysdatabases where status!=512 and dbid >case when @sysdb=1 then 0 else 4 end
Declare @sDBname varchar(500)
Declare @sDBcreationDate datetime
Declare @counter int
Declare @Maxid int
set @counter = 1
select @maxid = max(id) from #spacetemp
while (@counter<=@maxid)
begin
set @id=0
set @type =''
set @pages = 0
set @dbsize = 0
set @logsize = 0
set @reservedpages = 0
set @sqlstring = ''
select @dbname = DBname from #spacetemp where id =@counter
IF @type = 'SQ'
SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 --ITT_ServiceQueue
--set @sqlstring= 'update #spacetemp set ds = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
-- ,dl = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) from #spacetemp join '+@dbname+'..sysfiles as ss
--on #spacetemp.dbname=ss.name'
set @sqlstring= 'insert into #stemp (ds,dl)
select sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
,sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) from ['+@dbname+']..sysfiles'
exec (@sqlstring)
select @dbsize=ds,@logsize=dl from #stemp where sid=@counter
update #spacetemp set
dbsize = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))
* 8192 / 1048576,15,2)),
Avispace = ltrim(str((case when @dbsize >= @reservedpages then
(convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages))
* 8192 / 1048576 else 0 end),15,2))
where [DBNAME]=@DBNAME
--select @dbname,@counter,ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))
-- * 8192 / 1048576,15,2)+' MB'),
--ltrim(str((case when @dbsize >= @reservedpages then
-- (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages))
-- * 8192 / 1048576 else 0 end),15,2)+' MB'),@dbsize,@logsize
set @counter = @counter+1
end
set @sqlstring='
select
[DBname] as ''Database Name''
,[dbcreationdate] as ''Creation Date''
,convert(varchar(200),[dbsize])+'' MB'' as ''size''
--,convert(varchar(200),[avispace])+'' MB'' as ''Aviable Space''
--,convert(varchar(200),[dbsize]+[avispace])+'' MB'' as ''Total''
--,[DBstatus] as ''Database Status''
--,DBStatusid
from #spacetemp order by '+case @order
when 1 then 'DBname'
when 2 then 'dbcreationdate'
when 3 then 'dbsize'
--when 4 then 'avispace'
--when 5 then 'dbsize+avispace'
else 'DBname' end+' '+case @orderby when 1 then 'asc' when 0 then 'desc'end +''
exec (@sqlstring)
drop table #spacetemp
drop table #stemp
Attempting to try and auotmate the process a little by using sp_send_dbmail to send the results to
my INBOX like this:
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'my.email@gmail.com',@body='Message Body',
@subject ='Message Subject',@profile_name ='mailProfile',@query ='/**************************************************************************************************/
DECLARE @sysdb BIT -- 1 for include sysdatabases ,0 for Not include sysdatabases
DECLARE @order int -- set the order of columns
DECLARE @orderby bit -- 0 for asc, 1 for desc
set nocount on
SET @sysdb = 0
SET @order = 1
SET @orderby = 1
declare @id int
,@type character(2)
,@pages bigint
,@dbname sysname
,@dbsize bigint
,@logsize bigint
,@reservedpages bigint
,@sqlstring varchar(2000)
create table #spacetemp (
id int identity(1,1)
,DBname varchar(500)
,DBcreationDate datetime
,DBsize decimal(10,2)
,Avispace decimal(10,2)
,DBStatusid bigint
,DBStatus varchar(200)
)
create table #stemp (
sid int identity(1,1)
,mdfpath varchar(200)
,ldfpath varchar(200)
,ds bigint
,dl bigint
)
insert into #spacetemp (DBname,DBcreationDate,DBStatusid,DBStatus)
select [name],crdate, status,case
when status=1 then 'autoclose'
when status=4 then 'select into/bulkcopy'
when status=8 then 'trunc'
when status=16 then 'torn page detection'
when status=32 then 'loading'
when status=64 then 'pre recovery'
when status=128 then 'recovering'
when status=256 then 'not recovered'
when status=512 then 'offline'
when status=1024 then 'read only'
when status=2048 then 'dbo use only'
when status=4096 then 'single user'
when status=32768 then 'emergency mode'
when status=4194304 then 'autoshrink'
when status=1073741824 then 'cleanly shutdown'
end
from master..sysdatabases where status!=512 and dbid >case when @sysdb=1 then 0 else 4 end
Declare @sDBname varchar(500)
Declare @sDBcreationDate datetime
Declare @counter int
Declare @Maxid int
set @counter = 1
select @maxid = max(id) from #spacetemp
while (@counter<=@maxid)
begin
set @id=0
set @type =''
set @pages = 0
set @dbsize = 0
set @logsize = 0
set @reservedpages = 0
set @sqlstring = ''
select @dbname = DBname from #spacetemp where id =@counter
IF @type = 'SQ'
SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 --ITT_ServiceQueue
--set @sqlstring= 'update #spacetemp set ds = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
-- ,dl = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) from #spacetemp join '+@dbname+'..sysfiles as ss
--on #spacetemp.dbname=ss.name'
set @sqlstring= 'insert into #stemp (ds,dl)
select sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
,sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) from ['+@dbname+']..sysfiles'
exec (@sqlstring)
select @dbsize=ds,@logsize=dl from #stemp where sid=@counter
update #spacetemp set
dbsize = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))
* 8192 / 1048576,15,2)),
Avispace = ltrim(str((case when @dbsize >= @reservedpages then
(convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages))
* 8192 / 1048576 else 0 end),15,2))
where [DBNAME]=@DBNAME
--select @dbname,@counter,ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))
-- * 8192 / 1048576,15,2)+' MB'),
--ltrim(str((case when @dbsize >= @reservedpages then
-- (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages))
-- * 8192 / 1048576 else 0 end),15,2)+' MB'),@dbsize,@logsize
set @counter = @counter+1
end
set @sqlstring='
select
[DBname] as ''Database Name''
,[dbcreationdate] as ''Creation Date''
,convert(varchar(200),[dbsize])+'' MB'' as ''size''
--,convert(varchar(200),[avispace])+'' MB'' as ''Aviable Space''
--,convert(varchar(200),[dbsize]+[avispace])+'' MB'' as ''Total''
--,[DBstatus] as ''Database Status''
--,DBStatusid
from #spacetemp order by '+case @order
when 1 then 'DBname'
when 2 then 'dbcreationdate'
when 3 then 'dbsize'
--when 4 then 'avispace'
--when 5 then 'dbsize+avispace'
else 'DBname' end+' '+case @orderby when 1 then 'asc' when 0 then 'desc'end +''
exec (@sqlstring)
drop table #spacetemp
drop table #stemp ',
@attach_query_result_as_file = 1,@query_attachment_filename ='Results.txt'
Problem is... I get this error:
Msg 102, Level 15, State 1, Line 37
Incorrect syntax near 'autoclose'.
Msg 911, Level 16, State 1, Line 47
Could not locate entry in sysdatabases for database 'only'. No entry found with that name. Make sure that the name is entered correctly.
Can anyone help me out with this?
December 7, 2011 at 6:43 am
You're missing a lot of double quotes.
What I do to make this work is take the working query in ssms, shoot it to notepad and replace ' WITH ''. Then wrap that new string into quotes again.
Then you can start the concatenation process for the dbname & other variables.
Make sure to wrap the db names with [] for non "standard" names.
December 7, 2011 at 6:46 am
when your query contains single quotes, you have to escape them, as it's now dynamic sql.
this is updated witht hat change and is at least syntactically correct:
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'my.email@gmail.com',@body='Message Body',
@subject ='Message Subject',@profile_name ='mailProfile',@query =
'/**************************************************************************************************/
DECLARE @sysdb BIT -- 1 for include sysdatabases ,0 for Not include sysdatabases
DECLARE @order int -- set the order of columns
DECLARE @orderby bit -- 0 for asc, 1 for desc
set nocount on
SET @sysdb = 0
SET @order = 1
SET @orderby = 1
declare @id int
,@type character(2)
,@pages bigint
,@dbname sysname
,@dbsize bigint
,@logsize bigint
,@reservedpages bigint
,@sqlstring varchar(2000)
create table #spacetemp (
id int identity(1,1)
,DBname varchar(500)
,DBcreationDate datetime
,DBsize decimal(10,2)
,Avispace decimal(10,2)
,DBStatusid bigint
,DBStatus varchar(200)
)
create table #stemp (
sid int identity(1,1)
,mdfpath varchar(200)
,ldfpath varchar(200)
,ds bigint
,dl bigint
)
insert into #spacetemp (DBname,DBcreationDate,DBStatusid,DBStatus)
select [name],crdate, status,case
when status=1 then ''autoclose''
when status=4 then ''select into/bulkcopy''
when status=8 then ''trunc''
when status=16 then ''torn page detection''
when status=32 then ''loading''
when status=64 then ''pre recovery''
when status=128 then ''recovering''
when status=256 then ''not recovered''
when status=512 then ''offline''
when status=1024 then ''read only''
when status=2048 then ''dbo use only''
when status=4096 then ''single user''
when status=32768 then ''emergency mode''
when status=4194304 then ''autoshrink''
when status=1073741824 then ''cleanly shutdown''
end
from master..sysdatabases where status!=512 and dbid >case when @sysdb=1 then 0 else 4 end
Declare @sDBname varchar(500)
Declare @sDBcreationDate datetime
Declare @counter int
Declare @Maxid int
set @counter = 1
select @maxid = max(id) from #spacetemp
while (@counter<=@maxid)
begin
set @id=0
set @type =''''
set @pages = 0
set @dbsize = 0
set @logsize = 0
set @reservedpages = 0
set @sqlstring = ''''
select @dbname = DBname from #spacetemp where id =@counter
IF @type = ''SQ''
SELECT @id = object_id FROM sys.internal_tables WHERE parent_id = @id and internal_type = 201 --ITT_ServiceQueue
--set @sqlstring= ''update #spacetemp set ds = sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
-- ,dl = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) from #spacetemp join ''+@dbname+''..sysfiles as ss
--on #spacetemp.dbname=ss.name''
set @sqlstring= ''insert into #stemp (ds,dl)
select sum(convert(bigint,case when status & 64 = 0 then size else 0 end))
,sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) from [''+@dbname+'']..sysfiles''
exec (@sqlstring)
select @dbsize=ds,@logsize=dl from #stemp where sid=@counter
update #spacetemp set
dbsize = ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))
* 8192 / 1048576,15,2)),
Avispace = ltrim(str((case when @dbsize >= @reservedpages then
(convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages))
* 8192 / 1048576 else 0 end),15,2))
where [DBNAME]=@DBNAME
--select @dbname,@counter,ltrim(str((convert (dec (15,2),@dbsize) + convert (dec (15,2),@logsize))
-- * 8192 / 1048576,15,2)+'' MB''),
--ltrim(str((case when @dbsize >= @reservedpages then
-- (convert (dec (15,2),@dbsize) - convert (dec (15,2),@reservedpages))
-- * 8192 / 1048576 else 0 end),15,2)+'' MB''),@dbsize,@logsize
set @counter = @counter+1
end
set @sqlstring=''
select
[DBname] as ''Database Name''
,[dbcreationdate] as ''Creation Date''
,convert(varchar(200),[dbsize])+'' MB'' as ''size''
--,convert(varchar(200),[avispace])+'' MB'' as ''Aviable Space''
--,convert(varchar(200),[dbsize]+[avispace])+'' MB'' as ''Total''
--,[DBstatus] as ''Database Status''
--,DBStatusid
from #spacetemp order by ''+case @order
when 1 then ''DBname''
when 2 then ''dbcreationdate''
when 3 then ''dbsize''
--when 4 then ''avispace''
--when 5 then ''dbsize+avispace''
else ''DBname'' end+'' ''+case @orderby when 1 then ''asc'' when 0 then ''desc'' end +''''
exec (@sqlstring)
drop table #spacetemp
drop table #stemp ',
@attach_query_result_as_file = 1,
@query_attachment_filename ='Results.txt'
Lowell
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply