April 6, 2011 at 12:48 am
Help me in shrinking the LOG file of huge database.
its about 273 gigs of size and and its a Mirrored Database.
sqlperf shows - 91.5934%
Shall I go for below steps:
1. Full backup
2. BACKUP LOG Proc_IRS with truncate_only
go
DBCC SHRINKFILE (Proc_IRS_log2.LDF , 0)
go
Can I do only 2nd step without full backup, as we got full backup 3 hrs before.
Can I do while its in Mirroring. What if I disconnect from Mirroring and Try second step, as we have only 2 gigs of free space on drive now.?
Suggestions please.
Cheers,
- Win.
" Have a great day "
April 6, 2011 at 1:59 am
1) what does this command reflect for your database:-
select name,state_desc form sys.databases
2) How frequently you doing the log backup? If the interval between the log backup is high then consider reducing it(for ex, if its every hour, consider it every 15 min)
3) Run dbcc loginfo(yourdbname) and check the status field, how many active(2) and inactive(0).
4) Run checkpoint. It should flush your data from log to data file.
----------
Ashish
April 6, 2011 at 2:19 am
How often are you doing log backups?
Please read through this: http://qa.sqlservercentral.com/articles/Transaction+Log/72488/
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
April 6, 2011 at 3:17 am
we have a scheduled TransLog backups for every 1 hour.
Cheers,
- Win.
" Have a great day "
April 6, 2011 at 3:18 am
Did you read through the article I referenced?
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
April 6, 2011 at 3:24 am
Hello Ashish,
her you go..
1) what does this command reflect for your database:- state - ONLINE
select name,state_desc form sys.databases
2) How frequently you doing the log backup? If the interval between the log backup is high then consider reducing it(for ex, if its every hour, consider it every 15 min)
Every one hour based on the business we scheduled.
3) Run dbcc loginfo(yourdbname) and check the status field, how many active(2) and inactive(0).
830 connections Active, 29 Inactive
4) Run checkpoint. It should flush your data from log to data file.
How to from SQL Query to perform CHECKPOINT directly ?
Cheers,
- Win.
" Have a great day "
April 6, 2011 at 3:27 am
- Win. (4/6/2011)
4) Run checkpoint. It should flush your data from log to data file.How to from SQL Query to perform CHECKPOINT directly ?
Don't, it's not going to help anything. In full recovery (which you need for mirroring) checkpoint won't make log space reusable.
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
April 6, 2011 at 3:40 am
Shrinking will not help here as your log will grow again. I will suggest two option:-
1) Read Gail article which she referred in earlier reply.
2) Reduce the frequency of your log backup. Seems like there are many activity occuring within an hour and its filling the log. As suggested you can decrease it to 15 min or whatever you think suits best, but definately less than an hour. Its not necessary that you need to run the log backup every 15 min for the complete day but you can judge when the operation is more active(like 9-5) and then you can increase the frequency for log backup for that perticular duration and for the rest you can continue with 1 hour log backup if system is not much active.
----------
Ashish
April 6, 2011 at 3:44 am
Sorry Gail..
Am looking into now.
Let me know if I can have any suggestions.
Cheers,
- Win.
" Have a great day "
April 6, 2011 at 3:48 am
- Win. (4/6/2011)
Sorry Gail..Am looking into now.
Let me know if I can have any suggestions.
The article's full of suggestions (I know, I wrote it)
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
April 6, 2011 at 3:56 am
Ran through Gail's article most first and then I have posted the scenario here.
Its a Mirrored DB, active status with 534 gigs of size and 274 gigs of Log.
This is a very busy server and DB though..
We have scheduled operations like, JOBS, Packages more than 200 which executes frequently every day.
I planned to schedule TransLog back for every 30 mins. I have to mainly consider other JOBs as well which executes at every 15 mins which pulls and push data to other sources.
Need to get confirmation as well on this of schedule change to 30 mins than 1 hr.
Cheers,
- Win.
" Have a great day "
April 6, 2011 at 3:59 am
- Win. (4/6/2011)
Ran through Gail's article most first and then I have posted the scenario here.Its a Mirrored DB, active status with 534 gigs of size and 274 gigs of Log.
This is a very busy server and DB though..
Ok, so start at the beginning of the article. What's the value of log_reuse_wait_desc?
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
April 6, 2011 at 4:07 am
log_reuse_wait_desc - It shows - DATABASE_MIRRORING
DBCC OPENTRAN shows no queries - No active open transactions / for 5 mins at this point of time.
Getting Error from below query- Incorrect syntax near 'INNERJOIN'. tried with other active sessions.
its SQL 2005, SP3
SELECT host_name,program_name, original_login_name, st.text
FROM sys.dm_exec_sessions es
INNERJOIN sys.dm_exec_connections ec
ON es.session_id = ec.session_id
CROSSAPPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st
WHERE ec.session_id = 51
Cheers,
- Win.
" Have a great day "
April 6, 2011 at 4:10 am
BTW
I have disconnected the Mirroring. Since I have only 4 gigs now, moved some old files to other drive.
Cheers,
- Win.
" Have a great day "
April 6, 2011 at 4:13 am
INNERJOIN sys.dm_exec_connections ec
should be INNER JOIN sys.dm_exec_connections ec
CROSSAPPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st
should be CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st
----------
Ashish
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply