0

We have a DB which is part of ALWAYS ON. We want to clear the transaction logs for that DB because that is just a production server where we replicate data from one of our other production server 4 times a day.

As we do snapshot replication of 4 GB data every time, the transaction logs are increasing day by day.

Due to limited space we thought of clearing logs everyday so that we don't hit the space issues on the.

I tried a piece of code which was working fine for a DB which is not part of ALWAYS ON.

But when I tried the code on the actual DB it did not work at all and the logs remained there. 

USE AdventureWorks;
GO
EXEC sp_addumpdevice 'disk', 'AdventureWorks_backup', N'E:\Backup\AdventureWorks.bak'; 
Go
USE AdventureWorks;
GO
BACKUP DATABASE AdventureWorks
TO AdventureWorks_backup
WITH FORMAT,
  MEDIANAME = 'AdventureWorks_backup',
  MEDIADESCRIPTION =  N'E:\Backup\AdventureWorks.bak', 
  NAME = 'AdventureWorks_backup';
GO

USE AdventureWorks;
GO
BACKUP LOG AdventureWorks
TO AdventureWorks_backup;
Go

USE AdventureWorks;
GO
CHECKPOINT
GO
USE AdventureWorks;
GO
DBCC SHRINKFILE (AdventureWorks_log,1)
GO

USE AdventureWorks;
GO
EXEC sp_dropdevice 'AdventureWorks_backup','DELFILE'
Go

In the above code I created a back up device then took back up of DB and tried to shrink the log file. But it was of no help on DB part of always on.

halfer
  • 19,824
  • 17
  • 99
  • 186
Love Gupta
  • 975
  • 2
  • 16
  • 31
  • 2
    Even if this did work, I would still advise against doing the DBCC SHRINKFILE. Keep the log file at an appropriate size that is large enough. – Hannah Vernon Oct 15 '13 at 04:19
  • What is the recovery model of the database? – Igor Borisenko Oct 15 '13 at 04:19
  • @MaxVernon - Thanks for the reply buddy, as i already mentioned I dont need the logs as we can replicate the data anytime something wrong happens on the server. So we thought of reducing the size to minimum as anyhow in a day the logs grow to 16 GB atleast. We want dont want to face any space issues cause of useless transaction logs (just in our case). – Love Gupta Oct 15 '13 at 04:21
  • @Igor - As i already told this is part of ALWAYS ON and when a DB is part of always on we cannot have anything other than FULL recovery model for a DB. – Love Gupta Oct 15 '13 at 04:22
  • What does `SELECT log_reuse_wait_desc FROM sys.databases WHERE name = N'MyDatabaseName';` show? – Hannah Vernon Oct 15 '13 at 04:22
  • @MaxVernon LOG_BACKUP – Love Gupta Oct 15 '13 at 04:25
  • The reason you cannot shrink the log file is it is waiting for `LOG BACKUP`. Try manually backing up the log file using SSMS and see if you can shrink the file that way. If the file is 16GB every day, I would not shrink it less than 16GB. – Hannah Vernon Oct 15 '13 at 04:27
  • There are some great questions about shrinking the log file over at http://dba.stackexchange.com for instance http://dba.stackexchange.com/questions/40599/how-do-i-shrink-the-physical-transaction-log-file-when-its-the-principal-in-a-m – Hannah Vernon Oct 15 '13 at 04:28
  • Thanks @MaxVernon . But can you be more descriptive about why I should not shrink it less than 16 GB? And yeah i am able to shrink the logs manually but I dont want to do it daily manually. As we are not the owners for that server. So need to come up with a script that can be executed as a part of SQL Server Job – Love Gupta Oct 15 '13 at 04:28
  • also look at this question http://dba.stackexchange.com/questions/29829/why-does-the-transaction-log-keep-growing-or-run-out-of-space – Hannah Vernon Oct 15 '13 at 04:35
  • @LoveGupta As Max Vernon already told you need to do Log Backups. That's why i asked about recovery model. You can do backups on regular basis using SqlServer Agent. – Igor Borisenko Oct 15 '13 at 04:41
  • @Igor -thanks for your time. script written in the question is actually doing the same thing, It takes the back up of the DB on a backup device and then try to shrink the logs. but it is working on a standalone DB but not on a DB in always on. – Love Gupta Oct 15 '13 at 04:44
  • 2
    >>But can you be more descriptive about why I should not shrink it less than 16 GB?<< Because if the log file grows every day up to 16 GB (16 GB is just for example) then it takes time to do this grow. So if you shrink log file every day less than 16 GB you force the server do extra job every day. – Igor Borisenko Oct 15 '13 at 04:45
  • @MaxVernon I read the question mentioned there, they are also doing the same thing which i tried to do using my script. – Love Gupta Oct 15 '13 at 04:45
  • @Igor - thanks I think i got the point here. I will try to keep it to 16 GB when i get something working. :) – Love Gupta Oct 15 '13 at 04:47

0 Answers0