Recent comments

None


İçerik Ara











Yasal Uyarı
Bu sitede sunulan tüm bilgi ve dökümanlar Turgay Sahtiyan tarafından yazılmaktadır. Yazıların kaynak göstermek şartıyla kullanılması serbesttir.

© Copyright 2009-2013
Takvim
<<  Aralık 2017  >>
PaSaÇaPeCuCuPa
27282930123
45678910
11121314151617
18192021222324
25262728293031
1234567
Keywords

SQL Server’da performans açısından belki de en önemli noktalardan biri sistem database’lerinden biri olan TempDB’nin iyi bir şekilde configure edilmesi ve yönetilmesidir. Bugünkü yazımda TempDB ve TempDB best practice’leri üzerine konuşuyor olacağız.

[more]

TempDB Nedir?

TempDB, SQL Server kuruluşu ile beraber gelen 4 sistem database’inden biridir. Her instance için 1 adet olan TempDB’de örnek olarak aşağıdaki bilgiler tutulur:

  • Kullanıcı tarafından create edilen temp table, table variable ya da cursor gibi geçici objeler
  • SQL Server tarafından sort, spool, hash join gibi işlemler için create edilen work table ya da ara sonuçlar gibi internal objeler
  • Read committed snapshot isolation ya da snapshot isolation özelliği kullanıldığında generate edilen row versioning bilgileri
  • Online index operation işlemlerinde tutulan row versioning bilgileri TempDB’de tutulurlar.

Yukarıda da görüldüğü gibi hem kullanıcı hem de internal işlemler için çok fazla durumda kullanılan TempDB bazı durumlarda performans sıkıntısı oluşturabilmektedir. Bu yüzden çok iyi configure ve monitor edilmelidir.

Ayrıca daha önce de belirttiğim gibi tüm instance için sadece 1 adet olan TempDB, konfigurasyonun ve monitoring’in önemini daha da arttırmaktadır.

 

TempDB Best Practice’leri

Bu bölümde TempDB için uyguladığımız aşaıdaki best practice’lerden bahsediyor olacağım.

  • TempDB Data File Sayısı
  • TempDB Data File’larının Aynı Boyutta Olması
  • TempDB File’larının Bulunduğu Storage
  • Yoğun TempDB Kullanımının Analiz Edilmesi
  • T1118 Trace Flag’i

TempDB Data File Sayısı

TempDB üzerinde yapılan allocation ve deallocation işlemlerinde diğer database’lerde de olduğu gibi PFS, GAM ve SGAM page’leri kullanılır. Çok yoğun TempDB kullanılan workload’larda, örneğin sürekli ufak boyutlu temp table’lar create ve drop edildiğinde bu page’ler üzerinde contention yani beklemeler görülebilir. (Örneğin 2:1:3 SGAM page’inde PAGELATCH_XX wait tipi gibi)

Allocation bitmap page’lerinde oluşan bu contention’i azaltmak için 1’den fazla tempdb data file’i kullanılabilir. Bu şekilde page sayısı arttırılacak ve round robin kullanım sayesinde contention azaltılacaktır.

TempDB data file sayısının best practice’i workload’a ve core CPU sayısına göre değişiklik göstermektedir. Bu konu üzerinde internette çok fazla best practice’e rastlayabilirsiniz. Benim kullandığım ise şu şekilde;

  • Core CPU sayısı 8’den az ise core cpu sayısı kadar data file yapmak
  • Core CPU sayısı 8’den fazla ise TempDB data file sayısının 8 yapılması ve wait type’larda PAGELATCH_XX wait type’ının analiz edilmeye devam edilmesidir. Contention görülmeye devam edildiği sürece data file sayısı her defasında 4 arttırılabilir.
“ But there’s now even better guidance, and what I also recommend. At PASS in 2011, my good friend Bob Ward, who’s the top guy in SQL Product Support, espoused a new formula: if you have less than 8 cores, use #files = #cores. If you have more than 8 cores, use 8 files and if you’re seeing in-memory contention, add 4 more files at a time.”
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/

Bu noktada akla TempDB data file sayısını her defasında neden 4’er 4’er arttırıp tek seferde 32 ya da 40 yapmadığımız sorusu gelebilir. TempDB data file sayısının fazla olması da performance’a eksi yönde etki eden bir durumdur. Bu yüzden belirli bir sayıdan (8) başlanarak contention görüldüğü sürece (contention analizi için sys.dm_os_wait_stats DMV’si sonucunda çıkan result set’teki PAGELATCH_XX wait type’I gözlenebilir) data file sayısının arttırılması daha iyi bir yaklaşımdır.

Temp DB data file sayısının çok olmasının performans üzerindeki etkisi için aşağıdaki yazıya göz atılabilir;

“So, why is one-to-one not always a good idea? Too many tempdb data files can cause performance problems for another reason. If you have a workload that uses query plan operators that require lots of memory (e.g. sorts), the odds are that there won’t be enough memory on the server to accommodate the operation, and it will spill out to tempdb. If there are too many tempdb data files, then the writing out of the temporarily-spilled data can be really slowed down while the allocation system does round-robin allocation. The same thing can happen with very large temp tables in tempdb too.

Why would round-robin allocation cause things to slow down for memory-spills to tempdb with a large number of files? A couple of possibilities:

Round-robin allocation is per filegroup, and you can only have one filegroup in tempdb. With 16, 32, or more files in tempdb, and very large allocations happening from just a few threads, the extra synchronization and work necessary to do the round-robin allocation (looking at the allocation weightings for each file and deciding whether to allocate or decrement the weighting, plus quite frequently recalculating the weightings for all files – every 8192 allocations) starts to add up and become noticeable. It’s very different from lots of threads doing lots of small allocations. It’s also very different from allocating from a single-file filegroup – which is optimized (obviously) to not do round-robin.

Your tempdb data files are not the same size and so the auto-grow is only growing a single file (the algorithm is unfortunately broken), leading to skewed usage and an I/O hotspot.

Having too many files can lead to essentially random IO patterns when the buffer pool needs to free up space through the lazywriter (tempdb checkpoints don’t flush data pages) for systems with not very large buffer pools but *lots* of tempdb data. If the I/O subsystem can’t handle the load across multiple files, it will start to slow down.”
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/

TempDB’ye yeni bir data file ekleme işini hem SSMS hem de TSQL komutu ile yapabilirsiniz. Örneğin aşağıdaki komut TempDB’ye başlangıç boyutu 1024 MB, auto growth değeri 512 MB olan yeni bir data file ekler.

USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE 
	( NAME = N'Tempdev2', 
	  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Tempdev2.ndf' , 
	  SIZE = 1048576KB , 
	  FILEGROWTH = 524288KB )
GO

TempDB Data File’larının Aynı Boyutta Olması

Bir önceki bölümde TempDB data file’larının sayısı üzerine konuştuk ve diyelim ki core CPU sayımız 4 ve 4 adet TempDB data file bulundurmaya karar verdik. Şimdi ikinci best practice’imiz yani bu data file’ların aynı boyutta olmasının öneminden bahsedeceğiz.

TempDB’de bulunan data file’lar round robin olarak proportional fill algorithm özelliğini kullanırlar. Bu özelliğin performanslı bir şekilde kullanılabilmesi için tüm data file’ların aynı boyutta olması gerekmektedir. Ayrıca TempDB’ye özel bir problem, tempdb data file’larından eğer herhangi biri diğerlerinden büyükse, auto growth esnasında her zaman büyük olan data file büyütülecektir. Bu durum da proportional fill algorithm özelliğinin sağlıklı bir şekilde kullanılmamasına sebebiyet verecektir.

Aslında burada başka bir best practice’den daha bahsetmek gerekiyor. Bilindiği gibi data file’larda yer bittiğinde eğer auto growth özelliği aktif halde bırakılırsa data file için yeni bir alan otomatik olarak alloce edilecektir. Data file allocation işlemi file seviyesinde exclusive lock’a sebebiyet verdiğinden dolayı auto growth işlemi sırasında file’a erişim engellenecek ve eğer çok fazla auto growth oluşursa performans eksi yönde etkilencektir. Bu yüzden

  • TempDB data file’larına uygun bir başlangıç değeri vermek
  • Auto growth değerlerini daha büyük chunk’lar halinde ayarlamak önemli bir diğer best practice’dir

Bu şekilde TempDB data file’ları -ideal durumda- auto growth’a yakalanmayacak, yakalansa bile büyük chunk’lar halinde büyütüldükleri için sürekli büyüme ihtiyacı duymayacaklardır.

Bu bölümdeki ilk best practice yani TempDB data file’larına uygun bir başlangıç değeri vermek üzerine önemli bir noktanın altını çizmek istiyorum. Bildiğiniz gibi TempDB her SQL Server restart’ında baştan create edilir. Eğer TempDB data file’larının ilk başlangıç değerlerini çok yüksek tutarsanız restart işlemleri sonrası SQL Server’ın hazır hale gelmesi uzun sürecektir. Dolayısıyla uygun bir değerin belirlenmesi önem tşaımaktadır.

Bu bölümdeki best practice’leri toparlayacak olursak. Diyelim ki core CPU sayımız 4 ve 4 TempDB data file’i kullanmaya karar verdik. Ve eğer toplamda 10GB’lık TempDB boyutu bizim için yeterli ise yapmamız gerekenleri şu şekilde sıralayabiliriz.

  • TempDB’ye 3 adet daha data file eklenerek toplam data file sayısı 4’e çıkarılır.
  • Tüm data file’ların başlangıç boyutları 2.5 GB (10 GB/4) olarak ayarlanır.
  • Data file’ların auto growth değerleri büyük chunk’lar halinde auto growth gerçekleşsin diye uygun bir değere (örneğin 512 MB ya da 256 MB) set edilir.

Her ne kadar data file’lara uygun bir başlangıç değeri atansa da plansız ya da problemli bir durumda data file’lar büyüme ihtiyacı duyabilir. Bu durum da data file’lardan biri büyütülecek ve önemli best practice’lerimizden biri olan “TempDB data file’larının eşit boyutta olması” best practice geçersiz duruma gelecektir. Bu problemin önüne geçmek için T1117 trace flag’i kullanılabilir. Bu trace flag aktif hale getirildiğinde bir file group içerisindeki data file’lardan herhangi biri auto growth’a girdiğinde sadece ilgili file değil file group içerisinde bütün data file’lar beraber büyütülecek ve dolayısıyla TempDB data file’larının aynı boyutta olma best practice’i korunmuş olacaktır. Yalnız unutulmaması gereken önemli bir nokta T1117 trace flag’i sadece TempDB’yi değil instance’da bulunan tüm database’leri etkilemektedir.

TempDB data file’larına başlangıç değeri ve auto growth değeri verme işlemlerini SSMS’ten TempDB özelliklerine girerek yapabilirsiniz.

TempDB File’larının Bulunduğu Storage

TempDB’nin yoğun bir şekilde kullanıldığı ortamlarda TempDB performansının overall SQL Server performansını nasıl etkileyeceğinden bahsettik. Bu yüzden TempDB file’larının diğer user database file’larından ayrı bir yerde ve sistemde bulunan en yüksek performanslı disklerde (örneğin SSD disklerde) bulundurulması önemli bir best practice’dir.

TempDB data file’larının nasıl taşınabileceği ile alakalı şu blog post’umu okuyabilirsiniz.

Yoğun TempDB Kullanımının Analiz Edilmesi

Şu ana kadar bahsettiğimiz tüm best practice’ler, TempDB üzerinde yapabileceğimiz konfigurasyon değişiklikleri ile performans artışını sağlamak amacıyla kurgulayabileceğimiz değişiklikler. Aslında belki de bu best practice’lerden daha önce TempDB’nin neden yoğun bir şekilde kullanıldığı ve gerçekten bu kadar yoğun bir şekilde kullanılıp kullanılmamasını analiz etmek olmalıdır. Çünkü çoğu sorguda şunu görüyoruz ki; yoğun temp table’lar kullanılarak yapılan işlemler aslında temp table kullanılmadan da yapılabilmektedir.

Bu yüzden eğer yukarıdaki best practice’leri yapmanıza rağmen hala allocation bit page contention’ları görmeye devam ediyorsanız TempDB’nin neden yoğun bir şekilde kullanıldığının analiz edilmesi ve gerekiyorsa kodda değişiklik yapılarak bu kullanımın azaltılması gerekmektedir. Hatta daha da ideali bu analizi best practice’leri yapmadan önce yapmaktır.

Tekrar belirtiyorum; sadece 1 adet TempDB’miz var ve ona gerçekten iyi bakmalıyız.

T1118 Trace Flag’i

SQL Server’da tüm yeni allocation’larda ilk 8 page’e kadar yapılan page allocation’ları mixed extend’ler üzerinden yapılmaktadır. Obje için alloce edilen alan 8 page’i geçtikten sonra gelen yeni page istekleri artık uniform extend olarak sağlanmaktadır. İlk 8 page için mixed extend’lerden allocation yapılırken her allocation’da PFS, GAM ve SGAM allocation bit page’lerin tamamına bakılır. Bu durumda 8 page’in allocation’i için SGAM page’e 8 defa erişilmektedir ve bu da SGAM (örneğin 2:1:3) üzerinde contention görülmesine sebebiyet verir.

T1118 trace flag’i aktif edildiğinde tüm extend allocation’ları uniform olarak verilir. Bu durumda SGAM üzerindeki contention aşılabilir.

Eğer yukarıdaki best practice’leri uygulamanıza ragmen hala allocation bit page contention’larına rastlıyorsanız T1118 trace flag’ini aktif etmeyi düşünebilirsiniz

ÖZET

TempDB performans açısından SQL Server için oldukça önem taşımaktadır. Instance başına 1 TempDB olduğu için TempDB’nin konfigurasyonu ve yönetimi oldukça önemlidir.

TempDB performansı için base olarak şu best practice’ler uygulanabilir.

  • TempDB kullanımının azaltılması (örneğin gereksiz temp table kullanımının kaldırılması)
  • TempDB file’larının diğer kullanıcı database’lerinden farklı ve en yüksek performanslı disklere konulması.
  • TempDB data file’larının arttırılması
    • Core CPU sayısı 8’den az ise Core CPU sayısı kadar
    • Core CPU sayısı 8’den fazla ise 8 data file oluşturulup contention görülmeye devam edildiği sürece 4’er 4’er data file eklenmesi.
  • TempDB data file’larına uygun bir başlangıç boyutu verilmesi
  • TempDB data file’larının boyutlarının aynı yapılması (Proportional Fill Algorithm)
  • Auto growth değerlerine uygun bir değer verilmesi (Örneğin; 256 MB ya da 512 MB)
  • Gerekirse T1118 trace flag’i aktif hale getirilerek extend allocation’ının uniform extend olarak yapılmasının sağlanması.
Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


SQL Server’da master,model ve tempdb database’lerinin owner’ı değiştirilmeye çalışılırsa aşağıdaki gibi bir hata alınabilir.

[more]

image

Cannot change the owner of the master, model, tempdb or distribution database.
(Microsoft SQL Server, Error: 15109)

master,model ve tempdb database’lerinin owner’ları SSMS’ten ya da aşağıdaki T-SQL kodu ile değiştirilemez.

use model
EXEC dbo.sp_changedbowner @loginame = N'sa'

 

Bu değişikliği yapmak için detach-attach ya da backup-restore yöntemlerinin kullanılması gerekir.

System Database’lerin de detach-attach ve backup-restore yöntemlerinin nasıl kullanıldığı ile alakalı aşağıdaki makalelerimi okumanızı tavsiye ederim.

SQL Server da Master DB Database File larını Taşımak

SQL Server – master database backup – restore

SQL Server 2008 – Model ve MSDB Database lerinin Taşınması

SQL Server da TempDB Database File larını Taşımak

 

İyi Çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Geçenlerde bir cluster ortamımıza SP2 upgrade geçerken karşımıza çıkan hata mesajından sonra tüm system database’lerinin owner’larını sa yapmaya karar verdik. Bu makalede üzerinde duracağımız sorgu ile owner’ı sa olmayan system database’lerini nasıl sorgulayabileceğimizi göreceğiz.

[more]

SP2 upgrade’in de karşımıza çıkan hata şu şekilde idi;

The database owner SID recorded in the master database differs from the database owner SID recorded in database 'msdb'. You should correct this situation by resetting the owner of database 'msdb' using the ALTER AUTHORIZATION statement.'

Hatanın sebebi master DB’si ile msdb DB’sinin owner’larının aynı olmaması. Bunun da nedeni daha önce msdb’i backup restore yapmış olmamız. Servisi single user modda açtıktan sonra msdb’nin owner’ı değiştirdik ve hatadan kurtulduk.

Bu hata ile karşılaştıktan sonra bütün ortamlarımızda owner’ı sa olmayan system database’lerini sorgulamaya karar verdik.

Aşağıdaki script’i SQL Server 2008 Registered Server üzerinden bütün SQL Server’larımızda uygulayarak çok kısa sürede sorgu sonucunu alabildik.

select dbid,name,sid,SUSER_SNAME(sid)
from sysdatabases d
where SUSER_SNAME(sid)<>'sa'
	and d.dbid<=4

 

Belki bir gün sizin de böyle bir şeye ihtiyacınız olur.

 

İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


SQL Server TempDB veritabanı yanlış yapılan join sorgularının order by clause ile beraber kullanımı ile plan dışı büyüyebilir. Bu büyüme disk size ı dolduracak kadar olursa servis kullanılmaz duruma gelebilir. Bugünkü yazımda TempDB boyutunu küçültme amaçlı yapılabilecek shrink operasyonlarını inceliyor olacağız. Bu kapsamda 3 farklı shrink metodu anlatıyor olacağım.

[more]

Anlatacağım 3 farklı shrink operasyonundan ilkinde “size” tamamen kontrolünüz altında olmakla beraber service restart ı gerektirmektedir. Diğer 2 yöntemde ise shrink komutları kullanılacak ve bu komutlar kullanılırken tempdb üzerinde bir action olmaması gerekmektedir.

Yöntemleri anlatırken örnekler üstünden gidiyor olacağız. Bu örnekler de aşağıdaki 2 script i kullanacağız.

TempDB File Boyutları Sorgusu (1.Script)

Bu sorgu ile TempDB veritabanı dosyalarının aktif boyutlarını sorgulayabiliriz. Değerler MB cinsindendir.

use tempdb
select (size*8)/1024 as FileSizeMB from sys.database_files

 

Örnek sorgu sonucu aşağıdaki gibidir.

res1

TempDB Boyutunu Arttırmak İçin Kullanılacak Script (2.Script)

Bu script ile AdventureWorks2008 de bulunan 6 tablo join clause kullanmadan birbirine bağlanacak ve cross join olması sağlanacaktır. Ayrıca order by clause kullanılacak ve order işlemi tempdb de yapılacağından dolayı tempdb boyutunun büyümesi sağlanacaktır. File larının boyutlarının yeteri kadar büyüdüğünü düşündüğünüzde sorgunun tamamlanmasını beklemeden kapatabilirsiniz.

use AdventureWorks2008
select * 
from Production.TransactionHistory
	,Production.TransactionHistoryArchive	
	,Sales.SalesOrderDetail
	,Production.WorkOrder
	,Production.WorkOrderRouting
	,Person.BusinessEntity
order by 1,2,3

Yöntem 1

  1. TempDB file larının şu anki boyutuna bakmak için 1.Script i çalıştıralım. 

    res1
  2. 2.Script i kullanarak tempdb file larının boyutlarını arttıralım.
  3. 1 nolu script i kullanarak tempdb file larının aldığı boyutu görelim. 

    res2
  4. SQL Server service ini Configuration Manager den stop edelim. 

    res3
  5. Command Prompt u kullanarak SQL Server service ini –c –f parametresi ile tekrar start edelim. Bu parametreler SQL Server ın minimum configuration lar la açılmasını sağlayacaktır. TempDB data file ı 1MB , log file ı 0.5MB olarak açılacaktır. 

    Res4
  6. SSMS üzerinden aşağıdaki sorguyu kullanarak TempDB dosyalarının boyutunu set edelim. Ben örneğimde data file ını 100 MB a, log file ını 10 MB a set edeceğim. 

    Res5
  7. Command Prompt ekranında CTRL+C ye basarak ekranı kapatalım ve Configuration Manager dan SQL Server Service ini start edelim.
  8. 1.Script i tekrar çektiğimizde Data file ın 100 Mb log file ın 10 MB olduğunu göreceğiz. 

    Res6

 

Yöntem 2  - DBCC SHRINKDATABASE

Bu yöntemde DBCC SHRINKDATABASE komutunu kullanarak tempdb veritabanını shrink edeceğiz. Bu komut “target_percent” adında bir parametre almaktadır. Bu parametre de shrink işlemi bitirildikten sonra file larda kalması planlanan boş yer miktarıdır. Örneğin parametreyi %10 olarak verirsek shrink işlemi bittikten sonra data ve log file larının içinde %10 arlık boş alanların bırakılmasını istediğimizi belirtmiş oluruz.

  1. 2 nolu script i kullanarak file boyutlarını tekrar arttıralım.
  2. 1 nolu script i kullanarak tempdb file larının aldığı boyutu görelim. 

    Res7
  3. SHRINKDATABASE komutu ile veritabanını shrink edelim.
    	
        DBCC SHRINKDATABASE (tempdb, 10)	
        
    Res8
  4. 1 nolu script i tekrar kullanarak file boyutlarını kontrol ettiğimizde boyutların düştüğünü göreceğiz. 

    Res9

 

Yöntem 3 - DBCC SHRINKFILE

DBCC SHRINKFILE ın DBCC SHRINKDATABASE den en büyük farkı DB bazında değil file bazında shrink yapabiliyor olmaktır. Bu şekilde bütün DB üzerinde shrink işlemi yapmaktansa sadece shrink yapılmak istenen file üzerinde çalışılabilir. SHRINKFILE da SHRINKDATABASE gibi parametre almaktadır. Yalnız bu sefer oran bazında değil MB bazında parametre kullanılır ve shrink yapılmak istenen file ın shrink işleminden sonra olması planlanan boyutu parametre olarak verilir.

  1. 2 nolu script i kullanarak file boyutlarını tekrar arttıralım.
  2. 1 nolu script i kullanarak tempdb file larının aldığı boyutu görelim. 

    Res10
  3. SHRINKFILE komutu ile primary file ı shrink edelim ve boyutunu 50 MB a getirelim.
    DBCC SHRINKFILE (tempdev, 50)

    Res11

  4. 1 nolu script i tekrar kullanarak file boyutlarını kontrol ettiğimizde primary file ın düştüğünü göreceğiz. 

    Res12

 

ÖZET

Bugünkü yazımda production ortamlarında sıklıkla başımıza gelebilecek olan TempDB database boyutunun artması sonucu bu boyutu nasıl düşüreceğimizi inceledik. 3 yöntemden size en uygun geleni kullanarak TempDB veritabanını shrink edebilirsiniz.

 

İyi Çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Bir önceki yazımda Model DB nin nasıl taşınacağını anlatmıştım. Bu yazıma http://www.turgaysahtiyan.com/post/SQL-Server-2008-e28093-Model-ve-MSDB-Database-lerinin-Tasc4b1nmasc4b1.aspx adresinden erişebilirsiniz.

Bu yazımda ise tavsiye edilen bu yöntemle taşıma yaparken almış olduğum “Cannot detach an opened database when the server is in minimally configured mode” hatasından ve çözümünden bahsediyor olacağım. [more]

Microsoft, model DB nin taşınması ile alakalı yayınladığı makalelerde SQL Server Service inin –m –c –T3608 parametresi ile açılması gerektiğini belirtmekte. Ne varki benim yaşadığım case de service i bu parametre ile açtıktan sonra model db yi detach ederken aşağıdaki gibi bir hata almakta ve detach yapamamaktaydım.

“Cannot detach an opened database when the server is in minimally configured mode”

Yaptığım araştırmalar sonucunda ilk olarak new query ekranında connection kurarken default database konusuna takıldım. Aynı hatayı alan bir kullanıcı bu hatanın sebebini; “Connection kuran kullanıcının default database i model ve use master ile başka bir database e geçilmesine rağmen hala model kullanılıyor” olarak yorumlamıştı. Dolayısıyla connection kurarken default database in değiştirilmesini önermişti.

Bu değişikliği connection kurarken login ekranında ki options a basınca açılan ekrandan yapabilmekteyiz.

image

Fakat benim case imde bu da işe yaramamıştı. Daha sonra araştırmalara devam ettiğimde service –s –m –T3608 flag i ile değil –f flag ı ile açma fikri ortaya çıktı.

Ve gerçekten de servis i –f ile başlattığımda model DB yi sorunsuz bir şekilde detach edip taşıyabildim :)

Bu arada ek bilgi vermek gerekirse –f flag ı servis i minimum configuration la açmakta ve single user mode olarak hizmet vermektedir. Dolayısıyla ekstradan –m parametresinin kullanılmasına gerek yoktur.

sqlservr.exe –f


Hayat kurtaran bu parametreyi açıkladıktan sonra yazıma son veriyorum :)

 

İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


System database leri ve System Database lerinin taşınması konu başlıklı yazı dizimin son makalesinide bugün yazıyorum. Bu makalemde Model ve MSDB Database lerinin nasıl taşınacağına bakıyor olacağız. Ayrıca model DB yi taşırken almış olduğum bir hataya ve bu hatanın olası çözümlerini inceliyor olacağız. [more]

Daha önceki System Database leri makalelerimde belirttiğim gibi system database lerinin normal database lere göre taşınma şekilleri farklıdır. Örneğin bir özet geçecek olursak master db özel bir flag açılarak backup-restore yapılabilirken, temp db nin taşınması için alter script i kullanılır.

Model ve MSDB database leri için ise özel bir flag ile servis açıldıktan sonra detach – attach komutları kullanılır.

System database leri ve system database taşımaları ile alakalı yazdığım diğer makalelere aşağıdaki linklerden erişebilirsiniz.

http://www.turgaysahtiyan.com/post/SQL-Server-System-Databases-%28Sistem-Veritabanlarc4b1%29.aspx
http://www.turgaysahtiyan.com/post/SQL-Server-da-Master-DB-Database-File-larc4b1nc4b1-Tasc4b1mak.aspx
http://www.turgaysahtiyan.com/post/SQL-Server-e28093-master-database-backup-e28093-restore.aspx
http://www.turgaysahtiyan.com/post/TempDB-Database-File-larc4b1nc4b1-Tasc4b1mak.aspx


Şimdi bugünkü konumuza yani model ve msdb database lerinin nasıl taşınacağı konusuna geri dönelim.

Yapılacak işlemleri adım adım anlatmak gerekirse;

  1. SQL Server Service i Configuration manager dan yada bir cluster ortamı kullanılıyorsa ClusterAdmin ekranından offline yapılır.
  2. Daha sonra comand prompt açılır ve sql server –m –c –T3608 parametresi ile çalıştırılır.

    ……./sqlservr.exe –c –m –T3608

    Burada –m parametresi single user mode u, –c parametreside command prompt tan en hızlı şekilde servisi başlatmayı ifade etmektedir.
  3. Servis istediğimiz modda açıldıktan sonra yeni bir query window vasıtasıyla model ve msdb yi detach – attach edebiliriz.
  4. Model i detach etmek için

        Use Master 
        GO 
        sp_detach_db ‘model’ 
        GO 
        
  5. Model DB data ve log file larını yeni yerlerine kopyalayalım.
  6. Model i restore etmek için

    Use Master 
    GO 
    Create Database model on 
        (FILENAME = ‘YeniDrive\model.mdf’) 
       ,(FILENAME = ‘YeniDrive\modellog.ldf’) 
    For Attach; 
    GO 
  7. 4,5 ve 6. Adımları MSDB içinde uygulayalım
  8. Command Prompt u CTRL + C ile durduralım ve daha sonra kapatalım.
  9. SQL Server Service ini start edelim.


Taşıma işlemlerimiz tamamlanmış durumda.

Yaptığımız değişiklikleri kontrol etmek için sys.sysaltfiles a select çekebiliriz.

Select DB_NAME(dbid),* 
from sys.sysaltfiles
where DB_NAME(dbid) in ('model','msdb')

 

Model DB yi taşırken aldığım hata için ayrı bir makale hazırlıyor olacağım.


İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Master database SQL server ın en önemli sistem database i olduğu için backup stratejimizi kesinlikle dahil etmeliyiz.

Makaleme devam etmeden önce sistem database leri ile alakalı yazdığım aşağıdaki yazılarımı okumanızı tavsiye ederim.

SQL Server Sistem Veritabanları
TempDB Database File larını Taşımak
Master DB Database File larını Taşımak

Dediğimiz gibi master DB SQL Server ın en core sistem database idir. Zarar gördüğü durumlarda SQL Server service i restart olamaz.

Peki master DB ne gibi bilgiler içerir. Aşağıda ufak bir özetine geçiyorum.

  • Login Nesneleri
  • Sistem Konfigurasyon Bilgileri
  • Endpoint ler
  • User Database Bilgileri
  • Servis in restart olması için gerekli bilgiler

Dolayısıyla master DB nin backup stratejisi içine dahil edilmesi çok yerinde bir hareket olacaktır. SQL Server da SQL Agent ile otomatik backup alma ile alakalı yazıma aşağıdaki linkten erişebilirsiniz.
SQL Server 2008 de Otomatik BackUp için Job Oluşturma (Schedule)

Bu arada not olarak verelim; master DB nin sadece full backup ı alınabilmektedir. Differential yada TLog backup ı alınamaz.

 

Master Database Full Backup


Master DB nin full backup ının alınması diğer User Database ler ile aynıdır. Örneğin aşağıdaki kod ile master DB nin backup ını c:\ root una alalım.

BACKUP DATABASE [master] TO DISK = N'c:\masterbackup.bak' 
GO

 

Master Database Restore


Master DB backup landıktan sonra master DB yi etkileyen bazı değişiklikler yapıldıysa bunlar ne yazık ki restore edilemeyecektir.

Örneğin Backup tan sonra user create edildiyse Restore işleminden sonra bu user lar tekrar create edilmesi gerekmektedir.

Yada Database create edildiyse restore işleminden sonra bu database leri attach etmek en mantıklı yoldur. Eğer bu konuda bilgi almak isterseniz Attach işlemi ile alakalı yazımı okuyabilirsiniz.

Şimdi master DB nin restore adımlarına bakalım

  1. Service i stop ediniz. Bunun için command prompt ta aşağıdaki kodu kullanabilirsiniz.
    net stop MSSQLSERVER
  2. Service i single-user mode da start ediniz.
    Default instance için command prompt ta aşağıdaki kodu kullanabilirsiniz.
    sqlservr.exe –m
    Named instance için ise command prompt ta aşağıdaki kodu kullanabilirsiniz.
    sqlservr.exe -m -s <instancename>
  3. Şimdi restore işlemini yapacağız. Bu işlemi sqlcmd ile command prompt tan gerçekleştireceğiz. Aşağıdaki SQL script i command prompt ta sqlcmd den sonra çalıştırınız.
    SQLCMD
    RESTORE DATABASE master FROM DISK = 'c:\masterbackup.bak' WITH REPLACE;
    GO
  4. Restore işleminden sonra SQL Server service i stop olup sqlcmd nin çalıştığı ekran kapanacaktır.
  5. SQL Server ı start edin
    net start MSSQLSERVER


İyi Çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Bir önceki yazımda SQL Server daki system table larının ne olduklarını ve ne amaçla kullandıklarını anlatmıştım. Bu yazıma aşağıdaki linkten erişebilirsiniz.
http://www.turgaysahtiyan.com/post/SQL-Server-System-Databases-(Sistem-Veritabanlarc4b1).aspx

TempDB Database File larını Taşımak makalemde de bahsettiğim gibi system DB lerinin taşınması normal user DB lerinin taşınmasından farklıdır.

Bu yazımda da master DB nin database file larının nasıl taşınacağını adım adım anlatıyor olacağım.

  1. Start >> Programs >> Microsoft SQL Server 2008 >> Configuration Tool >> SQL Server Configuration Manager ı açalım.
    image
  2. Master DB sinde değişiklik yapmak istediğimiz SQL Server instance ı sağ tıklayıp properties e basalım. (örneğin SQLServer(MSSQLServer)
    image
  3. Advanced tab ında Startup Parameters te oynama yapacağız.
    image
  4. Startup parameters deki yazı herhangi bir değişiklik yapılmadıysa aşağıdaki gibidir.

    -dC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf;
    -eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;
    -lC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

    Master DB nin Data ve Log file larını C:\DATA folder ına taşıdığımızı düşünürsek startup parameters de ki yazı aşağıdaki gibi olmalıdır.

    -dC:\DATA\master.mdf;
    -eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG;
    -lC:\DATA\mastlog.ldf

    Bu yazıyı alıp startup parameters e paste edip apply diyerek ekranı kapatalım.
  5. Configuration Manager üzerinden SQL Server Service i stop edelim.
  6. Master DB nin data ve log file larını (master.mdf ve mastlog.ldf) bulunduğu yerden alıp C:\Data folder ına taşıyalım.
  7. Configuration Manager dan SQL Server service ini tekrar start edelim.
  8. Kontrol amaçlı Management Studio üzerinden aşağıdaki sorguyu çalıştıralım.
    SELECT name, physical_name AS CurrentLocation, state_desc
    FROM sys.master_files
    WHERE database_id = DB_ID('master');
    GO
  9. Aşağıdaki gibi bir sonuç almamız beklenmektedir.
    image


Bir sonraki makalemde görüşmek üzere.

İyi çalışmalar

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


SQL Server da bulunan Sistem Database leri ve kısa açıklamaları aşağıdaki gibidir.

Sistem Database

Açıklama

master

SQL Server ile alakalı server – level bilgileri tutar.

msdb

SQL Server Agent tarafından kullanılan bu db; alert ve job lar ile alakalı bilgileri tutar

model

Model DB si SQL Server da yeni oluşturulacak DB lerin template idir. Msdb üzerinde database size,collation,recovery model gibi bilgiler değiştirilerek yeni oluşturulan her DB nin bu bilgilerle oluşturulması sağlanabilir.

resource

SQL Server sistem obje leri tutan Resource DB si bir read-only DB dir. Sistem obje leri fiziksel olarak resource db de tutulurken mantıksal olarak her DB nin içerisinde gösterilir.

tempdb

TempDB temporary obje leri tutar. Her SQL Service in restart ında tekrar create edilir.


Sistem DB lerine Management Studio üzerinden aşağıdaki şekilde erişebilirsiniz.

image

Gördüğünüz gibi bu listede resource db bulunmamakta. Daha öncede bahsettiğimiz gibi obje ler fiziksel olarak resource db de tutulmaktadır. Bu objeler mantıksal olarak her db nin içerisinde gösterilmektedir.

Sistem DB lerine script ile ulaşmak için aşağıdaki kodu kullanabilirsiniz.

select * from sys.databases where database_id<=4

 

image

Sistem database leri SQL Server kurulumu ile beraber gelmektedir. Ve her SQL server instance ında sistem database leri query sonucunda görülen database_id leri almaktadır.

Sistem DB lerine daha detaylı incelemeye devam ediyoruz.


master Database

master Database SQL Server ile alakalı server-level bilgileri tutar. Örneğin loginler, backup device lar, linked server lar ve Endpoint gibi bilgiler master DB de tutulmaktadır. Ayrıca sistem de bulunan user database bilgileri ve bu database lerin file bilgileride master DB de tutulmaktadır. Dolayısıyla eğer master DB erişilemez durumdaysa SQL Server açılamaz durumda olur. Bundan dolayı master DB nin sıklıkla backuplanması önerilmektedir. Bu arada ufak bir bilgi vermek istiyorum. Master DB nin sadece full backup ı alınabilir, diff yada tlog backup ı alınamaz.

Master db ile alakalı MSDN de bulunan öneriler aşağıdaki gibidir.

  • Master DB periyodik olarak backup lanmalıdır.
  • Aşağıdaki işlemlerden herhangi biri yapıldığında en kısa sürede master db nin back up ı alınmalıdır.
    • Her hangi bir DB oluşturulduğunda, değiştirildiğinde yada silindiğinde.
    • Server yada DB configuration ları değiştirildiğinde.
    • Logon account larında değişiklik yapıldığında yada yeni bir login eklendiğinde.
  • Master DB içerisinde user object create edilmesi önerilmez.
  • Master DB nin TRUSTWORTHY özelliğinin ON yapılması önerilmez.


msdb Database


SQL Server agent tarafından kullanılan bu db; alert ve job lar ile alakalı bilgileri tutar.

Örneğin job history için aşağıdaki query i msdb db sinde çalıştırabilirsiniz.

select job_name, run_datetime, run_duration
from
(
    select job_name, run_datetime,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
    from
    (
        select DISTINCT
            j.name as job_name, 
            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +  
                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
        from msdb..sysjobhistory h
        inner join msdb..sysjobs j
        on h.job_id = j.job_id
    ) t
) t
order by job_name, run_datetime

 

model Database


Model DB si SQL Server da yeni oluşturulacak DB lerin template idir. Msdb üzerinde database size,collation,recovery model gibi bilgiler değiştirilerek yeni oluşturulan her DB nin bu bilgilerle oluşturulması sağlanabilir. TempDB her SQL Server restart ında create edildiği için, model DB SQL Server instance ında her zaman vardır.


tempDB Database


tempDB, local temporary tables, temporary stored procedures, table variables ve cursor gibi temp objeleri tutan database dir. Ayrıca result set te yapılan sorting(sıralama) işlemi tempDB de yapılmaktadır.

tempDB, SQL Server ın performans lı çalışması için en önemli etkenlerden biridir. En base olarak tempDB nin data ve log file larının diğer database lerden ayrı bir diskte, hatta mümkünse data ve log file larının farklı disklerde tutulması best practise dir. Ayrıca tempDB data file ının core işlemli sayısı kadar file dan oluşması ve her birinin eşit boyutta verilmesi diğer bir best practise adımıdır.

tempDB data file larının küçük değerlerde olmasının şöyle bir dezavantajı vardır. Örneğin çok kayıtlı bir result set execute edilidğinde tempDB data file ı bu sonucu allocate edecek boyutta değil ise auto growth olması gerekmektedir. Bu da performans a negatif etki yapmaktadır.

TempDB Data ve Log file larının taşınması ile ilgili yazıma aşağıdaki url den erişebilirsiniz.
http://www.turgaysahtiyan.com/post/TempDB-Database-File-larc4b1nc4b1-Tasc4b1mak.aspx



Makaleme burada nokta koyarken sistem DB leri hakkında yeteri kadar bilgiye ulaştığınızı umuyorum.

Bir sonraki makalemizde görüşmek üzere.

İyi çalışmalar,

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


TempDB System Database nin file larını taşımak normal User DB lerini taşımaktan farklıdır.

Normal User DB leri Backup-Restore veya Detach-Attach komutları ile taşınabiliyorken TempDB Database File ları bu yöntem ile taşınamaz.

Böyle bir taşıma için aşağıdaki script i kullanabilirsiniz.

USE master
GO
ALTER DATABASE tempdb 
	MODIFY FILE (NAME = tempdev, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TempDBFiles\Data\tempdb.mdf')
GO
ALTER DATABASE tempdb 
	MODIFY FILE (NAME = tmpf2, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TempDBFiles\Data\tmpf2.ndf')
GO
ALTER DATABASE tempdb 
	MODIFY FILE (NAME = tmpf3, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TempDBFiles\Data\tmpf3.ndf')
GO
ALTER DATABASE tempdb 
	MODIFY FILE (NAME = templog, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\TempDBFiles\Data\templog.ldf') 

 

Taşıma işleminden sonra SQL Service ini restart etmeniz yeterli olacaktır.

Turgay Sahtiyan

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan