Hi,

I have added additional T-log files in SQL Server 2012.

Can I follow below T-SQL to delete them? This is the prod database and is very critical.

1. Check the space of T-log by executing "dbcc sqlperf (logspace)"

2. Check the status of log by executing "select name,log_reuse_wait,log_reuse_wait_desc from sys.databases"

The output should be NOTHING

3. Empty the log file by executing "dbcc shrinkfile(SOBLOG,emptyfile)

once this command finishes, then execute 

4. alter database SOB remove file SOBLOG

Is it safe to use above procedure.

Hi.

Deleting a data or transaction log file removes the file from the database. You cannot remove a file from the database unless the file has no existing data or transaction log information; the file must be completely empty before it can be removed. To empty a data file by moving data from the data file to other files in the same filegroup, use the DBCC SHRINKFILE statement and specify the EMPTYFILE clause. Because the Database Engine no longer allows data to be placed on the file after the shrink operation, the empty file can be removed by using the ALTER DATABASE statement or SQL Server Management Studio.

You cannot move transaction log data from one log file to another to empty a transaction log file. To remove inactive transactions from a transaction log file, the transaction log must be truncated or backed up. When the transaction log file no longer contains any active or inactive transactions, the log file can be removed from the database. For more information, see Managing the Transaction Log.

more informations here:

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

The steps you mention are correct. Be aware that you may need to backup the transaction log in order for the log_reuse_wait_desc to show NOTHING.  Don't forget to delete the old file from the file system, but only have you successfully remove the file from the database. 

You will be able to remove the file only if SOBLOG file isn't in use

To check if file is in use run(undocumented command):
DBCC LOGINFO(database)  --Status=0

http://sqlblog.com/blogs/tibor_karaszi/archive/2009/06/17/remove-transaction-log-files.aspx

• Proposed as answer by Michelle LiMicrosoft contingent staff, Moderator 18 hours 36 minutes ago

To delete data or log files from a database

1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

2. Expand Databases, right-click the database from which to delete the file, and then click Properties.

3. Select the Files page.

4. In the Database files grid, select the file to delete and then click Remove.

5. Click OK.

And here are some tips that you don't want to do:

Backup the log with TRUNCATE_ONLY option and then SHRINKFILE - For one, this TRUNCATE_ONLY option has been deprecated and is no longer available in current versions of SQL Server. Second, if you are in FULL recovery model, this will destroy your log chain and require a new full backup.

Detach the database, delete the log file, and re-attach - Your database may not come back up, it may come up as suspect, you may have to revert to a backup.

Good luck!!!