0

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.

  • Remove Transaction Logs When 10% free space remained from the db_Log(100 mb free 900 mb used)
  • Transaction Logs file Must have a fixed size(1GB)
A Farmanbar
  • 4,381
  • 5
  • 24
  • 42
  • Why are you changing the recovery mode? You should be backing up the database transaction logs. If you want to limit the size of the transaction log you can use `ALTER DATABASE` and `MODIFY FILE` to set the maximum size (and current size) to `1024MB`. – Thom A Mar 15 '20 at 15:08
  • @Larnu the transaction logs file even 1024MB continuously filled within 2 days and database shuts down. – A Farmanbar Mar 15 '20 at 15:12
  • So why are you not doing more regular transaction log back ups ? That seems to be the *real* problem here. If you're aren't doing transaction log back ups, either don't use `FULL` recovery mode, or do them. – Thom A Mar 15 '20 at 15:13
  • @Larnu because the database is not inline(Security measurements) actually the transaction logs are not very important. and backuping again consume spaces unless we do pass them to somewhere else. – A Farmanbar Mar 15 '20 at 15:16
  • *"because the database is not inline"* what does that even mean? If if the transaction logs don't matter, don't use the the `FULL` recover mode... Leave it in `SIMPLE`. – Thom A Mar 15 '20 at 15:17
  • @Larnu there is a query validator which checks if the query is a valid query therefore, we are sure all transaction are valid because we have set a strong validator so transactions logs are not very important in order to track hacking and other security matters. – A Farmanbar Mar 15 '20 at 15:20

1 Answers1

1

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);
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • i have to investigate side effects of RECOVERY SIMPLE if it has not side effect i will do it. – A Farmanbar Mar 15 '20 at 15:23
  • Effect on what, @Mr.AF ? You aren't doing transaction log backups, so it's not going to have any (negative) effect as you aren't making use of the recovery model. – Thom A Mar 15 '20 at 15:24
  • i am reading here : https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-ver15 – A Farmanbar Mar 15 '20 at 15:26
  • `Under the simple recovery model, the database cannot be restored to a specific point in time within a specific backup.` – A Farmanbar Mar 15 '20 at 15:30
  • ... Try reading everything, @Mr.AF , especially under the Full section. Note it states "***Requires log backups.***" You ***aren't*** performing tranasction log backups, so the `FULL` recovery mode is **useless**. It's like saying that you have off site back ups because you back up to tape; but those tapes never leave the room the server is in. They aren't off site if they never left. – Thom A Mar 15 '20 at 15:34
  • for confident, if i backup database with simple recovery mode and delete whole database and use this backup to restore the database . what's the result? is there any lost regardless of logs? – A Farmanbar Mar 15 '20 at 15:42
  • That's also covered in the documentation you linked, @Mr.AF . – Thom A Mar 15 '20 at 15:46