Giving pain to DBA
-
Good to see u all again,
I suggest u all that better to post so good messages for me .It would be better to work on how MS-sql switch from one logfile to the other when we add one extra logfile to a particular database.
Why u all ask me the code . work on urself given u a good example.
from
sufian
-
They are asking for the code in an effort to help you. Perhaps in the way you've written the code there is a problem. You've looked over the code many times and you'd don't see anything wrong. But it's there. This happens a lot when we've written something. We go back over it but our minds trick us and we miss something. Someone else looks at the same thing and sees the problem right away. The only way to determine if this is the case with what you are doing is to look at the code. This isn't an unusual request in technical help forums.
A question for you: you said your logfile is very large. You've been asked whether all that data is being written in just 2 minutes or it's the case where you just have a really large logfile. When you do have a successful log file backup, how large is it?
K. Brian Kelley
@kbriankelley -
Mr Sufi
At the risk of increasing my post count once more.
You presented your problem, we gave a solution, you didn't like our solution so you came up with your own solution.
If you're not going to enlighten us poor, obviously inexperienced DBA's, by bestowing upon us the benefit of your immense wisdom, what is the point of us continuing "to work on how MS-sql switch from one logfile to the other" ??
--------------------
Colt 45 - the original point and click interface
-
welcome u all again,
I just read all ur answer but i dont care what u all wright for me because i hoped that u all will help me but sorry u cant.
but i will help u all but slowly.(like a slow poision)
in my perivious answer i mentioned DBCC Loginfo so again giving u all something to think.
When we detach the database and delete the logfile and again attach it sql server creates a new lofile for that database.
how and where that information is stored.
Secondly how sql server update that table with the new lofile information.
but yes during all this we have to restart the sql server services .why ?
from
sufian
Mr.Phillcart,
Yes i had given the question so that u all help me with ur presious knowldege .
But sorry i think u work on those things which are given with examples in BOL.Onething i will post the whole answer it is a bigning for u.
yes i asked"to work on how MS-sql switch from one logfile to the other"" because i want u to answer me.
because u are so knowledgeable and u must have drinked booksonline and with 1495 posts u might have.......
"2nd cluse to all of u .....
-
The information on the database files for a given database are stored in two places. In the master database, when the database is attached, you'll find the primary file stored in the filename column of sysdatabases. Within the database you'll find the information stored in sysfiles.
As for your use of DBCC LOGINFO(), that's not necessary to fix your problem. It's not documented for a reason: most of the time you don't need it.
This sounds to be your problem:
1) You need to back up the transaction log every 2 minutes.
2) The transaction log is very big.
3) The backup sometimes fails.
Is this correct?
K. Brian Kelley
@kbriankelley -
Sulian,
Just to be accurate I think you will find that I was the first to mention DBCC(Loginfo) not you, so you did not give us something to think about as you put it. As Brian says it's not documented as it is of little use to most users.
I no longer understand what I thought your original problem was so can you carefully describe it again, plus some details answering Brian's questions would certainly help.
Mike
-
I'm here for the fun only.....
Sufian, can you please answer Brian's question: "When you do have a successful log file backup, how large is it?"
How do you dump transactions of 200GB data in 2 minutes? Please explain.....what commands/tools do you use? disk/harware/mem??
per hour: 30 * 200G = 6000GB
per day: 6000GB * 24 = 144000GB
Imagine you have to recover data loss for one day, you'll be restoring transaction log of 144000GB considering your database is only 145GB.
Does that make any sense??
I think your transaction data is very small but you have created a very large physical logfile.
-
Good to see u all ,
Again u ask to repeat the question.So,
My question is how can we switch the default logfile with a new logfile created by the admin user for the particular database.??????????????????.
and also deleate the default logfile without stop and restart of sql services.
thanx u all for ur efforts.
also to those who mentioned that they are the first to tell me about dbcc loginfo.
I read dbcc loginfo information from a website.
3rd clue:
Pls look into the sysaltfiles table of master database ?
from
sufian
-
Oh goody, goody another clue ...
I must say this is the most interesting thread of the three ...
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=196243
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=92&messageid=196330
--------------------
Colt 45 - the original point and click interface -
Why do you need to switch the log file? Is the issue that your logfile is 200 GB and it should be smaller? If so there are better ways to handle this... for instance DBCC SHRINKFILE().
K. Brian Kelley
@kbriankelley -
Dear Bkelley,
The way i think is diffrent.But i like to answer ur question bkelley.
just for eaxmple.
if my logfile grows too much that only few space is left. a new logfile creates on diffrent server automatically(path already defined where to create the logfile) and i want that as this much space is left the logswitch occurs to that new defined logfile will be the default logfile for that database.
did u got what i want to say?.
from
sufian
I stopped looking on Mr.Phill posts.But thanx Mr.Phill for ur precious time.
-
OK - So we could monitor free log file space - there are perf counters that will let you see this, and then you could alter db to add new logfile on another spindle - but another SERVER as you stae - NO. Although there are trace flags that allow db files to be on UNC paths placing the log on a different server is not a good idea.
So you CAN ADD a logfile happily, you could then alter db to remove logfiles apart from the primary log file which you are stuck with.
But unable to see why this actually helps!
Mike
-
Phil, you are right, in fact it is the most exciting thread I have ever been on!!!!
I will be here all night if Sufian keeps on posting....
Sufian, can you please do me a favor...
In EM, can you right click on your database --> view-->taskpad and copy the space allocated section and post it here. I just want to know how much log in used.
-
1) Create a new logfile on another disk (I'm hoping that's what you meant... another poster has already indicated that putting database files on a network share isn't a good idea) using ALTER DATABASE ADD LOG FILE.
2) Issue DBCC SHRINKFILE() against the primary log file.
3) Set the max size on the primary log file to ensure it doesn't grow too large.
That's all you can do and keep the database up and running. There may be unsupported ways (such as directly modifying the system tables), but they are unsupported. The primary log file cannot be removed
K. Brian Kelley
@kbriankelley -
Guys!,
I was just wondering, why the hell do we have to go through this to help out a Jerk-Off, I repeat a total Jerk-Off too high on himself!
The Screw-Up doesn't have a single cell of respect for people trying to help him!
I think we all better just stop helping this @#$% anymore!!!
And yes Sufi, I am trying to increase my number of posts. And I will do it more often. What the hell you gonna do about it?!
SCUMBAG!!!
Viewing 15 posts - 61 through 75 (of 107 total)
You must be logged in to reply to this topic. Login to reply