Check the event and error logs to see if the recovery is completing after
restart. Should show you which db is taking so long, then back off or
truncate and shrink the log.
-----Original Message-----
From: Rick Memmer
[mailto:mssqldba-ezmlmshield-x83973673.[Email address protected]
Sent: Thursday, August 26, 2004 11:51 AM
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]
MS Sql Server LazyDBA home page