We help IT Professionals succeed at work.

Shrink the Transaction Logs of all databases on a SQL Server Instance

I am trying to find a way of shrinking the transaction logs on all databases against a specific SQL Server instance, this would ideally occur at a specific time. Is it possible to do this with a Transact-SQL Query?
Comment
Watch Question

Commented:
Assuming only one log file for each database you can do that like this. Note, that this will not truncate the log. If you like to do this add the following statement before dbcc shrinkfile:

declare @db sysname set @db = db_name()
backup database @db with truncate_only

Plase be warned that truncating the log will break log sequence. So don't truncate your logs if you have databases with recovery model other than simple

sp_MSforeachdb '
declare @log varchar(1000) 
   select top 1 @log = name 
      from sys.database_files where type_desc = ''LOG'' dbcc shrinkfile(@log)'

Open in new window

Author

Commented:
I have run this code and it gives me the following message:
Server: Msg 208, Level 16, State 1, Line 3
Invalid object name 'sys.database_files'.
Server: Msg 208, Level 16, State 1, Line 3
Invalid object name 'sys.database_files'.

I am running SQL Server 2000 and not 2005 so this might be the problem? Sorry for omitting that info in original post.

Commented:
On SQL2K this is a little more tricky. sp_MSforEachDb does not change database context so clearly as SQL2k5 does. The system table is also different. But you can still do that like in the snippet below.
sp_MSforeachdb 'declare @sql nvarchar(1000) 
   select top 1 @sql = N''dbcc shrinkfile(''+name+N'')'' 
      from ?.dbo.sysfiles where groupid = 0;exec ?.dbo.sp_executesql @sql'
      

Open in new window

Author

Commented:
The code seems to work on all databases on the SQL Server 2000 instance but some are reporting that
the log cannot be shrunk because all logical log files are in use. Is there any way to force the transaction log to be shrunk. I know that no-one is using the databases at the time of running the code?
Many thanks/
Commented:
Check recovery model for your databases

select name,databasepropertyex(name,'Recovery') From master..sysdatabases

if it is full you must backup the log first with BACKUP LOG If you don't need backup logs, set recovery model to SIMPLE and then run BACKUP LOG <db_name> WITH TRUNCATE_ONLY.

Author

Commented:
Many thanks for your time and effort.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.