SELECT
a3.name as [Şema Adı],
a2.name AS [Tablo Adı],
a1.rows as [Satır Sayısı],
CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS [Toplam Mb],
CAST(ROUND(a1.data * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS [Data Mb],
CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) – a1.data ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS [Index Mb],
CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS [Toplam Gb],
CAST(ROUND(a1.data * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS [Data Gb],
CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) – a1.data ELSE 0 END) * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS [Index Gb]
FROM
(SELECT
ps.object_id,
SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (CASE
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N’S’ and a2.type <> N’IT’
ORDER BY [Toplam Mb] DESC
Sql2019 kaynaklı bir TimeOut hatasının çözümüdür. Aşağıdaki kodu ilgili db de çalıştırmanız yeterli.
DECLARE @BilnexVersiyon INT;
DECLARE @LegacyCardinalityEstimation INT;
DECLARE @ScalarUdfInlining INT;
DECLARE @CompatibilityLevel INT;
DECLARE @DbName NVARCHAR(100)= DB_NAME()
DECLARE @sql NVARCHAR(MAX);
SELECT @BilnexVersiyon = CAST(PARSENAME(CAST(SERVERPROPERTY(‘ProductVersion’) AS NVARCHAR), 4) AS INT);
IF @BilnexVersiyon >= 12
BEGIN
EXEC sp_configure ‘show advanced options’, 1;
RECONFIGURE;
SELECT @LegacyCardinalityEstimation = CONVERT(INT, value)
FROM sys.database_scoped_configurations
WHERE name = ‘LEGACY_CARDINALITY_ESTIMATION’;
IF @LegacyCardinalityEstimation = 0
EXEC(‘ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON’);
IF @BilnexVersiyon >= 15
BEGIN
SET @sql = ‘
SELECT @ScalarUdfInlining = COALESCE(CONVERT(INT, value), 0)
FROM sys.database_scoped_configurations
WHERE name = ”TSQL_SCALAR_UDF_INLINING”;
IF @ScalarUdfInlining <> 0
ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF; ‘;
EXEC sp_executesql @sql, N’@ScalarUdfInlining INT OUTPUT’, @ScalarUdfInlining OUTPUT;
END
SELECT @CompatibilityLevel = compatibility_level
FROM sys.databases
WHERE name = DB_NAME()
IF @BilnexVersiyon = 15 AND @CompatibilityLevel < 150
EXEC(‘ALTER DATABASE ‘+ @DbName +’ SET COMPATIBILITY_LEVEL = 150′);
END
Database hatalarını gidermek için aşağıdaki sorguyu kullanabilirsiniz. Çalıştırmadan önce mutlaka yedek alın, burada yapılan işlemin geri dönüşü çok mümkün değil.
–BU İŞLEMLER ÖNCESİNDE MUTLAKA YEDEK ALIN…
— Data sorunlu mu? kontrol ederek diğerlerini çalıştıralım.
DBCC CHECKDB(‘BILNEX_BILNEX’) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
EXEC SP_RESETSTATUS ‘BILNEX_BILNEX’;
GO
ALTER DATABASE BILNEX_BILNEX SET EMERGENCY — Eğer uzun bekletirse yaklaşık 15sn bekleyip iptal edilerek aşağıdakine geçin.
GO
ALTER DATABASE BILNEX_BILNEX SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DBCC CHECKDB (‘BILNEX_BILNEX’, REPAIR_ALLOW_DATA_LOSS) — Alt kısımda dönen sorgularda kırmızı yazı kalmayana kadar bu sorguyu çalıştırın
GO
ALTER DATABASE BILNEX_BILNEX SET MULTI_USER
–Db nin multi user olmamsı halinde bu kodu kullanabilirsiniz.
USE MASTER;
— Add users
ALTER DATABASE [my_db] SET MULTI_USER
GO