Click here to Skip to main content
13,350,483 members (62,302 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


4 bookmarked
Posted 25 May 2010

Large Log File SQL 2005

, 25 May 2010
Rate this:
Please Sign up or sign in to vote.

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?


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


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:


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 | :)

You may also be interested in...


Comments and Discussions

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