Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

DBCC Shrinkfile

Posted on 2010-09-22
3
Medium Priority
?
783 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 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This is an article about my experiences with remote access to my clients (so that I may serve them) and eventually to my home office system via Radmin Remote Control. I have been using remote access for over 10 years and have been improving my metho…
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…

782 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