Ko `p marotaba SQL Server Disklardagi bo'sh joy muammosining sababi bo'lishi mumkin. Ushbu maqolada biz ushbu muammoning asosiy sabablari nima ekanligini va uni qanday tuzatish kerakligini ko'rib chiqamiz.
Sizning SQL Server joy kerak.
Ko'p marotaba SQL Server disk maydoni kerak bo'ladi. Buning sababi ma'lumotlar bazasi ichidagi ma'lumotlarning ko'payishi yoki qisqartirilmagan jurnal fayllari yoki o'chirilmagan zaxira fayllari yoki o'chirilmagan, kiruvchi ma'lumotlar bazasi fayllari bo'lishi mumkin. Sababi nima bo'lishidan qat'iy nazar, a SQL Server, diskdagi bo'sh joy ma'lumotlar bazasi tranzaksiyalari uchun juda muhimdir.
Tozalash vazifasi ishlamayapti
Agar siz SQL-ning zaxira nusxasini yaratish uchun mahalliy texnik xizmat ko'rsatish rejalaridan foydalanayotgan bo'lsangiz SQL server ma'lumotlar bazalarida ushbu texnik rejalardagi tozalash moduli o'z vazifalarini bajarmasligi ehtimoli bo'lishi mumkin. Diskda bo'sh joy tugashi bilan siz eski zaxira fayllari to'g'ri tozalanmaganligini tushunasiz. Siz tozalash modulini texnik xizmat ko'rsatish rejasiga kiritgan bo'lardingiz. Bularning barchasiga qaramay, nima uchun u ishlamayapti deb o'ylaysizmi?
Zaxira fayllarni o'chirish uchun to'g'ri papkani eslatib o'tganingizni tekshiring, zaxira fayllarini o'chirish uchun to'g'ri fayl kengaytmasini eslatib o'tganingizni tekshiring, "" nuqta bilan harakat qilib ko'ring. va fayl kengaytmasida nuqtasiz.
Jurnal fayllari ma'lumotlar bazasi fayllaridan katta
Bo'sh joy muammosining eng keng tarqalgan sababi SQL Server Bu qarovsiz jurnal fayllari va tempdb fayllari. SQL xizmati qayta ishga tushirilganda tempdb asl hajmiga kichraysa ham, tempdb o'sishini kuzatib borish va butun disk maydonini egallash arafasida uni kichraytirish yaxshi amaliyotdir. Tempdbga o'xshab, jurnal fayllari doimo tekshirilishi kerak. Jurnal fayllarini har doim minimal hajmda saqlash uchun jurnal zaxira nusxangiz borligiga ishonch hosil qiling.
Foydalanilmayotgan ma'lumotlar bazasi fayllari
Diskingizda foydalanilmagan va biriktirilmagan ma'lumotlar bazasi fayllari ko'p bo'lishi va bo'sh joyni yo'qotishi mumkin. O'zingizning skriptingizni bajaring SQL Server Bunday fayllarni ularning yo'llari bilan aniqlash uchun misol. Tez tahlildan so'ng, agar siz ushbu fayllar endi kerak emasligiga ishonchingiz komil bo'lsa, ularni o'chirib tashlang va joyni saqlang.
DECLARE @dpth NVARCHAR(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer'
,N'DefaultData'
,@dpth OUTPUT
DECLARE @lpth NVARCHAR(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer'
,N'DefaultLog'
,@lpth OUTPUT
DECLARE @bk NVARCHAR(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer'
,N'BackupDirectory'
,@bk OUTPUT
DECLARE @md NVARCHAR(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters'
,N'SqlArg0'
,@md OUTPUT
SELECT @md = substring(@md, 3, 255)
SELECT @md = substring(@md, 1, len(@md) - charindex('\', reverse(@md)))
DECLARE @ml NVARCHAR(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters'
,N'SqlArg2'
,@ml OUTPUT
SELECT @ml = substring(@ml, 3, 255)
SELECT @ml = substring(@ml, 1, len(@ml) - charindex('\', reverse(@ml)))
SET @dpth = isnull(@dpth, @md)
SET @lpth = isnull(@lpth, @ml)
PRINT @dpth
PRINT @lpth
EXEC sp_configure 'show advanced'
,1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell'
,1
RECONFIGURE
IF object_id('tempdb.dbo.#table1') IS NOT NULL
DROP TABLE #table1
CREATE TABLE #table1 (
[filename] VARCHAR(2000)
,depth INT
,isFile INT
)
SET @dpth = 'DIR ' + @dpth + '\*.mdf /b /s'
SET @lpth = 'DIR ' + @lpth + '\*.ldf /b /s'
INSERT INTO #table1
EXEC xp_DirTree @dpth
,1
,1
INSERT INTO #table1
EXEC xp_DirTree @lpth
,1
,1
DELETE
FROM #table1
WHERE isFile <> 1
UPDATE #table1
SET filename = rtrim(filename)
CREATE TABLE t_list (
filepath VARCHAR(2000)
,sizeinmb DECIMAL(18, 2)
)
INSERT INTO t_list (filepath)
SELECT otable.filename AS orphaned_files
FROM #table1 otable
LEFT OUTER JOIN master.dbo.sysaltfiles db ON rtrim(db.filename) = otable.filename
WHERE db.dbid IS NULL
ORDER BY 1
DECLARE @sizeingb AS DECIMAL(18, 2)
DECLARE @filepath AS VARCHAR(2000)
DECLARE db_cursor CURSOR
FOR
SELECT filepath
FROM t_list
OPEN db_cursor
FETCH NEXT
FROM db_cursor
INTO @filepath
WHILE @@FETCH_STATUS = 0
BEGIN
CREATE TABLE t_temp (c1 VARCHAR(2000))
DECLARE @cmd AS VARCHAR(3000)
SET @cmd = 'dir ' + @filepath
PRINT @cmd
INSERT INTO t_temp
EXEC master.dbo.xp_cmdshell @cmd
DELETE
FROM t_temp
WHERE c1 NOT LIKE '%1 File(s)%bytes'
DECLARE @size AS DECIMAL(18, 2)
SET @size = (
SELECT TOP 1 replace(replace(replace(c1, ' 1 File(s) ', ''), ',', ''), ' bytes', '')
FROM t_temp
WHERE c1 IS NOT NULL
)
SET @size = cast((@size / (1024 * 1024)) AS DECIMAL(18, 2))
DROP TABLE t_temp
UPDATE t_list
SET sizeinmb = @size
WHERE filepath = @filepath
FETCH NEXT
FROM db_cursor
INTO @filepath
END
CLOSE db_cursor
DEALLOCATE db_cursor
SELECT *
FROM t_list
DROP TABLE t_list
EXEC sp_configure 'show advanced'
,1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell'
,0
RECONFIGURE
SQL Server Ma'lumotlar bazasining buzilishi
Disk maydonini kuzatish va saqlashdan tashqari, diskingizning sog'lig'ini ham kuzatib boring. Nosog'lom disk sizni buzishi mumkin SQL Server ma'lumotlar bazalari. Agar shunday bo'lsa, iltimos, ma'lumotlar bazasini tiklash vositasidan foydalaning DataNumen SQL Recovery uchun buzilgan tuzatish SQL Server.
Muallif kirish:
Neil Varli - ma'lumotlarni qayta tiklash bo'yicha mutaxassis DataNumenMa'lumotlarni qayta tiklash texnologiyalari bo'yicha jahon yetakchisi bo'lgan , Inc Outlook elektron pochta buzilishini tuzatish va excel tiklash dasturiy mahsulotlar. Qo'shimcha ma'lumot olish uchun tashrif buyuring www.datanumen.com