2009-08-30

SQL Server Sistem Collation'ını Değiştirme

Komut satırından aşağıdaki komut yardımıyla SQL Server'ınızın sistem collation'ını değiştirebilirsiniz:

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=sa / SAPWD=Passw0rd /SQLCOLLATION=DegistirilecekCollation

Not: master system veritabanı değiştiği için tüm login, yetkiler vs bilgiler uçar(benden uyarması:))!

2009-08-25

SQL Server ile Bilgisayar Adını Bulma

SQL Server üzerinden xp_getnetname sp'ü ile server ismini alabilirsiniz. Bu sp yararlı bir undocumented extended sp olup; aşağıdaki gibi kullanabilirsiniz:

EXEC master.dbo.xp_getnetname

2009-08-19

Disklerdeki Boş Yer Büyüklüğünü Bulma

Server'ınıza bağlı disklerdeki boş yeri SQL Server üzerinden aşağıdaki sp ile bulabilirsiniz:

EXEC master.dbo.xp_fixeddrives

2009-08-16

Undocumented System Views

SQL Server 2005'te dökümante edilmemis sistem view'ları aşağıdaki gibidir:

Base Table Description
sys.sysschobjs : Exists in every database. Each row represents an object in the database.
sys.sysbinobjs : Exists in every database. Contains a row for each Service Broker entity in the database. Service Broker entities include the following:
Message type Service contract
Service The names and types use binary collation that is fixed.

sys.sysclsobjs : Exists in every database. Contains a row for each classified entity that shares the same common properties that include the following:
Assembly
Backup device
Full-text catalog
Partition function
Partition scheme
File group
Obfuscation key

sys.sysnsobjs : Exists in every database. Contains a row for each namespace-scoped entity. This table is used for storing XML collection entities.
sys.syscolpars : Exists in every database. Contains a row for every column in a table, view, or table-valued function. It also contains rows for every parameter of a procedure or function.
sys.systypedsubobjs : Exists in every database. Contains a row for each typed subentity. Currently, only parameters for partition function fall into this category.
sys.sysidxstats : Exists in every database. Contains a row for each index or statistics for tables and indexed views
Note:
Every index (except heap) is associated with a statistic that has the same name as the index.
sys.sysiscols : Exists in every database. Contains a row for each persisted index and statistics column.
sys.sysscalartypes : Exists in every database. Contains a row for each user-defined or system type.
sys.sysdbreg : Exists in the master database only. Contains a row for each registered database.
sys.sysxsrvs : Exists in the master database only. Contains a row for each local, linked, or remote server.
sys.sysrmtlgns : This system base table exists in the master database only. Contains a row for each remote login mapping. This is used to map incoming logins that claim to be coming from a corresponding server to an actual local login.
sys.syslnklgns : Exists in the master database only. Contains a row for each linked login mapping. Linked login mappings are used by remote procedure calls and distributed queries that emanate from a local server out to a corresponding linked server.
sys.sysxlgns : Exists in the master database only. Contains a row for each server principal.
sys.sysdbfiles : Exists in every database. If the column dbid is zero, the row represents a file that belongs to this database. In the master database, the column dbid can be nonzero. When this is the case, the row represents a master file.
sys.sysusermsg : Exists in the master database only. Each row represents a user-defined error message.
sys.sysprivs : Exists in every database. Contains a row for each database- or server-level permission.
Note: Server-level permissions are stored in the master database.
sys.sysowners : Exists in every database. Each row represents a database principal.
sys.sysobjkeycrypts : Exists in every database. Contains a row for each symmetric key, encryption, or cryptographic property associated with an object.
sys.syscerts : Exists in every database. Contains a row for each certificate in a database.
sys.sysasymkeys : Exists in every database. Each row represents an asymmetric key.
sys.ftinds : Exists in every database. Contains a row for each full-text index in the database.
sys.sysxprops : Exists in every database. Contains a row for each extended property.
sys.sysallocunits : Exists in every database. Contains a row for each storage allocation unit.
sys.sysrowsets : Exists in every database. Contains a row for each partition rowset for an index or a heap.
sys.sysrowsetcolumns : Exists in every database. Contains a row for each column, of partition rowset.
sys.syshobts : Exists in every database. Contains a row for each heap or B-tree (HOBT) that can host multiple rowsets
sys.syshobtcolumns : Exists in every database. Contains a row for each column of a HOBT.
sys.sysserefs : Exists in every database. Contains a row for each general storage engine metadata reference.
sys.sysrowsetrefs : Exists in every database. Contains a row for each index to rowset reference.
sys.syslogshippers : Exists in the master database only. Contains a row for each database mirroring witness.
sys.sysremsvcbinds : Exists in every database. Contains a row for each remote service binding.
sys.sysconvgroup : Exists in every database. Contains a row for each service instance in Service Broker.
sys.sysxmitqueue : Exists in every database. Contains a row for each Service Broker transmission queue.
sys.sysdesend : Exists in every database. Contains a row for each sending endpoint of a Service Broker conversation.
sys.sysdercv : Exists in every database. Contains a row for each receiving endpoint of a Service Broker conversation.
sys.sysendpts : Exists in the master database only. Contains a row for each endpoint created in the server.
sys.syswebmethods : Exists in the master database only. Contains a row for each SOAP-method defined on a SOAP-enabled HTTP endpoint that is created in the server.
sys.sysqnames : Exists in every database. Contains a row for each namespace or qualified name to a 4-byte ID token.
sys.sysxmlcomponent : Exists in every database. Each row represents an XML schema component.
sys.sysxmlfacet : Exists in every database. Contains a row for each XML facet (restriction) of XML type definition.
sys.sysxmlplacement : Exists in every database. Contains a row for each XML placement for XML components.
sys.syssingleobjrefs : Exists in every database. Contains a row for each general N-to-1 reference.
sys.sysmultiobjrefs : Exists in every database. Contains a row for each general N-to-N reference.
sys.sysobjvalues : Exists in every database. Contains a row for each general value property of an entity.
sys.sysguidrefs : Exists in every database. Contains a row for each GUID classified ID reference.

2009-08-15

En Son Alınan Backuplar(Last Backups)

SQL Server veritabanlarının en son alınan backupların başlangıç ve bitiş tarih ve saatlerini aşağıdaki SQL kodu ile alabilirsiniz:

SELECT user_name ,
database_name ,
server_name ,
backup_start_date ,
backup_finish_date ,
Type
FROM msdb.dbo.backupset
WHERE backup_set_id IN(SELECT MAX(backup_set_id) FROM msdb.dbo.backupset
WHERE TYPE in ('D','I')
GROUP BY database_name)
AND database_name IN(SELECT name FROM master.dbo.sysdatabases)
ORDER BY database_name

2009-08-05

Regaib Kandiliniz Mübarek Olsun

Regaib kandilinizi tebrik eder; kandilin hayırlara vesile olmasını Cenab-ı Hak'tan niyaz ediyorum.

2009-08-03

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

2009-07-27

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

2009-07-23

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

2009-04-11

Blog Ödülleri 2009'a Katıldım

Günlüğüm Blog Ödülleri 2009'da Teknoloji kategorisinde yarışmaya katılıyor. Dilediğiniz kategorilerde iyi olduğuna inandığınız günlüklere 11-26 Nisan tarihleri arasında oy verip destek olabilirsiniz.

Tüm günlüklere başarılar diliyorum.

2009-04-08

SQL Server 2008 SP1 Yayında

SQL Server 2008'in beklenen Service Pack 1'i nihayet yayında. SQL Server 2008 SP1'i microsoft'tan indirebilirsiniz(Build 10.00.2531.00).

.::YASAL UYARI::.

©2004-2024 Mehmet GÜZEL, www.mehmetguzel.net

Site içeriği kaynak gösterilmek koşuluyla yayınlanabilir. Yazılan yazı ve yorumlar sadece yazı ve yorum sahiplerini bağlar.