Selasa, o3 Des 2009
ShrinkFile SQLServer

Maintenance untuk mengecilkan ukuran file (file size) suatu database ini dalam SQLServer dikenal sebagai ShrinkFile.
Setiap SQLServer database, terdiri dari dua jenis data file. Yaitu
1. Data file (Mdf File), sebagai media penyimpan data
2. Log File (Ldf File), sebagai media penyimpan histori transaksi data

Penyimpanan Data ke Data File dan Log File
Ketika suatu data disimpan ke dalam database SQLServer, SQLServer menulis data ke Data File dan menuliskan histori data ke Log File. Data tersebut disimpan secara fisik dituliskan oleh SQLServer ke bagian terakhir dari data file dan log file yang akan menyebabkan file size menjadi lebih besar.
Sebaliknya, ketika suatu data dihapus dari database SQLServer, SQLServer akan menghilangkan data dari Data File, dan kembali menuliskan histori data ke Log File. Secara fisik, SQLServer menghilangkan data dari Data File, tetapi tanpa mengurangi data file size.
Ilustrasi :
Kita dapat membayangkan bahwa data file seolah-olah merupakan sebuah dinding yang sedang dibangun. Maka setiap penambahan batubata akan menambah luas dinding tersebut bukan? Tetapi jika bagian batu bata yang terletak di tengah dinding dihilangkan, luas dinding secara keseluruhan tetap tidak akan berubah.
Untuk Log File, kita dapat membayangkan bahwa setiap transaksi di suatu bank, akan didokumentasikan dalam kertas-kertas transaksi, misalnya bukti setor, bukti transfer, bukti pengambilan uang dan sebagainya. Dimana semua bukti tersebut baik penambahan dana dan pengurangan dana akan meningkatkan jumlah dokumen transaksi.
Perintah shrink dalam SQLServer, secara umum berfungsi untuk mengecilkan file size dari data file (Mdf file) dan log file (log file), tetapi dengan metode yang berbeda.
Terhadap Data File, ShrinkFile akan mengatur ulang penulisan data, sehingga setiap tempat yang sudah ditinggalkan data yang terhapus dapat diisi oleh data yang terletak diujung file. Sehingga dengan demikian file size dapat menjadi lebih kecil.
Ilustrasi :
Dengan ilustrasi dinding yang berlubang diatas, jika batubata yang terletak di bagian paling atas dipindahkan untuk mengisi bagian dinding yang berlubang, maka secara keseluruhan luas dinding tidak akan berkurang.
Terhadap Log File, ShrinkFile akan menghilangkan semua jejak histori transaksi yang tersimpan di dalam log file. Jika AutoShrink dalam database diset=False, maka menjadi syarat mutlak bahwa log file harus dibackup terlebih dahulu sebelum dapat dishrinkfile.
Ilustrasi :
Dengan ilustrasi kegiatan transaksi perbankan, jika suatu file kabinet sudah dipenuhi oleh dokumen transaksi, maka untuk mengosongkan file kabinet tersebut, maka semua dokumen transaksi harus dipindahkan ke suatu tempat lain.

SINTAX
Dalam contoh dibawah, database yang digunakan adalah BeNS
USE BeNS
*ShrinkFile Data File — DBCC SHRINKFILE(Nama DB SQL,5)
DBCC SHRINKFILE(BeNS,5)
— perhatikan angka 5 adalah ukuran file baru yg diinginkan, jika angka ini lebih kecil dari initial size, maka initial size database akan digunakan dan angka 5 ini diabaikan. Demikian juga bila angka ini akan diabaikan jika lebih kecil dari ukuran minimum data file ketika proses shrinkfile selesai.

*ShrinkFile Log File — DBCC SHRINKFILE(Nama DB SQL, 1)
BACKUP LOG BeNS WITH TRUNCATE_ONLY
DBCC SHRINKFILE(BeNS_log, 1)
–-perhatikan angka 1 adalah ukuran file baru yg diinginkan

DECLARE @TableName sysname
DECLARE @indid int
DECLARE cur_tblfetch CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type = ‘base table’
OPEN cur_tblfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE cur_indfetch CURSOR FOR
SELECT indid FROM sysindexes WHERE id = OBJECT_ID (@TableName) and keycnt > 0
OPEN cur_indfetch
FETCH NEXT FROM cur_indfetch INTO @indid
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT ‘Derfagmenting index_id = ‘ + convert(char(3), @indid) + ‘of the ‘ + rtrim(@TableName) + ‘ table’
IF @indid 255 DBCC INDEXDEFRAG (0, @TableName, @indid)
FETCH NEXT FROM cur_indfetch INTO @indid
END
CLOSE cur_indfetch
DEALLOCATE cur_indfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
END
CLOSE cur_tblfetch
DEALLOCATE cur_tblfetch