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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • Last Modified:

Unable to shrink log file SQL 2005

I am trying to shrink a log file. I run the first select statement to get the file ID and then I run the second statement to shrink the log file. The second statement comes back telling me the entry cannot be found in the sys.data table. From what I can see the names match. What am I missing?

USE DYNAMICS

SELECT file_id, name FROM sys.database_files
=====================================
1      GPSDYNAMICSDat.mdf
2      GPSDYNAMICSLog.ldf

USE DYNAMICS
GO
DBCC SHRINKFILE(GPSDYNAMICSLog, 2)
BACKUP LOG GPSDYNAMICS WITH TRUNCATE_ONLY
DBCC SHRINKFILE(GPSDYNAMICSLog, 2)
GO
0
rwheeler23
Asked:
rwheeler23
  • 5
  • 3
  • 3
  • +1
1 Solution
 
JestersGrindCommented:
The name is GPMDYNAMICSLog.ldf.  Usually the name doesn't include the file extension, but it looks like yours does.  Try this.

DBCC SHRINKFILE([GPSDYNAMICSLog.ldf], 2).

Greg

0
 
rwheeler23Author Commented:
Trying this gets me these error messages:

USE DYNAMICS
GO
DBCC SHRINKFILE([GPSDYNAMICSLog.ldf], 2)
BACKUP LOG [GPSDYNAMICSlog.ldf] WITH TRUNCATE_ONLY
DBCC SHRINKFILE([GPSDYNAMICSLog.LDF], 2)
GO
======================================================
Cannot shrink log file 2 (GPSDYNAMICSLog.ldf) because all logical log files are in use.

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 911, Level 16, State 10, Line 2
Could not locate entry in sysdatabases for database 'GPSDYNAMICSlog.ldf'. No entry found with that name. Make sure that the name is entered correctly.
Msg 3013, Level 16, State 1, Line 2
BACKUP LOG is terminating abnormally.
Cannot shrink log file 2 (GPSDYNAMICSLog.ldf) because all logical log files are in use.

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I tried serveral variations of the backup log command and none of them worked.
0
 
JestersGrindCommented:
The BACKUP LOG command uses the database name not the file name, so it should look like this.

USE DYNAMICS
GO
DBCC SHRINKFILE([GPSDYNAMICSLog.ldf], 2)
BACKUP LOG GPSDYNAMICS WITH TRUNCATE_ONLY
DBCC SHRINKFILE([GPSDYNAMICSLog.LDF], 2)
GO

Greg

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
rwheeler23Author Commented:
This was one the first variation I tried.
==========================================================
USE DYNAMICS
GO
DBCC SHRINKFILE([GPSDYNAMICSLog.ldf], 2)
BACKUP LOG GPSDYNAMICS WITH TRUNCATE_ONLY
DBCC SHRINKFILE([GPSDYNAMICSLog.LDF], 2)
GO
=============================================================
I get this mesage.

Cannot shrink log file 2 (GPSDYNAMICSLog.ldf) because all logical log files are in use.

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 911, Level 16, State 10, Line 2
Could not locate entry in sysdatabases for database 'GPSDYNAMICS'. No entry found with that name. Make sure that the name is entered correctly.
Msg 3013, Level 16, State 1, Line 2
BACKUP LOG is terminating abnormally.
Cannot shrink log file 2 (GPSDYNAMICSLog.ldf) because all logical log files are in use.

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

What is this other message about "All logical log files are in use"?
0
 
JestersGrindCommented:
Try removing the first shrink.  

USE DYNAMICS
GO
BACKUP LOG GPSDYNAMICS WITH TRUNCATE_ONLY
DBCC SHRINKFILE([GPSDYNAMICSLog.LDF], 2)
GO

Greg

0
 
Simone BCommented:
I'm not 100% sure what's causing the errors, but chances are pretty good that a Dynamics database is using a Full recovery model. In that case you will need to set the recovery model to Simple before you shrink the transaction log.

Also, you shouldn't need the .ldf file extension, just the name of the log file. Try this and see if it helps:


USE master
GO
ALTER DATABASE GPSDYNAMICS SET RECOVERY SIMPLE
GO

USE GPSDYNAMICS
GO
DBCC SHRINKFILE (N'GPSDYNAMICSLog' , 0, TRUNCATEONLY)
GO

USE master
GO
ALTER DATABASE GPSDYNAMICSSET RECOVERY FULL
GO
0
 
Scott PletcherSenior DBACommented:
Maybe this:


USE DYNAMICS
GO
--no reason to shrink log before backup
BACKUP LOG DYNAMICS WITH TRUNCATE_ONLY --<<-- db_name appears to be "DYNAMICS" not GSPDYNAMIC
DBCC SHRINKFILE([...], 2)
GO
0
 
rwheeler23Author Commented:
That was the trick.  If I wanted to specify a minimum size of the log file to be 20MB, would I simply change the 2 to a 20?

USE DYNAMICS
GO
--no reason to shrink log before backup
BACKUP LOG DYNAMICS WITH TRUNCATE_ONLY
DBCC SHRINKFILE([GPSDYNAMICSLog.LDF], 2)
GO
0
 
Scott PletcherSenior DBACommented:
Yep.

For an overgrown log, you are much better off shrinking it to a very small size and then expanding it to the full size you need.

You can do that through Mgmt Studio or using an ALTER DATABASE command:

ALTER DATABASE DYNAMICS
MODIFY FILE ( NAME = [GPSDYNAMICSLog.LDF], SIZE = 1024MB ) --or whatever size you want

That way you don't end up with too many VLFs, as happens with slow log growth over time.  Too many VLFs can seriously hurt db performance.
0
 
rwheeler23Author Commented:
These databases are created by a Microsoft application. The minimum size is always 20MB.
Thanks for your help with this.
0
 
Scott PletcherSenior DBACommented:
No problem.

You should explicitly grow the log to the largest reasonable size you expect it to reach yourself, by ALTERing the file as above.  That has several advantages:

1) the disk alloc has a much better chance of being contiguous
2) you don't have delays when running things waiting for the log to auto-grow
3) you get fewer VLFs and thus better overall log performance
0
 
rwheeler23Author Commented:
I support this Microsoft application and very often when I get called in I will see these huge log files and no one has every shrunk them. There could be a 500MB data file with an 8GB log file. The log file usually should be about 20MB unless some mass import was done. And then even so, once the records are committed to the database the log file should be shrunk. Why carry around this eccessive baggage. Thanks again for you help and clarification.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now