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
>>
>
Showing posts with label timeout. Show all posts
Showing posts with label timeout. Show all posts
Tuesday, March 20, 2012
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:
>
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:
>
>
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:
>
>
Friday, February 24, 2012
autogrow of log file possibly cause timeout?
i had a problem with a server instance where an app processing a file and putting the data into the database using a stored procedure was timing out. the app is a service and in the case of errors, will try to process the file until it has been succesfully added data to the database. in the event viewer i keep seeing this message during this timeout period:
"Autogrow of file 'mydatabase_log' in database 'mydatabase' was cancelled by user or timed out after 15687 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size."
is there a possibility that this process was causing my SP to timeout? are there any other log or debug files that SQL Express creates that might be helpful in discovering what caused the timeout? currently the log is at... ouch 10gigs restricted... maybe trying to autogrow from this while restricted is hogging resources?
As SQL Server Express databases are limited to 4GB per database, I just wonder why you need 10GB of logs ? Did you consider backing up your database and shrink your logfiles ?HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||when we created the database, we used the default setting for the log file, which was "By 10 percent, restricted growth ". currently we backed up the database, detached the DB, removed the old log file, and reattached the DB. it created a new log file and we set it to a smaller size. as i understand it the log file holds the uncommitted transactions, is this true? if we want the log file to stay at a certain size maybe i need to uncheck AutoGrowth? and going back to the original question, if SQL was trying to autogrow a 10 gig file, could this have used all the resources at the moment and timeout any stored procedures running during this autogrowth process?|||
Hi nattylife,
for an instance assump your Log File size is 12 GB, you have set up Auto Growth option in 20% , now when ever your T-Log file need to grow it will grow 12 GB * 20 % means it will grow 2.4 GB in size and while this process is running it occupy the server resource (yes it will effect while this happens in pick time).
If you stop/uncheck/disabled Auto Grow , you may be in trouble because if your T-log need to grow and Auto Grow is disabled it might be resulted in SUSPECT status of your database, so don't disabled Auto Grow set it in appropriate % / MB as per your *requirement/perdiction* in growth of your database. BTW what is your Recovery Model of your database?
Refer T-Log architecture in BOL to understand more, BOL is your best friend.
Hemantgiri S. Goswami
|||Your way of removing the log file is dangerous and not best practise. You should take a backup of your database and shrink the log afterwards, rather than just *deleting* it and let it recreate by SQL Server.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Jens K. Suessmeyer wrote:
Your way of removing the log file is dangerous and not best practise. You should take a backup of your database and shrink the log afterwards, rather than just *deleting* it and let it recreate by SQL Server.
HTH, Jens K. Suessmeyer.http://www.sqlserver2005.de
we backed up the database before we do any questionable practices. the database is filled with 3 text files that our app recieves every day. inside each file are the records for the database. we archive these every day for 90 days in case we have any database issues. after doing some more research, i found how to shrink the log in the CTP which was the approach i was looking for, just didnt find until after the fact. i didnt realize that this log file would grow coninuously, i was under the assumption it would eventually get written over. so we are discussing a way to archive the ldf file now too.
|||Hi,
you should consider using the backup functions of SQL Server. You are able to do either full / differential or transaction log backups. A combination of those will help you to make your databases disaster-recoverable.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||i checked the backup options in the CTP. i see where i can do a log backup, but is this a manual process or when will it do it on a regular basis since i dont see any option to automate it. if i wanted to automate it, would i need to implement this with my own service or such?|||Hi,SQL Server Agent is not shipped with SQL Server Express. An approach could be to script out the backup command (using the functionality of the management studio) and schedule the execution of the script using an AT command (or any other scheduler) by executing the script with SQLCMD.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Sunday, February 19, 2012
AutoClose
How do you set the AutoClose property of a SQLExpress db? Also, can the user instance timeout property be set at the database level too to make for easier application distribution.
|||Acutally I think you can′t. But you have another option from the MSDN archives:
Is there any downside to turning off AutoClose in order to speed up the initial startup times after a period of inactivity.
I think the AutoClose property is true by default in SQL Server Express. You can look it up and set it in Management Studio Express / Databases / Database / Properties / Options.
I have a question too:
If AutoClose is true: Can then the database files be copied at any time for backup? Even when users are working with the database?
Or must all connections be closed before the database files can be copied for backup or deployment? Or must the database even be detached to be sure?
How does connection pooling has effects?
What must a developer do to be sure that the database files are in a copy able state?
I asked this a few days before, but i did not get an answer.
|||
Hi Markus,
"If AutoClose is true: Can then the database files be copied at any time for backup? Even when users are working with the database?"
No the database *could* be not consistent in the time of the copy, cause you would also need the log with possible pending transactions.
"Or must all connections be closed before the database files can be copied for backup or deployment? Or must the database even be detached to be sure? "
The safest way would be to detach the database for cpoying (or stopping the server service). You can′be sure that in the meantime of copying another user could open the database again and change something in there (unless you change the state of the database to single_user mode). ANother option for a *hot* backup (which can be done along with open connections is to use backup instead of the *cold* method trhe detaching)
"How does connection pooling has effects? "
Same for connection pooling.
"What must a developer do to be sure that the database files are in a copy able state?"
You could query the server for the database state, but you could never be sure that the database in this state during the whole copy work. The status can be retrieved using the SMO enumeration DatabaseStatus on the Database object. (un?less you change the state of the database to single_user mode)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||Hello Jens,
thank you very much for your answer. Ok, safest way is detach or use backup.
> No the database *could* be not consistent in the time of the
> copy, cause you would also need the log with possible
> pending transactions.
Excuse me that i have another question, but i like to understand it. When a power outage occurs during pendig transactions this is handled from Sql Server at next startup.
When an attach attaches the database in the state as it was during detach, why is this a problem then?
What is the inconsistency and why is this a problem for Sql Server? Or do you mean that there can be changes in the files during the copy and only a part of these changes are in the copied files?
Regards,
Markus
Commonly spoken this is right.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||Acutally I think you can′t. But you have another option from the MSDN archives:
"A system administrator on the parent instance can set the duration of the time-out period for a user instance by using sp_configure to change the user instance timeout option. The default is 60 minutes."
http://msdn2.microsoft.com/en-us/ms254504.aspx
HTH; Jens Suessmeyer.
http://www.sqlserver2005.de
|||
> Commonly spoken this is right.
Sorry, may i ask you to explain it to me more exactly? So that even i can understand it?
It is still so that i don't really understand why copy of the .mdf and .ldf file is not safe. It's only a feeling that it may be so.
Subscribe to:
Posts (Atom)