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 writing. Show all posts
Showing posts with label writing. 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:
>
>
Monday, March 19, 2012
Automatic Query Generator
In my final project, the biggest time consuming issue was writing a query, when there are 15 fields on the average than it is certainly not easy to write an SQL statement and run it (through code)So I want to know, is there any automatic query generator ?that could work like:1) We enter all control names 1 by 12) we enter postfix text ( like .Trim(), .ToString() ) etc3) Result should be automatically generated queryIs there any software/program for that ?Infact it is very easy to develop myself, but I dont want to waste the time if such tool (most probably) already exists
A bit of advance. I figure your in school and by "final project" you mean your senior project or something along those lines. I recommend not using any "auto-generation" code tools until you are very familiar with hand writing all the code manually yourself during the learning stages. I too liked all of the visual tools while in school and used them when I had the chance (wizards, design view, etc.) Although it does make things quite easy, in the long run, it hurts no one but yourself. I've learned from experience. When changes are needed or additions, its a lot easier to go into the source of the code, or code behind to change it and not having to deal with the designer at all.
So now, I work about 95% from the source view and I stopped using the designer view. Since then I have really started to understand and learn A LOT more than the simple auto-generation or wizard methods. I mean, when there are time constraints and other variables involved, it could be benefical to use the easy methods, but if you really dont understand wants going on and the code in the back-end, any changes that are needed could take twice as long to figure out and change in the long-run. And most of the time there are always changes to a project, so understanding it key. Also, many of these auto-generated tools add a lot of "junk" in your code that you dont use and makes changes that much harder to understand.
Just a bit of advice, ive learned from doing it the "Wrong" way and then re-learning the stuff and doing it the "Right" way. Writing clean and understandable code is a must and best practice.
Anyone can write code, but if its not readable and maintainable to another person, its pretty much crap, in my eyes at least. Just keep this in mind while you develop.
Sorry if I got a little off the topic of your question. You might know a lot more than myself and are just looking for a tool to save yourself some time, but this is just a handy tip to consider, for I graduated a little over a year ago and matured a lot as a developer by trying to follow "best practices".
Subscribe to:
Posts (Atom)