SQL Server log shrinking Issue [duplicate]

Question

Possible Duplicate:
Why Does the Transaction Log Keep Growing or Run Out of Space?

I have a 300 GB database log in our SQL Server. I want to shrink all the logs but it is not allowing me to do so. I tried by using query and UI in SQL Server 2008 R2 . I used

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 7);
GO

But without any error database logs are not shrinking.

Then I want to use one more method as below :

I will detach the database from SQL Server, then after the move particular .LDF file to some other location and attach only the database file back to SQL Server. I am able to successfully move all the logs from SQL Server to some other server.

Please let me know this is good practice to move transaction log to some other drive. If not please suggest some other solution to recover from shrinking database log.

Answer 1

If you are shrinking a log file, you won't be able to shrink it any less than the active log portion. In other words, if you are in Full Recovery Model, and you haven't done any transaction log backups (which is why it could have grown that large), then running a DBCC SHRINKFILE will do absolutely nothing.

BOL has an example on how to do this:

USE AdventureWorks2012;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2012
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL;
GO

That was taken straight from BOL on DBCC SHRINKFILE.

It is worth noting in the above example that changing the Recovery Model to simple breaks the log chain. So once this operation is complete, you need to immediately start a new log chain by running a database backup.

It is also worth noting that if your log file is growing like this, you are not taking [enough] transaction log backups (provided you are indeed in Full Recovery Mode, as this would only happen in very rare situations in Simple Recovery). You may need to either start taking transaction log backups, or backup your log more often so it truncates the trans log and creates a portion of the file reusable so you don't have this file growth.

Answer 2

A couple of points not mentioned in the other answers.

---

There can be other reasons why the log isn't clearing besides a lack of log backups (although this is usually the case), such as database mirroring or replication.

Run this query to determine the recovery model of the database, and why the transaction log cannot be cleared:

SELECT
    name,
    log_reuse_wait_desc,
    recovery_model_desc
    FROM sys.databases

The full list of log_reuse_wait_desc values can be found here. As mentioned, you'll probably see LOG_BACKUP, but it's good to be sure there isn't anything else going on in the situation that will complicate matters.

---

You can use a safe undocumented command DBCC LOGINFO (run in the context of the target database) to dump out information about the transaction log's virtual log files (aka VLFs, described in another answer). A status value of 2 means the VLF is in use, and a 0 means it's not in use (a value of 1 is not possible).

As another answer mentioned, SQL Server will only shrink a log file as far back as the last in-use VLF.

If the database is in FULL or BULK_LOGGED recovery and you see a lot of zeros, but a 2 on the last one, you may have to wait for some transactions on the database to get the log to wrap around, then take a log backup to clear out the trailing VLFs, and finally run the shrink again.

If the database is in SIMPLE recovery, you may have to manually run a CHECKPOINT to initiate log clearing before attempting to shrink again.

Answer 3

A safe way to shrink the ldf file is to switch to simple recovery model and then perform a shrink.

Using SQL Server Management Studio:

1. Log into the SQL server.
2. Expand the "Databases" Folder, right click on the database, select properties.
3. In the "Options" page change "Recovery model:" to "Simple" and press the "OK" button.
4. You may want/need to backup the database (Right click the database, Tasks\Back Up...).
5. Right click on the database, open the "Tasks" sub menu, open the "Shrink" submenu and select "Database".
6. In the new window check the "reorganize files before releasing unused space." check box and press the "OK" button.
7. Right click on the database, open the "Tasks" sub menu, open the "Shrink" submenu and select "Files".
8. For "File Type:" select "Log".
9. Make sure "Release unused space" radio button is selected and press the "OK" button.

This will fix your issue, though you will no longer be able to restore from the transaction logs. Check the documentation to see which recovery model is best for you. I usually use simple since we do a large number of inserts, and hourly/daily backups are good enough for our needs. Also, if you use simple then the log does not grow so large.

If you do not want to change the recovery type, try doing a full backup before shrinking the DB and the DB files. It is recommended that you set the "Maximum free space in files after shrinking" to at least 10% on the Shrink Database screen for performance reasons. If you are using a db with a log which can grow to 300GB fairly quickly then you will want to leave it much larger, and set the "Shrink file to:" to 1024 MB on the "Shrink File" screen.

Let us know if you need more help, otherwise please accept an answer. The community does not like to answer questions from people who do not accept answers. :)

Edit: The steps above should have worked. You have probably done something wrong (like select the wrong database). For an alternate way of doing it please see this article.

Also, when performing the backup make sure the "Backup type:" is full, the "Copy-only Backup" check box is not checked, and "Database" is selected under "Backup component:". If possible then select the "Truncate the transaction log" radio button for "Transaction log" on the "Options" page.

Try taking the database off line, then bringing it back online before running the steps above. If it fails to go off line then you will need to manually terminate the SQL connections (users are still logged into the database preventing these changes).

If these options still do not work, then you will have to use a less safe method like backing up the database to a file and restoring it over the existing database, then shrinking the files.

Other links:

• Similar instructions as mine, but with pictures.
• Using scripts (as always learn what the scripts do before you use them).
• Another picture version. This one references a document (white paper) from Microsoft about managing the SharePoint 2007 and 2010 servers.

Also, make sure you are modifying the database associated with the file. You can do this by opening the database's properties and going to the "Files" page.

Answer 4

i wont repeat what @Trisped said, that if you convert to simple recovery what will be the consequences and also the need to back up your database before doing the steps below

Code below will convert your database recovery to simple and will shrink the transaction log file to its minimum and will set the recovery to full again incase you want it

ALTER DATABASE [DBNAME] SET RECOVERY SIMPLE;
use DBNAME
DBCC SHRINKFILE(DBNAME_log,1)
ALTER DATABASE [DBNAME] SET RECOVERY FULL;

but i recommend before shrinking the file to see the results of the

DBCC SQLPERF(LOGSPACE)

This will show you the Size of each log file of each database and will show you what space used in the transaction log

or

If the shrink operation runs without error as you said, but the file does not appear to have changed in size, verify that the file has adequate free space to remove by performing one of the following operations:

Run the following query.

use DBNAME
 SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

i recommend reading the following

http://msdn.microsoft.com/en-us/library/ms189493.aspx Remarks section

and also read about (Shrinking the Transaction Log) http://msdn.microsoft.com/en-us/library/ms178037(SQL.105).aspx

you need to note that a log file can only be shrunk to a virtual log file boundary, shrinking a log file to a size smaller than the size of a virtual log file might not be possible, even if it is not being used

to know more about what is a Virtual log files Read here http://msdn.microsoft.com/en-us/library/ms179355(SQL.105).aspx

The SQL Server Database Engine divides each physical log file internally into a number of virtual log files. Virtual log files have no fixed size, and there is no fixed number of virtual log files for a physical log file. The Database Engine chooses the size of the virtual log files dynamically while it is creating or extending log files. The Database Engine tries to maintain a small number of virtual files. The size of the virtual files after a log file has been extended is the sum of the size of the existing log and the size of the new file increment. The size or number of virtual log files cannot be configured or set by administrators.

---

the second part of the questions I will detach the database from SQL Server, then after the move particular .LDF file to some other location and attach only the database file back to SQL Server

if you detach and move the ldf file, when you try to attach again you will see a message (Not Found) and be given the option to either point to its new location or just remove it and if you remove it a new transaction log file will be created .

---

Last part of your questions

Please let me know this is good practice to move transaction log to some other drive. If not please suggest some other solution to recover from shrinking database log.

Detaching & Attaching is good method if you can stop the the application that is in your case stop users from using sharepoint..

Answer 5

I recently ran into an issue with a database whose log file kept growing even with log backups occuring. I eventually found out that it had something to do with the snapshot replication that was occuring on the database. Once I changed the replication from Snapshot to Transactional the logged starting working as intended again.

Check and see if you have replication running on this database.

Answer 6

I had the same problem with the log file growing on a database that was running in simple mode. The log file had grown to 70gig. even after a checkpoint I still was not able to shrink the log file. I was using replication with snapshot. The subscriber sql service had not been running for a few days. I was able to solve the problem by adjusting the publication properties on publisher from the default of a very large number of hours to one hour. After which I was able to checkpoint and shrink the log.