April 25, 2013 at 9:23 pm
Dear Experts
I have a 3 GB mdf file and 355 GB ldf file.
What should I do, I have taken a backup to the transaction log, should i delete the log file
Thanks
April 25, 2013 at 9:43 pm
zi (4/25/2013)
Dear Expertsshould i delete the log file
Thanks
Please don't try this ever.
Just try this.
select log_reuse_wait_desc,* from sys.databases where name='dbname'
-- see what it is waiting on
also run this dbcc sqlperf(logspace)
-- see log space utilization
M&M
April 26, 2013 at 12:47 am
can i shrink the log file
I want to solve the space problem
April 26, 2013 at 3:55 am
any idea how to solve this problem
is it correct to make backup log then shrink the log file
Thanks
April 26, 2013 at 4:05 am
What is the recovery model of your database? Is suspect it to be FULL.
If your recovery model is FULL, follow the below steps
1. Take a full database backup
2. Shrink the log file to an appropriate size
3. Take a full database backup again
4. Schedule transaction log backups to avoid such issues in the future
You can also check the below mentioned article for more information on Managing Transaction Logs
http://www.sqlservercentral.com/articles/Administration/64582/
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 26, 2013 at 4:16 am
After you either backup your log or switch your database to simple recovery, you can shrink the log file. But you need to do one or the other. I have a blog post [/url]up the explains the problem and the best solutions.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 26, 2013 at 7:10 am
is it correct to convert the recovery model to simple then shrink the log file
Thanks
April 26, 2013 at 7:26 am
zi (4/26/2013)
is it correct to convert the recovery model to simple then shrink the log fileThanks
Read the blog post that Grant provided you a link to above.
Your t-log is growing because you are in full recovery model with, most likely, no regularly scheduled transaction log backups to maintain th size of the transaction log.
April 26, 2013 at 7:32 am
zi (4/26/2013)
is it correct to convert the recovery model to simple then shrink the log fileThanks
That question is best answered by the people who use your database. How much data can they afford to lose? How much down time can they have?
As a quick fix converting to simple and then shrinking the log file (you may have to run a checkpoint or two before the log rolls over to the free space to allow shrinking) will work, but leaving the database in the simple model means no point in time recovery.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 26, 2013 at 7:53 am
(you may have to run a checkpoint or two before the log rolls over to the free space to allow shrinking) will work,
how to run a checkpoint?
Thanks
April 26, 2013 at 8:09 am
zi (4/26/2013)
(you may have to run a checkpoint or two before the log rolls over to the free space to allow shrinking) will work,how to run a checkpoint?
Thanks
It's a t-sql command:
CHECKPOINT
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 27, 2013 at 12:40 am
zi (4/26/2013)
(you may have to run a checkpoint or two before the log rolls over to the free space to allow shrinking) will work,how to run a checkpoint?
Thanks
use [yourdb]
GO
checkpoint
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 27, 2013 at 6:50 am
zi (4/25/2013)
Dear ExpertsI have a 3 GB mdf file and 355 GB ldf file.
What should I do, I have taken a backup to the transaction log, should i delete the log file
Thanks
A sledgehammer approach is:
1)backup database
2)detach database
3)copy mdf just in case
4)rename ldf
5)single file attach the mdf.
End result:
You have 5-10 minutes downtime.
A log file that is small. Read the articles about what size it needs to be etc
No waiting until next xmas for the logfile to shrink
Suggest you practice this on a dev server as the permissions of the file can cause a slight hiccup
Yes you can use the gui or a tsql script
Just as an aside: how did it get into this condition? Many years ago I had a similar issue when some consultants came in took responsibility for a system and then no one bothered to tell us that the consultants had finished. 3 weeks later we had a 300GB log due to autogrow and no log backups. The term consultants at times is open to debate 🙂
April 27, 2013 at 7:27 am
Surely you mean 😉
Steve JP (4/27/2013)
1)switch to simple recovery2)backup database
3)checkpoint database
4)shrink log
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 27, 2013 at 9:19 am
Depends as the logfile doesn't always want to shrink. If it doesn't then like I said a single file attach is a sledgehammer approach, never said it was a preferred method 😛
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply