Ada kalanya kita hanya mempunyai mdf file dari suatu database, tapi tidak ada log filenya. Padahal tanpa log file ini, mdf file tidak akan bisa di-attach ke SQL Server. Penyebabnya bisa karena log filenya corrupt ato mungkin terhapus secara tidak sengaja. Agar mdf file ini bisa di-attach ke SQL Server, salah satu caranya bisa dengan langkah-langkah berikut:
1.
Misal, nama database yang akan di-attach adalah
testDB, dengan nama file testDB.mdf
2.
Buat database baru dengan nama
testDB1.
3.
Matikan SQL Server service. Bisa melalui command
prompt atau klik kanan server di SQL Server Management Studio dan pilih “Stop”
4.
Hapus file MDF (dan NDF) dari database
testDB1.
5.
Copy file testDB.mdf ke folder dari
testDB1.mdf, dan ganti namanya
menjadi testDB1.mdf.
6.
Hidupkan SQL Server service. Bisa melalui command
prompt atau klik kanan server di SQL Server Management Studio dan pilih “Start”.
7.
Kondisi saat ini, testDB1 database sudah mengarah
ke mdf file yg benar tapi ldf file-nya belum tersambung.
8.
Buka jendela query baru dan ubah database testDB1
ke emergency mode
ALTER
DATABASE testDB1 SET
EMERGENCY
9.
Ubah database ke single user mode
ALTER
DATABASE testDB1 SET
SINGLE_USER WITH
NO_WAIT
10.
Jalankan dbcc checkdb
DBCC CHECKDB
('testDB1',repair_allow_data_loss)
Langkah ini
bervariasi mulai hitungan menit hingga hitungan jam. Saya coba tes dengan data
mdf sebesar 85 GB, proses ini memakan waktu 10 menit. Selama proses berlangsung
akan terlihat banyak error message yang muncul serta pesan bahwa proses generate
ldf dan restore data sedang
berlangsung.
11.
Ubah database kembali ke multi user mode
ALTER
DATABASE testDB1 SET
MULTI_USER WITH
NO_WAIT
12.
Ubah database online
ALTER
DATABASE testDB1 SET
ONLINE
13.
Saat ini Anda sudah mempunyai database testDB1
yang benar. Tapi SQL Server masih menyimpan referensi ke database lama (testDB).
Untuk mengatasi hal ini, detach database testDB1. Kemudian attach lagi dengan
memilih mdf file yg benar (testDB1.mdf)
14.
Jreng..jreng…. Database lama Anda (testDB.mdf)
sudah berhasil di-attach ke SQL Server dengan nama testDB1 database.
Sekedar berbagi query dari seorang teman. Query ini digunakan untuk
mengetahui status stored-procedure maupun proses yang sedang berjalan.
Jika ingin mengetahui status semua proses:
* EXEC uSP_UTIL_DISPLAY_LOCK
Jika ingin mengetahui status proses tertentu:
* EXEC uSP_UTIL_DISPLAY_LOCK @processID
----------------------------------------------------------------
CREATE proc [dbo].[uSP_UTIL_DISPLAY_LOCK]
@SPID int = null
as
set nocount on
create table
#NTPROCESS(
spid int,
dbid int,
obid int,
spname varchar(200) )
create table #use_object
(
resource_type
varchar(40),
resource_associated_entity_id bigint,
database_name
varchar(60),
object_nm
varchar(100),
objidn
bigint
)
insert #NTPROCESS
select session_id ,database_id,
(
select objectid from
sys.dm_exec_sql_text ( r.sql_handle )
),null From
sys.dm_exec_requests r with(nolock)
where session_id in
(select distinct session_id from master.sys.dm_exec_sessions with(nolock))
update #NTPROCESS
set spname = isnull('['+object_name(obid,dbid)
+ ']:','' )
if @SPID is null
begin
print 'Session yang kena block'
select a.session_id,
a.blocking_session_id, a.start_time, a.status, a.command, db_name(a.database_id)
as DBNAME, cast(host_name as varchar(30)) as HOST,
c.spname+b.program_name as program_name,
cast(a.total_elapsed_time/1000.000 as
varchar(30)) + ' Sec' as total_time, a.row_count
from master.sys.dm_exec_requests a with(nolock)
right join master.sys.dm_exec_sessions b with(nolock) on a.session_id =
b.session_id
join #NTPROCESS c
with(nolock) on b.session_id = c.spid
where a.blocking_session_id <> 0 and
b.status not in ('sleeping', 'dormant')
and a.session_id <> @@spid
print 'list pelakunya adalah: '
select a.session_id,
a.blocking_session_id, a.start_time, a.status, a.command, db_name(a.database_id)
as DBNAME, cast(host_name as varchar(30)) as HOST,
c.spname+b.program_name as program_name,
cast(a.total_elapsed_time/1000.000 as
varchar(30)) + ' Sec' as total_time, a.row_count
from master.sys.dm_exec_requests a
with(nolock) right join master.sys.dm_exec_sessions b
with(nolock) on a.session_id = b.session_id
join #NTPROCESS c
with(nolock) on b.session_id = c.spid
where a.session_id in (select
a.blocking_session_id from master.sys.dm_exec_requests a
with(nolock) right join master.sys.dm_exec_sessions b
with(nolock) on a.session_id = b.session_id
where a.blocking_session_id <> 0 and
b.status not in ('sleeping', 'dormant'))
and a.session_id <> @@spid
print 'Session'
select b.session_id, cast(b.login_name as
varchar(30)) as LOGIN, a.status, a.command, c.spname+b.program_name as
program_name,
cast(db_name(a.database_id) as varchar(30))
as DBNAME, cast(host_name as varchar(30)) as HOST,
cast(a.total_elapsed_time/1000.000 as
varchar(30)) + ' Sec' as total_time, a.row_count, a.start_time
from master.sys.dm_exec_requests a
with(nolock) right join master.sys.dm_exec_sessions b
with(nolock) on a.session_id = b.session_id
join #NTPROCESS c
with(nolock) on b.session_id = c.spid
where --a.blocking_session_id = 0 and
host_name is not null
and a.status not in ('sleeping',
'dormant')
and a.session_id <> @@spid
end
else
begin
declare
@db_name
varchar(40),
@cCommandUpdate
nvarchar(1000)
print 'Session yang kena block'
select a.session_id,
a.blocking_session_id, a.start_time, a.status, a.command,
c.spname+b.program_name,
db_name(a.database_id) as DBNAME, cast(host_name
as varchar(30)) as HOST,
cast(a.total_elapsed_time/1000.000 as
varchar(30)) + ' Sec' as total_time, a.row_count
from master.sys.dm_exec_requests a
with(nolock) right join master.sys.dm_exec_sessions b
with(nolock) on a.session_id = b.session_id
join #NTPROCESS c
with(nolock) on b.session_id = c.spid
where a.blocking_session_id <> 0 and
b.status not in ('sleeping', 'dormant')
and a.session_id = @SPID
print 'list pelakunya adalah: '
select a.session_id,
a.blocking_session_id, a.start_time, a.status, a.command,
c.spname+b.program_name,
db_name(a.database_id) as DBNAME, cast(host_name
as varchar(30)) as HOST,
cast(a.total_elapsed_time/1000.000 as
varchar(30)) + ' Sec' as total_time, a.row_count
from master.sys.dm_exec_requests a
with(nolock) right join master.sys.dm_exec_sessions b
with(nolock) on a.session_id = b.session_id
join #NTPROCESS c
with(nolock) on b.session_id = c.spid
where a.session_id in (select
a.blocking_session_id from master.sys.dm_exec_requests a
with(nolock) right join master.sys.dm_exec_sessions b
with(nolock) on a.session_id = b.session_id
where a.blocking_session_id <> 0 and
b.status not in ('sleeping', 'dormant') and a.session_id = @SPID)
print 'Session'
select b.session_id, cast(b.login_name as
varchar(30)) as LOGIN, a.status, a.command, c.spname+b.program_name,
cast(db_name(a.database_id) as varchar(30))
as DBNAME, cast(host_name as varchar(30)) as HOST,
cast(a.total_elapsed_time/1000.000 as
varchar(30)) + ' Sec' as total_time, a.row_count, a.start_time
from master.sys.dm_exec_requests a
with(nolock) right join master.sys.dm_exec_sessions b
with(nolock) on a.session_id = b.session_id
join #NTPROCESS c
with(nolock) on b.session_id = c.spid
where --a.blocking_session_id = 0 and
host_name is not null
and a.status not in ('sleeping',
'dormant')
and a.session_id = @SPID
print '[2] Detail process: '
declare @nC nvarchar(max)
declare @last_read datetime
declare @last_write datetime
select @nC = case when r.sql_handle is
not null
then (
select top 1
SUBSTRING(t2.text, (r.statement_start_offset + 2) / 2, ( (
case when r.statement_end_offset = -1
then ((len(convert(nvarchar(MAX),t2.text))) * 2)
else r.statement_end_offset end
) - r.statement_start_offset) / 2)
from
sys.dm_exec_sql_text(r.sql_handle) t2
)
else ''
end
from sys.dm_exec_sessions s
with(nolock)
left outer join sys.dm_exec_connections c
with(nolock) on ( s.session_id = c.session_id )
left outer join sys.dm_exec_requests r
with(nolock) on ( r.session_id = c.session_id and r.connection_id =
c.connection_id )
where s.is_user_process = 1
and s.session_id =
@SPID
print ''
print @nC
end
drop table #NTPROCESS
drop table #use_object
return 0
GO