I'm a beginner with SQL.  I'm running SQL server 2008 R2.  I have a transaction log that has grown to 49gb.  I run transaction log backups nightly but this hasn't been truncating the log probably due to the fact that I didn't set a growth limit on the file (??).  I've run a transaction log backup manually and when I go to shrink the file, the available free space shows as 99%.  The initial size of the file is set to 49gb, which means that it isn't shrinking.  I saw some previous posts which talked about running the command SHRINKFILE({logical file name}, target_size) in order to shrink the file to the target size and reset the initial size to the target size.  I'm wondering if it is okay to do this during production hours?  Also, is the target_size in mb?  So, if I want to shrink the file to 1gb and the logical name of the trans log file is "castlegar_Log" then I would run the query SHRINKFILE({castlegar_log}, 1000).  Does this look correct?  Does anybody have other suggestions for how I should deal with this?  Thank you.

You need to take regular full backups as well; taking a full backup (a) issues a checkpoint to the database and (b) transfers all committed transactions to the mdf/ndf files.  Until you do a full backup you cannot shrink the Transaction Log.

Ideally you should take a full backup once a night and regular TL backups through the working day.

The problem is with initial size of log file which is 49 G normally you cannot shrink it below that. You need to follow below procedure to shrink it more. I must tell you log file shrinking is NOT ADVISABLE

ALTER DATABASE [db_name] 
SET RECOVERY SIMPLE;

sp_helpdb db_name --get logical log file name

CHECKPOINT;

DBCC SHRINKFILE (db_name_log,0);

ALTER DATABASE [db_name]
MODIFY FILE (db_name_log,SIZE=240MB,MAXSIZE=UNLIMITED,FILEGROWTH=500MB);--dummy values change accordingly

ALTER DATABASE [db_name] 
SET RECOVERY FULL;

1) You should first run the command to see why if not allowing you to shrink..

select log_reuse_wait_desc,* from sys.databases. it might be log backup, active transaction or anything.

2) Once you rectify this run the shrink file command thru GUI (SSMS) or t-SQL.

3) Also, is the target_size in mb?

Yes Target Size is mentioned in MB's and your command should be like

DBCC SHRINKFILE('castlegar_log', 1024).  

• General practice is to keep your t-Log growth restricted else you will end up in Disk space issues.
• Don't shrink while some transactions are running against that database
• If you think its growing frequently have more t-log backups in place

So, a couple of things.  Check with your developers to see if you really need the database to be in FULL RECOVERY mode.  FULL RECOVERY allows point-in-time recovery which is dependent on the transaction log.  If it turns out that they don't need point-in-time recovery, suggest if they can get by with daily backups on SIMPLE recovery.  This will limit transaction log growth since the transaction logs are not kept after the transaction has completed.

Back to your original problem.  The transaction log has to be backed up regularly as you are already doing.  I think it automatically truncates the log by default now as well.  Then you need to set the initial size of the log back to something manageable through the properties setting or use the SHRINKFILE command.  The key is to shrink the file right after the transaction log backup.  If not, the log may fill up to a point higher than where you would like to set it.

Alternatively, check out this Microsoft article.  It has a whole section devoted to the transaction log not shrinking.

https://technet.microsoft.com/en-US/library/ms189493%28v=sql.105%29.aspx

• Edited by J I M B O 12 hours 21 minutes ago

use the below code to reduce the transaction log file to s specified size. 

It is recommended transaction log size to be 20-80 % of the data file size.

YOu can only reduce the lof size in simple recovery model only. so if your database is not in single recovery first set to simple mode.

ALTER DATABASE [Databasename] SET RECOVERY SIMPLE WITH NO_WAIT

go to your database >> properties>> files>> copy the logical name of your log file.

DBCC SHRINKFILE(N'logfile logicalname', 100)

i set it to 100 MB change tot eh required value

then put back your database tot eh previous recovery model using alter command.