Switching to Simple Recovery - shrinking transaction logs

First of all I'd like to again point out that, in most cases, SIMPLE recovery is not recommended for a production database. However, if that is something you wish to change the best way I've found to shrink the LOG file, after you put the database into SIMPLE recovery model, is as follows. This will not only accomplish releasing free space from the log, but also shrinking the file down to its smallest possible size.

As you stated, shrinking also is not normally a good practice, but there are times when it is warranted. There are many articles pro/con about it, and I agree with those that will tell you to never use DBCC SHRINKDATABASE; always use DBCC SHRINKFILE if you absolutely have to do a shrink.

However, the log file does indeed have to have a reasonable size to it, even in the SIMPLE recovery model, as that is where information is kept during transactions and long running SPIDs need more of a log file. Among many reasons, one of those is for roll-back in the event of a problem. As I stated above, these scripts will reduce your log file to its absolute smallest size possible, but because you need at least some log space you should follow up by regrowing it manually and also make sure your "auto grow" is set to a reasonable number; arguably, a percentage is to always be avoided. Shrinking the log to its smallest size and the regrowing it manually accomplishes a very important process - that of keeping your VLF fragmentation/counts down. That's another topic I wont go into here, and many sites including Brent Ozar can go into the actual statistics and testing showing why, but always grow your log file by 8 GB per growth if it needs to be that large or larger. You mentioned above 25 MB and I can't imagine that being large enough. I personally manage over a hundred databases at the moment, and less than a dozen are set to 64 MB - All others are much larger ranging up to 64 GB. Your actual SQL activity and queries will determine what your optimal size should be. At minimum I'd go 512 MB to 2 GB and set your growth to the same until you know what your log file should sit at.

I find running each line separately works out the best, ensuring success before going to the next step. The final line of course is what regrows your log to the size you set. I have it at 8 GB, but you can change that to fit your needs. If you do not at this time want to fully shrink and then regrow remove the last line and change the "1" in the line above to your size choice.

One last thing before you go out and shrink your log file.

IMPORTANT: Any time a log file grows, manually or automatically by SQL, essentially your entire database is locked until the grow is done. Pick a good time when doing that won't impact active transactions - if you have "after hours" or maintenance windows. Such a small size of 1 GB or less should be fairly fast and invisible to the end user, but that will truly be determined by your infrastructure.

USE [DatabaseName] 

DBCC SHRINKFILE (N'LogicalName', 0, TRUNCATEONLY)

DBCC SHRINKFILE (N'LogicalName', 1)

ALTER DATABASE [DatabaseName] MODIFY FILE ( NAME = N'LogicalName', SIZE = 8192 MB )

Standard disclaimer: I am an "involuntary DBA" (a nice phrase I saw in this article) and have done lots and lots and lots and lots and lots of reading on this subject, but am still confused/concerned...

So you know that you are playing with a double edge sword and are aware of the risks - any edge you touch, you are going to cut yourself.

I've now discovered that due to the way the software works the transaction logs are as big as the data files (in one case over 14Gb against a 13Gb data file).

Check with the vendor, if they are appending the transaction log backup files or not and use backup compression.

I know that having Simple Recovery will not provide point-in-time restoration - that is absolutely fine, as full backups are taking place hourly and we are "happy to lose" anything that might have happened in the previous 1-59 minutes.

I see a problem in this. If I understand correctly, you are fighting disk storage space. A full backup will occupy more space every time as compared to a transaction log backup (or a differential log backup).

You should consult your backup tool vendor and have them fix the issue. Alternatively, why dont you use a T-SQL solution - which has more transparency and control on what you are doing as opposed to using a tool that you know is giving you problems.

You should read one more awesome answer - Why Does the Transaction Log Keep Growing or Run Out of Space?

I really need to shrink the transaction log to a sensible size

You should find out how often autogrowth kick in and start with an average value and then adjust. Proper sizing of transaction log is very important and please dont go with 25 MB. Its sounds too low and it will be too low value which will start kicking autogrowth events more frequently.

If your transaction log if big, then (hover your mouse below)

you should shrink it in chunks.

Don't let your vendor's buggy tool affect your sane decision :-)