September 7, 2005 at 12:15 pm
How to change Log File Size from 1MB to 100MB for all the User Databases name start with "SRC_" or "STG_" .
Also how to select all the Log File Names corresponding to each DB?
TIA
Jagan
September 7, 2005 at 12:40 pm
try using exec sp_MSforeachdatabase or you can use loop to shrink the file.
to get log files
SELECT * FROM sysfiles
WHERE GroupId=0 AND (Name like '%SRC__%' Escape '_' or like '%STG__%' Escape '_')
Amit Lohia
September 7, 2005 at 2:47 pm
BINGO.. Here is the DSQL which did my JOB:
USE master
GO
select 'ALTER DATABASE '+ name + ' MODIFY FILE ( name = ' +name+'_Log, FILEGROWTH = 100MB)'
from master..sysdatabases
where (name like '%DMRT_%') or (name like '%SRC_%') or (name like '%STG_%')
order by name
Jagan
September 7, 2005 at 3:09 pm
oops I read wrong. I thought you need to shrink the log files name like DMRT_ or name like DMRT_ or SRC_ or STG_.
Note: _ is a special character you will need to use escape or it means any character
Amit Lohia
September 7, 2005 at 3:11 pm
you are also assuming that log file name is <Database name>_log and also every database has 1 log file.
Amit Lohia
September 9, 2005 at 3:16 pm
This is a 'dangerous' thread ... first you probably want to use the sysaltfiles table in the master database with your name selection criteria and augment it with "and fileid = 2" to make sure you've selected proper files (query below):
select 'ALTER DATABASE '+ name + ' MODIFY FILE ( name = ' +name+'_Log, FILEGROWTH = 100MB)'
from master..sysaltfiles
where ((db_name(dbid) like '%DMRT_%') or (db_name(dbid) like '%SRC_%') or (db_name(dbid)) like '%STG_%')
and fileid = 2
order by name
Secon, the actual logfile size was not changed to be 100 Mb, it was changed to grow by 100 Mb based on the query posted (do we see a potential issue here ?).
If you want to base queries on system tables directly in the future reference I've posted a link to some great documentation (beware, in SQL2005 things will change radically !):
http://www.microsoft.com/sql/techinfo/productdoc/2000/systables.mspx
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply