Cari Blog Ini

26 Juli 2012

How To Backup Database Using Maintenance Plan and Notify Operators with E-Mail

Apply To:
* Microsoft SQL Server 2008 R2
* Microsoft Outlook Express 6

A. Create a Backup Database Using Maintenance Plan
B. Make sure for Database Mail is running well
C. Create an Operator E-Mail
D. Setting Job for Backup Database with Notify Operators with E-Mail
E. Try to Run Jobs on Point D


For download a file .pdf, please open this link:
https://docs.google.com/open?id=0B6a5vK3IVP_0Y1JQQ3FnOWx6T0k

Error 1068 - The dependency service or group failed to start - When Run Manually SQL Server Agent Services

Apply To:
* Microsoft SQL Server 2000



* * * * *

25 Juli 2012

How To Avoid It In Logical Fragmentation SQL Server

Given that the primary contributor to logical fragmentation is page splitting,
the key to avoiding logical fragmentation
is to do everything possible to avoid page-splitting.

This can be done in a couple of different ways:

(1).
Choose index keys that are either ever-increasing values or ever-decreasing values.

In this type of index setup,
newly inserted records
will always be placed at either the beginning or end of the logical page-chain,
and hence lead to very few (if any) page-splitting as a result of insert operations.

You would still see page splitting at the root/intermediate level of the index,
but rarely (if ever) at the leaf level of the index.

If you are using GUID values for index keys,
with SQL Server 2005,
consider using the new function "newsequentialid()"
to generate ever-increasing GUID values
instead of random location values.

What Causes in File-Level Fragmentation SQL Server ?

(1).
Other applications/services/etc. (or SQL Server itself)
writing data to the same spindles
that SQL Server data/log files reside on
- this can lead to fragmentation on the file system
because as the SQL Server files grow,
they end up being allocated space on the spindle(s)
that is not physically contiguous to prior allocations for the same file,

since data for these other applications/services/etc. have been allocated
that space for storing whatever data it may be
(could be a word document,
could be a backup file (sql server or otherwise),
could a another sql server data file,
could be a text document, etc., etc.).

Notice that it doesn't have to be a non-sql server file
that can cause file-level fragmentation

What Can Cause a Page Split in Logical Fragmentation SQL Server ?

Logical fragmentation primarily results from page splitting, or page splits.
Aside from page splits,
logical fragmentation can also be introduced by deleting data
(resulting in free space within a page, dropping the page density).


What can cause a page split ?

A page split is what occurs to a database page
when new data of some kind needs to fit a page,
but there is not enough room on the page
to acommodate all the data needed to be placed in the page.

Page splits do NOT occur on a heap,
only on indexes (clustered or non-clustered).

What a page split is involves a complicated discussion,
because it is a fairly complicated operation
- for the sake of ease of description,
think of it as a single page being split in half,
with 1/2 of the rows on the page moving to a newly allocated page,
and the other 1/2 of the rows remaining where they are.

Type of SQL Server Fragmentation - File-Level Fragmentation

File-level fragmentation - this is your typical fragmentation of data blocks
as they reside physically on-disk.

SQL Server has limited control over this type of fragmentation
- much of it is controlled by the disk subsystem
and our decision on where to place files,
how many to have, our choice of storage, etc.

All SQL Server can do really is request space for the given data file(s)
when it is told to do so, or when it needs to -
these requests are then passed to the appropriate APIs, storage drivers, etc.
which handle the allocation of new space on-disk.

The best SQL can do is request this space be contiguous,
but it will only at best get a full contiguous block in the size requested,
and this assumes you are using a single spindle with a single file.

Once you start introducing things like RAID, SANs, etc.,
you are now treading into a territory
where it is nearly completely out of SQL Server's control.

24 Juli 2012

Type of SQL Server Fragmentation - Logical Fragmentation

We're going to use this terminology for what is arguably the most common type of fragmentation
- leaf pages of indexes becoming physically unordered within a given file
so as to no longer match the logical left-to-right linked-list ordering of the pages.

This type of fragmentation as we'll use it here
* relates in no way whatsoever to the physical pages being contiguous
(i.e. back to back, side by side, etc.) within the given file,
but only to the physical order of the pages within the file
compared to the logical order in the linked list;

* the difference between contiguous pages and properly ordered pages
would be that to be contiguous,
the pages must exist physically in side-by-side positions within the file
(i.e. pages 1,2,3,4,5,6,...n) with no gaps in between - to be properly ordered,
the pages must exist simply in logical order and on ever-increasing physical positions
within the file (i.e. pages 10, 17, 22, 23, 28, 42,...n).

Access Methods of Storage in SQL Server

* Seek
A seek is an efficient access method that can be fulfilled using an index structure.
Seeking touches fewer pages than scanning,
and can only occur on an index of some type.
Think of a seek as what you would do with a phone book
if I told you to find the phone number of someone
with last name and the first name.

* Scan
A scan is basically an access method whereby all data, or some range of data,
in an index or heap must be touched or retrieved in order to fulfill a request.
Think of a scan as what you would do with a phone book
if I told you to find the phone numbers for everyone in the book,
or the phone number for all people with the first name,
or for someone with a phone number of (555) 555-5555...

What is B-Tree in SQL Server ?

A B-Tree (balanced-tree) is a balanced, structured object,
and it is used for all indexes in Sql Server (Clustered, Nonclustered, XML, etc.).

Think of a B-Tree as a triangle-shaped structure of pages -
* it has a single root page at the top of the triangle
with pointers to the next level down the triangle
(which is wider than the root level by the number of pages
that the root page can contain pointers to),

* each of those pages contain pointers to the next level,
and so on (these are called intermediate pages) down the triangle
until you get to the bottom of the triangle,
which is the widest and referred to as the 'leaf' level of the structure.

In ASCII x marks, a B-tree would look like the following
if it contained 2 intermediate levels
where each page in the root and intermediate levels could each could point to 3 other pages:

What is Heap in SQL Server ?

A heap is basically a bunch of pages with no particular structure
(aside from that of a page/extent),  no order, no linkage.

If you have a table that has no Clustered Index defined on it,
then the data for that table is stored in a heap.

Heaps are logically a flat structure
(i.e. they are made up of only data pages, no root/intermediate pages).

Given that there is no order, structure, or page linkage in a heap,
there is no direct support in a heap for singleton lookups or range scans.


Source:
http://www.mssqltips.com/sqlservertip/2261/sql-server-fragmentation-storage-basics-and-access-methods-part-1-of-9/

What is HoBT in SQL Server ?

HoBT (pronounced "hobbit") stands for "Heap or B-Tree"
and is the logical name for how all user data is stored in Sql Server (either in a heap or a B-tree).

Source:
http://www.mssqltips.com/sqlservertip/2261/sql-server-fragmentation-storage-basics-and-access-methods-part-1-of-9/

What is Pages in SQL Server ?

A page in Sql Server is the primary storage structure for all data
(data, indexes, BLOB/CLOB/LOB, row-overflow/SLOB, etc.).

Pages are 8k in size and store records in no particular order
(row offset is used to find each logical record on the page)
- a very basic structure is as follows:

Source:
http://www.mssqltips.com/sqlservertip/2261/sql-server-fragmentation-storage-basics-and-access-methods-part-1-of-9/


23 Juli 2012

Example of SQL Server Date Difference Calculation - MINUTE

1. Find the number of MINUTE between two dates with format Date - yyyy-mm-dd
-----------------------------------------------------------------------
-----code:start-----
-----------------------------------------------------------------------
DECLARE @d1 DATETIME, @d2 DATETIME
SELECT
    @d1 = '2011-01-01 00:00:01',
    @d2 = '2011-01-02 23:59:00'
   
SELECT
    DATEDIFF(minute, @d1, @d2) AS MinuteDiff
-----------------------------------------------------------------------
-----code:end-----
-----------------------------------------------------------------------

Result Messages:
MinuteDiff
-----------
2879

Example of SQL Server Date Difference Calculation - HOUR

1. Find the number of HOUR between two dates with format Date - yyyy-mm-dd
-----------------------------------------------------------------------
-----code:start-----
-----------------------------------------------------------------------
DECLARE @d1 DATETIME, @d2 DATETIME
SELECT
    @d1 = '2011-01-01 00:00:01',
    @d2 = '2011-01-02 23:59:00'
   
SELECT
    DATEDIFF(hour, @d1, @d2) AS HourDiff
-----------------------------------------------------------------------
-----code:end-----
-----------------------------------------------------------------------

Result Messages:
HourDiff
-----------
47

Example of SQL Server Date Difference Calculation - DAY

1. Find the number of DAY between two dates with format Date - yyyy-mm-dd
-----------------------------------------------------------------------
-----code:start-----
-----------------------------------------------------------------------
DECLARE @d1 DATETIME, @d2 DATETIME
SELECT
    @d1 = '2011-01-01',
    @d2 = '2012-12-31'
   
SELECT
    DATEDIFF(day, @d1, @d2) AS DaysDiff
-----------------------------------------------------------------------
-----code:end-----
-----------------------------------------------------------------------

Result Messages:
DaysDiff
-----------
730

Example of SQL Server Date Difference Calculation - WEEK

1. Find the number of WEEK between two dates with format Date - yyyy-mm-dd
-----------------------------------------------------------------------
-----code:start-----
-----------------------------------------------------------------------
DECLARE @d1 DATETIME, @d2 DATETIME
SELECT
    @d1 = '2011-01-01',
    @d2 = '2012-12-31'
   
SELECT
    DATEDIFF(week, @d1, @d2) AS WeekDiff
-----------------------------------------------------------------------
-----code:end-----
-----------------------------------------------------------------------

Result Messages:
WeekDiff
-----------
105

Example of SQL Server Date Difference Calculation - MONTH

1. Find the number of MONTH between two dates with format Date - yyyy-mm-dd
-----------------------------------------------------------------------
-----code:start-----
-----------------------------------------------------------------------
DECLARE @d1 DATETIME, @d2 DATETIME
SELECT
    @d1 = '2011-01-01',
    @d2 = '2012-12-31'
   
SELECT
    DATEDIFF(month, @d1, @d2) AS MonthDiff
-----------------------------------------------------------------------
-----code:end-----
-----------------------------------------------------------------------

Result Messages:
MonthDiff
-----------
23

Example of SQL Server Date Difference Calculation - QUARTER

1. Find the number of QUARTER between two dates with format Date - yyyy-mm-dd
-----------------------------------------------------------------------
-----code:start-----
-----------------------------------------------------------------------
DECLARE @d1 DATETIME, @d2 DATETIME
SELECT
    @d1 = '2011-01-01',
    @d2 = '2012-12-31'
   
SELECT
    DATEDIFF(quarter, @d1, @d2) AS QuarterDiff
-----------------------------------------------------------------------
-----code:end-----
-----------------------------------------------------------------------

Result Messages:
QuarterDiff
-----------
7

Example of SQL Server Date Difference Calculation - YEARS


1. Find the number of YEARS between two dates with format Date - yyyy-mm-dd
-----------------------------------------------------------------------
-----code:start-----
-----------------------------------------------------------------------
DECLARE @d1 DATETIME, @d2 DATETIME
SELECT
    @d1 = '1997-01-01',
    @d2 = '2009-12-31'
   
SELECT
    DATEDIFF(year, @d1, @d2) AS YearDiff
-----------------------------------------------------------------------
-----code:end-----
-----------------------------------------------------------------------

Result Messages:
YearDiff
-----------
12

Example of Backup Database With Compression

1. Backup Database With Compression
-----------------------------------------------------------------------
-----code:start-----
-----------------------------------------------------------------------
USE master
go

BACKUP DATABASE DB_Simulasi
     TO DISK = 'D:\Backup\DB_Simulasi.bak'
     WITH COMPRESSION
-----------------------------------------------------------------------
-----code:end-----
-----------------------------------------------------------------------

Result Messages:
Processed 66832 pages for database 'DB_Simulasi', file 'SeaSQL_dat' on file 1.
Processed 2 pages for database 'DB_Simulasi', file 'SeaSQL_log' on file 1.
BACKUP DATABASE successfully processed 66834 pages in 12.519 seconds (41.707 MB/sec).

Example of Creating Backup Device for Network Path

1. Add Backup Device for Network Path
-----------------------------------------------------------------------
-----code:start-----
-----------------------------------------------------------------------
USE master
go

EXEC sp_addumpdevice  
         @devtype = 'Disk',
         @logicalname =  'NetworkDevice',
         @physicalname = '\\192.xxx.x.xxx\Dari DBBackup\DB_Simulasi.bak'
-----------------------------------------------------------------------
-----code:end-----
-----------------------------------------------------------------------

Result Messages:
Command(s) completed successfully.

Example of Creating Backup Device For Disk

1. Add Backup Device
-----------------------------------------------------------------------
-----code:start-----
-----------------------------------------------------------------------
USE master
go

EXEC sp_addumpdevice 
         @devtype = 'Disk',
         @logicalname =  'DB_Simulasi',
         @physicalname = 'D:\Backup\DB_Simulasi.bak'
-----------------------------------------------------------------------
-----code:end-----
-----------------------------------------------------------------------

Result Messages:
Command(s) completed successfully.


2. Check Availability of backup device
-----------------------------------------------------------------------
-----code:start-----
-----------------------------------------------------------------------
select * from sys.sysdevices
select * from sys.backup_devices

22 Juli 2012

Colouring - The Story Of Buddha - 77. Helping To Solve People's Problems


Colouring - The Story Of Buddha - 68. A Wild Elephant Tamed By Loving-Kindness


Colouring - The Story Of Buddha - 58. Teaching The Dharma To His Family


Colouring - The Story Of Buddha - 55. Returning To Kapilavatthu


Colouring - The Story Of Buddha - 54. Instructing The Assembly of Arahants


Colouring - The Story Of Buddha - 44. Spreading The Dharma


Colouring - The Story Of Buddha - 42. Teaching The Four Noble Truths


Colouring - The Story Of Buddha - 41. The Buddha Convinces His Companions


Colouring - The Story Of Buddha - 38. The Buddha Decides To Teach


Colouring - The Story Of Buddha - 36. The Supreme Enlightenment


Colouring - The Story Of Buddha - 35. Meditating Under The Bodhi Tree


Colouring - The Story Of Buddha - 22. A Last Look At Kapilavatthu


Colouring - The Story Of Buddha - 19. The 4 Sights - A Monk


Colouring - The Story Of Buddha - 18. The 4 Sights - Death


Colouring - The Story Of Buddha - 17. The 4 Sights - Sickness


Colouring - The Story Of Buddha - 16. The 4 Sights - Old Age


Colouring - The Story Of Buddha - 06. The Princes Loving and Kindness


21 Juli 2012

Asal Mula dari Kebaikan

Asal mula dari kebaikan terdiri dari :

1. Merasa puas dan bahagia
dengan apa yang dimiliki dengan jerih payah dan jalan yang benar.

2. Dengan memiliki cinta kasih, kasih sayang,
merasa simpati dan bahagia akan kebahagian orang lain
dan memiliki keseimbangan batin.

3. Memiliki pengetahuan dan kebijaksanaan yang benar


Sumber:
Majalah Dawai No. 43
Edisi Vesakh 2550 | 2006 | Vihara Dhammadipa Surabaya
Hal. 15-16

Kebaikan Melalui Ucapan, Tindakan, Pikiran

Tiga kebaikan melalui badan jasmani :

1. Tidak melakukan penyiksaan dan pembunuhan
demi mencari kepuasan atau kebahagiaan untuk Diri Sendiri.

2. Tidak mengambil barang yang tidak diberikan oleh Pemilik-nya,
bila tidak mau menderita di kehidupannya.

3. Tidak mengikuti nafsu rendah.


Tiga kebaikan melalui ucapan :

1. Tidak memfitnah,
karena fitnah lebih kejam daripada pembunuhan.

Empat Macam Kebahagiaan Bagi Umat

Sang Buddha menguraikan empat macam kebahagiaan bagi umat biasa,
yaitu:

1. Athi sukha
yaitu kebahagiaan karena memiliki kekayaan, kesehatan, umur panjang, kecantikan,
dan sebagainya

2. Bhoga sukha
yaitu karena dapat menikmati apa yang diperolehnya

3. Anana sukha
yaitu kebahagiaan karena seseorang bekerja keras dan dapat memenuhi kebutuhan sehari-hari

4. Anavajja sukha
yaitu kebahagiaan yang didapatkan jika seseorang merasa dirinya telah berbuat sesuai Dhamma


Sumber:
Majalah Dawai No. 43
Edisi Vesakh 2550 | 2006 | Vihara Dhammadipa Surabaya
Hal. 24

Cut-Out Buddhist Greeting


Colouring in The Sacred Lotus


Colouring in The Dharma Wheel


Colouring Flag Buddhist


Colouring Bodhi


Colouring Bodhi Buddha


Colouring The Buddha


20 Juli 2012

Msg 3102 - RESTORE Cannot Process Database Because It Is In Use By This Session

Apply To :
Microsoft SQL Server 2008 R2



* * * * * 

Example of Backup and Restore VerifyOnly Database Using Password

1. Backup database
----------------------------------------------------------------------------
-----code:start-----
----------------------------------------------------------------------------
BACKUP DATABASE DB_Simulasi TO DISK = 'D:\DB_Simulasi.bak'
    WITH
    PASSWORD='123456';
----------------------------------------------------------------------------
-----code:end-----
----------------------------------------------------------------------------

Result Messages:
Processed 66832 pages for database 'DB_Simulasi', file 'SeaSQL_dat' on file 1.
Processed 1 pages for database 'DB_Simulasi', file 'SeaSQL_log' on file 1.
BACKUP DATABASE successfully processed 66833 pages in 13.349 seconds (39.114 MB/sec).


2. Restore using RESTORE VERIFYONLY Statement

Example of Taking Split Backups Database for SQL Server

1. Run this query
---------------------------------------------------
-----code:start-----
---------------------------------------------------

    BACKUP DATABASE DB_Simulasi TO
    Disk = 'C:\Backup\DB_Simulasi_1.bak',
    Disk = 'D:\Backup\DB_Simulasi_2.bak',
    Disk = '\\192.xxx.x.xxx\DbBackup\Backup\DB_Simulasi_3.bak';

---------------------------------------------------
-----code:end-----
---------------------------------------------------

Result Messages:
Processed 66832 pages for database 'DB_Simulasi', file 'SeaSQL_dat' on file 1.
Processed 1 pages for database 'DB_Simulasi', file 'SeaSQL_log' on file 1.
BACKUP DATABASE successfully processed 66833 pages in 19.560 seconds (26.693 MB/sec).

Kerajinan Tangan - Membuat Bunga Teratai


Kerajinan Tangan - Membuat Bunga Mawar


Kerajinan Tangan - Membuat Lampion


Kerajinan Tangan - Membuat Daun Bodhi


Kerajinan Tangan - Membuat Pohon Bodhi


Kerajinan Tangan - Membuat Pagoda

Kerajinan Tangan - Membuat Stupa


19 Juli 2012

Restore VerifyOnly Statement with Send To Email

A. Sample Database
B. Creating Backup Device for Disk
C. Creating Sample Backup Database
D. Creating a Table for Information about Verify Restore
E. Make sure for Database Mail is running well
F. Run query for Restore VerifyOnly Statement with Send To Email

For download a file .pdf, please open this link:
https://docs.google.com/open?id=0B6a5vK3IVP_0MzVZSXh0cEl4MTg

The Backup Set On File 1 Is Valid with RESTORE VERIFYONLY Statement

When run this query:
/*-----code:start-----*/
RESTORE VERIFYONLY FROM DISK = 'D:\DB_Simulasi_20120717_1500.bak'
/*-----code:end-----*/

Result Messages:

The backup set on file 1 is valid.


It Means:
* File backup "DB_Simulasi_20120717_1500.bak" is ok.

Msg 3201, Level 16, State 2, Line 1 - Cannot open backup device When Run RESTORE VERIFYONLY Statement

When run this query:
/*-----code:start-----*/
RESTORE VERIFYONLY FROM DISK = 'D:\DB_Simulasi_20120717.bak'
/*-----code:end-----*/


Error Messages:

Msg 3201, Level 16, State 2, Line 1Cannot open backup device 

'D:\DB_Simulasi_20120717.bak'.

Operating system error 2 (The system cannot find the file specified.).



Msg 3013, Level 16, State 1, Line 1 

VERIFY DATABASE is terminating abnormally.


Menggambar Sikap Baik Hormat Kepada Orang Tua


Menggambar Buddha Rupang


Menggambar Objek Puja


Mewarnai Menolong Angsa


Mewarnai - Menghormati Guru


Kerajinan Tangan - Membuat Bunga Untuk Teman


Kerajinan Tangan - Membuat Ikan


Kerajinan Tangan - Membuat Burung