May 4, 2009 at 6:28 am
HI,
we have job which runs daily for shrinking the job from last few days we are getting the warning message but the log is not truncated...
the following is the error message
[SQLSTATE 01000] (Message 0) Cannot shrink log file 2 (PER_LOG)
because total number of logical log files cannot be fewer than 2. [SQLSTATE 01000] (Message 9006)
DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)
the following is the Script that is setup has a Job in SQL Server to clear all the log of all the Application DB’s
DECLARE @DBNAME VARCHAR(100),
@Query NVARCHAR(4000),
@SqlParams Nvarchar(4000),
@LogFileName VARCHAR(100)
DECLARE DBLog CURSOR FOR
SELECT LTRIM(RTRIM(convert(varchar,NAME))) FROM MASTER..SYSDATABASES WHERE DBID > 5 OR name = ‘tempdb’
Open DBLog
FETCH NEXT FROM DBLog INTO @DBNAME WHILE @@FETCH_STATUS = 0
BEGIn SET @Query = ‘BACKUP LOG ‘ + ltrim(rtrim(@DBNAME)) + ‘ WITH TRUNCATE_ONLY’
PRINT @Query EXEC (@Query)
select @SqlParams =’@LogFileName varchar(100) out ‘
select @Query = N’select @LogFileName = ltrim(rtrim(name)) from ‘ + @DBNAME + ‘..sysfiles where fileid = 2′
–PRINT @Query
exec sp_executesql @Query,@SqlParams, @LogFileName OUTPUT
–PRINT @LogFileName
SET @Query = ‘USE ‘ + @DBNAME + ‘ ‘ + char(10)+”+char(10) + ‘ DBCC SHRINKFILE (”’ + @LogFileName+ ”’,1)’
– PRINT @Query
EXEC (@Query)
FETCH NEXT FROM DBLog INTO @DBNAME END close DBLog deallocate DBLog
May 4, 2009 at 10:38 am
For which DB your shirnk file is not working?
check:
HTH.
Vinay
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
May 4, 2009 at 11:02 am
Why are you shrinking your log on a regular basis? It's just going to have to grow again. When it does it's going to slow the system down. Also repeated shrink/grow cycles can cause file-level fragmentation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply