November 24, 2011 at 1:38 pm
Andeavour (11/24/2011)
Argh... Shot down in flames by your sharp eyed skills 🙂You're right of course. I have actually only used it against Data files in the past, and now I'm hoist on my own petard of eagerness to answer a question :rolleyes:
I apologise for possibly sending an innocent questionner off barking up the wrong tree, and promise that in future I'll be more carreful with my answers...:ermm:
One of my favourite sayings is that "Every day is a school day", and today is no exception :blush:
You posted the question in a SQL Server 2005 Forum.
You were not incorrect in that those options can be used in SQL Server 2005 & below.
If you look at my post you will see that I used TRUNCATE_ONLY.
SQL Server did not ignore that option. 😀
You have nothing to appologize about. :exclamation:
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/
November 24, 2011 at 2:05 pm
Welsh Corgi (11/24/2011)
GilaMonster (11/24/2011)
The NoTruncate and TruncateOnly options on a shrink are solely for data files. They are completely ignored when shrinking a log file.It was my understanding that the NO_TRUNCATE & TRUNCATE_ONLY Options were discontinued in SQL Server 2008 and they are not ignored in SQL Server 2005 and below.
With SQL Server 2008 it is recommended that you switch to the Simple Recovery Model, SHrink the Log and Switch back from the Simple Recovery Model.
You're thinking about BACKUP LOG .. WITH TRUNCATE_ONLY. That was deprecated in SQL 2005 and discontinued in SQL 2008.
BACKUP_LOG .. WITH NO_TRUNCATE is fully supported in all versions, up to and including SQL 2012.
However, neither Andeavour nor I were talking about BACKUP LOG. We were talking about DBCC SHRINKFILE which has similarly named options. These options affect how shrinkfile handles moving data pages around and how it handles releasing the free space to the operating system.
For DBCC ShrinkFile, those options are only applicable to data files. When using DBCC ShrinkFile on a log file, the NOTRUNCATE and TRUNCATEONLY options are ignored (and note that the options are slightly different names, they have no underscores).
This was true in SQL 2005, SQL 2000 and probably earlier versions, as well as SQL 2008 and SQL 2012.
SQL 2005 Books Online:
http://msdn.microsoft.com/en-us/library/ms189493%28v=sql.90%29.aspx
NOTRUNCATE
Moves allocated pages from the end of a data file to unallocated pages in the front of the file with or without specifying target_percent. The free space at the end of the file is not returned to the operating system, and the physical size of the file does not change. Therefore, when NOTRUNCATE is specified, the file appears not to shrink.
NOTRUNCATE is applicable only to data files. The log files are not affected.
TRUNCATEONLY
Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent.
target_size is ignored if specified with TRUNCATEONLY.
TRUNCATEONLY is applicable only to data files.
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
November 24, 2011 at 2:54 pm
I'm unable to shrink the log file.
The log_reuse_wait_desc = REPLICATION and 11% of the log file is used.
Does anyone have any suggestions?
Thank you.
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/
November 24, 2011 at 3:10 pm
It was mentioned what can't be done using whatever method.
You can Shrink the log using the following as was provided in the BOL Link:
Copy
USE AdventureWorks;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
GO
However I would not do that if there are pending replication transactions.
I'm was trying to find a solution given the information that I provided and the responses to the questions that I was asked.
It's a good thing that this is not a critical situation where time is of essence. 🙂
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/
November 24, 2011 at 4:41 pm
Welsh Corgi (11/24/2011)
It was mentioned what can't be done using whatever method.You can Shrink the log using the following as was provided in the BOL Link:
Copy
USE AdventureWorks;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
GO
However I would not do that if there are pending replication transactions.
You could do that with pending replication transactions, it wouldn't harm replication in any way. However that's a solution for a log that's grown excessively solely due to a lack of log backups, not a general solution to shrink any log in any circumstance.
I'm was trying to find a solution given the information that I provided and the responses to the questions that I was asked.
Did you do any investigation of the replication setup? There will be a reason that the log can't be used due to replication. That should not be a prolonged condition in a properly working replication configuration.
Did you try what I suggested? :
Well, nothing's waiting for the log to be freed, so try to shrink it. If it won't, take a log backup and try to shrink again (and that's not a guess, that combo of commands has a specific meaning)
If that doesn't work it probably means that the active VLFs (held active because they're waiting to be replicated) are at the end of the log file. If that is the case (and DBCC LOGINFO will show you which VLFs are active in a log), then you'll need to resolve whatever is the problem with the replication first. Start by ensuring that the log reader is running.
It's a good thing that this is not a critical situation where time is of essence. 🙂
If it were a critical situation where time is of the essence then you would be best contacting Microsoft CSS and paying for support rather than depending on unpaid volunteers who are attempting to assist you in their spare time.
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
November 24, 2011 at 5:13 pm
Gail,
I appreciate your time and information.
The log backup jobs were turned off over a year ago have been neglected.
Please consider this is not a situation that I created but an environment in which I have recently been introduced and I'm trying to correct.
Yes, I need to investigate the Replication but I had a security issue when I attempted to install VPN.
There are 20 DB's on this Server. I shrank the logs on 16 of the 20 yesterday.
Cheers.
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/
November 24, 2011 at 5:19 pm
What do you mean did I do what you suggested?
Did you try what I suggested? :
Well, nothing's waiting for the log to be freed, so try to shrink it. If it won't, take a log backup and try to shrink again (and that's not a guess, that combo of commands has a specific meaning)
Yes I did everything that you suggested prior to posting the question.
So how is your day going so far?
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/
November 24, 2011 at 5:21 pm
First investigate and resolve whatever is preventing the log reuse in all of these. Once you've done that you can focus on shrinking them.
Thing is, nothing can ever move an VLF around in a log file. So if something (replication or other process) is holding log records active in a VLF at the end of the file, then that file won't shrink until that VLF can be marked inactive.
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
November 24, 2011 at 5:22 pm
Welsh Corgi (11/24/2011)
So how is your day going so far?
It's 2:21 AM and I'm still working.
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
November 24, 2011 at 5:33 pm
GilaMonster (11/24/2011)
Welsh Corgi (11/24/2011)
So how is your day going so far?It's 2:21 AM and I'm still working.
You are very dedicated and I'm sorry to hear that you are still working and I take it that you have to work until the end of the day.
I appreciate your sharing your vast knowledge of the subject material.
Too bad you did not have a Holiday like we did in the US.
It kinda S&#*%. The office is closed for 4 1/2 days for Turkey Day.
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/
November 25, 2011 at 9:28 am
This solution has worked for me...
http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/
November 25, 2011 at 11:44 am
Kevin Grogan (11/25/2011)
http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/%5B/quote%5D
Levin,
Thanks but I did that. Look at my initial post.
I need to do what Gail suggested. I really owe it to her for all of the excellent advice.
Check the Replication Configuration and pinpoint what is going on.
I did not set up replication on this machine.
I'm not sure why they are performing replication.
It has been a while since I was involved with replication so if anyone has some basic steps to take to check out the configuration and investigate what might be preventing from from shrinking the log I would welcome that.
Short of that all that I can do is Goggle.
I tried to set up VPN last night but I came up short. I was told to go for it by my boss but I need to ask her to initiate a formal request so that everything is kosher.
All that being said I need to be careful what I do.
The transaction log backups were turned off over a year and they did not grow overnight.
I was able to resolve the issue with 19 out of 20 Databases. That is not bad but not good enough.:laugh:
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/
November 25, 2011 at 12:08 pm
If you can confirm that the database is no longer (or never was) involved in replication, you can disable the replication (sp_repl_done). If that doesn't work, Gail has an article on her website that outlines the steps to clear the replication.
Basically, you have to rebuild the replication - then remove it.
After that, you should be able to shrink the log file.
If the database is supposed to be replicated, then you have to find out why the transactions are not being replicated and fix that.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 25, 2011 at 1:08 pm
Jeffrey Williams 3188 (11/25/2011)
If you can confirm that the database is no longer (or never was) involved in replication, you can disable the replication (sp_repl_done). If that doesn't work, Gail has an article on her website that outlines the steps to clear the replication.Basically, you have to rebuild the replication - then remove it.
After that, you should be able to shrink the log file.
If the database is supposed to be replicated, then you have to find out why the transactions are not being replicated and fix that.
Okie Dokie:cool:
Thank you.:-)
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/
November 29, 2011 at 3:40 am
Hello,
you could use "DBCC OPENTRAN" to see if there are transactions in the log file, that were not replicated.
If yes, as long as they are not replicated, you will not be able to shrink the log file.
(There is a command, sp_repldone, that will mark those transactions as finished - but that will also kill your replication)
Best regards
karl
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply