Ever wondered what could be done about the enormous growth of Microsoft SQL Server's log files? Tried to keep it under control but found that the Enterprise Manager was unable to cope sufficiently? Here's what we use.
E. Kastelijns, A.E.Veltstra
June 22, 2006
Fully reviewed at 11 Dec. 2012
Create a new SQL procedure with the following code:
Then, using the Enterprise Manager, create a sheduled task which calls this procedure. Done!
CREATE PROCEDURE [dbo].[ShrinkAllLogFiles] AS
BEGIN
set nocount on
declare @name sysname
declare c1 cursor for
select name from master.dbo.sysdatabases
where has_dbaccess(name) = 1
-- Only look at databases to which we have access
open c1
fetch c1 into @name
while @@fetch_status >= 0
begin
print @name
backup log @name with no_log
DBCC SHRINKDATABASE (@name, 10, TRUNCATEONLY)
fetch c1 into @name
end
deallocate c1
END
GO
Then, using the Enterprise Manager, create a sheduled task which calls this procedure. Done!
Need problem solving?
Talk to me. Let's meet for coffee or over lunch. Mail me at “omegajunior at protonmail dot com”.