Managing Transaction Logs

Question

Post reply

Managing Transaction Logs

Viewing 13 posts - 31 through 42 (of 42 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

MostInterestingMan (10/7/2010)

Then set it to Simple recovery in the meantime.[/quote]

[/quote]

For the actual used space in the log you can run this :

dbcc sqlperf(logspace)

That'll give you the hd used space and the % used space in that file.[/quote]

7 GB log DB set to SIMPLE.

Thanks for the DBCC command.

For my maintenance plan, is this the recommended best practice?

1) nightly dbcc check for user databases (before backups)

2) monthly selective index maintenance (reorg / rebuild based on frag)

-> reorg for avg_fragmentation_in_percent < 30

-> rebuild for avg_fragmentation_in_percent > 30

Do you run nightly integrity check on sys DBs? How about index maintenace?

Thanks,[/quote]

If time permit I'd run all those daily. That makes for a smaller required window for the indexes as you have less daily work to do.

My pers. preference is to restore the backup on a standby server and run the checkDB there... that way you know the backup is good and that the restore is not corrupted as well.

If the db is really small / or you have enough time you run it on both ends

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

Based on statements and questions asked I STRONGLY encourage you to hire a professional to review your systems and help you get a maintenance configuration set up that is correct for your needs. One day with a good consultant will keep you from getting yourself into trouble in the future.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 3

GilaMonster (10/7/2010)
MostInterestingMan (10/7/2010)
For the DB with 7GB log I am not doing any log backup until I investigation why the log grew to 7GB in the first place. I have a pending discussion with the developer to understand the app for some background.
Then set it to Simple recovery in the meantime.

Gail, something interesting happened when I backed up the 7gb log file. Note that the DB with the 7gb log file is now in FULL recovery. The file size is still 7gb, it didn't release the disk space. I was expecting it would. Is this the expected behavior?

Thanks

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

Derrick Smith SSChampion Points: 10833 More actions · Answer 4

Doing a transaction log backup does not release space back to the OS. The file stays allocated at that size (for good reason).

If you run DBCC SQLPERF(logspace), it should show very little usage for the log file in question, which means the majority of the data was cleared out of it.

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

MostInterestingMan (10/12/2010)
Gail, something interesting happened when I backed up the 7gb log file. Note that the DB with the 7gb log file is now in FULL recovery. The file size is still 7gb, it didn't release the disk space.

The only things that can reduce a file size are:

DBCC shrink database

DBCC shrink file

auto shrink

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

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 6

GilaMonster (10/12/2010)
MostInterestingMan (10/12/2010)
Gail, something interesting happened when I backed up the 7gb log file. Note that the DB with the 7gb log file is now in FULL recovery. The file size is still 7gb, it didn't release the disk space.
The only things that can reduce a file size are:
DBCC shrink database
DBCC shrink file
auto shrink

Understood.

I ran DBCC SQLPERF(logspace) and see that the space was not release to the OS. I will shrink the file and set auto shrink on the database options page.

Thanks

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

Derrick Smith SSChampion Points: 10833 More actions · Answer 7

MostInterestingMan (10/12/2010)
GilaMonster (10/12/2010)
MostInterestingMan (10/12/2010)
Gail, something interesting happened when I backed up the 7gb log file. Note that the DB with the 7gb log file is now in FULL recovery. The file size is still 7gb, it didn't release the disk space.
The only things that can reduce a file size are:
DBCC shrink database
DBCC shrink file
auto shrink
Understood.
I ran DBCC SQLPERF(logspace) and see that the space was not release to the OS. I will shrink the file and set auto shrink on the database options page.
Thanks

Do not set auto_shrink, EVER.

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

MostInterestingMan (10/12/2010)
I will shrink the file and set auto shrink on the database options page.

Good idea, if you want to absolutely cripple database performance, fragment your indexes to hell and back and have frequent unexplained bouts of poorer performance than usual.

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx

http://www.sqlskills.com/blogs/paul/post/Auto-shrink-e28093-turn-it-OFF!.aspx

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

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 9

GilaMonster (10/12/2010)
MostInterestingMan (10/12/2010)
I will shrink the file and set auto shrink on the database options page.
Good idea, if you want to absolutely cripple database performance, fragment your indexes to hell and back and have frequent unexplained bouts of poorer performance than usual.
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx
http://www.sqlskills.com/blogs/paul/post/Auto-shrink-e28093-turn-it-OFF!.aspx

I just read Paul's blog and the take-away is to leave the database as is and don't remove the extra space. But in my situation I need the space back. Is my index maintenace (reorg) task going to get the space back or do I need to manually shrink with truncateonly option?

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 10

Follow the experts advice @ Gail, Derrick, Ninja and homebrew.

Never turn AutoShrink option ON. This would be just an invitation to troubles.

Shrinking the files is not a regular or multi-time operation. Most of the times, if not always it's a one time operation.

You need to shrink the file only if it grows to a very large size or have some disk space issues.

Otherwise frequent TLog Backups will ensure that TLog file won't grow massively.

Thank You,

Best Regards,

SQLBuddy

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

MostInterestingMan (10/12/2010)
But in my situation I need the space back.

Then do a once-off shrink of the log (and only the log) to a reasonable size (not 1). Do not schedule shrinks. Do not turn autoshrink on (ever). Do not shrink the data files if it's the log that's too large, which from all you've said here is the case.

Is my index maintenace (reorg) task going to get the space back or do I need to manually shrink with truncateonly option?

Read my post earlier about the ONLY three things that reduce a file's size on disk.

Truncate only is a valid option only for data files, not for 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 12

MostInterestingMan (10/12/2010)
Note that the DB with the 7gb log file is now in FULL recovery.

You do have log backups scheduled?

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

MostInterestingMan SSCertifiable Points: 6291 More actions · Answer 13

GilaMonster (10/12/2010)
MostInterestingMan (10/12/2010)
Note that the DB with the 7gb log file is now in FULL recovery.
You do have log backups scheduled?

Yes.

MCTS: BI 2008, MCITP: BI 2008
Stay Thirsty My Friends