Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Log files running out of space after rebuild Expand / Collapse
Author
Message
Posted Sunday, July 13, 2014 11:16 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 12:38 AM
Points: 75, Visits: 166
Hi ....

I have an issue after running the rebuild/reorganize index maintenance job based on demand ... the log files grows very fast ..we just added new harddisk( triple size than original) a few days ago but then today ..it is running out of space again ....
we had tried shrink or backup transaction log but it doesnt help that much...

Pls kindly advice...Much appreciate it !!
Post #1592060
Posted Monday, July 14, 2014 1:00 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 42,771, Visits: 35,870
Don't shrink.

http://www.sqlservercentral.com/articles/Administration/64582/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1592072
Posted Monday, July 14, 2014 10:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:35 AM
Points: 9, Visits: 136
If the database is in FULL recovery mode you must back up the log file. Backing up the log file will not make the file smaller but will release space within the file that will be reused.
Post #1592272
Posted Monday, July 14, 2014 6:26 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 12:38 AM
Points: 75, Visits: 166
Dear Roger and Gail Shaw

Thanks for your response..much appreciate it .. actually we perform the log backup every 3 hours but we do it using third party which is Symantec..
I am thinking to increase the FILLFACTOR becomes 90 ( previously is 80 ) so it probably will slower down the speed of data growth ?

or any other suggestion ?

Thanks heaps
Post #1592385
Posted Monday, July 14, 2014 10:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 6:28 PM
Points: 24, Visits: 252
Rebuilding the index will be fully logged in the transaction log. You could bring down your whole database server if you dont get this right.
Is the reason you are rebuilding related to performance issues or just doing a regular maintenance?
are you using sort_in_tempdb='ON', this may help in your case depending on where you tempdb is located and how much free disk space you have.

I'd also suggest you look at your t-log backup stragey and considering changing to backing up every 15 minutes.


SQL Server Best Practices Supporter
Post #1592408
Posted Tuesday, July 15, 2014 1:29 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 12:38 AM
Points: 75, Visits: 166
Thanks for your response ...
I am doing the maintenance plan due to the performance issues...
hmm at the moment we changes the t-log backup into every 2 hours and let see how it goes ...
Tonight i am going to run the rebuild of other database which has more indexes and much bigger index size...Finger cross :)










Post #1592431
Posted Tuesday, July 15, 2014 1:41 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 12:38 AM
Points: 75, Visits: 166
sorry ... I hv just realized there is something that I haven't answered yet..
I perform the default rebuild indexes which has the feature ( Sort_temp = off ) and when i take a look on my temp db ( data file size is 792.56 MB and Volume usage = 0.25% ) and log file is 505MB ( volume usage = 0.16%)

meanwhile my database size is data file --> 5.36 GB ( volume usage = 1.78%)
log file 1 --> 5MB (volume usage = 0%)
log file 2 --> 15 GB ( volume usage = 9.37%)

I will rebuild around 23 indexes with each size around 0.3 GB

What do you think about it ?

thankss!!

Post #1592434
Posted Tuesday, July 15, 2014 4:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 6:28 PM
Points: 24, Visits: 252
I'd first try to update the stats on the database that has the performance issues to see if that helps. Looks like you have alot of free space on your drives, how often do you intend to do the rebuild?

Is your tempdb on a separate drive?
Out of curiosity, I see that you have two log files on your database, is there a specific reason for this?


SQL Server Best Practices Supporter
Post #1592482
Posted Tuesday, July 15, 2014 11:40 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 12:38 AM
Points: 75, Visits: 166
Shaun Finnegan (7/15/2014)
I'd first try to update the stats on the database that has the performance issues to see if that helps. Looks like you have alot of free space on your drives, how often do you intend to do the rebuild?

Is your tempdb on a separate drive?
Out of curiosity, I see that you have two log files on your database, is there a specific reason for this?


I perform the rebuild /reorganize everyday :) ( based on logic : rebuild if index fragmentation above 50% else reorganize)

no they are in the same drive ...FYI log 1 in the E:\ and log 2 in the G:\

hmm I guess they are afraid there will be a lot of transactions from that database so it is better to spilt it up ...

BTW can we rollback the rebuild / reorganize ?

Many thanks

Cheers
Post #1592909
Posted Tuesday, July 15, 2014 11:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:51 PM
Points: 36,959, Visits: 31,469
murnilim9 (7/15/2014)
sorry ... I hv just realized there is something that I haven't answered yet..
I perform the default rebuild indexes which has the feature ( Sort_temp = off ) and when i take a look on my temp db ( data file size is 792.56 MB and Volume usage = 0.25% ) and log file is 505MB ( volume usage = 0.16%)

meanwhile my database size is data file --> 5.36 GB ( volume usage = 1.78%)
log file 1 --> 5MB (volume usage = 0%)
log file 2 --> 15 GB ( volume usage = 9.37%)

I will rebuild around 23 indexes with each size around 0.3 GB

What do you think about it ?

thankss!!



Doing a log file backup once every 2 to 3 hours isn't enough especially when rebuilding/reorganizing indexes. I recommend you do T-LOG backups every 15 minutes or less. Don't worry about the number of log files. Restores through the GUI will easily take care of all that for you. If you don't trust the restore GUI, use it to build the restore script.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1592910
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse