iteration through all active database

Try:

EXEC sp_MSForEachDB 'DBCC shrinkfile('log',10)'

Select allOpen in new window

Read up on this stored procedure though, here before running it: http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

The script above won't work. Every db has different log file name.

Is it optimal that every db has a very small initial log size?

This is working script, same script can be found
http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31533/

USE master
GO
DECLARE @Statement varchar (2000)

SELECT @Statement = ''
SELECT @Statement = @Statement + 'USE ?; '
SELECT @Statement = @Statement + 'SELECT ''?''; '
SELECT @Statement = @Statement + 'DECLARE @Log_Logical_FileName varchar (30); '
SELECT @Statement = @Statement + 'SELECT @Log_Logical_FileName = rtrim(name) FROM dbo.sysfiles WHERE (status & 0x40) <> 0; '
SELECT @Statement = @Statement + 'dbcc shrinkfile (@Log_Logical_FileName, 30,truncateonly); '
SELECT @Statement = @Statement + 'SELECT fileid, name, filename, size, growth, status, maxsize FROM dbo.sysfiles WHERE (status & 0x40) <> 0; '
SELECT @Statement 

EXEC sp_MSforeachdb @command1=@Statement
GO

Select allOpen in new window

You could also try this to shrink all data and log files:

EXEC sp_MSForEachDB 'DBCC SHRINKDATABASE (''?'' , 0)'

Select allOpen in new window

but the above Expert's answer is correct as well...it will search for all log files, but still uses the sp_MSForEachDB stored procedure.

Shrinking database files causes fragmentation. Do not shrink your database, unless you have a very good reason for it.

The other thing to consider here is why the transaction logs have gotten as big as they have.

Do you have databases in full recovery mode where you're not taking transaction log backups regularly ?

Just going and shrinking transaction logs is not necessarily a wise thing to do without proper backups in place as you may break your recovery chain.

You may want to read this article: https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_11077-How-to-shrink-a-bloated-log-file.html that explains what causes a transaction log file to grow and steps you can take to recover space.  It also links to several other article on good transaction log management procedures.

If you do have TLOG backups in place already, and your logfiles grows large, then SQL Server obviously needs to have that space to perform your operations.  Shrinking log files and then allowing SQL Server to autogrow your logfile back to the size it needs will also lead to possible transaction log performance issues with too many VLF's in your logfile.

Then on top of that, allowing your transaction log to autogrow instead of presizing it to fit your needs can also have an impact on performance while the autogrow is happening.

THANKS Liija,

I want to execlude system database how to do that?

Added if-clause to the script that returns if system database

USE master
GO
DECLARE @Statement varchar (2000)

SELECT @Statement = ''
SELECT @Statement = @Statement + 'IF ''?'' IN (''master'',''model'',''msdb'',''tempdb'') RETURN '
SELECT @Statement = @Statement + 'USE ?; '
SELECT @Statement = @Statement + 'SELECT ''?''; '
SELECT @Statement = @Statement + 'DECLARE @Log_Logical_FileName varchar (30); '
SELECT @Statement = @Statement + 'SELECT @Log_Logical_FileName = rtrim(name) FROM dbo.sysfiles WHERE (status & 0x40) <> 0; '
SELECT @Statement = @Statement + 'dbcc shrinkfile (@Log_Logical_FileName, 30,truncateonly); '
SELECT @Statement = @Statement + 'SELECT fileid, name, filename, size, growth, status, maxsize FROM dbo.sysfiles WHERE (status & 0x40) <> 0; '
SELECT @Statement 

EXEC sp_MSforeachdb @command1=@Statement

Select allOpen in new window

it works. But as this will be my base script,which will help me to build my own logic.
I tried before block in my script ,i have execulded the tempdb ,despite it's including tempdb and throwing me the error as below

Msg 5058, Level 16, State 1, Line 1
Option 'RECOVERY' cannot be set in database 'tempdb'.

declare @statement varchar(2000)
select @statement = ''
select @statement = @statement +'if ''?'' in (''master'',''model'',''tempdb'',''msdb'')return '
select @statement = @statement + 'use ?;' 
select @statement = @statement +'select ''?'';'
select @statement = @statement + 'alter database ? set recovery simple'
select @statement 

exec sp_msforeachdb @command1=@statement

Select allOpen in new window

in the result window,it clearly not looped inside tempdb but the query is failing saying
Msg 5058, Level 16, State 1, Line 1
Option 'RECOVERY' cannot be set in database 'tempdb'.
Please see the attached screenshot
screenshot.jpg

Instead of line 9 exec sp_msforeachdb...
Could you let it do:
Print @statement
And let us see the full text of this.
Regards Marten

how does this will help?
Line 7 does the same select @statement .

if '?' in ('master','model','tempdb','msdb')return use ?;select '?';alter database ? set recovery simple

 this what it returned.
I don't think there wrong with query.But i like to understand why SQL is not able to exclude tempdb

Might be a bug in this undocumented feature.
Also tried this:

EXEC sp_MSforeachdb 'IF ''?''  NOT IN (''tempDB'',''model'',''msdb'')
BEGIN
ALTER DATABASE ? SET RECOVERY SIMPLE     
END'

Select allOpen in new window

Above also tries to modify tempdb... doesn't work as one would imagine

Also found out that there are other problems with MSforeachdb. Some people have rewritten that logic
http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/
http://sqlblog.com/blogs/aaron_bertrand/archive/2010/02/08/bad-habits-to-kick-relying-on-undocumented-behavior.aspx

hello Mark Sir,

First of all i thank you to give 2 valuable post and wonderful query which fulfill one part of my requirement.

And you are my one of the best experts in this area.I simply love your knowledge.

I do like to know ,how the query is able to loop through all the log file.

Similar I tried below query

declare @database varchar (3000)
;with cte_db as
( 
SELECT databases.name database_name
      ,master_files.name logical_name
      ,databases.recovery_model_desc recovery_model
      ,databases.compatibility_level
      ,master_files.type_desc type_desc
      ,master_files.physical_name physical_name
      ,master_files.state_desc state_desc
      ,master_files.size /128.0 size_allocated_MB
FROM sys.master_files
LEFT JOIN sys.databases ON master_files.database_id = databases.database_id
where databases.name not in ('model','master','tempdb','resources','msdb')
)

select @database='use master go ;'+'alter '+database_name +'set recovery full' from cte_db
print @database 

Select allOpen in new window

but it returned only one database.Where i'm going wrong?

OK, it *should* return all databases in that instance...

Always check to first see what the straight query returns,

Then you will notice your select @database is not concatenating...

ie

select @database = isnull(@database+';'+char(13)+char(10),'') + 'alter '+ -- etc

Now, I put a carriage return + line feed for ease of reading.

Then after you have your list of DB's, then put in the 'use master' at the beginning, although, I would suggest you actually run it from master and not worry about the "set" below.

ie set @database = 'use master go ' + @database

Does that make sense ? Check out the previous examples again, and you will notice the difference

Still I'm not clear.What does this isnull(@sql+';'+char(13)+char(10),'')?

why do we need to concatenate char(13)+char(10)

is @sql return null value then it will replace with ; but like understand the code char(13)+char(10),'')---and usage of ' '?

what does this mean sir? Kindly help me to understand.

OK, when we add a string to a NULL value, then we still get NULL.

We are using that to our advantage. And I will explain more later...

char(13)+char(10)  is the carriage return + line feed characters. Basically starts a new line. So, when we print it looks structured. the char() command converts the ascii number into a character examples : char(34) is the double quote, char(44) is a comma, char(65) is the letter 'A' etc.

So, it is a way we can easily include special characters like tabs, linefeeds, etc

If we want to check a NULL value, we use the ISNULL()  function and it basically has the form of ISNULL(<object to check>, <if it is NULL then use this value instead>)

so what we are doing with  isnull(@sql+';'+char(13)+char(10),'')  is creating the seperator between each of the objects from our select statement.

The first time in, @SQL is a NULL value because the only thing we have done is to declare it. So, the very first touch of @SQL is to check to see if it is NULL and if it is make it an empty string. But next time it is "touched" it is no longer NULL, so the concatenate (ie the + ) of those characters (ie the ';' and the two special characters) now works.

When you do a select <some object> from <a data source> you return multiple rows, so, we use that to build up a string when we select into a variable. But, we have to make sure we are adding to the variable and not simply replacing the contents of the variable

e.g.  if you were to copy and paste the code below into a new query window, to run through the examples one by one (highlight from "declare" through to "go" and press F5 - the keyboard shortcut for "run"):

declare @v varchar(100)
set @v = '1'    -- first time sets the value to be a '1'
set @v = '2'    -- second time replaces '1' with a '2'
set @v = @v + '3'   -- now we are concatenating the '3' to what ever we already had in @v and get '23'
print @v
go

-- now doing the same with a select
-- but a select has to be from a datasource
-- for our purposes, our datasource will simply be the subquery (select 1 as my_column union all select 2 union all select 3) as my_datasource
-- so lets first do a simple select from that datasource, 
-- oh and dont forget the single quotes around the number otherwise we will get a different result

select my_column
from (select '1' as my_column union all select '2' union all select '3') as my_datasource
go

-- now we can do similar things as the SET
declare @vs varchar(100)
select @vs = my_column      
from (select '1' as my_column union all select '2' union all select '3') as my_datasource
print @vs

-- to see the content of @v you have to either print out that variable or select it. 
-- but it will be whatever the last value was returned from our datasource ie a '3'
-- so, to concatenate, we have to add the selected contents into the variable

select @vs = @vs + my_column      
from (select '1' as my_column union all select '2' union all select '3') as my_datasource
print @vs

-- if we print out the above we will see the content of @vs will be a '3' from the previois select, and then a '123' from the above select.
go

-- so now, lets try to combine that isnull thingy

declare @vn varchar(100)
print isnull(@vn,'this value will be used if @vn is NULL')    -- hope that becomes self evident when you run
print isnull(@vn+';','this value will be used if @vn is NULL')    -- not how @vn + ';' still returns a NULL

select @vn = isnull(@vn+';','') + my_column      
from (select '1' as my_column union all select '2' union all select '3') as my_datasource
print @vn
go

-- and that last one shows pretty much what we are doing for the @SQL
-- the very first time the variable @vn is null 
-- so the isnull(@vn + ';','') + my_column will make it an empty string and appends the first row ie ('') + '1' ie '1'
-- the next row appends '2' to @vn so the isnull(@vn + ';','') + my_column becomes  ('1' + ';') + '2' ie '1;2'
-- the last row appends '3' to @vn so the isnull(@vn + ';','') + my_column becomes  ('1;2' + ';') + '3'

-- now to add in the carriage return and line feed we can use the char() function, or, 
-- we could also include an actual carriage return+line feed but makes it a bit harder to read 

declare @vn varchar(100)
select @vn = isnull(@vn+';
','') + my_column      
from (select '1' as my_column union all select '2' union all select '3') as my_datasource
print @vn
go

Select allOpen in new window

Awesome Mark!!! Thanks alot. Appreciate your explanation. Your are coolest person i found in EE. Thanks for being so patient.

Your words are way too kind, but thank you wholeheartedly...