October 7, 2009 at 11:58 pm
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
October 8, 2009 at 2:21 am
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
October 8, 2009 at 3:08 am
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]
October 8, 2009 at 3:16 am
Thanks to write..
Hi Gilla Actually my servers are full of logs and in near future I have to make space on drive....
Thanks
October 8, 2009 at 3:16 am
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
October 8, 2009 at 3:19 am
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
October 8, 2009 at 3:19 am
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
October 8, 2009 at 3:21 am
Dear Gila...
Thanx to write...
I know all but currently I was testing on a dummy server....
Thanks
October 8, 2009 at 3:22 am
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
October 8, 2009 at 3:28 am
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
October 8, 2009 at 4:13 am
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]
October 8, 2009 at 5:05 am
Dear Silver,
I have no idea...I am also searching and waiting.....
Thanks
October 8, 2009 at 5:31 am
guptaajay1985 (10/8/2009)
one transaction log filesize 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
October 8, 2009 at 7:20 am
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]
October 8, 2009 at 7:49 am
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
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply