How to restrict the auto log in SQL Transaction? [Resolved]

Posted by Bhuvanesh6 under Sql Server on 11/15/2016 | Points: 10 | Views : 334 | Status : [Member] | Replies : 2
We have numerous SQL Transaction occurs daily with our application.
The transaction log are so much, the server space goes out of memory.

What I actually need?

Can I reduce the logging items, say only certain transactions need to be logged and discard others.
If the logs are more than a week time older, can that be automatically deleted or override he older logs, if the memory is less.


What we tried?
Tried to auto shrink log files, but not sure about implementation, so it failed to work as expected.

Thanks in advance.

Bhuvan


Responses

Posted by: Rajnilari2015 on: 11/16/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Back up your database!

Launch SQL Server Management Studio.

Open up a query window associated with the database with the large transaction log. (Right-click on the DB and choose new query.)

Get the logical name of the transaction log file. (Right-click on the DB, select Properties, then in the Files screen, grab the Logical Name… probably ends in something like _Log.)

Execute the following, substituting with the appropriate logical name of the database log file, no quotes needed:

DBCC SHRINKFILE(<log_file_name_Log>)
BACKUP LOG <database> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<log_file_name_Log>)
Afterwards, perform a full backup of the database.

The file should shrink to a significant small shadow of its former self.

Opted from : http://help.fogcreek.com/8686/how-to-shrink-sql-server-transaction-logs

--
Thanks & Regards,
RNA Team

Bhuvanesh6, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Manicse on: 11/17/2016 [Member] Bronze | Points: 25

Up
0
Down
While creating Database we should know how much it will grow on yearly basis. based on that we need to make our logs file.
MDF and LDF are the two files exists in the SQL Servers where MDF should consume most of the memory.
To reduce or shrink or truncate the log file kindly check the below code.

USE myDB;  

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


Mani.R

Bhuvanesh6, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response