June 24, 2003 at 9:42 am
There are many messages in the error log for opening and closing the data file by different sources at very short time. Why is that?
e.g.
2003-06-23 17:24:30.47 spid7 Starting up database 'eBillingTest'.
2003-06-23 17:24:30.47 spid7 Opening file D:\MSSQL7\data\eBillingTest.mdf.
2003-06-23 18:00:01.38 spid10 Starting up database 'eBillingTest'.
2003-06-23 18:00:01.38 spid10 Opening file D:\MSSQL7\data\eBillingTest.mdf.
2003-06-23 18:00:01.38 spid10 Opening file D:\MSSQL7\data\eBillingTest_log.ldf.
2003-06-23 18:00:01.39 spid5 Closing file D:\MSSQL7\data\eBilling_Data.MDF.
2003-06-23 18:00:01.39 spid5 Closing file D:\MSSQL7\data\eBilling_Log.LDF.
2003-06-23 18:00:01.42 spid10 Closing file D:\MSSQL7\data\eBillingTest.mdf.
2003-06-23 18:00:01.42 spid10 Closing file D:\MSSQL7\data\eBillingTest_log.ldf.
2003-06-23 17:24:30.47 spid7 Opening file D:\MSSQL7\data\eBillingTest_log.ldf.
2003-06-23 17:24:30.55 spid7 Closing file D:\MSSQL7\data\eBillingTest.mdf.
2003-06-23 17:24:30.55 spid7 Closing file D:\MSSQL7\data\eBillingTest_log.ldf.
2003-06-23 17:24:30.61 spid7 Starting up database 'eBillingTest'.
2003-06-23 17:24:30.61 spid7 Opening file D:\MSSQL7\data\eBillingTest.mdf.
2003-06-23 17:24:30.61 spid7 Opening file D:\MSSQL7\data\eBillingTest_log.ldf.
2003-06-23 17:24:30.66 spid7 Closing file D:\MSSQL7\data\eBillingTest.mdf.
2003-06-23 17:24:30.66 spid7 Closing file D:\MSSQL7\data\eBillingTest_log.ldf.
2003-06-23 17:24:30.86 spid7 Closing file D:\MSSQL7\data\eBilling_Data.MDF.
2003-06-23 17:24:30.86 spid7 Closing file D:\MSSQL7\data\eBilling_Log.LDF.
June 24, 2003 at 9:47 am
Check the database properties. Probably set to auto close.
http://qa.sqlservercentral.com/columnists/sjones/autoclosefordatabases.asp
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 24, 2003 at 10:08 am
Steve,
Will the log size be increased if I uncheck the auto close option?
June 25, 2003 at 12:05 am
Hi tyang,
quote:
Will the log size be increased if I uncheck the auto close option?
autoclose just means, that this db will automatically close when the last user disconnects. Default is, that SQL Server keeps all db files open all the time. I don't think, this will affect log files, though it will affect performance
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 25, 2003 at 9:45 am
In my experience the effect should be a positive, opening and closing takes up
resources. Unless of course you don't have the resources to have all the databases open in which case time to upgrade that server.
quote:
Hi tyang,quote:
Will the log size be increased if I uncheck the auto close option?
autoclose just means, that this db will automatically close when the last user disconnects. Default is, that SQL Server keeps all db files open all the time. I don't think, this will affect log files, though it will affect performance
Cheers,
Frank
John Zacharkan
John Zacharkan
June 25, 2003 at 10:35 am
I unchecked 'auto close' option on the database property, it is SQL 7, but it is still showing me a lot messages on opening and closing the database files. Do I need to restart the service?
June 25, 2003 at 11:57 am
It will decrease the size of your errorlog since there will be fewer messages and it should improve performance a bit. As for the transaction log, I have no idea.
Henry
June 25, 2003 at 11:39 pm
Hi tyang,
quote:
I unchecked 'auto close' option on the database property, it is SQL 7, but it is still showing me a lot messages on opening and closing the database files. Do I need to restart the service?
I don't think so
Try something like this
SELECT LEFT(name,30) AS DB,
SUBSTRING(CASE status & 1 WHEN 0 THEN '' ELSE ',autoclose' END +
CASE status & 4 WHEN 0 THEN '' ELSE ',select into/bulk copy' END +
CASE status & 8 WHEN 0 THEN '' ELSE ',trunc. log on chkpt' END +
CASE status & 16 WHEN 0 THEN '' ELSE ',torn page detection' END +
CASE status & 32 WHEN 0 THEN '' ELSE ',loading' END +
CASE status & 64 WHEN 0 THEN '' ELSE ',pre-recovery' END +
CASE status & 128 WHEN 0 THEN '' ELSE ',recovering' END +
CASE status & 256 WHEN 0 THEN '' ELSE ',not recovered' END +
CASE status & 512 WHEN 0 THEN '' ELSE ',offline' END +
CASE status & 1024 WHEN 0 THEN '' ELSE ',read only' END +
CASE status & 2048 WHEN 0 THEN '' ELSE ',dbo USE only' END +
CASE status & 4096 WHEN 0 THEN '' ELSE ',single user' END +
CASE status & 32768 WHEN 0 THEN '' ELSE ',emergency mode' END +
CASE status & 4194304 WHEN 0 THEN '' ELSE ',autoshrink' END +
CASE status & 1073741824 WHEN 0 THEN '' ELSE ',cleanly shutdown' END +
CASE status2 & 16384 WHEN 0 THEN '' ELSE ',ANSI NULL default' END +
CASE status2 & 65536 WHEN 0 THEN '' ELSE ',concat NULL yields NULL' END +
CASE status2 & 131072 WHEN 0 THEN '' ELSE ',recursive triggers' END +
CASE status2 & 1048576 WHEN 0 THEN '' ELSE ',default TO local cursor' END +
CASE status2 & 8388608 WHEN 0 THEN '' ELSE ',quoted identifier' END +
CASE status2 & 33554432 WHEN 0 THEN '' ELSE ',cursor CLOSE on commit' END +
CASE status2 & 67108864 WHEN 0 THEN '' ELSE ',ANSI NULLs' END +
CASE status2 & 268435456 WHEN 0 THEN '' ELSE ',ANSI warnings' END +
CASE status2 & 536870912 WHEN 0 THEN '' ELSE ',full text enabled' END,
2,8000) AS Descr
FROM master..sysdatabases
maybe you have forgotten one db?
BTW, these entries are quite normal when SQL Server restarts.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply