automatically set database to simple recovery upon creation

Is there a way to set the recovery model of any new databases created on SQL
Server 2005 to Simple instead of Full? I would like the recovery model to
default to Simple for any new databases created on the server.
Thanks for your help!
Recovery model is inherited from the model database. So you can set model to simple.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
news:4B63FB22-E1A0-4E8F-895E-75E25FD089AA@.microsoft.com...
> Is there a way to set the recovery model of any new databases created on SQL
> Server 2005 to Simple instead of Full? I would like the recovery model to
> default to Simple for any new databases created on the server.
> Thanks for your help!
|||awesome! Thanks!
"Tibor Karaszi" wrote:

> Recovery model is inherited from the model database. So you can set model to simple.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
> news:4B63FB22-E1A0-4E8F-895E-75E25FD089AA@.microsoft.com...
>

automatically set database to simple recovery upon creation

Is there a way to set the recovery model of any new databases created on SQL
Server 2005 to Simple instead of Full? I would like the recovery model to
default to Simple for any new databases created on the server.
Thanks for your help!Recovery model is inherited from the model database. So you can set model to simple.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
news:4B63FB22-E1A0-4E8F-895E-75E25FD089AA@.microsoft.com...
> Is there a way to set the recovery model of any new databases created on SQL
> Server 2005 to Simple instead of Full? I would like the recovery model to
> default to Simple for any new databases created on the server.
> Thanks for your help!|||awesome! Thanks!
"Tibor Karaszi" wrote:
> Recovery model is inherited from the model database. So you can set model to simple.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
> news:4B63FB22-E1A0-4E8F-895E-75E25FD089AA@.microsoft.com...
> > Is there a way to set the recovery model of any new databases created on SQL
> > Server 2005 to Simple instead of Full? I would like the recovery model to
> > default to Simple for any new databases created on the server.
> >
> > Thanks for your help!
>

automatically set database to simple recovery upon creation

Is there a way to set the recovery model of any new databases created on SQL
Server 2005 to Simple instead of Full? I would like the recovery model to
default to Simple for any new databases created on the server.
Thanks for your help!Recovery model is inherited from the model database. So you can set model to
simple.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
news:4B63FB22-E1A0-4E8F-895E-75E25FD089AA@.microsoft.com...
> Is there a way to set the recovery model of any new databases created on S
QL
> Server 2005 to Simple instead of Full? I would like the recovery model to
> default to Simple for any new databases created on the server.
> Thanks for your help!|||awesome! Thanks!
"Tibor Karaszi" wrote:

> Recovery model is inherited from the model database. So you can set model
to simple.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "mp3nomad" <mp3nomad@.discussions.microsoft.com> wrote in message
> news:4B63FB22-E1A0-4E8F-895E-75E25FD089AA@.microsoft.com...
>

automatic Restore Solution with 2 Server.. need help

hi @.ll
I looking for a solution like
I have a Server A with a Backup Job every 4 hour's and a second Server B to
restore/recovery the backup DB's.
Is there any way to run the recovery process automaticy every 4 hours depend
on the existing Backup on backup Server.
for examble
the Server A make a backup at 9am and the server B restore this backup at
9.30am,
the Server A make a backup at 11am and the Server B restore this backup at
11:30
I see only a combination from VBS and T-SQL to create a job with the right
file.. Is there any way to do.
thanks for any Idea or.. information
KlausAre the servers on the same network? Can they talk to each other?
You can create the appropriate jobs on each server. You could start a job
on the standby server like this
exec LinkedServerName.msdb.dbo.sp_start_job @.job_name ='Your_job_name_goes_here', @.server_name ='The_Name_Of_The_Standby_Server_Goes_Here'
Or you could just schedule the job on the standby server to execute on a
schedule that will work with the schedule at which the logs are backed up on
your primary server.
--
Keith
"Klaus" <Klaus.bilger@.C-S-L.BIZ> wrote in message
news:eD%23ybR6LFHA.3228@.TK2MSFTNGP12.phx.gbl...
> hi @.ll
> I looking for a solution like
> I have a Server A with a Backup Job every 4 hour's and a second Server B
to
> restore/recovery the backup DB's.
> Is there any way to run the recovery process automaticy every 4 hours
depend
> on the existing Backup on backup Server.
> for examble
> the Server A make a backup at 9am and the server B restore this backup at
> 9.30am,
> the Server A make a backup at 11am and the Server B restore this backup at
> 11:30
> I see only a combination from VBS and T-SQL to create a job with the right
> file.. Is there any way to do.
> thanks for any Idea or.. information
> Klaus
>sql

automatic Restore Solution with 2 Server.. need help

hi @.ll
I looking for a solution like
I have a Server A with a Backup Job every 4 hour's and a second Server B to
restore/recovery the backup DB's.
Is there any way to run the recovery process automaticy every 4 hours depend
on the existing Backup on backup Server.
for examble
the Server A make a backup at 9am and the server B restore this backup at
9.30am,
the Server A make a backup at 11am and the Server B restore this backup at
11:30
I see only a combination from VBS and T-SQL to create a job with the right
file.. Is there any way to do.
thanks for any Idea or.. information
Klaus
Are the servers on the same network? Can they talk to each other?
You can create the appropriate jobs on each server. You could start a job
on the standby server like this
exec LinkedServerName.msdb.dbo.sp_start_job @.job_name =
'Your_job_name_goes_here', @.server_name =
'The_Name_Of_The_Standby_Server_Goes_Here'
Or you could just schedule the job on the standby server to execute on a
schedule that will work with the schedule at which the logs are backed up on
your primary server.
Keith
"Klaus" <Klaus.bilger@.C-S-L.BIZ> wrote in message
news:eD%23ybR6LFHA.3228@.TK2MSFTNGP12.phx.gbl...
> hi @.ll
> I looking for a solution like
> I have a Server A with a Backup Job every 4 hour's and a second Server B
to
> restore/recovery the backup DB's.
> Is there any way to run the recovery process automaticy every 4 hours
depend
> on the existing Backup on backup Server.
> for examble
> the Server A make a backup at 9am and the server B restore this backup at
> 9.30am,
> the Server A make a backup at 11am and the Server B restore this backup at
> 11:30
> I see only a combination from VBS and T-SQL to create a job with the right
> file.. Is there any way to do.
> thanks for any Idea or.. information
> Klaus
>

automatic Restore Solution with 2 Server.. need help

hi @.ll
I looking for a solution like
I have a Server A with a Backup Job every 4 hour's and a second Server B to
restore/recovery the backup DB's.
Is there any way to run the recovery process automaticy every 4 hours depend
on the existing Backup on backup Server.
for examble
the Server A make a backup at 9am and the server B restore this backup at
9.30am,
the Server A make a backup at 11am and the Server B restore this backup at
11:30
I see only a combination from VBS and T-SQL to create a job with the right
file.. Is there any way to do.
thanks for any Idea or.. information
KlausAre the servers on the same network? Can they talk to each other?
You can create the appropriate jobs on each server. You could start a job
on the standby server like this
exec LinkedServerName.msdb.dbo.sp_start_job @.job_name =
'Your_job_name_goes_here', @.server_name =
'The_Name_Of_The_Standby_Server_Goes_Her
e'
Or you could just schedule the job on the standby server to execute on a
schedule that will work with the schedule at which the logs are backed up on
your primary server.
Keith
"Klaus" <Klaus.bilger@.C-S-L.BIZ> wrote in message
news:eD%23ybR6LFHA.3228@.TK2MSFTNGP12.phx.gbl...
> hi @.ll
> I looking for a solution like
> I have a Server A with a Backup Job every 4 hour's and a second Server B
to
> restore/recovery the backup DB's.
> Is there any way to run the recovery process automaticy every 4 hours
depend
> on the existing Backup on backup Server.
> for examble
> the Server A make a backup at 9am and the server B restore this backup at
> 9.30am,
> the Server A make a backup at 11am and the Server B restore this backup at
> 11:30
> I see only a combination from VBS and T-SQL to create a job with the right
> file.. Is there any way to do.
> thanks for any Idea or.. information
> Klaus
>

Automatic Recovery of SQL database?

Hi
I have problems writing to a SQL Server database called Event.
I get a lot of Timeout has expired.
In SQL Server Enterprise Manager, reading the log I can boserve the
following messages:
2005-02-26 15:48:03.79 spid11 Analysis of database 'Event' (7) is 41%
complete (approximately 6 more seconds)
2005-02-26 15:48:08.85 spid11 Analysis of database 'Event' (7) is 86%
complete (approximately 1 more seconds)
2005-02-26 15:48:09.06 spid11 Analysis of database 'Event' (7) is 100%
complete (approximately 0 more seconds)
2005-02-26 15:48:09.07 spid11 Recovery of database 'Event' (7) is 0%
complete (approximately 213 more seconds) (Phase 2 of 3).
2005-02-26 15:48:11.07 spid11 Recovery of database 'Event' (7) is 2%
complete (approximately 83 more seconds) (Phase 2 of 3).
2005-02-26 15:48:14.95 spid11 Recovery of database 'Event' (7) is 8%
complete (approximately 65 more seconds) (Phase 2 of 3).
2005-02-26 15:48:19.20 spid11 Recovery of database 'Event' (7) is 15%
complete (approximately 56 more seconds) (Phase 2 of 3).
2005-02-26 15:48:23.54 spid11 Recovery of database 'Event' (7) is 22%
complete (approximately 49 more seconds) (Phase 2 of 3).
2005-02-26 15:48:28.25 spid11 Recovery of database 'Event' (7) is 30%
complete (approximately 43 more seconds) (Phase 2 of 3).
2005-02-26 15:48:33.10 spid11 Recovery of database 'Event' (7) is 38%
complete (approximately 38 more seconds) (Phase 2 of 3).
2005-02-26 15:48:37.87 spid11 Recovery of database 'Event' (7) is 46%
complete (approximately 32 more seconds) (Phase 2 of 3).
2005-02-26 15:48:42.90 spid11 Recovery of database 'Event' (7) is 54%
complete (approximately 28 more seconds) (Phase 2 of 3).
2005-02-26 15:48:48.03 spid11 Recovery of database 'Event' (7) is 62%
complete (approximately 23 more seconds) (Phase 2 of 3).
2005-02-26 15:48:53.29 spid11 Recovery of database 'Event' (7) is 70%
complete (approximately 18 more seconds) (Phase 2 of 3).
2005-02-26 15:48:58.18 spid11 Recovery of database 'Event' (7) is 78%
complete (approximately 13 more seconds) (Phase 2 of 3).
2005-02-26 15:49:02.45 spid11 Recovery of database 'Event' (7) is 86%
complete (approximately 8 more seconds) (Phase 2 of 3).
2005-02-26 15:49:06.62 spid11 Recovery of database 'Event' (7) is 95%
complete (approximately 2 more seconds) (Phase 2 of 3).
2005-02-26 15:49:09.32 spid11 Recovery of database 'Event' (7) is 99%
complete (approximately 0 more seconds) (Phase 2 of 3).
2005-02-26 15:49:09.32 spid11 1108 transactions rolled forward in
database 'Event' (7).
2005-02-26 15:49:09.39 spid11 Recovery of database 'Event' (7) is 99%
complete (approximately 0 more seconds) (Phase 3 of 3).
2005-02-26 15:49:09.42 spid11 Recovery of database 'Event' (7) is 100%
complete (approximately 0 more seconds) (Phase 3 of 3).
2005-02-26 15:49:09.42 spid11 1 transactions rolled back in database
'Event' (7).
2005-02-26 15:49:09.42 spid11 Recovery is checkpointing database 'Event'
(7)
2005-02-26 15:49:09.70 spid3 Recovery complete.
2005-02-26 15:49:09.70 spid3 SQL global counter collection task is
created.
What does it mean? Is it something wrong with the database?
Also
2005-02-27 16:02:48.78 spid52 Error: 1204, Severity: 19, State: 1
2005-02-27 16:02:48.78 spid52 The SQL Server cannot obtain a LOCK
resource at this time. Rerun your statement when there are fewer active
What does this mean?
Does anyone now if it could be a serious problem with the database?
The database is 30 GB at the moment.
I appreciate comments and suggestions.
Kjell Arne JohansenMost probably you took down the server while there's a lot of activity on the machine, so SQL Server
had a lotto do when you startup SQL Server. Read more about it in Books Online "automatic recovery".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
news:39078C36-8B58-47A0-B920-8CEF2A031282@.microsoft.com...
> Hi
> I have problems writing to a SQL Server database called Event.
> I get a lot of Timeout has expired.
> In SQL Server Enterprise Manager, reading the log I can boserve the
> following messages:
> 2005-02-26 15:48:03.79 spid11 Analysis of database 'Event' (7) is 41%
> complete (approximately 6 more seconds)
> 2005-02-26 15:48:08.85 spid11 Analysis of database 'Event' (7) is 86%
> complete (approximately 1 more seconds)
> 2005-02-26 15:48:09.06 spid11 Analysis of database 'Event' (7) is 100%
> complete (approximately 0 more seconds)
> 2005-02-26 15:48:09.07 spid11 Recovery of database 'Event' (7) is 0%
> complete (approximately 213 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:11.07 spid11 Recovery of database 'Event' (7) is 2%
> complete (approximately 83 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:14.95 spid11 Recovery of database 'Event' (7) is 8%
> complete (approximately 65 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:19.20 spid11 Recovery of database 'Event' (7) is 15%
> complete (approximately 56 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:23.54 spid11 Recovery of database 'Event' (7) is 22%
> complete (approximately 49 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:28.25 spid11 Recovery of database 'Event' (7) is 30%
> complete (approximately 43 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:33.10 spid11 Recovery of database 'Event' (7) is 38%
> complete (approximately 38 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:37.87 spid11 Recovery of database 'Event' (7) is 46%
> complete (approximately 32 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:42.90 spid11 Recovery of database 'Event' (7) is 54%
> complete (approximately 28 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:48.03 spid11 Recovery of database 'Event' (7) is 62%
> complete (approximately 23 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:53.29 spid11 Recovery of database 'Event' (7) is 70%
> complete (approximately 18 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:58.18 spid11 Recovery of database 'Event' (7) is 78%
> complete (approximately 13 more seconds) (Phase 2 of 3).
> 2005-02-26 15:49:02.45 spid11 Recovery of database 'Event' (7) is 86%
> complete (approximately 8 more seconds) (Phase 2 of 3).
> 2005-02-26 15:49:06.62 spid11 Recovery of database 'Event' (7) is 95%
> complete (approximately 2 more seconds) (Phase 2 of 3).
> 2005-02-26 15:49:09.32 spid11 Recovery of database 'Event' (7) is 99%
> complete (approximately 0 more seconds) (Phase 2 of 3).
> 2005-02-26 15:49:09.32 spid11 1108 transactions rolled forward in
> database 'Event' (7).
> 2005-02-26 15:49:09.39 spid11 Recovery of database 'Event' (7) is 99%
> complete (approximately 0 more seconds) (Phase 3 of 3).
> 2005-02-26 15:49:09.42 spid11 Recovery of database 'Event' (7) is 100%
> complete (approximately 0 more seconds) (Phase 3 of 3).
> 2005-02-26 15:49:09.42 spid11 1 transactions rolled back in database
> 'Event' (7).
> 2005-02-26 15:49:09.42 spid11 Recovery is checkpointing database 'Event'
> (7)
> 2005-02-26 15:49:09.70 spid3 Recovery complete.
> 2005-02-26 15:49:09.70 spid3 SQL global counter collection task is
> created.
> What does it mean? Is it something wrong with the database?
> Also
> 2005-02-27 16:02:48.78 spid52 Error: 1204, Severity: 19, State: 1
> 2005-02-27 16:02:48.78 spid52 The SQL Server cannot obtain a LOCK
> resource at this time. Rerun your statement when there are fewer active
> What does this mean?
> Does anyone now if it could be a serious problem with the database?
> The database is 30 GB at the moment.
> I appreciate comments and suggestions.
> Kjell Arne Johansen
>|||Unless you've implemented a startup trace flag to block it, EVERY DATABASE
IS RECOVERED, automatically, UPON SQL SERVER STARTUP. That's part of the
ACID properties. If it didn't and you had a power outage as the cause of
the last reboot, all of your databases would be hosed. No, don't be
worried. Why you see this for this particular databases is because it is a
little larger than the others, which recover much more quickly.
Now, the last error indicates you may have too little memory on your server
for the activity that is been given it or you have throttled back SQL
Server's access to RAM.
Sincerely,
Anthony Thomas
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:39078C36-8B58-47A0-B920-8CEF2A031282@.microsoft.com...
Hi
I have problems writing to a SQL Server database called Event.
I get a lot of Timeout has expired.
In SQL Server Enterprise Manager, reading the log I can boserve the
following messages:
2005-02-26 15:48:03.79 spid11 Analysis of database 'Event' (7) is 41%
complete (approximately 6 more seconds)
2005-02-26 15:48:08.85 spid11 Analysis of database 'Event' (7) is 86%
complete (approximately 1 more seconds)
2005-02-26 15:48:09.06 spid11 Analysis of database 'Event' (7) is 100%
complete (approximately 0 more seconds)
2005-02-26 15:48:09.07 spid11 Recovery of database 'Event' (7) is 0%
complete (approximately 213 more seconds) (Phase 2 of 3).
2005-02-26 15:48:11.07 spid11 Recovery of database 'Event' (7) is 2%
complete (approximately 83 more seconds) (Phase 2 of 3).
2005-02-26 15:48:14.95 spid11 Recovery of database 'Event' (7) is 8%
complete (approximately 65 more seconds) (Phase 2 of 3).
2005-02-26 15:48:19.20 spid11 Recovery of database 'Event' (7) is 15%
complete (approximately 56 more seconds) (Phase 2 of 3).
2005-02-26 15:48:23.54 spid11 Recovery of database 'Event' (7) is 22%
complete (approximately 49 more seconds) (Phase 2 of 3).
2005-02-26 15:48:28.25 spid11 Recovery of database 'Event' (7) is 30%
complete (approximately 43 more seconds) (Phase 2 of 3).
2005-02-26 15:48:33.10 spid11 Recovery of database 'Event' (7) is 38%
complete (approximately 38 more seconds) (Phase 2 of 3).
2005-02-26 15:48:37.87 spid11 Recovery of database 'Event' (7) is 46%
complete (approximately 32 more seconds) (Phase 2 of 3).
2005-02-26 15:48:42.90 spid11 Recovery of database 'Event' (7) is 54%
complete (approximately 28 more seconds) (Phase 2 of 3).
2005-02-26 15:48:48.03 spid11 Recovery of database 'Event' (7) is 62%
complete (approximately 23 more seconds) (Phase 2 of 3).
2005-02-26 15:48:53.29 spid11 Recovery of database 'Event' (7) is 70%
complete (approximately 18 more seconds) (Phase 2 of 3).
2005-02-26 15:48:58.18 spid11 Recovery of database 'Event' (7) is 78%
complete (approximately 13 more seconds) (Phase 2 of 3).
2005-02-26 15:49:02.45 spid11 Recovery of database 'Event' (7) is 86%
complete (approximately 8 more seconds) (Phase 2 of 3).
2005-02-26 15:49:06.62 spid11 Recovery of database 'Event' (7) is 95%
complete (approximately 2 more seconds) (Phase 2 of 3).
2005-02-26 15:49:09.32 spid11 Recovery of database 'Event' (7) is 99%
complete (approximately 0 more seconds) (Phase 2 of 3).
2005-02-26 15:49:09.32 spid11 1108 transactions rolled forward in
database 'Event' (7).
2005-02-26 15:49:09.39 spid11 Recovery of database 'Event' (7) is 99%
complete (approximately 0 more seconds) (Phase 3 of 3).
2005-02-26 15:49:09.42 spid11 Recovery of database 'Event' (7) is 100%
complete (approximately 0 more seconds) (Phase 3 of 3).
2005-02-26 15:49:09.42 spid11 1 transactions rolled back in database
'Event' (7).
2005-02-26 15:49:09.42 spid11 Recovery is checkpointing database 'Event'
(7)
2005-02-26 15:49:09.70 spid3 Recovery complete.
2005-02-26 15:49:09.70 spid3 SQL global counter collection task is
created.
What does it mean? Is it something wrong with the database?
Also
2005-02-27 16:02:48.78 spid52 Error: 1204, Severity: 19, State: 1
2005-02-27 16:02:48.78 spid52 The SQL Server cannot obtain a LOCK
resource at this time. Rerun your statement when there are fewer active
What does this mean?
Does anyone now if it could be a serious problem with the database?
The database is 30 GB at the moment.
I appreciate comments and suggestions.
Kjell Arne Johansen|||Thank You.
Does it exist rules for how much memory that should be be available for SQL
Server?
This is an alarms and events database and at the moment there is a lot of
events from the system. About 2000 - 3000 records to the database each
minute with text, date and time. I will assume that each event is about 200
byte before making the SQL.
The SQL Server and database is on the same machine but the application
writing to the database is on another machine. (Process network and
Administrative network).
When the system is finished and tuned there will not be more than 50 events
-and far less alarms- each minute.
Regards
Kjell Arne Johansen
"Anthony Thomas" wrote:
> Unless you've implemented a startup trace flag to block it, EVERY DATABASE
> IS RECOVERED, automatically, UPON SQL SERVER STARTUP. That's part of the
> ACID properties. If it didn't and you had a power outage as the cause of
> the last reboot, all of your databases would be hosed. No, don't be
> worried. Why you see this for this particular databases is because it is a
> little larger than the others, which recover much more quickly.
> Now, the last error indicates you may have too little memory on your server
> for the activity that is been given it or you have throttled back SQL
> Server's access to RAM.
> Sincerely,
>
> Anthony Thomas
>|||Well it all depends on how much activity there is on your server, by default
SQL Server will use memory dynamically. That means that it will allocate and
deallocate memory on the fly based on the needs.
I would suggest that you monitor the memory by using performance monitor.
Start by looking at the following counters:
SQLServer:Buffer Manager Buffer cache hit ratio
SQLServer:Buffer Manager Free pages
SQLServer:Buffer Manager Page life expectancy
SQLServer:Buffer Manager Target pages
There is more information about how you can monitor the memory usage of your
SQL Server in the book called:
Microsoft SQL Server 2000
Performance Tuning
Technical Reference
ISBN: 0-7356-1270-6
Simon
This posting is provided "as is" with no warranties and confers no rights.
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:B3B453C9-7964-4DF4-A0FC-C61CF116F9FD@.microsoft.com...
> Thank You.
> Does it exist rules for how much memory that should be be available for
> SQL
> Server?
> This is an alarms and events database and at the moment there is a lot of
> events from the system. About 2000 - 3000 records to the database each
> minute with text, date and time. I will assume that each event is about
> 200
> byte before making the SQL.
> The SQL Server and database is on the same machine but the application
> writing to the database is on another machine. (Process network and
> Administrative network).
> When the system is finished and tuned there will not be more than 50
> events
> -and far less alarms- each minute.
> Regards
> Kjell Arne Johansen
> "Anthony Thomas" wrote:
>> Unless you've implemented a startup trace flag to block it, EVERY
>> DATABASE
>> IS RECOVERED, automatically, UPON SQL SERVER STARTUP. That's part of the
>> ACID properties. If it didn't and you had a power outage as the cause of
>> the last reboot, all of your databases would be hosed. No, don't be
>> worried. Why you see this for this particular databases is because it is
>> a
>> little larger than the others, which recover much more quickly.
>> Now, the last error indicates you may have too little memory on your
>> server
>> for the activity that is been given it or you have throttled back SQL
>> Server's access to RAM.
>> Sincerely,
>>
>> Anthony Thomas
>>
>

Automatic Recovery of SQL database?

Hi
I have problems writing to a SQL Server database called Event.
I get a lot of Timeout has expired.
In SQL Server Enterprise Manager, reading the log I can boserve the
following messages:
2005-02-26 15:48:03.79 spid11 Analysis of database 'Event' (7) is 41%
complete (approximately 6 more seconds)
2005-02-26 15:48:08.85 spid11 Analysis of database 'Event' (7) is 86%
complete (approximately 1 more seconds)
2005-02-26 15:48:09.06 spid11 Analysis of database 'Event' (7) is 100%
complete (approximately 0 more seconds)
2005-02-26 15:48:09.07 spid11 Recovery of database 'Event' (7) is 0%
complete (approximately 213 more seconds) (Phase 2 of 3).
2005-02-26 15:48:11.07 spid11 Recovery of database 'Event' (7) is 2%
complete (approximately 83 more seconds) (Phase 2 of 3).
2005-02-26 15:48:14.95 spid11 Recovery of database 'Event' (7) is 8%
complete (approximately 65 more seconds) (Phase 2 of 3).
2005-02-26 15:48:19.20 spid11 Recovery of database 'Event' (7) is 15%
complete (approximately 56 more seconds) (Phase 2 of 3).
2005-02-26 15:48:23.54 spid11 Recovery of database 'Event' (7) is 22%
complete (approximately 49 more seconds) (Phase 2 of 3).
2005-02-26 15:48:28.25 spid11 Recovery of database 'Event' (7) is 30%
complete (approximately 43 more seconds) (Phase 2 of 3).
2005-02-26 15:48:33.10 spid11 Recovery of database 'Event' (7) is 38%
complete (approximately 38 more seconds) (Phase 2 of 3).
2005-02-26 15:48:37.87 spid11 Recovery of database 'Event' (7) is 46%
complete (approximately 32 more seconds) (Phase 2 of 3).
2005-02-26 15:48:42.90 spid11 Recovery of database 'Event' (7) is 54%
complete (approximately 28 more seconds) (Phase 2 of 3).
2005-02-26 15:48:48.03 spid11 Recovery of database 'Event' (7) is 62%
complete (approximately 23 more seconds) (Phase 2 of 3).
2005-02-26 15:48:53.29 spid11 Recovery of database 'Event' (7) is 70%
complete (approximately 18 more seconds) (Phase 2 of 3).
2005-02-26 15:48:58.18 spid11 Recovery of database 'Event' (7) is 78%
complete (approximately 13 more seconds) (Phase 2 of 3).
2005-02-26 15:49:02.45 spid11 Recovery of database 'Event' (7) is 86%
complete (approximately 8 more seconds) (Phase 2 of 3).
2005-02-26 15:49:06.62 spid11 Recovery of database 'Event' (7) is 95%
complete (approximately 2 more seconds) (Phase 2 of 3).
2005-02-26 15:49:09.32 spid11 Recovery of database 'Event' (7) is 99%
complete (approximately 0 more seconds) (Phase 2 of 3).
2005-02-26 15:49:09.32 spid11 1108 transactions rolled forward in
database 'Event' (7).
2005-02-26 15:49:09.39 spid11 Recovery of database 'Event' (7) is 99%
complete (approximately 0 more seconds) (Phase 3 of 3).
2005-02-26 15:49:09.42 spid11 Recovery of database 'Event' (7) is 100%
complete (approximately 0 more seconds) (Phase 3 of 3).
2005-02-26 15:49:09.42 spid11 1 transactions rolled back in database
'Event' (7).
2005-02-26 15:49:09.42 spid11 Recovery is checkpointing database 'Event'
(7)
2005-02-26 15:49:09.70 spid3 Recovery complete.
2005-02-26 15:49:09.70 spid3 SQL global counter collection task is
created.
What does it mean? Is it something wrong with the database?
Also
2005-02-27 16:02:48.78 spid52 Error: 1204, Severity: 19, State: 1
2005-02-27 16:02:48.78 spid52 The SQL Server cannot obtain a LOCK
resource at this time. Rerun your statement when there are fewer active
What does this mean?
Does anyone now if it could be a serious problem with the database?
The database is 30 GB at the moment.
I appreciate comments and suggestions.
Kjell Arne Johansen
Most probably you took down the server while there's a lot of activity on the machine, so SQL Server
had a lotto do when you startup SQL Server. Read more about it in Books Online "automatic recovery".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
news:39078C36-8B58-47A0-B920-8CEF2A031282@.microsoft.com...
> Hi
> I have problems writing to a SQL Server database called Event.
> I get a lot of Timeout has expired.
> In SQL Server Enterprise Manager, reading the log I can boserve the
> following messages:
> 2005-02-26 15:48:03.79 spid11 Analysis of database 'Event' (7) is 41%
> complete (approximately 6 more seconds)
> 2005-02-26 15:48:08.85 spid11 Analysis of database 'Event' (7) is 86%
> complete (approximately 1 more seconds)
> 2005-02-26 15:48:09.06 spid11 Analysis of database 'Event' (7) is 100%
> complete (approximately 0 more seconds)
> 2005-02-26 15:48:09.07 spid11 Recovery of database 'Event' (7) is 0%
> complete (approximately 213 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:11.07 spid11 Recovery of database 'Event' (7) is 2%
> complete (approximately 83 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:14.95 spid11 Recovery of database 'Event' (7) is 8%
> complete (approximately 65 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:19.20 spid11 Recovery of database 'Event' (7) is 15%
> complete (approximately 56 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:23.54 spid11 Recovery of database 'Event' (7) is 22%
> complete (approximately 49 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:28.25 spid11 Recovery of database 'Event' (7) is 30%
> complete (approximately 43 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:33.10 spid11 Recovery of database 'Event' (7) is 38%
> complete (approximately 38 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:37.87 spid11 Recovery of database 'Event' (7) is 46%
> complete (approximately 32 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:42.90 spid11 Recovery of database 'Event' (7) is 54%
> complete (approximately 28 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:48.03 spid11 Recovery of database 'Event' (7) is 62%
> complete (approximately 23 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:53.29 spid11 Recovery of database 'Event' (7) is 70%
> complete (approximately 18 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:58.18 spid11 Recovery of database 'Event' (7) is 78%
> complete (approximately 13 more seconds) (Phase 2 of 3).
> 2005-02-26 15:49:02.45 spid11 Recovery of database 'Event' (7) is 86%
> complete (approximately 8 more seconds) (Phase 2 of 3).
> 2005-02-26 15:49:06.62 spid11 Recovery of database 'Event' (7) is 95%
> complete (approximately 2 more seconds) (Phase 2 of 3).
> 2005-02-26 15:49:09.32 spid11 Recovery of database 'Event' (7) is 99%
> complete (approximately 0 more seconds) (Phase 2 of 3).
> 2005-02-26 15:49:09.32 spid11 1108 transactions rolled forward in
> database 'Event' (7).
> 2005-02-26 15:49:09.39 spid11 Recovery of database 'Event' (7) is 99%
> complete (approximately 0 more seconds) (Phase 3 of 3).
> 2005-02-26 15:49:09.42 spid11 Recovery of database 'Event' (7) is 100%
> complete (approximately 0 more seconds) (Phase 3 of 3).
> 2005-02-26 15:49:09.42 spid11 1 transactions rolled back in database
> 'Event' (7).
> 2005-02-26 15:49:09.42 spid11 Recovery is checkpointing database 'Event'
> (7)
> 2005-02-26 15:49:09.70 spid3 Recovery complete.
> 2005-02-26 15:49:09.70 spid3 SQL global counter collection task is
> created.
> What does it mean? Is it something wrong with the database?
> Also
> 2005-02-27 16:02:48.78 spid52 Error: 1204, Severity: 19, State: 1
> 2005-02-27 16:02:48.78 spid52 The SQL Server cannot obtain a LOCK
> resource at this time. Rerun your statement when there are fewer active
> What does this mean?
> Does anyone now if it could be a serious problem with the database?
> The database is 30 GB at the moment.
> I appreciate comments and suggestions.
> Kjell Arne Johansen
>
|||Unless you've implemented a startup trace flag to block it, EVERY DATABASE
IS RECOVERED, automatically, UPON SQL SERVER STARTUP. That's part of the
ACID properties. If it didn't and you had a power outage as the cause of
the last reboot, all of your databases would be hosed. No, don't be
worried. Why you see this for this particular databases is because it is a
little larger than the others, which recover much more quickly.
Now, the last error indicates you may have too little memory on your server
for the activity that is been given it or you have throttled back SQL
Server's access to RAM.
Sincerely,
Anthony Thomas

"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:39078C36-8B58-47A0-B920-8CEF2A031282@.microsoft.com...
Hi
I have problems writing to a SQL Server database called Event.
I get a lot of Timeout has expired.
In SQL Server Enterprise Manager, reading the log I can boserve the
following messages:
2005-02-26 15:48:03.79 spid11 Analysis of database 'Event' (7) is 41%
complete (approximately 6 more seconds)
2005-02-26 15:48:08.85 spid11 Analysis of database 'Event' (7) is 86%
complete (approximately 1 more seconds)
2005-02-26 15:48:09.06 spid11 Analysis of database 'Event' (7) is 100%
complete (approximately 0 more seconds)
2005-02-26 15:48:09.07 spid11 Recovery of database 'Event' (7) is 0%
complete (approximately 213 more seconds) (Phase 2 of 3).
2005-02-26 15:48:11.07 spid11 Recovery of database 'Event' (7) is 2%
complete (approximately 83 more seconds) (Phase 2 of 3).
2005-02-26 15:48:14.95 spid11 Recovery of database 'Event' (7) is 8%
complete (approximately 65 more seconds) (Phase 2 of 3).
2005-02-26 15:48:19.20 spid11 Recovery of database 'Event' (7) is 15%
complete (approximately 56 more seconds) (Phase 2 of 3).
2005-02-26 15:48:23.54 spid11 Recovery of database 'Event' (7) is 22%
complete (approximately 49 more seconds) (Phase 2 of 3).
2005-02-26 15:48:28.25 spid11 Recovery of database 'Event' (7) is 30%
complete (approximately 43 more seconds) (Phase 2 of 3).
2005-02-26 15:48:33.10 spid11 Recovery of database 'Event' (7) is 38%
complete (approximately 38 more seconds) (Phase 2 of 3).
2005-02-26 15:48:37.87 spid11 Recovery of database 'Event' (7) is 46%
complete (approximately 32 more seconds) (Phase 2 of 3).
2005-02-26 15:48:42.90 spid11 Recovery of database 'Event' (7) is 54%
complete (approximately 28 more seconds) (Phase 2 of 3).
2005-02-26 15:48:48.03 spid11 Recovery of database 'Event' (7) is 62%
complete (approximately 23 more seconds) (Phase 2 of 3).
2005-02-26 15:48:53.29 spid11 Recovery of database 'Event' (7) is 70%
complete (approximately 18 more seconds) (Phase 2 of 3).
2005-02-26 15:48:58.18 spid11 Recovery of database 'Event' (7) is 78%
complete (approximately 13 more seconds) (Phase 2 of 3).
2005-02-26 15:49:02.45 spid11 Recovery of database 'Event' (7) is 86%
complete (approximately 8 more seconds) (Phase 2 of 3).
2005-02-26 15:49:06.62 spid11 Recovery of database 'Event' (7) is 95%
complete (approximately 2 more seconds) (Phase 2 of 3).
2005-02-26 15:49:09.32 spid11 Recovery of database 'Event' (7) is 99%
complete (approximately 0 more seconds) (Phase 2 of 3).
2005-02-26 15:49:09.32 spid11 1108 transactions rolled forward in
database 'Event' (7).
2005-02-26 15:49:09.39 spid11 Recovery of database 'Event' (7) is 99%
complete (approximately 0 more seconds) (Phase 3 of 3).
2005-02-26 15:49:09.42 spid11 Recovery of database 'Event' (7) is 100%
complete (approximately 0 more seconds) (Phase 3 of 3).
2005-02-26 15:49:09.42 spid11 1 transactions rolled back in database
'Event' (7).
2005-02-26 15:49:09.42 spid11 Recovery is checkpointing database 'Event'
(7)
2005-02-26 15:49:09.70 spid3 Recovery complete.
2005-02-26 15:49:09.70 spid3 SQL global counter collection task is
created.
What does it mean? Is it something wrong with the database?
Also
2005-02-27 16:02:48.78 spid52 Error: 1204, Severity: 19, State: 1
2005-02-27 16:02:48.78 spid52 The SQL Server cannot obtain a LOCK
resource at this time. Rerun your statement when there are fewer active
What does this mean?
Does anyone now if it could be a serious problem with the database?
The database is 30 GB at the moment.
I appreciate comments and suggestions.
Kjell Arne Johansen
|||Thank You.
Does it exist rules for how much memory that should be be available for SQL
Server?
This is an alarms and events database and at the moment there is a lot of
events from the system. About 2000 - 3000 records to the database each
minute with text, date and time. I will assume that each event is about 200
byte before making the SQL.
The SQL Server and database is on the same machine but the application
writing to the database is on another machine. (Process network and
Administrative network).
When the system is finished and tuned there will not be more than 50 events
-and far less alarms- each minute.
Regards
Kjell Arne Johansen
"Anthony Thomas" wrote:

> Unless you've implemented a startup trace flag to block it, EVERY DATABASE
> IS RECOVERED, automatically, UPON SQL SERVER STARTUP. That's part of the
> ACID properties. If it didn't and you had a power outage as the cause of
> the last reboot, all of your databases would be hosed. No, don't be
> worried. Why you see this for this particular databases is because it is a
> little larger than the others, which recover much more quickly.
> Now, the last error indicates you may have too little memory on your server
> for the activity that is been given it or you have throttled back SQL
> Server's access to RAM.
> Sincerely,
>
> Anthony Thomas
>
|||Well it all depends on how much activity there is on your server, by default
SQL Server will use memory dynamically. That means that it will allocate and
deallocate memory on the fly based on the needs.
I would suggest that you monitor the memory by using performance monitor.
Start by looking at the following counters:
SQLServer:Buffer Manager Buffer cache hit ratio
SQLServer:Buffer Manager Free pages
SQLServer:Buffer Manager Page life expectancy
SQLServer:Buffer Manager Target pages
There is more information about how you can monitor the memory usage of your
SQL Server in the book called:
Microsoft SQL Server 2000
Performance Tuning
Technical Reference
ISBN: 0-7356-1270-6
Simon
This posting is provided "as is" with no warranties and confers no rights.
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:B3B453C9-7964-4DF4-A0FC-C61CF116F9FD@.microsoft.com...
> Thank You.
> Does it exist rules for how much memory that should be be available for
> SQL
> Server?
> This is an alarms and events database and at the moment there is a lot of
> events from the system. About 2000 - 3000 records to the database each
> minute with text, date and time. I will assume that each event is about
> 200
> byte before making the SQL.
> The SQL Server and database is on the same machine but the application
> writing to the database is on another machine. (Process network and
> Administrative network).
> When the system is finished and tuned there will not be more than 50
> events
> -and far less alarms- each minute.
> Regards
> Kjell Arne Johansen
> "Anthony Thomas" wrote:
>

Automatic Recovery of SQL database?

Hi
I have problems writing to a SQL Server database called Event.
I get a lot of Timeout has expired.
In SQL Server Enterprise Manager, reading the log I can boserve the
following messages:
2005-02-26 15:48:03.79 spid11 Analysis of database 'Event' (7) is 41%
complete (approximately 6 more seconds)
2005-02-26 15:48:08.85 spid11 Analysis of database 'Event' (7) is 86%
complete (approximately 1 more seconds)
2005-02-26 15:48:09.06 spid11 Analysis of database 'Event' (7) is 100%
complete (approximately 0 more seconds)
2005-02-26 15:48:09.07 spid11 Recovery of database 'Event' (7) is 0%
complete (approximately 213 more seconds) (Phase 2 of 3).
2005-02-26 15:48:11.07 spid11 Recovery of database 'Event' (7) is 2%
complete (approximately 83 more seconds) (Phase 2 of 3).
2005-02-26 15:48:14.95 spid11 Recovery of database 'Event' (7) is 8%
complete (approximately 65 more seconds) (Phase 2 of 3).
2005-02-26 15:48:19.20 spid11 Recovery of database 'Event' (7) is 15%
complete (approximately 56 more seconds) (Phase 2 of 3).
2005-02-26 15:48:23.54 spid11 Recovery of database 'Event' (7) is 22%
complete (approximately 49 more seconds) (Phase 2 of 3).
2005-02-26 15:48:28.25 spid11 Recovery of database 'Event' (7) is 30%
complete (approximately 43 more seconds) (Phase 2 of 3).
2005-02-26 15:48:33.10 spid11 Recovery of database 'Event' (7) is 38%
complete (approximately 38 more seconds) (Phase 2 of 3).
2005-02-26 15:48:37.87 spid11 Recovery of database 'Event' (7) is 46%
complete (approximately 32 more seconds) (Phase 2 of 3).
2005-02-26 15:48:42.90 spid11 Recovery of database 'Event' (7) is 54%
complete (approximately 28 more seconds) (Phase 2 of 3).
2005-02-26 15:48:48.03 spid11 Recovery of database 'Event' (7) is 62%
complete (approximately 23 more seconds) (Phase 2 of 3).
2005-02-26 15:48:53.29 spid11 Recovery of database 'Event' (7) is 70%
complete (approximately 18 more seconds) (Phase 2 of 3).
2005-02-26 15:48:58.18 spid11 Recovery of database 'Event' (7) is 78%
complete (approximately 13 more seconds) (Phase 2 of 3).
2005-02-26 15:49:02.45 spid11 Recovery of database 'Event' (7) is 86%
complete (approximately 8 more seconds) (Phase 2 of 3).
2005-02-26 15:49:06.62 spid11 Recovery of database 'Event' (7) is 95%
complete (approximately 2 more seconds) (Phase 2 of 3).
2005-02-26 15:49:09.32 spid11 Recovery of database 'Event' (7) is 99%
complete (approximately 0 more seconds) (Phase 2 of 3).
2005-02-26 15:49:09.32 spid11 1108 transactions rolled forward in
database 'Event' (7).
2005-02-26 15:49:09.39 spid11 Recovery of database 'Event' (7) is 99%
complete (approximately 0 more seconds) (Phase 3 of 3).
2005-02-26 15:49:09.42 spid11 Recovery of database 'Event' (7) is 100%
complete (approximately 0 more seconds) (Phase 3 of 3).
2005-02-26 15:49:09.42 spid11 1 transactions rolled back in database
'Event' (7).
2005-02-26 15:49:09.42 spid11 Recovery is checkpointing database 'Event'
(7)
2005-02-26 15:49:09.70 spid3 Recovery complete.
2005-02-26 15:49:09.70 spid3 SQL global counter collection task is
created.
What does it mean? Is it something wrong with the database?
Also
2005-02-27 16:02:48.78 spid52 Error: 1204, Severity: 19, State: 1
2005-02-27 16:02:48.78 spid52 The SQL Server cannot obtain a LOCK
resource at this time. Rerun your statement when there are fewer active
What does this mean?
Does anyone now if it could be a serious problem with the database?
The database is 30 GB at the moment.
I appreciate comments and suggestions.
Kjell Arne JohansenMost probably you took down the server while there's a lot of activity on th
e machine, so SQL Server
had a lotto do when you startup SQL Server. Read more about it in Books Onli
ne "automatic recovery".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message
news:39078C36-8B58-47A0-B920-8CEF2A031282@.microsoft.com...
> Hi
> I have problems writing to a SQL Server database called Event.
> I get a lot of Timeout has expired.
> In SQL Server Enterprise Manager, reading the log I can boserve the
> following messages:
> 2005-02-26 15:48:03.79 spid11 Analysis of database 'Event' (7) is 41%
> complete (approximately 6 more seconds)
> 2005-02-26 15:48:08.85 spid11 Analysis of database 'Event' (7) is 86%
> complete (approximately 1 more seconds)
> 2005-02-26 15:48:09.06 spid11 Analysis of database 'Event' (7) is 100%
> complete (approximately 0 more seconds)
> 2005-02-26 15:48:09.07 spid11 Recovery of database 'Event' (7) is 0%
> complete (approximately 213 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:11.07 spid11 Recovery of database 'Event' (7) is 2%
> complete (approximately 83 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:14.95 spid11 Recovery of database 'Event' (7) is 8%
> complete (approximately 65 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:19.20 spid11 Recovery of database 'Event' (7) is 15%
> complete (approximately 56 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:23.54 spid11 Recovery of database 'Event' (7) is 22%
> complete (approximately 49 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:28.25 spid11 Recovery of database 'Event' (7) is 30%
> complete (approximately 43 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:33.10 spid11 Recovery of database 'Event' (7) is 38%
> complete (approximately 38 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:37.87 spid11 Recovery of database 'Event' (7) is 46%
> complete (approximately 32 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:42.90 spid11 Recovery of database 'Event' (7) is 54%
> complete (approximately 28 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:48.03 spid11 Recovery of database 'Event' (7) is 62%
> complete (approximately 23 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:53.29 spid11 Recovery of database 'Event' (7) is 70%
> complete (approximately 18 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:58.18 spid11 Recovery of database 'Event' (7) is 78%
> complete (approximately 13 more seconds) (Phase 2 of 3).
> 2005-02-26 15:49:02.45 spid11 Recovery of database 'Event' (7) is 86%
> complete (approximately 8 more seconds) (Phase 2 of 3).
> 2005-02-26 15:49:06.62 spid11 Recovery of database 'Event' (7) is 95%
> complete (approximately 2 more seconds) (Phase 2 of 3).
> 2005-02-26 15:49:09.32 spid11 Recovery of database 'Event' (7) is 99%
> complete (approximately 0 more seconds) (Phase 2 of 3).
> 2005-02-26 15:49:09.32 spid11 1108 transactions rolled forward in
> database 'Event' (7).
> 2005-02-26 15:49:09.39 spid11 Recovery of database 'Event' (7) is 99%
> complete (approximately 0 more seconds) (Phase 3 of 3).
> 2005-02-26 15:49:09.42 spid11 Recovery of database 'Event' (7) is 100%
> complete (approximately 0 more seconds) (Phase 3 of 3).
> 2005-02-26 15:49:09.42 spid11 1 transactions rolled back in database
> 'Event' (7).
> 2005-02-26 15:49:09.42 spid11 Recovery is checkpointing database 'Event
'
> (7)
> 2005-02-26 15:49:09.70 spid3 Recovery complete.
> 2005-02-26 15:49:09.70 spid3 SQL global counter collection task is
> created.
> What does it mean? Is it something wrong with the database?
> Also
> 2005-02-27 16:02:48.78 spid52 Error: 1204, Severity: 19, State: 1
> 2005-02-27 16:02:48.78 spid52 The SQL Server cannot obtain a LOCK
> resource at this time. Rerun your statement when there are fewer active
> What does this mean?
> Does anyone now if it could be a serious problem with the database?
> The database is 30 GB at the moment.
> I appreciate comments and suggestions.
> Kjell Arne Johansen
>|||Unless you've implemented a startup trace flag to block it, EVERY DATABASE
IS RECOVERED, automatically, UPON SQL SERVER STARTUP. That's part of the
ACID properties. If it didn't and you had a power outage as the cause of
the last reboot, all of your databases would be hosed. No, don't be
worried. Why you see this for this particular databases is because it is a
little larger than the others, which recover much more quickly.
Now, the last error indicates you may have too little memory on your server
for the activity that is been given it or you have throttled back SQL
Server's access to RAM.
Sincerely,
Anthony Thomas
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:39078C36-8B58-47A0-B920-8CEF2A031282@.microsoft.com...
Hi
I have problems writing to a SQL Server database called Event.
I get a lot of Timeout has expired.
In SQL Server Enterprise Manager, reading the log I can boserve the
following messages:
2005-02-26 15:48:03.79 spid11 Analysis of database 'Event' (7) is 41%
complete (approximately 6 more seconds)
2005-02-26 15:48:08.85 spid11 Analysis of database 'Event' (7) is 86%
complete (approximately 1 more seconds)
2005-02-26 15:48:09.06 spid11 Analysis of database 'Event' (7) is 100%
complete (approximately 0 more seconds)
2005-02-26 15:48:09.07 spid11 Recovery of database 'Event' (7) is 0%
complete (approximately 213 more seconds) (Phase 2 of 3).
2005-02-26 15:48:11.07 spid11 Recovery of database 'Event' (7) is 2%
complete (approximately 83 more seconds) (Phase 2 of 3).
2005-02-26 15:48:14.95 spid11 Recovery of database 'Event' (7) is 8%
complete (approximately 65 more seconds) (Phase 2 of 3).
2005-02-26 15:48:19.20 spid11 Recovery of database 'Event' (7) is 15%
complete (approximately 56 more seconds) (Phase 2 of 3).
2005-02-26 15:48:23.54 spid11 Recovery of database 'Event' (7) is 22%
complete (approximately 49 more seconds) (Phase 2 of 3).
2005-02-26 15:48:28.25 spid11 Recovery of database 'Event' (7) is 30%
complete (approximately 43 more seconds) (Phase 2 of 3).
2005-02-26 15:48:33.10 spid11 Recovery of database 'Event' (7) is 38%
complete (approximately 38 more seconds) (Phase 2 of 3).
2005-02-26 15:48:37.87 spid11 Recovery of database 'Event' (7) is 46%
complete (approximately 32 more seconds) (Phase 2 of 3).
2005-02-26 15:48:42.90 spid11 Recovery of database 'Event' (7) is 54%
complete (approximately 28 more seconds) (Phase 2 of 3).
2005-02-26 15:48:48.03 spid11 Recovery of database 'Event' (7) is 62%
complete (approximately 23 more seconds) (Phase 2 of 3).
2005-02-26 15:48:53.29 spid11 Recovery of database 'Event' (7) is 70%
complete (approximately 18 more seconds) (Phase 2 of 3).
2005-02-26 15:48:58.18 spid11 Recovery of database 'Event' (7) is 78%
complete (approximately 13 more seconds) (Phase 2 of 3).
2005-02-26 15:49:02.45 spid11 Recovery of database 'Event' (7) is 86%
complete (approximately 8 more seconds) (Phase 2 of 3).
2005-02-26 15:49:06.62 spid11 Recovery of database 'Event' (7) is 95%
complete (approximately 2 more seconds) (Phase 2 of 3).
2005-02-26 15:49:09.32 spid11 Recovery of database 'Event' (7) is 99%
complete (approximately 0 more seconds) (Phase 2 of 3).
2005-02-26 15:49:09.32 spid11 1108 transactions rolled forward in
database 'Event' (7).
2005-02-26 15:49:09.39 spid11 Recovery of database 'Event' (7) is 99%
complete (approximately 0 more seconds) (Phase 3 of 3).
2005-02-26 15:49:09.42 spid11 Recovery of database 'Event' (7) is 100%
complete (approximately 0 more seconds) (Phase 3 of 3).
2005-02-26 15:49:09.42 spid11 1 transactions rolled back in database
'Event' (7).
2005-02-26 15:49:09.42 spid11 Recovery is checkpointing database 'Event'
(7)
2005-02-26 15:49:09.70 spid3 Recovery complete.
2005-02-26 15:49:09.70 spid3 SQL global counter collection task is
created.
What does it mean? Is it something wrong with the database?
Also
2005-02-27 16:02:48.78 spid52 Error: 1204, Severity: 19, State: 1
2005-02-27 16:02:48.78 spid52 The SQL Server cannot obtain a LOCK
resource at this time. Rerun your statement when there are fewer active
What does this mean?
Does anyone now if it could be a serious problem with the database?
The database is 30 GB at the moment.
I appreciate comments and suggestions.
Kjell Arne Johansen|||Thank You.
Does it exist rules for how much memory that should be be available for SQL
Server?
This is an alarms and events database and at the moment there is a lot of
events from the system. About 2000 - 3000 records to the database each
minute with text, date and time. I will assume that each event is about 200
byte before making the SQL.
The SQL Server and database is on the same machine but the application
writing to the database is on another machine. (Process network and
Administrative network).
When the system is finished and tuned there will not be more than 50 events
-and far less alarms- each minute.
Regards
Kjell Arne Johansen
"Anthony Thomas" wrote:

> Unless you've implemented a startup trace flag to block it, EVERY DATABASE
> IS RECOVERED, automatically, UPON SQL SERVER STARTUP. That's part of the
> ACID properties. If it didn't and you had a power outage as the cause of
> the last reboot, all of your databases would be hosed. No, don't be
> worried. Why you see this for this particular databases is because it is
a
> little larger than the others, which recover much more quickly.
> Now, the last error indicates you may have too little memory on your serve
r
> for the activity that is been given it or you have throttled back SQL
> Server's access to RAM.
> Sincerely,
>
> Anthony Thomas
>|||Well it all depends on how much activity there is on your server, by default
SQL Server will use memory dynamically. That means that it will allocate and
deallocate memory on the fly based on the needs.
I would suggest that you monitor the memory by using performance monitor.
Start by looking at the following counters:
SQLServer:Buffer Manager Buffer cache hit ratio
SQLServer:Buffer Manager Free pages
SQLServer:Buffer Manager Page life expectancy
SQLServer:Buffer Manager Target pages
There is more information about how you can monitor the memory usage of your
SQL Server in the book called:
Microsoft SQL Server 2000
Performance Tuning
Technical Reference
ISBN: 0-7356-1270-6
Simon
This posting is provided "as is" with no warranties and confers no rights.
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:B3B453C9-7964-4DF4-A0FC-C61CF116F9FD@.microsoft.com...
> Thank You.
> Does it exist rules for how much memory that should be be available for
> SQL
> Server?
> This is an alarms and events database and at the moment there is a lot of
> events from the system. About 2000 - 3000 records to the database each
> minute with text, date and time. I will assume that each event is about
> 200
> byte before making the SQL.
> The SQL Server and database is on the same machine but the application
> writing to the database is on another machine. (Process network and
> Administrative network).
> When the system is finished and tuned there will not be more than 50
> events
> -and far less alarms- each minute.
> Regards
> Kjell Arne Johansen
> "Anthony Thomas" wrote:
>
>

Auto update Statistics Option

The developer of a finance application suggests us not to select the "Auto
Update Statistics" Option of a SQL Server 2000 database (In Full Recovery
Model). The reason he mentions is that it will affect the database
performance. However, from a number of articles, they suggest us to turn it
ON and it is the default setting as well.
The size of the database file is around 10GB and there are around 20
concurrent users (mainly retrieving information).
I would like to know does the Execution Plan is fixed for Stored Procedure
OR it changes from time to time (Just like running query according to the
data structure) ?
Your advice is sought.The Execution Plan for Stored Procedure may change from time to time. For
example, after 'Update Statistics' of related table(s), the SQL Server will
generate a new Execution Plan for the SP. Below is more information from BOL
(Execution Plan Caching and Reuse):
Recompiling Execution Plans
Certain changes in a database can cause an execution plan to be either
inefficient or invalid, given the new state of the database. SQL Server
detects the changes that invalidate an execution plan, and marks the plan as
invalid. A new plan must then be recompiled for the next connection that
executes the query. The conditions that cause a plan to be invalidated
include:
Any structural changes made to a table or view referenced by the query
(ALTER TABLE and ALTER VIEW).
New distribution statistics generated either explicitly from a statement
such as UPDATE STATISTICS or automatically.
Dropping an index used by the execution plan.
An explicit call to sp_recompile.
Large numbers of changes to keys (generated by INSERT or DELETE statements
from other users that modify a table referenced by the query).
For tables with triggers, if the number of rows in the inserted or deleted
tables grows significantly.
"Jason" wrote:
> The developer of a finance application suggests us not to select the "Auto
> Update Statistics" Option of a SQL Server 2000 database (In Full Recovery
> Model). The reason he mentions is that it will affect the database
> performance. However, from a number of articles, they suggest us to turn it
> ON and it is the default setting as well.
> The size of the database file is around 10GB and there are around 20
> concurrent users (mainly retrieving information).
> I would like to know does the Execution Plan is fixed for Stored Procedure
> OR it changes from time to time (Just like running query according to the
> data structure) ?
> Your advice is sought.

Auto update statistics and Auto create statistics options

Recovery Model of the Production Database is FULL.
We have activated the Auto update statistics and Auto
create statistics options. We would like to know
switching off these options, will the size of the
Transaction Log be smaller ?
ThanksNo, these doesn't affect the size of the transaction log. If you have
problem with the size of the transaction log, there are other places to
look. Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:9b5501c3eaa8$aae1dcc0$a401280a@.phx.gbl...
> Recovery Model of the Production Database is FULL.
> We have activated the Auto update statistics and Auto
> create statistics options. We would like to know
> switching off these options, will the size of the
> Transaction Log be smaller ?
> Thanks

Auto update statistics and Auto create statistics options

Recovery Model of the Production Database is FULL.
We have activated the Auto update statistics and Auto
create statistics options. We would like to know
switching off these options, will the size of the
Transaction Log be smaller ?
ThanksNo, these doesn't affect the size of the transaction log. If you have
problem with the size of the transaction log, there are other places to
look. Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Paul" <anonymous@.discussions.microsoft.com> wrote in message
news:9b5501c3eaa8$aae1dcc0$a401280a@.phx.gbl...

quote:

---

> Recovery Model of the Production Database is FULL.
> We have activated the Auto update statistics and Auto
> create statistics options. We would like to know
> switching off these options, will the size of the
> Transaction Log be smaller ?
> Thanks

---