April 2, 2008 at 10:44 am
i have a script that goes through all db's and updatesusage but the problem i have is with the read only db's.
Where is the flag that states its read only or how can i incorporate that in my script to miss the read only dbs?
The script updates the usage and then reports back any db's with less than 20% freespace(including autogrow)
I will also need to take the readonly db's out of the final select statement
thanks in advance
will also need to find out info for offline db's ect so that all i get back is online read\write dbs.
I looked at status in sysdatabases but the value is not consistant - 1 read only db shows status of 1024, another 1048 and another 1052. im looking a a guarenteed flag to state the db status.
-----------------------------------------------------------------------------------
-- Update space stats before collecting informattion
-----------------------------------------------------------------------------------
USE master;
EXEC sp_msForEachDB 'DBCC UPDATEUSAGE ( ''?'')'
------------------------------------------------------------------------------------
-- Create tempory table to hold space info
------------------------------------------------------------------------------------
If exists (select name from tempdb..sysobjects where name = 'DASD' and type = 'U')
begin
drop table Dasd
end
create table tempdb..DASD
(
createDTM varchar(20),
SQL_Server varchar(30),
db_name varchar(30),
group_name varchar(30),
group_alias varchar(30),
total_DB_space varchar(10),
group_type varchar(20),
free_DB_space varchar(10),
total_drive_space varchar(10),
free_drive_space varchar(10),
drvLetter varchar(5),
db_maxsize int,
db_growth int
)
If exists (select name from tempdb..sysobjects where name = 'DrvSpace' and type = 'U')
begin
drop table DrvSpace
end
Create table DrvSpace (
DriveLetter char(02) null,
MB_Free float null)
use tempdb
go
INSERT DrvSpace EXECUTE master..xp_fixeddrives
SET NOCOUNT ON
DECLARE @counter SMALLINT
DECLARE @holddate VARCHAR(20)
DECLARE @dbname VARCHAR(100)
DECLARE @total_DB_space VARCHAR(10)
DECLARE @sum_total_Drives_space int
DECLARE @sum_free_Drives_space int
DECLARE @sum_growth int
declare @bytesperpagedec(15,0)
declare @pagesperMBdec(15,0)
declare @summaxsize int
select @summaxsize = 0
----------------------------------------------------------------------
-- Create Temp tables to hold Data Extracted by EXEC (string....) commands
----------------------------------------------------------------------
create table #holditems1 (dbsize_raw int, maxedsize int)
create table #holditems2 (group_name varchar(30), growth int, driveletter char(1))
create table #holditems3 (reserved int)
----------------------------------------------------------------------
-- Determine Pages per MB for this SQL Server and compute createDTM
----------------------------------------------------------------------
select @bytesperpage = low
from master.dbo.spt_values
where number = 1
and type = 'E'
select @pagesperMB = 1048576 / @bytesperpage
select @holddate=convert(char(08),getdate(),112)+convert(char(08),getdate(),108)
----------------------------------------------------------------------
-- Here we begin the looping through all the individual databases
----------------------------------------------------------------------
SET @dbname = 'none'
SELECT @counter=MAX(dbid) FROM master..sysdatabases
WHILE @counter > 0
BEGIN
SELECT @dbname=name FROM master..sysdatabases WHERE dbid=@counter
IF (SELECT @dbname) = 'none'
BEGIN
GOTO NEXTONE
END
----------------------------------------------------------------------
-- This clears the temp tables
----------------------------------------------------------------------
begin
truncate table #holditems1
truncate table #holditems2
truncate table #holditems3
end
----------------------------------------------------------------------
-- These EXEC statements extract data from an individual database and
-- place it in the #holditems tables
----------------------------------------------------------------------
EXEC ('insert #holditems1 select sum(convert(dec(15),size)), sum(maxsize)
from ' + '[' + @dbname + ']' + '..[sysfiles]
where (status & 64 = 0)')
EXEC ('insert #holditems2 select name, growth, substring(filename,1,1)
from ' + '[' + @dbname + ']' + '..[sysfiles]
where (status & 64 = 0)')
EXEC ('insert #holditems3 select sum(reserved)
from ' + '[' + @dbname + ']' + '..[sysindexes]
where indid in (0, 1, 255)')
----------------------------------------------------------------------
-- Here we set @summaxsize. If #holditems1.maxsize < 0, there is no Autogrowth.
-- This value (usually -1) is transferred to @summaxsize. If Autogrowth is on
-- and maxedsize has a value > 0, this value is divided by pagesperMB and
-- transferred to @summaxsize.
----------------------------------------------------------------------
if (select maxedsize from #holditems1) > 0
begin
select @summaxsize=sum(maxedsize)/@pagesperMB from #holditems1
end
else
begin
select @summaxsize=maxedsize from #holditems1
end
----------------------------------------------------------------------
-- Here we compute @total_DB_space and @sum_gowqth
-----------------------------------------------------------------------
select @total_DB_space=ltrim(str(dbsize_raw / @pagesperMB,15,2))
from #holditems1
select @sum_growth = sum(growth)
from #holditems2
----------------------------------------------------------------------
-- Here we insert data into the DASD table.
----------------------------------------------------------------------
insert tempdb..DASD
select @holddate,
@@servername,
@dbname,
'none',
'none',
@total_DB_space,
'data only',
ltrim(str(@total_DB_space - (select convert(dec(15),reserved)
from #holditems3)/@pagesperMB,15,2)),
' ',
' ',
'none',
@summaxsize,
@sum_growth
----------------------------------------------------------------------
-- Here we update these newly inserted rows with drive space information
----------------------------------------------------------------------
select @sum_total_Drives_space = sum(t.MB_Total)
from tempdb..DrvSpace t
where t.DriveLetter in (select distinct driveletter from #holditems2)
select @sum_free_Drives_space = sum(t.MB_Free)
from tempdb..DrvSpace t
where t.DriveLetter in (select distinct driveletter from #holditems2)
update [tempdb].[dbo].[DASD]
set total_drive_space = @sum_total_Drives_space,
free_drive_space = @sum_free_Drives_space
from tempdb..DASD d
where db_name = @dbname
and @holddate = d.createDTM
----------------------------------------------------------------------
-- This is the bottom of the loop, we increment the loop counter '@counter'
-- and go back to the top of the loop to process another database
----------------------------------------------------------------------
NEXTONE:
SET @counter = @counter - 1
SET @dbname = 'none'
END
----------------------------------------------------------------------
-- Here the loop through all the databases is complete, just extra
-- cautious to clean up.
----------------------------------------------------------------------
drop table #holditems1
drop table #holditems2
drop table #holditems3
-----------------------------------------------------------------------
-- Our select statement to return required rows
-----------------------------------------------------------------------
select sql_server,
cast(db_name as varchar (15)) as 'DB Name',
cast(free_db_space as decimal(10,2)) as 'free Space(Mb)',
case when cast(db_maxsize as varchar(15)) = -1 then 'Unrestricted'
else cast(db_maxsize as varchar(15))
end as 'Max Size(Mb)',
cast(Round(sum(cast(free_db_space as decimal(10,2)) / cast(db_maxsize as decimal(10,2)))*100,0) as decimal(5,2)) as 'Percent Free'
from dasd
where db_name != 'tempdb'
and db_maxsize != '-1'
group by dasd.sql_server, dasd.db_name,dasd.free_DB_space, dasd.db_maxsize
having cast(Round(sum(cast(free_db_space as decimal(10,2)) / cast(db_maxsize as decimal(10,2)))*100,0) as decimal(5,2)) < 5
drop table temp..dasd
April 2, 2008 at 11:46 am
Use this in your script..
select databaseproperty('Database_Name','isReadOnly')
0 = False
1 = True
April 4, 2008 at 8:34 am
thanks for the reply
but....yep theres always a but
how can i get the dbcc command to cycle through all the db's and miss out any that are set to read only as it wont be able to update the stats
Cheers
L
April 4, 2008 at 10:32 am
You need to do some checking in you msforeachdb statement. You will note I also excluded system databases you can take that out if you dont need it.
EXEC master..sp_MSForeachdb '
USE [?]
--EXCLUDE SYSTEM DATABASES
IF DB_ID(''?'') > 4
BEGIN
--IF THE CURRENT DATABASE IS READ ONLY DO NOT UPDATE USAGE
IF NOT EXISTS(SELECT 1 FROM sys.databases WHERE Name = ''?'' AND Is_Read_Only = 1)
DBCC UPDATEUSAGE ( ''?'')
END
'
April 4, 2008 at 10:45 am
doesnt work in 2000 as it doesnt know of a coloumn Is_Read_Only in sysdatabases
i noticed that you left the system db's out but i'll probably include them as this is for a space monitoring job to alert on db's with less than 20% freespace(including autogrow).
I also need to be able to monitor the trans logs in a seperate script based on the previous one.
Cheers
L
April 4, 2008 at 10:56 am
Your right this is a 2005, I must have missed the requirement for 2000. You should be able to use the database properties though.
EXEC master..sp_MSForeachdb '
USE [?]
--IF THE CURRENT DATABASE IS READ ONLY DO NOT UPDATE USAGE
IF (SELECT DATABASEPROPERTY(''?'','isReadOnly')) <> 1
DBCC UPDATEUSAGE ( ''?'')
END
'
April 7, 2008 at 12:40 am
thanks very much your a star
you missed the begin at the start and extra quotes around isreadonly....other than that your forgiven
Thanks so much
L
April 8, 2008 at 9:10 am
The reason you see different values in status for your read-only databases is that the status field is a bit field. You can see most of these values from books online under sysdatabases system table. For example, the status 1024 is read-only(1024). The status 1048 is read-only(1024), torn page detection(16), and trunc. log on chkpt(8). The status 1052 also includes select into/bulkcopy(4).
To exclude both read-only and offline databases from your query you can add the following two lines to your WHERE statement:
AND (status & 512) <> 512 --OFFLINE DB
AND (status & 1024) <> 1024 -- READ ONLY DB
The reason for this is that IF (status & 1024) = 1024 then the database is in Read Only mode. This statement will catch all of your examples of status values of 1024, 1048, and 1052.
Hope this helps!
Chad
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply