get sql log file name from within a script

Question

We have a few test databases that we are throwing test indexes into and the log file gets bloated real quickly due to us dropping the contents of the table and repopulating it.

I have found, thanks to Stack Overflow, a few scripts and put them together to do what I need.

Here is the script:

USE SSSIndexes
GO
ALTER DATABASE SSSIndexes SET RECOVERY SIMPLE WITH NO_WAIT
GO
DBCC SHRINKFILE(N'SSSIndexes_Log', 1)   <-- my issue is here
GO

The problem is the log file name. Is there a way to get the log file name without having to look it up manually and include it in the script to where this part is automated?

Btw, we never intend on restore this database. These are temporary indexes.

Thanks!

Dis Shishkov · Accepted Answer · 2013-03-04 18:16:17Z

10

USE SSSIndexes
GO
ALTER DATABASE SSSIndexes SET RECOVERY SIMPLE WITH NO_WAIT
GO
DECLARE @Name NVARCHAR(50)

DECLARE cur CURSOR FOR
SELECT [name]
FROM [sys].[database_files] 
where [type] = 1

OPEN cur
FETCH NEXT FROM cur INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
    DBCC SHRINKFILE(@Name, 1)
    FETCH NEXT FROM cur INTO @Name
END
CLOSE cur
DEALLOCATE cur

answered Mar 4, 2013 at 18:16

Dis Shishkov

6567 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ErocM Over a year ago

If I am reading this correctly, it is shinking all databases in that instance? Am I right in thinking this?

Dis Shishkov Over a year ago

No, only one database, see to the 1st row: USE SSSIndexes. [sys].[database_files] is specific for each data base.

L_J · Accepted Answer · 2019-12-31 08:19:18Z

0

You can use this to generate script for log file truncating of all databases on a specific server. To stick to specific databases use a filter.

SELECT
' USE [' + name + ']; GO

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE [' + name + '] SET RECOVERY SIMPLE;
GO

-- Shrink the truncated log file to 1 MB.
DECLARE @logname varchar(128);
SELECT TOP 1 @logname=[name] FROM [' + name + '].[sys].[database_files] WHERE [type] = 1;
DBCC SHRINKFILE (@logname, 1);
GO

-- Reset the database recovery model.
ALTER DATABASE [' + name + '] SET RECOVERY FULL;
GO

' AS qry FROM master.dbo.sysdatabases
WHERE dbid > 6

edited Dec 31, 2019 at 8:19

L_J

2,45711 gold badges28 silver badges33 bronze badges

answered Jun 28, 2016 at 6:13

Abdul Waahid

1891 silver badge4 bronze badges

Comments

Michael Adamission · Accepted Answer · 2020-07-17 02:57:01Z

WARNING!!!: YOU SHOULD ONLY DO THIS ON TEST DB SERVERS. Production DBs typically WANT to have FULL recovery mode. Test DBs you usually don't care.

Expanding on Abdul's answer (I had trouble getting it to show in new lines) and Dis' answers this finds the first db that has recovery FULL, sets it to simple and shrinks it... just keep clicking execute until it returns null

declare @dbname nvarchar(50)
declare @logfilename nvarchar(100)
select top(1) @dbname = name from sys.databases where recovery_model_desc <> 'SIMPLE' AND name <> 'tempdb'

declare @shrinkdb nvarchar(500)
set @shrinkdb = 'USE master 
ALTER DATABASE [' + @dbname + '] SET RECOVERY SIMPLE 
'
select @shrinkdb
execute sp_executesql @shrinkdb

set @shrinkdb = 'USE [' + @dbname + ']

DECLARE @Name NVARCHAR(50)

DECLARE cur CURSOR FOR
SELECT [name]
FROM [sys].[database_files] 
where [type] = 1

OPEN cur
FETCH NEXT FROM cur INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
    DBCC SHRINKFILE(@Name, 1)
    FETCH NEXT FROM cur INTO @Name
END
CLOSE cur
DEALLOCATE cur

'

select @shrinkdb
execute sp_executesql @shrinkdb

Collectives™ on Stack Overflow

get sql log file name from within a script

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related