| 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 |
|
|
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 ShawSQL Server MVP |
 |
|
|
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. |
|
|
|
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 ShawSQL Server MVP |
|
|
|
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 |
|
|
|
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 ShawSQL Server MVP |
|
|
|
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... |
|
|
|
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 ShawSQL Server MVP |
|
|
|
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. |
|
|
|
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 ShawSQL Server MVP |
|
|
|
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 . |
|
|
|
|
[SQLSTATE 01000] (Message 2528)