Automatic trigger on a specific time

Hi!

I use SQL Server Express 2005 and would like that a trigger is fired automatically at a specific time. I have been developing a game which has a lot of periods with a start date and an end date. Every time a periode is finished the trigger should add new records for the next periode. The dates of the periodes are already setted at the beginning of the game. For example:

periode startdate enddate
1 23.08.2006, 15:00 24.08.2006, 17:00
2 24.08 .2006, 17:00 25.08.2006, 08:00
and so on

Can anyone help me how to do this? Thanks!

You can create a maintenance job that runs every few hours or every few days and call a stored proc and put your logic in the stored proc.

|||

Hi ndinakar,

thx for your reply!

I know that jobs are an alternative but there are two problems: firstly it seems, that jobs are not available in SQL Server Express Version and secondly the trigger should actually fire on a specific time that could be every minute. So I would have to fire a job every minute but I think, that might not be a good idea.

|||I havent used Express editions. The other alternative is to create a Windows Service and schedule it to run every 1 minute. You could call a stored proc using OSQL. Activate the OSQL from your windows service. Might sound complicated but I cant think of any other option.|||Sounds like what you really want is a windows service like ndinakar mentioned.|||Thx ndinakar and Motley. I`ll try to use a windows service. That might be the best solution.sql

Automatic Recovery of SQL database?

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

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

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

Automatic Recovery of SQL database?

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

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

automate passing

hi!

i already have a program that when you click a button it will get the new transactions in the database then pass it to another database now i need to automate the passing... once there is a new entry in the database it should automatically pass it to the other database. how could i do this?!

thanks!

I believe there is a way to do such mirroring if the databases are both SQL Server databases and if that is the case you should post the same question to the appropriate SQL form...

Otherwise one could write a service application, an application with no GUI which runs as a windows service i.e not as a user, but the system, to check for the transfer condition, then do the read from the first database then write out to the second database. Here are a couple of links to get you started.
Windows Service Application|||

my other database is Oracle. But thanks for the links you gave! it was very helpful! ^_^

i now know how to create the windows service but how can i modify my previous program... originally the button has to be clicked for the transaction to be passed.. it also has an interface (a form with 2 buttons). Any tips or suggestions as to how i would change it?

thanks again!

|||You need to isolate the business logic away from the GUI logic. Its a common problem for people writing quick code...it all gets placed into one layer.

What you need to do is create a new assembly (library assembly) which will hold just the logic of contacting the database and returning or processing into it the data. Once that code is in a separate assembly, have the GUI application reference that assembly then remove the current db access code. Then wire in the GUI to use the new database/biz logic code from the newly created assembly.

Once you have it broken out you can now work on creating a windows service which will do the work in the background so to speak.

As a developer, get into the habit of always separating the code into layers. You may have heard of a three tiered architecture and this is what you are striving for... The first layer is a GUI or service or unit test layer which contacts a business layer which in turn contacts the database layer. Each of those layers are in separate assemblies which lend themselves to being unit tested outside the application via either NUnit tests or console applications linking in... Good luck.|||thanks for the explanation! ^__^

Automate Generate Sql Script?

Hi
I would like to autmate the Generate Sql Script.
I am running NUnit testing with one of my projects and i am resetting the
database frequently
Now, whenever i make any change to my database, i need to regenerate the Sql
Script for this to work.
This proves quite monotonous.
Is there a way to automate this process?
What i'm doing now:
1) Right click DB, click all tasks -> Generate Sql Script
2) Click Show All, then Script all objects
3) Click options, check all the Table Scripting Options
4) Click Ok
5) Navigate to where i would like the script saved (defaults to My
Documents every time, i don't want it there)
6) Save and click yes to replace the existing script
So, this gets quite repetitive and annoying at times.
Any suggestions (other then just editing the Sql Script myself)
TIA
Grant,
SQLDMO would be the obvious choice and Googling for it will show you several
links (eg http://www.dbazine.com/sql/sql-articles/larsen4).
If you want to use it directly, most objects have a Script method. If you
want a convenient wrapper around the SQLDMO code, there is an exe that you
could experiment with, eg this will script out the northwind database on
lon999:
"C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s lon999
/I /d northwind /f c:\masterscriptfile.txt
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||http://www.karaszi.com/SQLServer/inf...ate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Grant Merwitz" <grant@.workshare.com> wrote in message news:eedutdS4FHA.3460@.TK2MSFTNGP12.phx.gbl...
> Hi
> I would like to autmate the Generate Sql Script.
> I am running NUnit testing with one of my projects and i am resetting the database frequently
> Now, whenever i make any change to my database, i need to regenerate the Sql Script for this to
> work.
> This proves quite monotonous.
> Is there a way to automate this process?
> What i'm doing now:
> 1) Right click DB, click all tasks -> Generate Sql Script
> 2) Click Show All, then Script all objects
> 3) Click options, check all the Table Scripting Options
> 4) Click Ok
> 5) Navigate to where i would like the script saved (defaults to My Documents every time, i
> don't want it there)
> 6) Save and click yes to replace the existing script
> So, this gets quite repetitive and annoying at times.
> Any suggestions (other then just editing the Sql Script myself)
> TIA
>
|||Thanks Tibor and Paul for both your answers
I ended up using "scptxfr.exe" as it was so simple
Thanks Again
"Grant Merwitz" <grant@.workshare.com> wrote in message
news:eedutdS4FHA.3460@.TK2MSFTNGP12.phx.gbl...
> Hi
> I would like to autmate the Generate Sql Script.
> I am running NUnit testing with one of my projects and i am resetting the
> database frequently
> Now, whenever i make any change to my database, i need to regenerate the
> Sql Script for this to work.
> This proves quite monotonous.
> Is there a way to automate this process?
> What i'm doing now:
> 1) Right click DB, click all tasks -> Generate Sql Script
> 2) Click Show All, then Script all objects
> 3) Click options, check all the Table Scripting Options
> 4) Click Ok
> 5) Navigate to where i would like the script saved (defaults to My
> Documents every time, i don't want it there)
> 6) Save and click yes to replace the existing script
> So, this gets quite repetitive and annoying at times.
> Any suggestions (other then just editing the Sql Script myself)
> TIA
>

Automate Generate Sql Script?

Hi
I would like to autmate the Generate Sql Script.
I am running NUnit testing with one of my projects and i am resetting the
database frequently
Now, whenever i make any change to my database, i need to regenerate the Sql
Script for this to work.
This proves quite monotonous.
Is there a way to automate this process?
What i'm doing now:
1) Right click DB, click all tasks -> Generate Sql Script
2) Click Show All, then Script all objects
3) Click options, check all the Table Scripting Options
4) Click Ok
5) Navigate to where i would like the script saved (defaults to My
Documents every time, i don't want it there)
6) Save and click yes to replace the existing script
So, this gets quite repetitive and annoying at times.
Any suggestions (other then just editing the Sql Script myself)
TIAGrant,
SQLDMO would be the obvious choice and Googling for it will show you several
links (eg http://www.dbazine.com/sql/sql-articles/larsen4).
If you want to use it directly, most objects have a Script method. If you
want a convenient wrapper around the SQLDMO code, there is an exe that you
could experiment with, eg this will script out the northwind database on
lon999:
"C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s lon999
/I /d northwind /f c:\masterscriptfile.txt
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Grant Merwitz" <grant@.workshare.com> wrote in message news:eedutdS4FHA.3460@.TK2MSFTNGP12.ph
x.gbl...
> Hi
> I would like to autmate the Generate Sql Script.
> I am running NUnit testing with one of my projects and i am resetting the
database frequently
> Now, whenever i make any change to my database, i need to regenerate the S
ql Script for this to
> work.
> This proves quite monotonous.
> Is there a way to automate this process?
> What i'm doing now:
> 1) Right click DB, click all tasks -> Generate Sql Script
> 2) Click Show All, then Script all objects
> 3) Click options, check all the Table Scripting Options
> 4) Click Ok
> 5) Navigate to where i would like the script saved (defaults to My Docu
ments every time, i
> don't want it there)
> 6) Save and click yes to replace the existing script
> So, this gets quite repetitive and annoying at times.
> Any suggestions (other then just editing the Sql Script myself)
> TIA
>|||Thanks Tibor and Paul for both your answers
I ended up using "scptxfr.exe" as it was so simple
Thanks Again
"Grant Merwitz" <grant@.workshare.com> wrote in message
news:eedutdS4FHA.3460@.TK2MSFTNGP12.phx.gbl...
> Hi
> I would like to autmate the Generate Sql Script.
> I am running NUnit testing with one of my projects and i am resetting the
> database frequently
> Now, whenever i make any change to my database, i need to regenerate the
> Sql Script for this to work.
> This proves quite monotonous.
> Is there a way to automate this process?
> What i'm doing now:
> 1) Right click DB, click all tasks -> Generate Sql Script
> 2) Click Show All, then Script all objects
> 3) Click options, check all the Table Scripting Options
> 4) Click Ok
> 5) Navigate to where i would like the script saved (defaults to My
> Documents every time, i don't want it there)
> 6) Save and click yes to replace the existing script
> So, this gets quite repetitive and annoying at times.
> Any suggestions (other then just editing the Sql Script myself)
> TIA
>

Auto-Increasement field?

Hi!

I'm using Microsoft SQL Server Management Studio to design a table with two fields:

id (int)

file (text)

I set 'id' to be primary. I try to add a row to this table but it asks me for a custom value for 'id'. I want it simply to auto-assign a uniqe value for it. How to do this please?

In the table designer, set the Identity Specification to Is_Identity = Yes.

Also, I recommend NOT using [ID] as the column name. A good standard is to use the TableName and ID, so a table named MyTable would have it's IDENTITY column named MyTableID.

AutoGenerate

Hi
I have set a field "MessageId" as primary in a Messages table. What I want is that whenever user inserts a message through my site, the MsSql should automatically generate MessageId for the new message inserted, but this is not happening. Any suggestions, advice are highly appreciated. Thank YouIs the column set as an IDENTITY column? If not, that explains the problem. In Enterprise Manager, go into Design mode for the table, and make sure in the properties window, Identity is True (or Yes, do not recall which is used).|||You've got to create a table in SQL with something like the below. As long as there is input in the column named "Message" then the MessageID will automatically increase.

CREATE TABLE Message
(
MessageID int IDENTITY(1,1) PRIMARY KEY,
Messagevarchar (2000)NOT NULL
)

Good luck!
-Gabian-|||Thanks to both of you gentleman.

One more thing, what enum of SqlDbtype should i keep for my actual Message(thats being recorded by the user) : "text" or "varChar" ?|||What are your needs:

Varchar will allow a maximum of 8000 bytes
Text will allow very large values ~ 2gb

Varchar will give you much more flexibility for searching and manipulating data though and if it is sufficient would be my recomendation.|||Depends on the size...

I usually use varchar (^_^)

auto new line in same table

Hi

I have Table Like this In SQL server

Code Name 1001 Bank 1002 Cash

I need a TRIGGER
FOR INSERT, UPDATE, DELETE

that ADD a new Row's in the same table like this:

Code Name 8001 Bank 9001 Bank 8002 Cash 8002 Cash

In other mean for any row add to table by user that code start with 1xxx the trigger must inset to other row to that table whit code 8xxx & 9xxx.

with the same name.

thanks for your help.You could use an INSTEAD OF TRIGGER to do this. Code below should give you an idea of how to do this.

create trigger InsNewRows on tbl instead of insert
as
begin
insert into tbl (Code, Name)
select i.Code, i.Name from inserted as i
union all
select i.Code + n.Code, i.Name
from inserted as i
cross join (select 8000 union all select 9000) as n(Code)
where i.Code between 1000 and 1999
end|||Probably would want to use

where i.Code >= 1000 and cmpid <= 1999