August 13, 2012 at 1:36 am
Hi All,
I need to insert sp_spaceused output of DATABASE into temp table.
How can I do this?
Pls suggest.
Thanks
August 13, 2012 at 1:39 am
Create table with exact structure as returned by stored procedure. Then use INSERT-EXEC
--Vadim R.
August 13, 2012 at 2:37 am
Interesting. Thanks for the tip.
August 13, 2012 at 2:43 am
rVadim (8/13/2012)
Create table with exact structure as returned by stored procedure. Then use INSERT-EXEC
Unfortunately, that won't work. sp_spaceused returns multiple resultsets when no @objname parameter is specified and INSERT/EXEC doesn't work with multiple resultsets.
You could use the same technique I used here[/url] for DBCC commands, which is a loopback linked server:
DECLARE @spaceused TABLE (
database_name nvarchar(128),
database_size varchar(18),
unallocated_space varchar(18)
)
INSERT @spaceused
SELECT * FROM OPENQUERY(LOOPBACK, 'EXEC sp_spaceused')
Using the LOOPBACK linked serverm, only the first resultset is returned and the second one gets ignored.
That said, starting from SQL Server 2005, there are much more appropriate ways to return the same information as sp_spaceused with DMVs. I would suggest looking into them.
-- Gianluca Sartori
August 13, 2012 at 2:48 am
I tried but it is giving below error as the sp_spaceused output is in two rows.
Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 113
Column name or number of supplied values does not match table definition.
Below are the queries I am executing.
create table #temp1
(
dbname varchar(20),dbsize varchar(20),unallocatedspace varchar(20),reserved varchar(20),data varchar(20),index_size varchar(20),unused varchar(20))
insert into #temp1 (dbname,dbsize,unallocatedspace,reserved,data,index_size,unused)
exec sp_msforeachdb
@command1='use ?; exec sp_spaceused'
August 13, 2012 at 2:58 am
Hi,
I tried using linked server and it is working on test.
But I am not allowed to create linked server on prod servers.
Any other ways??
August 13, 2012 at 3:01 am
OPENROWSET?
-- Gianluca Sartori
August 13, 2012 at 3:11 am
You could also extract the code from sp_spaceused and query DMVs and system objects directly:
-- Taken from sp_spaceused:
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 STATUS & 64 = 0
THEN size
ELSE 0
END))
,logsize = sum(convert(BIGINT, CASE
WHEN STATUS & 64 <> 0
THEN size
ELSE 0
END))
FROM dbo.sysfiles
) 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
-- Gianluca Sartori
August 13, 2012 at 3:22 am
You may also want to replace sysfiles with sys.database_files:
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
-- Gianluca Sartori
August 13, 2012 at 5:05 am
Hi Gian,
The query worked for me. I have used sp_msforeachdb to get all database size. Below is the query.
drop table #temp1
create table #temp1
(name varchar(50),
database_size varchar(50),
Freespace varchar(50))
insert into #temp1(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'
select * from #temp1
Thanks a lot.
😀
August 13, 2012 at 6:40 am
Great!
Glad I could help.
-- Gianluca Sartori
May 6, 2014 at 10:32 pm
Or if you just want the numerical database size, you could just use
SELECTdatabase_name = db_name(),
database_size = (convert(DECIMAL(15, 2), sum(size))) * 8192 / 1048576
FROM sys.database_files
Which is functionally equivalent.
July 7, 2015 at 7:23 am
If this is still a problem for some reason, here's something i used to get the total space recently on a server. Hope it helps!
declare @toExec table(tid int identity (1,1), esql varchar(max))
insert into @toExec(esql)
select 'select ''' + sd.name + ''' as dbName, sum(size * 8.0 / 1024.0) as sizeMB from [' + sd.name + '].sys.database_files with (nolock)'
from sys.databases sd with (nolock)
where [state] = 0
order by sd.name
declare @res table(dbname sysname, sizeMB decimal(18,6))
declare @esql varchar(max) = null, @tid int = null
while exists(select * from @toExec)
begin
select top 1 @tid = tid, @esql = esql from @toExec
insert into @res
exec(@esql)
delete from @toExec where tid = @tid
end
select * from @res
May 18, 2022 at 3:27 pm
This is an old post but I had the code handy so I figured why not reply, some future DBA may find it useful.
"sp_spacedused" has a parameter to consolidate the result set to a single result, "@oneresultset =1". It may not have had this in 2012. As seen in the comments there are many ways to get usage. For a single result set use this command:
EXEC sp_spaceused @oneresultset =1;
For all databases, use this script:
create table #temp (
database_name sysname,
database_size varchar(18),
[unallocated space] varchar(18),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18)
)
insert into #temp (database_name,database_size,[unallocated space],reserved,data,index_size,unused)
exec sp_msforeachdb @command1='use [?]; exec sp_spaceused @oneresultset =1;'
Select * from #temp
drop table #temp
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply