asked
how to shrink log file
for one of the Databases the data file 20GB and log file is 35 GB. I took a full backup and tried to shrink the database but i don't see any changes. how to shrink log file?
Is the database set to FULL recovery? If so, have you backed up the transaction log part of the database recently? If not, then you won't be able to shrink the log file. See [`BACKUP LOG`][1]. Once you've taken a TLog dump, you may have to wait a bit before shrinking the log, as that last VLF might still be in use. Have a look at `DBCC LOGINFO` to see what's in use. [1]:
http://msdn.microsoft.com/en-us/library/ms186865.aspx
When i took a full backup it came upto 11gb. and i just have 11Gb more free space,when i tries to take log backup it is exceeding 11GB so i canceled the log backup. For this situation can i change it to simple recovery and take full backup and change it again to full recovery,and shrink the log file if i do as above will there be any transactions/data lost
You shouldn't lose any data doing that. However, it's not really recommended... Given your circumstances, it's probably the easiest way to go, unless you can get your storage guys to give you some more.
Right-click on the database is SSMS and select Tasks->Shrink->Files Change the File type to Log.
Are you using Replication with it, if so might be the case that you haven't replicated data yet which is why the log has not cleared? Also, check to see if you have any transactions which are still running, as they may explain why you can't clear the log, failing that you may want to force a CHECKPOINT by issuing the CHECKPOINT command, but check what the above first...
