Large Log File SQL 2005

Problem

Large SQL 2005 log file is a problem, especially if you have tried the following:
1. Shrinking of the big log file did not work?
2. The “available free space” property (in the shrinking page) is in minus (e.g.” -100 MB (-%40)”)?
3. Changing from “Full” recovery to “Simple” recovery and backup did not resolve the problem?
4. Most (if not all) the suggested solutions over the internet to shrink the file did not work?

:sigh:

Well I faced this problem in the last six months. With Microsoft support help, I have been able to resolve it. How?

Solution

The solution is: :-O
1. Create a new empty database
2. Write and execute the following command for each table in the current database

Select *
Into DestinationDatabase.dbo.Table1
From SourceDatabase.dbo.Table1

Please note the following:
a. The table will be created and filled automatically in the new database
b. Columns default values will not be transferred. In this case you need to open each table and set the columns default values one by one
c. Copy all stored procedures, functions, and/or assemblies to the new database
d. Copy Triggers
e. Rebuild the indexes
3. Assign the required user access list to the new database
4. Delete the old database (or rename it)
5. Rename the new database with the old database name
6. Enjoy
Good luck, :rolleyes: