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...
ntwksouthAsked:
Who is Participating?
 
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
 
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.