Click here to Skip to main content
Click here to Skip to main content

Large Log File SQL 2005

By , 25 May 2010
Rate this:
Please Sign up or sign in to vote.
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 | :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: Blush | :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, Roll eyes | :rolleyes:

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Ahmed Kahwaji
Software Developer (Senior)
Jordan Jordan
Your comment please Smile | :)

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web02 | 2.8.140415.2 | Last Updated 25 May 2010
Article Copyright 2010 by Ahmed Kahwaji
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid