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;
ALTER DATABASE [DBNAME] SET RECOVERY FULL;
but i recommend before shrinking the file to see the results of the
This will show you the Size of each log file of each database and will show you what space used in the transaction log
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.
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
i recommend reading the following
and also read about
(Shrinking the Transaction Log)
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..