Cannot remove push subscription on SQL Server 2000

Hi,

We have created replication from one production server to another backup server by using SQL scripts. These scripts have been generated by SQL Server after manual initial setup. When I ran these scripts they did not add the subscription for the backup server because a stored procedure were missing named something like sp_addsubscription. Therefore I ran the script on the production server and created the subscription manually on the backup server. But I made an error, I created a push subscription first but I should have created a pull subscription. Now I can't delete the push subscription. I don't even get a error message. I can't press delete and when right clicking on it I can only choose Help. The subscription has the following properties:

Type: push
Status: retrying
Last action: The process could not connect to distributor 'PSERV'
Replication type: Snapshot

Anyone know how I can delete this push subscription on my backup server?

Best,
/M

When you right-click on the subscription, are you connected to the production/publisher or backup/subscriber.

Try to do this on the publisher and see if you can delete it.

You can also call sp_dropsubscription (with the appropriate parameters) on the production server to drop the subscription.

|||

Nothing at all exist on the production server. The distribution and the publication has been removed. Only the push subscription exist at the backup server. And I can't delete that.

How can I use sp_dropsubscription. Can I use that from Query Analyzer. Does anyone have a SQL example for that?

Best
/M

|||

If you just want to get rid of replication on the backup server use sp_removedbreplication.

Note that this will remove all types of replication: merge and Transactional.

Use this only if you wish to remove all of replication.

|||

Additionally you can use the following:

sp_subscription_cleanup: for Transactional -- Removes metadata when a subscription is dropped at a Subscriber. For a synchronizing transaction subscription, it also includes immediate-updating triggers. This stored procedure is executed at the Subscriber on the subscription database.

sp_mergesubscription_cleanup: for Merge -- Removes metadata, such as triggers and entries, in sysmergesubscriptions and sysmergearticles after the specified merge push subscription is removed at the Publisher. This stored procedure is run at the Subscriber on the subscription database

|||

I used sp_removedbreplication 'db_name' to remove everything at the backup server. It worked as I wanted...

Thanks very much!

Now, I am happy again... for a short time :)

Best
/M

Cannot perform a differential backup for database

hi seniors ,

I am running sql server 2005 under details

Microsoft SQL Server 2005 - 9.00.3033.00 (X64)

Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit)
on Windows NT 5.2 (Build 3790: Service Pack 1)

When i attempt to take differentail backup i receieved following message

"Cannot perform a differential backup for database "abc",
because a current database backup does not exist.
Perform a full database backup by reissuing BACKUP DATABASE,
omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035)
BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)."

when i search this msg on net than i find that it is microsoft bug 863 so i found hotfixes
about this problem and tried to install but unable to install (On installation screen select feature i am unable to click on check box)

Can any one help me regarding to this issue .And more is.. full back of db "abc" exists and i never changed its location nor rename file name.

Regards

First action of any backup activity should be full backup. For differential backup u should have a Baseline/Full backup. SO first take full backup and the try differential backup

backup database SomeDtabase to Disk='D:\Somefile.bak' with init

the above statment will create the full backup of your database. then do the diffrential backup . Read more in BOL

Madhu

|||

thanxs madhu , When i saw deatils about with INIT...I think it should work now. But one thing more wheather i should use this clasue with differential backup or not...

thanxs again

Ahmad Drshen

|||

it depends .... whether u need to overwrite the backup file or not... its again depend upon the Backup strategy of your server....

Read more about INIT in BOL

Madhu

|||

Hi Madhu K Nair and other Seinors ,

Agian , i am fall in problem

" Cannot perform a differential backup for database "AAA", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).

I have just Taken backup of db "AAA" on friday at 7:30 PM through script

BACKUP DATABASE AAA TO DISK = 'C:\LV_full_AAA_20071702.bkp'

WITH INIT , NOUNLOAD ,

NAME = 'AAAbackup', NOSKIP , STATS = 10,

NOFORMAT

DECLARE @.i INT

select @.i = position from msdb..backupset where database_name='AAA'

and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='AAA')

RESTORE VERIFYONLY FROM DISK = 'C:\LV_full_AAA_20071702.bkp' WITH FILE = @.i

Database backup has done successfully. :)

Again I ran the script to take differentail backup on friday at 11:30 PM through script

BACKUP DATABASE AAA TO DISK = 'C:\LV_Diff_AAA_20071702.bkp'

WITH INIT , NOUNLOAD , DIFFERENTIAL ,

NAME = 'AAAbackup', NOSKIP , STATS = 10,

NOFORMAT

DECLARE @.i INT

select @.i = position from msdb..backupset where database_name='AAA'

and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='AAA')

RESTORE VERIFYONLY FROM DISK = 'C:\LV_Diff_AAA_20071702.bkp' WITH FILE = @.i

Differentail backup has also done successfully.

But on saturaday when the script ran for taking differentail backup of db AAA on 11:30 PM

it repoted follwoing error

" Cannot perform a differential backup for database "AAA", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). "

while these script are working properly on another production server even there is not mention WITH INIT option with full backup.

wat the hell is going in taking backup my this server .One thing more i just installed the SP2 exe name was "SQLServer2005SP2-KB921896-x64-ENU.exe" on my server perhaps i am stucking with this problem after installing this plz let me know wat is the solution to fix this hell.

thanxs in advance

Ahmad Drshen

|||

How recent is your full backup of the database? Have there been any significant changes since then?

This is CTP2 for SP2. Did you enable the VarDecimal compression? This would invalidate the previous full, as the database version number is changed.

|||

thanxs ,VarDecimal compression ! as far as i know this is not supported in standard edtion of Sql server. any how i also try to enable this anf got the messahe that "

Vardecimal Storage Format is not supported on Standard Edition (64-bit).

"

I just tookfull backup on friday on 7:30 and then took differential backup on 11:30 on firday it happened successfully.

But on saturady i was unable to take differential backup again due to this error.

"Cannot perform a differential backup for database "abc",
because a current database backup does not exist.
Perform a full database backup by reissuing BACKUP DATABASE,
omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035)
BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)."

plz any other solution

|||

ooh i just solve it by the help of follwoing article

http://support.microsoft.com/kb/903643

|||

Hi there,

I've had a similar problem with Differential Backups for SQL Server however I am on SP2 for SQL Server 2005 and SP2 of Windows Server 2003. The differential backups work perfectly until an NTBACKUP is run with the snapn option so that it uses the Volume Shadow Copy. The next differential backup executed by SQL Server then fails with the same sorts of errors reported here in this thread. If I run NTBACKUP with snapff then the problem disappears and SQL Server differential backups carry on working as usual.

Kind Regards

|||

HI there specially Post21, u r quite right , this problem is due to Ntbackup utility but unluckly i change the option sanp oFF but still facing problem. can u guide me some Backup command switches which can help in this issue.

|||

I found the solution to my problem. I posted on a thread in Newsgroups on this but thought I'd post my findings here as well.

I couldn't understand why all of a sudden I was getting broken differential chains when NT BACKUP was run using volume shadow copy service after
upgrading the servers to SP2 of SQL Server 2005 and SP2 of Windows Server 2003. As in this KB http://support.microsoft.com/kb/903643. I hadn't had
this problem until this upgrade.

Here's the reason:

The "Readme for Microsoft SQL Server 2005 Service Pack 2" at
http://download.microsoft.com/download/2/b/5/2b5e5d37-9b17-423d-bc8f-b11ecd4195b4/ReadmeSQL2005SP2.htm
states:

3.2.6 SQL Server 2005 SP2 Changes the SQL Writer Service Default Startup Type and Default State

Note the following changes to the SQL Writer service default startup type and default state:

The SQL Writer service startup type is changed from Manual to Automatic.

The SQL Writer service default state is changed from Stopped to Started.

So I changed the service back to manual and stopped the service. This of course
behaved as expected, the VSS didn't try to lock my Full recovery model
databases and break the differential chain. I can still allow /volsnapn
option with my NTBACKUP commands to backup all the other "open" files on the
volume without breaking the database differential chain.

The thing that started me looking into this was this line in the BOL article
that a guy on the newsgroup mentioned.

This SQL Writer Service is automatically installed but not enabled by
default. It must be explicitly enabled to run on the server machine and must
be running at the time that the Volume Shadow Copy Service (VSS) application
requests a backup or restore. Use the Microsoft Windows Services applet to
configure the service. The SQL Writer Service installs on all operating
systems but is only needed on server systems. For Microsoft Windows XP, use
the MSDE Writer.

Hope the above helps somebody else out of the same situation.

Regards
Nick

|||thats quite fine and i have got the solution . thanks for all of participents.

Cannot perform a differential backup for database

hi seniors ,

I am running sql server 2005 under details

Microsoft SQL Server 2005 - 9.00.3033.00 (X64)

Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit)
on Windows NT 5.2 (Build 3790: Service Pack 1)

When i attempt to take differentail backup i receieved following message

"Cannot perform a differential backup for database "abc",
because a current database backup does not exist.
Perform a full database backup by reissuing BACKUP DATABASE,
omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035)
BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)."

when i search this msg on net than i find that it is microsoft bug 863 so i found hotfixes
about this problem and tried to install but unable to install (On installation screen select feature i am unable to click on check box)

Can any one help me regarding to this issue .And more is.. full back of db "abc" exists and i never changed its location nor rename file name.

Regards

First action of any backup activity should be full backup. For differential backup u should have a Baseline/Full backup. SO first take full backup and the try differential backup

backup database SomeDtabase to Disk='D:\Somefile.bak' with init

the above statment will create the full backup of your database. then do the diffrential backup . Read more in BOL

Madhu

|||

thanxs madhu , When i saw deatils about with INIT...I think it should work now. But one thing more wheather i should use this clasue with differential backup or not...

thanxs again

Ahmad Drshen

|||

it depends .... whether u need to overwrite the backup file or not... its again depend upon the Backup strategy of your server....

Read more about INIT in BOL

Madhu

|||

Hi Madhu K Nair and other Seinors ,

Agian , i am fall in problem

" Cannot perform a differential backup for database "AAA", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013).

I have just Taken backup of db "AAA" on friday at 7:30 PM through script

BACKUP DATABASE AAA TO DISK = 'C:\LV_full_AAA_20071702.bkp'

WITH INIT , NOUNLOAD ,

NAME = 'AAAbackup', NOSKIP , STATS = 10,

NOFORMAT

DECLARE @.i INT

select @.i = position from msdb..backupset where database_name='AAA'

and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='AAA')

RESTORE VERIFYONLY FROM DISK = 'C:\LV_full_AAA_20071702.bkp' WITH FILE = @.i

Database backup has done successfully. :)

Again I ran the script to take differentail backup on friday at 11:30 PM through script

BACKUP DATABASE AAA TO DISK = 'C:\LV_Diff_AAA_20071702.bkp'

WITH INIT , NOUNLOAD , DIFFERENTIAL ,

NAME = 'AAAbackup', NOSKIP , STATS = 10,

NOFORMAT

DECLARE @.i INT

select @.i = position from msdb..backupset where database_name='AAA'

and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='AAA')

RESTORE VERIFYONLY FROM DISK = 'C:\LV_Diff_AAA_20071702.bkp' WITH FILE = @.i

Differentail backup has also done successfully.

But on saturaday when the script ran for taking differentail backup of db AAA on 11:30 PM

it repoted follwoing error

" Cannot perform a differential backup for database "AAA", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). "

while these script are working properly on another production server even there is not mention WITH INIT option with full backup.

wat the hell is going in taking backup my this server .One thing more i just installed the SP2 exe name was "SQLServer2005SP2-KB921896-x64-ENU.exe" on my server perhaps i am stucking with this problem after installing this plz let me know wat is the solution to fix this hell.

thanxs in advance

Ahmad Drshen

|||

How recent is your full backup of the database? Have there been any significant changes since then?

This is CTP2 for SP2. Did you enable the VarDecimal compression? This would invalidate the previous full, as the database version number is changed.

|||

thanxs ,VarDecimal compression ! as far as i know this is not supported in standard edtion of Sql server. any how i also try to enable this anf got the messahe that "

Vardecimal Storage Format is not supported on Standard Edition (64-bit).

"

I just tookfull backup on friday on 7:30 and then took differential backup on 11:30 on firday it happened successfully.

But on saturady i was unable to take differential backup again due to this error.

"Cannot perform a differential backup for database "abc",
because a current database backup does not exist.
Perform a full database backup by reissuing BACKUP DATABASE,
omitting the WITH DIFFERENTIAL option. [SQLSTATE 42000] (Error 3035)
BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)."

plz any other solution

|||

ooh i just solve it by the help of follwoing article

http://support.microsoft.com/kb/903643

|||

Hi there,

I've had a similar problem with Differential Backups for SQL Server however I am on SP2 for SQL Server 2005 and SP2 of Windows Server 2003. The differential backups work perfectly until an NTBACKUP is run with the snapn option so that it uses the Volume Shadow Copy. The next differential backup executed by SQL Server then fails with the same sorts of errors reported here in this thread. If I run NTBACKUP with snapff then the problem disappears and SQL Server differential backups carry on working as usual.

Kind Regards

|||

HI there specially Post21, u r quite right , this problem is due to Ntbackup utility but unluckly i change the option sanp oFF but still facing problem. can u guide me some Backup command switches which can help in this issue.

|||

I found the solution to my problem. I posted on a thread in Newsgroups on this but thought I'd post my findings here as well.

I couldn't understand why all of a sudden I was getting broken differential chains when NT BACKUP was run using volume shadow copy service after
upgrading the servers to SP2 of SQL Server 2005 and SP2 of Windows Server 2003. As in this KB http://support.microsoft.com/kb/903643. I hadn't had
this problem until this upgrade.

Here's the reason:

The "Readme for Microsoft SQL Server 2005 Service Pack 2" at
http://download.microsoft.com/download/2/b/5/2b5e5d37-9b17-423d-bc8f-b11ecd4195b4/ReadmeSQL2005SP2.htm
states:

3.2.6 SQL Server 2005 SP2 Changes the SQL Writer Service Default Startup Type and Default State

Note the following changes to the SQL Writer service default startup type and default state:

The SQL Writer service startup type is changed from Manual to Automatic.

The SQL Writer service default state is changed from Stopped to Started.

So I changed the service back to manual and stopped the service. This of course
behaved as expected, the VSS didn't try to lock my Full recovery model
databases and break the differential chain. I can still allow /volsnapn
option with my NTBACKUP commands to backup all the other "open" files on the
volume without breaking the database differential chain.

The thing that started me looking into this was this line in the BOL article
that a guy on the newsgroup mentioned.

This SQL Writer Service is automatically installed but not enabled by
default. It must be explicitly enabled to run on the server machine and must
be running at the time that the Volume Shadow Copy Service (VSS) application
requests a backup or restore. Use the Microsoft Windows Services applet to
configure the service. The SQL Writer Service installs on all operating
systems but is only needed on server systems. For Microsoft Windows XP, use
the MSDE Writer.

Hope the above helps somebody else out of the same situation.

Regards
Nick

|||thats quite fine and i have got the solution . thanks for all of participents.

Cannot make MantainancePlans (Backups)!

When I try to make SQL mantainance plans to backup my database automatically every 6 hours on SQL 2005 I get this:

http://img174.imageshack.us/img174/6838/mssqlbckptv2.jpg

How can I fix it? Help is greatly appriciated!

Regards

This behavior occurs when you install SQL Server 2005 without installing the SQL Server 2005 Integration Services (SSIS) service

Check out this support article I think it will answer your question.

http://support.microsoft.com/?kbid=909036

cannot load dll xpstar.dll reason 126

Hi
This error message happens when trying to restore/backup a database
(The file exists in the binn directory)
How do I solve it ?
thanks in advance
RomyIs the full error message:
Cannot load the DLL xp_star.dll, or one of the DLLs it
references.
Reason: 126(The specified module could not be found.).
If so, you are likely missing one of the files that
xp_star.dll has a reference to. You can use a dependency
tool like Dependency Walker (Depends.exe) to list all the
dependency for xp_star.dll and narrow down which of the
files is missing.
-Sue
On Tue, 11 Jan 2005 10:35:23 +0200, "romy"
<romyedevelop@.hotmail.com> wrote:
>Hi
>This error message happens when trying to restore/backup a database
>(The file exists in the binn directory)
>How do I solve it ?
>thanks in advance
>Romy
>|||Are you using the Database Maintenance Wizard to do your backups by any
change?
Than you should also check the dependencies of SQLMAINT.EXE which gets
called via XPSTAR.DLL xp_sqlmaint
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2004 All rights reserved.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:b2mau0plboskh42tcqhfeoovn48rngicnt@.4ax.com...
> Is the full error message:
> Cannot load the DLL xp_star.dll, or one of the DLLs it
> references.
> Reason: 126(The specified module could not be found.).
> If so, you are likely missing one of the files that
> xp_star.dll has a reference to. You can use a dependency
> tool like Dependency Walker (Depends.exe) to list all the
> dependency for xp_star.dll and narrow down which of the
> files is missing.
> -Sue
> On Tue, 11 Jan 2005 10:35:23 +0200, "romy"
> <romyedevelop@.hotmail.com> wrote:
>>Hi
>>This error message happens when trying to restore/backup a database
>>(The file exists in the binn directory)
>>How do I solve it ?
>>thanks in advance
>>Romy
>

cannot load dll xpstar.dll reason 126

Hi
This error message happens when trying to restore/backup a database
(The file exists in the binn directory)
How do I solve it ?
thanks in advance
Romy
Is the full error message:
Cannot load the DLL xp_star.dll, or one of the DLLs it
references.
Reason: 126(The specified module could not be found.).
If so, you are likely missing one of the files that
xp_star.dll has a reference to. You can use a dependency
tool like Dependency Walker (Depends.exe) to list all the
dependency for xp_star.dll and narrow down which of the
files is missing.
-Sue
On Tue, 11 Jan 2005 10:35:23 +0200, "romy"
<romyedevelop@.hotmail.com> wrote:

>Hi
>This error message happens when trying to restore/backup a database
>(The file exists in the binn directory)
>How do I solve it ?
>thanks in advance
>Romy
>
|||Are you using the Database Maintenance Wizard to do your backups by any
change?
Than you should also check the dependencies of SQLMAINT.EXE which gets
called via XPSTAR.DLL xp_sqlmaint
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:b2mau0plboskh42tcqhfeoovn48rngicnt@.4ax.com...
> Is the full error message:
> Cannot load the DLL xp_star.dll, or one of the DLLs it
> references.
> Reason: 126(The specified module could not be found.).
> If so, you are likely missing one of the files that
> xp_star.dll has a reference to. You can use a dependency
> tool like Dependency Walker (Depends.exe) to list all the
> dependency for xp_star.dll and narrow down which of the
> files is missing.
> -Sue
> On Tue, 11 Jan 2005 10:35:23 +0200, "romy"
> <romyedevelop@.hotmail.com> wrote:
>

cannot load dll xpstar.dll reason 126

Hi
This error message happens when trying to restore/backup a database
(The file exists in the binn directory)
How do I solve it ?
thanks in advance
RomyIs the full error message:
Cannot load the DLL xp_star.dll, or one of the DLLs it
references.
Reason: 126(The specified module could not be found.).
If so, you are likely missing one of the files that
xp_star.dll has a reference to. You can use a dependency
tool like Dependency Walker (Depends.exe) to list all the
dependency for xp_star.dll and narrow down which of the
files is missing.
-Sue
On Tue, 11 Jan 2005 10:35:23 +0200, "romy"
<romyedevelop@.hotmail.com> wrote:

>Hi
>This error message happens when trying to restore/backup a database
>(The file exists in the binn directory)
>How do I solve it ?
>thanks in advance
>Romy
>|||Are you using the Database Maintenance Wizard to do your backups by any
change?
Than you should also check the dependencies of SQLMAINT.EXE which gets
called via XPSTAR.DLL xp_sqlmaint
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2004 All rights reserved.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:b2mau0plboskh42tcqhfeoovn48rngicnt@.
4ax.com...
> Is the full error message:
> Cannot load the DLL xp_star.dll, or one of the DLLs it
> references.
> Reason: 126(The specified module could not be found.).
> If so, you are likely missing one of the files that
> xp_star.dll has a reference to. You can use a dependency
> tool like Dependency Walker (Depends.exe) to list all the
> dependency for xp_star.dll and narrow down which of the
> files is missing.
> -Sue
> On Tue, 11 Jan 2005 10:35:23 +0200, "romy"
> <romyedevelop@.hotmail.com> wrote:
>
>

Cannot Kill Process stays in KILLED/ROLLBACK status

I've just try to kill a process running for almost 10days now. It is a differential backup on which the SQLServer apparently choked. After killing the process it's in KILLED/ROLLBACK for quite sometime when I try to get the status with KILL 57 WITH STATUSONLY I get the following result:

SPID 57: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

So I would infer that the process is indeed finished but it stays there. How can I get rid of a process that has a 100% Rollback completion and an estimate time remaining of 0 seconds?

I'm moving this thread to DB Engine. They may be able to answer or point you in the right direction.|||

Thanks Samuel, It's sometimes difficult to find the right forum. Although I didn't like it I saw no other way than to do a restart of the server (I had some other maintenance at hand as well). Obviously after a restart the session was gone and I did not see adverse effects that could specifically be pinned down to this aborted session. Hopefully we did not loose any data, which is hard to track as daily about a 150'000 new records are created on the various databases on this particular server.

|||I'm having the same problem. You could have just restarted the SQL Service without restarting the server. I had this problem once before and it happened again today. Restarting the service fixed the problem.|||

Hi Guys,

if the process that is running is external to SQL server i.e. OSQL, sqlmaint, and if you do the KILL SPID on it, it might not finish the rollback.

To avoid the server restart of the SQL Service, do not run the KILL SPID within the SQL Server instead kill the process at the OS level and the SPID from within the SQL will disappear.

regards

Jag

|||

Hi Guys,

I am facing the same problem.

My job corresponding to distribution agent failed. But its spid still exists with WaitType = EC and in Killed/Rollback status.

I dont see any distrib.exe on the OS side in the task mgr which i can kill. And because of this spid the Sql agent wont execute the distribution agent saying that there is already one instance of same running.

And i dont want to restart the service :(

Thanks

|||

Top post Jag

I just had this problem when running xp_cmdshell, I found cmd.exe under windows processes and stopped this and this cleared the SPID without restarting the SQL services.

Cannot Kill Process stays in KILLED/ROLLBACK status

I've just try to kill a process running for almost 10days now. It is a differential backup on which the SQLServer apparently choked. After killing the process it's in KILLED/ROLLBACK for quite sometime when I try to get the status with KILL 57 WITH STATUSONLY I get the following result:

SPID 57: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

So I would infer that the process is indeed finished but it stays there. How can I get rid of a process that has a 100% Rollback completion and an estimate time remaining of 0 seconds?

I'm moving this thread to DB Engine. They may be able to answer or point you in the right direction.|||

Thanks Samuel, It's sometimes difficult to find the right forum. Although I didn't like it I saw no other way than to do a restart of the server (I had some other maintenance at hand as well). Obviously after a restart the session was gone and I did not see adverse effects that could specifically be pinned down to this aborted session. Hopefully we did not loose any data, which is hard to track as daily about a 150'000 new records are created on the various databases on this particular server.

|||I'm having the same problem. You could have just restarted the SQL Service without restarting the server. I had this problem once before and it happened again today. Restarting the service fixed the problem.|||

Hi Guys,

if the process that is running is external to SQL server i.e. OSQL, sqlmaint, and if you do the KILL SPID on it, it might not finish the rollback.

To avoid the server restart of the SQL Service, do not run the KILL SPID within the SQL Server instead kill the process at the OS level and the SPID from within the SQL will disappear.

regards

Jag

|||

Hi Guys,

I am facing the same problem.

My job corresponding to distribution agent failed. But its spid still exists with WaitType = EC and in Killed/Rollback status.

I dont see any distrib.exe on the OS side in the task mgr which i can kill. And because of this spid the Sql agent wont execute the distribution agent saying that there is already one instance of same running.

And i dont want to restart the service :(

Thanks

|||

Top post Jag

I just had this problem when running xp_cmdshell, I found cmd.exe under windows processes and stopped this and this cleared the SPID without restarting the SQL services.

Cannot Kill Process stays in KILLED/ROLLBACK status

I've just try to kill a process running for almost 10days now. It is a differential backup on which the SQLServer apparently choked. After killing the process it's in KILLED/ROLLBACK for quite sometime when I try to get the status with KILL 57 WITH STATUSONLY I get the following result:

SPID 57: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

So I would infer that the process is indeed finished but it stays there. How can I get rid of a process that has a 100% Rollback completion and an estimate time remaining of 0 seconds?

I'm moving this thread to DB Engine. They may be able to answer or point you in the right direction.|||

Thanks Samuel, It's sometimes difficult to find the right forum. Although I didn't like it I saw no other way than to do a restart of the server (I had some other maintenance at hand as well). Obviously after a restart the session was gone and I did not see adverse effects that could specifically be pinned down to this aborted session. Hopefully we did not loose any data, which is hard to track as daily about a 150'000 new records are created on the various databases on this particular server.

|||I'm having the same problem. You could have just restarted the SQL Service without restarting the server. I had this problem once before and it happened again today. Restarting the service fixed the problem.|||

Hi Guys,

if the process that is running is external to SQL server i.e. OSQL, sqlmaint, and if you do the KILL SPID on it, it might not finish the rollback.

To avoid the server restart of the SQL Service, do not run the KILL SPID within the SQL Server instead kill the process at the OS level and the SPID from within the SQL will disappear.

regards

Jag

|||

Hi Guys,

I am facing the same problem.

My job corresponding to distribution agent failed. But its spid still exists with WaitType = EC and in Killed/Rollback status.

I dont see any distrib.exe on the OS side in the task mgr which i can kill. And because of this spid the Sql agent wont execute the distribution agent saying that there is already one instance of same running.

And i dont want to restart the service :(

Thanks

|||

Top post Jag

I just had this problem when running xp_cmdshell, I found cmd.exe under windows processes and stopped this and this cleared the SPID without restarting the SQL services.

Cannot Kill Process stays in KILLED/ROLLBACK status

I've just try to kill a process running for almost 10days now. It is a differential backup on which the SQLServer apparently choked. After killing the process it's in KILLED/ROLLBACK for quite sometime when I try to get the status with KILL 57 WITH STATUSONLY I get the following result:

SPID 57: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

So I would infer that the process is indeed finished but it stays there. How can I get rid of a process that has a 100% Rollback completion and an estimate time remaining of 0 seconds?

I'm moving this thread to DB Engine. They may be able to answer or point you in the right direction.|||

Thanks Samuel, It's sometimes difficult to find the right forum. Although I didn't like it I saw no other way than to do a restart of the server (I had some other maintenance at hand as well). Obviously after a restart the session was gone and I did not see adverse effects that could specifically be pinned down to this aborted session. Hopefully we did not loose any data, which is hard to track as daily about a 150'000 new records are created on the various databases on this particular server.

|||I'm having the same problem. You could have just restarted the SQL Service without restarting the server. I had this problem once before and it happened again today. Restarting the service fixed the problem.|||

Hi Guys,

if the process that is running is external to SQL server i.e. OSQL, sqlmaint, and if you do the KILL SPID on it, it might not finish the rollback.

To avoid the server restart of the SQL Service, do not run the KILL SPID within the SQL Server instead kill the process at the OS level and the SPID from within the SQL will disappear.

regards

Jag

|||

Hi Guys,

I am facing the same problem.

My job corresponding to distribution agent failed. But its spid still exists with WaitType = EC and in Killed/Rollback status.

I dont see any distrib.exe on the OS side in the task mgr which i can kill. And because of this spid the Sql agent wont execute the distribution agent saying that there is already one instance of same running.

And i dont want to restart the service :(

Thanks

|||

Top post Jag

I just had this problem when running xp_cmdshell, I found cmd.exe under windows processes and stopped this and this cleared the SPID without restarting the SQL services.

Cannot Kill Process stays in KILLED/ROLLBACK status

I've just try to kill a process running for almost 10days now. It is a differential backup on which the SQLServer apparently choked. After killing the process it's in KILLED/ROLLBACK for quite sometime when I try to get the status with KILL 57 WITH STATUSONLY I get the following result:

SPID 57: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

So I would infer that the process is indeed finished but it stays there. How can I get rid of a process that has a 100% Rollback completion and an estimate time remaining of 0 seconds?

I'm moving this thread to DB Engine. They may be able to answer or point you in the right direction.|||

Thanks Samuel, It's sometimes difficult to find the right forum. Although I didn't like it I saw no other way than to do a restart of the server (I had some other maintenance at hand as well). Obviously after a restart the session was gone and I did not see adverse effects that could specifically be pinned down to this aborted session. Hopefully we did not loose any data, which is hard to track as daily about a 150'000 new records are created on the various databases on this particular server.

|||I'm having the same problem. You could have just restarted the SQL Service without restarting the server. I had this problem once before and it happened again today. Restarting the service fixed the problem.|||

Hi Guys,

if the process that is running is external to SQL server i.e. OSQL, sqlmaint, and if you do the KILL SPID on it, it might not finish the rollback.

To avoid the server restart of the SQL Service, do not run the KILL SPID within the SQL Server instead kill the process at the OS level and the SPID from within the SQL will disappear.

regards

Jag

|||

Hi Guys,

I am facing the same problem.

My job corresponding to distribution agent failed. But its spid still exists with WaitType = EC and in Killed/Rollback status.

I dont see any distrib.exe on the OS side in the task mgr which i can kill. And because of this spid the Sql agent wont execute the distribution agent saying that there is already one instance of same running.

And i dont want to restart the service :(

Thanks

|||

Top post Jag

I just had this problem when running xp_cmdshell, I found cmd.exe under windows processes and stopped this and this cleared the SPID without restarting the SQL services.

Cannot Initialize RS

Hi All
I'm having some problem to initialize a RS Instance, backup, restore, change
or delete Encryption Keys. The RS reports the following error message:
"ReportServicesConfigUI.WMIProvider.WMIProviderException: Cannot decrypt the
Configuration Encrypteds Value for "LogonCred".
(rsFailedToDecryptConfigInformation)
in
ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject
mo)
en
ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.RestoreEncryptionKey(Byte[]
encryptedBytes, String password)"
This issue avoid me to implement some reports I made by Business
Intelligence Development Studio.
Thanks in advance.Hello Tiempo,
Based on my research, you should respecify a domain user as the excution
account in the reporting services configuration.
If this still could not resolved the problem, please check the report
services error log file to see are there any detailed error logs.
By default, the log file is located at \Microsoft SQL Server\<SQL Server
Instance>\Reporting Services\LogFiles.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Thank you very much, Wei Lu.
I'll test it next week due to technical reasons.
Arturo Carrión
.Net & Sql Server Developer at
Tiempo Soluciones Informáticas
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> escribió en el mensaje
news:MEt$kSfOHHA.2300@.TK2MSFTNGHUB02.phx.gbl...
> Hello Tiempo,
> Based on my research, you should respecify a domain user as the excution
> account in the reporting services configuration.
> If this still could not resolved the problem, please check the report
> services error log file to see are there any detailed error logs.
> By default, the log file is located at \Microsoft SQL Server\<SQL Server
> Instance>\Reporting Services\LogFiles.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hi Wei, I solve the problem but by another way. I navigated through the MSDN
and I found the rsconfig utility that I had been run by a comman line, whit
this utility I regenerated the private keys and I could initialize my RS.
Thanks, in any case..
<tiempotecnologia@.newsgroup.nospam> escribió en el mensaje
news:eNRbXZwOHHA.320@.TK2MSFTNGP06.phx.gbl...
> Thank you very much, Wei Lu.
> I'll test it next week due to technical reasons.
> Arturo Carrión
> .Net & Sql Server Developer at
> Tiempo Soluciones Informáticas
> "Wei Lu [MSFT]" <weilu@.online.microsoft.com> escribió en el mensaje
> news:MEt$kSfOHHA.2300@.TK2MSFTNGHUB02.phx.gbl...
>> Hello Tiempo,
>> Based on my research, you should respecify a domain user as the excution
>> account in the reporting services configuration.
>> If this still could not resolved the problem, please check the report
>> services error log file to see are there any detailed error logs.
>> By default, the log file is located at \Microsoft SQL Server\<SQL Server
>> Instance>\Reporting Services\LogFiles.
>> Sincerely,
>> Wei Lu
>> Microsoft Online Community Support
>> ==================================================>> Get notification to my posts through email? Please refer to
>> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
>> ications.
>> Note: The MSDN Managed Newsgroup support offering is for non-urgent
>> issues
>> where an initial response from the community or a Microsoft Support
>> Engineer within 1 business day is acceptable. Please note that each
>> follow
>> up response may take approximately 2 business days as the support
>> professional working with you may need further investigation to reach the
>> most efficient resolution. The offering is not appropriate for situations
>> that require urgent, real-time or phone-based interactions or complex
>> project analysis and dump analysis issues. Issues of this nature are best
>> handled working with a dedicated Microsoft Support Engineer by contacting
>> Microsoft Customer Support Services (CSS) at
>> http://msdn.microsoft.com/subscriptions/support/default.aspx.
>> ==================================================>> (This posting is provided "AS IS", with no warranties, and confers no
>> rights.)
>|||Hello Tiempo,
Glad to hear that you resolved this issue bu regenerated the private key.
If you have any questions, please feel free to let me know.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Cannot get write permissions to a folder

I have a server ser1 which has the sql server running under the
security context of the local admin.Whn i try to copy the backup files
from this server to a network share it acnnot access the share unless
the sever has all authenticated user write permisssions.On setting the
auditing on we found that ser1$ uis the account which is trying to
write to the share.My question is as follows:- what is the difference
between the domaonname\ser1 and domainname\ser1$ account.I cannot see
this ser1$ account in the domain so I caaanot give it permissions.Is
there a way I could get to this account and give it permissions to
tighter up the security?
Thanks in anticipation.
AjayI'm not a network adminstrator, but I tried the same thing before and it
doesn't work. If you want SQL to have access to network resources it has to
run under a domain account. Then you can grant permissions to that account
on the network share you are trying to access. You will either need to setup
sql server to run under a domain account or have sql server write the backup
locally and schedule a job that can run with domain credentials to copy the
backup to the network share.
Mark
"Ag" <ajaygarg1962@.gmail.com> wrote in message
news:1154617829.443666.306270@.i42g2000cwa.googlegroups.com...
>I have a server ser1 which has the sql server running under the
> security context of the local admin.Whn i try to copy the backup files
> from this server to a network share it acnnot access the share unless
> the sever has all authenticated user write permisssions.On setting the
> auditing on we found that ser1$ uis the account which is trying to
> write to the share.My question is as follows:- what is the difference
> between the domaonname\ser1 and domainname\ser1$ account.I cannot see
> this ser1$ account in the domain so I caaanot give it permissions.Is
> there a way I could get to this account and give it permissions to
> tighter up the security?
> Thanks in anticipation.
>
> Ajay
>|||Mark Miller wrote:
> I'm not a network adminstrator, but I tried the same thing before and it
> doesn't work. If you want SQL to have access to network resources it has to
> run under a domain account. Then you can grant permissions to that account
> on the network share you are trying to access. You will either need to setup
> sql server to run under a domain account or have sql server write the backup
> locally and schedule a job that can run with domain credentials to copy the
> backup to the network share.
> Mark
> "Ag" <ajaygarg1962@.gmail.com> wrote in message
> news:1154617829.443666.306270@.i42g2000cwa.googlegroups.com...
> >I have a server ser1 which has the sql server running under the
> > security context of the local admin.Whn i try to copy the backup files
> > from this server to a network share it acnnot access the share unless
> > the sever has all authenticated user write permisssions.On setting the
> > auditing on we found that ser1$ uis the account which is trying to
> > write to the share.My question is as follows:- what is the difference
> > between the domaonname\ser1 and domainname\ser1$ account.I cannot see
> > this ser1$ account in the domain so I caaanot give it permissions.Is
> > there a way I could get to this account and give it permissions to
> > tighter up the security?
> >
> > Thanks in anticipation.
> >
> >
> > Ajay
Thanks Mark for the response:-
Here is what I notices and I was able to resolve the issue:-
ser1$ is the computername account which is trying to acces a directory
whenever the sql server uses a local admin account.We can resolve this
in three ways:-
1.Give the network share permissions for the the
domainname/computername account in this case domainname/compuetname$ .
2.Change sql servr to use a domain account means chaning the apps to
use the domain acocunt a non starteer in my enviormment as the apps are
owned by the vendors.
3.Use a proxy account for the sql server agent so that even if sql runs
on a local admin account the proxy account would enable the agent to
use a doain account.
Thanks for your response.
Ajay
> >|||Ag wrote:
> Mark Miller wrote:
>> I'm not a network adminstrator, but I tried the same thing before and it
>> doesn't work. If you want SQL to have access to network resources it has to
>> run under a domain account. Then you can grant permissions to that account
>> on the network share you are trying to access. You will either need to setup
>> sql server to run under a domain account or have sql server write the backup
>> locally and schedule a job that can run with domain credentials to copy the
>> backup to the network share.
>> Mark
>> "Ag" <ajaygarg1962@.gmail.com> wrote in message
>> news:1154617829.443666.306270@.i42g2000cwa.googlegroups.com...
>> I have a server ser1 which has the sql server running under the
>> security context of the local admin.Whn i try to copy the backup files
>> from this server to a network share it acnnot access the share unless
>> the sever has all authenticated user write permisssions.On setting the
>> auditing on we found that ser1$ uis the account which is trying to
>> write to the share.My question is as follows:- what is the difference
>> between the domaonname\ser1 and domainname\ser1$ account.I cannot see
>> this ser1$ account in the domain so I caaanot give it permissions.Is
>> there a way I could get to this account and give it permissions to
>> tighter up the security?
>> Thanks in anticipation.
>>
>> Ajay
> Thanks Mark for the response:-
> Here is what I notices and I was able to resolve the issue:-
> ser1$ is the computername account which is trying to acces a directory
> whenever the sql server uses a local admin account.We can resolve this
> in three ways:-
> 1.Give the network share permissions for the the
> domainname/computername account in this case domainname/compuetname$ .
> 2.Change sql servr to use a domain account means chaning the apps to
> use the domain acocunt a non starteer in my enviormment as the apps are
> owned by the vendors.
This is just plain wrong. Changing the account that the SQL Server and
SQL Agent services run under has absolutely nothing to do with the
logins use by applications to connect to SQL databases. If you want to
access network resources from within SQL, this is how you do it. Change
the MSSQLSERVER and SQLSERVERAGENT services to run as a domain user, and
grant share permissions to that domain user.
> 3.Use a proxy account for the sql server agent so that even if sql runs
> on a local admin account the proxy account would enable the agent to
> use a doain account.
> Thanks for your response.
>
> Ajay
>
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> Ag wrote:
> > Mark Miller wrote:
> >> I'm not a network adminstrator, but I tried the same thing before and it
> >> doesn't work. If you want SQL to have access to network resources it has to
> >> run under a domain account. Then you can grant permissions to that account
> >> on the network share you are trying to access. You will either need to setup
> >> sql server to run under a domain account or have sql server write the backup
> >> locally and schedule a job that can run with domain credentials to copy the
> >> backup to the network share.
> >>
> >> Mark
> >>
> >> "Ag" <ajaygarg1962@.gmail.com> wrote in message
> >> news:1154617829.443666.306270@.i42g2000cwa.googlegroups.com...
> >> I have a server ser1 which has the sql server running under the
> >> security context of the local admin.Whn i try to copy the backup files
> >> from this server to a network share it acnnot access the share unless
> >> the sever has all authenticated user write permisssions.On setting the
> >> auditing on we found that ser1$ uis the account which is trying to
> >> write to the share.My question is as follows:- what is the difference
> >> between the domaonname\ser1 and domainname\ser1$ account.I cannot see
> >> this ser1$ account in the domain so I caaanot give it permissions.Is
> >> there a way I could get to this account and give it permissions to
> >> tighter up the security?
> >>
> >> Thanks in anticipation.
> >>
> >>
> >> Ajay
> >
> > Thanks Mark for the response:-
> >
> > Here is what I notices and I was able to resolve the issue:-
> >
> > ser1$ is the computername account which is trying to acces a directory
> > whenever the sql server uses a local admin account.We can resolve this
> > in three ways:-
> >
> > 1.Give the network share permissions for the the
> > domainname/computername account in this case domainname/compuetname$ .
> > 2.Change sql servr to use a domain account means chaning the apps to
> > use the domain acocunt a non starteer in my enviormment as the apps are
> > owned by the vendors.
> This is just plain wrong. Changing the account that the SQL Server and
> SQL Agent services run under has absolutely nothing to do with the
> logins use by applications to connect to SQL databases. If you want to
> access network resources from within SQL, this is how you do it. Change
> the MSSQLSERVER and SQLSERVERAGENT services to run as a domain user, and
> grant share permissions to that domain user.
>
> > 3.Use a proxy account for the sql server agent so that even if sql runs
> > on a local admin account the proxy account would enable the agent to
> > use a doain account.
> >
> > Thanks for your response.
> >
> >
> > Ajay
> >
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Maybe i did not explain it right:- AT the sql end changing the sql
server to use the domain service accoutn would make the sql server run
on a domain account but the DSn which is configurd to use the local
admin account and use IIS has been configured to use the local admin by
defualt(that is what irt chooses) SO CHANING THE SECURITY CONTEXT OF
THE SQL SERVER SERVICE AND THE SQL AGENT SERICE WOULD MEAN THAT I
CHANGE THE DSN OF ALL THE APPLICATIONS WHICH ARE CONFIGURED TO LOGIN
USING NT ATHENTICATION AND LOCAL ADMIN account on the box.Surprisingly
most apps reside on the same box as the sql server and use the Local
Admin account and are configured to use a dsn /logins odf the local
account on sql server.
This is diiifcult as it entails coordinatig with vendors and so I wold
not wat to go with this option.

Cannot get write permissions to a folder

I have a server ser1 which has the sql server running under the
security context of the local admin.Whn i try to copy the backup files
from this server to a network share it acnnot access the share unless
the sever has all authenticated user write permisssions.On setting the
auditing on we found that ser1$ uis the account which is trying to
write to the share.My question is as follows:- what is the difference
between the domaonname\ser1 and domainname\ser1$ account.I cannot see
this ser1$ account in the domain so I caaanot give it permissions.Is
there a way I could get to this account and give it permissions to
tighter up the security?
Thanks in anticipation.
AjayI'm not a network adminstrator, but I tried the same thing before and it
doesn't work. If you want SQL to have access to network resources it has to
run under a domain account. Then you can grant permissions to that account
on the network share you are trying to access. You will either need to setup
sql server to run under a domain account or have sql server write the backup
locally and schedule a job that can run with domain credentials to copy the
backup to the network share.
Mark
"Ag" <ajaygarg1962@.gmail.com> wrote in message
news:1154617829.443666.306270@.i42g2000cwa.googlegroups.com...
>I have a server ser1 which has the sql server running under the
> security context of the local admin.Whn i try to copy the backup files
> from this server to a network share it acnnot access the share unless
> the sever has all authenticated user write permisssions.On setting the
> auditing on we found that ser1$ uis the account which is trying to
> write to the share.My question is as follows:- what is the difference
> between the domaonname\ser1 and domainname\ser1$ account.I cannot see
> this ser1$ account in the domain so I caaanot give it permissions.Is
> there a way I could get to this account and give it permissions to
> tighter up the security?
> Thanks in anticipation.
>
> Ajay
>|||Mark Miller wrote:[vbcol=seagreen]
> I'm not a network adminstrator, but I tried the same thing before and it
> doesn't work. If you want SQL to have access to network resources it has t
o
> run under a domain account. Then you can grant permissions to that account
> on the network share you are trying to access. You will either need to set
up
> sql server to run under a domain account or have sql server write the back
up
> locally and schedule a job that can run with domain credentials to copy th
e
> backup to the network share.
> Mark
> "Ag" <ajaygarg1962@.gmail.com> wrote in message
> news:1154617829.443666.306270@.i42g2000cwa.googlegroups.com...
Thanks Mark for the response:-
Here is what I notices and I was able to resolve the issue:-
ser1$ is the computername account which is trying to acces a directory
whenever the sql server uses a local admin account.We can resolve this
in three ways:-
1.Give the network share permissions for the the
domainname/computername account in this case domainname/compuetname$ .
2.Change sql servr to use a domain account means chaning the apps to
use the domain acocunt a non starteer in my enviormment as the apps are
owned by the vendors.
3.Use a proxy account for the sql server agent so that even if sql runs
on a local admin account the proxy account would enable the agent to
use a doain account.
Thanks for your response.
Ajay[vbcol=seagreen]|||Ag wrote:
> Mark Miller wrote:
> Thanks Mark for the response:-
> Here is what I notices and I was able to resolve the issue:-
> ser1$ is the computername account which is trying to acces a directory
> whenever the sql server uses a local admin account.We can resolve this
> in three ways:-
> 1.Give the network share permissions for the the
> domainname/computername account in this case domainname/compuetname$ .
> 2.Change sql servr to use a domain account means chaning the apps to
> use the domain acocunt a non starteer in my enviormment as the apps are
> owned by the vendors.
This is just plain wrong. Changing the account that the SQL Server and
SQL Agent services run under has absolutely nothing to do with the
logins use by applications to connect to SQL databases. If you want to
access network resources from within SQL, this is how you do it. Change
the MSSQLSERVER and SQLSERVERAGENT services to run as a domain user, and
grant share permissions to that domain user.

> 3.Use a proxy account for the sql server agent so that even if sql runs
> on a local admin account the proxy account would enable the agent to
> use a doain account.
> Thanks for your response.
>
> Ajay
>
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben wrote:
> Ag wrote:
> This is just plain wrong. Changing the account that the SQL Server and
> SQL Agent services run under has absolutely nothing to do with the
> logins use by applications to connect to SQL databases. If you want to
> access network resources from within SQL, this is how you do it. Change
> the MSSQLSERVER and SQLSERVERAGENT services to run as a domain user, and
> grant share permissions to that domain user.
>
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Maybe i did not explain it right:- AT the sql end changing the sql
server to use the domain service accoutn would make the sql server run
on a domain account but the DSn which is configurd to use the local
admin account and use IIS has been configured to use the local admin by
defualt(that is what irt chooses) SO CHANING THE SECURITY CONTEXT OF
THE SQL SERVER SERVICE AND THE SQL AGENT SERICE WOULD MEAN THAT I
CHANGE THE DSN OF ALL THE APPLICATIONS WHICH ARE CONFIGURED TO LOGIN
USING NT ATHENTICATION AND LOCAL ADMIN account on the box.Surprisingly
most apps reside on the same box as the sql server and use the Local
Admin account and are configured to use a dsn /logins odf the local
account on sql server.
This is diiifcult as it entails coordinatig with vendors and so I wold
not wat to go with this option.