Reduce LOG in SQL Server 2000

Kecilkan SQL Server Log File

(Digunakan di SQL Server 7, dan jalan juga di SQL Server 2000)

Kita bisa pinjam TSql syntax : DBCC SHRINKFILE dan BACKUP LOG do not reduce the log file's size.

Run: DBCC LOGINFO (database name) dan cek last entry's harus dengan status 2, lalu akhir dari transaction log adalah active portion. Ini akan berfek ke mengecilkan file log.

Nah ini caranya :

Langkah 1

run: DBCC SHRINKFILE (logfile, TRUNCATEONLY )
run: BACKUP LOG database WITH TRUNCATE_ONLY

Langkah 2

Create a dummy table and insert a record to MyTable

CREATE TABLE MyTable (MyField VARCHAR(10), PK INT )
INSERT Mytable (PK) VALUES (1)
GO

Langkah 3

Create and run the following script:

(SET NOCOUNT ON
DECLARE @Index INT
SELECT @Index = 0
WHILE (@Index < 20000)
BEGIN
  UPDATE MyTable SET MyField = MyField WHERE PK = 1
  SELECT @Index = @Index + 1
END
SET NOCOUNT OFF
)

Langkah 4  jalankan sekali lagi
DBCC SHRINKFILE (logfile, truncateonly ) 
BACKUP LOG database name WITH TRUNCATE_ONLY
  file log kudunya sudah lebih kecil :-).

stay watch

Phasellus facilisis convallis metus, ut imperdiet augue auctor nec. Duis at velit id augue lobortis porta. Sed varius, enim accumsan aliquam tincidunt, tortor urna vulputate quam, eget finibus urna est in augue.