Backup log with truncate only

This piece of T-SQL is deprecated in 2005:

I don’t need to keep a backup the log for my db — but I do not want the piece of code to stop working if we port to SQL2008 or successive versions in future.

4 Answers 4

Switch the database recovery mode to SIMPLE, and then use DBCC SHRINKFILE. Then restore your original recovery mode. If your LOG file does not shrink, you might have uncommitted transactions. For more details, see Tibor’s Karaszi’s article on shrinking.

If you change the recovery model of the database to Simple, I think it will stop forcing you to backup/truncate the log.

Change your database to use the simple recovery model. This means you do not have point in time recovery (you won’t have that anyway if you are truncating your log), but the log file is automatically cycled and won’t grow too large.

A log file is mandatory and you have no option but to keep it, what you don’t want is for it to grow out of control and fill your disk.

As Andy Jones answered, the log file is mandatory. It is not simply a log of events for your sake, but a vital part of the way the database handles transaction rollbacks as well as memory commits.

Олег Филиппов

Для MS SQL 2008/2012 рекомендации ИТС уже устарели, кроме того и раньше они не всегда помогали. В статье попытался собрать наиболее полный комплект информации по данному вопросу.
В своё время в одном месте всего этого не нашел, поэтому думаю будет полезно.

Популярная статья ИТС http://its.1c.ru/db/metod81#content:2373:1 устарела — теперь уменьшение размера журнала транзакций стало не самой простой операцией.

Собственно там рекомендуется следующий скрипт:

BACKUP LOG Имя_Базы_Данных WITH TRUNCATE_ONLY

DBCC SHRINKFILE(Имя_Файла_Журнала_Транзакций)

Если выполнить его в MS SQL 2008/2012 получите ошибку:

‘truncate_only’ is not a recognized BACKUP option

Что теперь делать?
Решения, собственно два:

1)
USE [Database]
ALTER DATABASE [Database] SET RECOVERY SIMPLE
go

DBCC SHRINKFILE ([Database]_log, 1);
ALTER DATABASE [Database] SET RECOVERY FULL
go

2)
USE [Database]
BACKUP LOG [Database] TO DISK=’NUL:’
go
DBCC SHRINKFILE ([Database]_log, 1)
go

Если "Урезанием лога" не злоупотреблять (т.е. сокрашать лог вместе с полной копией) то по большому счету не принципиально каким методом пользоваться.
Второй вроде как правильнее, зато первый "надежнее".

На этом казалось бы можно и остановиться, но зачем тогда отдельную статью писать. Нет, конечно это ещё не всё. Обычно вопросы про урезание лога возникают когда это сделать не получается.
Притом способы, описанные выше, как правило, описаны не раз, все их освоили и проблем не вызывают.

Итак, если все действия, описанные выше не помогли — лог файл по-прежнему занимает N гигабайт. Переходим к плану B:

select log_reuse_wait_desc from sys.databases

В результате можете получить 3 варианта:

а. Пусто — Обычно это означает что у БД лог можно хоть сейчас полностью сократить, могу предложить только попробовать ещё раз Shrink, а если не поможет — переходить к плану C
b. Log_Backup — Нормальный варинат. В данном случае говорит о том, что Backup Log не выполнено, или выполнено некорректно
b. Replication — значит что ваш лог не обрезается из за репликации — скорее всего ошибки.
с. Active transactions — Самая частая ситуация — в базе есть подвисшие транзакции, с ними нужно разобраться.

Replication — Репликация для систем на платформе 1С, пожалуй, бессмысленное дело. Потому как Read only баз MS SQL не бывает, средства создания распределенных систем в 1С есть собственне (да, я про РИБ). Для обеспечения отказоустойчивости гораздо лучше подходят кластерные технологии. Собственно рекоммендация простая:

sp_removedbreplication ‘[Database]’

Собственно после этого бэкап и Shrink помогут. Если же вопреки здравому смыслу вы всё-таки хотите сохранить репликацию БД то конечно выполнять эту команду нельзя, а нужно разбираться с ошибками репликации. Но это уже тема отдельной статьи.

Active transactions — наиболее популярная история. В базе есть транзакции, которые не завершены, и чего то ожидают. Чащи всего такие транзакции получаются при потере сетевого соединения или "вылете" клиента 1С в момент записи в БД. В этом случае нужно собственно узнать какая транзакция "повисла":

DBCC OPENTRAN

После выполнения этой команды вы получите примерно следующий результат:

Transaction information for database ‘master’.
Oldest active transaction:
SPID (server process ID) : 52
UID (user ID) : -1
Name : user_transaction
LSN : (518:1576:1)
Start time : May 5 2014 3:30:07:197PM
SID : 0x010500000000000515000000a065cf7e784b9b 5fe77c87709e611500
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Из этого обилия информации ключевым является Start Time и SPID. Если транзакция в базе 1С выполняется боле нескольких секунд это уже означает что что-то не так. А если start Time будет минут 10 или более от текущего времени — такие транзакции (сеансы) нужно завершать. Но предварительно я бы рекоммендовал узнать что эта транзакция делала.

Для завершения процесса можно ввести команду

KILL [Process ID]

Где Process ID — это тот самый SPID полученный на предыдущем шаге. При этом незавершенные транзакции откатятся средствами MS SQL Server. Возможно при "убийстве" процесса будут завершены и несколько сеансов 1С, но вряд ли много. Сервер 1С поддерживает собственный пул соединений с MS SQL, соответственно соединения из этого пула используются только тогда, когда серверу что-то нужно от СУБД. При этом если соединение занято (а оно как видим занято) вряд ли оно будет использоваться для других процессов.

Но предварительно (!) если хотите всё-таки разобраться в проблеме рекомендую выполнить скрипт вроде:

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE sp >SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO

В результате вы получите текст команды SQL Server, на которой, собственно, всё и "зависло". Из неё вам нужна будет таблица в которую производилась запись, далее используя функцию "ПолучитьСтруктуруХраненияБазыДанных()" вы определите таблицу в терминах объектов метаданных в которую производилась запись и смотрите код. Как правило такие неприятные последствия происходят:

Читайте также:  Процессор intel atom d525

1) Ошибки в сетевых подключениях (для толстого клиента в т.ч. в сетевых подключениях клиентов, для тонкого — только в проблемах сети между сервером 1С и MS SQL).
2) Каких то неправильных действиях (отправка почты, запись в файл, запуск внешних обработок, чтения из файла) производимых в транзакциях (при записи, при проведении)

Собственно от них надо избавляться.


Если ничего не помогло (или план B)

ВНИМАНИЕ! Перед выполнением процедур, описанных ниже, сделать полную резервную копию файлов БД MS SQL нужно обязательно.

Есть ещё один — более радикальный способ решения вопроса роста журнала транзакций MS SQL. Но я лично его бы не рекомендовал к использованию. Тем не менее, специалисты Microsoft тоже могут ошибаться,
и SQL Server может содержать ошибки, о которых мы регулярно читаем в BugFix, или же наблюдаем сами, поэтому приведу и этот способ.

Суть его заключается в том что журнал транзакций просто удаляется и создается новый. При этом вы конечно теряете информацию из него и БД можно будет восстановить только из полной копии (которую вы конечно перед этим сделали).

Конечно при этом, особенно если в базе были всё-таки не зафиксированные может быть нарушена логическая целостность, но для этого запускается CheckDB которая в общем и целом приводит базу в порядок. Для аналогии это то же самое что в 1С проврять ссылочную целостность с опцией "Удалять если не найден". Если транзакция полностью не зафиксирована, но от неё остались частично данные, что противоречит принципу атомарности транзакций — эти данные будут удалены.

1) Detach БД из списка

2) Фал *.ldf удаляем (вы же его сохранили уж, да?)

3) Файл *.mdf переименовываем (в любое имя какое нравится)

4) В MS SQL создаём новую (. ) БД с тем же именем, с каким была "больная" БД

5) Останавливаем MS SQL Server

6) Новый *.mdf файл удаляем, а старый переименовываем под "старое имя", подменяя тем самым файл новой БД

7) Запускаем MS SQL Server. При этом будет "битая БД", далее мы её исправляем

8) ALTER DATABASE [Database] SET EMERGENCY

9) exec sp_dboption [Database], ‘single user’, ‘TRUE’
Монопольный режим работы с БД

10) DBCC CHECKDB ([Database], REPAIRALLOWDATA_LOSS)
Ключевая операция — "возвращает БД к жизни". Может выполняться достаточно долго — до получаса на больших БД. Ни в коем случае не прерывайте эту операцию. Результат, где будут собраны исправленные ошибки
на всякий случай сохраните

11) exec sp_dboption [Database], ‘single user’, ‘FALSE’
Сбрасываем монопольный режим

12) ALTER DATABASE [Database] SET ONLINE
Делаем базу доступной.

После чего получаем БД с чистым новеньким логом. На самом деле операция достаточно проста и в большинстве случае не несёт никаких критических последствий. Но всё-таки рекомендую прибегать к ней только в крайнем случае. Описана она не раз и в разных вариациях. Привожу свой вариант, который показался наиболее простым и понятным.

BACKUP LOG WITH TRUNCATE_ONLY is a dangerous command: it empties out the contents of your SQL Server’s transaction log without really backing it up. Database administrators sometimes run this command right before shrinking their log file with a DBCC SHRINKFILE command, thereby freeing up drive space.

Why ‘truncate_only’ is not a recognized backup option.

When you truncate transaction logs, you lose the ability to recover to a specific point in time. You shouldn’t be running this command except during extreme emergencies. Unfortunately, administrators started running it on a regularly scheduled basis, and then they got surprised when they couldn’t restore the way they wanted.

Microsoft recommends that instead of truncating logs, you switch to simple recovery mode instead. That way you don’t generate logs you won’t be using, and you won’t incur performance impacts from repeatedly filling and truncating the logs. You also remove the need to regularly back up the transaction log. This has plenty of drawbacks – if something goes wrong with your database, your only option will be to restore the previous full backup. You could lose hours – maybe even days – of data.

To stop people from shooting themselves in the foot, Microsoft removed this capability completely from SQL Server 2008. If you try to use this command:

You get an error:

The only official workaround in SQL Server 2008 and newer is to switch the database’s recovery model to simple as shown in Books Online. This empties out the transaction log, thereby letting the DBA run a DBCC SHRINKFILE afterwards, then switch the recovery model back to full.

That solution still suffers from most of the same problems as using TRUNCATE_ONLY – the database’s recoverability is compromised. It’s just as bad of a solution, but unfortunately Microsoft can’t remove that workaround since we do need to put databases into simple recovery mode for other reasons.

How to BACKUP LOG WITH TRUNCATE_ONLY in SQL Server 2008

Don’t try what you’re about to see at home. We’re what you call experts. We’ve got years of experience that keeps us safe. Just like TRUNCATE_ONLY, this solution has a ton of drawbacks and compromises your recoverability. This should only be done in cases where a log has grown out of control and must be erased or else the system may crash. In any other situation, you should consider backing up the log with conventional means.

We can fake it by not writing our backup to a real device. SQL Server lets us use the NUL: location as a backup target, so the following will do a log backup without actually saving the contents anywhere:

Читайте также:  Вов как работать с симкрафт

Remember, we’re still not fixing anything here: whatever caused the log file to grow in the first place can happen again and put us right back where we started.

Your data is not actually backed up. This is a giant problem. Don’t leave this script lying around where a junior DBA might see it and reuse it for regular backups. This should only be used to impress your friends with your useless knowledge of SQL Server, much like I’m doing here.

Other Common Questions About Transaction Log Backups

Q: Why shouldn’t I shrink log files?
A: When SQL Server needs to grow the log file back out, it’s a blocking operation. Everything in the database is put on hold while the log file grows out. We can avoid this for data files by using Instant File Initialization, but that doesn’t take effect for log files.

Q: But I had a one-time log file growth and I swear it’ll never need to grow that big again.
A: Okay, cool – go ahead and shrink the log file this once, but make sure you leave enough log file space for normal operations.

Q: How big should the transaction log be for normal operations?
I generally start at 25% of the data file size. If you plan on rebuilding your indexes, the log needs to be large enough to hold the size of your largest object, plus space for transactions that are happening during your index rebuilds. If your database is dominated by a single large object, then it might need to be bigger than 25%. Plus, if you’re using things like replication, mirroring, or AlwaysOn Availability Groups, you’ll need enough log space to hang on to transactions during replica downtime – until that replica comes back up and can download the rest of the transactions it missed during the outage.

Q: What can I do to keep transaction log requirements low?
Check out Kendra Little’s webcast on The Developer And The SQL Server Log where she explains the physical structure of the transaction log and how you can aim for minimal logging.

Q: What are the biggest mistakes people make with the log file?
Watch Brent Ozar’s video DBA Darwin Awards: Log File Edition where he talks about automatically shrinking your logs, ignoring Virtual Log Files (VLFs), and bulk inserts.

Q: How can I find out if Virtual Log Files (VLFs) are a problem for me?
Run our free sp_Blitz®, a health check stored procedure that catches databases with abnormal VLFs, bad growth configurations, and much more.

Want to Learn More About SQL Server?

We’ve got a free 6-month DBA Training Plan email series that walks you up Ozar’s Hierarchy of Database Needs, going from backups through security to capacity planning and performance tuning.

We also have a Senior DBA Class:

60 Comments . Leave new

Another reason for litespeed and the benefits to having chosen the product for my own installations.
Thanks, this is pretty cool! (as scary as the topic is 😉 )

+1 for the Mythbusters Reference. 🙂

Brent – I’m a big fan and love reading your blog but this post is odd to me, for several reasons.

1) You say that “Microsoft even recognized the problems with [TRUNCATE_ONLY] and removed this capability completely from SQL Server 2008” – is that really why they removed TRUNCATE_ONLY? My understanding was that it was removed because it is redundant now that we have the SIMPLE recovery model. I haven’t heard – I honestly don’t know – but I know of now “problems” with TRUNCATE_ONLY that led to its demise; only that switching to SIMPLE recovery model instantly has the desired effect (thus rendering TRUNCATE_ONLY surplus to requirements). If someone from MSFT would chime in and set the record straight, that would be kinda cool.

2) There’s no need to use LiteSpeed (though a nice plug) – you can use the NUL: drive: BACKUP LOG MyDb TO DISK=’NUL:’. That has the same effect – it tricks SQL into thinking that a log backup has taken place when, in fact, it hasn’t.

3) You imply that switching to SIMPLE recovery model is “just as bad of a solution [as using TRUNCATE_ONLY]” yet I would imply that your suggested solution (or my use of the NUL: drive) is a much, much worse solution. By switching to SIMPLE, you are doing it the recommended way. Using the NUL: drive or LiteSpeed is just making life far more complicated than it needs to be. Can you make a case in which it is actually better to use LiteSpeed with -nowrite than to actually go “by the book”? I cannot think of a single scenario in which using LiteSpeed is preferable. In fact, I can’t think of a single “pro”, only “cons”. If the log has “grown out of control” then switching to SIMPLE will clear out the inactive entries nearly instantly (and generally faster than a faux log backup).

In fact, I’ve covered doing this in a training video in my SQL Server 2008 DBA training video course:

Like I said, I do enjoy reading your blog and tweets but I think that the SQL community should be aware of (a) the alternatives, and (b) that this goes against the “recommended” way from MSFT.

Hi, Scott! Great questions.

1. I couldn’t tell you what the reasoning was inside Microsoft, but it just appears that way to me and to the folks I’ve talked to.

Читайте также:  Разбивка текста по столбцам в excel

2. That’s a great point! That works too.

3. Absolutely, that’s why I point out repeatedly in the article that you shouldn’t use this trick. I’m just showing people that it can be done. You mentioned that doing it my way is “far more complicated” – keep in mind that my method only requires one statement. Your method requires multiple statements.

You’re absolutely, completely correct that this method is dangerous and is not recommended, and I go way, way out of my way throughout this article to point that out. If there’s anything I can say or do in here to make it more clear, I’d love to hear it. Thanks!

Kudos for replying so quickly – I’m generally more of a lurker than not so please don’t take my only responding with something “negative” to say to imply anything other than this post inspired me to stop lurking! Hopefully we’ll get the chance to meet up at PASS or DevConn this Fall.

I know that you there is a lot in this post to let people know *not* to do it this way but here’s the thing: the page title and the H3 tags on the page contradict what you’re saying. The page title is “How to Do BACKUP LOG WITH TRUNCATE_ONLY in SQL Server 2008” but this post is not about how to do that; it’s about how to use an undocumented workaround in a third party software tool. Another example is that you have the section on how to use LiteSpeed titled “How to Do TRUNCATE_ONLY in SQL Server 2008” yet it’s really about using LiteSpeed’s -nowrite switch, not about how to quickly mark the inactive entries in the log as free space.

Now sure – the second sentence of that section says, “Don’t try what you’re about to see at home” and talks about the “cons” but remember: it’s in a section called “How to Do TRUNCATE_ONLY in SQL Server 2008” on a page titled “How to Do BACKUP LOG WITH TRUNCATE_ONLY in SQL Server 2008”. What are people supposed to think who come here from a search for those phrases? What would *you* think if you were a junior DBA coming from SQL 2000 to 2008 trying to figure out what to do and you saw that headline and this page title? You’d probably think, “Well, this guy’s really well known and super smart so I’ll do it with LiteSpeed. Besides, he said something about SIMPLE recovery model but he didn’t show any examples so that must not be the ‘real’ way to do it.”

So you said in the comment follow-up, “If there’s anything I can say or do in here to make it more clear, I’d love to hear it.” It’s your blog, of course, so I’m only making suggestions but, to me, I’d prefer to see the page title and the title of that section made more clear, something akin to “How to Use LiteSpeed to Simulate BACKUP LOG WITH TRUNCATE_ONLY in SQL Server 2008”. Or keep the page title but add a paragraph that fleshes out using the SIMPLE recovery model and taking a backup (pros/cons/code).

Thanks for taking the time to listen and keep up the great work!

Scott – I love it! Your questions segue perfectly into an article in my five-part series this week on how to blog better. One of the posts talks about how to use page titles and H3 tags. I’ll give you a hint: those tools are for what people are searching for, not to summarize your content. If I put an H3 with “How to Use LiteSpeed to Simulate…” nobody will ever find this post, because DBAs aren’t sitting around asking themselves, “Gee, how could I use LiteSpeed to simulate a truncate_only?” Instead, you have to write H3’s in a way that matches what people are searching for.

I wish I had a time to do a full writeup on the pros/cons/code of switching to simple mode, but unfortunately I can’t cover everything here in full, so what I’ve done is added a link to Books Online to cover how to do that.

I applaud your participation! I’m glad you popped up out of the shadows, and you’re making the post better. If I just holler with a bullhorn and nobody responds, then my work doesn’t get any better. (Believe me, hahaha.) Look forward to meeting you!

You’ve given me a blog idea, this isn’t the first time I’ve seen the backup to nul trick (of course the others didn’t have 16 warnings on them) 😉

Think I’ll tackle the backup to nul vs simple recovery, effects side effects and warnings.

I have tried the “new and improved” method from Microsoft…and it doesn’t work! I inherited a database whose log has grown enormous. I have tried the “set to simple”, “back to full”, “back to simple”…and the log is still gigantic.

Like you mentioned earlier, I too am a professional and I know when to use TRUNCATE_ONLY and when not to use it.

You would think that Microsoft would trust professionals to know when to use something and when not to! If that’s their reasoning they better take away TRUNCATE TABLE before somebody hurts themselves.

Anthony – just changing from simple, full, simple doesn’t change the size. You also have to shrink the log.