Record Locks: state

Showing posts with label state. Show all posts

Wednesday, March 28, 2012

Recovery mode "SIMPLE" & SP2

I have several databases that perform daily backups. Ever since I installed SP2, the backup jobs are failing. The logs state that it's because the database recovery mode cannot be SIMPLE, but need to be either FULL or Bulk-Logged.

Can anyone tell me if this is true, and that I do have to change my recovery mode?

Here's the error:

NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042
Report was generated on "ICIS-SQL-SERVER".
Maintenance Plan: DB Backup
Duration: 00:08:05
Status: Warning: One or more tasks failed..
Details:
Back Up Database (Transaction Log) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-10T05:08:09.
Task end: 2007-03-10T05:08:09.
Failed:(-1073548784) Executing the query "BACKUP LOG [hl7 db_SamirTesting] TO DISK = N'D:\\mssql\\backup\\hl7 db_SamirTesting\\hl7 db_SamirTesting_backup_200703100508.trn' WITH NOFORMAT, NOINIT, NAME = N'hl7 db_SamirTesting_backup_20070310050809', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Command:BACKUP LOG [hl7 db_SamirTesting] TO DISK = N''D:\mssql\backup\hl7 db_SamirTesting\hl7 db_SamirTesting_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_SamirTesting_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [dg_efilm_153] TO DISK = N''D:\mssql\backup\dg_efilm_153\dg_efilm_153_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''dg_efilm_153_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [hl7 db] TO DISK = N''D:\mssql\backup\hl7 db\hl7 db_backup_200703100508.trn'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_backup_20070310050809'', SKIP, REWIND, NOUNLOAD, STATS = 10

Back Up Database (Transaction Log) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-10T05:08:09.
Task end: 2007-03-10T05:08:09.
Failed:(-1073548784) Executing the query "BACKUP LOG [dg_efilm_153] TO DISK = N'D:\\mssql\\backup\\dg_efilm_153\\dg_efilm_153_backup_200703100508.trn' WITH NOFORMAT, NOINIT, NAME = N'dg_efilm_153_backup_20070310050809', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Back Up Database (Transaction Log) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-10T05:08:09.
Task end: 2007-03-10T05:08:09.
Failed:(-1073548784) Executing the query "BACKUP LOG [hl7 db] TO DISK = N'D:\\mssql\\backup\\hl7 db\\hl7 db_backup_200703100508.trn' WITH NOFORMAT, NOINIT, NAME = N'hl7 db_backup_20070310050809', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Back Up Database (Full) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All databases
Type: Full
Append existing
Task start: 2007-03-10T05:00:06.
Task end: 2007-03-10T05:08:08.
Success
Command:EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\master''
GO
EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\model''
GO
EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\msdb''
GO
EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\hl7 db_SamirTesting''
GO
EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\dg_efilm_153''
GO
EXECUTE master.dbo.xp_create_subdir N''D:\mssql\backup\hl7 db''
GO
BACKUP DATABASE [master] TO DISK = N''D:\mssql\backup\master\master_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''master_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [model] TO DISK = N''D:\mssql\backup\model\model_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''model_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [msdb] TO DISK = N''D:\mssql\backup\msdb\msdb_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''msdb_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [hl7 db_SamirTesting] TO DISK = N''D:\mssql\backup\hl7 db_SamirTesting\hl7 db_SamirTesting_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_SamirTesting_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [dg_efilm_153] TO DISK = N''D:\mssql\backup\dg_efilm_153\dg_efilm_153_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''dg_efilm_153_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10
GO
BACKUP DATABASE [hl7 db] TO DISK = N''D:\mssql\backup\hl7 db\hl7 db_backup_200703100500.bak'' WITH NOFORMAT, NOINIT, NAME = N''hl7 db_backup_20070310050006'', SKIP, REWIND, NOUNLOAD, STATS = 10

Please help,

-tim

Check what kind of recovery model is adopted on the databases that are involved in this backup schedule.|||

I have this issue aswell but with the ReportServer database and the ReportServerTempDB database. ReportServer recovery mode is set to full

ReportServerTempDB recovery mode is set to simple.

We have a database maintenance plan set to back up all user databases (at the top of the window it says that simple will be ignored'. The maintenance plan runs but fails as it tries to back up the ReportServerTempDB database.

The messages in the history of the plan say…

9/5/2007 09:15 Database 'ReportServerTempDB' will not be backed up because it does not have its recovery model set to Full or Bulk Logged.

9/5/2007 09:15 Executing the query "BACKUP LOG [ReportServerTempDB] TO DISK = N'G:\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\ReportServerTempDB\\ReportServerTempDB_backup_200705091638.trn' WITH NOFORMAT, NOINIT, NAME = N'ReportServerTempDB_backup_20070509163838', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

It appears that it reports it as an error when it checks the recovery mode, but then tries to back it up anyway. I do not have any problems with any other databases except the reporting services ones. At the moment I have both set to full recovery mode, just to get the maintenance plan to work. Any ideas anyone? This worked fine on Sp1 with the database set to Simple recover mode..

Thank you!

|||You cannot backup the system database and report server based databases in the transaction log schedule, as the error is obvious due to the configuration for system databases recovery model and you will not be able change that.

Recovery mode "SIMPLE" & SP2

Can anyone tell me if this is true, and that I do have to change my recovery mode?

Here's the error:

Back Up Database (Transaction Log) (ICIS-SQL-SERVER)
Backup Database on Target server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Type: Transaction Log
Append existing
Task start: 2007-03-10T05:08:09.
Task end: 2007-03-10T05:08:09.
Failed:(-1073548784) Executing the query "BACKUP LOG [hl7 db] TO DISK = N'D:\\mssql\\backup\\hl7 db\\hl7 db_backup_200703100508.trn' WITH NOFORMAT, NOINIT, NAME = N'hl7 db_backup_20070310050809', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Please help,

-tim

Check what kind of recovery model is adopted on the databases that are involved in this backup schedule.|||

I have this issue aswell but with the ReportServer database and the ReportServerTempDB database. ReportServer recovery mode is set to full

ReportServerTempDB recovery mode is set to simple.

The messages in the history of the plan say…

9/5/2007 09:15 Database 'ReportServerTempDB' will not be backed up because it does not have its recovery model set to Full or Bulk Logged.

Thank you!

Friday, March 23, 2012

Recovering SQL 2000 Database without the Log File not in clean disconnected state

We just had a hard drive crash here. We were able to copy off the .mdf file from SQL server 2000 from the drive, but not the .ldf (which was only 720k anyhow).

When I try to attach the mdf to an SQL Server 2000 or 2005 server I get an error saying that the log file doesn't exist.

Does anyone know a way to attach this not-cleanly-closed database file and recover the data? (apparently the person decided that they need-not backup their database :<)

Thanks for any help you can provide!

You may get lucky and be able to attach to a SQL 2005 instance and use the ATTACH_REBUILD_LOG clause...try something like this:

create database <x> on <filespec> for attach_rebuild_log

|||

Thanks. I tried that, but it said the same thing, and the documentation says that you have to have a cleanly shut down database to be able to use that command.

Thanks anyways! Any other ideas?

|||I got the same problem and the sames answers from 2000 and 2005. Any idea will help me too.|||This has to go back to your backups. Do you have full/differential/log backups? What's your disk architecture?|||I posted too fast...didn't get to read that you do not have backups...I have another question...did the entire server go down or only the disk?|||The whole drive failed. I was able to freeze it and get the mdf, but the log file was corrupt.

Hence i have an mdf and no log file that will work, and the idiot that owned the machine didn't do a backup.

Recovering SQL 2000 Database without the Log File not in clean disconnected state

We just had a hard drive crash here. We were able to copy off the .mdf file from SQL server 2000 from the drive, but not the .ldf (which was only 720k anyhow).

When I try to attach the mdf to an SQL Server 2000 or 2005 server I get an error saying that the log file doesn't exist.

Does anyone know a way to attach this not-cleanly-closed database file and recover the data? (apparently the person decided that they need-not backup their database :<)

Thanks for any help you can provide!

You may get lucky and be able to attach to a SQL 2005 instance and use the ATTACH_REBUILD_LOG clause...try something like this:

create database <x> on <filespec> for attach_rebuild_log

|||

Thanks. I tried that, but it said the same thing, and the documentation says that you have to have a cleanly shut down database to be able to use that command.

Thanks anyways! Any other ideas?

Hence i have an mdf and no log file that will work, and the idiot that owned the machine didn't do a backup.

Recovering SQL 2000 Database without the Log File not in clean disconnected state

We just had a hard drive crash here. We were able to copy off the .mdf file from SQL server 2000 from the drive, but not the .ldf (which was only 720k anyhow).

When I try to attach the mdf to an SQL Server 2000 or 2005 server I get an error saying that the log file doesn't exist.

Does anyone know a way to attach this not-cleanly-closed database file and recover the data? (apparently the person decided that they need-not backup their database :<)

Thanks for any help you can provide!

You may get lucky and be able to attach to a SQL 2005 instance and use the ATTACH_REBUILD_LOG clause...try something like this:

create database <x> on <filespec> for attach_rebuild_log

|||

Thanks. I tried that, but it said the same thing, and the documentation says that you have to have a cleanly shut down database to be able to use that command.

Thanks anyways! Any other ideas?

Hence i have an mdf and no log file that will work, and the idiot that owned the machine didn't do a backup.

Recovering MDF file! Need Assistance!!

Somehow, our MS SQL 2000 Server database mdf file size is 0,
The database is in suspected state. and the backup file related to that database shown 0 size.
Its totaly weird as there are many databases in server group but just one database's mdf file and backup file size is 0.

Any idea how this can possibly happen and is there any way to recover it from this state

Thank you very muchTry running sp_resetstatus 'DBNAME' .
For a Suspect database the query "Select Status from master..Sysdatabases' would return 256.
The above sp should set it back to 0.

Also restoring the database file will turn off the suspect mode.|||reseting status did not help cause mdf files' physical size on the HD is zero bytes. the backup files size is as same as its

IS there a way to see a log file in sql to see what has happened?|||The only tool I have heard of that does this is Lumigent Log Explorer. It is fairly expensive though...

http://www.lumigent.com/products/le_sql/le_sql.htm|||My guess is that there is something wrong with your backup procedure (has it ever been tested) and that the zero length file has been restored.

Recovering DBs with a "Suspect" state

Hello
(SQL Server 2000)
I have databases that appear as "Suspect" in the Enteprise Manager. What can I do to recover them or put them in a normal state?
Thanks a lot.Depends on why it is suspect. If you are lucky, someone simply moved the database files, and you just beed to move them back. if you have average luck, then you are looking at a database restore. What does the errorlog say?|||before you decide what to do, you ought to determine the cause.

but you can either...

a. you might be able to put your database in emergency mode and extract your data using bcp or dts.

or

b. restore from your last good backup.

I practice full recovery and can recover to any point in time, so I usually go with b and I have never done a, but I hear it is possible in some situations.

Saturday, February 25, 2012

Records comparsion

Please look at the code template below for my problem. I am comparing two tables. Table 99 is the current state of the table. I get in a fresh extract of Table 99 everyday from my source - DB2 and I insert that in Table 00. Then I execute the code template below so that Table 99 stays current with the source DB2 on a daily basis.
--
CREATE TABLE myTable99 (
Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1)
, CONSTRAINT myTable99_pk PRIMARY KEY (Col1, Col2))

CREATE TABLE myTable00 (
Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1)
, CONSTRAINT myTable00_pk PRIMARY KEY (Col1, Col2))
GO

INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL
SELECT '1','2','d','e','f' UNION ALL
SELECT '1','3','g','h','i' UNION ALL
SELECT '1','4','j','k','l'
--DELETED

INSERT INTO myTable00(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL --NO CHANGE
SELECT '1','2','x','y','z' UNION ALL -- UPDATE
SELECT '1','3','g','h','i' UNION ALL --NO CHANGE
SELECT '2','3','j','b','c' --INSERT
GO

SELECT * FROM myTable99
SELECT * FROM myTable00
GO

--DO DELETES FIRST

DELETE FROM a
FROM myTable99 a
LEFT JOIN myTable00 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
WHERE b.Col1 IS NULL AND b.Col2 IS NULL

-- INSERT

INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT a.Col1, a.Col2, a.Col3, a.Col4, a.Col5
FROM myTable00 a
LEFT JOIN myTable99 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
WHERE b.Col1 IS NULL AND b.Col2 IS NULL

-- UPDATE

UPDATE a
SET Col3 = b.Col3
, Col4 = b.Col4
, Col5 = b.Col5
FROM myTable99 a
INNER JOIN myTable00 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
AND ( a.Col3 <> b.Col3
OR a.Col4 <> b.Col4
OR a.Col5 <> b.Col5)
GO

SELECT * FROM myTable99
SELECT * FROM myTable00
GO

DROP TABLE myTable00
DROP TABLE myTable99
GO

--
I want to process new and updated records.
Let me explain the problem -

The last row for myTable99 is - 1','4','j','k','l'

The last row for myTable00 is - '2','3','j','b','c'

Col1 & Col2 are the PK's. So the last row from Table 99 gets deleted and the one from Table00 gets inserted as a 'New' record.Now I want to do processing only on those records that are new. So I should process record '2','3','j','b','c' .

My processing depends on the Col3 (attribute). My problem is I do not want to process records if the attribute has been processed before. In this case Col3 has 'j' and it has been processed before in 1','4','j','k','l'. It's just that this record no longer exist. So when I get in '2','3','j','b','c' , I want to say that this record has been processed.

How do I do that with the Insert new records query? The same case is for updates as well.

Any help is appreciated.

Thank you.My processing depends on the Col3 (attribute). My problem is I do not want to process records if the attribute has been processed before. In this case Col3 has 'j' and it has been processed before in 1','4','j','k','l'. It's just that this record no longer exist. So when I get in '2','3','j','b','c' , I want to say that this record has been processed.

You lost me....

Huh?

Understand....Whatever is in Col3 attribute BELONGS to the composite key Col1+Col2...it does not transcend the row to some other key?

It belongs to that row...if it so happens that the same value is on another row that's fine...but it belongs to that other key...

Can you give the explanation another shot?|||Why so complicated?

Can you not flag the affected records after the import and use the EXISTS / NOT EXISTS clause to target those which have been actioned as you translate them from import source to stored data?|||I think he's saying, that if I messed with an "attribute" (bad choice of words here) of a value of 5 in column 3, then I don't want to touch ANY of the rows...across keys...right?|||Here is the processing deal. I get the value of col 3 and check to see if it exists in this other table Z.

So now when I get a new record and if I have already checked that col3 value in Table Z, then I don't really need to check it again?|||Clear as mud...what does that have to do with adding and updating rows?

And what are you checking col3 for?|||what does that have to do with adding and updating rows?
Well every row that is added/updated has the status of a new record. And all new records have to be processed.

And what are you checking col3 for?
I am checking if the col3 value of the new record exist in this other table called Table Z. If it exist then I spit out the contents of Table 99 and Table Z into another table say X. Now my client looks to see the degree of similarity in col3 and the column in Table Z. For instance - col3 value is 'j' and the column in Table Z is 'jo'

Now for a new record which has the same value of Col3 as an old/delted record that was spit out then I have many duplicate values of Col3 (but different primary key) in X. Now the client has to check the same value of Col3 again for degree of similarity with Table Z.

I know it's a bit crazy but that's how the data is. Let me know what you think.|||I;m thinking I'm glad I'm not you...

But a previous post by someone a good idea...

When you add or update a record, tag it with an indicator NEW_IND for example, the do you're comparison step where the ind = 'Y' or whatever...then when you're donme set them all bacl to N

What'chyou think?|||Well if I tag it with IND = 'Y' and do the comparison then isn't it possible that Col3 of the record with IND = 'Y' would have been compared before?|||You cannot delete the old records AND retain knowledge of the old record's values. You must retain a knowledge of what the old the old values were.

You probably need an intermediate table indicating which of the possible values you have already processed, or implement some ki9nd of flagging system such that you identify the records to be deleted, insert the new records, compare the col3 values and then delete the flagged records.|||I appologize for the tone of my previous message. I did not intend for it to sound aggressive.

I had to address a similar problem to create change files (LDIFs) for changes to an X500 directory. Our solution was to mirror the table structure and do a comparison of changes based off of the key values for the appropriate tables.

Prior to import (or live data manipulation) the two instances are identical. A series of EXISTS / NOT EXISTS comparisons returns the newly added or deleted records. These changses are passed to the ADD/DELETE tables.

At the field level we passed the field names with an INNER JOIN fo the PK to look for field level changes. These changes were passed to a MODIFY table.

I am sure that you can use a variation of this logic to solve your problem.

Wednesday, March 28, 2012

Recovery mode "SIMPLE" & SP2

Recovery mode "SIMPLE" & SP2

Friday, March 23, 2012

Recovering SQL 2000 Database without the Log File not in clean disconnected state

Recovering SQL 2000 Database without the Log File not in clean disconnected state

Recovering SQL 2000 Database without the Log File not in clean disconnected state

Recovering MDF file! Need Assistance!!

Recovering DBs with a "Suspect" state

Saturday, February 25, 2012

Records comparsion

Record Locks

Blog Archive

About Me