Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 420
  • Last Modified:

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
0
TRACEYMARY
Asked:
TRACEYMARY
  • 3
  • 2
1 Solution
 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now