December 8, 2007 at 6:26 pm
I guess the best way to help you get this sorted out, is to give you a chunk of "tool" code I made a while back... could easily be turned into a stored proc... It's like sp_HelpDB on steroids... lemme know if it helps...
--====================================================================================================================
-- Presets
--====================================================================================================================
--===== Setup the environment to prevent blocking, etc
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --Allows "dirty" reads
SET NOCOUNT ON --Suppresses auto-display of rowcounts for appearance/speed
--===== Declare local variables
DECLARE @DBCount INT --Number of databases names to process
DECLARE @Counter INT --General purpose loop counter
DECLARE @SQLExec VARCHAR(8000) --Holds executable dynamic SQL
DECLARE @SQLTmpl VARCHAR(8000) --Holds dynamic SQL template
--===== Preset Values
SET @SQLTmpl ='
--===== Identify the database to use
USE
--===== Make sure usage info is up to date for each DB
DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS
--===== Get the database information similar to sp_SpaceUsed but in a set based fashion.
-- (Returns only 1 row for current database for each call)
INSERT INTO #Results
(DBName,DBID,DBTotalMB,LogFileMB,DataFileMB,UnallocatedMB,ReservedMB,TotalUsedMB,UnusedMB,DataMB,IndexMB)
SELECT DBName = DB_NAME(),
DBID = DB_ID(),
DBTotalMB = fs.DataFileMB + fs.LogFileMB,
LogFileMB = fs.LogFileMB,
DataFileMB = fs.DataFileMB,
UnallocatedMB = fs.DataFileMB - r.ReservedMB,
ReservedMB = r.ReservedMB,
TotalUsedMB = r.TotalUsedMB,
UnusedMB = r.ReservedMB - r.TotalUsedMB,
DataMB = r.DataMB,
IndexMB = r.TotalUsedMB - r.DataMB
FROM (--==== Derived table "fs" finds total file sizes (Status 64 = Log Device, 128 = Pages per MB)
SELECT DataFileMB = SUM(CASE WHEN Status & 64 = 0 THEN Size ELSE 0 END)/128.0,
LogFileMB = SUM(CASE WHEN Status & 64 <> 0 THEN Size ELSE 0 END)/128.0
FROM dbo.SysFiles
)fs
,
(--==== Derived table "r" finds types of space
SELECT ReservedMB = SUM(Reserved)/128.0,
TotalUsedMB = SUM(Used)/128.0,
DataMB = SUM(CASE WHEN IndID < 2 THEN DPages
WHEN IndID = 255 THEN Used
ELSE 0
END)/128.0
FROM dbo.SysIndexes
WHERE IndID IN (0,1,255)
)r'
--====================================================================================================================
-- Create Temp Tables
--====================================================================================================================
--===== Temp table to hold database names to work with and remember how many there are
IF OBJECT_ID('TempDB..#DatabaseNames','U') IS NOT NULL
DROP TABLE #DatabaseNames
SELECT RowNum = IDENTITY(INT,1,1),
Name
INTO #DatabaseNames
FROM Master.dbo.SysDatabases
ORDER BY Name
SET @DBCount = @@ROWCOUNT
--===== Temp table to hold results to be displayed
IF OBJECT_ID('TempDB..#Results','U') IS NOT NULL
DROP TABLE #Results
CREATE TABLE #Results
(
DBName SYSNAME PRIMARY KEY CLUSTERED,
DBID INT,
DBTotalMB DECIMAL(19,1),
LogFileMB DECIMAL(19,1),
DataFileMB DECIMAL(19,1),
UnallocatedMB DECIMAL(19,1),
ReservedMB DECIMAL(19,1),
TotalUsedMB DECIMAL(19,1),
UnusedMB DECIMAL(19,1),
DataMB DECIMAL(19,1),
IndexMB DECIMAL(19,1)
)
--====================================================================================================================
-- Loop through the databases and save the size information for each
--====================================================================================================================
--=====
SET @Counter = 1
WHILE @Counter <= @DBCount
BEGIN
--===== Get the next database name to work on an insert the dynamic SQL using the template as a master
SELECT @SQLExec = REPLACE(@SQLTmpl,' ',Name)
FROM #DatabaseNames
WHERE RowNum = @Counter
--===== Execute the dynamic SQL to get the size information for the current database name
EXEC (@SQLExec)
--===== Bump the loop counter
SET @Counter = @Counter + 1
END
--====================================================================================================================
-- Return the results
--====================================================================================================================
SELECT * FROM #Results
--Jeff Moden
Change is inevitable... Change for the better is not.
December 8, 2007 at 6:47 pm
I got the following error msg:
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '0'.
Msg 103, Level 15, State 4, Line 11
The identifier that starts with 'AdventureWorksSELECTAdventureWorksDBNameAdventureWorksAdventureWorksAdventureWorksAdventureWorksAdventureWorksAdventureWorksAdve' is too long. Maximum length is 128.
December 8, 2007 at 6:59 pm
Ah, crud... I just noticed that this is a 2k5 forum... the code I posted is most definitely for 2k...
You'll need to do some mods to the code but I can't help on that... I'm still stuck on 2k and haven't loaded the Dev Edition of 2k5, yet.
Sorry...
--Jeff Moden
Change is inevitable... Change for the better is not.
December 8, 2007 at 7:07 pm
Ran the script on 2K, and got the following error msg:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '0'.
Server: Msg 103, Level 15, State 1, Line 32
The identifier that starts with 'mastermastermastermastermastermastermastermastermastermastermastermastermastermastermastermasterDataMBmastermastermastermasterma' is too long. Maximum length is 128.
December 9, 2007 at 7:47 am
What are you running the code from? Query Analyzer or ???
The code, as I've posted it, runs fine...
Is it possible that you've run into the same problem a lot of folks have? When you copy code from one of the little blue code windows on this forum, it produces "Vertical tabs" at the end of each line of code instead of a "Carriage Return" or "Line Feed". You must first paste the code into Word, replace ^l with ^p, and then paste the code into something like Query Analyzer.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 2, 2008 at 3:05 pm
create table #temp (dbname nvarchar (50), logsize nvarchar(25),logspace nvarchar (25),status int, i int identity)
declare @cmd nvarchar(1024),@cmd2 nvarchar(1024)
set @cmd = 'dbcc sqlperf (logspace)'
insert into #temp execute( @cmd)
declare @min-2 int, @max-2 int, @count int, @dbname nvarchar(1024)
select @min-2 = min(i) from #temp
select @max-2 = max(i) from #temp
set @count = @min-2
while (@count <= @max-2)
begin
select @dbname = dbname from #temp where i = @count
SET @cmd =' USE ' + @dbname + ' exec sp_spaceused '
print (@cmd)
exec (@cmd)
set @count = @count + 1
end
select * from #temp
this will give you the database name and the log files. i suggest you modify sp_spacedused and create a modify version of sp_spaceused.
to modify the script for return one result you have to comment this section off:
--select
--reserved = ltrim(str(@reservedpages * 8192 / 1024.,15,0) + ' KB'),
--data = ltrim(str(@pages * 8192 / 1024.,15,0) + ' KB'),
--index_size = ltrim(str((@usedpages - @pages) * 8192 / 1024.,15,0) + ' KB'),
--unused = ltrim(str((@reservedpages - @usedpages) * 8192 / 1024.,15,0) + ' KB')
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply