Hi,
Thank you for your help in advance...
We are running SQL 2K Sp3. For some reason, the maintenance plan (both
backup and optimize) stopped running. I made sure the SQL agent running but
the job never ran successfully. It ran fine before for long time but just
stopped running recently.
I did some tests by setting a maintenanace plan just running a simple db
backup (no transaction log included). it still failed. The error in the
report is " ODBC SQLState:42000" Error 4064...... Cannot open user default
database. Login failed.
However, I setup some backup jobs to backup both db and transaction log by
NOT using maintenance plan. Those work fine.
Do you know what I should look into to fix the problem?
Hope you can help.
Thank you very much for your help.
Bobson
Hi Bobson,
That should be very easy to fix. Probably the job is running under a user
that has an incorrect default database. Perhaps the default database was
deleted or the user does not have access to it. Change the default database
for this user.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Bobson" wrote:
> Hi,
> Thank you for your help in advance...
> We are running SQL 2K Sp3. For some reason, the maintenance plan (both
> backup and optimize) stopped running. I made sure the SQL agent running but
> the job never ran successfully. It ran fine before for long time but just
> stopped running recently.
> I did some tests by setting a maintenanace plan just running a simple db
> backup (no transaction log included). it still failed. The error in the
> report is " ODBC SQLState:42000" Error 4064...... Cannot open user default
> database. Login failed.
> However, I setup some backup jobs to backup both db and transaction log by
> NOT using maintenance plan. Those work fine.
> Do you know what I should look into to fix the problem?
> Hope you can help.
> Thank you very much for your help.
> Bobson
|||Hi Ben,
Thank you very much for your response. After reading your response, I
checked into that but still has no clue.
sa - is default to "master" db. It has full access to it and other dbs.
lsadmin - is the Domain admin account I logged onto the Windows server. It
has full admin right on the local servers and access right to two dbs, ch and
pd.
I tried to run the maintenance jobs on master, ch and pd...but no luck. I
also treid to set up a test maintenance job to backup just the Master db...
but failed as well.
What else should I check ?
Thank you agian for your help.
Bobson
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Hi Bobson,
> That should be very easy to fix. Probably the job is running under a user
> that has an incorrect default database. Perhaps the default database was
> deleted or the user does not have access to it. Change the default database
> for this user.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Bobson" wrote:
|||Try this:
Right-click the job that is not working. Select View Job History, Show step
details. See the steps and look for 'Executed as user: x' or 'The Job was
invoked by User x'. Check the account under which the SQL Server Agent is
running. Also, on SQL Server Agent Properties go to the Connection tab and
see the account used under 'SQL Server connection'.
Then verify that these logins have a valid default database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Bobson" wrote:
[vbcol=seagreen]
> Hi Ben,
> Thank you very much for your response. After reading your response, I
> checked into that but still has no clue.
> sa - is default to "master" db. It has full access to it and other dbs.
> lsadmin - is the Domain admin account I logged onto the Windows server. It
> has full admin right on the local servers and access right to two dbs, ch and
> pd.
> I tried to run the maintenance jobs on master, ch and pd...but no luck. I
> also treid to set up a test maintenance job to backup just the Master db...
> but failed as well.
> What else should I check ?
> Thank you agian for your help.
> Bobson
>
>
> "Ben Nevarez" wrote:
|||Hi Ben,
Good morning. Thank you for your response again!
I just checked it...in the Connection tab. It was pointing to Windows
Authenticaitons...so I change it to use SA account instead. And tried to run
the job again. It works!!
Again, thank you very much for your help. Happy holidays to you and your
family!!
Bobson
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Try this:
> Right-click the job that is not working. Select View Job History, Show step
> details. See the steps and look for 'Executed as user: x' or 'The Job was
> invoked by User x'. Check the account under which the SQL Server Agent is
> running. Also, on SQL Server Agent Properties go to the Connection tab and
> see the account used under 'SQL Server connection'.
> Then verify that these logins have a valid default database.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Bobson" wrote:
|||Hi Bobson,
I am glad you fixed the problem.
Happy holidays!
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Bobson" wrote:
[vbcol=seagreen]
> Hi Ben,
> Good morning. Thank you for your response again!
> I just checked it...in the Connection tab. It was pointing to Windows
> Authenticaitons...so I change it to use SA account instead. And tried to run
> the job again. It works!!
> Again, thank you very much for your help. Happy holidays to you and your
> family!!
> Bobson
> "Ben Nevarez" wrote:
Showing posts with label optimize. Show all posts
Showing posts with label optimize. Show all posts
Wednesday, March 21, 2012
Failed on runnning maintenance backup plan but....
Hi,
Thank you for your help in advance...
We are running SQL 2K Sp3. For some reason, the maintenance plan (both
backup and optimize) stopped running. I made sure the SQL agent running but
the job never ran successfully. It ran fine before for long time but just
stopped running recently.
I did some tests by setting a maintenanace plan just running a simple db
backup (no transaction log included). it still failed. The error in the
report is " ODBC SQLState:42000" Error 4064...... Cannot open user default
database. Login failed.
However, I setup some backup jobs to backup both db and transaction log by
NOT using maintenance plan. Those work fine.
Do you know what I should look into to fix the problem?
Hope you can help.
Thank you very much for your help.
BobsonHi Bobson,
That should be very easy to fix. Probably the job is running under a user
that has an incorrect default database. Perhaps the default database was
deleted or the user does not have access to it. Change the default database
for this user.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Bobson" wrote:
> Hi,
> Thank you for your help in advance...
> We are running SQL 2K Sp3. For some reason, the maintenance plan (both
> backup and optimize) stopped running. I made sure the SQL agent running but
> the job never ran successfully. It ran fine before for long time but just
> stopped running recently.
> I did some tests by setting a maintenanace plan just running a simple db
> backup (no transaction log included). it still failed. The error in the
> report is " ODBC SQLState:42000" Error 4064...... Cannot open user default
> database. Login failed.
> However, I setup some backup jobs to backup both db and transaction log by
> NOT using maintenance plan. Those work fine.
> Do you know what I should look into to fix the problem?
> Hope you can help.
> Thank you very much for your help.
> Bobson|||Hi Ben,
Thank you very much for your response. After reading your response, I
checked into that but still has no clue.
sa - is default to "master" db. It has full access to it and other dbs.
lsadmin - is the Domain admin account I logged onto the Windows server. It
has full admin right on the local servers and access right to two dbs, ch and
pd.
I tried to run the maintenance jobs on master, ch and pd...but no luck. I
also treid to set up a test maintenance job to backup just the Master db...
but failed as well.
What else should I check ?
Thank you agian for your help.
Bobson
"Ben Nevarez" wrote:
> Hi Bobson,
> That should be very easy to fix. Probably the job is running under a user
> that has an incorrect default database. Perhaps the default database was
> deleted or the user does not have access to it. Change the default database
> for this user.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Bobson" wrote:
> > Hi,
> >
> > Thank you for your help in advance...
> >
> > We are running SQL 2K Sp3. For some reason, the maintenance plan (both
> > backup and optimize) stopped running. I made sure the SQL agent running but
> > the job never ran successfully. It ran fine before for long time but just
> > stopped running recently.
> >
> > I did some tests by setting a maintenanace plan just running a simple db
> > backup (no transaction log included). it still failed. The error in the
> > report is " ODBC SQLState:42000" Error 4064...... Cannot open user default
> > database. Login failed.
> >
> > However, I setup some backup jobs to backup both db and transaction log by
> > NOT using maintenance plan. Those work fine.
> >
> > Do you know what I should look into to fix the problem?
> >
> > Hope you can help.
> >
> > Thank you very much for your help.
> >
> > Bobson|||Try this:
Right-click the job that is not working. Select View Job History, Show step
details. See the steps and look for 'Executed as user: x' or 'The Job was
invoked by User x'. Check the account under which the SQL Server Agent is
running. Also, on SQL Server Agent Properties go to the Connection tab and
see the account used under 'SQL Server connection'.
Then verify that these logins have a valid default database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Bobson" wrote:
> Hi Ben,
> Thank you very much for your response. After reading your response, I
> checked into that but still has no clue.
> sa - is default to "master" db. It has full access to it and other dbs.
> lsadmin - is the Domain admin account I logged onto the Windows server. It
> has full admin right on the local servers and access right to two dbs, ch and
> pd.
> I tried to run the maintenance jobs on master, ch and pd...but no luck. I
> also treid to set up a test maintenance job to backup just the Master db...
> but failed as well.
> What else should I check ?
> Thank you agian for your help.
> Bobson
>
>
> "Ben Nevarez" wrote:
> >
> > Hi Bobson,
> >
> > That should be very easy to fix. Probably the job is running under a user
> > that has an incorrect default database. Perhaps the default database was
> > deleted or the user does not have access to it. Change the default database
> > for this user.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "Bobson" wrote:
> >
> > > Hi,
> > >
> > > Thank you for your help in advance...
> > >
> > > We are running SQL 2K Sp3. For some reason, the maintenance plan (both
> > > backup and optimize) stopped running. I made sure the SQL agent running but
> > > the job never ran successfully. It ran fine before for long time but just
> > > stopped running recently.
> > >
> > > I did some tests by setting a maintenanace plan just running a simple db
> > > backup (no transaction log included). it still failed. The error in the
> > > report is " ODBC SQLState:42000" Error 4064...... Cannot open user default
> > > database. Login failed.
> > >
> > > However, I setup some backup jobs to backup both db and transaction log by
> > > NOT using maintenance plan. Those work fine.
> > >
> > > Do you know what I should look into to fix the problem?
> > >
> > > Hope you can help.
> > >
> > > Thank you very much for your help.
> > >
> > > Bobson|||Hi Ben,
Good morning. Thank you for your response again!
I just checked it...in the Connection tab. It was pointing to Windows
Authenticaitons...so I change it to use SA account instead. And tried to run
the job again. It works!!
Again, thank you very much for your help. Happy holidays to you and your
family!!
Bobson
"Ben Nevarez" wrote:
> Try this:
> Right-click the job that is not working. Select View Job History, Show step
> details. See the steps and look for 'Executed as user: x' or 'The Job was
> invoked by User x'. Check the account under which the SQL Server Agent is
> running. Also, on SQL Server Agent Properties go to the Connection tab and
> see the account used under 'SQL Server connection'.
> Then verify that these logins have a valid default database.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Bobson" wrote:
> > Hi Ben,
> >
> > Thank you very much for your response. After reading your response, I
> > checked into that but still has no clue.
> >
> > sa - is default to "master" db. It has full access to it and other dbs.
> > lsadmin - is the Domain admin account I logged onto the Windows server. It
> > has full admin right on the local servers and access right to two dbs, ch and
> > pd.
> >
> > I tried to run the maintenance jobs on master, ch and pd...but no luck. I
> > also treid to set up a test maintenance job to backup just the Master db...
> > but failed as well.
> >
> > What else should I check ?
> >
> > Thank you agian for your help.
> >
> > Bobson
> >
> >
> >
> >
> > "Ben Nevarez" wrote:
> >
> > >
> > > Hi Bobson,
> > >
> > > That should be very easy to fix. Probably the job is running under a user
> > > that has an incorrect default database. Perhaps the default database was
> > > deleted or the user does not have access to it. Change the default database
> > > for this user.
> > >
> > > Hope this helps,
> > >
> > > Ben Nevarez
> > > Senior Database Administrator
> > > AIG SunAmerica
> > >
> > >
> > >
> > > "Bobson" wrote:
> > >
> > > > Hi,
> > > >
> > > > Thank you for your help in advance...
> > > >
> > > > We are running SQL 2K Sp3. For some reason, the maintenance plan (both
> > > > backup and optimize) stopped running. I made sure the SQL agent running but
> > > > the job never ran successfully. It ran fine before for long time but just
> > > > stopped running recently.
> > > >
> > > > I did some tests by setting a maintenanace plan just running a simple db
> > > > backup (no transaction log included). it still failed. The error in the
> > > > report is " ODBC SQLState:42000" Error 4064...... Cannot open user default
> > > > database. Login failed.
> > > >
> > > > However, I setup some backup jobs to backup both db and transaction log by
> > > > NOT using maintenance plan. Those work fine.
> > > >
> > > > Do you know what I should look into to fix the problem?
> > > >
> > > > Hope you can help.
> > > >
> > > > Thank you very much for your help.
> > > >
> > > > Bobson|||Hi Bobson,
I am glad you fixed the problem.
Happy holidays!
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Bobson" wrote:
> Hi Ben,
> Good morning. Thank you for your response again!
> I just checked it...in the Connection tab. It was pointing to Windows
> Authenticaitons...so I change it to use SA account instead. And tried to run
> the job again. It works!!
> Again, thank you very much for your help. Happy holidays to you and your
> family!!
> Bobson
> "Ben Nevarez" wrote:
> >
> > Try this:
> >
> > Right-click the job that is not working. Select View Job History, Show step
> > details. See the steps and look for 'Executed as user: x' or 'The Job was
> > invoked by User x'. Check the account under which the SQL Server Agent is
> > running. Also, on SQL Server Agent Properties go to the Connection tab and
> > see the account used under 'SQL Server connection'.
> >
> > Then verify that these logins have a valid default database.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "Bobson" wrote:
> >
> > > Hi Ben,
> > >
> > > Thank you very much for your response. After reading your response, I
> > > checked into that but still has no clue.
> > >
> > > sa - is default to "master" db. It has full access to it and other dbs.
> > > lsadmin - is the Domain admin account I logged onto the Windows server. It
> > > has full admin right on the local servers and access right to two dbs, ch and
> > > pd.
> > >
> > > I tried to run the maintenance jobs on master, ch and pd...but no luck. I
> > > also treid to set up a test maintenance job to backup just the Master db...
> > > but failed as well.
> > >
> > > What else should I check ?
> > >
> > > Thank you agian for your help.
> > >
> > > Bobson
> > >
> > >
> > >
> > >
> > > "Ben Nevarez" wrote:
> > >
> > > >
> > > > Hi Bobson,
> > > >
> > > > That should be very easy to fix. Probably the job is running under a user
> > > > that has an incorrect default database. Perhaps the default database was
> > > > deleted or the user does not have access to it. Change the default database
> > > > for this user.
> > > >
> > > > Hope this helps,
> > > >
> > > > Ben Nevarez
> > > > Senior Database Administrator
> > > > AIG SunAmerica
> > > >
> > > >
> > > >
> > > > "Bobson" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > Thank you for your help in advance...
> > > > >
> > > > > We are running SQL 2K Sp3. For some reason, the maintenance plan (both
> > > > > backup and optimize) stopped running. I made sure the SQL agent running but
> > > > > the job never ran successfully. It ran fine before for long time but just
> > > > > stopped running recently.
> > > > >
> > > > > I did some tests by setting a maintenanace plan just running a simple db
> > > > > backup (no transaction log included). it still failed. The error in the
> > > > > report is " ODBC SQLState:42000" Error 4064...... Cannot open user default
> > > > > database. Login failed.
> > > > >
> > > > > However, I setup some backup jobs to backup both db and transaction log by
> > > > > NOT using maintenance plan. Those work fine.
> > > > >
> > > > > Do you know what I should look into to fix the problem?
> > > > >
> > > > > Hope you can help.
> > > > >
> > > > > Thank you very much for your help.
> > > > >
> > > > > Bobson
Thank you for your help in advance...
We are running SQL 2K Sp3. For some reason, the maintenance plan (both
backup and optimize) stopped running. I made sure the SQL agent running but
the job never ran successfully. It ran fine before for long time but just
stopped running recently.
I did some tests by setting a maintenanace plan just running a simple db
backup (no transaction log included). it still failed. The error in the
report is " ODBC SQLState:42000" Error 4064...... Cannot open user default
database. Login failed.
However, I setup some backup jobs to backup both db and transaction log by
NOT using maintenance plan. Those work fine.
Do you know what I should look into to fix the problem?
Hope you can help.
Thank you very much for your help.
BobsonHi Bobson,
That should be very easy to fix. Probably the job is running under a user
that has an incorrect default database. Perhaps the default database was
deleted or the user does not have access to it. Change the default database
for this user.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Bobson" wrote:
> Hi,
> Thank you for your help in advance...
> We are running SQL 2K Sp3. For some reason, the maintenance plan (both
> backup and optimize) stopped running. I made sure the SQL agent running but
> the job never ran successfully. It ran fine before for long time but just
> stopped running recently.
> I did some tests by setting a maintenanace plan just running a simple db
> backup (no transaction log included). it still failed. The error in the
> report is " ODBC SQLState:42000" Error 4064...... Cannot open user default
> database. Login failed.
> However, I setup some backup jobs to backup both db and transaction log by
> NOT using maintenance plan. Those work fine.
> Do you know what I should look into to fix the problem?
> Hope you can help.
> Thank you very much for your help.
> Bobson|||Hi Ben,
Thank you very much for your response. After reading your response, I
checked into that but still has no clue.
sa - is default to "master" db. It has full access to it and other dbs.
lsadmin - is the Domain admin account I logged onto the Windows server. It
has full admin right on the local servers and access right to two dbs, ch and
pd.
I tried to run the maintenance jobs on master, ch and pd...but no luck. I
also treid to set up a test maintenance job to backup just the Master db...
but failed as well.
What else should I check ?
Thank you agian for your help.
Bobson
"Ben Nevarez" wrote:
> Hi Bobson,
> That should be very easy to fix. Probably the job is running under a user
> that has an incorrect default database. Perhaps the default database was
> deleted or the user does not have access to it. Change the default database
> for this user.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Bobson" wrote:
> > Hi,
> >
> > Thank you for your help in advance...
> >
> > We are running SQL 2K Sp3. For some reason, the maintenance plan (both
> > backup and optimize) stopped running. I made sure the SQL agent running but
> > the job never ran successfully. It ran fine before for long time but just
> > stopped running recently.
> >
> > I did some tests by setting a maintenanace plan just running a simple db
> > backup (no transaction log included). it still failed. The error in the
> > report is " ODBC SQLState:42000" Error 4064...... Cannot open user default
> > database. Login failed.
> >
> > However, I setup some backup jobs to backup both db and transaction log by
> > NOT using maintenance plan. Those work fine.
> >
> > Do you know what I should look into to fix the problem?
> >
> > Hope you can help.
> >
> > Thank you very much for your help.
> >
> > Bobson|||Try this:
Right-click the job that is not working. Select View Job History, Show step
details. See the steps and look for 'Executed as user: x' or 'The Job was
invoked by User x'. Check the account under which the SQL Server Agent is
running. Also, on SQL Server Agent Properties go to the Connection tab and
see the account used under 'SQL Server connection'.
Then verify that these logins have a valid default database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Bobson" wrote:
> Hi Ben,
> Thank you very much for your response. After reading your response, I
> checked into that but still has no clue.
> sa - is default to "master" db. It has full access to it and other dbs.
> lsadmin - is the Domain admin account I logged onto the Windows server. It
> has full admin right on the local servers and access right to two dbs, ch and
> pd.
> I tried to run the maintenance jobs on master, ch and pd...but no luck. I
> also treid to set up a test maintenance job to backup just the Master db...
> but failed as well.
> What else should I check ?
> Thank you agian for your help.
> Bobson
>
>
> "Ben Nevarez" wrote:
> >
> > Hi Bobson,
> >
> > That should be very easy to fix. Probably the job is running under a user
> > that has an incorrect default database. Perhaps the default database was
> > deleted or the user does not have access to it. Change the default database
> > for this user.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "Bobson" wrote:
> >
> > > Hi,
> > >
> > > Thank you for your help in advance...
> > >
> > > We are running SQL 2K Sp3. For some reason, the maintenance plan (both
> > > backup and optimize) stopped running. I made sure the SQL agent running but
> > > the job never ran successfully. It ran fine before for long time but just
> > > stopped running recently.
> > >
> > > I did some tests by setting a maintenanace plan just running a simple db
> > > backup (no transaction log included). it still failed. The error in the
> > > report is " ODBC SQLState:42000" Error 4064...... Cannot open user default
> > > database. Login failed.
> > >
> > > However, I setup some backup jobs to backup both db and transaction log by
> > > NOT using maintenance plan. Those work fine.
> > >
> > > Do you know what I should look into to fix the problem?
> > >
> > > Hope you can help.
> > >
> > > Thank you very much for your help.
> > >
> > > Bobson|||Hi Ben,
Good morning. Thank you for your response again!
I just checked it...in the Connection tab. It was pointing to Windows
Authenticaitons...so I change it to use SA account instead. And tried to run
the job again. It works!!
Again, thank you very much for your help. Happy holidays to you and your
family!!
Bobson
"Ben Nevarez" wrote:
> Try this:
> Right-click the job that is not working. Select View Job History, Show step
> details. See the steps and look for 'Executed as user: x' or 'The Job was
> invoked by User x'. Check the account under which the SQL Server Agent is
> running. Also, on SQL Server Agent Properties go to the Connection tab and
> see the account used under 'SQL Server connection'.
> Then verify that these logins have a valid default database.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Bobson" wrote:
> > Hi Ben,
> >
> > Thank you very much for your response. After reading your response, I
> > checked into that but still has no clue.
> >
> > sa - is default to "master" db. It has full access to it and other dbs.
> > lsadmin - is the Domain admin account I logged onto the Windows server. It
> > has full admin right on the local servers and access right to two dbs, ch and
> > pd.
> >
> > I tried to run the maintenance jobs on master, ch and pd...but no luck. I
> > also treid to set up a test maintenance job to backup just the Master db...
> > but failed as well.
> >
> > What else should I check ?
> >
> > Thank you agian for your help.
> >
> > Bobson
> >
> >
> >
> >
> > "Ben Nevarez" wrote:
> >
> > >
> > > Hi Bobson,
> > >
> > > That should be very easy to fix. Probably the job is running under a user
> > > that has an incorrect default database. Perhaps the default database was
> > > deleted or the user does not have access to it. Change the default database
> > > for this user.
> > >
> > > Hope this helps,
> > >
> > > Ben Nevarez
> > > Senior Database Administrator
> > > AIG SunAmerica
> > >
> > >
> > >
> > > "Bobson" wrote:
> > >
> > > > Hi,
> > > >
> > > > Thank you for your help in advance...
> > > >
> > > > We are running SQL 2K Sp3. For some reason, the maintenance plan (both
> > > > backup and optimize) stopped running. I made sure the SQL agent running but
> > > > the job never ran successfully. It ran fine before for long time but just
> > > > stopped running recently.
> > > >
> > > > I did some tests by setting a maintenanace plan just running a simple db
> > > > backup (no transaction log included). it still failed. The error in the
> > > > report is " ODBC SQLState:42000" Error 4064...... Cannot open user default
> > > > database. Login failed.
> > > >
> > > > However, I setup some backup jobs to backup both db and transaction log by
> > > > NOT using maintenance plan. Those work fine.
> > > >
> > > > Do you know what I should look into to fix the problem?
> > > >
> > > > Hope you can help.
> > > >
> > > > Thank you very much for your help.
> > > >
> > > > Bobson|||Hi Bobson,
I am glad you fixed the problem.
Happy holidays!
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Bobson" wrote:
> Hi Ben,
> Good morning. Thank you for your response again!
> I just checked it...in the Connection tab. It was pointing to Windows
> Authenticaitons...so I change it to use SA account instead. And tried to run
> the job again. It works!!
> Again, thank you very much for your help. Happy holidays to you and your
> family!!
> Bobson
> "Ben Nevarez" wrote:
> >
> > Try this:
> >
> > Right-click the job that is not working. Select View Job History, Show step
> > details. See the steps and look for 'Executed as user: x' or 'The Job was
> > invoked by User x'. Check the account under which the SQL Server Agent is
> > running. Also, on SQL Server Agent Properties go to the Connection tab and
> > see the account used under 'SQL Server connection'.
> >
> > Then verify that these logins have a valid default database.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "Bobson" wrote:
> >
> > > Hi Ben,
> > >
> > > Thank you very much for your response. After reading your response, I
> > > checked into that but still has no clue.
> > >
> > > sa - is default to "master" db. It has full access to it and other dbs.
> > > lsadmin - is the Domain admin account I logged onto the Windows server. It
> > > has full admin right on the local servers and access right to two dbs, ch and
> > > pd.
> > >
> > > I tried to run the maintenance jobs on master, ch and pd...but no luck. I
> > > also treid to set up a test maintenance job to backup just the Master db...
> > > but failed as well.
> > >
> > > What else should I check ?
> > >
> > > Thank you agian for your help.
> > >
> > > Bobson
> > >
> > >
> > >
> > >
> > > "Ben Nevarez" wrote:
> > >
> > > >
> > > > Hi Bobson,
> > > >
> > > > That should be very easy to fix. Probably the job is running under a user
> > > > that has an incorrect default database. Perhaps the default database was
> > > > deleted or the user does not have access to it. Change the default database
> > > > for this user.
> > > >
> > > > Hope this helps,
> > > >
> > > > Ben Nevarez
> > > > Senior Database Administrator
> > > > AIG SunAmerica
> > > >
> > > >
> > > >
> > > > "Bobson" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > Thank you for your help in advance...
> > > > >
> > > > > We are running SQL 2K Sp3. For some reason, the maintenance plan (both
> > > > > backup and optimize) stopped running. I made sure the SQL agent running but
> > > > > the job never ran successfully. It ran fine before for long time but just
> > > > > stopped running recently.
> > > > >
> > > > > I did some tests by setting a maintenanace plan just running a simple db
> > > > > backup (no transaction log included). it still failed. The error in the
> > > > > report is " ODBC SQLState:42000" Error 4064...... Cannot open user default
> > > > > database. Login failed.
> > > > >
> > > > > However, I setup some backup jobs to backup both db and transaction log by
> > > > > NOT using maintenance plan. Those work fine.
> > > > >
> > > > > Do you know what I should look into to fix the problem?
> > > > >
> > > > > Hope you can help.
> > > > >
> > > > > Thank you very much for your help.
> > > > >
> > > > > Bobson
Failed on runnning maintenance backup plan but....
Hi,
Thank you for your help in advance...
We are running SQL 2K Sp3. For some reason, the maintenance plan (both
backup and optimize) stopped running. I made sure the SQL agent running but
the job never ran successfully. It ran fine before for long time but just
stopped running recently.
I did some tests by setting a maintenanace plan just running a simple db
backup (no transaction log included). it still failed. The error in the
report is " ODBC SQLState:42000" Error 4064...... Cannot open user default
database. Login failed.
However, I setup some backup jobs to backup both db and transaction log by
NOT using maintenance plan. Those work fine.
Do you know what I should look into to fix the problem?
Hope you can help.
Thank you very much for your help.
BobsonHi Bobson,
That should be very easy to fix. Probably the job is running under a user
that has an incorrect default database. Perhaps the default database was
deleted or the user does not have access to it. Change the default database
for this user.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Bobson" wrote:
> Hi,
> Thank you for your help in advance...
> We are running SQL 2K Sp3. For some reason, the maintenance plan (both
> backup and optimize) stopped running. I made sure the SQL agent running bu
t
> the job never ran successfully. It ran fine before for long time but just
> stopped running recently.
> I did some tests by setting a maintenanace plan just running a simple db
> backup (no transaction log included). it still failed. The error in the
> report is " ODBC SQLState:42000" Error 4064...... Cannot open user defaul
t
> database. Login failed.
> However, I setup some backup jobs to backup both db and transaction log by
> NOT using maintenance plan. Those work fine.
> Do you know what I should look into to fix the problem?
> Hope you can help.
> Thank you very much for your help.
> Bobson|||Hi Ben,
Thank you very much for your response. After reading your response, I
checked into that but still has no clue.
sa - is default to "master" db. It has full access to it and other dbs.
lsadmin - is the Domain admin account I logged onto the Windows server. It
has full admin right on the local servers and access right to two dbs, ch an
d
pd.
I tried to run the maintenance jobs on master, ch and pd...but no luck. I
also treid to set up a test maintenance job to backup just the Master db...
but failed as well.
What else should I check ?
Thank you agian for your help.
Bobson
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Hi Bobson,
> That should be very easy to fix. Probably the job is running under a user
> that has an incorrect default database. Perhaps the default database was
> deleted or the user does not have access to it. Change the default databas
e
> for this user.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Bobson" wrote:
>|||Try this:
Right-click the job that is not working. Select View Job History, Show step
details. See the steps and look for 'Executed as user: x' or 'The Job was
invoked by User x'. Check the account under which the SQL Server Agent is
running. Also, on SQL Server Agent Properties go to the Connection tab and
see the account used under 'SQL Server connection'.
Then verify that these logins have a valid default database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Bobson" wrote:
[vbcol=seagreen]
> Hi Ben,
> Thank you very much for your response. After reading your response, I
> checked into that but still has no clue.
> sa - is default to "master" db. It has full access to it and other dbs.
> lsadmin - is the Domain admin account I logged onto the Windows server. It
> has full admin right on the local servers and access right to two dbs, ch
and
> pd.
> I tried to run the maintenance jobs on master, ch and pd...but no luck. I
> also treid to set up a test maintenance job to backup just the Master db..
.
> but failed as well.
> What else should I check ?
> Thank you agian for your help.
> Bobson
>
>
> "Ben Nevarez" wrote:
>|||Hi Ben,
Good morning. Thank you for your response again!
I just checked it...in the Connection tab. It was pointing to Windows
Authenticaitons...so I change it to use SA account instead. And tried to run
the job again. It works!!
Again, thank you very much for your help. Happy holidays to you and your
family!!
Bobson
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Try this:
> Right-click the job that is not working. Select View Job History, Show ste
p
> details. See the steps and look for 'Executed as user: x' or 'The Job was
> invoked by User x'. Check the account under which the SQL Server Agent is
> running. Also, on SQL Server Agent Properties go to the Connection tab and
> see the account used under 'SQL Server connection'.
> Then verify that these logins have a valid default database.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Bobson" wrote:
>|||Hi Bobson,
I am glad you fixed the problem.
Happy holidays!
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Bobson" wrote:
[vbcol=seagreen]
> Hi Ben,
> Good morning. Thank you for your response again!
> I just checked it...in the Connection tab. It was pointing to Windows
> Authenticaitons...so I change it to use SA account instead. And tried to r
un
> the job again. It works!!
> Again, thank you very much for your help. Happy holidays to you and your
> family!!
> Bobson
> "Ben Nevarez" wrote:
>sql
Thank you for your help in advance...
We are running SQL 2K Sp3. For some reason, the maintenance plan (both
backup and optimize) stopped running. I made sure the SQL agent running but
the job never ran successfully. It ran fine before for long time but just
stopped running recently.
I did some tests by setting a maintenanace plan just running a simple db
backup (no transaction log included). it still failed. The error in the
report is " ODBC SQLState:42000" Error 4064...... Cannot open user default
database. Login failed.
However, I setup some backup jobs to backup both db and transaction log by
NOT using maintenance plan. Those work fine.
Do you know what I should look into to fix the problem?
Hope you can help.
Thank you very much for your help.
BobsonHi Bobson,
That should be very easy to fix. Probably the job is running under a user
that has an incorrect default database. Perhaps the default database was
deleted or the user does not have access to it. Change the default database
for this user.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Bobson" wrote:
> Hi,
> Thank you for your help in advance...
> We are running SQL 2K Sp3. For some reason, the maintenance plan (both
> backup and optimize) stopped running. I made sure the SQL agent running bu
t
> the job never ran successfully. It ran fine before for long time but just
> stopped running recently.
> I did some tests by setting a maintenanace plan just running a simple db
> backup (no transaction log included). it still failed. The error in the
> report is " ODBC SQLState:42000" Error 4064...... Cannot open user defaul
t
> database. Login failed.
> However, I setup some backup jobs to backup both db and transaction log by
> NOT using maintenance plan. Those work fine.
> Do you know what I should look into to fix the problem?
> Hope you can help.
> Thank you very much for your help.
> Bobson|||Hi Ben,
Thank you very much for your response. After reading your response, I
checked into that but still has no clue.
sa - is default to "master" db. It has full access to it and other dbs.
lsadmin - is the Domain admin account I logged onto the Windows server. It
has full admin right on the local servers and access right to two dbs, ch an
d
pd.
I tried to run the maintenance jobs on master, ch and pd...but no luck. I
also treid to set up a test maintenance job to backup just the Master db...
but failed as well.
What else should I check ?
Thank you agian for your help.
Bobson
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Hi Bobson,
> That should be very easy to fix. Probably the job is running under a user
> that has an incorrect default database. Perhaps the default database was
> deleted or the user does not have access to it. Change the default databas
e
> for this user.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Bobson" wrote:
>|||Try this:
Right-click the job that is not working. Select View Job History, Show step
details. See the steps and look for 'Executed as user: x' or 'The Job was
invoked by User x'. Check the account under which the SQL Server Agent is
running. Also, on SQL Server Agent Properties go to the Connection tab and
see the account used under 'SQL Server connection'.
Then verify that these logins have a valid default database.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Bobson" wrote:
[vbcol=seagreen]
> Hi Ben,
> Thank you very much for your response. After reading your response, I
> checked into that but still has no clue.
> sa - is default to "master" db. It has full access to it and other dbs.
> lsadmin - is the Domain admin account I logged onto the Windows server. It
> has full admin right on the local servers and access right to two dbs, ch
and
> pd.
> I tried to run the maintenance jobs on master, ch and pd...but no luck. I
> also treid to set up a test maintenance job to backup just the Master db..
.
> but failed as well.
> What else should I check ?
> Thank you agian for your help.
> Bobson
>
>
> "Ben Nevarez" wrote:
>|||Hi Ben,
Good morning. Thank you for your response again!
I just checked it...in the Connection tab. It was pointing to Windows
Authenticaitons...so I change it to use SA account instead. And tried to run
the job again. It works!!
Again, thank you very much for your help. Happy holidays to you and your
family!!
Bobson
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Try this:
> Right-click the job that is not working. Select View Job History, Show ste
p
> details. See the steps and look for 'Executed as user: x' or 'The Job was
> invoked by User x'. Check the account under which the SQL Server Agent is
> running. Also, on SQL Server Agent Properties go to the Connection tab and
> see the account used under 'SQL Server connection'.
> Then verify that these logins have a valid default database.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Bobson" wrote:
>|||Hi Bobson,
I am glad you fixed the problem.
Happy holidays!
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Bobson" wrote:
[vbcol=seagreen]
> Hi Ben,
> Good morning. Thank you for your response again!
> I just checked it...in the Connection tab. It was pointing to Windows
> Authenticaitons...so I change it to use SA account instead. And tried to r
un
> the job again. It works!!
> Again, thank you very much for your help. Happy holidays to you and your
> family!!
> Bobson
> "Ben Nevarez" wrote:
>sql
Wednesday, March 7, 2012
Fact Table Optimal Design
I have a working model, but looking to optimize it. Particulary the fact table. Here is how fact table looks:
ProductKey ChargeAKey ChargeBKey ChargeCKey ChargeA$ ChargeB$ ChargeC$ TotalABC$
1 1 0 0 2$ 0 0 2$
1 2 0 0 3$ 0 0 3$
1 0 1 0 0 4$ 0 4$
1 0 0 1 0 0 2$ 2$
1 0 0 2 0 0 3$ 3$
1 0 0 3 0 0 3$ 3$
... now some explanation.. granuality of fact table is Product + ChargeType. each charge type (A, B, C) has it's own dimension table with its own unique list of attributes and they all come from different sources. The simplest loading would append each Product + ChargeX to the table. I was thinking of option b - settingup fact table as Product + ChargeType + ChargeKey + Chargeamount, but this design in my opinion is less friendly then the original because user would have to know what charges have to be filtered down first by type to get to specific key and its attributes... what I don't like about option a is that it takes a lot of space and there are fields populated with only meningless 0 vs having everything compressed into few records. e.g. above exmple could be designed to have only 3 records vs. 6 which translates in huge savings when we are talking millions of records.Would anyone have anyexperience with similiar challenge? I would be able to solve this with complex ETL procedures, but there has to be something much simplier. I am still not sure if this is design or ETL solution... any feedback greatly appreciated.
My general recommendation on dimensional design is to stay focused on your business users. What is the business process, activity, or event this table represents? How would your users describe the facts associated with this process/activity/event?
It appears you have incorporated a dimension (charge type) with three values into your model. If each charge type represents a different event/process/activity, then three fact tables are in order. If this is a single event that could be broken down by charge type (of which there are currently three identified), then a single fact table is required with a charge type dimension. (What happens if a fourth charge type is introduced?)
B.
|||Thank You. That's right I always keep end user in mind because it is for him that data is being presented... and you always think the performance and simplicity. You gave me the answer: "What happens if a fourth charge type is intorduced?".. I will be in a lot of work, if not trouble, to integrate everything into one fact table.. Thanks.
Subscribe to:
Posts (Atom)