transaction logs

Question

transaction logs

Viewing 15 posts - 16 through 30 (of 32 total)

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

Donn Polix Default port Points: 1453 More actions · Answer 1

GilaMonster (11/4/2008)
Lynn Pettis (11/4/2008)
Don't simply truncate the transaction log. You will break your log chain, and won't be able to do a restore to a point in time. Also, you won't be able to perform any more transaction log backups until you have completed another full backup (possibly just a differential, but not too sure on that one).
Diff is sufficient. It just has to be something to base the log chain off. Both full and diff do that. If diff didn't it wouldn't be possible to do the full backup restore, diff backup restore and then the tran logs from there.
alter database testing set recovery full
backup database testing to disk = 'D:\DEvelop\testing.bak' with init
backup log testing with truncate_only
backup log testing to disk = 'D:\Develop\testing.trn' with init
/*Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.*/
backup database testing to disk = 'D:\DEvelop\testing_diff.bak' with DIFFERENTIAL
backup log testing to disk = 'D:\Develop\testing.trn' with init
-- successful

I jumped the gun. Diff can, sorry. Thanks gail.

_____________
Donn Policarpio

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

GilaMonster (11/4/2008)
Lynn Pettis (11/4/2008)
Don't simply truncate the transaction log. You will break your log chain, and won't be able to do a restore to a point in time. Also, you won't be able to perform any more transaction log backups until you have completed another full backup (possibly just a differential, but not too sure on that one).
Diff is sufficient. It just has to be something to base the log chain off. Both full and diff do that. If diff didn't it wouldn't be possible to do the full backup restore, diff backup restore and then the tran logs from there.
alter database testing set recovery full
backup database testing to disk = 'D:\DEvelop\testing.bak' with init
backup log testing with truncate_only
backup log testing to disk = 'D:\Develop\testing.trn' with init
/*Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.*/
backup database testing to disk = 'D:\DEvelop\testing_diff.bak' with DIFFERENTIAL
backup log testing to disk = 'D:\Develop\testing.trn' with init
-- successful

I thought it might, but I didn't want to say so based on an assumption without testing to be sure.

Thanks for the clarification Gail.

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 3

I normally use this method:

1. backup log Pubs with no_log

2. dbcc shrinkfile(Pubs_log, 100) -- shrink log file

3. dbcc updateusage(Pubs) -- correct available free space amount

sayfrend SSCommitted Points: 1733 More actions · Answer 4

GilaMonster (11/4/2008)

terryj30 (11/4/2008)
try this
1) dbcc opentran(dbname) - this will show the oldest open transaction
2) Kill this process
3) backup log dbname with no_log
4) use dbname
5) dbcc shrinkfile(dbname_log,100) - shrinks the log back to 100mb

What is the reason to kill the open Transaction. Correct me if I am wrong, When SQL Server takes Transactional log backup, it removes all the inactive transactions leaving active transactions untouched.

Now we have free space left in the log file after transactional log backup, with the help of DBCC Shrinkfile we can shrink logfile...

Now one thing I did not understand from your explanation... what is the need to kill the open transactions ??

Thanks,

IM

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

sayfrend (11/4/2008)
What is the reason to kill the open Transaction. Correct me if I am wrong, When SQL Server takes Transactional log backup, it removes all the inactive transactions leaving active transactions untouched.

SQL can only truncate the log up to the beginning of the oldest open transaction. If there's a very old open transaction, the log can't be truncated

It's only necessary if you do have long running transactions in the system, and, if you do, just killing them is not the greatest idea. What if that was the month's payroll run?

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 6

clive (11/4/2008)
I normally use this method:
1. backup log Pubs with no_log
2. dbcc shrinkfile(Pubs_log, 100) -- shrink log file
3. dbcc updateusage(Pubs) -- correct available free space amount

You missed a step

4. Backup pubs to disk = ... -- restart the log chain so it's possible to take log backups and do point in time restores past this point

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

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

WHY is everyone so eager to shrink their databases transaction log files? If they keep shrinking them after backups, they will just grow again. You should size your transaction log files and schedule periodic transaction log backups so that if they do grow it is due to exceptional processing, not the day to processing that occurs.

It takes system resources to expand the log file, and due to constant growing and shrinking, the file could easily become highly fragmented on disk impacting system performance.

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

Lynn Pettis (11/4/2008)
WHY is everyone so eager to shrink their databases transaction log files?

Beats me. I'm wondering why people are so eager to throw away log records and expose themselves to the potential loss of hours of data.

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

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

GilaMonster (11/4/2008)
Lynn Pettis (11/4/2008)
WHY is everyone so eager to shrink their databases transaction log files?
Beats me. I'm wondering why people are so eager to throw away log records and expose themselves to the potential loss of hours of data.

That one bothers me too. I'll do it on development databases where it isn't critical, but production databases, that is just plain dumb.

SQLPhil SSCarpal Tunnel Points: 4204 More actions · Answer 10

Lynn Pettis (11/4/2008)
WHY is everyone so eager to shrink their databases transaction log files?

A very good question! It seems to me that people seem very keen to clear the log regardless rather than figuring out why the log is growing in the first place. And then shrink the log.

Unless you have capacity issues on your disk that hosts the log, then why not leave the log to be so-many GB in size if that's what it needs to be. Whatever transaction is making it grow to be that size will complete faster if it doesn't get interrupted by frequent log file grows.

And if you are concerned about capacity, then you can always switch the recovery model to bulk logged if you're doing things like index rebuilds (just don't forget to switch it back!).

rlondon SSCarpal Tunnel Points: 4653 More actions · Answer 11

GilaMonster (11/4/2008)
Lynn Pettis (11/4/2008)
WHY is everyone so eager to shrink their databases transaction log files?
Beats me. I'm wondering why people are so eager to throw away log records and expose themselves to the potential loss of hours of data.

It shows lack of understanding of what the transaction log is for and how it works. They should read these posts more often. Gail everything I needed to know about transaction logs I learned from reading your posts. 😀

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

rlondon (11/5/2008)
Gail everything I needed to know about transaction logs I learned from reading your posts. 😀

😀 Thank you. i assume you saw the headline article here last friday (31 Oct)?

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

Steve Jones - SSC Editor SSC Guru Points: 736078 More actions · Answer 13

I think the naming could be better. Logs, for the most part in computer science, are just extra information about what an application is doing. It seems more and more people new to SQL Server see them as a repository and not an integral part of transaction integrity.

Perhaps a Transaction Record File or something like that would make it seem more intuitive.

kevin_nikolai SSCarpal Tunnel Points: 4343 More actions · Answer 14

Below are 2 reasons to truncate SQL Server log file/s (transaction log):

1.

Truncate log file/s if you have very little disk space left due to the SQL Server log file (transaction log) being several gigabytes in size and you won't be adding another disk or LUN (SAN STORAGE) soon.

Note:

If you truncate a log file which is usually around 10GB, it will simply grow back to 10GB again. This transaction log grow process will affect your SQL Server and application performance (slow response times). So truncate the log file now, but start ordering more disks for (RAID) or (LUN).

2.

If you truncate a log file which is usually around 2GB, but is now 7GB due to bulk inserts, truncating it is recommended (unless you have plenty disk space and don’t mind the extra time it takes for backup).

***

The transaction log is automatically truncated when the database is using the simple recovery model.

Microsoft recommends using the full recovery model on Production databases, this will allow restore to point-in time and backup of transaction logs.

===========================================================

Below is the procedure to truncate SQL Server log file/s (transaction log):

USE AdventureWorks

GO

-- Checkpoint forces all dirty pages for the current database to be

-- written to disk. Dirty pages are data or log pages modified after

-- entered into the buffer cache, but the modifications have not yet

-- been written to disk.

-- The Checkpoint statement saves time in a subsequent recovery

-- by creating a point at which all modifications to data and log

-- pages are guaranteed to have been written to disk.

CHECKPOINT

-- SET RECOVERY FULL (for full backup)

ALTER DATABASE [AdventureWorks]

SET RECOVERY FULL

GO

-- If you did not backup database previous night, then do it now,

-- otherwise skip this step.

BACKUP DATABASE [AdventureWorks] TO DISK = N'C:\AdventureWorks.bak' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

-- Backup log file (recommended).

-- skip this step if transaction log backups take hours (their size is several Gigabytes)

-- and your management / customer management approves.

-- It will truncate the virtual log file, so physical log file size won't change.

BACKUP LOG [AdventureWorks] TO DISK = N'C:\AdventureWorks-TLOG.bak' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

-- Backup log file WITH NO_LOG, if you skipped the previous step –

-- (Backup log file), then do this.

-- If you did the previous step – (Backup log file), then skip this

-- step.

BACKUP LOG [AdventureWorks] WITH NO_LOG

-- This step is to clear & set the size of physical log file.

ALTER DATABASE [AdventureWorks]

SET RECOVERY SIMPLE

GO

DBCC SHRINKFILE (AdventureWorks_Log, 100)

-- Updates database space available.

DBCC UPDATEUSAGE(AdventureWorks)

-- SET RECOVERY FULL (for full backup)

ALTER DATABASE [AdventureWorks]

SET RECOVERY FULL

GO

Guess we can now put the truncate transaction log issue to rest.

Regards

Kevin

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

clive (11/6/2008)
Below are 2 reasons to truncate SQL Server log file/s (transaction log):
1.
Truncate log file/s if you have very little disk space left due to the SQL Server log file (transaction log) being several gigabytes in size and you won't be adding another disk or LUN (SAN STORAGE) soon.
Note:
If you truncate a log file which is usually around 10GB, it will simply grow back to 10GB again. This transaction log grow process will affect your SQL Server and application performance (slow response times). So truncate the log file now, but start ordering more disks for (RAID) or (LUN).
2.
If you truncate a log file which is usually around 2GB, but is now 7GB due to bulk inserts, truncating it is recommended (unless you have plenty disk space and don’t mind the extra time it takes for backup).
***
The transaction log is automatically truncated when the database is using the simple recovery model.
Microsoft recommends using the full recovery model on Production databases, this will allow restore to point-in time and backup of transaction logs.
===========================================================
Below is the procedure to truncate SQL Server log file/s (transaction log):
USE AdventureWorks
GO
-- Checkpoint forces all dirty pages for the current database to be
-- written to disk. Dirty pages are data or log pages modified after
-- entered into the buffer cache, but the modifications have not yet
-- been written to disk.
-- The Checkpoint statement saves time in a subsequent recovery
-- by creating a point at which all modifications to data and log
-- pages are guaranteed to have been written to disk.
CHECKPOINT
-- SET RECOVERY FULL (for full backup)
ALTER DATABASE [AdventureWorks]
SET RECOVERY FULL
GO
-- If you did not backup database previous night, then do it now,
-- otherwise skip this step.
BACKUP DATABASE [AdventureWorks] TO DISK = N'C:\AdventureWorks.bak' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- Backup log file (recommended).
-- skip this step if transaction log backups take hours (their size is several Gigabytes)
-- and your management / customer management approves.
-- It will truncate the virtual log file, so physical log file size won't change.
BACKUP LOG [AdventureWorks] TO DISK = N'C:\AdventureWorks-TLOG.bak' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-- Backup log file WITH NO_LOG, if you skipped the previous step –
-- (Backup log file), then do this.
-- If you did the previous step – (Backup log file), then skip this
-- step.
BACKUP LOG [AdventureWorks] WITH NO_LOG
-- This step is to clear & set the size of physical log file.
ALTER DATABASE [AdventureWorks]
SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (AdventureWorks_Log, 100)
-- Updates database space available.
DBCC UPDATEUSAGE(AdventureWorks)
-- SET RECOVERY FULL (for full backup)
ALTER DATABASE [AdventureWorks]
SET RECOVERY FULL
GO
Guess we can now put the truncate transaction log issue to rest.
Regards
Kevin

Having set the database to simple then back to full recovery model, you need to do one more thing, another full backup of the database.