Author Archives: radityopw

Mengetahui History Restore Database pada SQL Server

bagi yang kadang bingung, “eh ini db udah di restore gak ? “, “waduh tadi aku ngerestore db apa ya ? ” . daripada tetap bingung, mending cek langsung aja pakai query ini 

SELECT [r].[destination_database_name] as [Database Name], 
[r].[restore_date] [Restore Date], 
[r].user_name as [User],
(cast([b].software_major_version as varchar(10))+'.' + 
cast([b].software_minor_version as varchar(10)) +'.'+
cast([b].software_build_version as varchar(10))) as [Server version],
[b].compressed_backup_size as [Backup file Size],
[b].[backup_start_date] [Backup Start Time], 
[b].[backup_finish_date][Backup End Time], 
[b].[database_name] as [Source Database Name], 
[bkpmf].[physical_device_name] as [Backup File Used For Database Restore]
FROM msdb..restorehistory r
INNER JOIN msdb..backupset b
ON [r].[backup_set_id] = [b].[backup_set_id]
INNER JOIN msdb..backupmediafamily bkpmf 
ON [b].[media_set_id] = [bkpmf].[media_set_id] 
ORDER BY [r].[restore_date] DESC

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 dan 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