SQL Server Veri Tabanı Yöneticileri İçin Tavsiyeler

Veri tabanında bazı komutlar vardır ki her veri tabanı yöneticisi için yeri geldiğinde hayat kurtarabiliyor. Bu makalemizde bu komutlardan bazılarını beraber görmeye çalışacağız. Sözü çok uzatmadan Sistem Bilgileri başlığı ile başlayalım.

Sistem Bilgileri

Aktif Çalışan SQL Server Versiyonu Bulma

SELECT @@VERSION

xp_msver

Sunucu Özelliklerini Bulma

SELECT	SERVERPROPERTY('productversion') AS 'Product Version'
		,SERVERPROPERTY ('productlevel') AS 'Service Pack'
		,SERVERPROPERTY ('edition') AS 'Edition'
		,@@SERVERNAME As [@@SERVERNAME]
		,CAST(SERVERPROPERTY('MACHINENAME') AS VARCHAR(128)) + COALESCE('' +
CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128)), '') As 'Instance Name'

SQL Server’ın Üzerinde Çalıştığı Port Bilgisini Bulma

SQL Server varsayılan olarak 1433 portunu kullanıyor olsa da dba tarafından bu port değiştirilebiliyor. Aynı şekilde sunucuda birden fazla SQL Instance yüklü ise ikinci yüklenen instance için farklı bir port ayarlaması yapılır.

sp_readerrorlog 0, 1, N'Server is listening on'

SQL Server Agent Servisinin Üzerinde Çalıştığı Hesabı Bulma

Agent servisi SQL Server’da otomatikleştirilmiş işlemleri yürüten servistir ve çalışırken üzerinde çalıştığı hesabın yetkilerini kullanır. Bu sebeple özellikle dosya okuma, yazma işlemi yapan görevlerin hatasız çalışması için bu hesaba ait yetkilerin iyi ayarlanması gerekmektedir. Aşağıdaki kodlar yardımı ile bu bilgiye kolaylıkla ulaşabiliriz.

• SQL 2005 ve 2008 İçin

DECLARE @ServiceAccount NVARCHAR(128);
EXEC master.dbo.xp_regread
    'HKEY_LOCAL_MACHINE',
    'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT',
    'ObjectName', 
    @ServiceAccount OUTPUT;
SELECT @ServiceAccount;

• SQL 2012 ve Sonrası İçin

SELECT 
	servicename AS 'Service Name'
	,service_account
	,startup_type_desc AS 'Startup Type'
	,status_desc as 'Status'
	,last_startup_time as 'Last Startup Time'
FROM sys.dm_server_services;

2012 ve sonrası versiyonlarda ilgili sorgu çalıştırıldığında Agent servisinin yanında SQL ile ilgili bütün servislerin bilgileri gelmektedir.

Sisteme Tanımlı Bütün DMV’leri Görme

DMV’ler (Dynamic Management Views) sisteme dair bilgileri almak da son derece yararlı yapılardır.

SELECT * FROM sys.all_objects 
WHERE [name] LIKE '%DM_%' AND [type] IN ('V', 'TF', 'IF')
	AND [schema_id] = 4;  --The sys schema has schema_id =4;

Donanım Bilgisi Öğrenme

İşlemci Sayısını Bulma

• SQL Server 2005 / 2008 İçin

SELECT cpu_count AS 'Logical CPUs'
	,hyperthread_ratio AS 'Hyperthread Ratio'
	,cpu_count/hyperthread_ratio AS '# of Physical CPU'
	,physical_memory_in_bytes/1048576 AS 'Physical Memory (MB)'
	--SQL 2008 also has sqlserver_start_time field shown below
 FROM sys.dm_os_sys_info;

• SQL Server 2012 ve Sonrası İçin

SELECT cpu_count AS 'Logical CPUs'
	,hyperthread_ratio AS 'Hyperthread Ratio'
	,cpu_count/hyperthread_ratio AS '# of Physical CPU'
	,physical_memory_kb/1024 AS 'Physical Memory (MB)'
	,sqlserver_start_time AS 'SQL Server Start Time'  
FROM sys.dm_os_sys_info;

RAM Bilgisi Alma

SELECT * FROM sys.dm_os_sys_memory;

Boş Disk Alanı Bulma

Aşağıdaki stored procedure ile sunucu üzerinde bulunan hard disklerde ki boş alan bilgisini MB cinsinden verir.

EXEC master..xp_fixeddrives;

Veri Tabanı Bilgileri

Veri Tabanı Compatibility Level Öğrenme

SELECT name, compatibility_level 
	,version_name = 
	CASE compatibility_level
		WHEN 65  THEN 'SQL Server 6.5'
		WHEN 70  THEN 'SQL Server 7.0'
		WHEN 80  THEN 'SQL Server 2000'
		WHEN 90  THEN 'SQL Server 2005'
		WHEN 100 THEN 'SQL Server 2008/R2'
		WHEN 110 THEN 'SQL Server 2012'
		WHEN 120 THEN 'SQL Server 2014'
		WHEN 130 THEN 'SQL Server 2016'
		WHEN 140 THEN 'SQL Server 2017'
		WHEN 150 THEN 'SQL Server 2019'
	END
FROM sys.databases

Compatibility Level Değiştirme

USE [master]
GO
ALTER DATABASE VeriTabani_Ad SET COMPATIBILITY_LEVEL = XXX;

Veri Tabanlarını dbid ve Dosyaları ile Göstermek

SELECT name AS 'Database',dbid,crdate AS 'Create Date',filename as 'Data Filename' 
FROM sys.sysdatabases;

Veri Tabanı Adından db_id Değerini Bulma

SELECT db_id('veriTabani_ad') AS 'Database ID';

Veri Tabanı Sahiplerini Bulma

SELECT	name AS 'Database', suser_sname(owner_sid) AS 'Database Owner'
FROM sys.databases;

Veri Tabanı Dosya Bilgilerini Alma

SELECT
    DB.name AS 'Database',
    MF.Name AS 'Logical File Name',
    MF.physical_name AS 'Physical File',
    MF.state_desc AS 'Status',
    CAST((MF.size*8)/1024 AS VARCHAR(26)) + ' MB' AS 'File Size (MB)',
    CAST(MF.size*8 AS VARCHAR(32)) + ' Bytes' as 'File Size (Bytes)'
FROM 
    sys.master_files MF
    INNER JOIN sys.databases DB ON DB.database_id = MF.database_id
ORDER BY
    DB.name;

Kullanıcı Stored Procedurleri ve Fonksiyonları Bul

EXECUTE [dbo].[sp_stored_procedures] @sp_owner ='dbo';

Stored Procedure ve Fonksiyon Bilgilerini Alma

-- sp bilgileri
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' ;
--fonksiyon bilgileri
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'FUNCTION' and ROUTINE_SCHEMA = N'dbo' ;

Tablo Listesini Alma

SELECT * FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME;

Veri Tabanı Boş Alanı Bulma

EXEC sp_spaceused;

Transaction Log Dosyalarının Boyutu Gösterme

DBCC SQLPERF(LOGSPACE);

Yedekleme Stratejisi

Yedekleme stratejileri sistemde bir hata oluşması durumunda ne kadar geriden gelinerek sistemi tekrar hayata döndüreceğimizi belirleyen karardır.

Veri Tabanlarının Yedekleme Modellerini Görme

SELECT name AS [Database Name],recovery_model_desc AS [Recovery Model]
FROM sys.databases;
GO

Full Backup Alma Ve Yedekleme Dosyasını Doğrulama

Aşağıdaki kod ile veri tabanının full yedeği alınabilir. Yedeklemenin başarılı olabilmesi için SQL Server servisinin üzerinde çalıştığı kullanıcının yedekleme hedefi klasör üzerinde yazma yetkisine sahip olması gerekmektedir.

BACKUP DATABASE Veri_Tabani
TO DISK= 'e:\\MSSQL\Backup\MYDATABASE_backup_20130919.bak'
WITH STATS = 10;

Alınan yedek dosyasının da tekrar kullanımda hata almaması adına yedeklemeden sonra aşağıdaki kodla doğrulaması yapılmalıdır.

RESTORE VERIFYONLY FROM DISK= 'e:\\MSSQL\Backup\MYDATABASE_backup_20130919.bak';

Yedekleme dosyası aynı olduğunda bir önceki yedekleme dosyasına yeni bir dönme noktası ekleyerek dosyaya ekleme yapar. Bunun yerine aşağıdaki kod ile her yedek alındığında yedek dosyasının adı, yedek alınan tarih ve saate göre verilebilir.

DECLARE @fileName varchar(50)
SELECT @fileName= 'e:\db_FULL_' + REPLACE(REPLACE(REPLACE((CONVERT(nvarchar,GETDATE(),120)),'-',''),' ','_'),':','') + '.bak'
BACKUP DATABASE movies TO DISK=@fileName WITH NOFORMAT, NOINIT, SKIP, STATS=10;

Full Recovery Model ve T-Log Yedeklemesi

Veri tabanı log bilgisi “FULL Recovery” modda ayarlandıysa yapılan her işlemin logu kayıtlı kalır ve T-LOG yedeklemesi alınmadığı sürece ilgili log dosyası büyümeye devam eder. Bu büyümenin önüne geçmek için sistemin yoğunluğuna göre uygun zaman aralıkları ile log bilgilerinin yedeklenmesi gerekmektedir. Aşağıdaki kod ile T-LOG bilgileri yedeklenerek temizlenebilir.

BACKUP LOG Movies TO 's:\dddddd';

Bu noktada sistem yöneticisinin kontrol altında tutması gereken nokta, hangi veri tabanlarının FULL Recovery modda çalıştığıdır. Aşağıdaki kod ile sistemde FULL Recovery modda çalışan veri tabanları listelenebilir.

SELECT D.[name] AS [database_name], D.[recovery_model_desc] 
FROM sys.databases D LEFT JOIN  
   ( 
   SELECT BS.[database_name],  
       MAX(BS.[backup_finish_date]) AS [last_log_backup_date] 
   FROM msdb.dbo.backupset BS  
   WHERE BS.type = 'L' 
   GROUP BY BS.[database_name] 
   ) BS1 ON D.[name] = BS1.[database_name] 
WHERE D.[recovery_model_desc] <> 'SIMPLE' 
   AND BS1.[last_log_backup_date] IS NULL 
ORDER BY D.[name];

Bu kodları nerede kullanabilirim önerileri:

• Diskte kalan boş alanı sorgulayan kodu bir göreve bağlayıp kritik seviyenin altına düştüğünde sistem yöneticisi olarak kendimize mail ile bilgilendirme yapabiliriz.
• Veri tabanı dosya boyutlarını sorgulayan kod ile veri tabanındaki ani büyümeler mail ile haber verilebilir.