Solved

DBCC Shrinkfile

Posted on 2010-09-22
3
777 Views
Last Modified: 2013-11-05
Obviously I am not a SQL person...

I have an application that is locking up because the log file (carpedata_log) is full.  I opened SQL and ran the following query:

  dbcc shrinkfile (carpedata_log,10)
 
backup log carpedata_log with truncate_only

The response I get is:

Cannot shrink log file 2 (carpedata_log) 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 3
Could not locate entry in sysdatabases for database 'carpedata_log'. No entry found with that name. Make sure that the name is entered correctly.
Msg 3013, Level 16, State 1, Line 3
BACKUP LOG is terminating abnormally.

1 row:
Dbld - 7
Field - 2
CurrentSize - 27392760
MinimumSize - 25600
UsedPages - 27392760
EstimatedPages - 25600
0
Comment
Question by:tmartin40
3 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 33738374
backup log with truncate_only
0
 
LVL 15

Accepted Solution

by:
mcmonap earned 500 total points
ID: 33738433
Hi,

Your actions here will make your database more difficult to recover if you have problems so you must proceed with caution!  I assume your application is not working so your main goal is to get it working, here is how I would proceed.
1. try a sql backup (this will probably fail if you have no disk space left)
2. truncate the transaction log (you were trying to do this)
3. shrink the log file (you were trying to do this too
4. setup a regular planned backup schedule for the database(s)
 
So first off, where you are probably going wrong above:
1. When you use backup you should specify the database name (probably carpedata?) not the file name
2. the shrink command will only shrink the file to the last used portion of the file so it is best to run this after the backup not before.

what to do now:
1. look at the code below; try running the commands in order, one at a time.
2. Post back and we can talk about backups
--try backing up the database (to a disk location with space
BACKUP DATABASE CARPEDATA TO DISK='c:\carpe.bak'

--truncate the log (YOU LOSE POINT IN TIME RECOVERY OF YOUR DATABASE BY DOING THIS)
BACKUP LOG CARPEDATA WITH TRUNCATE_ONLY

--shrink the log file
dbcc shrinkfile (carpedata_log,10)

Open in new window

0
 

Author Closing Comment

by:tmartin40
ID: 33738943
Like you thought, the backup failed (not enough disk space) but I ran line 5 and 8 one at a time and it cleaned it right up.  I guess I need to employ a maintenance plan...shrinkfile restored over 200 GB of diskspace!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

OpenVPN is a great open source VPN server that is capable of providing quick and easy VPN access to your network on the cheap.  By default the software is configured to allow open access to your network.  But what if you want to restrict users to on…
Let’s list some of the technologies that enable smooth teleworking. 
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

770 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