SQL Server Performance Forum – Threads Archive

SQL script fails

Hi All, I have modified Mohamed script for getting the total DB size to work on SQL2005, it works fine on test server but fails on live server. Individual DBCC and system streod procedure used in SQL script executes fine without any errors, seems some setting is preventing ..any suggestions would be great ? For now planning to remove "set nocount on" since that requires permssions as PUBLIC role. Error: line 80 : FREE not declared. SCRIPT IS LISTED BELOW: /*This query is used to check the database space used currently */ set nocount on declare @cmd varchar(500) declare @db varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> /*Create #results tables to store results, usually the temp table starts with # sign */ create table #results(FileType varchar(4) NOT NULL, [Name] sysname NOT NULL, Total numeric(9,2) NOT NULL, Used numeric(9,2) NOT NULL, [Free] numeric(9,2) NOT NULL, dbname sysname NULL) /* Create #data table to store the total extents and used extents in temporarily */ create table #data(Fileid int NOT NULL, [FileGroup] int NOT NULL, TotalExtents int NOT NULL, UsedExtents int NOT NULL, [Name] sysname NOT NULL, [FileName] varchar(300) NOT NULL) /* Create #log table to store the log size in temporarily */ create table #log(dbname sysname NOT NULL, LogSize numeric(15,7) NOT NULL, LogUsed numeric(9,5) NOT NULL, Status int NOT NULL) begin declare dcur cursor local fast_forward for select name from sysdatabases open dcur fetch next from dcur into @db while @@fetch_status=0 begin set @cmd = ‘use ‘ +'[‘[email protected]+’]’ + ‘ DBCC showfilestats’ insert #data exec(@cmd) insert #results(FileType,[Name],Total,Used,[Free],dbname) select ‘Data’, left(right([FileName],charindex(”,reverse([FileName]))-1), charindex(‘.’,right([FileName], charindex(”,reverse([FileName]))-1))-1), CAST(((TotalExtents*64)/1024.00) as numeric(9,2)), CAST(((UsedExtents*64)/1024.00) as numeric(9,2)), (CAST(((TotalExtents*64)/1024.00) as numeric(9,2)) -CAST(((UsedExtents*64)/1024.00) as numeric(9,2))), @db from #data delete #data fetch next from dcur into @db end close dcur deallocate dcur /* Get log file(s) size */ insert #log exec(‘dbcc sqlperf(logspace)’) insert #results(FileType,[Name],Total,Used,[Free],dbname) select ‘Log’,dbname+’_log’,LogSize, ((LogUsed/100)*LogSize),LogSize-((LogUsed/100)*LogSize), dbname from #log end select @@servername as servername, dbname, sum(total) Total, sum(used) Used, sum(free) Free from #results group by dbname order by Total desc drop table #results, #data, #log

The script looks ok to me….and I have executed the query on SQL 2005 database. It executed w/o error. Regards, Chetan Best Regards, Chetan
"Calm seas can never make skillful sailors".

Are you running it on a case-sensitive server? You may need to change the case in line 80 if it is erroring on ‘free’, the column has a different case earlier on in the table create Line 80 maybe should be select @@servername as servername, dbname, sum(Total) Total, sum(Used) Used, sum(Free) Free
for me it executes without errors too (unless you run it on databases which are offline)

It is working fine for me too in 2000 and 2005…
I don’t think if any of the database offline on the server should give this DECLARE error… Replace "sum(Free) Free" with "sum(Free) [Free]" and see if you get the same error…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thx guys will check it out. Cheers
Satish

]]>