Giving pain to DBA
-
Sorry all,
As i was busy in meeting i havent seen ur posts.
So, u all still working on .
Ok i had did the lof switch same as oracle but before that can any of u who had posted the post for me can tell me .
if i had 3 log files and now i want to know which log file is the active log file for the paticular database.
If u all can give me the answer then definately i will post how to switch log. else pls dont reply
--
Mr. Phili,
u asked me this (So what is the exact syntax you used for the shrink command?)
It is funny. Good carry with ur postes.I think ur are intrested in only inreasing the post .
pls try to do what i asked and u can do that i know but dont undestimate me.
Mr.Mike,
So u want to use a default log file in circular manner . Can u pls tell me how a single logfile can be used in circular manner.Pls tell me.
Mr.Gabor
I know that is really a very nice summary. but there is always some thing out of the box.
u know why i am intrested in this discussion because i thinked that i can be helped by many experienced sql users but i am really sad that everybody is giving me the same answer and no one has appriciated me that i wanted to do something diffrent which u all had not thinked or tried. some one said stupid question yes it seems stupid but this is a real challenge to u all .
if u might have tried then u will get it . there is nothing impossible.
I will give u a clue in 6.5 there was a table in which sql server saves the information about the active log so why it is not there in sql 2000. It is still there but hidden.
that tables contains the logid and if u know the syntex then u can see what database is using which transaction log . if u use that syntex with the database name it will show the active transactionlog indicating 2 in the transaction log active field.
what u think now i am wright or wrong. do i had posted wrong question.
hope u will find a diffrent way to do something diffrent from others.
i think i will get a good reply from u all .
from
sufian
-
Sulian,
I suggest you have a read arouind the logfile architecture in both BOL and Kalen's Inside SQl Server 2000.
The logfile is treated as a number of what are called "Virtual Log Files" How many get created depends on the initial creation size and what extensions have happened to it. As the log fills it sequentially fills one VLF, then the next and so on.
When you run Backup Log it will archive data from the log that does relate to currently open transactions, if a VLF is completelyu cleared then that VLF may be reused. Hence the file opertes in a circular fashion without you being aware of it. INGRES (last time I used it) used a similar concept except in that case the log was shared between databases, whereas with SQl Server there is one log (which may be split across multiple physical files) per db.
I guiess Phil was after the EXACT dbcc shrink you were using to try and understand the problem better - can't see anything funny in that, so why not tell him precisely (in T-SQL) what you were doing.
Giving people 'clues' about 6.5 is not likely to help, most of those sort of details have never been formally documented, so MS are at liberty to change implementation details as the product evolves. Since the entire storage engine was redesigned between 6.5 and 7, and then tweaked further at 2000 there is every chance that structures like you seem to be referring to have changed.
I guess you are looking for dbcc loginfo(dbname) to give you the sort of information you need about what is going on, but giving more details of what you did at 6.5 would help everyone understand your issues. Everyone is saying shrink, because that is indeed what it sounds like you need.
Mike
-
Mr. Phili,
u asked me this (So what is the exact syntax you used for the shrink command?)
It is funny. Good carry with ur postes.I think ur are intrested in only inreasing the post.
pls try to do what i asked and u can do that i know but dont undestimate me.
Mr Suli, Ok I won't undestimate you
now that is funny. 
Why would I underestimate someone that can't post a message in legible english and has to abbreviate words all the time.In a previous post you stated "i executed the command and i lost data for 2 mins", that is why I asked what is the exact syntax you used. In my maintenance routines I perform numerous shrinks quite regularly and have not lost any data yet. So I, and probably every sane SQL Server DBA, would be interested in the why and how you lost data. Is it a once off occurrence? Is it repeatable? Do we have to take other precautions to avoid the problem?
<sarcasm on>
I'm eagerly awaiting for you to enlighten us all with your obviously expansive knowledge.
<sarcasm off>
--------------------
Colt 45 - the original point and click interface
-
Hi Phill,
I am working on that only and ASAP i will finish that i will let u now . and i will personally send u .now dont reply this question pls stop ok.
I will give the answer what i want and i dont want that u suggest me to read BOI.
Pls i know how to use shrink command but i dont want to use shrink command.
and for ur kind information can u pls give me a brief discription about how sql server uses tlog in circular manner.
if a database had 3 logfiles.
from
sufian
-
Why should I stop posting here? Have you got something to hide from the rest of the forum members? Is your discovery so profound that no-one else is worthy of it. If I don't post here, how can I increase my post count.
The Books Online topic you want to read is titled Transaction Log Physical Architecture. It even has pictures that illustrate the process. If you can't read it in Books Online, then I can post it here verbatim, just to increase my post count.
--------------------
Colt 45 - the original point and click interface -
Yes please... I'll be able to increase mine too
.Especially since I never help around here
.My question stands... how the hell can a drive write 200gig of data in the log and on the drive in 2 minutes??? that's just beyond me...
-
Sulian,
A number of people have tried to help, but are now left hanging with a promise of your magic solution. Please tell!
Mike
-
The magic solution is to go back to Oracle where he's a God amongst insects and not the contrary
. Looks like we're not gonna hear his screams again... not a bad news. -
"I will give the answer what i want and i dont want that u suggest me to read BOI."
Just feel that I must mention this : It's BOL as in books online as the best information source on sql server (after this site of course). Now if you don't want to be reffered to bol but to a specific section I can understand, but stop dishing that tool as it's probabely the only thing that will save your arse after all the flaming you did here.
-
Man....I'm too late!!! I miss all the great stuff. Anyway....
Mohammad, what kind of DBA job is this? 200GB of log in 2mins??...you're kidding me!!!.
What kind of users do you have??? They must be real barbaric....hungered for data!
Why do you do shrink on busy hours?? Why can't you do shrink when database is not busy????
I've never experienced losing data using shrink before but have experienced transaction rollback.
-
Good,
Nice to see u all again.
Ok have a look on this again:
For a example to u all (on a test detabase)
Fieldid-filesize-------startoffset ------fseqno-----status---parity--createLSN
2 253952 8192 10191 0 128 0
2 253952 262144 10192 0 128 0
3 1245184 8192 10196 2 64 0
3 1245184 1253376 10195 0 64 0
3 1245184 2498560 10194 0 64 0
3 1499136 3743744 10193 0 64 0
4 1245184 8192 0 0 0 0
4 1245184 1253376 0 0 0 0
Ok can u all tell me what does this 2 indicate in the status column.
as by sql server when we create a database it creates a default logfile and if u execute dbcc loginfo it show the status 2 where fieldid=2
now u all tell how i changed it to 3 .
I dont understand why u all force to read books online everytime also tell me on which page of books online the
DBCC loginfo is described.Pls tell me.
I know u all will say that i had used this rebulid log command but pls explain how i used that command that still
i have the default logfile.
Mr.Andy
Really u are too late .
I am backingup Tlog every two mins.Is it possible or not.
Mr.Philip,
Carry on with ur post hope u will reach to the world record soon.
Remi,
That is really beyond to u .Yes not bad(but good to see).So good 3 posts for one answer carry on buddy.
Mr.Mike ,
Yes i am back on that . But love to see the frustation of u all.
from
sufian
-
Maybe if you could post the query you used to get these results, we could help more :
Select Left(O.Name, 20) as OName, Left(C.Name, 20) as CName, C.id, C.XType from dbo.SysColumns C inner join dbo.SysObjects O on C.id = O.id and O.XType = 'S' and C.name = 'Status' order by O.name
OName CName id XType
-------------------- -------------------- ----------- -----
syscolumns status 3 48
syscomments status 6 52
sysdepends status 12 52
sysfilegroups status 96 56
sysfiles status 95 56
sysfiles1 status 8 56
sysfulltextcatalogs status 19 52
sysindexes status 2 56
sysobjects status 1 56
systypes status 4 48
sysusers status 10 52
Now I know you don't want to hear it. But open up the books online and type the system table (in the index or search section) in which you found that status table and it "should" be explained clearly.
Also :
Mr.Philip,
Carry on with ur post hope u will reach to the world record soon.
No because I'll get there first
Remi,
That is really beyond to u (ya like it's not beyond you) .So good
34 posts for one answer carry on buddy. -
Sufian,
You cann't see any documentation about DBCC loginfo in the BOL because it is a so called "Undocumented" feature. It means It can be changed by MS without any notice.
On the other hand the number 2 in the status field means that that part of the log is actually active. So it is normal to see the number 2 is moving around.
In your case the actual active status is in the file number 3 at the beginning (Startoffset 8192) which means you could be able to shrink the log. If the active status would be on the 6th row (with startoffset 3743744) then you wouldn't be able to shrink the log.
In general you cann't find any "official" documentation about undocumented features but you can do a search in your favorite search engine.
As I've already written to you it doesn't really make sense to create several log files (in your case 3 files) because the log is written and read sequencially (ie. no paralell run is possible on log files). No performance gain can be obtained by having several files.
And yes, as a regular DBA job, you have to check and shrink your logs if you can. If you want still to use dbcc loginfo, then you can shrink the best the file whilt the active status is at the beginning.
As somebody in that thread has already mentioned, I also suggest to read Inside SQLServer from Kalen Delaney. I think this is one of the best book describing what is happening behind the scene. Even for me after 10 years of SQLServer experience (beside Sybase, DB2) it could tell me some new things but this book is definitly not for beginners which obviously you aren't.
I hope it could help
Bye
Gabor -
Hey Sufi, If anyone is working on increasing their post count it must be you. How many posts have you contirbuted to the community with no actual useful solution yet produced !!
And no Remi, you won't get there first. I've got some holidays coming up and I might take Mr Sufi's example and post a whole lot of useless blustering just to push my count past you all ...
--------------------
Colt 45 - the original point and click interface -
"And no Remi, you won't get there first. I've got some holidays coming up and I might take Mr Sufi's example and post a whole lot of useless blustering just to push my count past you all ...
"You can't post that much useless blustering :
Month Year EndCount StartCount Posts Avg/Day Mai 2004 3 0 3 0.10 Juin 2004 7 3 4 0.13 Juillet 2004 14 7 7 0.23 Août 2004 31 14 17 0.55 Septembre 2004 51 31 20 0.67 Octobre 2004 63 51 12 0.39 Novembre 2004 125 63 62 2.07 Décembre 2004 192 125 67 2.16 Janvier 2005 306 192 114 3.68 Février 2005 399 306 93 3.32 Mars 2005 653 399 254 8.19 Avril 2005 999 653 346 11.53 Mai 2005 1416 999 417 13.45 Juin 2005 3096 1416 1680 56.00 Juillet 2005 3435 3096 339 48.43
Viewing 15 posts - 31 through 45 (of 107 total)
You must be logged in to reply to this topic. Login to reply