CODEWITHSUNDEEP
sql-server
A Farmanbar
A Farmanbar

Reputation: 4798

Remove Transaction Log When 10% of Allocated Space Remained

I have a limited space in the server and i have to remove transactions periodically. Moreover, I use below query that answered in this StackOverFlow question:How do you clear the SQL Server transaction log?

USE db;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE db
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (db_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE db
SET RECOVERY FULL;
GO

However, i want this simple query to be much more advanced and put below conditions.

Upvotes: 0

Views: 97

Answers (1)

Thom A
Thom A

Reputation: 95989

This is just an XYProblem. The problem isn't the transaction log size, it that's you aren't taking transaction log back ups and wondering why the transaction log is growing. It's growing because you aren't backing it up.

Either you need to add an agent task to regularly create transaction log back ups, or change the recovery model. Considering your statement "actually the transaction logs are not very important" I suggest the latter, and then set the max size of the file:

ALTER DATABASE db SET RECOVERY SIMPLE;
GO

ALTER DATABASE db
MODIFY FILE (NAME=db_Log, MAXSIZE = 1024MB);

Upvotes: 1

Related Questions