SQL Server-ის recovery მოდელები და ტრანზაქციების ლოგის ზრდა

თუ SQL Server-ის ტრანზაქციების ლოგის ფაილი სულ იზრდება, ბაზა თითქმის აუცილებლად full recovery მოდელშია და ლოგის backup-ებს არავინ იღებს. full recovery-ში ლოგის ხელახლა გამოყენება მხოლოდ მას შემდეგ შეიძლება, რაც ლოგის backup-მა ის სადმე გადაწერა, ამიტომ მათ გარეშე ბაზის შექმნიდან მოყოლებული ყოველი ცვლილება .ldf ფაილში გროვდება, სანამ დისკი არ გაივსება. გამოსავალი გადაწყვეტილებაა და არა ბრძანება: ან გჭირდება point-in-time აღდგენა და მაშინ BACKUP LOG-ს ყოველ 15-60 წუთში გეგმავ, ან არ გჭირდება და მაშინ simple recovery მოდელზე გადადიხარ. ამის შემდეგ ლოგს ერთხელ შეკუმშავ გონივრულ ზომამდე და მეტს აღარასოდეს.

გზამკვლევის დანარჩენი ნაწილი ხსნის, რატომ, რომ ეს გადაწყვეტილება სწორად მიიღო და იცნო სხვა, უფრო იშვიათი მიზეზებიც, რის გამოც ლოგი ზრდას არ წყვეტს.

რას აკეთებს ტრანზაქციების ლოგი#

SQL Server-ის ბაზაში ყოველი ცვლილება ტრანზაქციების ლოგში იწერება მანამ, სანამ commit-ად ჩაითვლება. თავად მონაცემთა გვერდები მეხსიერებაში იცვლება და მონაცემთა ფაილში მოგვიანებით იწერება, checkpoint-ზე ან როცა მეხსიერება სჭირდება. თუ სერვერი მოულოდნელად გაჩერდა, სწორედ ლოგი აძლევს SQL Server-ს საშუალებას, ხელახლა შეასრულოს commit-ებული სამუშაო, რომელიც მონაცემთა ფაილამდე ჯერ ვერ მისულა, და გააუქმოს ის, რაც commit-ებული არ იყო. ეს არის write-ahead logging და სწორედ ამიტომ გადაურჩება SQL Server-ის ბაზა დენის გათიშვას.

შიგნით ლოგის ფაილი დაყოფილია ვირტუალურ ლოგის ფაილებად (VLF) და წრიულად გამოიყენება. ახალი ჩანაწერები თავში იწერება; როცა VLF-ის არცერთი ჩანაწერი აღარ არის საჭირო, ის VLF შეიძლება ხელახლა გამოსაყენებლად მოინიშნოს, რასაც ლოგის truncation ჰქვია. Truncation ფაილს არ ამცირებს. ის მხოლოდ საშუალებას აძლევს SQL Server-ს, თავზე გადაწეროს ადგილი, რომელიც უკვე აქვს. ფაილი მხოლოდ მაშინ იზრდება, როცა წრის თავი დაეწევა VLF-ს, რომელიც ჯერ კიდევ საჭიროა.

ამიტომ კითხვა „რატომ არის ჩემი ლოგი ასეთი დიდი?“ სინამდვილეში ასეთია: „რა უშლის ხელს truncation-ს?“. ჩვეულებრივ პასუხს recovery მოდელი განსაზღვრავს.

სამი recovery მოდელი#

Simple უმეტეს პატარა აპლიკაციის ბაზას უხდება. ლოგს მხოლოდ ამჟამად აქტიური ტრანზაქციების შენახვა უწევს, ამიტომ ის თავისით პატარა რჩება. ფასი ისაა, რომ აღდგენა მხოლოდ ბოლო full ან differential backup-ის მომენტამდე შეგიძლია: ღამის backup ნიშნავს მაქსიმუმ ერთი დღის შესაძლო დანაკარგს.

Full ყველაფერს ლოგავს და ინახავს, სანამ ლოგის backup არ წაიღებს. კვირას აღებული full backup-ით და ყოველ 15 წუთში ლოგის backup-ებით შეგიძლია ბაზა აღადგინო ისეთი, როგორიც იყო ხუთშაბათს 14:31-ზე - ერთი წუთით ადრე, სანამ ვიღაცამ DELETE გაუშვა WHERE-ის გარეშე. ეს შესაძლებლობა full recovery-ის გამოყენების ერთადერთი მიზეზია, და ლოგის backup-ების გარეშე მთელ ხარჯს იღებ და სარგებელს - არაფერს.

Bulk-logged არის full recovery, რომელიც გარკვეულ bulk ოპერაციებს მინიმალურად ლოგავს - BULK INSERT, bcp ჩატვირთვები ცხრილის lock-ით, SELECT INTO, ინდექსების rebuild. დიდი ჩატვირთვებისას ლოგი უფრო პატარა რჩება, მაგრამ ლოგის backup, რომელიც bulk ოპერაციას შეიცავს, მხოლოდ მის ბოლომდე აღდგება და არა მის შიგნით რომელიმე წერტილამდე. ის განკუთვნილია იმისთვის, რომ maintenance ფანჯრის გარშემო ჩართო და მერე გამორთო, და არა ჩართული დატოვო.

შეამოწმე ყველა ბაზა ერთად, მათ შორის ის, რა აკავებს თითოეულის ლოგს:

SELECT name, recovery_model_desc, log_reuse_wait_descFROM sys.databasesORDER BY name;

ახალი ბაზები recovery მოდელს model სისტემური ბაზიდან იღებენ. Express-ზე model თავიდანვე simple-ზეა დაყენებული, ამიტომ იქ შექმნილი ბაზები simple recovery-ში იწყებენ; Standard-სა და Enterprise-ზე ის full-ზეა. სხვა სერვერიდან აღდგენილი ბაზა ინარჩუნებს იმ მოდელს, რომელიც წყაროზე ჰქონდა, და სწორედ ასე აღმოჩნდება full recovery-ის ბაზა ლოგის backup-ების გარეშე პატარა Express ინსტანსზე.

რატომ არ ხდება ლოგის truncation: log_reuse_wait_desc#

log_reuse_wait_desc ასახელებს მიზეზს, რის გამოც ლოგის ყველაზე ძველი ნაწილი ჯერ კიდევ საჭიროა. მნიშვნელობები, რომლებსაც რეალურად ნახავ:

LOG_BACKUP ბევრად ყველაზე ხშირია და ეს სწორედ ის შემთხვევაა, რომელიც დასაწყისში აღვწერეთ. მეორეა ACTIVE_TRANSACTION: ერთმა სესიამ გახსნა ტრანზაქცია და აღარასოდეს დახურა - SSMS-ის ფანჯარა BEGIN TRAN-ით და COMMIT-ის გარეშე, აპლიკაცია, რომელმაც კავშირი ტრანზაქციის შუაში დაკარგა, მიგრაცია, რომელიც ჯერ კიდევ მუშაობს. იმ ტრანზაქციის დაწყების შემდეგ დაწერილი არაფრის truncation არ შეიძლება, simple recovery-შიც კი. იპოვე ის:

DBCC OPENTRAN;SELECT s.session_id, s.login_name, s.host_name, s.program_name,       t.transaction_begin_timeFROM sys.dm_tran_active_transactions AS tJOIN sys.dm_tran_session_transactions AS st ON st.transaction_id = t.transaction_idJOIN sys.dm_exec_sessions AS s ON s.session_id = st.session_idORDER BY t.transaction_begin_time;

ყველაზე ძველი row არის ის, რომელიც ლოგს აკავებს. ჰკითხე მის მფლობელს, ან თუ აშკარად მიტოვებულია, სესიას KILL გაუკეთე, რაც ტრანზაქციას უკან აბრუნებს - და rollback-ს შეიძლება იმდენივე დრო დასჭირდეს, რამდენიც თავად სამუშაოს.

კიდევ ერთი დახვეწილი დეტალი: full recovery-ზე გადართული ბაზა რეალურად full-ის მსგავსად არ იქცევა, სანამ პირველი full backup არ აიღება. მანამდე ლოგების ჯაჭვს საფუძველი არ აქვს, ამიტომ SQL Server ლოგს ისე ატარებს truncation-ს, თითქოს simple იყოს. ზოგჯერ ხალხი ხედავს, რომ ლოგი კვირების განმავლობაში პატარა რჩება, და მერე იმ დღეს იწყებს ზრდას, როცა პირველი full backup გაეშვა. სწორედ იმ მომენტში დაიწყო ლოგების ჯაჭვი.

რამდენად დიდია ლოგი და რამდენად სავსე#

-- Every database: log size in MB and percentage usedDBCC SQLPERF(LOGSPACE);-- The current database in more detailSELECT total_log_size_in_bytes / 1048576.0 AS log_mb,       used_log_space_in_bytes / 1048576.0 AS used_mb,       used_log_space_in_percentFROM sys.dm_db_log_space_usage;-- Number of virtual log filesSELECT COUNT(*) AS vlf_count FROM sys.dm_db_log_info(DB_ID());

დიდი ლოგი, რომელიც 3 პროცენტით არის დაკავებული, თავისთავად პრობლემა არ არის - ადგილი ერთხელ დასჭირდა და ხელახლა იქნება გამოყენებული. პრობლემაა ლოგი, რომელიც 99 პროცენტით არის სავსე და ისევ იზრდება. VLF-ების რაოდენობასაც აქვს მნიშვნელობა: ლოგი, რომელიც ათასობით პაწაწინა ნაბიჯით გაიზარდა, ათასობით VLF-ით მთავრდება, რაც ანელებს გაშვებისას აღდგენას და restore-ებს. რამდენიმე ასეული ჩვეულებრივია; ათიათასობით უკვე ღირს იმისთვის, რომ გამოასწორო ლოგის შეკუმშვით და მერე რამდენიმე დიდ ნაბიჯად ხელახლა გაზრდით.

როცა ლოგი უკვე ვეღარ იზრდება - დისკი სავსეა ან მაქსიმალური ზომაა დაყენებული - SQL Server აგდებს შეცდომას 9002 და იმ ბაზაში ცვლილებების მიღებას წყვეტს:

Msg 9002, Level 17, State 2The transaction log for database 'appdb' is full due to 'LOG_BACKUP'.

შეტყობინება ასახელებს log_reuse_wait_desc-ის მიზეზს, რაც გეუბნება, რომელი გამოსავალი გამოიყენო. წაკითხვა გრძელდება; ჩაწერები ვარდება, სანამ ადგილი არ გათავისუფლდება.

Express-ზე ამას განსაკუთრებული წახნაგი აქვს. 10 GB-იანი ზღვარი თითო ბაზაზე მხოლოდ მონაცემთა ფაილებს ითვლის, ამიტომ ლოგს შეუძლია შეუმჩნევლად გადააჭარბოს მას, ჰოსტინგის გეგმაზე კი ლოგი გეგმის დისკს მონაცემებთან და backup-ებთან ერთად იყოფს. 10 GB-იან ან 20 GB-იან დისკზე უყურადღებოდ დატოვებული ლოგი full recovery-ში ნელი მოძრაობით მიმავალი გათიშვაა.

გამოსავალი 1: გადადი simple recovery-ზე#

თუ ღამის ან უფრო ხშირი full backup მისაღები აღდგენის წერტილია, გადადი simple-ზე, მიეცი ლოგს truncation-ის საშუალება და ერთხელ შეკუმშე:

ALTER DATABASE [appdb] SET RECOVERY SIMPLE;CHECKPOINT;-- Find the log file's logical nameSELECT name, size / 128 AS size_mb FROM sys.database_files WHERE type_desc = 'LOG';-- Shrink it to 1 GB, then give it a sensible fixed growthDBCC SHRINKFILE (N'appdb_log', 1024);ALTER DATABASE [appdb]MODIFY FILE (NAME = N'appdb_log', FILEGROWTH = 256MB);

თუ შეკუმშვამ ფაილი ვერ შეამცირა, ლოგის აქტიური ნაწილი ფაილის ბოლოშია; გაუშვი CHECKPOINT და რამდენიმე წუთში შეკუმშვა თავიდან. ლოგის ზომა შენი ყველაზე დიდი რეგულარული ტრანზაქციისთვის შეარჩიე - ხშირად ეს ინდექსის rebuild-ია ან ღამის იმპორტი - და არა რაც შეიძლება პატარა. ლოგი, რომელიც 100 MB-მდე იკუმშება მხოლოდ იმისთვის, რომ ყოველ ღამე 2 GB-მდე გაიზარდოს, უაზრო სამუშაოს აკეთებს, და ყოველი ზრდა ჩაწერებს აჩერებს, სანამ ახალი ადგილი ნულებით ივსება. SQL Server 2022-ს შეუძლია ეს ნულებით შევსება გამოტოვოს 64 MB-მდე ლოგის ზრდებისთვის, მაგრამ უფრო დიდი ზრდები მაინც ნულებით ინიციალიზდება.

ლოგის ერთხელ შეკუმშვა მიზეზის გამოსწორების შემდეგ ნორმალურია. მისი განრიგით შეკუმშვა ნიშანია, რომ მიზეზი არასოდეს გამოსწორებულა. მონაცემთა ფაილების განრიგით შეკუმშვა უარესია, რადგან ის ყოველ ინდექსს აფრაგმენტებს, რომელსაც ეხება.

გამოსავალი 2: დარჩი full recovery-ში და აიღე ლოგის backup-ები#

თუ ერთი დღის მონაცემების დაკარგვა მისაღები არ არის, დატოვე full recovery და ლოგის backup იმდენად ხშირად აიღე, რომ ის არასოდეს გაიზარდოს დიდად:

BACKUP LOG [appdb]TO DISK = N'/var/opt/mssql/data/appdb-log-20261008-1415.trn'WITH CHECKSUM, INIT;

ყოველი ლოგის backup აკოპირებს ლოგს წინა backup-იდან მოყოლებული და იმ ნაწილს truncation-ის საშუალებას აძლევს. ყოველ 15 წუთში ჩვეულებრივი არჩევანია; ინტერვალი შენი მაქსიმალური მონაცემთა დანაკარგია, თუ სერვერი ლოგთან ერთად დაიკარგა. თითოეულ ფაილს დრო დაარქვი, არასოდეს გადაწერო ისინი და შეინახე ყოველი ლოგის backup იმ ყველაზე ძველ full backup-მდე, რომელსაც ინახავ - ჯაჭვი ერთი დაკარგული ფაილით ხარვეზის იქით ვერ აღდგება.

Express-ს SQL Server Agent არ აქვს, ამიტომ განრიგი ძრავის გარეთ მუშაობს: sqlcmd ზარი cron-იდან სხვა მანქანაზე, სკრიპტით, რომელიც ფაილის სახელს თარიღისა და დროისგან აწყობს, ზუსტად ისე, როგორც full backup-ებისთვის. SQL Server backup და აღდგენა შეიცავს სკრიპტს, sqlcmd და bcp კი განმარტავს -b flag-ს, რომელიც შეცდომებს ხილულს ხდის. ლოგის backup-ები მხოლოდ მაშინ არის სასარგებლო, თუ ისინიც ტოვებენ სერვერს, ამიტომ გადაიტანე ისინი გარეთ, როგორც კი აიღება.

აღდგენა დროის კონკრეტულ მომენტამდე#

ეს არის ის, რასაც full recovery ყიდულობს. ვთქვათ, ცუდი DELETE 14:32-ზე გაეშვა. ჯერ აიღე tail-log backup, რომელიც ყველაფერს იჭერს ახლანდელ მომენტამდე და ბაზას restoring მდგომარეობაში ტოვებს, რომ მას სხვა ვერაფერი შეცვალოს:

USE master;BACKUP LOG [appdb]TO DISK = N'/var/opt/mssql/data/appdb-tail.trn'WITH NORECOVERY;

შემდეგ აღადგინე ბოლო full backup, ნებისმიერი differential და ყოველი ლოგის backup თანმიმდევრობით, და გაჩერდი შეცდომამდე ცოტა ადრე:

RESTORE DATABASE [appdb] FROM DISK = N'/var/opt/mssql/data/appdb-full.bak'WITH NORECOVERY, REPLACE;RESTORE LOG [appdb] FROM DISK = N'/var/opt/mssql/data/appdb-log-20261008-1400.trn'WITH NORECOVERY;RESTORE LOG [appdb] FROM DISK = N'/var/opt/mssql/data/appdb-log-20261008-1415.trn'WITH NORECOVERY;RESTORE LOG [appdb] FROM DISK = N'/var/opt/mssql/data/appdb-tail.trn'WITH STOPAT = '2026-10-08T14:31:30', RECOVERY;

ხშირად ჯობს აღადგინო ახალი სახელით production-ის გვერდით და უკან მხოლოდ წაშლილი row-ები გადაიტანო, რომ შეინარჩუნო ყველაფერი, რაც სხვა მომხმარებლებმა 14:32-ის შემდეგ ჩაწერეს. msdb-ის backup-ების ისტორია ჩამოთვლის ყოველ ლოგის backup-ს მისი დროის დიაპაზონით, და ასე პოულობ სწორ ფაილებს, როცა ისინი ასობითაა. ივარჯიშე ამაზე ერთხელ ასლზე, სანამ დაგჭირდება; აღდგენის ტესტირება, სანამ დაგჭირდება ამას ასაბუთებს.

Recovery მოდელები და ჰოსტის backup-ები#

ჰოსტინგის სერვერებს ჩვეულებრივ საკუთარი backup-ის რაიმე ფორმა მოჰყვება, და ღირს ნათლად გესმოდეს, როგორ უკავშირდება ის recovery მოდელს, რადგან ისინი სხვადასხვა კითხვას პასუხობენ.

ჰოსტის დონის backup სერვერის ფაილებს ერთ მომენტში აკოპირებს და მისი აღდგენა მთელ სერვერს იმ მომენტში აბრუნებს. მან არაფერი იცის ლოგების ჯაჭვებზე. ის ერთ ბაზას 14:31-მდე ვერ აღადგენს და იმდენად კარგია, რამდენადაც მისი განრიგი: ღამის ასლი ღამის აღდგენის წერტილია, რომელ recovery მოდელშიც არ უნდა იყოს ბაზა. full recovery-ის ბაზა ლოგის backup-ების გარეშე ჰოსტის backup-იდან ვერაფერს იგებს, გარდა დასაკოპირებელი უფრო დიდი ლოგისა.

Native backup-ები ის ფენაა, რომელსაც SQL Server ესმის. full backup გაძლევს თანმიმდევრულ ბაზას, differential აღდგენას ამოკლებს, ლოგის backup-ები კი წუთის სიზუსტის აღდგენის წერტილს იძლევა. პატარა ბაზისთვის პრაქტიკული კომბინაციაა: simple recovery, native full backup ყოველ ღამე, ჩაწერილი ჰოსტის საკუთარ დაგეგმილ backup-მდე რამდენიმე წუთით ადრე, რომ არქივში ყოველთვის იყოს თანმიმდევრული .bak, და ამ ფაილის ასლი, ყოველკვირეულად სადმე სხვაგან ჩამოტვირთული. თუ ყოველდღიურზე უკეთესი აღდგენის წერტილი გჭირდება, გადადი full recovery-ზე და დაამატე ლოგის backup-ები, რომლებიც სერვერს აღებისთანავე ტოვებენ.

RE:NODE-ზე SQL Server-ის გეგმებს მოჰყვება ერთიდან ოთხამდე backup slot, დონის მიხედვით, რომლებიც იღება მოთხოვნით ან განრიგით Schedules ჩანართიდან და ინახება იმ მანქანის გარეთ, რომელსაც იცავს. ეს მთელი სერვერის backup-ებია ზუსტად ზემოთ აღწერილი გაგებით. შენთვის შექმნილი ბაზის კონფიგურაცია შენზეა, ამიტომ მისი recovery მოდელი ამ გზამკვლევის დასაწყისში მოცემული query-ით პირველივე დღეს შეამოწმე და არა მას შემდეგ, რაც დისკი გაივსება.

ჩვევები, რომლებიც ლოგს პატარას ტოვებს#

Recovery მოდელი საბაზისო დონეს ადგენს. ყოველდღიურ ლოგის ზომას ყველაზე დიდი ცალკეული ტრანზაქცია განსაზღვრავს, რადგან ღია ტრანზაქცია აკავებს მთელ ლოგს, რომელიც მისი დაწყების შემდეგ დაიწერა.

• წაშალე ნაწილ-ნაწილ. ათი მილიონი row-ის ერთი DELETE ერთი ტრანზაქციაა, რომელიც ლოგში უნდა ჩაეტიოს. ციკლი, რომელიც ერთდროულად 5,000 row-ს შლის, ლოგს საშუალებას აძლევს, ნაწილებს შორის გაიწმინდოს - simple recovery-ში checkpoint-ის შემდეგ, full-ში კი შემდეგი ლოგის backup-ის შემდეგ.
• ტრანზაქციები მოკლე შეინარჩუნე. ნუ დატოვებ ტრანზაქციას ღიად, სანამ მომხმარებელს, HTTP ზარს ან ფაილის ატვირთვას ელოდები.
• ყურადღება მიაქციე ინდექსების მოვლას. დიდი ინდექსის rebuild full recovery-ში სრულად ილოგება და შეიძლება ინდექსის ზომის ლოგი შექმნას. Rebuild მხოლოდ იმას გაუკეთე, რასაც ეს სჭირდება, რაც NVMe-ზე ძალიან ცოტაა; SQL Server-ის ინდექსები და execution plan-ები ამის არგუმენტაციას შეიცავს.
• ზრდა მეგაბაიტებში დააყენე და არა პროცენტებში. პატარა ლოგის ათი პროცენტი პაწაწინაა და მას ბევრ VLF-ად აფრაგმენტებს; უზარმაზარი ლოგის ათი პროცენტი კი გრძელი პაუზაა.

WHILE 1 = 1BEGIN    DELETE TOP (5000) FROM dbo.Events WHERE CreatedAt < '2026-01-01';    IF @@ROWCOUNT = 0 BREAK;END;

FAQ#

უსაფრთხოა .ldf ფაილის წაშლა ადგილის გასათავისუფლებლად?

არა. ლოგი ბაზის ნაწილია, და მისმა წაშლამ, სანამ ის აქტიურ ტრანზაქციებს შეიცავს, შეიძლება ბაზა აღუდგენელი დატოვოს ან აიძულოს ისეთი შეკეთება, რომელიც მონაცემებს კარგავს. გამოასწორე ზრდის მიზეზი, შემდეგ კი შეკუმშე ის DBCC SHRINKFILE-ით.

simple გამოვიყენო თუ full recovery პატარა ვებ-აპლიკაციისთვის?

Simple, თუ ბოლო backup-ის შემდეგ ჩაწერილი მონაცემების დაკარგვა მართლა მტკივნეული არ იქნება. პატარა აპლიკაციების უმეტესობა ღამის full backup-ს იღებს და ამ ფანჯარას ეგუება. თუ full-ს ირჩევ, ლოგის backup-ები იმავე დღეს დაგეგმე, თორემ ლოგი უსაზღვროდ გაიზრდება.

რატომ გაიზარდა ჩემი ლოგი simple recovery-შიც კი?

იმიტომ, რომ რაღაცამ ტრანზაქცია ღიად დატოვა, ან ერთმა ინსტრუქციამ უზარმაზარი რაოდენობის row შეცვალა. simple recovery-შიც ლოგს ყოველი აქტიური ტრანზაქციის სრულად შენახვა უწევს. დამნაშავის საპოვნელად შეამოწმე log_reuse_wait_desc და DBCC OPENTRAN.

ითვლება ტრანზაქციების ლოგი Express-ის 10 GB-იან ზღვარში?

არა. ზღვარი თითოეული ბაზის მონაცემთა ფაილებს ეხება. თუმცა ლოგი მაინც დისკს იყენებს, და ჰოსტინგის გეგმაზე ის ადგილს იყოფს მონაცემებთან და სერვერზე შენახულ ნებისმიერ backup-თან, ამიტომ მასაც ისევე სჭირდება თვალყურის დევნება.

რამდენად ხშირად უნდა გაეშვას ლოგის backup-ები?

იმდენად ხშირად, რამდენი სამუშაოს დაკარგვაც მზად ხარ. ყოველ 15 წუთში ჩვეულებრივი ნაგულისხმევია; ყოველ 5 წუთში დატვირთულ სისტემებს უხდება. უფრო ხშირი backup-ები ასევე ყოველ ფაილს პატარას და თავად ლოგს კომპაქტურს ტოვებს.