How to clean database transaction log

Check logical name

USE <$DB_NAME>;

GO

SELECT FILE_NAME(1) AS ‘File Name 1’, FILE_NAME(2) as ‘File Name 2’;

GO

Clean database transaction log

USE <$DB_NAME>

BACKUP LOG <$DB_NAME> WITH TRUNCATE_ONLY;

DBCC SHRINKFILE (<$LOGICAL_NAME_OF_LOG_FILE>, 1);

Check database size

SELECT D.NAME,

CONVERT(DECIMAL(18,2), (SUM(SIZE)*8) AS ‘DB_SIZE_IN_KB’,

(SELECT CONVERT(DECIMAL(18,2), (SIZE*8)) FROM SYS.SYSALTFILES WHERE DBID = SAF.DBID AND GROUPID = 0) AS ‘LOG_SIZE_IN_KB’

FROM SYS.SYSALTFILES SAF

JOIN SYS.DATABASES D ON SAF.DBID = D.DATABASE_ID

WHERE D.NAME IN (<$DB_NAME>)

GROUP BY DBID, D.NAME

ORDER BY D.NAME

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s