[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to shrink the log file

Posted on 2004-10-22
6
Medium Priority
?
820 Views
Last Modified: 2008-02-01
Greetings,

Can anyone advise me how to shrink the log file of a SQL Server database?
I have tried to follow the Microsoft article 272318 "Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE" but to no avail.
When I run the code:
 BACKUP LOG Projects WITH TRUNCATE_ONLY
the system tells me that the command has been run successfully but the log file size does not change.  Then if I run the code
 DBCC SHRINKFILE(Projects_log,2)
I get the error message:
"Server: Msg 8985, Level 16, State 1, Line 2
Could not locate file 'Projects_log' in sysfiles."

Can anyone please help?
0
Comment
Question by:IAmTheEggman
6 Comments
 
LVL 14

Accepted Solution

by:
Thandava Vallepalli earned 1000 total points
ID: 12378641
Hi,

USE xxxxx                              
BACKUP LOG xxxxxx WITH TRUNCATE_ONLY
DBCC SHRINKFILE(fileName, 20)  

Note: xxxx - Database Name
fileName: *.ldb file name of your database... not "Project_log".  
FileName, you can find from properties of your database, select Transaction Log Tab in that you can find ....

itsvtk
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 12378644
sorry its not *.ldb file,   its *.ldf file  

itsvtk
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 12378708
This is not for u, but others who have same problem in future...

http://support.microsoft.com/default.aspx?kbid=272318
http://support.microsoft.com/kb/256650/EN-US/

itsvtk
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:sigmacon
ID: 12379046
itsvtk seems to describe what you already did. The commands both of you used will truncate your log file ONLY if your database recovery mode is set to SIMPLE. Otherwise, any backup will only reduce the log size to the last checkpoint (MinLSN). If you want more details about this, see Books Online (BOL). Find the BACKUP LOG index entry and then select 'truncating the transaction log' topic. You can check this option in Enterprise Manager, DB > Properties > Options > Recovery Model.

see also http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20651271.html
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 12379187
Hi IAmTheEggman,

I believe you need to run the DBCC SHRINKFILE from within the database you are shrinking, this may be the problem with not finding the file (this is as itsvtk mentioned).  The below should confirm the name you need to use with the dbcc command:

exec sp_helpdb Projects
0
 

Author Comment

by:IAmTheEggman
ID: 12381231
Many thanks itsvtk, your solution worked just fine.
I appreciate the swift response.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question