Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now
Course Of The Month
5 days, 16 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Cannot shrink SQL 2005 Logfile, continues to grow
Posted on 2012-12-26
HELP! I have a SQL 2005 database with a logfile that is 27gb and growing.
I have tried using the GUI to shrink, but it just says that it 'completes successfully' and never does anything to the file.
The database recovery mode is already set to simple. I have tried executing the DBCC ShrinkFile query and while it says that it is completing successfully, it's not. The file not only remains the same size, it continues to grow for some reason.
I'm afraid of detaching the database and deleting this file because I'm worried about uncommitted transactions.
What can I do? I need help!
I have tried using the GUI to shrink, but it just says that it 'completes successfully' and never does anything to the file.
The database recovery mode is already set to simple. I have tried executing the DBCC ShrinkFile query and while it says that it is completing successfully, it's not. The file not only remains the same size, it continues to grow for some reason.
I'm afraid of detaching the database and deleting this file because I'm worried about uncommitted transactions.
What can I do? I need help!
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
3
2- +3
15 Comments
By the way, the message I'm getting is:
"Cannot shrink log file 2 (xxxxxxxxxx_Log) because all logical log files are in use."
How do I get around this?
"Cannot shrink log file 2 (xxxxxxxxxx_Log) because all logical log files are in use."
How do I get around this?
"Have you verified the database is or ever was involved in Replication?"
It's the only running SQL server we have, so I don't believe its involved in any replication.
It's the only running SQL server we have, so I don't believe its involved in any replication.
Ok, well how about just backing the logfile up somewhere else, detaching the DB and then deleting the logfile and reattach it?
I've had to stop SQL server for the interim because the file just continues growing and I'm out of disk space. I have no idea whats happening.
I've had to stop SQL server for the interim because the file just continues growing and I'm out of disk space. I have no idea whats happening.
Active 5 days ago
Ok, well how about just backing the logfile up somewhere else, detaching the DB and then deleting the logfile and reattach it?
If in fact you are using the Simple Recovery Model, then you will not be able to backup the Transaction Log. Secondly the Transaction Log is an integral part of the database so deleting the log file is reckless to say the least. Whatever you do, please please do a full backup of your database before you make matters worse.
If in fact you are using the Simple Recovery Model, then you will not be able to backup the Transaction Log. Secondly the Transaction Log is an integral part of the database so deleting the log file is reckless to say the least. Whatever you do, please please do a full backup of your database before you make matters worse.
The database is backed up daily using MozyPro, so I'm not worried about that.
I agree that its potentially reckless, but what other alternative do I have? I can't shrink it, can't delete it, wtf am I supposed to do?
I agree that its potentially reckless, but what other alternative do I have? I can't shrink it, can't delete it, wtf am I supposed to do?
Active today
If nothing helps, I suggest to rename the DB, back it up and recreate the original DB from backup. Make sure you also rename the "old" DB files, so there will be no filename conflicts with the restored one.
Are you certain the DB shows to have free log space in that 27 GB?
Are you certain the DB shows to have free log space in that 27 GB?
Active 2 days ago
Woah! Don't do anything outside the normal SQL realm yet!
First let's see what, if anything, SQL says is preventing it from truncating log records:
SELECT
log_reuse_wait_desc,
state_desc,
recovery_model_desc,
name --for verification that we're looking at the right db
FROM sys.databases
WHERE
name = N'<your_db_name_here>'
First let's see what, if anything, SQL says is preventing it from truncating log records:
SELECT
log_reuse_wait_desc,
state_desc,
recovery_model_desc,
name --for verification that we're looking at the right db
FROM sys.databases
WHERE
name = N'<your_db_name_here>'
Active 2 days ago
If you're getting into emergency territory, and you have additional disk space on another drive, add a log file to the db to keep your current drive from filling and causing all kinds of problems.
ALTER DATABASE <your_db_name>
ADD LOG FILE ( NAME = your_db_name_log2, FILENAME = 'd:\full\path\on\new\drive
ALTER DATABASE <your_db_name>
ADD LOG FILE ( NAME = your_db_name_log2, FILENAME = 'd:\full\path\on\new\drive
For shrink log file please follow guidelines mentioned in following articles:-
1. http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/
2. http://www.techrepublic.com/blog/datacenter/help-my-sql-server-log-file-is-too-big/448
3. http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/ae4db890-c15e-44de-a2af-e85c04260331
Hope that it would short out the issue.
Thanks
Kernel Recovery Tools
1. http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/
2. http://www.techrepublic.com/blog/datacenter/help-my-sql-server-log-file-is-too-big/448
3. http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/ae4db890-c15e-44de-a2af-e85c04260331
Hope that it would short out the issue.
Thanks
Kernel Recovery Tools
Perform the bellow operation:
• Take a full backup of your database
• Take transaction-log backup of your database frequently like in every Half an hour so that log file will not continuously grow
• Shrink if you do not have any free space. You can perform this operation manually if required.
NOTE : Most of DBAs suggest to avoid shrinking database because when you shrink the database to gain space keeping in mind it will help to increase in performance, but in fact it leads to increase in fragmentation which reduces the performance.
• Take a full backup of your database
• Take transaction-log backup of your database frequently like in every Half an hour so that log file will not continuously grow
• Shrink if you do not have any free space. You can perform this operation manually if required.
NOTE : Most of DBAs suggest to avoid shrinking database because when you shrink the database to gain space keeping in mind it will help to increase in performance, but in fact it leads to increase in fragmentation which reduces the performance.
Active today
RRobinho,
The Asker already told us (in the original question) that the DB is in Simple recovery mode - no Tranaction Log backup hence.
The Asker already told us (in the original question) that the DB is in Simple recovery mode - no Tranaction Log backup hence.
Thanks everyone. It turned out that replication was, in fact, turned on. Something weird must have happened with the replication trying to catch up or generating bogus logs, because its been that way for a long time and this issue never occurred before.
Anyway, a normal shrink was possible after just stopping replication. I'll just split points evenly for everyone that offered a possible solution. Thanks.
Anyway, a normal shrink was possible after just stopping replication. I'll just split points evenly for everyone that offered a possible solution. Thanks.
Featured Post
What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables.
Make a table:
Update a specific column given a specific row using the UPDATE statement:
Remove a set of values using the DELETE s…
Suggested Courses
Course of the Month5 days, 16 hours left to enroll
Earn Certification
MCSA Configuring Windows 7 Exam 70-680
$49.00$44.10
705 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.