Automatics restore script

I am setting up a job to automatically restore the standby database.
This is to test our tape backups. I need to passon the latest full
backup and log file names to the tape system to extract the files. How
do I get the file names? I would like to store these file names in temp
table and pass on to tape system one by one to extract it to local
disk. Do I need to query the msdb of primary or backup directory?
Please suggest sql to achieve this.
Thanks
Tram
If you do SQL Backups then you shouldn't need to know the file names because
the backup is referenced by database name. Are you doing file system
backups? That's not recommended unless you detach the database first.
Query the sysfiles system table for the physical file names.
David Portas
SQL Server MVP
|||A I need to restore from third party tape subsyste, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.
|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.
|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.
|||Hi
If it is 3rd party, ask the 3rd party vendor how you can script a restore
using their command line tools.
If SQL Server did not back the data up itself (to disk or a backup device
that it can control) it can't get the data back for you by itself.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"tram" <tram_e@.hotmail.com> wrote in message
news:1117887893.908844.130200@.f14g2000cwb.googlegr oups.com...
>A I need to restore from third party tape subsystem, the enterprise
> manager doen't recognize the restore fle anmes. I have to develop the
> script to retiriev the latest backup and logs therafter.
>
|||Thirty party vendor has command to resore the files. I have to passon
the filename as parameter, that is latest bak file and corresponding
trn files.
|||Hi
Have you looked at the backupfile and backupset tables in msdb to see if it
has what you need?
John
"tram" <tram_e@.hotmail.com> wrote in message
news:1117889003.683361.325430@.g43g2000cwa.googlegr oups.com...
> Thirty party vendor has command to resore the files. I have to passon
> the filename as parameter, that is latest bak file and corresponding
> trn files.
>

Automatics restore script

I am setting up a job to automatically restore the standby database.
This is to test our tape backups. I need to passon the latest full
backup and log file names to the tape system to extract the files. How
do I get the file names? I would like to store these file names in temp
table and pass on to tape system one by one to extract it to local
disk. Do I need to query the msdb of primary or backup directory?
Please suggest sql to achieve this.
Thanks
TramIf you do SQL Backups then you shouldn't need to know the file names because
the backup is referenced by database name. Are you doing file system
backups? That's not recommended unless you detach the database first.
Query the sysfiles system table for the physical file names.
David Portas
SQL Server MVP
--|||A I need to restore from third party tape subsyste, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||A I need to restore from third party tape subsystem, the enterprise
manager doen't recognize the restore fle anmes. I have to develop the
script to retiriev the latest backup and logs therafter.|||Hi
If it is 3rd party, ask the 3rd party vendor how you can script a restore
using their command line tools.
If SQL Server did not back the data up itself (to disk or a backup device
that it can control) it can't get the data back for you by itself.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"tram" <tram_e@.hotmail.com> wrote in message
news:1117887893.908844.130200@.f14g2000cwb.googlegroups.com...
>A I need to restore from third party tape subsystem, the enterprise
> manager doen't recognize the restore fle anmes. I have to develop the
> script to retiriev the latest backup and logs therafter.
>|||Thirty party vendor has command to resore the files. I have to passon
the filename as parameter, that is latest bak file and corresponding
trn files.|||Hi
Have you looked at the backupfile and backupset tables in msdb to see if it
has what you need?
John
"tram" <tram_e@.hotmail.com> wrote in message
news:1117889003.683361.325430@.g43g2000cwa.googlegroups.com...
> Thirty party vendor has command to resore the files. I have to passon
> the filename as parameter, that is latest bak file and corresponding
> trn files.
>

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:
>
>

Automatic Purge of Transaction Log Data?

Hello,

I have a database that I am setting up in SQL Server 2005. Initially, I am doing very large imports of data. Every time I run an import, I am having the increase the size of my transaction logs, and now they are approaching 2 GB. Should these be purging themselves? I have to keep increasing the max size of the log so that I can get my data in. While this will work for now, it is not a long term solution, because I can see the log size growing quite large and the amount of space on the server obviously isn't infinite. Is there a setting that I can change so they will automatically purge? If not, how do I purge this information myself?

Thanks so much!

Christine

The management of the transaction log depends on the recovery model used in your database. There is a good overview on http://msdn2.microsoft.com/en-us/library/ms189275.aspx about the available recovery models.

With a full recovery model, you will need to backup your transaction log to reclaim its space. With a simple recovery model, the system will do this for you, but there is a greater risk for data loss - be sure to make the right tradeoff for your database!

For the problem of large imports filling up the log space, you might also want to look into switching to bulk-logged recovery for the duration of the imports, although you might not need to do this if the one of the other models works for you after the log is under control (reclaimed by the system or by regular backups).

Another SQL BOL reference that will probably come in handy is Truncating the Transaction Log at http://msdn2.microsoft.com/en-us/library/ms189085.aspx, which explains a bit more about how log truncation works.

|||

Here are some good resources that will help you better understand and control the Transaction Log:

FileSize -How to stop the log file from growing
http://www.support.microsoft.com/?id=873235

FileSize -Log file filling up
http://www.support.microsoft.com/?id=110139

FileSize -Log File Grows too big
http://www.support.microsoft.com/?id=317375

FileSize -Log File issues
http://www.nigelrivett.net/TransactionLogFileGrows_1.html

FileSize -Shrinking Log in SQL Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=272318

And especially, this article about why cautions about reducing the Log file size is worth reading:

FileSize -DB Shrink Issues
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

|||Thank you very much Marcelo and Arnie. I appreciate all of the information you have provided. I will spend this afternoon and tomorrow morning looking into it! :0)

Automatic Log expansion

SQL Server 2000 SP4
On an underpowered machine (mostly an old RAID 5 controller/drives), hosting
Visnetic MailFlow we had a .LDF file that was 10 GB (already dealt with).
When the .LDF file filled, SQL Server did a 10% expansion and the entire
server locked up for several minutes.
Is it normal for the system to refuse connections when expanding the .LDF
file? My guess is yes, because it has nowhere to put log entries (though I
would have hoped it would do the expansion before it filled, thus allowing
continued use).
How about when the .MDF/.NDF is expanded?
Thanks,
JayA 10 gig TLog? Wow :)
Is not it too big mate? You have an issue which is more important than
"expanding" thing... You better take a look at the following documentation:
Optimizing Transaction Log Performance
http://msdn2.microsoft.com/en-us/library/aa178420(SQL.80).aspx
Transaction Logs
http://msdn2.microsoft.com/en-us/library/aa933065(SQL.80).aspx
Managing the Transaction Log (Applies to SQL Server 2005, however, it's
gonna give you precious ideas I believe)
http://msdn2.microsoft.com/en-us/library/ms345382.aspx
--
Ekrem Önsoy
"Jay" <nospan@.nospam.org> wrote in message
news:e7Y3Bb3BIHA.5980@.TK2MSFTNGP04.phx.gbl...
> SQL Server 2000 SP4
> On an underpowered machine (mostly an old RAID 5 controller/drives),
> hosting Visnetic MailFlow we had a .LDF file that was 10 GB (already dealt
> with). When the .LDF file filled, SQL Server did a 10% expansion and the
> entire server locked up for several minutes.
> Is it normal for the system to refuse connections when expanding the .LDF
> file? My guess is yes, because it has nowhere to put log entries (though I
> would have hoped it would do the expansion before it filled, thus allowing
> continued use).
> How about when the .MDF/.NDF is expanded?
> Thanks,
> Jay
>|||Sounds like you have Full recovery mode enabled and you are not backing up
your tlog. Common mistake.
You should size tlog appropriately for your workload, data size and backup
plan. It should NEVER grow automatically - you should control this if
needed during periods of minimal activity. Autogrowth should only fire if
something unexpected happens.
"Jay" <nospan@.nospam.org> wrote in message
news:e7Y3Bb3BIHA.5980@.TK2MSFTNGP04.phx.gbl...
> SQL Server 2000 SP4
> On an underpowered machine (mostly an old RAID 5 controller/drives),
> hosting Visnetic MailFlow we had a .LDF file that was 10 GB (already dealt
> with). When the .LDF file filled, SQL Server did a 10% expansion and the
> entire server locked up for several minutes.
> Is it normal for the system to refuse connections when expanding the .LDF
> file? My guess is yes, because it has nowhere to put log entries (though I
> would have hoped it would do the expansion before it filled, thus allowing
> continued use).
> How about when the .MDF/.NDF is expanded?
> Thanks,
> Jay
>|||Jay,
I wouldn't expect SQL Server to to refuse connections. But perhaps this was perceived so, because
the app tries to do some modification when it connects. Even the smallest modifications would
require a log records and if the log is full, well...
No, database files are not expended in advance, that is your job. ;-) This is one of the reasons why
we only use autogrow as a sort of backup plan (don't page me while I'm sleeping).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospan@.nospam.org> wrote in message news:e7Y3Bb3BIHA.5980@.TK2MSFTNGP04.phx.gbl...
> SQL Server 2000 SP4
> On an underpowered machine (mostly an old RAID 5 controller/drives), hosting Visnetic MailFlow we
> had a .LDF file that was 10 GB (already dealt with). When the .LDF file filled, SQL Server did a
> 10% expansion and the entire server locked up for several minutes.
> Is it normal for the system to refuse connections when expanding the .LDF file? My guess is yes,
> because it has nowhere to put log entries (though I would have hoped it would do the expansion
> before it filled, thus allowing continued use).
> How about when the .MDF/.NDF is expanded?
> Thanks,
> Jay
>|||That makes sense Tibor, thanks.
To everyone else, thanks for your replies, but I already dealt with the
super-sized file before the post, hence the statement "(already dealt
with)". The question was to verify why the server locked up during the
expansion. As to how it happened, I'm being handed production database
servers one-by-one and would have dealt with something as basic as a huge
logfie withing a day, if not hours, of getting a server.
Thanks,
Jay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:0E2F49FB-37F3-4497-9CB3-F8886A294B98@.microsoft.com...
> Jay,
> I wouldn't expect SQL Server to to refuse connections. But perhaps this
> was perceived so, because the app tries to do some modification when it
> connects. Even the smallest modifications would require a log records and
> if the log is full, well...
> No, database files are not expended in advance, that is your job. ;-) This
> is one of the reasons why we only use autogrow as a sort of backup plan
> (don't page me while I'm sleeping).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:e7Y3Bb3BIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> SQL Server 2000 SP4
>> On an underpowered machine (mostly an old RAID 5 controller/drives),
>> hosting Visnetic MailFlow we had a .LDF file that was 10 GB (already
>> dealt with). When the .LDF file filled, SQL Server did a 10% expansion
>> and the entire server locked up for several minutes.
>> Is it normal for the system to refuse connections when expanding the .LDF
>> file? My guess is yes, because it has nowhere to put log entries (though
>> I would have hoped it would do the expansion before it filled, thus
>> allowing continued use).
>> How about when the .MDF/.NDF is expanded?
>> Thanks,
>> Jay
>>
>

Automatic log decresing space used size in AdventureWorks

Hello all, I was making test with AdventureWorks about monitoring the log size and I run script:
-
/*
** This script is used to fill the Northwind transaction
*/

USE AdventureWorks

WHILE 1 = 1
BEGIN
UPDATE Production.Product
SET Name = Name
WAITFOR DELAY '000:00:00:999' --simulates a somewhat realistic OLTP environment
END-

I used a NEW AdventureWorks, just that I changed the recovery model to FULL RECOVERY MODEL.

By default Adventure Works use a log initial file size of 2 MB with automatic increment of 16MB.

The problem is that the log starts to increase his size 0.7 MB, 1.4 MB ... 1.8 MB and later automatically the log used size decrease to 0.5MB... and the process repeat again. Why is the log decreasing his space used size?, it should increase becouse is configurated whit automatic increment.

I support my test with performance monitor, my results are (please see picture):
http://www.compuaulas.com/performance2.gif

1. There is not log file growths, witch is strange becouse the log should growth!! and the log size didn't change his size.

2. The LOG SPACE USED changed. It's starts to growth but suddenly the space used decrease automatically (See Fuchsia line in the picture).

3. After a while, the log started to growth but instead still growing, TRUNCATIONS started to occur, and it's shouldn't be happening.

4. There is not file shrinking.

please help!!, thank you a lot.

Hi !

I found the same problem in SQL 2000. I used log truncations and log used space (kb) counters to see what was happening. I also used DBCC loginfo to see VLFs, but I still have no solution for that...

[]'s

Dennes

|||

Hi!

I found the answer.

The recovery model full is useless until we made a full backup. SQL Server knows that and keep using recovery model simple until that.

[]'s

Dennes

Automatic log decresing space used size in AdventureWorks

Hello all, I was making test with AdventureWorks about monitoring the log size and I run script:
-
/*
** This script is used to fill the Northwind transaction
*/

USE AdventureWorks

WHILE 1 = 1
BEGIN
UPDATE Production.Product
SET Name = Name
WAITFOR DELAY '000:00:00:999' --simulates a somewhat realistic OLTP environment
END-

I used a NEW AdventureWorks, just that I changed the recovery model to FULL RECOVERY MODEL.

By default Adventure Works use a log initial file size of 2 MB with automatic increment of 16MB.

The problem is that the log starts to increase his size 0.7 MB, 1.4 MB ... 1.8 MB and later automatically the log used size decrease to 0.5MB... and the process repeat again. Why is the log decreasing his space used size?, it should increase becouse is configurated whit automatic increment.

I support my test with performance monitor, my results are (please see picture):
http://www.compuaulas.com/performance2.gif

1. There is not log file growths, witch is strange becouse the log should growth!! and the log size didn't change his size.

2. The LOG SPACE USED changed. It's starts to growth but suddenly the space used decrease automatically (See Fuchsia line in the picture).

3. After a while, the log started to growth but instead still growing, TRUNCATIONS started to occur, and it's shouldn't be happening.

4. There is not file shrinking.

please help!!, thank you a lot.

Hi !

I found the same problem in SQL 2000. I used log truncations and log used space (kb) counters to see what was happening. I also used DBCC loginfo to see VLFs, but I still have no solution for that...

[]'s

Dennes

|||

Hi!

I found the answer.

The recovery model full is useless until we made a full backup. SQL Server knows that and keep using recovery model simple until that.

[]'s

Dennes

Automatic Creation of LDF file

I was wondering how the automatic creation of a LDF (log)
file worked in MSSQL Server 2000. Is there a special
utility to use? I only have an MDF file and I try
to "Attach" it as a database. I get the following error
message:
--
Microsoft SQL-DMO (ODBC SQLState: 42000)
--
Error 1813: Could not open new database 'DBName'. CREATE
DATABASE is aborted.
Device activation error. The physical file name 'C:\SQL
Data\DBName_log.ldf' may be incorrect.
--
OK
--
It's looking for the ldf file and it won't create it. How
do I get it to create a new log file? I tried creating a
new blank database with the same name and using it's ldf,
but somehow it knows they're not from the same database.
ThanksDanny,
May be there were some open transactions and its searching the ldf file for
matching.Was this database properly detached before?A prerequisite for
sp_attach* is that the database should be properly detached using
sp_detach_db.Either you can RESTORE from a valid and latest database backup
or try the workaround mentioned in :
Restoring databases when only data file available
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
The archived newsgroup thread
http://tinyurl.com/m071
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Danny Forbes" <dforbes@.salessimplicity.net> wrote in message
news:105901c3724a$14f89e50$a601280a@.phx.gbl...
> I was wondering how the automatic creation of a LDF (log)
> file worked in MSSQL Server 2000. Is there a special
> utility to use? I only have an MDF file and I try
> to "Attach" it as a database. I get the following error
> message:
> --
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> --
> Error 1813: Could not open new database 'DBName'. CREATE
> DATABASE is aborted.
> Device activation error. The physical file name 'C:\SQL
> Data\DBName_log.ldf' may be incorrect.
> --
> OK
> --
> It's looking for the ldf file and it won't create it. How
> do I get it to create a new log file? I tried creating a
> new blank database with the same name and using it's ldf,
> but somehow it knows they're not from the same database.
> Thanks|||Hi,
- Change your DB Status to Emergency Mode (32768).
- Stop SQL Server / Start SQL
- In query analyser, execute dbcc rebuild_log. REMEMBER: Rename old File Log
before.
- Change Your DB Status to Normal (0)
- Stop SQL Server / Start SQL
This error is because your GUID of file Log not match with definition of
your DB.
Regards,
Fabiano Maciel
"Danny Forbes" <dforbes@.salessimplicity.net> wrote in message
news:105901c3724a$14f89e50$a601280a@.phx.gbl...
> I was wondering how the automatic creation of a LDF (log)
> file worked in MSSQL Server 2000. Is there a special
> utility to use? I only have an MDF file and I try
> to "Attach" it as a database. I get the following error
> message:
> --
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> --
> Error 1813: Could not open new database 'DBName'. CREATE
> DATABASE is aborted.
> Device activation error. The physical file name 'C:\SQL
> Data\DBName_log.ldf' may be incorrect.
> --
> OK
> --
> It's looking for the ldf file and it won't create it. How
> do I get it to create a new log file? I tried creating a
> new blank database with the same name and using it's ldf,
> but somehow it knows they're not from the same database.
> Thanks

Automatic Creation and Modification log

Hi there,

I need some help in order to reduce the amount of code needed to
implement the logging of INSERT and UPDATE in my application's
database.

In every table of my database I have four fields (CreatedBy,
CreatedTime, ModifiedBy, ModificatedTime), these fields allow me to
trace the users who create and modify the registers in my application.
I know that I can add in every stored procedure a piece of code who
uses the app user as a parameter, and insert these information in the
register. But I would like to go beyond these method. I imagine there
is a better way of doing it, and I don't like to use triggers. Is
there any method, within the SQLServer functionality, that allows me
to automate this process?

So, any help would be very appreciated!!
Thank you very much in advance!Phino wrote:
> I imagine there is a better way of doing it, and I don't like to use
> triggers. Is
> there any method, within the SQLServer functionality, that allows me
> to automate this process?

Yeah, triggers.

--
Steve Troxell

Automatic Backups

Hi:
Somebody knows how I can program a stored procedure for automatic backup to
disk in SQL Server 2005 checking the transaction log?
Thanks in advance
Ale :)In SQL Server Management Studio, Go to Management, Maintenance Plans, right
click the node and select New. Choose the "Back Up Database Task" option from
the toolbox. Configure the backup wizard as needed.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"Ale" wrote:
> Hi:
> Somebody knows how I can program a stored procedure for automatic backup to
> disk in SQL Server 2005 checking the transaction log?
> Thanks in advance
> Ale :)|||ok, I checked this but I have one doubt: how can I check my log? if I want
that my backup init when my log is at 80 %, how can I send an automatic form
the backup process to work? it exists a manner for programming this in SQL
Server 2005? (like a cron in UNIX)
Thanks again
Ale
"AndyP" wrote:
> In SQL Server Management Studio, Go to Management, Maintenance Plans, right
> click the node and select New. Choose the "Back Up Database Task" option from
> the toolbox. Configure the backup wizard as needed.
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "Ale" wrote:
> > Hi:
> > Somebody knows how I can program a stored procedure for automatic backup to
> > disk in SQL Server 2005 checking the transaction log?
> > Thanks in advance
> > Ale :)|||You can create an Agent Performance Condition Alert that triggers on log full percent and trigger
your job that does a backup of the log. If prefer to just schedule my backups regularly, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ale" <Ale@.discussions.microsoft.com> wrote in message
news:02D8FBBD-0BB8-4514-B2D9-051E39DA18B0@.microsoft.com...
> ok, I checked this but I have one doubt: how can I check my log? if I want
> that my backup init when my log is at 80 %, how can I send an automatic form
> the backup process to work? it exists a manner for programming this in SQL
> Server 2005? (like a cron in UNIX)
> Thanks again
> Ale
> "AndyP" wrote:
>> In SQL Server Management Studio, Go to Management, Maintenance Plans, right
>> click the node and select New. Choose the "Back Up Database Task" option from
>> the toolbox. Configure the backup wizard as needed.
>>
>> --
>> AndyP,
>> Sr. Database Administrator,
>> MCDBA 2003
>>
>> "Ale" wrote:
>> > Hi:
>> > Somebody knows how I can program a stored procedure for automatic backup to
>> > disk in SQL Server 2005 checking the transaction log?
>> > Thanks in advance
>> > Ale :)|||Ok, I'll try it!
This Agent triggers when the condition is completed?
Thanks!
Ale :)
"Tibor Karaszi" wrote:
> You can create an Agent Performance Condition Alert that triggers on log full percent and trigger
> your job that does a backup of the log. If prefer to just schedule my backups regularly, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Ale" <Ale@.discussions.microsoft.com> wrote in message
> news:02D8FBBD-0BB8-4514-B2D9-051E39DA18B0@.microsoft.com...
> > ok, I checked this but I have one doubt: how can I check my log? if I want
> > that my backup init when my log is at 80 %, how can I send an automatic form
> > the backup process to work? it exists a manner for programming this in SQL
> > Server 2005? (like a cron in UNIX)
> > Thanks again
> > Ale
> >
> > "AndyP" wrote:
> >
> >> In SQL Server Management Studio, Go to Management, Maintenance Plans, right
> >> click the node and select New. Choose the "Back Up Database Task" option from
> >> the toolbox. Configure the backup wizard as needed.
> >>
> >>
> >> --
> >> AndyP,
> >> Sr. Database Administrator,
> >> MCDBA 2003
> >>
> >>
> >> "Ale" wrote:
> >>
> >> > Hi:
> >> > Somebody knows how I can program a stored procedure for automatic backup to
> >> > disk in SQL Server 2005 checking the transaction log?
> >> > Thanks in advance
> >> > Ale :)
>
>|||> This Agent triggers when the condition is completed?
When the condition is true, yes. For instance, you can for the counter "Percent log full" specify a
value "raises above" and 80. When that perf mon counter is > 80, the alert is fired.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ale" <Ale@.discussions.microsoft.com> wrote in message
news:E8BD8E4E-C95C-4A01-903F-C91848F8BD52@.microsoft.com...
> Ok, I'll try it!
> This Agent triggers when the condition is completed?
> Thanks!
> Ale :)
> "Tibor Karaszi" wrote:
>> You can create an Agent Performance Condition Alert that triggers on log full percent and trigger
>> your job that does a backup of the log. If prefer to just schedule my backups regularly, though.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Ale" <Ale@.discussions.microsoft.com> wrote in message
>> news:02D8FBBD-0BB8-4514-B2D9-051E39DA18B0@.microsoft.com...
>> > ok, I checked this but I have one doubt: how can I check my log? if I want
>> > that my backup init when my log is at 80 %, how can I send an automatic form
>> > the backup process to work? it exists a manner for programming this in SQL
>> > Server 2005? (like a cron in UNIX)
>> > Thanks again
>> > Ale
>> >
>> > "AndyP" wrote:
>> >
>> >> In SQL Server Management Studio, Go to Management, Maintenance Plans, right
>> >> click the node and select New. Choose the "Back Up Database Task" option from
>> >> the toolbox. Configure the backup wizard as needed.
>> >>
>> >>
>> >> --
>> >> AndyP,
>> >> Sr. Database Administrator,
>> >> MCDBA 2003
>> >>
>> >>
>> >> "Ale" wrote:
>> >>
>> >> > Hi:
>> >> > Somebody knows how I can program a stored procedure for automatic backup to
>> >> > disk in SQL Server 2005 checking the transaction log?
>> >> > Thanks in advance
>> >> > Ale :)
>>|||It´s working!
Thanks!!!
Ale
"Tibor Karaszi" wrote:
> > This Agent triggers when the condition is completed?
> When the condition is true, yes. For instance, you can for the counter "Percent log full" specify a
> value "raises above" and 80. When that perf mon counter is > 80, the alert is fired.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ale" <Ale@.discussions.microsoft.com> wrote in message
> news:E8BD8E4E-C95C-4A01-903F-C91848F8BD52@.microsoft.com...
> > Ok, I'll try it!
> > This Agent triggers when the condition is completed?
> > Thanks!
> > Ale :)
> >
> > "Tibor Karaszi" wrote:
> >
> >> You can create an Agent Performance Condition Alert that triggers on log full percent and trigger
> >> your job that does a backup of the log. If prefer to just schedule my backups regularly, though.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Ale" <Ale@.discussions.microsoft.com> wrote in message
> >> news:02D8FBBD-0BB8-4514-B2D9-051E39DA18B0@.microsoft.com...
> >> > ok, I checked this but I have one doubt: how can I check my log? if I want
> >> > that my backup init when my log is at 80 %, how can I send an automatic form
> >> > the backup process to work? it exists a manner for programming this in SQL
> >> > Server 2005? (like a cron in UNIX)
> >> > Thanks again
> >> > Ale
> >> >
> >> > "AndyP" wrote:
> >> >
> >> >> In SQL Server Management Studio, Go to Management, Maintenance Plans, right
> >> >> click the node and select New. Choose the "Back Up Database Task" option from
> >> >> the toolbox. Configure the backup wizard as needed.
> >> >>
> >> >>
> >> >> --
> >> >> AndyP,
> >> >> Sr. Database Administrator,
> >> >> MCDBA 2003
> >> >>
> >> >>
> >> >> "Ale" wrote:
> >> >>
> >> >> > Hi:
> >> >> > Somebody knows how I can program a stored procedure for automatic backup to
> >> >> > disk in SQL Server 2005 checking the transaction log?
> >> >> > Thanks in advance
> >> >> > Ale :)
> >>
> >>
> >>
> .
>

Automated backup failing

I keepp getting the following error in my application log for SQL Server 2000:
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan master, msdb, model''
(0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
2007-09-27 00:00:05 - Message: The job failed. The Job was invoked by
Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
Does anyone know what this means?
--
John SchusterSpecify a report file for the plan and check that file for detailed information about errors,. My
guess is that you try to do log backups for databases that are in simple recovery mode. Also, you
can't do log backup for master even if you set it to full recovery (master is special).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
>I keepp getting the following error in my application log for SQL Server 2000:
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> 'DB Maintenance Plan master, msdb, model''
> (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
> 2007-09-27 00:00:05 - Message: The job failed. The Job was invoked by
> Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
> Does anyone know what this means?
> --
> John Schuster|||Tibor,
Thank you very much. I will try doing what you suggest.
--
John Schuster
"Tibor Karaszi" wrote:
> Specify a report file for the plan and check that file for detailed information about errors,. My
> guess is that you try to do log backups for databases that are in simple recovery mode. Also, you
> can't do log backup for master even if you set it to full recovery (master is special).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
> >I keepp getting the following error in my application log for SQL Server 2000:
> >
> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> > 'DB Maintenance Plan master, msdb, model''
> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
> > 2007-09-27 00:00:05 - Message: The job failed. The Job was invoked by
> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
> >
> > Does anyone know what this means?
> > --
> >
> > John Schuster
>|||I do have reporting set up. This is the message I see for the maintenance
plan in question:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'EPDDOCS3' as 'SEP\EPDDOCS3$' (trusted)
Starting maintenance plan 'DB Maintenance Plan master, msdb, model' on
9/27/2007 12:00:06 AM
Backup can not be performed on database 'master'. This sub task is ignored.
[1] Database model: Transaction Log Backup...
Destination: [X:\MSSQL\EPDdocs_BACKUP\model_tlog_200709270000.TRN]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[2] Database model: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub task is ignored.
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'DB Maintenance Plan master, msdb, model' on
9/27/2007 12:00:06 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
--
John Schuster
"Tibor Karaszi" wrote:
> Specify a report file for the plan and check that file for detailed information about errors,. My
> guess is that you try to do log backups for databases that are in simple recovery mode. Also, you
> can't do log backup for master even if you set it to full recovery (master is special).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
> >I keepp getting the following error in my application log for SQL Server 2000:
> >
> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> > 'DB Maintenance Plan master, msdb, model''
> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
> > 2007-09-27 00:00:05 - Message: The job failed. The Job was invoked by
> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
> >
> > Does anyone know what this means?
> > --
> >
> > John Schuster
>|||Yep, as I suspected. You can't do log backups on databases that are in simple recovery model.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
news:78BACDAB-3C67-41B5-AB69-B67A701B45BE@.microsoft.com...
>I do have reporting set up. This is the message I see for the maintenance
> plan in question:
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'EPDDOCS3' as 'SEP\EPDDOCS3$' (trusted)
> Starting maintenance plan 'DB Maintenance Plan master, msdb, model' on
> 9/27/2007 12:00:06 AM
> Backup can not be performed on database 'master'. This sub task is ignored.
> [1] Database model: Transaction Log Backup...
> Destination: [X:\MSSQL\EPDdocs_BACKUP\model_tlog_200709270000.TRN]
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [2] Database model: Verifying Backup...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Backup can not be performed on database 'msdb'. This sub task is ignored.
> Deleting old text reports... 1 file(s) deleted.
> End of maintenance plan 'DB Maintenance Plan master, msdb, model' on
> 9/27/2007 12:00:06 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
> --
> John Schuster
>
> "Tibor Karaszi" wrote:
>> Specify a report file for the plan and check that file for detailed information about errors,. My
>> guess is that you try to do log backups for databases that are in simple recovery mode. Also, you
>> can't do log backup for master even if you set it to full recovery (master is special).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
>> news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
>> >I keepp getting the following error in my application log for SQL Server 2000:
>> >
>> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
>> > 'DB Maintenance Plan master, msdb, model''
>> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
>> > 2007-09-27 00:00:05 - Message: The job failed. The Job was invoked by
>> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
>> >
>> > Does anyone know what this means?
>> > --
>> >
>> > John Schuster|||Tibor,
How, then, do I back up the master and msdb databases? Is it possible?
--
John Schuster
"Tibor Karaszi" wrote:
> Yep, as I suspected. You can't do log backups on databases that are in simple recovery model.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> news:78BACDAB-3C67-41B5-AB69-B67A701B45BE@.microsoft.com...
> >I do have reporting set up. This is the message I see for the maintenance
> > plan in question:
> >
> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> > 'EPDDOCS3' as 'SEP\EPDDOCS3$' (trusted)
> > Starting maintenance plan 'DB Maintenance Plan master, msdb, model' on
> > 9/27/2007 12:00:06 AM
> > Backup can not be performed on database 'master'. This sub task is ignored.
> >
> > [1] Database model: Transaction Log Backup...
> > Destination: [X:\MSSQL\EPDdocs_BACKUP\model_tlog_200709270000.TRN]
> >
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >
> > [2] Database model: Verifying Backup...
> >
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >
> > Backup can not be performed on database 'msdb'. This sub task is ignored.
> >
> > Deleting old text reports... 1 file(s) deleted.
> >
> > End of maintenance plan 'DB Maintenance Plan master, msdb, model' on
> > 9/27/2007 12:00:06 AM
> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
> > --
> > John Schuster
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Specify a report file for the plan and check that file for detailed information about errors,. My
> >> guess is that you try to do log backups for databases that are in simple recovery mode. Also, you
> >> can't do log backup for master even if you set it to full recovery (master is special).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> >> news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
> >> >I keepp getting the following error in my application log for SQL Server 2000:
> >> >
> >> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> >> > 'DB Maintenance Plan master, msdb, model''
> >> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
> >> > 2007-09-27 00:00:05 - Message: The job failed. The Job was invoked by
> >> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
> >> >
> >> > Does anyone know what this means?
> >> > --
> >> >
> >> > John Schuster
> >>
>|||John,
You can only perform the Full backup, don't check for the Transaction log
backups.
Chris
"John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
news:0B25B6FF-9A71-4F34-9B23-D77090551424@.microsoft.com...
> Tibor,
> How, then, do I back up the master and msdb databases? Is it possible?
> --
> John Schuster
>
> "Tibor Karaszi" wrote:
>> Yep, as I suspected. You can't do log backups on databases that are in
>> simple recovery model.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
>> news:78BACDAB-3C67-41B5-AB69-B67A701B45BE@.microsoft.com...
>> >I do have reporting set up. This is the message I see for the
>> >maintenance
>> > plan in question:
>> >
>> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
>> > Server
>> > 'EPDDOCS3' as 'SEP\EPDDOCS3$' (trusted)
>> > Starting maintenance plan 'DB Maintenance Plan master, msdb, model' on
>> > 9/27/2007 12:00:06 AM
>> > Backup can not be performed on database 'master'. This sub task is
>> > ignored.
>> >
>> > [1] Database model: Transaction Log Backup...
>> > Destination: [X:\MSSQL\EPDdocs_BACKUP\model_tlog_200709270000.TRN]
>> >
>> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> >
>> > [2] Database model: Verifying Backup...
>> >
>> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> >
>> > Backup can not be performed on database 'msdb'. This sub task is
>> > ignored.
>> >
>> > Deleting old text reports... 1 file(s) deleted.
>> >
>> > End of maintenance plan 'DB Maintenance Plan master, msdb, model' on
>> > 9/27/2007 12:00:06 AM
>> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
>> > --
>> > John Schuster
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Specify a report file for the plan and check that file for detailed
>> >> information about errors,. My
>> >> guess is that you try to do log backups for databases that are in
>> >> simple recovery mode. Also, you
>> >> can't do log backup for master even if you set it to full recovery
>> >> (master is special).
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
>> >> >I keepp getting the following error in my application log for SQL
>> >> >Server 2000:
>> >> >
>> >> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB
>> >> > Maintenance Plan
>> >> > 'DB Maintenance Plan master, msdb, model''
>> >> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
>> >> > 2007-09-27 00:00:05 - Message: The job failed. The Job was invoked
>> >> > by
>> >> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
>> >> >
>> >> > Does anyone know what this means?
>> >> > --
>> >> >
>> >> > John Schuster
>> >>|||Thank you.
--
John Schuster
"Chris Wood" wrote:
> John,
> You can only perform the Full backup, don't check for the Transaction log
> backups.
> Chris
> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> news:0B25B6FF-9A71-4F34-9B23-D77090551424@.microsoft.com...
> > Tibor,
> >
> > How, then, do I back up the master and msdb databases? Is it possible?
> > --
> > John Schuster
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> Yep, as I suspected. You can't do log backups on databases that are in
> >> simple recovery model.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> >> news:78BACDAB-3C67-41B5-AB69-B67A701B45BE@.microsoft.com...
> >> >I do have reporting set up. This is the message I see for the
> >> >maintenance
> >> > plan in question:
> >> >
> >> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
> >> > Server
> >> > 'EPDDOCS3' as 'SEP\EPDDOCS3$' (trusted)
> >> > Starting maintenance plan 'DB Maintenance Plan master, msdb, model' on
> >> > 9/27/2007 12:00:06 AM
> >> > Backup can not be performed on database 'master'. This sub task is
> >> > ignored.
> >> >
> >> > [1] Database model: Transaction Log Backup...
> >> > Destination: [X:\MSSQL\EPDdocs_BACKUP\model_tlog_200709270000.TRN]
> >> >
> >> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >> >
> >> > [2] Database model: Verifying Backup...
> >> >
> >> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >> >
> >> > Backup can not be performed on database 'msdb'. This sub task is
> >> > ignored.
> >> >
> >> > Deleting old text reports... 1 file(s) deleted.
> >> >
> >> > End of maintenance plan 'DB Maintenance Plan master, msdb, model' on
> >> > 9/27/2007 12:00:06 AM
> >> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
> >> > --
> >> > John Schuster
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Specify a report file for the plan and check that file for detailed
> >> >> information about errors,. My
> >> >> guess is that you try to do log backups for databases that are in
> >> >> simple recovery mode. Also, you
> >> >> can't do log backup for master even if you set it to full recovery
> >> >> (master is special).
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
> >> >> >I keepp getting the following error in my application log for SQL
> >> >> >Server 2000:
> >> >> >
> >> >> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB
> >> >> > Maintenance Plan
> >> >> > 'DB Maintenance Plan master, msdb, model''
> >> >> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked on:
> >> >> > 2007-09-27 00:00:05 - Message: The job failed. The Job was invoked
> >> >> > by
> >> >> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step 1).
> >> >> >
> >> >> > Does anyone know what this means?
> >> >> > --
> >> >> >
> >> >> > John Schuster
> >> >>
> >>
>
>|||Recommended backup strategy for system databases (such as master, msdb,
model) is taking full backups of them when you make a change in your SQL
Server system like changing SQL Server configuration, adding new logins,
adding new jobs, adding linked servers etc.
Transaction Log backup is unnecessary for system databases.
--
Ekrem Ã?nsoy
"John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
news:B4858CF1-97B9-4A03-A365-802CFB2C92E9@.microsoft.com...
> Thank you.
> --
> John Schuster
>
> "Chris Wood" wrote:
>> John,
>> You can only perform the Full backup, don't check for the Transaction log
>> backups.
>> Chris
>> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
>> news:0B25B6FF-9A71-4F34-9B23-D77090551424@.microsoft.com...
>> > Tibor,
>> >
>> > How, then, do I back up the master and msdb databases? Is it possible?
>> > --
>> > John Schuster
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Yep, as I suspected. You can't do log backups on databases that are in
>> >> simple recovery model.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:78BACDAB-3C67-41B5-AB69-B67A701B45BE@.microsoft.com...
>> >> >I do have reporting set up. This is the message I see for the
>> >> >maintenance
>> >> > plan in question:
>> >> >
>> >> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
>> >> > Server
>> >> > 'EPDDOCS3' as 'SEP\EPDDOCS3$' (trusted)
>> >> > Starting maintenance plan 'DB Maintenance Plan master, msdb, model'
>> >> > on
>> >> > 9/27/2007 12:00:06 AM
>> >> > Backup can not be performed on database 'master'. This sub task is
>> >> > ignored.
>> >> >
>> >> > [1] Database model: Transaction Log Backup...
>> >> > Destination:
>> >> > [X:\MSSQL\EPDdocs_BACKUP\model_tlog_200709270000.TRN]
>> >> >
>> >> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> >> >
>> >> > [2] Database model: Verifying Backup...
>> >> >
>> >> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> >> >
>> >> > Backup can not be performed on database 'msdb'. This sub task is
>> >> > ignored.
>> >> >
>> >> > Deleting old text reports... 1 file(s) deleted.
>> >> >
>> >> > End of maintenance plan 'DB Maintenance Plan master, msdb, model' on
>> >> > 9/27/2007 12:00:06 AM
>> >> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
>> >> > --
>> >> > John Schuster
>> >> >
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> Specify a report file for the plan and check that file for detailed
>> >> >> information about errors,. My
>> >> >> guess is that you try to do log backups for databases that are in
>> >> >> simple recovery mode. Also, you
>> >> >> can't do log backup for master even if you set it to full recovery
>> >> >> (master is special).
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://sqlblog.com/blogs/tibor_karaszi
>> >> >>
>> >> >>
>> >> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in
>> >> >> message
>> >> >> news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
>> >> >> >I keepp getting the following error in my application log for SQL
>> >> >> >Server 2000:
>> >> >> >
>> >> >> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB
>> >> >> > Maintenance Plan
>> >> >> > 'DB Maintenance Plan master, msdb, model''
>> >> >> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked
>> >> >> > on:
>> >> >> > 2007-09-27 00:00:05 - Message: The job failed. The Job was
>> >> >> > invoked
>> >> >> > by
>> >> >> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step
>> >> >> > 1).
>> >> >> >
>> >> >> > Does anyone know what this means?
>> >> >> > --
>> >> >> >
>> >> >> > John Schuster
>> >> >>
>> >>
>>|||Thank you.
--
John Schuster
"Ekrem Ã?nsoy" wrote:
> Recommended backup strategy for system databases (such as master, msdb,
> model) is taking full backups of them when you make a change in your SQL
> Server system like changing SQL Server configuration, adding new logins,
> adding new jobs, adding linked servers etc.
> Transaction Log backup is unnecessary for system databases.
> --
> Ekrem Ã?nsoy
>
> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> news:B4858CF1-97B9-4A03-A365-802CFB2C92E9@.microsoft.com...
> > Thank you.
> > --
> > John Schuster
> >
> >
> > "Chris Wood" wrote:
> >
> >> John,
> >>
> >> You can only perform the Full backup, don't check for the Transaction log
> >> backups.
> >>
> >> Chris
> >>
> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in message
> >> news:0B25B6FF-9A71-4F34-9B23-D77090551424@.microsoft.com...
> >> > Tibor,
> >> >
> >> > How, then, do I back up the master and msdb databases? Is it possible?
> >> > --
> >> > John Schuster
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Yep, as I suspected. You can't do log backups on databases that are in
> >> >> simple recovery model.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:78BACDAB-3C67-41B5-AB69-B67A701B45BE@.microsoft.com...
> >> >> >I do have reporting set up. This is the message I see for the
> >> >> >maintenance
> >> >> > plan in question:
> >> >> >
> >> >> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL
> >> >> > Server
> >> >> > 'EPDDOCS3' as 'SEP\EPDDOCS3$' (trusted)
> >> >> > Starting maintenance plan 'DB Maintenance Plan master, msdb, model'
> >> >> > on
> >> >> > 9/27/2007 12:00:06 AM
> >> >> > Backup can not be performed on database 'master'. This sub task is
> >> >> > ignored.
> >> >> >
> >> >> > [1] Database model: Transaction Log Backup...
> >> >> > Destination:
> >> >> > [X:\MSSQL\EPDdocs_BACKUP\model_tlog_200709270000.TRN]
> >> >> >
> >> >> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >> >> >
> >> >> > [2] Database model: Verifying Backup...
> >> >> >
> >> >> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >> >> >
> >> >> > Backup can not be performed on database 'msdb'. This sub task is
> >> >> > ignored.
> >> >> >
> >> >> > Deleting old text reports... 1 file(s) deleted.
> >> >> >
> >> >> > End of maintenance plan 'DB Maintenance Plan master, msdb, model' on
> >> >> > 9/27/2007 12:00:06 AM
> >> >> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
> >> >> > --
> >> >> > John Schuster
> >> >> >
> >> >> >
> >> >> > "Tibor Karaszi" wrote:
> >> >> >
> >> >> >> Specify a report file for the plan and check that file for detailed
> >> >> >> information about errors,. My
> >> >> >> guess is that you try to do log backups for databases that are in
> >> >> >> simple recovery mode. Also, you
> >> >> >> can't do log backup for master even if you set it to full recovery
> >> >> >> (master is special).
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >> >>
> >> >> >>
> >> >> >> "John Schuster" <JohnSchuster@.discussions.microsoft.com> wrote in
> >> >> >> message
> >> >> >> news:C87E4F0D-D670-4496-9169-2BF3E0922749@.microsoft.com...
> >> >> >> >I keepp getting the following error in my application log for SQL
> >> >> >> >Server 2000:
> >> >> >> >
> >> >> >> > SQL Server Scheduled Job 'Transaction Log Backup Job for DB
> >> >> >> > Maintenance Plan
> >> >> >> > 'DB Maintenance Plan master, msdb, model''
> >> >> >> > (0x0D97085E32705247B590ADA245D3FE07) - Status: Failed - Invoked
> >> >> >> > on:
> >> >> >> > 2007-09-27 00:00:05 - Message: The job failed. The Job was
> >> >> >> > invoked
> >> >> >> > by
> >> >> >> > Schedule 76 (Schedule 1). The last step to run was step 1 (Step
> >> >> >> > 1).
> >> >> >> >
> >> >> >> > Does anyone know what this means?
> >> >> >> > --
> >> >> >> >
> >> >> >> > John Schuster
> >> >> >>
> >> >>
> >>
> >>
> >>
>