Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 [SQLSTATE 01000] (Message 2528)

Author  Topic 

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-09-16 : 17:50:41
Hi every one ,

we have a job that creates new partions on the databse everyday.
The job runs well during the weekdays but recently every saturday its failing and this is the error:

Executed as user: EXCHANGE\customer. ...) ==> Reindex partition 6 [SQLSTATE 01000] (Message 0) Running reindex task [SQLSTATE 01000] (Message 0) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQ... The step failed.

basically we have a back up running on the saturday at 2 pm for the entire server of which this databse is a part.

So what i infer is the since we are doing the backup on saturday 2pm , the transaction log size must be shrinking and the weekdays the job is not having any problems and on saturday the job runs at 3 am by this time the transactions log must have reached max size and hence its failing.... is my inference correct?

Thanks in advance...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-16 : 20:20:29
I don't quite understand. How often do you run transaction log backups?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-17 : 03:58:42
Backups don't shrink the log. Full backups do nothing to the log, log backups make the space in the log reusable, nothing more.

What recovery model are you in, what backups are you running?

--
Gail Shaw
SQL Server MVP
Go to Top of Page

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-09-17 : 09:35:10
thank you for your responses.

we are using symantec backup exec and since i am new to databse back ups i of impression that even the transaction logs are backed up there by shrinking the size of it.
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-17 : 14:47:31
No idea whether Backup Exec does the logs or not, check the settings on your installation.

Full backups are completely different from transaction log backups. A full backup does back part of the log up, but it does not truncate it and it does not shrink it. Log backups truncate the log, allowing the space in the log to be reused, but they do not shrink the log file.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-09-17 : 16:56:31
hi gila

yes our back exec backs even the logs and sorry in my problem i mentioned 'shrinks' the size but what i meant was that the commited transactions in it are truncated so that the spaces is used by new transactions.

ya but still can you throw somelight on why we are getting the error only on weekends
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-18 : 04:29:20
Index rebuilds right before the error? Those are fully logged in full recovery and can make the log very, very big.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-09-18 : 10:06:35
thanks again gila,

what do you mean by full recovery .... are you referencing the backup exec . if yes, that does not start till 2pm saturday and this job fails at 1:00 am..

Sorry if i understood you wrongly...
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-18 : 11:32:59
Full recovery is one of the three recovery models that the database can be in. It's got nothing to do with Backup Exec. Log backups can only be done in full recovery or bulk-logged recovery, so you have to be in one of those.

The error that you initially posted comes from an index rebuild job, so I'm guessing that the index rebuild is filling the log. It's very easy for that to happen as index rebuilds are fully logged if the DB is in full recovery. Can't say for sure as the complete error message isn't there, it got cut off by the maintenance plan

--
Gail Shaw
SQL Server MVP
Go to Top of Page

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-09-21 : 17:45:08
thanks gila for the explaination.

Can yous suggest one solution that i should follow or test doing that.

Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-22 : 03:04:38
Solution for what, the log filling? First I'd like to see the entire error message. Can you enable the job to log output to a file and post the entire error next time you get it?

--
Gail Shaw
SQL Server MVP
Go to Top of Page

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-09-24 : 10:06:31
thanks gila for your responses..right now we are stuck with something else..i will keep you posted once i come back to this issue .
Go to Top of Page
   

- Advertisement -