shrinked my ss2k5 log file by below option

Question

shrinked my ss2k5 log file by below option

Viewing 12 posts - 16 through 26 (of 26 total)

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

Silverfox SSC-Insane Points: 23150 More actions · Answer 1

GilaMonster (10/8/2009)
Silverfox (10/8/2009)
it looks like it just resized back to the free space in the log file and done nothing with the 2nd log file. if that is the case, then that just satisifies my curiosity. and from the ops point of view i take it that if you have just 1 log file, the emptyfile just runs and just removes the commited transactions from the 1st log file.
Books Online implies (but does not outright state) that EmptyFile is for data files only. It makes sense, when you think of the log's architecture. Shrink when run on a log shrinks the file back to the boundary of the active VLF (virtual log file). It doesn't (to my understanding) move log records around.
The way to remove a second log file is to ensure that the active log is only in the other log file and then drop the file. Remember the log files are written sequentially, log records aren't striped across log files.

Thanks for the clarification, the test I done confused the hell out of me. not saying I would ever use more than 1 log file. but it is nice to hear from someone else who knows what they are talking about.

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

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

Silverfox (10/8/2009)
not saying I would ever use more than 1 log file.

Only valid reason for multiple log files is space. If the drive that the log is on has perhaps not quite enough space for the case where the log grows unexpectedly, then adding a second log file elsewhere is a safety net.

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

Silverfox SSC-Insane Points: 23150 More actions · Answer 3

Never worked for a client where we have had a need for multiple log files, but then again maybe I have been lucky so far.

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

forsqlserver SSCoach Points: 18902 More actions · Answer 4

I only want to shrink my log files without deleting.....

Thanks

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

guptaajay1985 (10/8/2009)
I only want to shrink my log files without deleting.....

Why? The log should not be regularly shrunk. It should be set to the size it needs to be for the workload of the database and the frequency of log backups, and then it should be left alone.

Are you running log backups? Your earlier post implied not. Please confirm if you are running log backups.

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

forsqlserver SSCoach Points: 18902 More actions · Answer 6

forsqlserver

SSCoach

Points: 18902

October 9, 2009 at 12:12 am

#1063874

Dear,

I am taking full backup...

Thanks

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

I didn't ask about full backups. I asked about log backups.

Have you yet read the article that's been recommended several times?

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

forsqlserver SSCoach Points: 18902 More actions · Answer 8

When I restore a full back up then I also got a log file at restore...

So we are taking full backup Is it not included log backup itself...?

Thanks

Silverfox SSC-Insane Points: 23150 More actions · Answer 9

guptaajay1985 (10/9/2009)
When I restore a full back up then I also got a log file at restore...
So we are taking full backup Is it not included log backup itself...?

I think your understanding is wrong, it would be easier if you read the article that has been mentioned.

A full backup does not include a log backup. log backups are done separately. having the database in full recovery model, means that you can do log backups as well as full backups. unless you do log backups separately, you will not be able to do a point of time recovery which involves restoring from the last full backup then the log backups to the time that you want to restore to.

if you are only doing a full backup every day, you will lose a days information if you need to restore from your last full backup. doing log backups frees up space in the transaction log, so in theory if you size the log correctly to handle your daily workload, and you backup your log file regularly, your log file will never grow past what you sized it to initially. therefore you should never have to shrink the log file.

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

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

guptaajay1985 (10/9/2009)
When I restore a full back up then I also got a log file at restore...

Yup, it's a full database backup. It restores the full database to the state it was at the time of backup

So we are taking full backup Is it not included log backup itself...?

If that was the case, would I be repeatedly asking about log backups?

Read the article

Read this http://msdn.microsoft.com/en-us/library/ms191429%28SQL.90%29.aspx

and this http://msdn.microsoft.com/en-us/library/ms190440%28SQL.90%29.aspx

and this http://msdn.microsoft.com/en-us/library/ms190217%28SQL.90%29.aspx

Come back once you've read all of them

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

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 11

@ajayguptha

Selecting the Empty file by migrating the data to other files in the same filegroup check box.

Selecting this option moves all data from the specified file to other files in the filegroup. The empty file can then be deleted. This option is the same as executing DBCC SHRINKFILE with the EMPTYFILE option.

Dave Jones - CRC SSC-Addicted Points: 431 More actions · Answer 12

The way to remove a second log file is to ensure that the active log is only in the other log file and then drop the file. Remember the log files are written sequentially, log records aren't striped across log files.

So if I understand you correctly, if I have 2 log files the 2nd one wont get used at all until the first one fills up? Never used 2, but I can see where having a safety net would be nice.

Thanks