• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 531
  • Last Modified:

How do I shrink an SQL logfile when Enterprise Mgr Backup/Shrink Tools Fail ?

Customer has database with 50MB .mdf data file and coresponding 22GB .ldf logfile. Have tried backup/truncating/shrinking within Enterprise Manager w/ no success.

I'm their server/infrastructure guy, not a DBA, so please type slowly :)

Thanks in advance...
0
ntwksouth
Asked:
ntwksouth
  • 7
  • 4
1 Solution
 
MrRobotCommented:
Hi,

A portion of the log file may be still active, preventing it to be shrinked. Please see;

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21690592.html?qid=21690592
0
 
MrRobotCommented:
You can go to the properties of the database, take it into simple recovery mode, apply and take it back to full recovery mode as a workaround. And don't forget to take a full backup.

0
 
MrRobotCommented:
btw, the tsql way to shrink a log file is described in :

http://support.microsoft.com/?id=272318

but when it doesn't work with the GUI, it probably won't with tsql, as I mentioned in my previous post.

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
MrRobotCommented:
if you don't need the functionality to backup to a point in time (backup to a restore point is enough for you) you don't need full recovery mode (which keeps all transactions in the log and in the backups), so you can keep it in the simple recovery mode. otherwise, you need to periodically backup your transaction logs to keep the log file in a normal size.

0
 
ntwksouthAuthor Commented:
Thanks for helping, MrRobot.

I have ran the DBCC loginfo command and found 562 record w/ status of 2, not sure what I should do to clear those - any thoughts there?

If I understand your second comment, taking the DB into simple recovery mode, backing up, then placing back in full recovery mode will make all good again - sounds too good to be true or I'm misunderstanding completely (probably the latter); are there any drawbacks to that method?

Also, when I perform a backup the .bak file is only 50MB - if that were restored would it recreate the 20GB logfile? Just curious on that one - I only have 16GB free on DB server
0
 
ntwksouthAuthor Commented:
On comment #3, if trying to shrink via the tsql method, what would you think the target size should be for a 50MB DB/.mdf file?
0
 
ntwksouthAuthor Commented:
Another detail, when I run dbcc opentran it shows no open transactions - does that shed any add'l light?
0
 
MrRobotCommented:
hi,

right, 2 states the active portions of the logfile, which can not be truncated.

putting the db into simple mode, back to full recovery mode will close the active portions and enable you to truncate the log file. you better take a backup 'after' putting it back to full recovery mode, yes, it's good enough =))

well, a data backup keeps your data, in the other hand the log files are just needed to keep a log of all the operations and restore to a point in time, like instead of restoring to a backup point, they give you the option to restore to a given time, like '11:45 of a given date'

restoring a data file wont restore the log file, that 20 GB file. the logs are backed up to and restored from log backup files.




0
 
ntwksouthAuthor Commented:
MUCH APPRECIATED, MrRobot!!!

I used a combination of the MS articles and tsql script w/ truncate_only then dbcc shrinkfile to get down to an acceptable level - couldn't have done it w/o your help.

Will still put DB in simple recovery mode for future operation based on your recommendations.

Have a good one...
0
 
MrRobotCommented:
to make it clear, the 20GB log file doesn't carry your data, and not needed in most situations. it only holds the operations between backups, so it makes the server able to restore to a full backup, say '10:00 AM' of a date, and redo all operations in the log file to '2:00 PM' of the same date, to restore to a point in time.

and as I said, if you don't need that functionality, you can simply keep the DB in simple recovery mode, and restore to a backup point when needed.

good luck
0
 
MrRobotCommented:
glad it helped, you're welcome =)
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now