shrinked my ss2k5 log file by below option

Question

shrinked my ss2k5 log file by below option

forsqlserver

SSCoach

Points: 18902
More actions
October 7, 2009 at 11:58 pm

#226985

Sorry for the wrong entry in sql7,2000 group..
Dear All,
I have shrinked my ss2k5 log file by below option and I have got my log file from 10GB TO 10MB but where is the rest file ,I have not got it.Please help. I want that log files immediately.
tasks-->shrinks--->files--->logs---->empty files by migrating the data into same file group(last option)
Thanks,
Ajay
Thanks

Viewing 15 posts - 1 through 15 (of 26 total)

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

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

Why are you trying to shrink (empty) a log. What's the goal here, what's the reasoning?

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 2

guptaajay1985 (10/7/2009)
Sorry for the wrong entry in sql7,2000 group..
Dear All,
I have shrinked my ss2k5 log file by below option and I have got my log file from 10GB TO 10MB but where is the rest file ,I have not got it.Please help. I want that log files immediately.
tasks-->shrinks--->files--->logs---->empty files by migrating the data into same file group(last option)
Thanks,
Ajay

Well you certanity lived up to your signature.

I take it that you have additional files in your filegroup, because that is exactly what you have done using that option

--------------------------------------------------------------------------------------
[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 3

Thanks to write..

Hi Gilla Actually my servers are full of logs and in near future I have to make space on drive....

Thanks

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

Silverfox (10/8/2009)
I take it that you have additional files in your filegroup, because that is exactly what you have done using that option

He's shrinking the log, so no filegroups. I hope there's a second log file

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 5

guptaajay1985 (10/8/2009)
Hi Gilla Actually my servers are full of logs and in near future I have to make space on drive....

That didn't answer anything.

You're talking about transaction log files (.ldf)? How many log files does the DB have? How many data files? What sizes?

What recovery model? How often are you doing full backups? How often are you doing log backups?

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

I have searched it but I have not found any other log file.....

Hi silverfox ...But I have not found any other log file in whole computer...

Thanks

forsqlserver SSCoach Points: 18902 More actions · Answer 7

Dear Gila...

Thanx to write...

I know all but currently I was testing on a dummy server....

Thanks

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

Can you answer my questions and explain exactly what you're trying to do and why.

Please read through this as well - 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 9

Dear Gila,

Thanx for special attention..

one transaction log file

size 11.6 GB (before) now 1MB

recovery mode full

daily backup at 9:00 pm

Thanks

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

Not quite sure what you are trying to achieve here.

If you shrunk the log and specified emptyfile, not sure I understand the option. you normally use that option to move data across to a different file so the existing one can be dropped.

So am i right in assuming that this would move the existing log entries into the next existing log file, allowing you to delete the existing log file.:ermm:

More used to doing this with data files not log files ?

Edit:

Now I am a bit confused, just tested this out, just done a 10 million record insert with 2 log files, both log files grew as expected, 66% free on both log files when transaction finished. doing a dbcc shrinkfile with emptyfile on the 1st log file. the result was.

after the transaction finished

1st log file

1284MB 860.27 MB (66%) free

2nd log file

1744MB 1168.47 MB (66%) free

after the dbcc shrinkfile with emptyfile on the 1st log file

1st log file

860.00 MB 530.52 MB (61%) free

2nd log file

1744.00 MB 1075.84 MB (61%) free

So would I right in thinking that it has truncated the 1st log in effect and done nothing with the 2nd log file.

just curious really, as I wouldnt basically have more than 1 log file anyways

--------------------------------------------------------------------------------------
[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 11

Dear Silver,

I have no idea...I am also searching and waiting.....

Thanks

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

guptaajay1985 (10/8/2009)
one transaction log file
size 11.6 GB (before) now 1MB
recovery mode full
daily backup at 9:00 pm

So no log backups?

In full recovery you have to do log backups. If you don't, the log will grow til it fills the drive. Please read the article I referenced earlier.

I'm still trying to understand what you're trying to do and what your problem is. If there's only one log file you cannot empty it. It's needed for database operation. Furthermore, shrinking right down to 1 MB is stupid, the log will grow again and it's going to slow everything down while it does so.

Read that article! If you still have questions after, please ask.

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 13

Hi Gail, When you get a moment, can you help me with my understanding, based on my previous post.

If you have 2 log files, and do the emptyfile option as the op did. does it actually mark the 1st log file as inactive. as the test i done doesnt suggest that.

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.

--------------------------------------------------------------------------------------
[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 14

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.

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