July 31, 2008 at 10:22 am
We've got a small server for our test server. I'm trying to move all of the databases from off of this test server onto a larger virtual server (but are Windows 2003 Servers). One of the databases on the test server is small. The .MDF file is only 4 MB in size. However, the log file is HUGE! It is 4 GB in size! I have been trying to do a DBCC SHRINKFILE repeatedly, after doing a backup/truncate of the log file, but all I'm getting is the following message:
"Cannot shrink log file 2 (MyDB_Log) because all logical log files are in use"
Not very helpful. And looking at the rest of the databases, I see I've got similar problems with 2 others, where the data file is really small, but the log file is very large (one .Log file is 8 GB in size, and the data file is only a couple of MB in size).
So, how do I shrink these files down?
Kindest Regards, Rod Connect with me on LinkedIn.
July 31, 2008 at 10:26 am
Full recovery mode?
Any log backups running?
Any replication running?
Do you have a point-in-time recovery requirement for these databases?
If you run the following query, what does it say for the offending databases?
SELECT name, recovery_model_desc, log_reuse_wait_desc from sys.databases
Rod at work (7/31/2008)
"Cannot shrink log file 2 (MyDB_Log) because all logical log files are in use"Not very helpful.
What it's saying is that the log file is full and hence there is no free space that can be released to the file system. Hence, the first thing to do to resolve the problem is figure out why the log is full.
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
July 31, 2008 at 10:37 am
Your database are set to full recovery and require frequent transaction log backups to clear the log file. If you don't need to be in full recovery model, switch the database to simple and try running the shrinkfile.
If you do need the database in full recovery, switch to simple recovery - shrink the log files, switch back to full recovery, perform a backup and then setup frequent transaction log backups. The frequency will be determined by your business requirements on what is acceptable loss of data in a disaster. This could be every 15 minutes - or up to every couple of hours.
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
July 31, 2008 at 10:57 am
Gail,
The recovery mode is Bulk-Logged
No log backups are running, that I can see.
No replication.
We don't have a point-in-time recovery requirements for these databases.
Running the SELECT you specified, for this database, yields
MyDBBULK_LOGGEDNOTHING
Kindest Regards, Rod Connect with me on LinkedIn.
July 31, 2008 at 11:32 am
Both full and bulk-logged recovery models require log backups. If you don't have log backups, the transaction logs will grow without bound (as you've noticed). You can have a look at this for a little more info - Recovery model and transaction logs[/url]
Since you don't need to be able to recover to a point in time, set the databases into simple recovery model. Simple means that the logs will auto-truncate on checkpoint. Run a checkpoint, then you should be able to shrink the logs down to a reasonable size. Leave the DBs in simple recovery and the logs should look after themselves.
Make sure that for all DBs that are in full or bulk-logged recovery and that you need to be able to restore to point-in-time, you have regular log backups running.
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply