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?
amlloydAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
amlloydAuthor Commented:
Many thanks for your time and effort.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.