Sql-server – Does shrinking a SQL Server transaction log remove transactions from the log

The problem probably occurred because in order to delete the transaction log you would have had to stop the SQL Server. This causes the buffer pool (cached data and execution plans) to be erased - nothing can be in memory for a process that isn't running anymore. Now that you've restarted your server, all of your execution plans need to be recompiled.

I would also guess that you're running the server with the default transaction log settings wich specify a default size of 1MB for the transaction log and an automatic growth of 10%. These are very bad settings for performance. The transaction log has to 0 out the file when it grows. You can read more about transaction log management in 8 Steps to Better Transaction Log Management and Database Maintenance Best Practices Part III - Transaction Log Maintenance.

The first question is what did you do to the transaction log? You should never delete a transaction log. This is how your database is able to recover from a crash.

The second question is do you have a log backup solution in place? That is the only reason your transaction logs are growing so large. I suggest looking at Ola Hallengren's excellent backup scripts.

Typically, you want to grow your transaction log to a fixed, sane, size to start with and leave it there. This will depend on your database, but in the past I've monitored log file growth over the course of the week with full database backups happening every morning and log file backups running every 15 minutes. Once I've figured out how large the transaction log can get, I size the transaction log to about 15% larger than that (but as a multiple of 8GB).

To summarize the answers here and to inject my own advice:

1: Blindly shrinking a data file is not the best route. Your data is your data and it is characterized by this size. Unless you are planning on deleting large chunks of data, shrinking is not going to do much of anything. As @billinkc pointed out, simply shrinking the file will not give you any sort of appreciable gain.

2: Running this is going to make your log file grow. Apparently you have your data and log on the same drive. I'd recommend against this for many reasons including space and IO contention.

3: You can certainly create a new data file and put that on a different volume. You could "archive" old data to free space in the current .mdf, or you could leave the current data file as-is and it would become an archive, of sorts.

4: I'm guessing that there are other things on this volume as well. I would move those things as quickly as I could in order to free up space.

If this is enterprise data, I'd push for more disk. This would allow you to separate your data and log files. Additionally, outside of just deleting data, you're going to need more disk to implement Brandon's suggestion anyway.