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.

  • SQL Server's SoundEx function on non-Latin character sets?
  • Combine multiple rows in a single row by descending order SQL
  • Entity Framework returning bad data
  • TCP/IP Connection
  • How do I pass a string parameter greater than varchar(8000) in SQL Server 2000?
  • Anyway to decrypt an encrypted sql server stored procedure?
  • If any one has a better solution than splitting up the transaction log, then that would be great.

  • SQLSRV Query Returning Output Parameter As Strange Text
  • Help me put Oracle terminology into SQL Server terminology
  • SQL Server to PL/SQL
  • How to automatically delete records in sql server after a certain amount of time
  • SSIS package SQL job not using new environment variable configuration
  • Does the number of columns transferred over the network matter?
  • 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:

    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.

    in Practice

    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>
    Alter database <databasename> set Recovery full  /* if database is set to FULL */

    hope this helps

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.