Wierd going ons in my logs. mdf/ldf open and close

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Wierd going ons in my logs. mdf/ldf open and close

Author

Topic

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2005-12-01 : 10:49:47

Greetings from Tanzania,

Can anyone tell me why my current SQL Server Log is displaying the following information. ldf and mdf files are constantly opened and closed prior to the log back up.

Date	Source	Message
2005-12-01 14:50:02.37 	spid11	Closing file D:\SQL7\data\FMproperty.ldf. 
2005-12-01 14:50:02.35 	spid11	Closing file D:\SQL7\data\FMproperty.MDF. 
2005-12-01 14:50:02.29 	backup	Log backed up with following information: Database: FMProperty, creation date a
2005-12-01 14:50:02.01 	spid11	Opening file D:\SQL7\data\FMproperty.ldf. 
2005-12-01 14:50:02.00 	spid11	Opening file D:\SQL7\data\FMproperty.MDF. 
2005-12-01 14:50:02.00 	spid11	Starting up database 'FMProperty'.
2005-12-01 14:50:01.76 	spid11	Closing file d:\sql7\DATA\AmpHarrogate_log.ldf. 
2005-12-01 14:50:01.73 	spid11	Closing file d:\sql7\DATA\AmpHarrogate_Data.mdf. 
2005-12-01 14:50:01.67 	backup	Log backed up with following information: Database: AMPHarrogate, creation date
2005-12-01 14:50:01.34 	spid11	Opening file d:\sql7\DATA\AmpHarrogate_log.ldf. 
2005-12-01 14:50:01.34 	spid11	Opening file d:\sql7\DATA\AmpHarrogate_Data.mdf. 
2005-12-01 14:50:01.34 	spid11	Starting up database 'AMPHarrogate'.
2005-12-01 14:50:01.29 	spid11	Closing file D:\SQL7\data\FMproperty.ldf. 
2005-12-01 14:50:01.28 	spid11	Closing file D:\SQL7\data\FMproperty.MDF. 
2005-12-01 14:50:01.20 	spid11	Opening file D:\SQL7\data\FMproperty.ldf. 
2005-12-01 14:50:01.18 	spid11	Opening file D:\SQL7\data\FMproperty.MDF. 
2005-12-01 14:50:01.18 	spid11	Starting up database 'FMProperty'.
2005-12-01 14:50:01.15 	spid11	Closing file d:\sql7\DATA\AmpHarrogate_log.ldf. 
2005-12-01 14:50:01.12 	spid11	Closing file d:\sql7\DATA\AmpHarrogate_Data.mdf. 
2005-12-01 14:50:01.06 	spid11	Opening file d:\sql7\DATA\AmpHarrogate_log.ldf. 
2005-12-01 14:50:01.04 	spid11	Opening file d:\sql7\DATA\AmpHarrogate_Data.mdf.

This doesn't occur on the other servers.

Thanks,

Drew

Kristen
Test

22859 Posts

Posted - 2005-12-01 : 10:56:31

Looks like they are set to AUTOCLOSE. That's the default for an MSDE database, so if you are using that, or have RESTORED a Backup from a Database created on MSDE, that's probably the cause. If so performance will be DIRE!

You can turn it off using Enterprise Manager : Database properties : Options

Kristen

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2005-12-01 : 10:57:11

Is Auto-Close turned on for these databases?

exec sp_dboption AmpHarrogate, 'Auto-Close'
exec sp_dboption FMProperty, 'Auto-Close'

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2005-12-01 : 11:23:04

AutoClose huh?

"When set to true, the db is closed and resources freed up when the last user connection ends...it reopens automatically when a user tries to use the db again.

Well, I'll be. I've changed that now. ... I love you guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources