which db holds all the log files | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

which db holds all the log files

Hi!
I am modifing a script which will truncate the log files of all the databases on the server.This script will first open a cursor consisting of dbname,backup the log.and shrink the log file.But I am unable to write the correct shrinkfile command ,because i am not getting all the logfile name/path in any db.So pls tell me how to get the path of all log files and use it in this script
Here is the script
USE ADMIN
GO
CREATE PROCEDURE usp_truncatelog
AS
Set quoted_identifier off
DECLARE @dbname varchar(30)
DECLARE @dbname_header varchar(60)
DECLARE dbnames_cursor CURSOR FOR SELECT name FROM master..sysdatabases
WHERE name not in (‘master’, ‘msdb’,’pubs’, ‘tempdb’, ‘model’, ‘northwind’) OPEN dbnames_cursor FETCH NEXT FROM dbnames_cursor INTO @dbname WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status = -2)
BEGIN
FETCH NEXT FROM dbnames_cursor INTO @dbname
CONTINUE
END
SELECT @dbname_header = "Database " + RTRIM(UPPER(@dbname))
PRINT @dbname_header
EXEC("BACKUP LOG " + @dbname + " WITH TRUNCATE_ONLY")
–EXEC("DBCC SHRINKFILE ("logfilename",1,TRUNCATEONLY)")
—-The above statement need to be modified for logfile name corresp to the currently ——-pointed db by the cursor
FETCH NEXT FROM dbnames_cursor INTO @dbname
END DEALLOCATE dbnames_cursor
A database has at least one data file and one transaction log file. Data and transaction log information is never mixed on the same file, and individual files are used by only one database. To get the log file name you execution SP_HELPFILE. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
select filename from sysfiles where fileid=2
declare @dbname as varchar(25)
declare @sqlstr as nvarchar(4000)
declare cur_cursor cursor for
select name from master..sysdatabases where dbid>4
open cur_cursor
fetch next from cur_cursor into @dbname
while @@fetch_status=0
begin
select @sqlstr=’SELECT filename from ‘[email protected]+’..sysfiles where fileid=2′
execute sp_executesql @sqlstr
print @sqlstr
fetch next from cur_cursor into @dbname
end
close cur_cursor
deallocate cur_cursor
go
]]>