August 11, 2005 at 9:52 am
Hoping one of you gurus will be able to help me out.
I'm working with several clusters, each containing several dozen dbs. The problem occurs in a 'feature' of our software which will be fixed in the next realease.
The 'feature' alters the database from the default maxsize unrestricted to mb, and growth from % to mb.
Until our amazing R&D fixes this issue, i need to parse thru all the dbs on each cluster, determine which dbs need their logs set to 'unrestricted growth' and change it.
Here is what i was thinking;
sp_msforeachdb
use [?]
check sysfiles on status column
alter db if needed
any suggestions?
Thanks in advance,
Warren
August 12, 2005 at 4:27 am
Have you found a solution to your problem? I don't have much time but I put together this simple script that works for me. Value -1 in sysfiles.maxsize indicates that maxsize is not set to unlimited. If you go for this solution please test it thoroughly. Note that it won't work for databases with more than one log file.
declare @db_name sysname,
@sql_string varchar(1024)
declare my_cursor cursor for select name from master..sysdatabases where dbid>4 --not system dbs
open my_cursor
fetch next from my_cursor into @db_name
while (@@fetch_status=0)
begin
set @sql_string='declare @file_name sysname,@maxsize int; select @file_name=rtrim(name), @maxsize=maxsize from '+ @db_name+'.dbo.sysfiles where groupid=0;
if @maxsize<>-1 exec (''alter database '+@db_name+' modify file (name=''+@file_name+'',maxsize=unlimited)'')'
--print (@sql_string)
exec (@sql_string)
fetch next from my_cursor into @db_name
end
close my_cursor
deallocate my_cursor
August 12, 2005 at 12:46 pm
This will work, but there is an unstated underlying issue here. Why are your transaction logs set to 'unlimited' growth ? Are they not managed ? Is the application that uses the database that tranasction 'hungry' ? Are the units of work (uncommitted transactions) like table updates with no 'where' clauses, that large ? I've never managed databases with both 'autogrow' and 'unlimited' together. It usually leads to problems ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply