GOOGLE來的

SQL 2005 LOG檔 壓縮

http://www.weste.net/2008/10-28/11075783024.html

http://www.cnblogs.com/rickel/archive/2008/10/07/1305521.html

--01.get all database name
SELECT DBNAME = DB_NAME(s_mf.database_id) INTO #DN
FROM
sys.master_files s_mf
WHERE
s_mf.state = 0 and -- ONLINE
HAS_DBACCESS(DB_NAME(s_mf.database_id)) = 1 -- Only look at databases
to which we have access
GROUP BY s_mf.database_id
ORDER BY 1
--02.use cursor to fetch db name
-----02.01 declare cursor
DECLARE cur CURSOR FOR
SELECT DBNAME FROM #DN

-----02.02 open cursor & declare var
OPEN cur
DECLARE @DbName VARCHAR(200)
-----02.03 loop db bane
FETCH NEXT FROM cur into @DbName
WHILE(@@FETCH_STATUS=0)
BEGIN
print 'shrinking ' + @DbName + ' ...'
-------02.04 only shink log file

exec(
'
declare @dn varchar(200);
declare @ln varchar(210);
select @dn = name from '+@DbName+'.dbo.sysfiles where
fileid=1
select @ln = name from '+@DbName+'.dbo.sysfiles where
fileid=2
use ['+ @DbName + '] backup log ['+ @DbName +'] with no_log
dbcc shrinkfile (@ln)
'
)

-------02.04 shink db file & log file
-- dbcc SHRINKDATABASE (@DbName)
print @DbName + ' done'
FETCH NEXT FROM cur INTO @DbName
END
-----02.05 colse cursor
CLOSE cur
DEALLOCATE cur
-----03.drop temp table
DROP TABLE #DN

http://140.135.11.30/bmore?P_vega&3754