SQLServer In Recovery, Selanjutnya Ngapain ?

jadi cerita ~~kelam~~ nya begini ,

udah buat Stored Procedure (SP) yang akan mengkalkulasi objek pada sekitar 2.400.000 baris.

untuk mempercepat, maka SP tersebut dijalankan secara multi proses dengan bantuan script .bat sederhana. script ini membuat sebanyak 70 proses terpisah yang memanggil SP tersebut dengan parameter yang berbeda antara proses.

script di jalankan, menjelang sekitar kurang dari 1 jam , baru nyadar sih kalo SP nya ada yang kurang. wal hasil harus kita off dulu tuh semua proses. Biasanya saya sih cara mematikan service SQL Server nya, namun entah karena ~~streess , kalap, atau~~ sedang capek, maka saya off semua proses .bat yang telah tercreate tadi, dan saya matikan service SQL Server nya, ya cuman kebalik aja urutannya.

Dan yang terjadi adalah sesuatu yang tak terduga…, dimulai dari peringatan di windows bahwa ada kegagalan proses transaksi di SQLServer, lalu dilanjutkan dengan database yang berada pada state in recovery (ilustrasi lihat gambar di atas ye).

then saya mencoba untuk tetap ~~ganteng da~~n tenang , mengingat sebenarnya sudah ada proteksi dalam hal – hal seperti ini oleh SQL Servernya seperti penggunaan konsep Write Ahead Log (WAL) , dan most likely disk saya sih baik2 aja sehingga minim resiko, dan mengingat pula bahwa ukuran db saya ini gak besar2 amat, cuman skitar 15 Giga aja, saya yakin sekali abang SQL Server bisa menyelesaikan masalah ini dengan sendirinya.

tapi, selama db ini dalam state in recovery, saya nganggur nya luar biasa nih, iseng – iseng , nyari di internet bagaimana sih cara kita tahu progress dari in recovery nya, saya nemu ada beberapa cara :

Melihat log dari SQLServer

cara ini, cara yang paling mudah , yaitu menggunakan SSMS anda, lalu klik klik next aja wkwkw. (lihat gambar ya, males njelasin), setelah nanti window log akan muncul dan dapat dilihat keterangan berapa persen progress dari in recovery ini

Menggunakan script 1

karena kurang puas ngeliat log aja, bahasa gaulnya sih kurang keren. nyari – nyari lah script untuk liat ini itu.. hehe akhirnya nemu juga

DECLARE @DBName VARCHAR(64) = 'viplus_general' DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX)) INSERT INTO @ErrorLog EXEC master..sp_readerrorlog 0, 1, 'Recovery of database', @DBName SELECT TOP 5 [LogDate] ,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete ,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining,[TEXT] FROM @ErrorLog ORDER BY [LogDate] DESC

script ini menampilkan juga informasi tambahan dari step yang ada di in recovery, namun juga gak detil2 amat tapi ini fave saya sih.

Menggunakan script 2

kalau menggunakan script 1 masih dirasa kurang detil, terutama bagi agan – agan yang punya trust issue bisa nyoba query ini

DECLARE @ErrorLog AS TABLE([LogDate] DateTime, [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX)) INSERT INTO @ErrorLog EXEC sys.xp_readerrorlog 0, 1, 'Recovery of database' SELECT DB_NAME(dt.database_id) AS DBName,GETDATE() as currenttime, at.transaction_begin_time ,dt.transaction_id,at.name AS TranName ,cx.PercentComplete,cx.MinutesRemaining ,d.log_reuse_wait_desc ,database_transaction_log_record_count, database_transaction_log_bytes_used , database_transaction_next_undo_lsn ,CASE at.transaction_state WHEN 0 THEN 'Not Completely Initialized' WHEN 1 THEN 'Initialized but Not Started' WHEN 2 THEN 'Transaction is Active' WHEN 3 THEN 'Read-Only tran has Ended' WHEN 4 THEN 'Distributed Tran commit process has been initiated' WHEN 5 THEN 'In prepared state and waiting resolution' WHEN 6 THEN 'Transaction has been committed' WHEN 7 THEN 'Transaction is being rolled back' WHEN 8 THEN 'Transaction has been rolled back' END AS TranState FROM sys.dm_tran_database_transactions dt LEFT OUTER JOIN sys.dm_tran_active_transactions at ON dt.transaction_id = at.transaction_id INNER JOIN master.sys.databases d ON d.database_id = dt.database_id Cross Apply (SELECT TOP 1 [LogDate] ,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete ,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining ,db_name(SUBSTRING([TEXT], CHARINDEX('(', [TEXT]) + 1,CHARINDEX(')', [TEXT]) - CHARINDEX('(', [TEXT]) - 1) ) as DBName ,Cast(SUBSTRING([TEXT], CHARINDEX('(', [TEXT]) + 1,CHARINDEX(')', [TEXT]) - CHARINDEX('(', [TEXT]) - 1) as Int) as DBID FROM @ErrorLog ORDER BY [LogDate] DESC) cx WHERE d.state_desc <> 'online' And cx.dbid = dt.database_id

* yaaay, akhirnya update blog, Happy recovering.. 😀

menemukan query sebenarnya dibalik “FETCH API CURSORXXX”

Lagi asik memantau database, eh nemu yang aneh

query yang paling banyak habisin resource terdeteksi, namun setelah dilhat kok querynya cuman FETCH API CURSORxxxxxxx ,

gimana mau diperbaiki ?

untuk mencari tahu apa saja query yang sedang berjalan yang bertipe FETCH API CURSOR ini dapat menggunakan query template dari om Dave berikut

SELECT creation_time, cursor_id, c.session_id, c.properties, c.creation_time, c.is_open, SUBSTRING(st.TEXT, ( c.statement_start_offset / 2) + 1, ( ( CASE c.statement_end_offset WHEN -1 THEN DATALENGTH(st.TEXT) ELSE c.statement_end_offset END - c.statement_start_offset) / 2) + 1) AS statement_text FROM sys.Dm_exec_cursors(0) AS c JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id CROSS apply sys.Dm_exec_sql_text(c.sql_handle) AS st GO

cara mudah bulk restore transaction log backup sql server

untuk yang kesusahan dalam merestore transaction log backup sql server

dikarenakan banyaknya data log yang akan direstore

dapat menggunakan script saya dibawah ini :

untuk menjalankannya membutuhkan php terinstall pada komputer anda

jalankan di command line anda

php -f gen_trans_log_restore.php {dir tempat log file} {nama db} > hasil_query_anda.sql

psst .. jangan lupa membaca asumsi saya di script ya…

Menemukan Query yang dieksekusi

sedang asik asik “nguery”

ketik ketik ~~cantik panjang~~, tekan f5 di SQL Server Management Studio (SSMS) ,

copy hasil ke excel

lalu klik close window di SSMS , wew lupa save query panjang itu !!

jangan keburu panik ~~dan bunuh diri…~~

kembali ke SSMS anda, dan jalankan query :

SELECT dest.TEXT AS [Query], deqs.execution_count [Count], deqs.last_execution_time AS [Time] FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest ORDER BY deqs.last_execution_time DESC

alhasil query kita masih dapat dikembalikan lagi ..
wew leganya…

Menghapus semua FK constraint pada tabel innodb di mysql

ternyata banyak juga ya yang belum bisa move on!

hehe , dari postingan saya sebelumnya, ternyata banyak yang masih cinta sama MyISAM !..

tapi setelah mengikuti contoh, banyak yang gagal, kenapa ?

tentu karena adanya constraint foreign key dari InnoDB yang telah terinstall

untuk itu untuk membereskannya, harus di hapus semua dulu foreign key constraint yang sudah kadung melekat ~~di dalam dada.~~.

caranya tentu dengan menghapus constraint satu per satu, namun karena kita pintar ~~dan malas~~, tentu kita pakai script saja untuk mengenerate script menghapus tersebut

berikut scriptnya

select concat('alter table ',table_schema,'.',table_name,' DROP FOREIGN KEY ',constraint_name,';') from information_schema.table_constraints where constraint_type='FOREIGN KEY'

yai… dengan script ini maka semua rintangan halangan dan constraint foreign key akan terhapus juga..

Merubah semua engine table pada mysql

bagi yang menggunakan mysql, secara paham atau tidak,
suka atau tidak , sadar atau tidak pasti akan menggunakan table engine yang tersedia di mysql

jaman dulu sih biasanya menggunakan MyISAM sebagai default table engine nya, sedangkan yang kekinian menggunakn INNODB

anggap saja saya orang yang susah move on, MyISAM tetap jadi primadona… dengan segala kekurangannya.

nah karena sekarang setiap membuat table otomatis INNODB, maka sedikit banyak mengganggu, apalagi kalo buat tabel yang banyak sekali sampe lupa ngecek.. ~~jadi benalu dalam palung hati yang terluka ini.~~

untuk merubahnya menjadi myISAM bisa kita lakukan dengan merubah satu demi satu tabel, tapi apa daya, rasa malas ini mengalahkan daya juang api dingin ini… , solusinya adalah mengenerate script yang akan merubah tipe engine ini.
script tersebut adalah :

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=MyISAM;') FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'DBNAME' AND ENGINE = 'InnoDB' AND TABLE_TYPE = 'BASE TABLE'

yayy!!.. semoga membantu orang2 malas yang lain…

happy sql ing

btw , tentu script ini juga bisa membalikkan keadaan dari myISAM ke INNODB dll 😀

Menampilkan Informasi Detail PHP dan cURL

untuk yang suka pakai PHP dan cURL ,
seringkali saat terjadi trouble kita akan kesusahan untuk mendebug nya,

untuk itu, langkah pertama dari semua proses debugging itu dimulai dari menampilkan seluruh aktivitas dari cURL

hal itu dapat dilakukan dengan script di bawah :

yang perlu diperhatikan untuk ditambahkan pada kode anda adalah bagian didalam if($debug) dan if($result === FALSE && $debug) ..

happy coding

Hal Apa Saja Yang Perlu di monitor pada SQL Server Anda ? – SQL Server counters –

Setelah SQLServer terinstall diproduction terkadang kita kebingungan apa saja yang perlu kita perhatikan, berikut beberapa hal yang perlu diperhatikan pada env SQL Server kita :

General Statistics : Objek ini memonitor aktivitas umum pada server seperti jumlah koneksi user yang sekarang sedang terkoneksi dan jumlah user yang terkonek dan diskonek per detiknya. Biasanya jika terlihat jumlah user yang terkonek 500 kali lipat dari biasanya, maka waspadai penurunan dari performa sistem.
Database : Objek ini memonitor hal – hal yang terjadi pada database, seperti operasi bulk copy, backup dan restore , dan aktivitas transaction log. Hal yang penting untuk dilihat adalah ukuran jumlah transaksi per detik, dengan melihat ini , kita dapat mengukur kemampuan server kita dalam menghadapi banyak transaksi sekaligus.
Locks : Objek ini sangat penting dimonitor karena menggambarkan terjadinya kondisi yang dapat memperlambat kinerja sistem. beberapa jenis lock yang perlu diperhatikan antara lain :

(1) . Average Wait Time (ms) : rata – rata waktu tunggu dalam masa lock pada setiap transaksi
(2) . Lock Wait Time (ms) : Wait Time terakhir yang tercatat
(3) . Lock Wait per Seconds : Jumlah Lock per detik yang tidak dapat diselesaikan secepatnya
(4) . Number of Deadlock per Seconds : Jumlah Deadlock yang terjadi per detik

Latch : Dalam melakukan Lock, SQL Server membutuhkan objek lain untuk mengontrol lock tersebut, hal itu disebut Latch . beberapa hal yang penting untuk dilihat adalah :

(1). Average Latch Wait Time (ms) : rata – rata Latch Wait Time
(2). Latch Wait / Sec : Lama Latch Time per detik pada Latch yang tidak dapat diselesaikan langsung.

5. SQL Statistics : Untuk mengetahui hal – hal yang berkaitan dengan SQL, biasanya dapat dilhat dari SQL Compliation per detik dan SQL Re Compilation per detik.

Buffer Manager : Untuk melihat penggunaan memory dari sqlserver , biasanya melihat Buffer cache Hit Ratio yang dapat melihat sebearapa banyak sqlserver menggunakan memory tanpa mencari dari disk. Hal ini sangat berpengaruh pada performa sqlserver.
Plan Cache : Melihat seberapa efektif sqlserver menggunakan memory pada stored procedure , TSQL, Triggers. Hal ini dapat dilihat pada Cache Hit Ratio.

untuk lebih detail dapat anda lihat di sini

Mengaktifkan Page Compression pada Semua Tabel

Untuk data yang sangat besar, database bisa memanfaatkan fitur kompresi pada sqlserver untuk diaktifkan, hal itu berguna untuk mengurangi space dan meningkatkan performa (mengurangi IO cost).

bentuk – bentuk kompresi antara lain :

Page Compression
Row Compression

Banyak sumber mengatakan bahwa Page Compression lebih memberikan efek pada kompresi maupun pada performa.

sedangkan untuk object database yang dapat dilakukan kompresi adalah :

Tabel
Index

Untuk merubah satu buah tabel cukup mudah yaitu dengan cara :

ALTER TABLE {NAMA TABEL} REBUILD WITH (DATA_COMPRESSION=PAGE);

sedangkan untuk index seperti :

ALTER INDEX {NAMA INDEX} ON {NAMA TABEL} REBUILD WITH (DATA_COMPRESSION=PAGE);

namun masalah muncul saat kita sudah memiliki banyak tabel dan banyak index, tentu sangat melelahkan untuk melakukan script satu demi satu. Solusinya saya menemukan script yang mengenerate script untuk melakukan pengaktifan kompresi pada semua tabel dan pada semua index.

Untuk Semua Tabel :

SELECT 'ALTER TABLE ' + '[' + s.[name] + ']'+'.' + '[' + o.[name] + ']' + ' REBUILD WITH (DATA_COMPRESSION=PAGE);'
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.schemas AS s WITH (NOLOCK)
ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.dm_db_partition_stats AS ps WITH (NOLOCK)
ON i.[object_id] = ps.[object_id]
AND ps.[index_id] = i.[index_id]
WHERE o.[type] = 'U'
ORDER BY ps.[reserved_page_count]

Untuk Semua Index :

SELECT 'ALTER INDEX '+ '[' + i.[name] + ']' + ' ON ' + '[' + s.[name] + ']' + '.' + '[' + o.[name] + ']' + ' REBUILD WITH (DATA_COMPRESSION=PAGE);'
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.schemas s WITH (NOLOCK)
ON o.[schema_id] = s.[schema_id]
INNER JOIN sys.dm_db_partition_stats AS ps WITH (NOLOCK)
ON i.[object_id] = ps.[object_id]
AND ps.[index_id] = i.[index_id]
WHERE o.type = 'U' AND i.[index_id] &gt;0
ORDER BY ps.[reserved_page_count]

kedua script tersebut, tinggal dijalankan pada SQLServer Management Studio kesayangan anda, dan voilla! script yang didambakan akan muncul 😀

ok, semoga bermanfaat

Ringkasan Macam Bentuk Backup pada Oracle Database

Proses Backup salah satu proses yang penting pada Sistem Basis Data. Beribu alasan dapat dikemukakan mengapa backup ini sangat penting mulai dari sekedar memindahkan data sampai mengembalikan data yang hilang akibat kelalaian personal ataupun kesalahan sistem.

kali ini mari kita lihat opsi proses backup apa saja yang ada di salah satu database tersohor di dunia, yaitu Oracle . Opsi – opsi tersebut adalah :

Export

Opsi ini merupakan opsi logikal dari proses backup. Proses ini akan melakukan “copy” database object yang akan dilakukan export ke dalam bentuk propietary binary. Opsi ini cocok untuk proses backup yang dilakukan pada target objek database spesifik, misalnya pada satu atau beberapa tabel saja, walaupun opsi ini juga bisa dilakukan untuk keseluruhan objek dari database. Model ini sangat mudah untuk dilakukan , sehingga banyak pengguna oracle menggunakan metode ini untuk melakukan backup.

Salah satu kekurangan dari model ini adalah adanya kemungkinan terjadi ketidakkonsistenan dari hasil backup, terutama jika tabel atau objek database yang dilakukan backup tidak mencantumkan table yang berelasi secara schema (foreign key constraint) maka akan ditemukan kesulitan saat melakukan restore karena pengencekan foreign key constraint tersebut. Model ini juga model backup yang termasuk lamban, dan tidak cocok untuk database dengan ukuran yang besar, beberapa pengguna oracle mengatakan ukuran yang besar adalah > 50GB.

Contoh penggunaan :

exp scott/tiger file=emp.dmp log=emp.log tables=emp rows=yes indexes=no exp scott/tiger file=emp.dmp tables=(emp,dept)

Data Pump (Export)

Model backup ini merupakan upgrade dari model sebelumnya. Model ini muncul pertama kali di database oracle 10G.

contoh penggunan

SQL> CREATE DIRECTORY dmpdir AS '/opt/oracle'; Directory created.

SQL> GRANT read, write ON DIRECTORY dmpdir TO scott; Grant succeeded.

$ expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

Cold / Offline Backup

prinsipnya, cold backup ini dilakukan dengan cara mematikan database , lalu melakukan copy file database seperti *.dbf dan *.ora

Hot / Online Backup

hot backup ini merupakan kebalikan dari cold backup, dengan teknik teknik khusus , file database dapat dicopy walaupun database masih dalam kondisi online.

RMAN Backup

rman adalah utilitas khusus yang dibangun oleh oracle untuk kebutuhan backup dan restore database dengan lebih cepat dan lebih effisien, mendukung fitur – fitur khusus seperti incremental backup dan lain sebagainya.

catatan penting : konfigurasi penting dari database oracle adalah mode archivelog, konfigurasi ini menentukan jenis backup apa saja yang dapat dilakukan.