Misconception or not doing right?

Question

Post reply

Misconception or not doing right?

DBABC

SSCarpal Tunnel

Points: 4813
More actions
June 4, 2009 at 1:50 pm

#201170

Hi,
I was of the concept that if we shrink the transaction log it will break the LSN sequence and i won't be able to restore to point in time.
so i tried to test it but i couldnt break the restore here is what i did
Scenario 1:
1-Full backup
2-TLog backup1
3-TLog Shrink
4-TLog backup2
Restore Process1:
1-Restore Full backup
2-Restore TLog backup1
3-Restore TLog backup2
Scenario 2:
1-Full backup
2-TLog shrink
3-TLog backup1
Restore Process2:
1-Restore Full backup
2-Restore TLog backup1
in both scenarios i am able to restore all my transactions, so can someone please tell me the steps when i won't be able to restore all my transactions?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 1

If u Truncate the log file using TRUNCATE ONLY(by either shriinking log or by either taking backup)

OR

Switching to simple recovery model

OR if u miss one of the log file between two log file sequences.

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 2

A normal Shrink doesnt effects log chain because u r not messing or truncating any data in log file, u r just shrinking space that was occupied by log file but is unused now.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Mayank Khatri (6/4/2009)
If u Truncate the log file using TRUNCATE ONLY(by either shriinking log or by either taking backup)

A shrink does not truncate the log.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Please read through this - Managing Transaction Logs[/url]

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

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

Mayank Khatri SSCertifiable Points: 7984 More actions · Answer 5

GilaMonster (6/4/2009)
Mayank Khatri (6/4/2009)
If u Truncate the log file using TRUNCATE ONLY(by either shriinking log or by either taking backup)
A shrink does not truncate the log.

Gila,

Please look at this MSDN as u might have done many times earlier, u can SHRINK the file using TRUNCATE ONLY that would break the sequence

[Code]

USE AdventureWorks;

GO

SELECT file_id, name

FROM sys.database_files;

GO

DBCC SHRINKFILE (1, TRUNCATEONLY);

[/CODE]

Source

http://msdn.microsoft.com/en-us/library/ms189493.aspx">

http://msdn.microsoft.com/en-us/library/ms189493.aspx

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Mayank Khatri (6/4/2009)
Gila,
Please look at this MSDN as u might have done many times earlier, u can SHRINK the file using TRUNCATE ONLY that would break the sequence

I see nothing on that page that says anywhere that shrinking a log truncates the log or breaks the log chain. Please quote the section of it that does so. In fact, from said article:

Examples
B. Shrinking a log file to a specified target size
The following example shrinks the log file in the AdventureWorks database to 1 MB. To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.

[Code]
USE AdventureWorks;
GO
SELECT file_id, name
FROM sys.database_files;
GO
DBCC SHRINKFILE (1, TRUNCATEONLY);
[/CODE]

File 1 is the primary data file, so that code isn't even shrinking a log. Furthermore, from the page you referenced.

From http://msdn.microsoft.com/en-us/library/ms189493.aspx

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.

The only operations that truncate a log file are:

Log backups (in full/bulk-logged)

Checkpoint (in Simple)

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

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

DBABC SSCarpal Tunnel Points: 4813 More actions · Answer 7

GilaMonster (6/4/2009)The only operations that truncate a log file are:
Log backups (in full/bulk-logged)
Checkpoint (in Simple)

does that mean, it is not possible to break the Log sequence as long as we are taking backups of the log files (not missing any backup), no matter whether we shrink the log file or not?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

usman.tanveer (6/5/2009)
GilaMonster (6/4/2009)The only operations that truncate a log file are:
Log backups (in full/bulk-logged)
Checkpoint (in Simple)
does that mean, it is not possible to break the Log sequence as long as we are taking backups of the log files (not missing any backup), no matter whether we shrink the log file or not?

The only two ways I know of to break the log sequence are: 1) BACKUP LOG [i]dbname[/i] WITH TRUNCATE_ONLY (This is depreciated), and 2) changing the database recovery model to simple and back to full/bulk_logged (ussing DDL or the GUI).