• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 947
  • Last Modified:

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?
0
amlloyd
Asked:
amlloyd
  • 3
  • 3
1 Solution
 
maradamCommented:
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

0
 
amlloydAuthor 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.
0
 
maradamCommented:
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

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
amlloydAuthor 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/
0
 
maradamCommented:
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.
0
 
amlloydAuthor Commented:
Many thanks for your time and effort.
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now