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
MS Sql Server LazyDBA home page