Kenapa sih tempdb bisa penuh?

Artikel ini menjelaskan gejala-gejala dan tanda-tanda untuk semua database yang ukurannya lebih besar dari seharusnya, jadi tidak hanya bisa diaplikasikan untuk tempdb tapi bisa diaplikasikan juga untuk database yang mengalami hal yang serupa.SQL Server mengalokasikan satu database yang disebut dengan tempdb, utamanya digunakan untuk membantu proses pada penggunaan worktable / #temp table. Kadang-kadang, kamu akan menemukan salah satu dari beberapa gejala sebagai berikut:

  • Pesan error pada event log:
    Source: MSSQLSERVER
    Event ID: 17052
    Description: The log file for database ‘tempdb’ is full.
    Back up the transaction log for the database to free up
    some log space
  • Pesan error pada Query Analyzer:
    Server: Msg 8624, Level 16, State 1
    Internal SQL Server erroror

    Server: Msg 1101, Level 17, State 10, Line 1
    Could not allocate new page for database ‘TEMPDB’. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth.

  • Atau kamu menyadari bahwa filenya lebih besar dari seharusnya – dengan menggunakan perintah EXEC sp_spaceused, atau melihat pada taskpad view didalam Enterprise Manager, ataupun melihat file MDF/LDF melalui Windows Explorer, atau bisa juga diperingatkan oleh software monitoring seperti SiteScope atau Quest Spotlight.

PenyebabBiasanya, tempdb diisi ketika kamu dalam keadaan “low on disk space”, atau ketika kamu mensetting suatu ukuran minimal yang tidak masuk akal untuk pertumbuhan suatu database.

Banyak orang berpikir bahwa tempdb hanya digunakan untuk table-table temporary (#temp tables). Kenyataannya, kamu dapat dengan mudah membuat suatu single tabel. Skenario-skenario lagin yang membuat ukuran tempdb bertambah adalah sebagai berikut:

  • proses sorting yang membutuhkan memory melebihi dari ukuran yang sudah dialokasikan SQL Server akan memaksakan perkerjaannya ke tempdb;
  • Jika proses sorting membutuhkan ruang lebih daripada yang telah dialokasikan oleh tempdb, salah satu error diatas akan terjadi;
  • DBCC CheckDB(‘any database’) akan melakukan pekerjaannya pada tempdb — pada database yangberukuran besar, akan memakan resource dan tempat yang cukup banyak;
  • DBCC DBREINDEX atau perintah DBCC yang serupa dengan  ‘Sort in tempdb’ option juga berpotensi membengkakan ukuran tempdb;
  • hasil query yang besar yang melibatkan perintah unions, order by / group by, cartesian joins, outer joins, cursors, temp tables, table variables, dan hashing seringkali juga membutuhkan bantuan dari tempdb;
  • transaksi yang tidak dicommit dan tidak dirolled back dapat meninggalkan objek sendirian(orphaned) didalam tempdb;
  • penggunaan dari ODBC DSN dengan pilihan ‘create temporary stored procedures’ dapat meninggalkan objek disana terus menerus selama ada koneksi(koneksi belum ditutup).

Poin-poin analisa yang lain:
Perintah berikut akan menjelaskan bagaimana ruang untuk tempdb dialokasikan oleh SQL Server:

USE tempdb
GO
EXEC sp_spaceused

Hal berikut seharusnya akan memberikan petunjuk-petunjuk tabel mana yang menggunakan ruang terbeser dalam file-file data — hal ini akam membantu anda untuk mempersempit transaksi yang mengambil waktu yang lama atau berulang-ulang didalam limbo:

USE tempdb
GOSELECT name
FROM tempdb..sysobjects

SELECT OBJECT_NAME(id), rowcnt
FROM tempdb..sysindexes
WHERE OBJECT_NAME(id) LIKE ‘#%’
ORDER BY rowcnt DESC

Semakin tinggi nilai rowcount akan mengindikasikan tabel temporary terbesar yang memakan tempat. Dan hal ini tidak menjelaskan apa-apa selama tempdb digunakan untuk proses internal I/O dan proses-proses lainnya seperti penyortiran data, yang mungkin membantu jika kamu menyederhanakan stored procedure(s) yang menyebabkan growth (kamu bisa meng-query INFORMATION_SCHEMA.ROUTINES untuk  ROUTINE_DEFINITION LIKE ‘%#table_name%’ diatas).

Sebagai tambahan,kamu bisa menggunakan Profiler untuk mengamati events seperti database file auto grow dan log file auto grow. Jika seringkali terjadi, kemudian kamu bisa tahu bahwa tempat yang kamu alokasikan untuk tempdb kurang.

Kamu juga dapat mengamati performance monitor’s counter untuk PhysicalDisk: CurrentDiskQueueLength pada drive dimana terdapat tempdb. Jika angkanya secara konsisten melebihi dari 2, itu berarti ada kemungkinan terjadi  bottleneck pada disk I/O.


Penyelesaian jangka pendek
Me-Restart SQL Server akan membangun tempdb dari awal, dan akan mengembalikannya ke ukuran semula. Solusi ini hanya efektif untuk jangka pendek; asumsikan bahwa aplikasi dan/atau kode T-SQL yang menyebabkan ukuran tempdb bertambah dengan cepat, maka hal ini akan terulang lagi.

Shrink tempdb, kamu dapat mempertimbangkan menggunakan perintah DBCC ShrinkDatabase, DBCC ShrinkFile (untuk data atau log file), atau ALTER DATABASE. Lihat KB #256650, KB #272318 dan KB #307487 untuk informasi lebih lanjut.

Jika kamu dapat men-shrink lognya, ini mungkin bertujuan untuk suatu transaksi yang tidak di-committed . Lihat jika ada transaksi yang berjalan cukup lama dengan command sebagai berikut:

DBCC OPENTRAN — or DBCC OPENTRAN(‘tempdb’)

Cek transaksi paling lama (jika ada), dan lihat siapa SPID (akan ada line yang dimulai dengan ‘SPID (Server Process ID) : <number>’). Gunakan <number> sebagai berikut:

DBCC INPUTBUFFER(<number>)

Hal ini akan menunjukkan porsi perintah SQL terakhir yang dieksekusi oleh SPID tersebut, dan akan membantu anda untuk menentukan apakah akan mengakhiri proses tersebut dengan:

KILL <number>

Pencegahan jangka panjang
Berikut ada beberapa saran untuk memaintain tempdb:

  • Pastikan tempdb di set autogrow — jangan menset ukuran maximum untuk tempdb. Jika drive yang dipakai terlalu penuh untuk membolehkan suatu autogrow events, maka gantilah dengan drive yang lebih besar, atau tambahkan file kedalam tempdb pada device yang lain(gunakan ALTER DATABASE) dan memperkenankan file tersebut autogrow. Kamu memerlukan paling sedikit satu data file dan satu log file untuk menghindari masalah ini menghentikan system anda dimasa datang.
  • Untuk performance yang optimal, pastikan ukuran awalnya memadai untuk menangani typical workload (autogrow events dapat menyebabkan performance menurun dan akan mengalokasikan ruang yang lebih besar). Untuk pendekatan, setting ukuran non-default pada tempdb, lihat saran-saran di http://www.tkdinesh.com/faq/ans/tempdbsh….
  • Jika memungkinkan, letakkan tempdb pada physical disk sendiri, array atau disk subsystem (lihat KB #224071 untuk informasi lebih lanjut).
  • Untuk mencegah tempdb log file growth, pastikan tempdb menggunakan simple recovery mode (ini memungkinkan log di-truncated secara otomatis). Untuk mencek jika ini masalahnya:
    — SQL Server 7.0, should show ‘trunc. log on chkpt.’
    — or ‘recovery=SIMPLE’ as part of status column:EXEC sp_helpdb ‘tempdb’

    — SQL Server 2000, should yield ‘SIMPLE’:

    SELECT DATABASEPROPERTYEX(‘tempdb’, ‘recovery’)

    Jika database tidak di set menggunakan simple recovery, kamu dapat mensetnya sebagai berikut:

    ALTER DATABASE tempdb SET RECOVERY SIMPLE
  • Gunakan SQLOLEDB, jangan ODBC / DSN untuk mengakses database  (untuk VB / ASP, lihat Article #2126 untuk contoh connection strings).
  • Coba pastikan anda mengcover indexes untuk semua table yang besar yang digunakan pada query yang tidak dapat menggunakan clustered index / index seek.
  • Batch larger heavily-logged operations (terutama deletes) yang mungkin menyebabkan  overflow pada tempdb kedalam potongan-potongan baris yang lebih masuk akal, terutama jika menggunakan perintah joins.
  • Teliti kode anda untuk mencari transaksi-transaksi yang potential tidak dicommit.
  • Secara umum, coba buat kode anda seefisien mungkin, hindari cursor, loop bersarang dan temporary tables jika memungkinkan. Lihat Article #2424 untuk ide-ide efisiensi.
  • Cek  WebCast pada KB #834846 untuk ide-ide tentang administering dan maintaining TempDB.
Diambil dan ditranslasi dari :

~ oleh Wulan pada Agustus 26, 2008.

5 Tanggapan to “Kenapa sih tempdb bisa penuh?”

  1. Wah, komplit plit plit..
    Hebat…well done!!

  2. terima kasih Bud, tyt kejadian diserverku kemaren gara-gara ada proses ftp yang ngeberat2in beban servernya. pantes aja jadi sering low disk space on C hiks hiks hiks ……

  3. betul.. solusi ngegedein harddisk kayanya ga ngebantu deh.. soalnya tempdb itu bakal ngegede terus kalo gak dimaintain.

  4. ayoo..buat gravatar,bpk-ibu… fotonya ganteng kan :D—->

  5. gravatar apaaannnnn tuh Ded?????
    yupi fotonya nggguannnntennng 😀

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

 
%d blogger menyukai ini: