HELP!!!! Transaction Log Automatically Shrinking
-
I dug this old script up which shows the status from sysdatabases,
select
name as [DB_NAME]
, isnull(case when status & 1 = 1 then 'autoclose' else null end + ',', '')
+ isnull(case when status & 4 = 4 then 'select into/bulkcopy' else null end + ',', '')
+ isnull(case when status & 8 = 8 then 'trunc. log on chkpt' else null end + ',', '')
+ isnull(case when status & 16 = 16 then 'torn page detection' else null end + ',', '')
+ isnull(case when status & 32 = 32 then 'loading' else null end + ',', '')
+ isnull(case when status & 64 = 64 then 'pre recovering' else null end + ',', '')
+ isnull(case when status & 128 = 128 then 'recovering' else null end + ',', '')
+ isnull(case when status & 256 = 256 then 'not recovered' else null end + ',', '')
+ isnull(case when status & 512 = 512 then 'offline' else null end + ',', '')
+ isnull(case when status & 1024 = 1024 then 'read only' else null end + ',', '')
+ isnull(case when status & 2048 = 2048 then 'dbo use only' else null end + ',', '')
+ isnull(case when status & 4096 = 4096 then 'single user' else null end + ',', '')
+ isnull(case when status & 32768 = 32768 then 'emergency mode' else null end + ',', '')
+ isnull(case when status & 4194304 = 4194304 then 'autoshrink' else null end + ',', '')
+ isnull(case when status & 1073741824 = 1073741824 then 'cleanly shutdown' else null end + ',', '')
from master..sysdatabases
--------------------
Colt 45 - the original point and click interface
-
When i run sqlperf in master database this is the output:
master 2.4921875 44.416145 0
tempdb 0.7421875 50.789474 0
model 0.9921875 41.190945 0
msdb 2.2421875 33.253483 0
pubs 0.7421875 51.710526 0
Northwind 0.9921875 45.767715 0
SSADB 3738.8047 1.0285394 0
Student_Info 0.7421875 49.342106 0
Data 4.875 8.2131414 0
Data_Replication 0.9921875 39.222443 0
Loans 0.9921875 39.616142 0
McSystem 3441.3047 0.80714643 0
*12 out of 15 database only appeared
When i run your script the output below appeared:
Data torn page detection,
Data_Replication torn page detection,
DBA autoclose,trunc. log on chkpt,torn page detection,autoshrink,cleanly shutdown,
HelpDesk autoclose,trunc. log on chkpt,torn page detection,autoshrink,cleanly shutdown,
Loans
master trunc. log on chkpt,torn page detection,
McSystem
model torn page detection,cleanly shutdown,
msdb trunc. log on chkpt,torn page detection,
Northwind select into/bulkcopy,trunc. log on chkpt,torn page detection,
pubs trunc. log on chkpt,torn page detection,
SSADB
Student_Info autoclose,trunc. log on chkpt,torn page detection,autoshrink,
tempdb trunc. log on chkpt,
Testing autoclose,trunc. log on chkpt,torn page detection,autoshrink,cleanly shutdown,
Any ideas??
Lester A. Policarpio
"-=Still Learning=-"
Lester Policarpio
-
Well that's pretty clear, the three databases missing from the sqlperf output are set to autoclose and they're shutdown.
You'll need to connect to those databases to "wake" them.
--------------------
Colt 45 - the original point and click interface -
Ohh im too noob hehehe thanks now i know why... another thing is there any effect on the database if I unchecked the autoclose checkbox??
"-=Still Learning=-"
Lester Policarpio
-
Only thing that will happen is that the database won't automatically close when there are no connections.
You might actually find that applications using those databases are more responsive when they first start.
--------------------
Colt 45 - the original point and click interface -
especialy on production boxes, you may want to avoid haven autoclose active !
This way sqlserver will keep the database open and you'll avoid the time consuming open action for your production applications.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Ahhh ok thanks Phil & ALZDBA
"-=Still Learning=-"
Lester Policarpio
-
@TOPIC
Guys any insights??? when i removed the truncate command (BACKUP LOG <db> WITH TRUNCATE_ONLY) the transaction log became stable then when i run the script again (thurs) it shows stability on friday but when i checked it again (monday/NOW) the transaction log automatically shrunk again what could be the problem all the things that other DBA said about the option in the database is OK but it shrunk again? plzz help me i do not know that irregularity is happening in that database thanks....
Lester A. Policarpio
"-=Still Learning=-"
Lester Policarpio
-
Have you run a trace at that time to see whats happening on the server?
--------------------
Colt 45 - the original point and click interface -
Ahhh that i guess i forgot or i mean do not know
sorry for being a noob im just a fresh graduate and only 2 1/2 months being a DBA can u help me coz i really do not know how to use that trace if it is ok to you thank you very muchLester A. Policarpio
"-=Still Learning=-"
Lester Policarpio
-
Can someone explain this I think I know the reason why my transaction log is shrinking... before i run the BACKUP LOG <dbname> WITH TRUNCATE_ONLY my log file have values like this : Total Size 3327.8, Log Used 3020.9, Free Log 306.9 when i run the script the values are changed : Total Size 2784.55, Log Used 51.25, Free Log 2733.3
Based on my understanding when we use Truncate Command it will only truncate the log used but not the total log size since it is the job of the shrink command to shrink the total size of the transaction log? Am I correct or not?? please help me
"-=Still Learning=-"
Lester Policarpio
-
Lester, One thing that's bugging me about this, if you're issuing BACKUP LOG WITH TRUNCATE_ONLY, why haven't you got the database in simple recovery mode? You're basically throwing away the transaction log anyway.
--------------------
Colt 45 - the original point and click interface -
So you mean by changing the database option to simple recovery mode I can save my transaction log? I thought simple recovery mode will nat save T-Logs?
"-=Still Learning=-"
Lester Policarpio
-
You're not saving your T-log now. The WITH TRUNCATE_ONLY is throwing it away.
If you want to save the transaction log then you should be backing it up to a file and never do a WITH TRUNCATE_ONLY.
--------------------
Colt 45 - the original point and click interface -
http://support.microsoft.com/kb/272318
--------------------
Colt 45 - the original point and click interface
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply