How to split SQL Server Transaction Log
My database server has just ran out of disk space. The Transaction log is taking over 100GB space and I have only 30GB free space. Because of this some transactions are taking infinite amount of time to process resulting in 9002 error.
I would like to ask is there a way I can split this transaction log and delete one of them to get some free space. There is no way now that I can take a backup or increase the disk space by adding another disk.
If any one has a better solution than splitting up the transaction log, then that would be great.
2 Solutions collect form web for “How to split SQL Server Transaction Log”
There are lots of good resources about dealing with large logs linked to from here: http://sqlblog.com/blogs/aaron_bertrand/archive/2009/07/27/oh-the-horror-please-stop-telling-people-they-should-shrink-their-log-files.aspx
Basically, you either need to get more space, backup the logs, or switch to simple recovery mode on your databases. Each approach has its ups and downs.
Data, Logs & OS should be on different disks…
say C for OS, E for Data, and L for Logs.
and if your transaction logs doesnt shrink after a transaction backup then you might want to check you setting. FULL or Simple (doesnt shrink logs).
to force shrink a Log wherein database is set to simple is :
Alter database <databasename>set Recovery simple /* if database is set to FULL */ USE <databasename> DBCC SHRINKFILE (<log_name>, 0, TRUNCATEONLY) GO Alter database <databasename> set Recovery full /* if database is set to FULL */
hope this helps