Cannot pass database name into dbcc exec command

I have the following:
All works execpt the line i have marked -***************

CREATE TABLE #filestats (Fileid int, FileGroup int, TotalExtents dec(15,2),
UsedExtents dec(15,2), Name nchar(128), FileName nchar(260))

declare @DATABASENAME nvarchar(200)
SET @DATABASENAME = 'DATASTREAM7i'
----This works for one database
INSERT INTO #filestats
EXEC ('USE ' + @DATABASENAME + ' DBCC SHOWFILESTATS')


declare @pagesperMB   dec(15,2)
declare @totalExtents dec(15,2)
declare @usedExtents dec(15,2)
declare @dbsize dec(15,2)
declare @dbspaceavail dec(15,2)

SELECT @totalExtents = sum(TotalExtents) FROM #filestats
SELECT @usedExtents = sum(UsedExtents) FROM #filestats

select @pagesperMB = 1048576.00 / (select low from master.dbo.spt_values
where number = 1 and type = 'E')

set @dbsize = (@totalExtents * 8.00 ) / @pagesperMB
set @dbspaceavail = @dbsize - ((@usedExtents * 8.00 ) / @pagesperMB)

DROP TABLE #filestats

CREATE TABLE #logstats (DatabaseName nchar(128), LogSize dec(15,2),
LogSpaceUsed dec(15,2),  Status int)

declare @DATABASENAME nvarchar(200)
SET @DATABASENAME = 'DATASTREAM7i'
---When i execute this i get every database not just the one database
INSERT INTO #logstats
EXEC ('USE ' + @DATABASENAME + ' DBCC SQLPERF(LOGSPACE)')                  -***************

-




declare @logsize dec(15,2)
declare @logsizeused dec(15,2)
declare @logspaceavail dec(15,2)


select @logsize = sum(LogSize) FROM #logstats WHERE DatabaseName = @DATABASENAME
select @logsizeused = sum(LogSpaceUsed) FROM #logstats WHERE DatabaseName = @DATABASENAME



set @logspaceavail = @logsize - (@logsize * (@logsizeused / 100.00))

select @dbsize + @logsize AS DatabaseSize,
       @logsize as LogSize,
       @logspaceavail + @dbspaceavail AS
       SpaceAvailable,  @dbsize as DBSIZE


DROP TABLE #logstats
drop table #filestats
LVL 7
TRACEYMARYAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
DBCC SQLPERF
Provides statistics about the use of transaction-log space in all databases.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
So whether you use it or not it will push all the information
0
 
TRACEYMARYAuthor Commented:
Thats a shame only wanted it on one...........any other way to get the log information then using the DBCC SQLPERF
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Insert into the temp table, delete all the rest except that particular db
0
 
TRACEYMARYAuthor Commented:
That should work.....i got it as

declare @DATABASENAME nvarchar(200)
SET @DATABASENAME = 'DATASTREAM7i'
declare @logsize dec(15,2)
declare @logsizeused dec(15,2)
declare @logspaceavail dec(15,2)

select @logsize = sum(LogSize) FROM #logstats WHERE DatabaseName = @DATABASENAME
select @logsizeused = sum(LogSpaceUsed) FROM #logstats WHERE DatabaseName = @DATABASENAME
print @logsize

But it gives me 399.99 for logsize and i know its 409,600 kb ? on the hard disk .
little off

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.