Как уменьшить лог файл базы sql

How do I decrease the size of my sql server log file?

So I have been neglecting to do any backups of my fogbugz database, and now the fogbugz ldf file is over 2 and half gigs. Thats been built up over the six months we’ve been using fogbugz.

I backed up the database, then I backed up, and truncated the transaction log, yet the transaction log is still 2 and a half gigs. I did a shrink on the log file and its still 2 and a half gigs. Nothing I do seems to shrink the file in size.

Is there anyway to fix the problem? Or is the only way back at this point to detach the database, delete the log file and then reattach with a new one?

6 Answers 6

• Perform a full backup of your database. Don’t skip this. Really.
• Change the backup method of your database to «Simple»
• Open a query window and enter «checkpoint» and execute
• Perform another backup of the database
• Change the backup method of your database back to «Full» (or whatever it was, if it wasn’t already Simple)
• Perform a final full backup of the database.
• Run below queries one by one
  1. USE Database_Name
  2. select name,recovery_model_desc from sys.databases
  3. ALTER DATABASE Database_Name SET RECOVERY simple
  4. DBCC SHRINKFILE (Database_Name_log , 1)

Welcome to the fickle world of SQL Server log management.

SOMETHING is wrong, though I don’t think anyone will be able to tell you more than that without some additional information. For example, has this database ever been used for Transactional SQL Server replication? This can cause issues like this if a transaction hasn’t been replicated to a subscriber.

In the interim, this should at least allow you to kill the log file:

1. Perform a full backup of your database. Don’t skip this. Really.
2. Change the backup method of your database to «Simple»
3. Open a query window and enter «checkpoint» and execute
4. Perform another backup of the database
5. Change the backup method of your database back to «Full» (or whatever it was, if it wasn’t already Simple)
6. Perform a final full backup of the database.

You should now be able to shrink the files (if performing the backup didn’t do that for you).

This is one of the best suggestion in which is done using query. Good for those who has a lot of databases just like me. Can run it using a script.

Ensure the database’s backup mode is set to Simple (see here for an overview of the different modes). This will avoid SQL Server waiting for a transaction log backup before reusing space.

Use dbcc shrinkfile or Management Studio to shrink the log files.

Step #2 will do nothing until the backup mode is set.

You have to shrink & backup the log a several times to get the log file to reduce in size, this is because the the log file pages cannot be re-organized as data files pages can be, only truncated. For a more detailed explanation check this out.

WARNING : Detaching the db & deleting the log file is dangerous! don’t do this unless you’d like data loss

I had the same problem, my database log file size was about 39 gigabyte, and after shrinking (both database and files) it reduced to 37 gigabyte that was not enough, so I did this solution: (I did not need the ldf file (log file) anymore)

(**Important) : Get a full backup of your database before the process.

Run «checkpoint» on that database.

Detach that database (right click on the database and chose tasks >> Detach. )

Move MyDatabase.ldf to another folder, you can find it in your hard disk in the same folder as your database (Just in case you need it in the future for some reason such as what user did some task).

Attach the database (right click on Databases and chose Attach. )

On attach dialog remove the .ldf file (which shows ‘file not found’ comment) and click Ok. (don`t worry the ldf file will be created after the attachment process.)

After that, a new log file create with a size of 504 KB.

In step 2, if you faced an error that database is used by another user, you can:

1.run this command on master database «sp_who2» and see what process using your database.

2.read the process number, for example it is 52 and type «kill 52», now your database is free and ready to detach.

If the number of processes using your database is too much:

1.Open services (type services in windows start) find SQL Server . process and reset it (right click and chose reset).

SELECT * FROM Vzakladke.net

Существует ситуация, когда LDF файл занимает много гигабайт места (файл с постфиксом _log), и его необходимо уменьшить.

Это происходит когда база в SQL находится в режиме Full, т.е. с фиксацией всех произведенных транзакций. Модель Full позволяет восстановить состояние базы SQL на любое время, в то время, как модель Simple не позволяет этого сделать, а только восстановить базу из бэкапа. Смысл модели Full в том, что в журнал транзакций LDF записываются ВСЕ транзакции и там остаются, ну до определенного времени, например, до операции shrink. Таким образом SQL последовательным откатом транзакций назад может восстановить состояние базы на любой момент времени периода записанных в LDF транзакций.

Переход в режим Simple приведет к тому, что в файле LDF будут находиться только незавершенные транзакции, что уменьшит размер этого файла.

Первое что нужно сделать, перевести базу в модель восстановления Simple (при этом настроить механизм создания беэкапов базы, если этого до сих пор не сделано). Эту операцию можно делать "на ходу".
Однако, перевод в simple автоматически не уменьшает размер файла транзакций. Можно, провести операцию shrink (сжатие базы) сразу, но лучше сначала сделать полный бэкап базы средствами SQL (есть там в SQL-е по этому поводу одна маленькая хитрость), а потом сделать shrink как файлу базы MDF, так и файлу журнала транзакций LDF. Размер базы тоже должен уменьшиться, но не на много, а, вот, размер файла транзакций LDF, если было сделано все правильно, должен стать практически нулевым (в случае, когда в этом момент в базе нет активной работы пользователей).
Операции бэкапа средствами SQL, и shrink-а, можно делать не выгоняя пользователей, эти операции могут, разве что, сказаться на производительности. Настоятельная рекомендация сделать резервные копии перед началом этой операции.

Для выполнения операции по очистке логов необходимо запустить восстановление:

Если не охота мучиться с запросами можно сделать через GUI: правой кнопкой на базе -> задачи -> шринк -> файлы -> выбираем лог (там будет видно на сколько процентов можно уменьшить).
Иногда, если лог большой — например 50 Гб, то уменьшать (шринкать) его надо 2 раза — с первого раза уменьшается, но не полностью.

Уменьшение размера логов баз SQL

Чаще всего логи хранятся в файле .ldf рядом с файлом базы данных. Если в настройках базы данных нет ограничения на размер логов и используется полная модель восстановления, то файл логов может разрастаться до очень больших размеров и тогда его нужно очистить.

Заходим в SQL Server Management Studio, подключаемся к нужному серверу, выбираем нужную базу и открываем для неё форму свойства в контекстном меню и в разделе “Параметры” меняем модель восстановления на простую.

Закрыв форму “Свойства”, выбираем в контекстном меню базы “Задачи”-“Сжать”-“Файлы”

В появившийся форме выбираем тип файла “Журнал”, и в настройках операций сжатия пункт “Реорганизовать страницы, перед тем как освободить”. Если в пункте “Сжать файл” установить 0, тогда накопившиеся на момент сжатия логи будут удалены.

При необходимости, возвращаем обратно модель восстановления данных “Полная”.

В продолжение темы, рассмотрим удаление неиспользуемых журналов регистрации из папки Srvinfo Сервера Предприятия 1С.

Для каждой базы данных 1С существует своя директория хранения журнала регистрации и выглядит она таким образом

После удаления базы данных с сервера 1С папка журнала регистрации не удаляется из Srvinfo. Поэтому из множества папок в Srvinfo могут находиться и те, которые давно не используются и просто занимают место на жестком диске. Вычислить эти папки можно открыв файл 1CV8Clst.lst, который находится так же в reg_1541.

Копируем <Идентификатор базы на сервере> из папки Srvinfo и ищем в файле 1CV8Clst.lst. Если идентификатор в файле не найден, то папку можно удалять.

В директории Srvinfo находится папка с названием вида snccntx+<Идентификатор базы на сервере>. Эта папка содержит сеансовые данные и ее лучше не удалять без необходимости, да и много места она не занимает.

Shrink SQL Server Transaction Log for all Databases

I need to quickly generate T-SQL commands to shrink all SQL Server user database log files or shrink a single database log file back to the original size or as small as possible. Can you provide a script?

Solution

My solution involves creating a T-SQL stored procedure in a SQL Server database called dbo.usp_ShrinkAllLogsExcludeSysDBS that will get a parameter for the database name. The parameter will default to '%' meaning all user databases are in scope if a database is not specified.

The procedures uses a VARCHAR (max) parameter (@TSQLExec) in order to execute the T-SQL shrink statement (or block of statements) dynamically.

The code executed for each user database will contain a CHECKPOINT statement and a DBCC SHRINKFILE statement. Issuing a manual CHECKPOINT statement in the SQL Server database will write the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and also record the information in the transaction log.

The DBCC SHRINKFILE statement will shrink the current database's specified data or log file size. In this case I will not use any target database file size. If this parameter is not specified, then the DBCC SHRINKFILE statement will reduce the file size to its creation size. I don't use a target size parameter in order to avoid any errors.

The procedure joins sys.databases and sys.master_files system views in order to generate the CHECKPOINT and DBCC SHRINKFILE statement for all the user databases. It filters the system databases (DBID > 4) because these statements are not recommended for the system databases.

SQL Server Shrink Database T-SQL Code

Here are examples for executing this stored procedure:

Here are the results for running against all databases. You can see for database Test2 the log went from 8072MB to 8MB, but the others didn’t change because it was unable to shrink the log file any further.

Next Steps

• You can create and compile this simple procedure in your master database and use it as a simple SQL tool for shrinking all the user database log files or shrinking a specific user database log.
• The procedure was tested for SQL Server 2014 and 2017, but should work for SQL Server 2005 and later.

About the author

Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

Comments For This Article

try to use this in place of the CONCAT statement.

SELECT @TSQLExec =
@TSQLExec + ‘USE [‘ + d.NAME + ‘]; CHECKPOINT; DBCC SHRINKFILE ([‘ + f.NAME + ‘]) with no_infomsgs;’ + Char(13) + Char(10)

CREATE OR ALTER PROCEDURE dbo.usp_ShrinkAllLogsExcludeSysDBS (@dbname SYSNAME = ‘%’)
AS
BEGIN

DECLARE @TSQLExec VARCHAR (MAX) = »;
SET NOCOUNT ON;

IF OBJECT_ID(‘tempdb..#Temp’) IS NOT NULL
DROP TABLE #temp

CREATE TABLE #temp (dbname sysname, dbid int, logFileSizeBeforeMB decimal(15,2), logFileSizeAfterMB decimal(15,2),is_preferred_replica INT);

WITH fs
AS
(
SELECT database_id, TYPE, SIZE * 8.0 / 1024 SIZE
FROM sys.master_files
)
INSERT INTO #temp (dbname, dbid, logFileSizeBeforeMB,is_preferred_replica)
SELECT
name, database_id,
(SELECT SUM(SIZE) FROM fs WHERE TYPE = 1 AND fs.database_id = db.database_id) LogFileSizeMB,x.is_preferred_replica
FROM sys.databases db
OUTER APPLY (SELECT [master].sys.fn_hadr_backup_is_preferred_replica(db_name(db.database_id))is_preferred_replica) x
WHERE database_id > 4 AND x.is_preferred_replica = 1
AND NAME LIKE @dbname;

SELECT @TSQLExec = CONCAT (
@TSQLExec,
‘USE [‘,
d.NAME,
‘]; CHECKPOINT; DBCC SHRINKFILE ([‘,
f.NAME + ‘],0,TRUNCATEONLY) with no_infomsgs;’ ,
Char (13),Char (10))
FROM sys.databases d
OUTER APPLY (SELECT [master].sys.fn_hadr_backup_is_preferred_replica(db_name(d.database_id))is_preferred_replica) x
,sys.master_files f
WHERE d.database_id = f.database_id
AND d.database_id > 4
AND x.is_preferred_replica = 1
AND f.type = 1
AND d.NAME LIKE @dbname;
PRINT @TSQLExec;
EXEC (@TSQLExec);

WITH fs
AS
(
SELECT database_id, TYPE, SIZE * 8.0 / 1024 SIZE
FROM sys.master_files
)
UPDATE a
set a.logFileSizeAfterMB = (SELECT SUM(SIZE) FROM fs WHERE TYPE = 1 AND fs.database_id = db.database_id)
FROM #temp a
inner join sys.databases db on a.dbid = db.database_id
WHERE database_id > 4
AND NAME LIKE @dbname

FYI : Not working on SQL Servers prior to 2012 because of missing CONCAT

Thank you very much.

Good point about the NULL issue

The temporary table needs to allow for NULLs in the logFileSizeAfterMB column. So add the NULL to the CREATE TABLE line.