August 24, 2009 at 6:33 pm
Hi friends,
One of our dev server's D drive is full.. I need to shrink the log file (maindb.ldf is 20GB) .. Just checking if this is the correct step:
USE maindb;
GO
DBCC SHRINKFILE(maindb.ldf, 1024)
I've backed up the database.
THanks
August 24, 2009 at 7:20 pm
Assuming you have already back up the transaction log file to turn as many active virtual log files inactive - statement is correct.
Just out of curiosity, what's the backup/recovery model for the affected database?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 24, 2009 at 9:36 pm
I am going to guess that the recovery model of the database is full - and that you don't have regular scheduled transaction log backups.
If that is the case, you will not be able to shrink the log until you backup the data. If you don't want to back it up and it is not important for you to have the database in full recovery model (it's a dev box, so I would assume you don't need it in full recovery model), you can just switch it to simple recovery, run a few checkpoints to roll over to the beginning of the file and then shrink.
Example:
ALTER DATABASE devDB SET RECOVERY SIMPLE;
CHECKPOINT;
CHECKPOINT;
DBCC SHRINKFILE(devDB, size);
Review the size of the database - if not shrunk down to expected size, issue another checkpoint and try again.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 25, 2009 at 3:11 am
Please read through this - Managing Transaction Logs[/url]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply