One of the key differences between an OLTP system and a Data Warehouse, is that a DW can be completely truncated and reloaded from the OLTP, since it is purely an aggregation and reporting area. So while Log files are important for OLTP, you don't need have heavy logging on the DW. I always set my DW's to "Simple". However, even in "Simple" mode some logging does occur, and log files grow over time. Not all my customers have the luxury of a DBA, and in order to help them out, I have written this script to automatically shrink log files.
Some notes before you use it:
- You must have the necessary permissions on the databases
- Change the "SomeNameFilter" in the code to filter out only databases you want to filter.
EXEC sp_MSForEachDB
'
use ?;
declare @SQL varchar(2000);
select @SQL = ''DBCC SHRINKFILE ('' + name + '')''
from sys.database_files
where type = 1
and name like ''SomeNameFilter%'';
if @SQL <> ''''
begin;
exec (@SQL);
end '
Enjoy!!
