January 23, 2014 at 11:34 am
GilaMonster (1/22/2014)
btw, going through the GUI to shrink the log runs DBCC ShrinkFile, there's no difference between running the command from a script or the GUI.
By going through the GUI it generated the following syntax:
USE [DW]
GO
DBCC SHRINKFILE (N'DW_log' , 0, TRUNCATEONLY)
GO
The job that I was using had the following syntax:
DBCC SHRINKFILE (DW_log,1)
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 23, 2014 at 2:48 pm
Welsh Corgi (1/23/2014)
GilaMonster (1/22/2014)
btw, going through the GUI to shrink the log runs DBCC ShrinkFile, there's no difference between running the command from a script or the GUI.By going through the GUI it generated the following syntax:
USE [DW]
GO
DBCC SHRINKFILE (N'DW_log' , 0, TRUNCATEONLY)
GO
The job that I was using had the following syntax:
DBCC SHRINKFILE (DW_log,1)
Which are completely equivalent commands.
Truncate Only has no meaning when applied to a log file, it's ignored. So the only difference is that one is trying to shrink the log to 0 (which is impossible) and one is trying to shrink the log to 1MB. Since it's exceedingly unlikely that you have less than 1MB of used log, those two commands do exactly the same thing.
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
January 23, 2014 at 2:56 pm
GilaMonster (1/23/2014)
Welsh Corgi (1/23/2014)
GilaMonster (1/22/2014)
btw, going through the GUI to shrink the log runs DBCC ShrinkFile, there's no difference between running the command from a script or the GUI.By going through the GUI it generated the following syntax:
USE [DW]
GO
DBCC SHRINKFILE (N'DW_log' , 0, TRUNCATEONLY)
GO
The job that I was using had the following syntax:
DBCC SHRINKFILE (DW_log,1)
Which are completely equivalent commands.
Truncate Only has no meaning when applied to a log file, it's ignored. So the only difference is that one is trying to shrink the log to 0 (which is impossible) and one is trying to shrink the log to 1MB. Since it's exceedingly unlikely that you have less than 1MB of used log, those two commands do exactly the same thing.
It did not do the same thing.
The code generated from the GUI shrank the file as long as the last entry in the status column did not equal 0.
Often I had to perform multiple backups to get zeros at the end when I executed DBCC LogInfo.
FileIdFileSizeStartOffsetFSeqNoStatusParityCreateLSN
2253952819215217680640
2253952262144152185421280
225395251609615217660640
227852877004815217670640
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 23, 2014 at 3:03 pm
Welsh Corgi (1/23/2014)
GilaMonster (1/23/2014)
Welsh Corgi (1/23/2014)
GilaMonster (1/22/2014)
btw, going through the GUI to shrink the log runs DBCC ShrinkFile, there's no difference between running the command from a script or the GUI.By going through the GUI it generated the following syntax:
USE [DW]
GO
DBCC SHRINKFILE (N'DW_log' , 0, TRUNCATEONLY)
GO
The job that I was using had the following syntax:
DBCC SHRINKFILE (DW_log,1)
Which are completely equivalent commands.
Truncate Only has no meaning when applied to a log file, it's ignored. So the only difference is that one is trying to shrink the log to 0 (which is impossible) and one is trying to shrink the log to 1MB. Since it's exceedingly unlikely that you have less than 1MB of used log, those two commands do exactly the same thing.
It did not do the same thing.
They are completely equivalent commands, they do exactly the same thing (except in the rare case where you have in-use VLFs making up less than 1MB). It makes no difference at all whether you run a shrink log from a job, the gui or the command line.
If you'd run shrink log twice in a row from the job, you'd have seen the same thing. There's a feature where if two shrinks and a log backup are run close together, SQL realises you're trying to shrink and does some operations to help the log to shrink. Can't recall the details.
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
January 23, 2014 at 3:16 pm
GilaMonster (1/23/2014)
Welsh Corgi (1/23/2014)
GilaMonster (1/23/2014)
Welsh Corgi (1/23/2014)
GilaMonster (1/22/2014)
btw, going through the GUI to shrink the log runs DBCC ShrinkFile, there's no difference between running the command from a script or the GUI.By going through the GUI it generated the following syntax:
USE [DW]
GO
DBCC SHRINKFILE (N'DW_log' , 0, TRUNCATEONLY)
GO
The job that I was using had the following syntax:
DBCC SHRINKFILE (DW_log,1)
Which are completely equivalent commands.
Truncate Only has no meaning when applied to a log file, it's ignored. So the only difference is that one is trying to shrink the log to 0 (which is impossible) and one is trying to shrink the log to 1MB. Since it's exceedingly unlikely that you have less than 1MB of used log, those two commands do exactly the same thing.
It did not do the same thing.
They are completely equivalent commands, they do exactly the same thing (except in the rare case where you have in-use VLFs making up less than 1MB). It makes no difference at all whether you run a shrink log from a job, the gui or the command line.
If you'd run shrink log twice in a row from the job, you'd have seen the same thing. There's a feature where if two shrinks and a log backup are run close together, SQL realises you're trying to shrink and does some operations to help the log to shrink. Can't recall the details.
No offense but I worked with a Microsoft senior SQL Server Engineer and we were able to resolve the issue. I know that you have superior skills to probably anyone on the Microsoft SQL Team but this young lady was good.
She had the benefit of a help session which you did not . Everything is working fine since our last call.
The command in the job did not return the desired results. The code generated via the GUI did exactly what I wanted.
Thank you very much for you help. I appreciate it.
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 23, 2014 at 3:28 pm
GilaMonster (1/23/2014)
Welsh Corgi (1/23/2014)
GilaMonster (1/22/2014)
btw, going through the GUI to shrink the log runs DBCC ShrinkFile, there's no difference between running the command from a script or the GUI.By going through the GUI it generated the following syntax:
USE [DW]
GO
DBCC SHRINKFILE (N'DW_log' , 0, TRUNCATEONLY)
GO
The job that I was using had the following syntax:
DBCC SHRINKFILE (DW_log,1)
Which are completely equivalent commands.
Truncate Only has no meaning when applied to a log file, it's ignored. So the only difference is that one is trying to shrink the log to 0 (which is impossible) and one is trying to shrink the log to 1MB. Since it's exceedingly unlikely that you have less than 1MB of used log, those two commands do exactly the same thing.
There was nothing being used in the log at times. All Status 0, no 2's.
So the GUI Command worked, the other did not and I performed the same actions besides the DBCC Shrinkfile. I had to backup the log more than once on more than one occasion but I cleared the log.
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 23, 2014 at 3:31 pm
Welsh Corgi (1/23/2014)
GilaMonster (1/23/2014)
Welsh Corgi (1/23/2014)
GilaMonster (1/23/2014)
Welsh Corgi (1/23/2014)
GilaMonster (1/22/2014)
btw, going through the GUI to shrink the log runs DBCC ShrinkFile, there's no difference between running the command from a script or the GUI.By going through the GUI it generated the following syntax:
USE [DW]
GO
DBCC SHRINKFILE (N'DW_log' , 0, TRUNCATEONLY)
GO
The job that I was using had the following syntax:
DBCC SHRINKFILE (DW_log,1)
Which are completely equivalent commands.
Truncate Only has no meaning when applied to a log file, it's ignored. So the only difference is that one is trying to shrink the log to 0 (which is impossible) and one is trying to shrink the log to 1MB. Since it's exceedingly unlikely that you have less than 1MB of used log, those two commands do exactly the same thing.
It did not do the same thing.
They are completely equivalent commands, they do exactly the same thing (except in the rare case where you have in-use VLFs making up less than 1MB). It makes no difference at all whether you run a shrink log from a job, the gui or the command line.
If you'd run shrink log twice in a row from the job, you'd have seen the same thing. There's a feature where if two shrinks and a log backup are run close together, SQL realises you're trying to shrink and does some operations to help the log to shrink. Can't recall the details.
No offense but I worked with a Microsoft. I know that you have superior skills but this young lady was good.
She had the benefit of a help session. Everything is working fine since our last call.
The command in the job did not return the desired results. The code generated via the GUI did exactly what I wanted.
Thanks again.
May be belatedly, but first, what do you mean by 'a Microsoft'? One of many, does not matter which one?
Second, if you have as gripe against any - and I stress ANY - SQLS tool, please log it on connect, and if you have a problem to get a positive reply, say so and someone will step in.
Please consider yourself lucky that Gail stepped in to bail you out - you got the best of the best.
January 23, 2014 at 4:00 pm
Revenant (1/23/2014)
Welsh Corgi (1/23/2014)
GilaMonster (1/23/2014)
Welsh Corgi (1/23/2014)
GilaMonster (1/23/2014)
Welsh Corgi (1/23/2014)
GilaMonster (1/22/2014)
btw, going through the GUI to shrink the log runs DBCC ShrinkFile, there's no difference between running the command from a script or the GUI.By going through the GUI it generated the following syntax:
USE [DW]
GO
DBCC SHRINKFILE (N'DW_log' , 0, TRUNCATEONLY)
GO
The job that I was using had the following syntax:
DBCC SHRINKFILE (DW_log,1)
Which are completely equivalent commands.
Truncate Only has no meaning when applied to a log file, it's ignored. So the only difference is that one is trying to shrink the log to 0 (which is impossible) and one is trying to shrink the log to 1MB. Since it's exceedingly unlikely that you have less than 1MB of used log, those two commands do exactly the same thing.
It did not do the same thing.
They are completely equivalent commands, they do exactly the same thing (except in the rare case where you have in-use VLFs making up less than 1MB). It makes no difference at all whether you run a shrink log from a job, the gui or the command line.
If you'd run shrink log twice in a row from the job, you'd have seen the same thing. There's a feature where if two shrinks and a log backup are run close together, SQL realises you're trying to shrink and does some operations to help the log to shrink. Can't recall the details.
No offense but I worked with a Microsoft. I know that you have superior skills but this young lady was good.
She had the benefit of a help session. Everything is working fine since our last call.
The command in the job did not return the desired results. The code generated via the GUI did exactly what I wanted.
Thanks again.
May be belatedly, but first, what do you mean by 'a Microsoft'? One of many, does not matter which one?
Second, if you have as gripe against any - and I stress ANY - SQLS tool, please log it on connect, and if you have a problem to get a positive reply, say so and someone will step in.
Please consider yourself lucky that Gail stepped in to bail you out - you got the best of the best.
I have no problem with any advise that I was provided. I do not have a gripe, please, do you?
I got the situation resolved. I know what to look for and what steps to take to resolve the issue.
I had an open Ticket with Microsoft's SQL Server Engineers Team and I do not feel that they have anything going above Gail. I very much appreciate her help.
Quite to the contrary.
My problem is resolved so let it go, you are not helping with the reply. The problem has been resolved for 7 hours.
Don't be so negative. There was no value added to your remarks other than Gail knows her stuff. That is common knowledge.
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/
January 23, 2014 at 4:23 pm
Revenant,
I will get a statement from the Microsoft SQL Server Engineer that documents what actions were taken and the outcome?
The situation was not the norm and there were extenuating circumstances.
What technical advice do you have to offer?
Regards.:-)
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 23, 2014 at 4:59 pm
Welsh Corgi (1/23/2014)
Revenant,I will get a statement from the Microsoft SQL Server Engineer that documents what actions were taken and the outcome?
The situation was not the norm and there were extenuating circumstances.
What technical advice do you have to offer?
Regards.:-)
Not much technical advice because I have no technical details; however, I can escalate the issue - if it is properly logged with the expected supporting documentation.
edit: In other words, I can get the attention.
January 23, 2014 at 5:08 pm
Revenant (1/23/2014)
Welsh Corgi (1/23/2014)
Revenant,I will get a statement from the Microsoft SQL Server Engineer that documents what actions were taken and the outcome?
The situation was not the norm and there were extenuating circumstances.
What technical advice do you have to offer?
Regards.:-)
Not much technical advice because I have no technical details; however, I can escalate the issue - if it is properly logged with the expected supporting documentation.
edit: In other words, I can escalate the issue.
There is nothing to escalate. I would have thought that you would have picked up on that.
Case Closed.
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/
Viewing 11 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply