January 21, 2014 at 8:51 am
I have a job to backup the Transaction Log of a Database.
I also have another job to shrink the Transaction Log.
All of a sudden the transaction log is not shrinking by running the transaction log backup and shrink transaction log.
If I right lick on the Database, select task. shrink, files select the log and it works.
Any ideas on what and why this is happening?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 21, 2014 at 9:11 am
Is this the same question that Grant and Gail already answered? Or is this a different one? Also, what is the reason to shrink the Transaction log file? Seems like it is going to grow again. Why not presize the log to the correct size and not worry about shrinking it?
January 21, 2014 at 9:19 am
Keith Tate (1/21/2014)
Is this the same question that Grant and Gail already answered? Or is this a different one? Also, what is the reason to shrink the Transaction log file? Seems like it is going to grow again. Why not presize the log to the correct size and not worry about shrinking it?
No it is not the same question.
This started within the last 24 hours.
The previous problem occurred over the weekend.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 21, 2014 at 9:22 am
January 21, 2014 at 9:23 am
Keith Tate (1/21/2014)
So, what is the reason to shrink the log file?
It grew to over 100 GB.
It keeps growing.
I created this Database (Data Warehouse) years ago.
It was never a problem until the last few days.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 21, 2014 at 9:26 am
Do you know what is causing the growth in the log? If not I would start there. What is the log_reuse_wait_desc say in sys.databases for the database in question?
January 21, 2014 at 9:29 am
Keith Tate (1/21/2014)
Do you know what is causing the growth in the log? If not I would start there. What is the log_reuse_wait_desc say in sys.databases for the database in question?
LOG_BACKUP
I do not understand what is going on. :unsure:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 21, 2014 at 9:31 am
I have a job to backup the Transaction Log of a Database.
Have you checked that the job is completing successfully? How often are you running the job and is it enough? Have you started loading more data recently than in the past?
January 21, 2014 at 9:33 am
Keith Tate (1/21/2014)
I have a job to backup the Transaction Log of a Database.
Have you checked that the job is completing successfully? How often are you running the job and is it enough? Have you started loading more data recently than in the past?
The transaction log backup is running hourly but that does not matter.
If I manually run the job followed by a job to shrink the DB it does not shrink.
If I shrink via the GUI it shrinks.
It is very weird.
Thank you for your input.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 21, 2014 at 9:34 am
January 21, 2014 at 9:38 am
I would stop shrinking the log file. If you want to keep the size of the file smaller take your tlog backups more often than once an hour. You are causing performance problems by shrinking and then growing the log file all day long. The log file can not take advance of instant file initialization so it has to zero out the file every time it grows. Also, what is the autogrowth setting for you log file? Is it 10% and now that you DW is larger maybe it shouldn't grow by percent, but instead by a set amount of space.
Shrinking the file is only ignoring the actual problem (and could be causing more performance problems)
January 21, 2014 at 9:46 am
Keith Tate (1/21/2014)
LOG_BACKUP
I do not understand what is going on.
This means that it is waiting on a transaction log backup
So what do I need to do to rectify the situation?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 21, 2014 at 7:16 pm
Keith Tate (1/21/2014)
LOG_BACKUP
I do not understand what is going on.
This means that it is waiting on a transaction log backup
If I do the backup in SQL Code how do I specify that log is waiting or no?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 21, 2014 at 7:20 pm
Just taking your normal tlog backups will clear the wait until there are new log records that need to be backed up, so seeing that description is pretty normal. Please see my last post about not shrinking the log file and running your tlog backups more often (or finding out why the log is growing more than is used too).
January 21, 2014 at 10:31 pm
What is the exact command you are using to backup the log (or if a maintenance plan, what are the exact options)?
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 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply