[MSSQL] Desizing the Transaction log

for further steps

To desize the transaction log of statistical  database which is just for the log-likely data and of which the transaction log grows very fast, set the recovery option for that database as “SIMPLE” and truncate the log.

Due to the queries to desize the logs are slightly different by the version of MSSQL, to memorize, keep the record here.

* MSSQL 2012 

–Get db file information

EXEC SP_HELPFILE

SELECT * FROM SYSFILES  — for database file information

–truncate log

DBCC SHRINKFILE([log filename or file id], file size)      –desize to a specific size
DBCC SHRINKFILE([log filename or file id], TRUNCATEONLY)  –desize by truncating the unecessary log file

ex)

DBCC SHRINKFILE (‘XERP_LOG’,500)
DBCC SHRINKFILE (‘XERP_LOG’,TRUNCATEONLY)

DBCC LOGINFO

아래는 http://unions5.tistory.com/84 에서 참고한 내용
(휴면계정이라 동의를 구하지 못한 점 양해바랍니다)

* MSSQL 2008 

USE [DataBase];

GO

— Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE [DataBase] SET RECOVERY SIMPLE;

GO
— Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE ([DataBase_Log or file id], 1);

GO
— Reset the database recovery model.

ALTER DATABASE [DataBase] SET RECOVERY FULL;

GO

* MSSQL 2005 

use [DB name];
sp_helpfile;   –for log file name, file id

backup log [DB name] with no_log;
dbcc shrinkfile ([log filename or file id], 10);  –[log filename or file id] desize to 10MB

* MSSQL 2000 

use [DB name];
sp_helpfile; –for log file name, file id

backup log [DB name] with truncate_only;
dbcc shrinkfile ([log filename or file id], 10);  –[log filename or file id] desize to 10MB

 

7,580 total views, 14 views today

One thought on “[MSSQL] Desizing the Transaction log

  1. Pingback: log truncate interruption – open transaction – bluecore's…

Leave a Reply