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 @@VERSIONxp_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.
Eline sağlık
Teşekkür ederim.