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
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
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
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,
I've found this script to work best for us.
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=26
Good Luck!
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.
