August 13, 2010 at 3:24 am
Hi guys, I have the following script to pull data about the file sizes etc of all databases in a instance, but for some reason, if the database name is "BB Messanger" for instance it does not parse the whole name stops at BB.
the same if BB-Messanger.
Heres the code, anyone got some suggestions?
---------------------------------------------
-- Script to get file sizes from SQL Database
-- Script by Gareth Thompson (2010/08/06)
-- Tested on SQL 2005 and 2008.
---------------------------------------------
SET QUOTED_IDENTIFIER OFF
SET NOCOUNT ON
DECLARE @dbname varchar(50)
declare @string varchar(250)
set @string = ''
create table #datafilestats
( Fileid tinyint,
FileGroup1 tinyint,
TotalExtents1 dec (8, 2),
UsedExtents1 dec (8, 2),
[Name] varchar(50),
[FileName] sysname )
create table #dbstats
( dbname varchar(50),
FileGroupId tinyint,
FileGroupName varchar(25),
TotalSizeinMB dec (8, 2),
UsedSizeinMB dec (8, 2),
FreeSizeinMB dec (8, 2))
DECLARE dbnames_cursor CURSOR FOR SELECT name FROM master..sysdatabases
OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor INTO @dbname
WHILE (@@fetch_status = 0)
BEGIN
set @string = 'use ' + @dbname + ' DBCC SHOWFILESTATS'
insert into #datafilestats exec (@string)
insert into #dbstats (dbname, FileGroupId, TotalSizeinMB, UsedSizeinMB)
select @dbname, FileGroup1, sum(TotalExtents1)*65536.0/1048576.0,
sum(UsedExtents1)*65536.0/1048576.0
from #datafilestats group by FileGroup1
set @string = 'use ' + @dbname + ' update #dbstats set FileGroupName =
sysfilegroups.groupname from #dbstats, sysfilegroups where
#dbstats.FileGroupId = sysfilegroups.groupid and #dbstats.dbname =''' +
@dbname + ''''
exec (@string)
update #dbstats set FreeSizeinMB = TotalSizeinMB - UsedSizeinMB where
dbname = @dbname
truncate table #datafilestats
FETCH NEXT FROM dbnames_cursor INTO @dbname
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
drop table #datafilestats
select * from #dbstats
drop table #dbstats
-----------------------------------------------------
--Script to calculate information about the Log Files
-----------------------------------------------------
set nocount on
create table #LogUsageInfo
( db_name varchar(50),
log_size dec (8, 2),
log_used_percent dec (8, 2),
status dec (7, 1) )
insert #LogUsageInfo exec ('dbcc sqlperf(logspace) with no_infomsgs')
select * from #LogUsageInfo
drop table #LogUsageInfo
August 13, 2010 at 4:46 am
try this ---------------------------------------------
-- Script to get file sizes from SQL Database
-- Script by Gareth Thompson (2010/08/06)
-- Tested on SQL 2005 and 2008.
---------------------------------------------
SET QUOTED_IDENTIFIER OFF
SET NOCOUNT ON
DECLARE @dbname varchar(50)
declare @string varchar(250)
set @string = ''
create table #datafilestats
( Fileid tinyint,
FileGroup1 tinyint,
TotalExtents1 dec (8, 2),
UsedExtents1 dec (8, 2),
[Name] varchar(50),
[FileName] sysname )
create table #dbstats
( dbname varchar(50),
FileGroupId tinyint,
FileGroupName varchar(25),
TotalSizeinMB dec (8, 2),
UsedSizeinMB dec (8, 2),
FreeSizeinMB dec (8, 2))
DECLARE dbnames_cursor CURSOR FOR SELECT name FROM master..sysdatabases
OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor INTO @dbname
WHILE (@@fetch_status = 0)
BEGIN
set @string = 'use [' + @dbname + ']; DBCC SHOWFILESTATS'
insert into #datafilestats exec (@string)
insert into #dbstats (dbname, FileGroupId, TotalSizeinMB, UsedSizeinMB)
select @dbname, FileGroup1, sum(TotalExtents1)*65536.0/1048576.0,
sum(UsedExtents1)*65536.0/1048576.0
from #datafilestats group by FileGroup1
set @string = 'use [' + @dbname + ']; update #dbstats set FileGroupName =
sysfilegroups.groupname from #dbstats, sysfilegroups where
#dbstats.FileGroupId = sysfilegroups.groupid and #dbstats.dbname =''' +
@dbname + ''''
exec (@string)
update #dbstats set FreeSizeinMB = TotalSizeinMB - UsedSizeinMB where
dbname = @dbname
truncate table #datafilestats
FETCH NEXT FROM dbnames_cursor INTO @dbname
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
drop table #datafilestats
select * from #dbstats
drop table #dbstats
-----------------------------------------------------
--Script to calculate information about the Log Files
-----------------------------------------------------
set nocount on
create table #LogUsageInfo
( db_name varchar(50),
log_size dec (8, 2),
log_used_percent dec (8, 2),
status dec (7, 1) )
insert #LogUsageInfo exec ('dbcc sqlperf(logspace) with no_infomsgs')
select * from #LogUsageInfo
drop table #LogUsageInfo
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply