Copy Code code as follows:
DECLARE @name VARCHAR (25)
DECLARE @SQL VARCHAR (1000)
DECLARE @logid INT
DECLARE sysdatabase_name CURSOR for SELECT name from master.dbo.sysdatabases
OPEN Sysdatabase_name
FETCH NEXT from Sysdatabase_name into @name
While @ @FETCH_STATUS = 0
BEGIN
IF (@name not in (' xxx ')-database name that does not require log shrinkage
BEGIN
SET @SQL = ' DECLARE @logid INT
Use ' + @name + '
SELECT @logid = Fileid from Sysfiles WHERE right (RTrim (filename), 3) = ' ldf '
BACKUP LOG ' + @name + ' with no_log
DBCC shrinkfile (@logid) '
EXEC (@SQL)
End
FETCH NEXT from Sysdatabase_name into @name
End
Close Sysdatabase_name
Deallocate Sysdatabase_name
SQL statement Implementation SQL Server 2005 log shrink (bulk)
Copy Code code as follows:
-->title: Generating test data
-->author:wufeng4552
-->date:2009-09-15 08:56:03
declare @dbname nvarchar (20)--Data library name
declare @sql nvarchar (max)
Declare sysdbname cursor for select name from master.dbo.sysdatabases
Open Sysdbname
FETCH NEXT from Sysdbname into @dbname
While @ @fetch_status =0
Begin
if (@dbname not in (' xxx ')-database name that does not require log shrinkage
Begin
Set @sql =--Journal file ID
N ' Declare @logid int ' +
N ' use ' + @dbname +
N ' select @logid =fileid from Sysfiles where right (LTrim (RTrim (filename), 3) = ' ldf ' +
--Truncate transaction log
N ' backup log ' + @dbname + ' with no_log ' +
--Shrinking the specified data file
N ' DBCC SHRINKFILE (@logid) '
EXEC (@sql)
End
FETCH NEXT from Sysdbname into @dbname
End
Close Sysdbname
Deallocate Sysdbname
