Shrinking Log file

SQL Server Administration
1

Hi All,

Have a question about shrinking a transaction log file and hope someone can point me in the right direction.

One of the transaction logs is about 70GB in size, and the drive it is located on currently has 70GB free space.

When I use SSMS to Shrink the log file, nothing seems to happen, the dialog box for shrinking the log file disappears and that´s it.

Having read some information on the net, it mentions that it can take some time for the log file to actually shrink.

I started the shrink on Friday afternoon and have checked the size of the log file this morning, and it is the exactly the same size.

The setup is Windows Server 2008 R2/SQL Server 2008 and the database is set to FULL recovery.

Would appreciate any help.

Thanks,

Steve

2

Did you take a log backup before shrinking the file?
If not try this way.

3

Excuse my ignorance, but as I am really a beginner with SQL, how do I do that through SSMS?

Having just checked the information in SSMS when I look at shrinking the log file again, it shows Available free space: 6361.55MB (8%).

Not sure what this means exactly, so do not want to even attempt to guess.

Thanks,

Steve

4

you have to either back up the log first, or change to Simple recovery, then shrink the file. for more see here: http://www.sqlserverlogexplorer.com/how-to-clear-transaction-with-dbcc-shrinkfile/

5

You can run this query to see what your database recovery model is, when backups were made, and what type. You should see [Type] = "L" for Log Backups

DECLARE @database_name nvarchar(256) = N'MyDatabaseName'
--
SELECT	D.recovery_model_desc, D.name
FROM	master.sys.databases AS D
WHERE	D.name = @database_name
--
SELECT TOP 500 WITH TIES
	BS.backup_start_date,
	BS.type,
	[Size KB]=CONVERT(int, BS.backup_size/(1024)),
	BS.database_name
FROM	msdb.dbo.backupset AS BS
WHERE	1=1
	AND BS.database_name = @database_name
ORDER BY BS.backup_start_date DESC

Is the database in FULL or SIMPLE recovery model?

If in SIMPLE then your Log Shrink should work, if not something is wrong! It might help to re-start the SQL Service (in case there is an open, orphaned, transaction right at the end of the log file for some reason)

If FULL then what I do is:

Take a LOG Backup
Shrink the LOG file
If the Shrink has not reduced the file by enough then:
Take another LOG Backup
Repeat Shrinking the LOG file again

If this does not shrink it enough then my earlier point applies here too!

You can make a LOG Backup using SSMS by RightClick the Database - Tasks - Backup ... BUT ...

It is critical (for a database in FULL recovery model) that the LOG Backup chain is preserved, otherwise you will not be able to restore for disaster recovery. Thus it is important that wherever you "store" the Log Backup is somewhere where anyone needing to perform disaster recovery can find it - so NOT on the C: drive of your PC!! but on the Server itself is good.

For that reason do NOT change the database from FULL to SIMPLE to work around this - that will also prevent Disaster Recovery.

You need to find out what the cause of your large Log file is. Perhaps your database is in FULL recovery model and there are currently NO Log Backups? If so either:

You do not need FULL recovery mode? You would be happy to restore from the last full backup and lose all the data changes / work SINCE that backup ? If that is the case then SIMPLE may be appropriate

If you DO need to be able to recover to a point-in-time, and want the minimum data loss (quite often "no data loss" in the case of a database corruption) then a) you need FULL Recovery Model and b) you need frequent (no less often than every 10 minutes) Log Backups.

6

We need to get some db details to help you solve this. Run this query and post the results:

SELECT name, state_desc, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
WHERE name = '<your_db_name_goes_here>'

7

@SteveP,
From https://msdn.microsoft.com/en-us/library/ms190925.aspx#FactorsThatDelayTrunc: Log truncation can be delayed by a variety of reasons. You can discover what, if anything, is preventing log truncation by querying the log_reuse_wait and log_reuse_wait_desc columns of the sys.databases catalog view. The following table describes the values of these columns.
The transaction log file is internally made up of virtual log files (VLF). As all the transactions in the VLF are committed or rolled back the VLF is marked reusable and therefore "shrinkable" in Simple recovery model. If you are running Full recovery model, the log file needs to be backed up before marking the VLF as reusable. This allows recovery to a point in time. No matter what recovery model you're using long running transactions can prevent log truncation.

8

Thanks to everyone for the help here. It is really appreciated.

I will go through each response and hopefully find the solution which helps me.

Thanks again!

Steve

9

You can try this

BACKUP LOG [My DB] TO DISK = 'D:\SQLBackup\20110824-MyDB-Log.bak'
GO
DBCC SHRINKFILE (N'My DB_Log' , 12800)
GO