SQL Server Version, Service Pack, Edition ve Cluster Bilgilerilerini Alma

SQL Server'ınızın build number'ının kaç olduğunu, hangi service pack'ın ve hangi sürümün kurulu olduğu ile birlikte cluster olup olmadığını aşağıdaki sorgu ile öğrenebilirsiniz:

SELECT SERVERPROPERTY('productversion') As Version,
SERVERPROPERTY ('productlevel') As ServicePack,
SERVERPROPERTY ('edition') As Edition,
SERVERPROPERTY('IsClustered') As isClustered

SQL ve Performans Üzerine

Bu gece twitter'da T-SQL Code Review da performans ile ilgili bir kaç hususu 160 karakter ile arzı endam eyledik, merak eden zevat bunları aşağıda bulabilir:

• T-SQL de WHERE koşulları eşitsizlik üzerine kurulu geliştiriciye eşittir i aşılamak gerekir.Zira tuttuğu yolun eşiti çıkmaz.
• Cross Joini Inner Joine dönüştüremeyen geliştiricinin tüm yolları CROSS dur, table/index SCAN dan bir türlü başını SEEK e çeviremez.
• SELECT * ı alışkanlık haline getirmiş bir geliştirici Network u kendisine tahsis edilmiş Formula 1 pisti zanneder. Kodu trafiği alt üst eder!
• İyi bir SQL ci az kod ile çok şey alabilendir; çok şey ile az kaynak tüketendir; veriyi disk yerine cache den okutabilendir.
• Kodu ORDER BY a alışmış olan kişileri ORDER BY (hizaya çekmek) yapmak zordur.
• Çok büyük kayıtlara sahip Temp Table kullanma hastalığına yakalanmış bir geliştiriciyi Physical Table Hastanesinde tedavi etmek gerekir.
  
  

LOGINPROPERTY

Login policy ayarları hakkında bilgi verir.


Syntax:
LOGINPROPERTY ( 'login_name' , 'property_name' )

Parametreler:
login_name: Hakkında bilgi alınacak SQL Server login adı
propertyname: Login için bazı özellik bilgileri geri döndürür. Propertyname aşağıdaki değerlerden birini olabilir:

BadPasswordCount : Yanlış bir parola ile girişlerin ardışık denemelerinin sayısını döndürür.

BadPasswordTime : Yanlış bir parola ile giriş yapmak için son girişim zamanını döndürür.

DaysUntilExpiration : Parolanın süresi kadar gün sayısını döndürür.

DefaultDatabase : Kullanıcının giriş için default veritabanını döndürür.

DefaultLanguage : Kullanıcının giriş için varsayılan dilini döndürür.

HistoryLength : Şifre politikası uygulama mekanizmasını kullanarak, giriş için izlenen parola sayısını döndürür. 0: Password Policy uygulanmıyorsa. 1: Password Policy uygulanıyorsa.

IsExpired : Oturum açma süresinin dolup dolmadığı hakkında bilgi verir.

IsLocked : Loginin kilitli olup olmadığı hakkında bilgi verir.

IsMustChange : Bir sonraki girişte şifrenin değiştirilmesinin gerekip gerekmediğini hakkında bilgi verir.

LockoutTime : Lock tarihini geri döndürür.

PasswordHash : Şifrenin hash halini geri döndürür.

PasswordLastSetTime : Geçerli parolanın set edildiği tarihi döndürür.

Not: Login üzerinde VIEW yetkisini gerektirir. Password Hash için CONTROL SERVER yetkisini gerektirir.

Örnek-1: Mehmet kullanıcısının bir sonraki oturumda şifresinin değiştirilmesinin zorunlu olup olmadığını kontrol edelim

SELECT LOGINPROPERTY('mehmet', 'IsMustChange');

Örnek-2: Mehmet kullanıcısının lock olup olmadığını kontrol edelim

SELECT LOGINPROPERTY('mehmet', 'IsLocked');

Örnek-3: Lock olan tüm kullanıcıların listesini aşağıdaki T-SQL ile alabilirsiniz:

select name
from sys.server_principals
where loginproperty(name, 'IsLocked') =1

Aktif Tüm SQL Joblarını Disable Etme

Geçenlerde bir veritabanı sunucusunda bulunan tüm aktif jobları pasif yapma ihtiyacım oldu. Bu ihtiyacımı aşağıdaki gibi T-SQL kodu yazarak giderdim:
declare @cmd varchar(max)=''

select @cmd=@cmd + 'exec msdb.dbo.sp_update_job @job_id=N' + '''' + cast(job_id as varchar(36)) + '''' + ', @enabled=0' + char(13)+char(10)+ 'go' + char(13) + char(10)
from msdb.dbo.sysjobs
where enabled=1

exec(@cmd)

Umarım bu T-SQL kodu sizin de işinize yarar.

-- Kodlar SQL Server 2008  ile test edilmiştir. Koddaki değişken atamaları SQL Server 2008 ve sonrası sürümleri gerektirir.

Unused Tables Since SQL Server Restarted

SQL Server servisinin en son restart edildiği zamandan itibaren kullanılmayan tabloları veren script aşağıdaki gibidir:

select object_schema_name(t.object_id) + '.' + t.name as TableName
from sys.dm_db_index_usage_stats i right outer join
sys.tables t on (t.object_id = i.object_id)
group by object_schema_name(t.object_id) + '.' + t.name
having sum(isnull(i.user_seeks,0)+isnull(i.system_seeks,0)+isnull(i.user_scans,0)+ isnull(i.system_scans,0)
+ isnull(i.user_lookups,0) + isnull(i.system_lookups,0)+ isnull(i.user_updates,0)+ isnull(i.system_updates,0))=0
order by object_schema_name(t.object_id) + '.' + t.name

SQL Server 2012 - T-SQL Paging

SQL Server 2012 ile birlikte gelen güzel özelliklerden birisi de T-SQL'de paging yapısı. Paging  ile istediğimiz sıradaki yada sıralar arasındaki kayıtları listeleyebileceğiz. Dilersek bu sıraları parametrik hale de getirebileceğiz.

Örnek-1: 10.kayıttan sonraki tüm kayıtları aşağıdaki T-SQL kodu ile alabiliriz.:

SELECT PersonID, FirstName, LastName
FROM Person
ORDER BY PersonID
OFFSET 10 ROWS

Örnek-2: 10  ile 20. kayıtlar arasındakı kayıtları aşağıdaki T-SQL kodu ile alabiliriz:

SELECT PersonID, FirstName, LastName
FROM Person
ORDER BY PersonID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

Syntax:

ORDER BY order_by_expression
[ COLLATE collation_name ]
[ ASC | DESC ]
[ ,...n ]
[ ]


 
 ::=
{
OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
[
FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
]
}

Yararlı DBCC Komutları - Clear SQL Cache

-- SQL deki procedure cache'lerini silme
DBCC FREEPROCCACHE;

-- Cache'den özel bir sql plan silme
DBCC FREEPROCCACHE (0x060006001ECA270EC0215D05000000000000000000000000);
/*
Bir sql plan bozulduğunda ve başınızı ağrıttığında tüm cache silmeden ilgili bozuk cache silmek oldukça işinize yarayacaktır. Saniyede 1000 transaction alan bir sql planınızın bozulduğunu düşünün. Veritabanı sisteminiz alt üst olmadan ve tüm cache sıfırlamadan terayağından kıl çeker gibi işi zahmetsizce yapabileceğiniz yararlı bir dbcc komutu.
*/

-- Ad-hoc SQL planlarını silme
DBCC FREESYSTEMCACHE('SQL Plans');

Tüm Kullanıcı Veritabanlarının Full Backup'ını Alma

/*
Tüm kullanıcı veritabanlarınızın full ve compress backup'ını tek seferde aşağıdaki T-SQL kodu ile alabilirsiniz.
*/

use master
go
declare @db_name nvarchar(128),
@stmt varchar(8000)

declare crdatabase insensitive cursor for
select name from sys.databases
where database_id>4

open crdatabase fetch from crdatabase into @db_name
while (@@fetch_status =0)
begin
set @stmt= 'BACKUP DATABASE ['+ @db_name +'] TO DISK = N''D:\' + @db_name + '.bak'' WITH NOFORMAT, INIT, NAME = N''Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,COMPRESSION, STATS = 10'
exec(@stmt)

fetch next from crdatabase into @db_name
end
close crdatabase
deallocate crdatabase

--Not: Kodlar SQL Server 2008 ile test edilmiştir.

Last Update Index Statistics

Son 30 gündür istatistiği güncellenmeyen indexlerin istatistiğini güncelleyecek SQL kodu aşağıdaki gibidir:

select 'update statistics [' + object_schema_name(s.object_id) + '].[' + object_name(s.object_id) + '] '+ s.name
+ ' with fullscan --Rows:' + cast(p.rows as varchar) + ' Last Update:'+ convert(varchar(16),stats_date(s.object_id, s.stats_id) ,112)+ '
go'
from sys.stats s inner join sys.partitions p on s.object_id=p.object_id and s.stats_id=p.index_id
where stats_date(s.object_id, s.stats_id) < getdate()-30 and s.name not like '_WA%' and object_schema_name(s.object_id)<>'sys'
and p.rows>1000000
and p.partition_number=1
order by p.rows

Açıklama satırı olarak eklenen kayıt sayısı ve index istatistiğinin en son güncellendiği tarih bilgi vermek açısından yararlı olacaktır.

Cross-Database Dependencies

select db_name() as current_db_name,
object_name(referencing_id) as object_name ,
upper(coalesce(sed.referenced_server_name, '')) as referenced_server_name,
sed.referenced_database_name,
sed.referenced_schema_name,
sed.referenced_entity_name
from sys.sql_expression_dependencies as sed
where referenced_database_name <> db_name()
and referenced_database_name <> 'msdb'
order by upper(referenced_server_name)

Gizli Sonsuz Döngü

Diyelim ki parametre olarak verilen bir string’i 16 karaktere tamamlama ihtiyacımız var ve aşağıdaki gibi sp’mizi yazdık:

create proc FixedLength
@InputStr varchar(16)
as
begin
set nocount on
while len(@InputStr) < 16
begin
set @InputStr = @InputStr + ' '
end
select @InputStr
end
go

Gayet basit bir şekilde, @InputStr’nin uzunluğu 16 karakterden küçük olduğu sürece @InputStr değişkeninin sonuna boşluk karakteri ekleniyor. Gayet masumane gibi gözükse de sp’mizi aşağıdaki gibi çalıştırdığımızda sonsuz bir döngünün fitilini ateşlemiş oluruz:

exec FixedLength '123456';

LEN fonksiyonu; karakter kümesinin sağındaki boşlukları kale almaz, yani bir nevi RTRIM yapar. len(@InputStr) değeri sürekli 6 olarak döndüğü için sonsuz döngüye girer. Sorunun çözümü için hemen datalength fonksiyonunu kullanalım diyenleri duyar gibiyim Evet, datalength fonksiyonu bu sp’miz için çözüm olur fakat @InputStr varchar(16) yerine nvarchar(16) olsaydı sp sonsuz döngüye girmezdi ama istediğimiz sonucu da alamazdık.(@InputStr='123456 ' olduğunda datalength(@InputStr) değeri 16 olur ve döngüden çıkar fakat elde etmek istediğimiz '123456 ' sonucunu alamamış olurduk). Bir değişken nvarchar olduğunda her karakter için 2 byte yer kaplar. Datalength fonksiyonu nvarchar olarak tanımlanan bir değişkenin karakter uzunluğunu karakter sayısının 2 katı olarak verir.

Not: While döngüsü kullanmadan daha kısa kod ile işimizi halledebiliriz diyenler için aşağıdaki kısa kod işimizi görür ama sonsuz döngü örneğimiz için işimize yaramaz:)

create proc FixedLength
@InputStr varchar(16)
as
begin
set nocount on
set @InputStr=@InputStr + replicate(' ',16-len(@InputStr))
select @InputStr
end
go

DBCC SHOWCONTIG

Tablo ve viewlarda bulunan veri ve indexlere ait fragmantasyonları aşağıdaki kod ile görebilirsiniz:

dbcc showcontig('TabloAdi') with ALL_INDEXES,TABLERESULTS , FAST

T-SQL Best Practices

1. select cümlelerinizde muhakkak field isimlerini açık bir şekilde yazın; select * kullanmaktan kaçının
2. Mecbur kalmadıkça UNION kullanmayın. UNION yerine UNION ALL kullanmak daha performanslıdır.
3. Yine mecbur kalmadıkça DISTINCT i kullanmayın. Çünkü DISTINCT ın yaptığı sıralama ve çift kayıtları tek e indirmektir.
4. sp içerisinde tek bir kayıt döndürecekseniz bu kaydı select ile döndürmek yerine OUTPUT parametresi olarak verin.
5. Zorunlu olmadıkça ORDER BY ifasesinden kaçının

Login Failedleri Alma

/*
Son bir günde alınan login failed leri aşağıdaki SQL kodları ile alabilirsiniz.
*/

CREATE TABLE #tmploginfailed (
LogDate smallDATETIME,
ProcessInfo NVARCHAR(50),
[Text] NVARCHAR(MAX))
go

insert into #tmploginfailed
EXEC xp_readerrorlog 0,1,'login failed','login failed'
go

select cast(cast(LogDate as varchar(12)) as smalldatetime) as Date ,Text,count(1) as AttemptCount
from #tmploginfailed
where LogDate>=getdate()-1
group by cast(cast(LogDate as varchar(12)) as smalldatetime),text
order by count(1) desc,cast(cast(LogDate as varchar(12)) as smalldatetime)
go

drop table #tmploginfailed
go

Tablolardaki Kayıt Sayısını ve Fiziksel Büyüklüğü Bulmak

Veritabanınızda bulundan tüm tabloların kayıt sayısını ve bu tabloların fiziksel olarak kapladığı yeri KB cisinden aşağıdaki T-SQL cümlecikleri ile bulabilirsiniz:

SET NOCOUNT ON

IF EXISTS(SELECT * FROM TempDb.dbo.SysObjects WHERE NAME = '##Space_Used') DROP TABLE ##Space_Used

CREATE TABLE ##Space_Used (
name nvarchar(128),
rows char(11),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18)
)

DECLARE @User_Table_Name varchar(200)

DECLARE User_Tables_Cursor CURSOR FOR
SELECT Name
FROM Dbo.SysObjects
WHERE XTYPE = 'U'

OPEN User_Tables_Cursor

FETCH NEXT FROM User_Tables_Cursor INTO @User_Table_Name
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO ##Space_Used
EXEC sp_spaceused @User_Table_Name

FETCH NEXT FROM User_Tables_Cursor INTO @User_Table_Name
END

CLOSE User_Tables_Cursor
DEALLOCATE User_Tables_Cursor

SELECT
TableName=LEFT(Name, 50),
"RowCount"=Rows,
Phys_Size_KB = CONVERT(int, LEFT(Reserved, PATINDEX('% KB', Reserved) - 1))
FROM ##Space_Used
ORDER BY Phys_Size_KB DESC

IF EXISTS(SELECT * FROM TempDb.dbo.SysObjects WHERE NAME = '##Space_Used') DROP TABLE ##Space_Used

Kaynak: SQLServerCentral

Trigger İle Insert, Update, Delete Yakalama

/*
Trigger içerisinde aşağıdaki T-SQL kod ile bir kaydın Insert, Update, Delete olduğunu yakalayabilirsiniz.
*/

DECLARE @IfDelete VARCHAR (1), @IfInsert VARCHAR (1)

SET @IfDelete =
(CASE WHEN EXISTS(SELECT * FROM inserted) THEN 'O' -- (O)ld record in update
ELSE 'D' -- (D)eleted record
END)

SET @IfInsert =
(CASE WHEN EXISTS(SELECT * FROM deleted) THEN 'N' -- (N)ew record in update
ELSE 'I' -- (I)nserted record
END)

Seneler önce yazılan bu kodun daha optimize edilmiş hali değerli arkadaşım Levent YILDIZ'dan geldi:

if exists (select * from inserted) and exists (select * from deleted)
select @type = 'U'
else if exists (select * from inserted)
select @type = 'I'
else
select @type = 'D'

SQL Server 2005'te CLR Enable

SQL Server 2005 e CLR default olarak OFF gelir. Aşağıdaki T-SQL kodu ile CLR'ı enable yapabilirsiniz:

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO

Aynı şekide CLR yetkisini tekrardan OFF konuma getirmek için aşağıdaki kodu kullanabilirsiniz:

EXEC sp_configure 'clr enabled', 0;
RECONFIGURE WITH OVERRIDE;
GO

Tüm bu değişiklikleri görsel olarak ta Başlat-> Programlar-> MS SQL 2005->Configuration Tools ->SQL Surface Area Configuration adımından da değiştirebilirsiniz(MSSQLServer->Database Engine->CLR Integration).

Suspect Moddaki SQL Server Veritabanını Kurtarma

Restore SQL Server 2005 Suspect Database

Birkaç gün önce suspect olmuş bir SQL Server 2005 veritabanını kurtarmak için epey uğraştım. Kurtarmaya çalıştığım veritabanı replikasyon için oluşturulan Distribution veritabanı idi. Ne veritananına erieşbiliyordum ne de üzerinde birazdan bahsettiğim komutları çalıştırabiliyordum. Data dosyası CRC hataları ile doluydu ve tüm uğraşlarım sonucu replikasyon ayarlarını uçurup yeniden yapmak zorunda kaldım:( Benzer durumlar ile karşılaşacak arkadaşlar aşağıdaki komutları denemeden veritabanınızı gözden çıkarmayın.

Suspect durumuna düşmüş bir SQL Server veritabanınızı; veri kayıplarını da gözönüne alarak aşağıdaki komutları ard arda çalıştırarak kurtarabilirsiniz:

EXEC SP_RESETSTATUS 'VeritabaniAdi';

ALTER DATABASE VeritabaniAdi SET EMERGENCY

DBCC CHECKDB('VeritabaniAdi')

ALTER DATABASE VeritabaniAdi SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CHECKDB ('VeritabaniAdi', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE VeritabaniAdi SET MULTI_USER

T-SQL Kod Kaynağı: CodeProject

Change Logical File Name of Database

Veri tabanınızı oluştururken veritabanınızın mantıksal dosya adında yanlışlık yaptınız. Bu yanlışlığı her gördüğünüzde canınız mı sıkılıyor, içiniz mi daralıyor. Bunu hiç kendinize dert etmeyin ve mantıksal dosya adınızı değiştirip paşalar gibi rahatlayın:). Mantıksal dosya adı değişikliğini T-SQL sayesinde aşağıdaki kod ile yapabiliriz:

ALTER DATABASE VeritabaniAdi MODIFY FILE(NAME=DosyaAdi, NEWNAME=YeniDosyaAdi)

Örnek:
ALTER DATABASE Gunluk MODIFY FILE(NAME=Mehmet_Guzel, NEWNAME=MehmetGuzel)

Dipnot:
Aynı mantıkla log dosyalarınızın mantıksal adını da değiştirebilirsiniz.

FileGrowth'u Değiştirme

ALTER DATABASE VeritabaniAdi MODIFY FILE( NAME=DosyaAdi, FileGrowth = 5%)

/*
İlgili alanları kendi veritabanınıza göre değiştirip FileGrowth = 5% kısmını istediğiniz gibi ayarlayabilirsiniz. Dilerseniz FileGrowth u MB cinsinden de verebilirsiniz.
*/