Shrinking Log file - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Shrinking Log file

Author

Topic

lousueyesh
Starting Member

9 Posts

Posted - 2005-04-08 : 08:58:34

Hi,

I tried to shrink the database log file (34.2 GB) & the data file (5.3GB) with the Enterprise Manager shrink database feature.

The data file was shrunk successfully but the log file after shrinking remains as 34.2 GB.

Is there anyway to force it to shrink to a few MB? I wasn't able to do so???

Thanks for suggestions & help,

Louis

lousueyesh
Starting Member

9 Posts

Posted - 2005-04-08 : 09:00:23

Hi louis again,

Are there any 3rd party utility anyone knows that can force the shrinking of the log files which MS enterprise Manager might not be able to accomplish???

Thanks

bakerjon
Posting Yak Master

145 Posts

Posted - 2005-04-08 : 09:11:24

dbcc shrinkfile ('log_file_name', 100) where log_file_name is the logical name of the log file and 100 would be the number of MB to shrink it to.

to find the logical name of the log file

use DBNAME
go
sp_helpfile

or you can use enterprise manager, Right click on the DB | Properties | Transaction Log

I would not use Shrink Database to shrink the log file. Shrink Database takes space from the entire database, not just the file you want to shrink (i.e. the log file).

HTH

Jon

Now I know, and knowing is half the battle!
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=48013

jason
Posting Yak Master

164 Posts

Posted - 2005-04-08 : 16:28:34

You need to backup the log file with BACKUP LOG statement before attempting to shrink it. This will truncate the log and remove old entries.

If you are not backing up the log on a regular basis, you might consider whether or not you actually need the database in full recovery mode. You can set it to simple and not worry about the transaction log at all.

zaire
Starting Member

3 Posts

Posted - 2005-04-13 : 08:10:03

Hi,
I dont know if your problem was resolved but I had the same problem and I resolved it by doing the follow steps.
You must have a PC with SQL.

Server side
1)Stop the data base of the server or INSTANCE.
2)Zip the log and the data
3)Copy the ziped file on your other PC.

PC side
1)create a new data base with the same name.
2)Stop the instance or sql
3)Unzip your file and over write the data and the log you have just created.
4)Star your instance or sql
By doing that have alredy shrink a part of your log file.
5)Now shrink your log and it will work.
I did that a lot of times and it works.

Let me know if it works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources