Tag: sqlserver

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

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…

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 :

  1. 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.

  2. 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.

  3. 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

  1. 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.

  1. 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.

  2. 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 :

  1. Page Compression
  2. 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 :

  1. Tabel
  2. 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

Cara Cepat Menampilkan History Database Size

Seringkali, kita diminta secara mendadak untuk menampilkan history ukuran database dari waktu ke waktu,

idealnya kita harus menghitung ukuran per baris dari data yang ada. namun terdapat juga beberapa query yang dapat membantu kita tanpa melakukan hal tersebut , beberapa yang berhasil saya kumpulkan antara lain :

{ ORACLE } :


select to_char(creation_time, 'MM-RRRR') "Month", sum(bytes)/1024/1024/1024 "Growth in GB"
from sys.v_$datafile
where to_char(creation_time,'RRRR')='2014'
group by to_char(creation_time, 'MM-RRRR')
order by to_char(creation_time, 'MM-RRRR');

dengan query ini, kita akan mendapatkan ukuran database dari waktu ke waktu memanfaatkan v_$datafile , yang mana tentu syaratnya database anda belum di restart. jika sudah direstart maka v_$datafile pun akan kehilangan catatannya.

{SQL SERVER} :


SELECT
[database_name] AS "Database",
DATEPART(month,[backup_start_date]) AS "Month",
AVG([backup_size]/1024/1024) AS "Backup Size MB",
AVG([compressed_backup_size]/1024/1024) AS "Compressed Backup Size MB",
AVG([backup_size]/[compressed_backup_size]) AS "Compression Ratio"
FROM msdb.dbo.backupset
WHERE [database_name] = N'AdventureWorks'
AND [type] = 'D'
GROUP BY [database_name],DATEPART(mm,[backup_start_date]);

dengan query ini , kita memanfaatkan pencatatan backup yang dilakukan oleh SQL SERVER, tentunya ini hanya berlaku untuk database yang rutin di backup , jika tidak rutin ya wassalam.

ok sekian selamat berhitung

SQLServer Import Excel 2007 Error

setelah sekian lama, akhirnya saya mendapatkan error legendaris ini

Picture1

error ini muncul saat ingin mengimport excel 2017 ke sqlserver melalui SQLServer Import and Export Wizard

solusi yang dapat dilakukan adalah :

  1. menginstall https://www.microsoft.com/en-us/download/details.aspx?id=13255 , saya menggunakan yang versi 64 bit
  2. jika point 1 tidak berhasil , maka gunakan https://www.microsoft.com/en-us/download/confirmation.aspx?id=23734

saya pribadi banyak bisa setelah point 2 saya install saya baru berhasil menjalankan import excel 2017 dengan lancar sekali…

semoga membantu,

happy querying :p

Akses user SqlServer pada linked server

jika anda pernah mengalami kasus seperti :

image_thumb26

maka user yang anda pakai belum diberi grant untuk mengakses linked server ,

untuk mengatasi hal ini, diperlukan hanya 2 langkah saja :

Tambahkan user anda pada database master

USE [master]
GO
CREATE USER user_login_anda FOR LOGIN user_login_anda;

Tambahkan hak mengeksekusi linked server pada user

USE [master]
GO
GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO user_login_anda;

*jika anda menggunakan domain user maka user_login_anda menjadi [DOMAIN\user_login_anda]

ok itu saja, semoga banyak membantu 😀