RE: SQL Server mem usage spiking after trans. log full...

RE: SQL Server mem usage spiking after trans. log full...

 

  

Yes, you were both right about the recovery in the error logs (I have much
to learn!!).

I couldn't get the log to shrink, but detaching the database, deleting the
log file, then reattaching the database worked (creating a new log file...
which is fine in this case).


Thanks for your help!!

Rick



-----Original Message-----
From: Kevin Martin
[mailto:mssqldba-ezmlmshield-x29921388.[Email address protected]
Sent: Thursday, August 26, 2004 2:02 PM
To: LazyDBA Discussion
Subject: RE: SQL Server mem usage spiking after trans. log full...

It is possible that the SQL Server is grabbing all of those resources while
it is in recovery mode. I'm not aware of anything you can do to stop that
process from occurring.
-km


-----Original Message-----
From: Rick Memmer [mailto:mssqldba-ezmlmshield-x83973673.[Email address
protected]
Sent: Thursday, August 26, 2004 12:51 PM
To: LazyDBA Discussion
Subject: SQL Server mem usage spiking after trans. log full...

I made the mistake of not putting a max on the transaction log, and when I
ran a process, the change could not be made due to the space running out
(among other things).

Anyway, the specifics of the change are not important. The question I have
is: why is the memory usage immediately climbing once SQL Services starts?
I am unable to get into any of the databases, and I cannot shrink the
transaction log (or detach the database) either.

I tried to kill all processes, but that has not helped. The last time I had
something like this happen, I had to end up deleting the transaction log and
doing a repair on the database, and while this worked... I don't want to
have to do that again.

What I am missing here? Surely there is something.


Any assistance would be greatly appreciated!


Rick





-----Original Message-----
From: Howard John
[mailto:mssqldba-ezmlmshield-x8615270.[Email address protected]
Sent: Wednesday, August 25, 2004 9:32 AM
To: LazyDBA Discussion
Subject: RE: Delete transaction Log[Scanned]

From my understanding and please feel free to correct me, the overhead for
shrinking a database comes from having autoshrink enabled. This runs dbcc's
on your database almost constantly. I understand that overhead. I however am
unfamiliar with the over head from shrinking the database.

If I am not mistaken, shrinking the database is basically going to get rid
of the empty space that is not going to be re-used. I myself came from pre
6.5 I still have files setup in a files space I control. So I do not run
into this problem. Which brings me to my next question, why should you need
to shrink the log file? If you have regular log backups you are droppuing
space off that file all the time and it should grow proportionally to your
database. Also in the case of a particularly large action as in a re-index
on a large database your log should expand to handle it then a logbackup
should bring it right back down.

Please explain more.

-----Original Message-----
From: Crosby Terry
[mailto:mssqldba-ezmlmshield-x95672711.[Email address protected]
Sent: Wednesday, August 25, 2004 9:23 AM
To: LazyDBA Discussion
Subject: RE: Delete transaction Log[Scanned]

Hugh is absolutely correct. The overhead involved with the shrink and expand
is not worth the effort. Shrinking the log to keep in manageable is the best
practice.

-----Original Message-----
From: Hugh du Toit
[mailto:mssqldba-ezmlmshield-x71526823.[Email address protected]
Sent: Wednesday, August 25, 2004 8:17 AM
To: LazyDBA Discussion
Subject: RE: Delete transaction Log[Scanned]


I was explained that by shrinking the .mdf file, you remove space created by
die SQL server with a purpose (for temp tables etc). It may not currently
use this space, but during operations it does. Be removing this space, you
create some overhead. And then when SQL needs this space again, it has to
recreate the space it had - adding the workload again...


-----Original Message-----
From: Howard John [mailto:mssqldba-ezmlmshield-x65832393.[Email
address
protected]
Sent: Wednesday, August 25, 2004 14:56 PM
To: LazyDBA Discussion
Subject: RE: Delete transaction Log[Scanned]

I was not aware that the DBCC SHRINKDATABASE statement was not a recommended
thing to do. Can you explain the reason for this?

-----Original Message-----
From: Hugh du Toit
[mailto:mssqldba-ezmlmshield-x36185699.[Email address protected]
Sent: Wednesday, August 25, 2004 8:53 AM
To: LazyDBA Discussion
Subject: RE: Delete transaction Log[Scanned]


Do not do the DBCC SHRINKDATABASE statement, cause this will shrink you
..mdf file and it is not the recommended thing to do. Just shrink your ..ldf
file with the DBCC SHRINKFILE command.

-----Original Message-----
From: Rick Memmer [mailto:mssqldba-ezmlmshield-x94982678.[Email
address protected]
Sent: Wednesday, August 25, 2004 14:43 PM
To: LazyDBA Discussion
Subject: RE: Delete transaction Log[Scanned]

Tanmaya,


If you search in Books Online under "shrinking transaction logs," you will
find the section below (as well as in-depth explanation). Hope that does it
for you. I had the same problem recently, and DBCC SHRINKFILE worked
perfectly.


Shrinking the Transaction Log
The size of the log files are physically reduced when:
* A DBCC SHRINKDATABASE statement is executed.
* A DBCC SHRINKFILE statement referencing a log file is executed.
* An autoshrink operation occurs.


-----Original Message-----
From: Tanmaya
[mailto:mssqldba-ezmlmshield-x31258804.[Email address protected]
Sent: Wednesday, August 25, 2004 12:28 AM
To: LazyDBA Discussion
Subject: Delete transaction Log


Hi All,
I have a database having mdf file size of 400MB with a ldf file size of
5GB.
Can you please guide me how to reinitialise the ldf file without disuturbing
the data.

Please help out.



Tanmaya Kumar Pattajoshi
Sr. Executive-IT
Vishay Components India Pvt. Ltd.
Pune
Ph:-91-20-26913285 Ext:5562

""For every problem there is a solution which is simple, clean and wrong."





---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]





---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]





---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]




---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]





---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]



---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.742 / Virus Database: 495 - Release Date: 8/19/2004


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.742 / Virus Database: 495 - Release Date: 8/19/2004



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]


MS Sql Server LazyDBA home page