Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««910111213»»»

Managing Transaction Logs Expand / Collapse
Author
Message
Posted Tuesday, January 3, 2012 7:31 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:00 PM
Points: 21,212, Visits: 14,910
Great Article Gail.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1229638
Posted Tuesday, January 3, 2012 8:56 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, July 18, 2014 6:31 AM
Points: 68, Visits: 121
The only time a log should be shrunk is if some abnormal database activity (or failed log backups) has resulted in the log growing far beyond the size it needs to be for the database activity. In this situation, the log can be shrunk as a once-off operation, reducing it back to the size that it was before the abnormal activity.


I didn't see a recommendation for it, but what is the best method or T-SQL command to shrink the log back to its normal working size and keep the log file fragmentation to a minimum. Or, put another way, what is the best method to recreate a new log?

Thanks,
Bob
Post #1229645
Posted Wednesday, January 4, 2012 1:52 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 42,451, Visits: 35,506
RLB (1/3/2012)
The only time a log should be shrunk is if some abnormal database activity (or failed log backups) has resulted in the log growing far beyond the size it needs to be for the database activity. In this situation, the log can be shrunk as a once-off operation, reducing it back to the size that it was before the abnormal activity.


I didn't see a recommendation for it, but what is the best method or T-SQL command to shrink the log back to its normal working size and keep the log file fragmentation to a minimum. Or, put another way, what is the best method to recreate a new log?


It's in Kimberly's article that I referenced.

Shrink log to 0, regrow (in maybe a couple of steps) to the desired size.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1229714
Posted Wednesday, January 4, 2012 1:56 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 42,451, Visits: 35,506
jswong05 (1/3/2012)
This article again shows SQL Server's shortcomings.

1) default to full recovery mode (Oracle is opposite)


Default is whatever model is set to. If you want new databases defaulting to simple recovery, set model to simple recovery

2) not able to free unused space without side effecct. (Oracle does not have this problem)


What exactly do you mean by side effect? Fragmentation of the data files?

3) restore commands have to actually specify name of backup files (Oracle RMAN does not need to)


So how do you chose which backup you want to restore? (especially if restoring a backup from another server)
And there is a way in SQL to restore without specifying file names - backup devices. Also Management Studio can automatically pick up lists of backups if you select 'database' rather than 'device' and the backup history is intact. SQL 2012's even better in that regard

4) select can block insert, update .... (not in Oracle)


Read committed snapshot and snapshot isolation levels, introduced in SQL 2005. Readers no longer block writers and vis versa. It's your choice now whether you want locking or row versioning for isolation.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1229716
Posted Wednesday, January 4, 2012 2:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:37 PM
Points: 1, Visits: 80
Can you recommend the good algorithm for the log to be shrunk as a once-off operation, reducing it back to the size that it was before the abnormal activity.
Post #1229720
Posted Wednesday, January 4, 2012 2:29 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 42,451, Visits: 35,506
DBCC ShrinkFile(<name of log file>,<size log file was before abnormal growth>)


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1229732
Posted Friday, January 6, 2012 10:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 7:40 AM
Points: 47, Visits: 161
Very enlightening. I was actually shrinking my log files in the first and last step of an ETL run LOL. We need a real DBA so badly.
Post #1231599
Posted Friday, January 6, 2012 10:57 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:50 AM
Points: 42,451, Visits: 35,506
Good luck in you search. In the meantime, may I suggest this? http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1231614
Posted Friday, January 6, 2012 11:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 7:40 AM
Points: 47, Visits: 161
What a great book title :). To give you an idea of how far out of my element I am..I have to read this after I pass my CPA exams, 1 down 3 to go.
Post #1231621
Posted Tuesday, January 10, 2012 6:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, July 6, 2014 8:38 AM
Points: 248, Visits: 468
By mistake I choose one star I'm sorry this is great article thanks a lot
Post #1233101
« Prev Topic | Next Topic »

Add to briefcase «««910111213»»»

Permissions Expand / Collapse