Database status: Loading

After restoring my old transaction logs, the database
showing a status: 'Loading'. The database status still
remain 'Loading' even after 3 days.
Is there anyway I can stop the loading and will it cause
any problem to my data?Perhaps the last log was restored with NORECOVERY. In this case, you
can recover the database with the command below. This will not
introduce any data problems.
RESTORE DATABASE MyDatabase
WITH RECOVERY
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"TEO" <teo.yongjiew@.eonbank.com.my> wrote in message
news:067501c3a80b$73cf54e0$a401280a@.phx.gbl...
> After restoring my old transaction logs, the database
> showing a status: 'Loading'. The database status still
> remain 'Loading' even after 3 days.
> Is there anyway I can stop the loading and will it cause
> any problem to my data?sql

Database size allocation - where other than tables, logs, etc ?

I have a Db that is 1.7 gigs. The table data takes approximately 200megs. The transaction logs were truncated. Where else can this large size be coming from and how can I confirm?

DB is generally small. ~25 tables, 100 SPs, 10 views, etc.

Note:

I have 4 queues using SQL Notifications, but when selecting from them results in no data.

Thanks

Scott

sp_spaceused can give you some details about data space. sp_helpfile will show data and log file sizes.

You may just need to shrink your database to get the file sizes down using DBCC SHRINKDATABASE or SHRINKFILE.

Database set to simple recovery

Hello I want to perform a transaction log backup for disaster recovery every
30min. Because the recovery mode is set to simple it is not allowing me to
to do this.
My questions are;
1. What are the beneifits of a simple recovery? My transaction log still gro
ws very large.
2. What are the benefits of performing a transaction log backup?
3. Do I have to detach the database to change from simple to full recovery?
ThanksPlease read the page titled "Selecting a Recovery Model" in SQL Server 2000
Books Online. This answers most of your questions.
Transaction log backups help you recover to a point-in-time, and no, you
don't have to detach the database, to change the recovery mode. See ALTER
DATABASE command in SQL Server Books Online for more information on changing
recovery models of databases.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"robert salazar" <rsalazar@.cbbank.com> wrote in message
news:E055BCF9-34BE-45DE-BE89-62C56AF61E11@.microsoft.com...
Hello I want to perform a transaction log backup for disaster recovery every
30min. Because the recovery mode is set to simple it is not allowing me to
to do this.
My questions are;
1. What are the beneifits of a simple recovery? My transaction log still
grows very large.
2. What are the benefits of performing a transaction log backup?
3. Do I have to detach the database to change from simple to full recovery?
Thanks

Database set to simple recovery

Hello I want to perform a transaction log backup for disaster recovery every 30min. Because the recovery mode is set to simple it is not allowing me to to do this.
My questions are;
1. What are the beneifits of a simple recovery? My transaction log still grows very large.
2. What are the benefits of performing a transaction log backup?
3. Do I have to detach the database to change from simple to full recovery?
Thanks
Please read the page titled "Selecting a Recovery Model" in SQL Server 2000
Books Online. This answers most of your questions.
Transaction log backups help you recover to a point-in-time, and no, you
don't have to detach the database, to change the recovery mode. See ALTER
DATABASE command in SQL Server Books Online for more information on changing
recovery models of databases.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"robert salazar" <rsalazar@.cbbank.com> wrote in message
news:E055BCF9-34BE-45DE-BE89-62C56AF61E11@.microsoft.com...
Hello I want to perform a transaction log backup for disaster recovery every
30min. Because the recovery mode is set to simple it is not allowing me to
to do this.
My questions are;
1. What are the beneifits of a simple recovery? My transaction log still
grows very large.
2. What are the benefits of performing a transaction log backup?
3. Do I have to detach the database to change from simple to full recovery?
Thanks

Database Seal: Transaction Log Backup, error in translog backup

I have changed the time for the maintenanceplan but i still get the same
message, could it be something wrong with the database?
a part from the last backuplog:
[31] Database SPS01_Config_db: Transaction Log Backup...
Destination: [D:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\SPS01_Config_db\SPS01_Config_db_tlog_200505100115.TRN]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[32] Database SPS01_Config_db: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'VirtualCenter'. This sub task is
ignored.
End of maintenance plan 'UserDBs' on 2005-05-10 01:15:20
SQLMAINT.EXE Process Exit Code: 1 (Failed)You can not perform a transaction log backup on a database that uses the
simple recovery model. Set your database to either the full or bulk_logged
recovery model and you can make transaction log backups.
--
Jacco Schalkwijk
SQL Server MVP
"Biowaste" <Biowaste@.discussions.microsoft.com> wrote in message
news:35E8E641-6689-461A-A12C-CF1E69C08B8A@.microsoft.com...
>I have changed the time for the maintenanceplan but i still get the same
> message, could it be something wrong with the database?
> a part from the last backuplog:
> [31] Database SPS01_Config_db: Transaction Log Backup...
> Destination: [D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\SPS01_Config_db\SPS01_Config_db_tlog_200505100115.TRN]
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [32] Database SPS01_Config_db: Verifying Backup...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Backup can not be performed on database 'VirtualCenter'. This sub task is
> ignored.
> End of maintenance plan 'UserDBs' on 2005-05-10 01:15:20
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>|||How and where do I change that in the maint. plan?
this has worked in the past... just a few weaks ago.
how come it is only one db that fails?
"Jacco Schalkwijk" wrote:
> You can not perform a transaction log backup on a database that uses the
> simple recovery model. Set your database to either the full or bulk_logged
> recovery model and you can make transaction log backups.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Biowaste" <Biowaste@.discussions.microsoft.com> wrote in message
> news:35E8E641-6689-461A-A12C-CF1E69C08B8A@.microsoft.com...
> >I have changed the time for the maintenanceplan but i still get the same
> > message, could it be something wrong with the database?
> >
> > a part from the last backuplog:
> > [31] Database SPS01_Config_db: Transaction Log Backup...
> > Destination: [D:\Program Files\Microsoft SQL
> > Server\MSSQL\BACKUP\SPS01_Config_db\SPS01_Config_db_tlog_200505100115.TRN]
> >
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >
> > [32] Database SPS01_Config_db: Verifying Backup...
> >
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >
> > Backup can not be performed on database 'VirtualCenter'. This sub task is
> > ignored.
> >
> > End of maintenance plan 'UserDBs' on 2005-05-10 01:15:20
> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
> >
>
>|||This is a database option, not something you set in the maint plan. EM, right-click the database,
properties, options.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Biowaste" <kalmeborg@.hotmail.com> wrote in message
news:85A3F0C2-62B8-47E2-B424-09A2B5B245A4@.microsoft.com...
> How and where do I change that in the maint. plan?
> this has worked in the past... just a few weaks ago.
> how come it is only one db that fails?
> "Jacco Schalkwijk" wrote:
>> You can not perform a transaction log backup on a database that uses the
>> simple recovery model. Set your database to either the full or bulk_logged
>> recovery model and you can make transaction log backups.
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "Biowaste" <Biowaste@.discussions.microsoft.com> wrote in message
>> news:35E8E641-6689-461A-A12C-CF1E69C08B8A@.microsoft.com...
>> >I have changed the time for the maintenanceplan but i still get the same
>> > message, could it be something wrong with the database?
>> >
>> > a part from the last backuplog:
>> > [31] Database SPS01_Config_db: Transaction Log Backup...
>> > Destination: [D:\Program Files\Microsoft SQL
>> > Server\MSSQL\BACKUP\SPS01_Config_db\SPS01_Config_db_tlog_200505100115.TRN]
>> >
>> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> >
>> > [32] Database SPS01_Config_db: Verifying Backup...
>> >
>> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
>> >
>> > Backup can not be performed on database 'VirtualCenter'. This sub task is
>> > ignored.
>> >
>> > End of maintenance plan 'UserDBs' on 2005-05-10 01:15:20
>> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
>> >
>>

Database Seal: Transaction Log Backup, error in translog backup

I have changed the time for the maintenanceplan but i still get the same
message, could it be something wrong with the database?
a part from the last backuplog:
[31] Database SPS01_Config_db: Transaction Log Backup...
Destination: [D:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\SPS01_Config_db\SPS01_Config_d b_tlog_200505100115.TRN]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[32] Database SPS01_Config_db: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'VirtualCenter'. This sub task is
ignored.
End of maintenance plan 'UserDBs' on 2005-05-10 01:15:20
SQLMAINT.EXE Process Exit Code: 1 (Failed)
You can not perform a transaction log backup on a database that uses the
simple recovery model. Set your database to either the full or bulk_logged
recovery model and you can make transaction log backups.
Jacco Schalkwijk
SQL Server MVP
"Biowaste" <Biowaste@.discussions.microsoft.com> wrote in message
news:35E8E641-6689-461A-A12C-CF1E69C08B8A@.microsoft.com...
>I have changed the time for the maintenanceplan but i still get the same
> message, could it be something wrong with the database?
> a part from the last backuplog:
> [31] Database SPS01_Config_db: Transaction Log Backup...
> Destination: [D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\SPS01_Config_db\SPS01_Config_d b_tlog_200505100115.TRN]
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [32] Database SPS01_Config_db: Verifying Backup...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Backup can not be performed on database 'VirtualCenter'. This sub task is
> ignored.
> End of maintenance plan 'UserDBs' on 2005-05-10 01:15:20
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>

Database Seal: Transaction Log Backup, error in translog backup

I have changed the time for the maintenanceplan but i still get the same
message, could it be something wrong with the database?
a part from the last backuplog:
[31] Database SPS01_Config_db: Transaction Log Backup...
Destination: [D:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\SPS01_Config_db\SPS0
1_Config_db_tlog_200505100115.TRN]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[32] Database SPS01_Config_db: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'VirtualCenter'. This sub task is
ignored.
End of maintenance plan 'UserDBs' on 2005-05-10 01:15:20
SQLMAINT.EXE Process Exit Code: 1 (Failed)You can not perform a transaction log backup on a database that uses the
simple recovery model. Set your database to either the full or bulk_logged
recovery model and you can make transaction log backups.
Jacco Schalkwijk
SQL Server MVP
"Biowaste" <Biowaste@.discussions.microsoft.com> wrote in message
news:35E8E641-6689-461A-A12C-CF1E69C08B8A@.microsoft.com...
>I have changed the time for the maintenanceplan but i still get the same
> message, could it be something wrong with the database?
> a part from the last backuplog:
> [31] Database SPS01_Config_db: Transaction Log Backup...
> Destination: [D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\SPS01_Config_db\SPS0
1_Config_db_tlog_200505100115.TRN]
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [32] Database SPS01_Config_db: Verifying Backup...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> Backup can not be performed on database 'VirtualCenter'. This sub task is
> ignored.
> End of maintenance plan 'UserDBs' on 2005-05-10 01:15:20
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
>

Database restores using Enterprise Manager

Good afternoon. I am using MS SQL 2K and was wondering if it is possible to restore multiple back-up files (database and transaction logs) to a database, if you haven't created a back-up set, using Enterprise Manager. I know that you can write T-SQL to first restore the back-up file and each of the transaction log files, except the last one, with the option of norecovery, and then the last transaction log file, with recovery. Any help would be greatly appreciated. Thank you.

Chris

That the way, restoring the database files one by one.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks for the reply, Jens.

Database restore after system Rebuild

Hi,
I have one SQL server where my databases where kept on the D drive, and
the transaction logs where on a physically seperate C drive. The D
drive became corrupted and I could not start SQL Server because the
master database was corrupt. So, I had to use the RebuildDM.exe to
rebuild the system tables. I have the log files sitting on the C
drive, but I never had a chance to back them up to the most recent
point today because I could not get into SQL Server until I rebuild it.
Now what I would like to do is just restore the databases and then use
the logs that are sitting on C, but the only way it seems that I can do
this is by backing up the transaction logs first, which I did not have
a chance to do. Is there anyway I can take a backup of my DB and tell
it to use the transaction logs that already exists so that it can apply
any missing transactions?
I forgot to mention, this box is windows server 2003 with sql server
2000 SP4
|||Doh, after hours of searching I found the answer right after posting
this. For anyone who has this problem, look here
http://support.microsoft.com/default...;EN-US;Q253817

Database restore after system Rebuild

Hi,
I have one SQL server where my databases where kept on the D drive, and
the transaction logs where on a physically seperate C drive. The D
drive became corrupted and I could not start SQL Server because the
master database was corrupt. So, I had to use the RebuildDM.exe to
rebuild the system tables. I have the log files sitting on the C
drive, but I never had a chance to back them up to the most recent
point today because I could not get into SQL Server until I rebuild it.
Now what I would like to do is just restore the databases and then use
the logs that are sitting on C, but the only way it seems that I can do
this is by backing up the transaction logs first, which I did not have
a chance to do. Is there anyway I can take a backup of my DB and tell
it to use the transaction logs that already exists so that it can apply
any missing transactions?I forgot to mention, this box is windows server 2003 with sql server
2000 SP4|||Doh, after hours of searching I found the answer right after posting
this. For anyone who has this problem, look here
http://support.microsoft.com/defaul...b;EN-US;Q253817

Database restore after system Rebuild

Hi,
I have one SQL server where my databases where kept on the D drive, and
the transaction logs where on a physically seperate C drive. The D
drive became corrupted and I could not start SQL Server because the
master database was corrupt. So, I had to use the RebuildDM.exe to
rebuild the system tables. I have the log files sitting on the C
drive, but I never had a chance to back them up to the most recent
point today because I could not get into SQL Server until I rebuild it.
Now what I would like to do is just restore the databases and then use
the logs that are sitting on C, but the only way it seems that I can do
this is by backing up the transaction logs first, which I did not have
a chance to do. Is there anyway I can take a backup of my DB and tell
it to use the transaction logs that already exists so that it can apply
any missing transactions?I forgot to mention, this box is windows server 2003 with sql server
2000 SP4|||Doh, after hours of searching I found the answer right after posting
this. For anyone who has this problem, look here
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q253817

Database Recovery and Checkpoint

Recovery Model is Full and we make full backup of database
every night. Transaction Log backup is performed hourly.
I would like to know if the system crashes, does it mean
that we can recover the system by restoring
1) Last night full backup
2) Transaction Log since Full Backup ?
Someone mentioned that checkpoints are automatically
created by SQL Server 2000. I would like to know how
checkpoint affect the database recovery procedure ? IF
not, why do we create checkpoint periodically ?You can recover the system to the point of your last log backup using
the process you describe. Checkpoints do not affect your recovery
procedure.
The main purpose of automatic checkpoints is to reduce database recovery
time during system restart. SQL Server can start forward recovery from
the last checkpoint rather than the beginning of the log. See the Books
Online <architec.chm::/8_ar_da2_8y3y.htm> for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Stephen Mak" <anonymous@.discussions.microsoft.com> wrote in message
news:07b701c3af0a$78c2ec40$a301280a@.phx.gbl...
> Recovery Model is Full and we make full backup of database
> every night. Transaction Log backup is performed hourly.
> I would like to know if the system crashes, does it mean
> that we can recover the system by restoring
> 1) Last night full backup
> 2) Transaction Log since Full Backup ?
> Someone mentioned that checkpoints are automatically
> created by SQL Server 2000. I would like to know how
> checkpoint affect the database recovery procedure ? IF
> not, why do we create checkpoint periodically ?|||In addition to Dan's post:
You can do a log backup of a damaged database using the NO_TRUNCATE option of the backup log
command. Even if the whole installation is what, you can get the ldf file to a working SQL Server
and do the procedure on that machine (there's a KB on this).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Stephen Mak" <anonymous@.discussions.microsoft.com> wrote in message
news:07b701c3af0a$78c2ec40$a301280a@.phx.gbl...
> Recovery Model is Full and we make full backup of database
> every night. Transaction Log backup is performed hourly.
> I would like to know if the system crashes, does it mean
> that we can recover the system by restoring
> 1) Last night full backup
> 2) Transaction Log since Full Backup ?
> Someone mentioned that checkpoints are automatically
> created by SQL Server 2000. I would like to know how
> checkpoint affect the database recovery procedure ? IF
> not, why do we create checkpoint periodically ?