Forum Orisinil
OtomaX - Orisinil Topup Machine => Tips dan Trik => Topic started by: Yusuf on June 21, 2010, 09:32:24 AM
-
Periodically (daily, weekly, or monthly) perform a database reorganization on all the indexes on all the tables in your database. This will rebuild the indexes so that the data is no longer fragmented. Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Server's performance. It will also update column statistics.
If you do a reorganization on a table with a clustered index, any non-clustered indexes on that same table will automatically be rebuilt.
Database reorganizations can be done using the Maintenance Wizard, or by running your own custom script via the SQL Server Agent (see below).
The DBCC DBREINDEX command will not automatically rebuild all of the indexes on all the tables in a database; it can only work on one table at a time. But if you run the following script, you can index all the tables in a database with ease:
--Script to automatically reindex all tables in a database
USE otomax --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',95)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
The script will automatically reindex every index in every table of any database you select, and provide a fill factor of 95%. You can substitute any number appropriate for the fill factor in the above script.
When DBCC DBREINDEX is used to rebuild indexes, keep in mind that as the indexes on a specific table are being rebuilt, that the table becomes unavailable for use by your users.
For example, when a non-clustered index is rebuilt, a shared table lock is put on the table, preventing all but SELECT operations to be performed on it. When a clustered index is rebuilt, an exclusive table lock is put on the table, preventing any table access by your users. Because of this, you should only run this command when users don't need access to the tables being reorganized. [7.0, 2000, 2005] Updated 7-24-2006
sumber: http://www.sql-server-performance.com/tips/rebuilding_indexes_p1.aspx
-
ora ngerti bos ;D
-
Maksud nya gmn...
Babar Blas gak Mudeng Bahasa Inggris... :'( :'( :'( :'( :'( :'( :'(
di translate Keluarnya gini...
Berkala (harian, mingguan, atau bulanan) melakukan reorganisasi database pada semua indeks pada semua tabel dalam database Anda. Hal ini akan membangun kembali indeks sehingga data tidak lagi terfragmentasi. Terfragmentasi data dapat menyebabkan SQL Server untuk melakukan data yang tidak perlu dibaca, memperlambat kinerja SQL server. Hal ini juga akan memperbarui kolom statistik.
Jika Anda melakukan reorganisasi di atas meja dengan clustered index, setiap non-clustered indexes pada tabel yang sama secara otomatis akan dibangun kembali.
Database reorganisasi dapat dilakukan dengan menggunakan Wizard Pemeliharaan, atau dengan menjalankan script kustom Anda sendiri melalui SQL Server Agen (lihat di bawah).
Perintah DBCC DBREINDEX tidak akan secara otomatis membangun kembali semua indeks pada semua tabel dalam database, hanya dapat bekerja pada satu meja pada suatu waktu. Tapi jika Anda menjalankan script berikut, Anda dapat mengindeks semua tabel dalam database dengan mudah:
Script secara otomatis akan Isi nama baru kotak setiap indeks dalam setiap tabel database yang Anda pilih, dan memberikan faktor isi dari 90%. Anda dapat mengganti sejumlah faktor yang tepat untuk mengisi naskah di atas.
Ketika DBCC DBREINDEX digunakan untuk membangun kembali indeks, perlu diingat bahwa sebagai indeks pada tabel tertentu sedang dibangun kembali, bahwa meja menjadi tidak tersedia untuk digunakan oleh pengguna Anda.
Sebagai contoh, ketika indeks non-clustered ini dibangun kembali, meja bersama kunci diletakkan di atas meja, mencegah semua tapi operasi SELECT untuk dilakukan di atasnya. Ketika sebuah clustered index ini dibangun kembali, kunci meja eksklusif disimpan di meja, meja mencegah akses oleh pengguna Anda. Karena itu, Anda hanya perlu menjalankan perintah ini ketika pengguna tidak memerlukan akses ke meja yang ditata kembali. [7,0, 2000, 2005] Diperbarui 2006/07/24
-
Periodically (daily, weekly, or monthly) perform a database reorganization on all the indexes on all the tables in your database. This will rebuild the indexes so that the data is no longer fragmented. Fragmented data can cause SQL Server to perform unnecessary data reads, slowing down SQL Server's performance. It will also update column statistics.
If you do a reorganization on a table with a clustered index, any non-clustered indexes on that same table will automatically be rebuilt.
Database reorganizations can be done using the Maintenance Wizard, or by running your own custom script via the SQL Server Agent (see below).
The DBCC DBREINDEX command will not automatically rebuild all of the indexes on all the tables in a database; it can only work on one table at a time. But if you run the following script, you can index all the tables in a database with ease:
--Script to automatically reindex all tables in a database
USE DatabaseName --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
The script will automatically reindex every index in every table of any database you select, and provide a fill factor of 90%. You can substitute any number appropriate for the fill factor in the above script.
When DBCC DBREINDEX is used to rebuild indexes, keep in mind that as the indexes on a specific table are being rebuilt, that the table becomes unavailable for use by your users.
For example, when a non-clustered index is rebuilt, a shared table lock is put on the table, preventing all but SELECT operations to be performed on it. When a clustered index is rebuilt, an exclusive table lock is put on the table, preventing any table access by your users. Because of this, you should only run this command when users don't need access to the tables being reorganized. [7.0, 2000, 2005] Updated 7-24-2006
sumber: http://www.sql-server-performance.com/tips/rebuilding_indexes_p1.aspx
solusi http://forum.orisinil.com/index.php?topic=1546.0, setelah berbulan2 pake otomax br x ini ngalamin yg aneh, tapi langsung beres, trims big bozz :-* tapi lbh enaknya dijelaskan maksud dan tujuan script ini biar lbh di mengerti oleh org awam seperti saya...
-
mungkin bisa membantu nih
http://forum.orisinil.com/index.php?topic=1550.0
makasi
-
Sedikit sharing aja...
Index adalah semacam tabel tambahan dari tabel sesungguhnya. Tujuannya adalah untuk mempercepat query pencarian data.
Karena data terus berubah/bertambah, Index ini bisa ter-"fragmented" sehingga menurunkan performance operasi pada tabel tersebut.
Script diatas tujuannya untuk defrag index. Coba bayangkan seperti kita defrag harddisk, kurang lebih sama persis seperti itu...
Pada proses defrag, sebaiknya matikan OtomaX atau program2 lain yang mengakses database tersebut.
-
Sedikit sharing aja...
Index adalah semacam tabel tambahan dari tabel sesungguhnya. Tujuannya adalah untuk mempercepat query pencarian data.
Karena data terus berubah/bertambah, Index ini bisa ter-"fragmented" sehingga menurunkan performance operasi pada tabel tersebut.
Script diatas tujuannya untuk defrag index. Coba bayangkan seperti kita defrag harddisk, kurang lebih sama persis seperti itu...
Pada proses defrag, sebaiknya matikan OtomaX atau program2 lain yang mengakses database tersebut.
pak, script diatas ditempatkan dimana?
-
gak ngerti blas ..... ;D
make e utk otomax gimana ??? :-[
-
Mantap..... big bos.... cara ini bermanfaat banget buat yang sudah bengkak databasenya.....
-
Cara pake nya gmn ya ??
-
nih aku bikinin file phpnya biar lebih gampang..
jadi tinggal masuk masukin parameter aja ;D
[attachment deleted by admin]
-
nih aku bikinin file phpnya biar lebih gampang..
jadi tinggal masuk masukin parameter aja ;D
sedot lagi... ;)
Tq
-
nih aku bikinin file phpnya biar lebih gampang..
jadi tinggal masuk masukin parameter aja ;D
sedot lagi... ;)
Tq
semalem abis tes
pas lagi sepi trx otomax di close, database di backup (buat jaga2)
jalanin file nya deh..
stelah selesai. Tak buka lagi oto nya..
wuihh jadi cepeet...
kerasa banget bedanya..
tested by me ;D
-
make' nya gimana gan,... ??? ??? ???
-
??? ??? ???
-
??? ??? ??? ???
-
kasih tutornya dong .. ;D
-
make' nya gimana gan,... ??? ??? ???
pake sql studio bos. ada klik tombol new query. masukin scriptnya disitu dan execute...
-
MANTAB berasa banget .....
thxs big boss
-
di sy kok keluar gini y ??? ??? ???
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'DatabaseName'. No entry found with that name. Make sure that the name is entered correctly.
-
Ya iyalah.. Wong nama database nya aja gak dirubah gmn mau eksekusi, hahaha.. ;D :D
-
tutornya gmn gan ??? ???
yg luengkap biar yg gaptek ky sy bs coba :D :D
-
Ya iyalah.. Wong nama database nya aja gak dirubah gmn mau eksekusi, hahaha.. ;D :D
udh di rubah jg sama bos, ;D ;D
kasih contoh dong
-
tutornya gmn gan ??? ???
yg luengkap biar yg gaptek ky sy bs coba :D :D
1. buka mssql studionya
2. klik new query di pojok kiri atas.
3. nah copy kode ini
USE otomax --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
lalu paste di mssql studio..
itu database namenya diganti sesuai nama DB juragan.. (standardnya sih otomax kan? )
lalu klik execute..
tinggal tunggu deh...
NB : Pastikan otomax, webreport (jika ada) pokoknya semua aplikasi yang mengakses database itu (kecuali mssql studio) di close dulu..
Goodluck ;D
-
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
berhasilkah klo hasilnya begini???
-
tutornya gmn gan ??? ???
yg luengkap biar yg gaptek ky sy bs coba :D :D
1. buka mssql studionya
2. klik new query di pojok kiri atas.
3. nah copy kode ini
USE otomax --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
lalu paste di mssql studio..
itu database namenya diganti sesuai nama DB juragan.. (standardnya sih otomax kan? )
lalu klik execute..
tinggal tunggu deh...
NB : Pastikan otomax, webreport (jika ada) pokoknya semua aplikasi yang mengakses database itu (kecuali mssql studio) di close dulu..
Goodluck ;D
setelah di coba hasilnya seperti ini :
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
apakah hasil diatas kita save atau diabaikan saja?
-
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
berhasilkah klo hasilnya begini???
itu berhasil bos.
-
SAYA uda coba seperti nya mmg agak cepat pada saat membuka otomax nya,hihihihi
-
tutornya gmn gan ??? ???
yg luengkap biar yg gaptek ky sy bs coba :D :D
1. buka mssql studionya
2. klik new query di pojok kiri atas.
3. nah copy kode ini
USE otomax --Enter the name of the database you want to reindex
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
lalu paste di mssql studio..
itu database namenya diganti sesuai nama DB juragan.. (standardnya sih otomax kan? )
lalu klik execute..
tinggal tunggu deh...
NB : Pastikan otomax, webreport (jika ada) pokoknya semua aplikasi yang mengakses database itu (kecuali mssql studio) di close dulu..
Goodluck ;D
tinggal testi kalo gini.. hatur tengkyu om bajay :-* :-*
-
MANTAB berasa banget .....
thxs big boss
yg paling berasa dimana nya gan ??? ??? ;D ;D
-
;D iya nih bos,yg berasa banget dimananya sih..?
Kl saya,berhubung baru,jd database masih sedikit..waktu blm di rebuilding,ukuran database sekitar 70MB..setelah di rebuilding,kok jadi 90MB ya..?
-
ayooo siapa yg bisa kasi tau yg paling berasa di bagian yg mana ;D :D :D
-
MANTAB berasa banget .....
thxs big boss
yg paling berasa dimana nya gan ??? ??? ;D ;D
paling bersa klu kita udah diujung tanduk oowwwwwhhhhh sungguh nikmat MANTAB ;D ;D ;D ;D
-
MANTAB berasa banget .....
thxs big boss
yg paling berasa dimana nya gan ??? ??? ;D ;D
paling bersa klu kita udah diujung tanduk oowwwwwhhhhh sungguh nikmat MANTAB ;D ;D ;D ;D
malah bnr2 kesitu jwbanya :D :D
-
;D iya nih bos,yg berasa banget dimananya sih..?
Kl saya,berhubung baru,jd database masih sedikit..waktu blm di rebuilding,ukuran database sekitar 70MB..setelah di rebuilding,kok jadi 90MB ya..?
dishrink aja bos... ada menunya di sql studio...
-
dishrink aja bos... ada menunya di sql studio...
kalo di shrink nya di tab menghapus data sama ga ???
-
kok punya sy respon nya begini ya???
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
The statement has been terminated.
Msg 1105, Level 17, State 2, Line 14
Could not allocate space for object 'dbo.SORT temporary run storage: 458229543075840' in database 'Otomax' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
-
benar2 terasa jd cepat aksesnya
-
Untuk melakukan Shrink via SQL
otomax>Tasks>Shrink... Pada SQL Studio Express ada 2 pilihan Yaitu database sama file dan yang menjadi pertanyaan saya apakah ke-2 pilihan tersebut yg dilakukan apa salah satu aja............ ??? ??? ???
-
Nyimak aj aahh... db ku masih dikit.... belom banyak kaya konco2 disini.... jadi malu... :-\ :-\ :-\ :-\
-
Setelah beberapa lalu melakukan reindex pada otomaX (gara2 kena penyakit kuning)
Emang sangat terasa sekali perbedaannya... OtomaX terasa lebih ringan dan proses semakin cepat...
Nah, yang jadi pertanyaan,,
Apakah Re-Index ini perlu dilakukan secara berkala? Kalau perlu idealnya dengan interval berapa lama?
Kalau terlalu sering id re-index ada efek negatifnya ga?
Buat para master, ditunggu pencerahannya.. trims
-
Setelah beberapa lalu melakukan reindex pada otomaX (gara2 kena penyakit kuning)
Emang sangat terasa sekali perbedaannya... OtomaX terasa lebih ringan dan proses semakin cepat...
Nah, yang jadi pertanyaan,,
Apakah Re-Index ini perlu dilakukan secara berkala? Kalau perlu idealnya dengan interval berapa lama?
Kalau terlalu sering id re-index ada efek negatifnya ga?
Buat para master, ditunggu pencerahannya.. trims
Tul nich... spa yg tau tlg sharing...
-
Nunggu screen cara reindex nya maaf newbie ;)
-
Betul bgt bos, mudah2an ada yg sukarela bwt manual book spt wkt instal awal step by step beserta picnya.
Pastinya semua setuju, ngarep.com ;D ;D
memantau sekalian menunggu mode on. 8) 8)
-
sodara2, file mdf saya udah 1,8 giga.. maw nangis.. berat bgt. baru jalankan query ini alhamdulilah enteng
hemh,, bisa tidur nyenyak
-
sodara2, file mdf saya udah 1,8 giga.. maw nangis.. berat bgt. baru jalankan query ini alhamdulilah enteng
hemh,, bisa tidur nyenyak
Wuih..1,8GB itu data brpa abad bos kl bagi saya..tp kl bagi bos,mungkin cuma 1 bulan kali ya ;D
-
Wediann....muanteb bosss...kerasa banget bedanya...biasanya search trx lambat gila..sekarang lancar...mantap jaya...
Unitedtronik
-
sodara2, file mdf saya udah 1,8 giga.. maw nangis.. berat bgt. baru jalankan query ini alhamdulilah enteng
hemh,, bisa tidur nyenyak
Wuih..1,8GB itu data brpa abad bos kl bagi saya..tp kl bagi bos,mungkin cuma 1 bulan kali ya ;D
punyaku baru 1,2gb
trx tetap aman terkendali.
-
akhirnya ngalami juga otomax suspect :'( :'( :'( :'( :'(
untunggya para suhu dah posting disini cara atasinya alhamdulillah sudah beres
da reindex telah di lakukan sesuai prosedur.....
makasih semuanya terutama buat Bos Yusuf ..
dan seseorang yang jauh disana (sulawesi) thanks ya bro
-
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 16916, Level 16, State 1, Line 20
A cursor with the name 'TableCurso' does not exist.
ada yg merah knapa ya ???
maklum newbie
-
all tq beud ;D
-
akhirnya ngalami juga otomax suspect :'( :'( :'( :'( :'(
untunggya para suhu dah posting disini cara atasinya alhamdulillah sudah beres
da reindex telah di lakukan sesuai prosedur.....
makasih semuanya terutama buat Bos Yusuf ..
dan seseorang yang jauh disana (sulawesi) thanks ya bro
database suspect itu yang seperti apa ya, mohon pencerahannya
-
apa ada solusi ????
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
The statement has been terminated.
Msg 1105, Level 17, State 2, Line 11
Could not allocate space for object 'dbo.SORT temporary run storage: 422214188335104' in database 'otomax' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup