Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Reduce the size of Transaction Log FIle Expand / Collapse
Author
Message
Posted Wednesday, January 4, 2006 9:55 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 23, 2013 9:06 AM
Points: 86, Visits: 123

Hi All,

 

SQL Server Version : SQL Server 2000 8.0.194

 

My one production database Transaction Log size is more than 10GB. But data file size is only 1 GB.

 

I try to reduce the side of the logfile using shrink data file, again the size is not reducing.

How can I reduce the size of a log file .

 

Please advice

 

Mathew

 

Post #248298
Posted Wednesday, January 4, 2006 11:42 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
Are you using simple or full recovery?  If using full, backup the transaction log, or switch to simple recovery mode which will truncate the log at various intervals.


Post #248316
Posted Thursday, January 5, 2006 2:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 9:02 AM
Points: 125, Visits: 338

Hi Matthew,

Here is the command you need to run to carry out Ian's suggestion.

backup log <DB Name Here> with truncate_only

After this, you need to consider the backup solution you require for this db, then set the database recovery mode as Ian suggests.

Best regards,

Martin




Post #248353
Posted Thursday, January 5, 2006 1:36 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 23, 2013 9:06 AM
Points: 86, Visits: 123

I did the following

1. Change the recovery model to simple

2. issue the commnad backup log <DB Name Here> with truncate_only. But this time file size not changed. FIle size is still 10 gb . But uses only 25 MB

3. When i try to reduce the log size using enterprise manager, sql showing message "error 21335, New DB File size must be larger than current size'

Pls explain me how can i reduce the physicial Transaction  file size

Mathew

 

 

 

Post #248631
Posted Thursday, January 5, 2006 4:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 9:02 AM
Points: 125, Visits: 338

Matthew,

What you have effectively by running the backup log command is emptied the water out of a bucket.  The contents of the bucket are gone, but the bucket still remains, if you catch my drift.

To shrink the file, you must now run the following...

dbcc shrinkfile (<virtual file name here> )

and it will then shrink back to it's original size.  To find the virtual file name, run sp_helpfile from within the context of the target database.

Hope that helps,

Martin




Post #248680
Posted Friday, January 6, 2006 8:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 15, 2011 7:20 AM
Points: 21, Visits: 24

I've found this script to work best for us.

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=26

 

Good Luck!

Post #248909
Posted Friday, January 6, 2006 9:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, February 8, 2010 8:02 AM
Points: 151, Visits: 15

As said in BOL:

USE UserDBGODBCC SHRINKFILE (DataFil1, 7)GO

"Datafill" is the logical file name and u need to difine a size the file needs to be shrinked to. Try with 9000 (GB) to start with. Once the file has been reduced to 9 GB go for another GB less. Seems to be in SQL 2000 if u have less phisical disk space - basically less then the log file DBCC SHRINKFILE takes forever to shrink the log. By reducing it by chunks its more faster and efficient. I had the same issue 2 days ago and this solution took care of it. post a note if this works.

Good Luck.




Post #248930
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse