Hi,
How to achieve this?
I want to backup a database via linked server
e.g. BACKUP DATABASE <linkedservername>.Northwind to LinkDbBkup
Or is there any other workaround to achieve this?
Thanks and Regards,
Dilip
You can't use a linked server to address the database you want to backup. But you can use
sp_executesql to execute an SQL string on the remote server:
EXEC <linkedservername>.master.dbo.sp_executesql N'BACKUP DATABASE pubs TO DISK = ''c:\pubs.bak'''
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<dilipn123@.gmail.com> wrote in message news:1123568862.267207.106550@.z14g2000cwz.googlegr oups.com...
> Hi,
> How to achieve this?
> I want to backup a database via linked server
> e.g. BACKUP DATABASE <linkedservername>.Northwind to LinkDbBkup
> Or is there any other workaround to achieve this?
> Thanks and Regards,
> Dilip
>
|||Hi Tibor,
Thanks for the quick response.
Actually, my objective here is to use the processing power of another
machine to backup the remote database. e.g. Server A hosts the database
and Server B is from where I would like to take the backup.
I tried your workaround and it works but it uses the processing power
of Server A and not Server B. e.g. I issued the backup command from
Server B and checked the processes running on Server A by sp_who and it
showed the backup commands. How do I do it from Server B?
TIA
Regards,
Dilip
|||Hi,
No You cant.
If want to Backup the database which is in SERVER A, then ultimately
resource usage will be on Server A only.
Server B can be a client to issue the backup command as well as backup
storage; but the Backup command will be
executing in Server A.
Thanks
Hari
SQL Server MVP
<dilipn123@.gmail.com> wrote in message
news:1123571709.739767.266160@.z14g2000cwz.googlegr oups.com...
> Hi Tibor,
> Thanks for the quick response.
> Actually, my objective here is to use the processing power of another
> machine to backup the remote database. e.g. Server A hosts the database
> and Server B is from where I would like to take the backup.
> I tried your workaround and it works but it uses the processing power
> of Server A and not Server B. e.g. I issued the backup command from
> Server B and checked the processes running on Server A by sp_who and it
> showed the backup commands. How do I do it from Server B?
> TIA
> Regards,
> Dilip
>
|||You can't do that, and it would make little sense as the backup is almost all I/O. Imagine if
database resides on A and you want B to process the backup. Then B would need to read all the data.
How would B get access to the data?
1. B directly accessing the database files over the network. You get into permissions aspects
(service account of B need permissions on files that A uses). Also, it cannot be done since the
files are already opened by the A SQL Server.
2. B asks A to read the files and send the data to B.
Then the backup data has to be written. If they are to be written on B, you can gain a little bit.
If they are to be written on A, you have to push back the data from B to A, making it even more
expensive.
Above is just thinking out loud to hopefully make you see that there would be little, if any, to
gain from such a scheme. In any event, it isn't possible.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<dilipn123@.gmail.com> wrote in message news:1123571709.739767.266160@.z14g2000cwz.googlegr oups.com...
> Hi Tibor,
> Thanks for the quick response.
> Actually, my objective here is to use the processing power of another
> machine to backup the remote database. e.g. Server A hosts the database
> and Server B is from where I would like to take the backup.
> I tried your workaround and it works but it uses the processing power
> of Server A and not Server B. e.g. I issued the backup command from
> Server B and checked the processes running on Server A by sp_who and it
> showed the backup commands. How do I do it from Server B?
> TIA
> Regards,
> Dilip
>
|||Tibor/Hari,
Thanks for your inputs.
I can understand it doesn't make that much sense, actually my objective
was to release the cpu and I/O resources which by backup process takes.
Our environment is such that it's a highly transaction processing ERP
database (40 GB) in size and avg 2/3 transactions per sec.
The system is high end having 3 processors and 6.5 GB RAM but for
disaster recovery situation, I have my tlogs backup running every 15
mins in between the user transactions and sometimes the backup process
takes too much of CPU resources and I/O since i'm backing up onto
network machine(standby server).
Recently, I was trying out SQLLite which reduces backup and compresses
the size, but it installs extended stored procedures into master
database to do the job and I dont want to install it on my production
database. I was thinking like if i can run the backup remotely, it
would have been good - that's why this remote backup question spawn up.
Guys, thanks for all ur inputs..have a good day
regards,
dilip
Showing posts with label via. Show all posts
Showing posts with label via. Show all posts
Thursday, March 22, 2012
Backing up a database remotely
Hi,
How to achieve this?
I want to backup a database via linked server
e.g. BACKUP DATABASE <linkedservername>.Northwind to LinkDbBkup
Or is there any other workaround to achieve this?
Thanks and Regards,
DilipYou can't use a linked server to address the database you want to backup. B
ut you can use
sp_executesql to execute an SQL string on the remote server:
EXEC <linkedservername>.master.dbo.sp_executesql N'BACKUP DATABASE pubs TO D
ISK = ''c:\pubs.bak'''
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<dilipn123@.gmail.com> wrote in message news:1123568862.267207.106550@.z14g2000cwz.googlegroup
s.com...
> Hi,
> How to achieve this?
> I want to backup a database via linked server
> e.g. BACKUP DATABASE <linkedservername>.Northwind to LinkDbBkup
> Or is there any other workaround to achieve this?
> Thanks and Regards,
> Dilip
>|||Hi Tibor,
Thanks for the quick response.
Actually, my objective here is to use the processing power of another
machine to backup the remote database. e.g. Server A hosts the database
and Server B is from where I would like to take the backup.
I tried your workaround and it works but it uses the processing power
of Server A and not Server B. e.g. I issued the backup command from
Server B and checked the processes running on Server A by sp_who and it
showed the backup commands. How do I do it from Server B?
TIA
Regards,
Dilip|||Hi,
No You cant.
If want to Backup the database which is in SERVER A, then ultimately
resource usage will be on Server A only.
Server B can be a client to issue the backup command as well as backup
storage; but the Backup command will be
executing in Server A.
Thanks
Hari
SQL Server MVP
<dilipn123@.gmail.com> wrote in message
news:1123571709.739767.266160@.z14g2000cwz.googlegroups.com...
> Hi Tibor,
> Thanks for the quick response.
> Actually, my objective here is to use the processing power of another
> machine to backup the remote database. e.g. Server A hosts the database
> and Server B is from where I would like to take the backup.
> I tried your workaround and it works but it uses the processing power
> of Server A and not Server B. e.g. I issued the backup command from
> Server B and checked the processes running on Server A by sp_who and it
> showed the backup commands. How do I do it from Server B?
> TIA
> Regards,
> Dilip
>|||You can't do that, and it would make little sense as the backup is almost al
l I/O. Imagine if
database resides on A and you want B to process the backup. Then B would nee
d to read all the data.
How would B get access to the data?
1. B directly accessing the database files over the network. You get into pe
rmissions aspects
(service account of B need permissions on files that A uses). Also, it canno
t be done since the
files are already opened by the A SQL Server.
2. B asks A to read the files and send the data to B.
Then the backup data has to be written. If they are to be written on B, you
can gain a little bit.
If they are to be written on A, you have to push back the data from B to A,
making it even more
expensive.
Above is just thinking out loud to hopefully make you see that there would b
e little, if any, to
gain from such a scheme. In any event, it isn't possible.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<dilipn123@.gmail.com> wrote in message news:1123571709.739767.266160@.z14g2000cwz.googlegroup
s.com...
> Hi Tibor,
> Thanks for the quick response.
> Actually, my objective here is to use the processing power of another
> machine to backup the remote database. e.g. Server A hosts the database
> and Server B is from where I would like to take the backup.
> I tried your workaround and it works but it uses the processing power
> of Server A and not Server B. e.g. I issued the backup command from
> Server B and checked the processes running on Server A by sp_who and it
> showed the backup commands. How do I do it from Server B?
> TIA
> Regards,
> Dilip
>|||Tibor/Hari,
Thanks for your inputs.
I can understand it doesn't make that much sense, actually my objective
was to release the cpu and I/O resources which by backup process takes.
Our environment is such that it's a highly transaction processing ERP
database (40 GB) in size and avg 2/3 transactions per sec.
The system is high end having 3 processors and 6.5 GB RAM but for
disaster recovery situation, I have my tlogs backup running every 15
mins in between the user transactions and sometimes the backup process
takes too much of CPU resources and I/O since i'm backing up onto
network machine(standby server).
Recently, I was trying out SQLLite which reduces backup and compresses
the size, but it installs extended stored procedures into master
database to do the job and I dont want to install it on my production
database. I was thinking like if i can run the backup remotely, it
would have been good - that's why this remote backup question spawn up.
Guys, thanks for all ur inputs..have a good day
regards,
dilip
How to achieve this?
I want to backup a database via linked server
e.g. BACKUP DATABASE <linkedservername>.Northwind to LinkDbBkup
Or is there any other workaround to achieve this?
Thanks and Regards,
DilipYou can't use a linked server to address the database you want to backup. B
ut you can use
sp_executesql to execute an SQL string on the remote server:
EXEC <linkedservername>.master.dbo.sp_executesql N'BACKUP DATABASE pubs TO D
ISK = ''c:\pubs.bak'''
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<dilipn123@.gmail.com> wrote in message news:1123568862.267207.106550@.z14g2000cwz.googlegroup
s.com...
> Hi,
> How to achieve this?
> I want to backup a database via linked server
> e.g. BACKUP DATABASE <linkedservername>.Northwind to LinkDbBkup
> Or is there any other workaround to achieve this?
> Thanks and Regards,
> Dilip
>|||Hi Tibor,
Thanks for the quick response.
Actually, my objective here is to use the processing power of another
machine to backup the remote database. e.g. Server A hosts the database
and Server B is from where I would like to take the backup.
I tried your workaround and it works but it uses the processing power
of Server A and not Server B. e.g. I issued the backup command from
Server B and checked the processes running on Server A by sp_who and it
showed the backup commands. How do I do it from Server B?
TIA
Regards,
Dilip|||Hi,
No You cant.
If want to Backup the database which is in SERVER A, then ultimately
resource usage will be on Server A only.
Server B can be a client to issue the backup command as well as backup
storage; but the Backup command will be
executing in Server A.
Thanks
Hari
SQL Server MVP
<dilipn123@.gmail.com> wrote in message
news:1123571709.739767.266160@.z14g2000cwz.googlegroups.com...
> Hi Tibor,
> Thanks for the quick response.
> Actually, my objective here is to use the processing power of another
> machine to backup the remote database. e.g. Server A hosts the database
> and Server B is from where I would like to take the backup.
> I tried your workaround and it works but it uses the processing power
> of Server A and not Server B. e.g. I issued the backup command from
> Server B and checked the processes running on Server A by sp_who and it
> showed the backup commands. How do I do it from Server B?
> TIA
> Regards,
> Dilip
>|||You can't do that, and it would make little sense as the backup is almost al
l I/O. Imagine if
database resides on A and you want B to process the backup. Then B would nee
d to read all the data.
How would B get access to the data?
1. B directly accessing the database files over the network. You get into pe
rmissions aspects
(service account of B need permissions on files that A uses). Also, it canno
t be done since the
files are already opened by the A SQL Server.
2. B asks A to read the files and send the data to B.
Then the backup data has to be written. If they are to be written on B, you
can gain a little bit.
If they are to be written on A, you have to push back the data from B to A,
making it even more
expensive.
Above is just thinking out loud to hopefully make you see that there would b
e little, if any, to
gain from such a scheme. In any event, it isn't possible.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<dilipn123@.gmail.com> wrote in message news:1123571709.739767.266160@.z14g2000cwz.googlegroup
s.com...
> Hi Tibor,
> Thanks for the quick response.
> Actually, my objective here is to use the processing power of another
> machine to backup the remote database. e.g. Server A hosts the database
> and Server B is from where I would like to take the backup.
> I tried your workaround and it works but it uses the processing power
> of Server A and not Server B. e.g. I issued the backup command from
> Server B and checked the processes running on Server A by sp_who and it
> showed the backup commands. How do I do it from Server B?
> TIA
> Regards,
> Dilip
>|||Tibor/Hari,
Thanks for your inputs.
I can understand it doesn't make that much sense, actually my objective
was to release the cpu and I/O resources which by backup process takes.
Our environment is such that it's a highly transaction processing ERP
database (40 GB) in size and avg 2/3 transactions per sec.
The system is high end having 3 processors and 6.5 GB RAM but for
disaster recovery situation, I have my tlogs backup running every 15
mins in between the user transactions and sometimes the backup process
takes too much of CPU resources and I/O since i'm backing up onto
network machine(standby server).
Recently, I was trying out SQLLite which reduces backup and compresses
the size, but it installs extended stored procedures into master
database to do the job and I dont want to install it on my production
database. I was thinking like if i can run the backup remotely, it
would have been good - that's why this remote backup question spawn up.
Guys, thanks for all ur inputs..have a good day
regards,
dilip
Backing up a database remotely
Hi,
How to achieve this?
I want to backup a database via linked server
e.g. BACKUP DATABASE <linkedservername>.Northwind to LinkDbBkup
Or is there any other workaround to achieve this?
Thanks and Regards,
DilipYou can't use a linked server to address the database you want to backup. But you can use
sp_executesql to execute an SQL string on the remote server:
EXEC <linkedservername>.master.dbo.sp_executesql N'BACKUP DATABASE pubs TO DISK = ''c:\pubs.bak'''
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<dilipn123@.gmail.com> wrote in message news:1123568862.267207.106550@.z14g2000cwz.googlegroups.com...
> Hi,
> How to achieve this?
> I want to backup a database via linked server
> e.g. BACKUP DATABASE <linkedservername>.Northwind to LinkDbBkup
> Or is there any other workaround to achieve this?
> Thanks and Regards,
> Dilip
>|||Hi Tibor,
Thanks for the quick response.
Actually, my objective here is to use the processing power of another
machine to backup the remote database. e.g. Server A hosts the database
and Server B is from where I would like to take the backup.
I tried your workaround and it works but it uses the processing power
of Server A and not Server B. e.g. I issued the backup command from
Server B and checked the processes running on Server A by sp_who and it
showed the backup commands. How do I do it from Server B?
TIA
Regards,
Dilip|||Hi,
No You cant.
If want to Backup the database which is in SERVER A, then ultimately
resource usage will be on Server A only.
Server B can be a client to issue the backup command as well as backup
storage; but the Backup command will be
executing in Server A.
Thanks
Hari
SQL Server MVP
<dilipn123@.gmail.com> wrote in message
news:1123571709.739767.266160@.z14g2000cwz.googlegroups.com...
> Hi Tibor,
> Thanks for the quick response.
> Actually, my objective here is to use the processing power of another
> machine to backup the remote database. e.g. Server A hosts the database
> and Server B is from where I would like to take the backup.
> I tried your workaround and it works but it uses the processing power
> of Server A and not Server B. e.g. I issued the backup command from
> Server B and checked the processes running on Server A by sp_who and it
> showed the backup commands. How do I do it from Server B?
> TIA
> Regards,
> Dilip
>|||You can't do that, and it would make little sense as the backup is almost all I/O. Imagine if
database resides on A and you want B to process the backup. Then B would need to read all the data.
How would B get access to the data?
1. B directly accessing the database files over the network. You get into permissions aspects
(service account of B need permissions on files that A uses). Also, it cannot be done since the
files are already opened by the A SQL Server.
2. B asks A to read the files and send the data to B.
Then the backup data has to be written. If they are to be written on B, you can gain a little bit.
If they are to be written on A, you have to push back the data from B to A, making it even more
expensive.
Above is just thinking out loud to hopefully make you see that there would be little, if any, to
gain from such a scheme. In any event, it isn't possible.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<dilipn123@.gmail.com> wrote in message news:1123571709.739767.266160@.z14g2000cwz.googlegroups.com...
> Hi Tibor,
> Thanks for the quick response.
> Actually, my objective here is to use the processing power of another
> machine to backup the remote database. e.g. Server A hosts the database
> and Server B is from where I would like to take the backup.
> I tried your workaround and it works but it uses the processing power
> of Server A and not Server B. e.g. I issued the backup command from
> Server B and checked the processes running on Server A by sp_who and it
> showed the backup commands. How do I do it from Server B?
> TIA
> Regards,
> Dilip
>|||Tibor/Hari,
Thanks for your inputs.
I can understand it doesn't make that much sense, actually my objective
was to release the cpu and I/O resources which by backup process takes.
Our environment is such that it's a highly transaction processing ERP
database (40 GB) in size and avg 2/3 transactions per sec.
The system is high end having 3 processors and 6.5 GB RAM but for
disaster recovery situation, I have my tlogs backup running every 15
mins in between the user transactions and sometimes the backup process
takes too much of CPU resources and I/O since i'm backing up onto
network machine(standby server).
Recently, I was trying out SQLLite which reduces backup and compresses
the size, but it installs extended stored procedures into master
database to do the job and I dont want to install it on my production
database. I was thinking like if i can run the backup remotely, it
would have been good - that's why this remote backup question spawn up.
Guys, thanks for all ur inputs..have a good day
regards,
dilip
How to achieve this?
I want to backup a database via linked server
e.g. BACKUP DATABASE <linkedservername>.Northwind to LinkDbBkup
Or is there any other workaround to achieve this?
Thanks and Regards,
DilipYou can't use a linked server to address the database you want to backup. But you can use
sp_executesql to execute an SQL string on the remote server:
EXEC <linkedservername>.master.dbo.sp_executesql N'BACKUP DATABASE pubs TO DISK = ''c:\pubs.bak'''
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<dilipn123@.gmail.com> wrote in message news:1123568862.267207.106550@.z14g2000cwz.googlegroups.com...
> Hi,
> How to achieve this?
> I want to backup a database via linked server
> e.g. BACKUP DATABASE <linkedservername>.Northwind to LinkDbBkup
> Or is there any other workaround to achieve this?
> Thanks and Regards,
> Dilip
>|||Hi Tibor,
Thanks for the quick response.
Actually, my objective here is to use the processing power of another
machine to backup the remote database. e.g. Server A hosts the database
and Server B is from where I would like to take the backup.
I tried your workaround and it works but it uses the processing power
of Server A and not Server B. e.g. I issued the backup command from
Server B and checked the processes running on Server A by sp_who and it
showed the backup commands. How do I do it from Server B?
TIA
Regards,
Dilip|||Hi,
No You cant.
If want to Backup the database which is in SERVER A, then ultimately
resource usage will be on Server A only.
Server B can be a client to issue the backup command as well as backup
storage; but the Backup command will be
executing in Server A.
Thanks
Hari
SQL Server MVP
<dilipn123@.gmail.com> wrote in message
news:1123571709.739767.266160@.z14g2000cwz.googlegroups.com...
> Hi Tibor,
> Thanks for the quick response.
> Actually, my objective here is to use the processing power of another
> machine to backup the remote database. e.g. Server A hosts the database
> and Server B is from where I would like to take the backup.
> I tried your workaround and it works but it uses the processing power
> of Server A and not Server B. e.g. I issued the backup command from
> Server B and checked the processes running on Server A by sp_who and it
> showed the backup commands. How do I do it from Server B?
> TIA
> Regards,
> Dilip
>|||You can't do that, and it would make little sense as the backup is almost all I/O. Imagine if
database resides on A and you want B to process the backup. Then B would need to read all the data.
How would B get access to the data?
1. B directly accessing the database files over the network. You get into permissions aspects
(service account of B need permissions on files that A uses). Also, it cannot be done since the
files are already opened by the A SQL Server.
2. B asks A to read the files and send the data to B.
Then the backup data has to be written. If they are to be written on B, you can gain a little bit.
If they are to be written on A, you have to push back the data from B to A, making it even more
expensive.
Above is just thinking out loud to hopefully make you see that there would be little, if any, to
gain from such a scheme. In any event, it isn't possible.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<dilipn123@.gmail.com> wrote in message news:1123571709.739767.266160@.z14g2000cwz.googlegroups.com...
> Hi Tibor,
> Thanks for the quick response.
> Actually, my objective here is to use the processing power of another
> machine to backup the remote database. e.g. Server A hosts the database
> and Server B is from where I would like to take the backup.
> I tried your workaround and it works but it uses the processing power
> of Server A and not Server B. e.g. I issued the backup command from
> Server B and checked the processes running on Server A by sp_who and it
> showed the backup commands. How do I do it from Server B?
> TIA
> Regards,
> Dilip
>|||Tibor/Hari,
Thanks for your inputs.
I can understand it doesn't make that much sense, actually my objective
was to release the cpu and I/O resources which by backup process takes.
Our environment is such that it's a highly transaction processing ERP
database (40 GB) in size and avg 2/3 transactions per sec.
The system is high end having 3 processors and 6.5 GB RAM but for
disaster recovery situation, I have my tlogs backup running every 15
mins in between the user transactions and sometimes the backup process
takes too much of CPU resources and I/O since i'm backing up onto
network machine(standby server).
Recently, I was trying out SQLLite which reduces backup and compresses
the size, but it installs extended stored procedures into master
database to do the job and I dont want to install it on my production
database. I was thinking like if i can run the backup remotely, it
would have been good - that's why this remote backup question spawn up.
Guys, thanks for all ur inputs..have a good day
regards,
dilip
Monday, March 19, 2012
Back up
Hi all
I am able to get back up VIA Database>All Tasks>Backup Database but I cannot
schedule it for particular time.
May be I am missing something but I tried all options.
Is there any other thing to do.
RegardsHi,
There are 2 options
1. Use Maintenance plan to backup the database. It has the scheduling option
2. Write ur own script and schedule it using SQL agent -- Jobs inside
Enterprise manager
1. Maintenance Plan
1. Open Enterprise maneger, Expand the Management options and select
"database Maintenance Plans"
2. Right click and select new maintenance plan
3. Click Next and select the database u need to backup and click next till
the "Database Backup Plan " screen comes
4. There you can schedule the time for backup and click next
5. In the backup disk directory , you can specify the backup folder and
click next and specify the number of backup copies to keep
6. COntinue the steps (it is prestty easy to understand)
Maintenance plans use SQL agent to schedule so ensure that SQL Agent service
run all the time in the SQL server Machine.
2. SQL Agent schedule
1. In enterprise manager -- select SQL agent - Jobs
2. Right click jobs and create new job
3. Give a name and go to Steps tab and give the TSQL command to backup
(Backup database <dbname> to disk='c:\backup\dbname.bak' with init)
4. Go to schedule tab and schedule atime for backup
Ensure that SQL Agent service run all the time in the SQL server Machine.
Thanks
Hari
MCDBA
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:#O27QolEEHA.624@.TK2MSFTNGP10.phx.gbl...
> Hi all
>
> I am able to get back up VIA Database>All Tasks>Backup Database but I
cannot
> schedule it for particular time.
> May be I am missing something but I tried all options.
> Is there any other thing to do.
>
> Regards
>|||Thanks Hari
> Ensure that SQL Agent service run all the time in the SQL server Machine
This is the main thing I was missing after running it everything is OK
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23XobD4lEEHA.3344@.tk2msftngp13.phx.gbl...
> Hi,
> There are 2 options
> 1. Use Maintenance plan to backup the database. It has the scheduling
option
> 2. Write ur own script and schedule it using SQL agent -- Jobs inside
> Enterprise manager
> 1. Maintenance Plan
> 1. Open Enterprise maneger, Expand the Management options and select
> "database Maintenance Plans"
> 2. Right click and select new maintenance plan
> 3. Click Next and select the database u need to backup and click next till
> the "Database Backup Plan " screen comes
> 4. There you can schedule the time for backup and click next
> 5. In the backup disk directory , you can specify the backup folder and
> click next and specify the number of backup copies to keep
> 6. COntinue the steps (it is prestty easy to understand)
> Maintenance plans use SQL agent to schedule so ensure that SQL Agent
service
> run all the time in the SQL server Machine.
> 2. SQL Agent schedule
> 1. In enterprise manager -- select SQL agent - Jobs
> 2. Right click jobs and create new job
> 3. Give a name and go to Steps tab and give the TSQL command to backup
> (Backup database <dbname> to disk='c:\backup\dbname.bak' with init)
> 4. Go to schedule tab and schedule atime for backup
> Ensure that SQL Agent service run all the time in the SQL server Machine.
> Thanks
> Hari
> MCDBA
> "F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
> news:#O27QolEEHA.624@.TK2MSFTNGP10.phx.gbl...
> cannot
>
I am able to get back up VIA Database>All Tasks>Backup Database but I cannot
schedule it for particular time.
May be I am missing something but I tried all options.
Is there any other thing to do.
RegardsHi,
There are 2 options
1. Use Maintenance plan to backup the database. It has the scheduling option
2. Write ur own script and schedule it using SQL agent -- Jobs inside
Enterprise manager
1. Maintenance Plan
1. Open Enterprise maneger, Expand the Management options and select
"database Maintenance Plans"
2. Right click and select new maintenance plan
3. Click Next and select the database u need to backup and click next till
the "Database Backup Plan " screen comes
4. There you can schedule the time for backup and click next
5. In the backup disk directory , you can specify the backup folder and
click next and specify the number of backup copies to keep
6. COntinue the steps (it is prestty easy to understand)
Maintenance plans use SQL agent to schedule so ensure that SQL Agent service
run all the time in the SQL server Machine.
2. SQL Agent schedule
1. In enterprise manager -- select SQL agent - Jobs
2. Right click jobs and create new job
3. Give a name and go to Steps tab and give the TSQL command to backup
(Backup database <dbname> to disk='c:\backup\dbname.bak' with init)
4. Go to schedule tab and schedule atime for backup
Ensure that SQL Agent service run all the time in the SQL server Machine.
Thanks
Hari
MCDBA
"F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
news:#O27QolEEHA.624@.TK2MSFTNGP10.phx.gbl...
> Hi all
>
> I am able to get back up VIA Database>All Tasks>Backup Database but I
cannot
> schedule it for particular time.
> May be I am missing something but I tried all options.
> Is there any other thing to do.
>
> Regards
>|||Thanks Hari
> Ensure that SQL Agent service run all the time in the SQL server Machine
This is the main thing I was missing after running it everything is OK
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23XobD4lEEHA.3344@.tk2msftngp13.phx.gbl...
> Hi,
> There are 2 options
> 1. Use Maintenance plan to backup the database. It has the scheduling
option
> 2. Write ur own script and schedule it using SQL agent -- Jobs inside
> Enterprise manager
> 1. Maintenance Plan
> 1. Open Enterprise maneger, Expand the Management options and select
> "database Maintenance Plans"
> 2. Right click and select new maintenance plan
> 3. Click Next and select the database u need to backup and click next till
> the "Database Backup Plan " screen comes
> 4. There you can schedule the time for backup and click next
> 5. In the backup disk directory , you can specify the backup folder and
> click next and specify the number of backup copies to keep
> 6. COntinue the steps (it is prestty easy to understand)
> Maintenance plans use SQL agent to schedule so ensure that SQL Agent
service
> run all the time in the SQL server Machine.
> 2. SQL Agent schedule
> 1. In enterprise manager -- select SQL agent - Jobs
> 2. Right click jobs and create new job
> 3. Give a name and go to Steps tab and give the TSQL command to backup
> (Backup database <dbname> to disk='c:\backup\dbname.bak' with init)
> 4. Go to schedule tab and schedule atime for backup
> Ensure that SQL Agent service run all the time in the SQL server Machine.
> Thanks
> Hari
> MCDBA
> "F@.yy@.Z" <fayyaz.ahmed@.mvwebmaker.com> wrote in message
> news:#O27QolEEHA.624@.TK2MSFTNGP10.phx.gbl...
> cannot
>
Back Issue
Following is my log file for transaction log backup via DB maintainance plan.
The step 2 of the process fails due to the reason mentioned.
I can try omitting the verify backup step in the plan, however wanted to
know if anybody has come accross this before and found a solution.
thanks
Starting maintenance plan 'SDS2 Database' on 9/20/2005 4:00:01 PM
[1] Database sds2: Transaction Log Backup...
Destination: [\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_2005092 01600.TRN]
** Execution Time: 0 hrs, 0 mins, 32 secs **
[2] Database sds2: Verifying Backup...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203: [Microsoft][ODBC SQL
Server Driver][SQL Server]Read on
'\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_2005092 01600.TRN' failed, status =
1450. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
terminating abnormally.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'SDS2 Database' on 9/20/2005 4:00:33 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Hi
Looks like you are having network problems during the verify.
Have you tried backup up the file to a local disk and then verifying it?
Backup up across a network is rather risky as you are relying on your
backups to be 100%, but might be working on an un-reliable network.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"b_dba" <b_dba@.discussions.microsoft.com> wrote in message
news:B669C041-0235-4673-94C1-DC0002998145@.microsoft.com...
> Following is my log file for transaction log backup via DB maintainance
> plan.
> The step 2 of the process fails due to the reason mentioned.
> I can try omitting the verify backup step in the plan, however wanted to
> know if anybody has come accross this before and found a solution.
> thanks
>
> Starting maintenance plan 'SDS2 Database' on 9/20/2005 4:00:01 PM
> [1] Database sds2: Transaction Log Backup...
> Destination:
> [\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_2005092 01600.TRN]
> ** Execution Time: 0 hrs, 0 mins, 32 secs **
> [2] Database sds2: Verifying Backup...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]Read on
> '\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_2005092 01600.TRN' failed,
> status =
> 1450. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
> terminating abnormally.
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'SDS2 Database' on 9/20/2005 4:00:33 PM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
The step 2 of the process fails due to the reason mentioned.
I can try omitting the verify backup step in the plan, however wanted to
know if anybody has come accross this before and found a solution.
thanks
Starting maintenance plan 'SDS2 Database' on 9/20/2005 4:00:01 PM
[1] Database sds2: Transaction Log Backup...
Destination: [\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_2005092 01600.TRN]
** Execution Time: 0 hrs, 0 mins, 32 secs **
[2] Database sds2: Verifying Backup...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203: [Microsoft][ODBC SQL
Server Driver][SQL Server]Read on
'\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_2005092 01600.TRN' failed, status =
1450. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
terminating abnormally.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'SDS2 Database' on 9/20/2005 4:00:33 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Hi
Looks like you are having network problems during the verify.
Have you tried backup up the file to a local disk and then verifying it?
Backup up across a network is rather risky as you are relying on your
backups to be 100%, but might be working on an un-reliable network.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"b_dba" <b_dba@.discussions.microsoft.com> wrote in message
news:B669C041-0235-4673-94C1-DC0002998145@.microsoft.com...
> Following is my log file for transaction log backup via DB maintainance
> plan.
> The step 2 of the process fails due to the reason mentioned.
> I can try omitting the verify backup step in the plan, however wanted to
> know if anybody has come accross this before and found a solution.
> thanks
>
> Starting maintenance plan 'SDS2 Database' on 9/20/2005 4:00:01 PM
> [1] Database sds2: Transaction Log Backup...
> Destination:
> [\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_2005092 01600.TRN]
> ** Execution Time: 0 hrs, 0 mins, 32 secs **
> [2] Database sds2: Verifying Backup...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]Read on
> '\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_2005092 01600.TRN' failed,
> status =
> 1450. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
> terminating abnormally.
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'SDS2 Database' on 9/20/2005 4:00:33 PM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
Back Issue
Following is my log file for transaction log backup via DB maintainance plan.
The step 2 of the process fails due to the reason mentioned.
I can try omitting the verify backup step in the plan, however wanted to
know if anybody has come accross this before and found a solution.
thanks
Starting maintenance plan 'SDS2 Database' on 9/20/2005 4:00:01 PM
[1] Database sds2: Transaction Log Backup...
Destination: [\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_200509201600.TRN]
** Execution Time: 0 hrs, 0 mins, 32 secs **
[2] Database sds2: Verifying Backup...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203: [Microsoft][ODBC SQL
Server Driver][SQL Server]Read on
'\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_200509201600.TRN' failed, status = 1450. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
terminating abnormally.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'SDS2 Database' on 9/20/2005 4:00:33 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)Hi
Looks like you are having network problems during the verify.
Have you tried backup up the file to a local disk and then verifying it?
Backup up across a network is rather risky as you are relying on your
backups to be 100%, but might be working on an un-reliable network.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"b_dba" <b_dba@.discussions.microsoft.com> wrote in message
news:B669C041-0235-4673-94C1-DC0002998145@.microsoft.com...
> Following is my log file for transaction log backup via DB maintainance
> plan.
> The step 2 of the process fails due to the reason mentioned.
> I can try omitting the verify backup step in the plan, however wanted to
> know if anybody has come accross this before and found a solution.
> thanks
>
> Starting maintenance plan 'SDS2 Database' on 9/20/2005 4:00:01 PM
> [1] Database sds2: Transaction Log Backup...
> Destination:
> [\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_200509201600.TRN]
> ** Execution Time: 0 hrs, 0 mins, 32 secs **
> [2] Database sds2: Verifying Backup...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]Read on
> '\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_200509201600.TRN' failed,
> status => 1450. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
> terminating abnormally.
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'SDS2 Database' on 9/20/2005 4:00:33 PM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
The step 2 of the process fails due to the reason mentioned.
I can try omitting the verify backup step in the plan, however wanted to
know if anybody has come accross this before and found a solution.
thanks
Starting maintenance plan 'SDS2 Database' on 9/20/2005 4:00:01 PM
[1] Database sds2: Transaction Log Backup...
Destination: [\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_200509201600.TRN]
** Execution Time: 0 hrs, 0 mins, 32 secs **
[2] Database sds2: Verifying Backup...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203: [Microsoft][ODBC SQL
Server Driver][SQL Server]Read on
'\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_200509201600.TRN' failed, status = 1450. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
terminating abnormally.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'SDS2 Database' on 9/20/2005 4:00:33 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)Hi
Looks like you are having network problems during the verify.
Have you tried backup up the file to a local disk and then verifying it?
Backup up across a network is rather risky as you are relying on your
backups to be 100%, but might be working on an un-reliable network.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"b_dba" <b_dba@.discussions.microsoft.com> wrote in message
news:B669C041-0235-4673-94C1-DC0002998145@.microsoft.com...
> Following is my log file for transaction log backup via DB maintainance
> plan.
> The step 2 of the process fails due to the reason mentioned.
> I can try omitting the verify backup step in the plan, however wanted to
> know if anybody has come accross this before and found a solution.
> thanks
>
> Starting maintenance plan 'SDS2 Database' on 9/20/2005 4:00:01 PM
> [1] Database sds2: Transaction Log Backup...
> Destination:
> [\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_200509201600.TRN]
> ** Execution Time: 0 hrs, 0 mins, 32 secs **
> [2] Database sds2: Verifying Backup...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]Read on
> '\\epsql09sa\e$\backup\epsql12sa\sds2_tlog_200509201600.TRN' failed,
> status => 1450. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is
> terminating abnormally.
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'SDS2 Database' on 9/20/2005 4:00:33 PM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
Back Issue
Following is my log file for transaction log backup via DB maintainance plan
.
The step 2 of the process fails due to the reason mentioned.
I can try omitting the verify backup step in the plan, however wanted to
know if anybody has come accross this before and found a solution.
thanks
Starting maintenance plan 'SDS2 Database' on 9/20/2005 4:00:01 PM
[1] Database sds2: Transaction Log Backup...
Destination: & #91;\\epsql09sa\e$\backup\epsql12sa\sds2
_tlog_200509201600.TRN
]
** Execution Time: 0 hrs, 0 mins, 32 secs **
[2] Database sds2: Verifying Backup...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Read on
'\\epsql09sa\e$\backup\epsql12sa\sds2_tl
og_200509201600.TRN' failed, status
=
1450. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE i
s
terminating abnormally.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'SDS2 Database' on 9/20/2005 4:00:33 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)Hi
Looks like you are having network problems during the verify.
Have you tried backup up the file to a local disk and then verifying it?
Backup up across a network is rather risky as you are relying on your
backups to be 100%, but might be working on an un-reliable network.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"b_dba" <b_dba@.discussions.microsoft.com> wrote in message
news:B669C041-0235-4673-94C1-DC0002998145@.microsoft.com...
> Following is my log file for transaction log backup via DB maintainance
> plan.
> The step 2 of the process fails due to the reason mentioned.
> I can try omitting the verify backup step in the plan, however wanted to
> know if anybody has come accross this before and found a solution.
> thanks
>
> Starting maintenance plan 'SDS2 Database' on 9/20/2005 4:00:01 PM
> [1] Database sds2: Transaction Log Backup...
> Destination:
> & #91;\\epsql09sa\e$\backup\epsql12sa\sds2
_tlog_200509201600.TRN]
> ** Execution Time: 0 hrs, 0 mins, 32 secs **
> [2] Database sds2: Verifying Backup...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203: [Microsoft]
[ODBC
> SQL
> Server Driver][SQL Server]Read on
> '\\epsql09sa\e$\backup\epsql12sa\sds2_tl
og_200509201600.TRN' failed,
> status =
> 1450. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE
is
> terminating abnormally.
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'SDS2 Database' on 9/20/2005 4:00:33 PM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
.
The step 2 of the process fails due to the reason mentioned.
I can try omitting the verify backup step in the plan, however wanted to
know if anybody has come accross this before and found a solution.
thanks
Starting maintenance plan 'SDS2 Database' on 9/20/2005 4:00:01 PM
[1] Database sds2: Transaction Log Backup...
Destination: & #91;\\epsql09sa\e$\backup\epsql12sa\sds2
_tlog_200509201600.TRN
]
** Execution Time: 0 hrs, 0 mins, 32 secs **
[2] Database sds2: Verifying Backup...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Read on
'\\epsql09sa\e$\backup\epsql12sa\sds2_tl
og_200509201600.TRN' failed, status
=
1450. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE i
s
terminating abnormally.
Deleting old text reports... 0 file(s) deleted.
End of maintenance plan 'SDS2 Database' on 9/20/2005 4:00:33 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)Hi
Looks like you are having network problems during the verify.
Have you tried backup up the file to a local disk and then verifying it?
Backup up across a network is rather risky as you are relying on your
backups to be 100%, but might be working on an un-reliable network.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"b_dba" <b_dba@.discussions.microsoft.com> wrote in message
news:B669C041-0235-4673-94C1-DC0002998145@.microsoft.com...
> Following is my log file for transaction log backup via DB maintainance
> plan.
> The step 2 of the process fails due to the reason mentioned.
> I can try omitting the verify backup step in the plan, however wanted to
> know if anybody has come accross this before and found a solution.
> thanks
>
> Starting maintenance plan 'SDS2 Database' on 9/20/2005 4:00:01 PM
> [1] Database sds2: Transaction Log Backup...
> Destination:
> & #91;\\epsql09sa\e$\backup\epsql12sa\sds2
_tlog_200509201600.TRN]
> ** Execution Time: 0 hrs, 0 mins, 32 secs **
> [2] Database sds2: Verifying Backup...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3203: [Microsoft]
[ODBC
> SQL
> Server Driver][SQL Server]Read on
> '\\epsql09sa\e$\backup\epsql12sa\sds2_tl
og_200509201600.TRN' failed,
> status =
> 1450. See the SQL Server error log for more details.
> [Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE
is
> terminating abnormally.
> Deleting old text reports... 0 file(s) deleted.
> End of maintenance plan 'SDS2 Database' on 9/20/2005 4:00:33 PM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
Sunday, February 19, 2012
avoid deadlock at all cost
Hi,
Is there a way to avoid deadlock completely, it will be ok for me if it goes
a store procedure with serializable execution (not just result via isolation
level). I can do it in my code but since we have many different servers, we
cannot easily serialize the execution in the code, so I'm looking for a way
to serialize the execution of store proc in the db level. It's only involved
with just one table but with 3 operations: read, update, and delete. I don't
care about performance cost of the serialization. Is that possible? Thanks!You can serialize sections of code in your stored procedures
by using sp_getapplock/sp_releaseapplock. Look in BOL
for more information.|||Based on the conversation that I saw, there is no guarantee to avoid
deadlock via sp_getapplock. See:
[url]http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread
/b5216d42905d2a6/f878db90a5a25c38?lnk=st&q=sp_getapplock&rnum=1#f878db90a5a25c38[/
url]
Any other idea? Thanks!
<markc600@.hotmail.com> wrote in message
news:1147709999.616489.13200@.v46g2000cwv.googlegroups.com...
> You can serialize sections of code in your stored procedures
> by using sp_getapplock/sp_releaseapplock. Look in BOL
> for more information.
>|||"Zen" <zen@.nononospam.com> wrote in message
news:ey9swOEeGHA.4576@.TK2MSFTNGP05.phx.gbl...
> Based on the conversation that I saw, there is no guarantee to avoid
> deadlock via sp_getapplock. See:
> http://groups.google.com/group/micr...5a25c38
> Any other idea? Thanks!
>
It will work. Depending on the scope of your transactions and the other
queries running in other sessions, a procedure using sp_getapplock can still
have locking or deadlocking problems.
However, sp_getapplock will prevent multiple connections invoking the same
stored procedure from deadlocking. If the connections call the procedure in
the scope of a larger transaction, they will acquire and keep other locks
for the duration of the transaction, so there is no absolute guarantee.
So sp_getapplock is a very effective tool for eliminating deadlocks.
Also in SQL Server 2005 READ COMMITED SNAPSHOT ISOLATION will reduce
deadlocks by eliminating the shared locks.
David|||That sounds great, how do we ensure that the lock is released at the end of
the transaction regardless of what happens? From what I understand some
fatal error can stop sql execution immediately and the next error catching
statement might not be executed, so trapping error and calling
sp_releaseapplock doesn't always work. Any idea? thanks!!
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:utyiKfEeGHA.2456@.TK2MSFTNGP04.phx.gbl...
> "Zen" <zen@.nononospam.com> wrote in message
> news:ey9swOEeGHA.4576@.TK2MSFTNGP05.phx.gbl...
> It will work. Depending on the scope of your transactions and the other
> queries running in other sessions, a procedure using sp_getapplock can
> still have locking or deadlocking problems.
> However, sp_getapplock will prevent multiple connections invoking the same
> stored procedure from deadlocking. If the connections call the procedure
> in the scope of a larger transaction, they will acquire and keep other
> locks for the duration of the transaction, so there is no absolute
> guarantee.
> So sp_getapplock is a very effective tool for eliminating deadlocks.
> Also in SQL Server 2005 READ COMMITED SNAPSHOT ISOLATION will reduce
> deadlocks by eliminating the shared locks.
> David
>|||"Zen" <zen@.nononospam.com> wrote in message
news:u0yhdnEeGHA.3348@.TK2MSFTNGP03.phx.gbl...
> That sounds great, how do we ensure that the lock is released at the end
> of the transaction regardless of what happens? From what I understand some
> fatal error can stop sql execution immediately and the next error catching
> statement might not be executed, so trapping error and calling
> sp_releaseapplock doesn't always work. Any idea? thanks!!
>
I would never use sp_releaseapplock in the first place.
You can use sp_getapplock to create session-level or transaction-level
application locks. session-level locks are tricky because of the issue you
identified (you must somehow guarantee that the lock gets released). So I
would never use a session-level application lock. Instead use a
transaction-level application lock, which is released automatically when
your transaction ends.
If you start a transaction and use the default value for the @.LockOwner
argument to sp_getapplock, then the app lock will be enlisted into your
transaction like any other lock. It will automatically be released when
your transaction is either commited or rolled back.
David
Is there a way to avoid deadlock completely, it will be ok for me if it goes
a store procedure with serializable execution (not just result via isolation
level). I can do it in my code but since we have many different servers, we
cannot easily serialize the execution in the code, so I'm looking for a way
to serialize the execution of store proc in the db level. It's only involved
with just one table but with 3 operations: read, update, and delete. I don't
care about performance cost of the serialization. Is that possible? Thanks!You can serialize sections of code in your stored procedures
by using sp_getapplock/sp_releaseapplock. Look in BOL
for more information.|||Based on the conversation that I saw, there is no guarantee to avoid
deadlock via sp_getapplock. See:
[url]http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread
/b5216d42905d2a6/f878db90a5a25c38?lnk=st&q=sp_getapplock&rnum=1#f878db90a5a25c38[/
url]
Any other idea? Thanks!
<markc600@.hotmail.com> wrote in message
news:1147709999.616489.13200@.v46g2000cwv.googlegroups.com...
> You can serialize sections of code in your stored procedures
> by using sp_getapplock/sp_releaseapplock. Look in BOL
> for more information.
>|||"Zen" <zen@.nononospam.com> wrote in message
news:ey9swOEeGHA.4576@.TK2MSFTNGP05.phx.gbl...
> Based on the conversation that I saw, there is no guarantee to avoid
> deadlock via sp_getapplock. See:
> http://groups.google.com/group/micr...5a25c38
> Any other idea? Thanks!
>
It will work. Depending on the scope of your transactions and the other
queries running in other sessions, a procedure using sp_getapplock can still
have locking or deadlocking problems.
However, sp_getapplock will prevent multiple connections invoking the same
stored procedure from deadlocking. If the connections call the procedure in
the scope of a larger transaction, they will acquire and keep other locks
for the duration of the transaction, so there is no absolute guarantee.
So sp_getapplock is a very effective tool for eliminating deadlocks.
Also in SQL Server 2005 READ COMMITED SNAPSHOT ISOLATION will reduce
deadlocks by eliminating the shared locks.
David|||That sounds great, how do we ensure that the lock is released at the end of
the transaction regardless of what happens? From what I understand some
fatal error can stop sql execution immediately and the next error catching
statement might not be executed, so trapping error and calling
sp_releaseapplock doesn't always work. Any idea? thanks!!
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:utyiKfEeGHA.2456@.TK2MSFTNGP04.phx.gbl...
> "Zen" <zen@.nononospam.com> wrote in message
> news:ey9swOEeGHA.4576@.TK2MSFTNGP05.phx.gbl...
> It will work. Depending on the scope of your transactions and the other
> queries running in other sessions, a procedure using sp_getapplock can
> still have locking or deadlocking problems.
> However, sp_getapplock will prevent multiple connections invoking the same
> stored procedure from deadlocking. If the connections call the procedure
> in the scope of a larger transaction, they will acquire and keep other
> locks for the duration of the transaction, so there is no absolute
> guarantee.
> So sp_getapplock is a very effective tool for eliminating deadlocks.
> Also in SQL Server 2005 READ COMMITED SNAPSHOT ISOLATION will reduce
> deadlocks by eliminating the shared locks.
> David
>|||"Zen" <zen@.nononospam.com> wrote in message
news:u0yhdnEeGHA.3348@.TK2MSFTNGP03.phx.gbl...
> That sounds great, how do we ensure that the lock is released at the end
> of the transaction regardless of what happens? From what I understand some
> fatal error can stop sql execution immediately and the next error catching
> statement might not be executed, so trapping error and calling
> sp_releaseapplock doesn't always work. Any idea? thanks!!
>
I would never use sp_releaseapplock in the first place.
You can use sp_getapplock to create session-level or transaction-level
application locks. session-level locks are tricky because of the issue you
identified (you must somehow guarantee that the lock gets released). So I
would never use a session-level application lock. Instead use a
transaction-level application lock, which is released automatically when
your transaction ends.
If you start a transaction and use the default value for the @.LockOwner
argument to sp_getapplock, then the app lock will be enlisted into your
transaction like any other lock. It will automatically be released when
your transaction is either commited or rolled back.
David
avoid deadlock at all cost
Hi,
Is there a way to avoid deadlock completely, it will be ok for me if it goes
a store procedure with serializable execution (not just result via isolation
level). I can do it in my code but since we have many different servers, we
cannot easily serialize the execution in the code, so I'm looking for a way
to serialize the execution of store proc in the db level. It's only involved
with just one table but with 3 operations: read, update, and delete. I don't
care about performance cost of the serialization. Is that possible? Thanks!You can serialize sections of code in your stored procedures
by using sp_getapplock/sp_releaseapplock. Look in BOL
for more information.|||Based on the conversation that I saw, there is no guarantee to avoid
deadlock via sp_getapplock. See:
[url]http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread
/b5216d42905d2a6/f878db90a5a25c38?lnk=st&q=sp_getapplock&rnum=1#f878db90a5a25c38[/
url]
Any other idea? Thanks!
<markc600@.hotmail.com> wrote in message
news:1147709999.616489.13200@.v46g2000cwv.googlegroups.com...
> You can serialize sections of code in your stored procedures
> by using sp_getapplock/sp_releaseapplock. Look in BOL
> for more information.
>|||"Zen" <zen@.nononospam.com> wrote in message
news:ey9swOEeGHA.4576@.TK2MSFTNGP05.phx.gbl...
> Based on the conversation that I saw, there is no guarantee to avoid
> deadlock via sp_getapplock. See:
> http://groups.google.com/group/micr...5a25c38
> Any other idea? Thanks!
>
It will work. Depending on the scope of your transactions and the other
queries running in other sessions, a procedure using sp_getapplock can still
have locking or deadlocking problems.
However, sp_getapplock will prevent multiple connections invoking the same
stored procedure from deadlocking. If the connections call the procedure in
the scope of a larger transaction, they will acquire and keep other locks
for the duration of the transaction, so there is no absolute guarantee.
So sp_getapplock is a very effective tool for eliminating deadlocks.
Also in SQL Server 2005 READ COMMITED SNAPSHOT ISOLATION will reduce
deadlocks by eliminating the shared locks.
David|||That sounds great, how do we ensure that the lock is released at the end of
the transaction regardless of what happens? From what I understand some
fatal error can stop sql execution immediately and the next error catching
statement might not be executed, so trapping error and calling
sp_releaseapplock doesn't always work. Any idea? thanks!!
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:utyiKfEeGHA.2456@.TK2MSFTNGP04.phx.gbl...
> "Zen" <zen@.nononospam.com> wrote in message
> news:ey9swOEeGHA.4576@.TK2MSFTNGP05.phx.gbl...
> It will work. Depending on the scope of your transactions and the other
> queries running in other sessions, a procedure using sp_getapplock can
> still have locking or deadlocking problems.
> However, sp_getapplock will prevent multiple connections invoking the same
> stored procedure from deadlocking. If the connections call the procedure
> in the scope of a larger transaction, they will acquire and keep other
> locks for the duration of the transaction, so there is no absolute
> guarantee.
> So sp_getapplock is a very effective tool for eliminating deadlocks.
> Also in SQL Server 2005 READ COMMITED SNAPSHOT ISOLATION will reduce
> deadlocks by eliminating the shared locks.
> David
>|||"Zen" <zen@.nononospam.com> wrote in message
news:u0yhdnEeGHA.3348@.TK2MSFTNGP03.phx.gbl...
> That sounds great, how do we ensure that the lock is released at the end
> of the transaction regardless of what happens? From what I understand some
> fatal error can stop sql execution immediately and the next error catching
> statement might not be executed, so trapping error and calling
> sp_releaseapplock doesn't always work. Any idea? thanks!!
>
I would never use sp_releaseapplock in the first place.
You can use sp_getapplock to create session-level or transaction-level
application locks. session-level locks are tricky because of the issue you
identified (you must somehow guarantee that the lock gets released). So I
would never use a session-level application lock. Instead use a
transaction-level application lock, which is released automatically when
your transaction ends.
If you start a transaction and use the default value for the @.LockOwner
argument to sp_getapplock, then the app lock will be enlisted into your
transaction like any other lock. It will automatically be released when
your transaction is either commited or rolled back.
David
Is there a way to avoid deadlock completely, it will be ok for me if it goes
a store procedure with serializable execution (not just result via isolation
level). I can do it in my code but since we have many different servers, we
cannot easily serialize the execution in the code, so I'm looking for a way
to serialize the execution of store proc in the db level. It's only involved
with just one table but with 3 operations: read, update, and delete. I don't
care about performance cost of the serialization. Is that possible? Thanks!You can serialize sections of code in your stored procedures
by using sp_getapplock/sp_releaseapplock. Look in BOL
for more information.|||Based on the conversation that I saw, there is no guarantee to avoid
deadlock via sp_getapplock. See:
[url]http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread
/b5216d42905d2a6/f878db90a5a25c38?lnk=st&q=sp_getapplock&rnum=1#f878db90a5a25c38[/
url]
Any other idea? Thanks!
<markc600@.hotmail.com> wrote in message
news:1147709999.616489.13200@.v46g2000cwv.googlegroups.com...
> You can serialize sections of code in your stored procedures
> by using sp_getapplock/sp_releaseapplock. Look in BOL
> for more information.
>|||"Zen" <zen@.nononospam.com> wrote in message
news:ey9swOEeGHA.4576@.TK2MSFTNGP05.phx.gbl...
> Based on the conversation that I saw, there is no guarantee to avoid
> deadlock via sp_getapplock. See:
> http://groups.google.com/group/micr...5a25c38
> Any other idea? Thanks!
>
It will work. Depending on the scope of your transactions and the other
queries running in other sessions, a procedure using sp_getapplock can still
have locking or deadlocking problems.
However, sp_getapplock will prevent multiple connections invoking the same
stored procedure from deadlocking. If the connections call the procedure in
the scope of a larger transaction, they will acquire and keep other locks
for the duration of the transaction, so there is no absolute guarantee.
So sp_getapplock is a very effective tool for eliminating deadlocks.
Also in SQL Server 2005 READ COMMITED SNAPSHOT ISOLATION will reduce
deadlocks by eliminating the shared locks.
David|||That sounds great, how do we ensure that the lock is released at the end of
the transaction regardless of what happens? From what I understand some
fatal error can stop sql execution immediately and the next error catching
statement might not be executed, so trapping error and calling
sp_releaseapplock doesn't always work. Any idea? thanks!!
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:utyiKfEeGHA.2456@.TK2MSFTNGP04.phx.gbl...
> "Zen" <zen@.nononospam.com> wrote in message
> news:ey9swOEeGHA.4576@.TK2MSFTNGP05.phx.gbl...
> It will work. Depending on the scope of your transactions and the other
> queries running in other sessions, a procedure using sp_getapplock can
> still have locking or deadlocking problems.
> However, sp_getapplock will prevent multiple connections invoking the same
> stored procedure from deadlocking. If the connections call the procedure
> in the scope of a larger transaction, they will acquire and keep other
> locks for the duration of the transaction, so there is no absolute
> guarantee.
> So sp_getapplock is a very effective tool for eliminating deadlocks.
> Also in SQL Server 2005 READ COMMITED SNAPSHOT ISOLATION will reduce
> deadlocks by eliminating the shared locks.
> David
>|||"Zen" <zen@.nononospam.com> wrote in message
news:u0yhdnEeGHA.3348@.TK2MSFTNGP03.phx.gbl...
> That sounds great, how do we ensure that the lock is released at the end
> of the transaction regardless of what happens? From what I understand some
> fatal error can stop sql execution immediately and the next error catching
> statement might not be executed, so trapping error and calling
> sp_releaseapplock doesn't always work. Any idea? thanks!!
>
I would never use sp_releaseapplock in the first place.
You can use sp_getapplock to create session-level or transaction-level
application locks. session-level locks are tricky because of the issue you
identified (you must somehow guarantee that the lock gets released). So I
would never use a session-level application lock. Instead use a
transaction-level application lock, which is released automatically when
your transaction ends.
If you start a transaction and use the default value for the @.LockOwner
argument to sp_getapplock, then the app lock will be enlisted into your
transaction like any other lock. It will automatically be released when
your transaction is either commited or rolled back.
David
avoid deadlock at all cost
Hi,
Is there a way to avoid deadlock completely, it will be ok for me if it goes
a store procedure with serializable execution (not just result via isolation
level). I can do it in my code but since we have many different servers, we
cannot easily serialize the execution in the code, so I'm looking for a way
to serialize the execution of store proc in the db level. It's only involved
with just one table but with 3 operations: read, update, and delete. I don't
care about performance cost of the serialization. Is that possible? Thanks!You can serialize sections of code in your stored procedures
by using sp_getapplock/sp_releaseapplock. Look in BOL
for more information.|||Based on the conversation that I saw, there is no guarantee to avoid
deadlock via sp_getapplock. See:
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/b5216d42905d2a6/f878db90a5a25c38?lnk=st&q=sp_getapplock&rnum=1#f878db90a5a25c38
Any other idea? Thanks!
<markc600@.hotmail.com> wrote in message
news:1147709999.616489.13200@.v46g2000cwv.googlegroups.com...
> You can serialize sections of code in your stored procedures
> by using sp_getapplock/sp_releaseapplock. Look in BOL
> for more information.
>|||"Zen" <zen@.nononospam.com> wrote in message
news:ey9swOEeGHA.4576@.TK2MSFTNGP05.phx.gbl...
> Based on the conversation that I saw, there is no guarantee to avoid
> deadlock via sp_getapplock. See:
> http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/b5216d42905d2a6/f878db90a5a25c38?lnk=st&q=sp_getapplock&rnum=1#f878db90a5a25c38
> Any other idea? Thanks!
>
It will work. Depending on the scope of your transactions and the other
queries running in other sessions, a procedure using sp_getapplock can still
have locking or deadlocking problems.
However, sp_getapplock will prevent multiple connections invoking the same
stored procedure from deadlocking. If the connections call the procedure in
the scope of a larger transaction, they will acquire and keep other locks
for the duration of the transaction, so there is no absolute guarantee.
So sp_getapplock is a very effective tool for eliminating deadlocks.
Also in SQL Server 2005 READ COMMITED SNAPSHOT ISOLATION will reduce
deadlocks by eliminating the shared locks.
David|||That sounds great, how do we ensure that the lock is released at the end of
the transaction regardless of what happens? From what I understand some
fatal error can stop sql execution immediately and the next error catching
statement might not be executed, so trapping error and calling
sp_releaseapplock doesn't always work. Any idea? thanks!!
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:utyiKfEeGHA.2456@.TK2MSFTNGP04.phx.gbl...
> "Zen" <zen@.nononospam.com> wrote in message
> news:ey9swOEeGHA.4576@.TK2MSFTNGP05.phx.gbl...
>> Based on the conversation that I saw, there is no guarantee to avoid
>> deadlock via sp_getapplock. See:
>> http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/b5216d42905d2a6/f878db90a5a25c38?lnk=st&q=sp_getapplock&rnum=1#f878db90a5a25c38
>> Any other idea? Thanks!
> It will work. Depending on the scope of your transactions and the other
> queries running in other sessions, a procedure using sp_getapplock can
> still have locking or deadlocking problems.
> However, sp_getapplock will prevent multiple connections invoking the same
> stored procedure from deadlocking. If the connections call the procedure
> in the scope of a larger transaction, they will acquire and keep other
> locks for the duration of the transaction, so there is no absolute
> guarantee.
> So sp_getapplock is a very effective tool for eliminating deadlocks.
> Also in SQL Server 2005 READ COMMITED SNAPSHOT ISOLATION will reduce
> deadlocks by eliminating the shared locks.
> David
>|||"Zen" <zen@.nononospam.com> wrote in message
news:u0yhdnEeGHA.3348@.TK2MSFTNGP03.phx.gbl...
> That sounds great, how do we ensure that the lock is released at the end
> of the transaction regardless of what happens? From what I understand some
> fatal error can stop sql execution immediately and the next error catching
> statement might not be executed, so trapping error and calling
> sp_releaseapplock doesn't always work. Any idea? thanks!!
>
I would never use sp_releaseapplock in the first place.
You can use sp_getapplock to create session-level or transaction-level
application locks. session-level locks are tricky because of the issue you
identified (you must somehow guarantee that the lock gets released). So I
would never use a session-level application lock. Instead use a
transaction-level application lock, which is released automatically when
your transaction ends.
If you start a transaction and use the default value for the @.LockOwner
argument to sp_getapplock, then the app lock will be enlisted into your
transaction like any other lock. It will automatically be released when
your transaction is either commited or rolled back.
David
Is there a way to avoid deadlock completely, it will be ok for me if it goes
a store procedure with serializable execution (not just result via isolation
level). I can do it in my code but since we have many different servers, we
cannot easily serialize the execution in the code, so I'm looking for a way
to serialize the execution of store proc in the db level. It's only involved
with just one table but with 3 operations: read, update, and delete. I don't
care about performance cost of the serialization. Is that possible? Thanks!You can serialize sections of code in your stored procedures
by using sp_getapplock/sp_releaseapplock. Look in BOL
for more information.|||Based on the conversation that I saw, there is no guarantee to avoid
deadlock via sp_getapplock. See:
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/b5216d42905d2a6/f878db90a5a25c38?lnk=st&q=sp_getapplock&rnum=1#f878db90a5a25c38
Any other idea? Thanks!
<markc600@.hotmail.com> wrote in message
news:1147709999.616489.13200@.v46g2000cwv.googlegroups.com...
> You can serialize sections of code in your stored procedures
> by using sp_getapplock/sp_releaseapplock. Look in BOL
> for more information.
>|||"Zen" <zen@.nononospam.com> wrote in message
news:ey9swOEeGHA.4576@.TK2MSFTNGP05.phx.gbl...
> Based on the conversation that I saw, there is no guarantee to avoid
> deadlock via sp_getapplock. See:
> http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/b5216d42905d2a6/f878db90a5a25c38?lnk=st&q=sp_getapplock&rnum=1#f878db90a5a25c38
> Any other idea? Thanks!
>
It will work. Depending on the scope of your transactions and the other
queries running in other sessions, a procedure using sp_getapplock can still
have locking or deadlocking problems.
However, sp_getapplock will prevent multiple connections invoking the same
stored procedure from deadlocking. If the connections call the procedure in
the scope of a larger transaction, they will acquire and keep other locks
for the duration of the transaction, so there is no absolute guarantee.
So sp_getapplock is a very effective tool for eliminating deadlocks.
Also in SQL Server 2005 READ COMMITED SNAPSHOT ISOLATION will reduce
deadlocks by eliminating the shared locks.
David|||That sounds great, how do we ensure that the lock is released at the end of
the transaction regardless of what happens? From what I understand some
fatal error can stop sql execution immediately and the next error catching
statement might not be executed, so trapping error and calling
sp_releaseapplock doesn't always work. Any idea? thanks!!
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:utyiKfEeGHA.2456@.TK2MSFTNGP04.phx.gbl...
> "Zen" <zen@.nononospam.com> wrote in message
> news:ey9swOEeGHA.4576@.TK2MSFTNGP05.phx.gbl...
>> Based on the conversation that I saw, there is no guarantee to avoid
>> deadlock via sp_getapplock. See:
>> http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/b5216d42905d2a6/f878db90a5a25c38?lnk=st&q=sp_getapplock&rnum=1#f878db90a5a25c38
>> Any other idea? Thanks!
> It will work. Depending on the scope of your transactions and the other
> queries running in other sessions, a procedure using sp_getapplock can
> still have locking or deadlocking problems.
> However, sp_getapplock will prevent multiple connections invoking the same
> stored procedure from deadlocking. If the connections call the procedure
> in the scope of a larger transaction, they will acquire and keep other
> locks for the duration of the transaction, so there is no absolute
> guarantee.
> So sp_getapplock is a very effective tool for eliminating deadlocks.
> Also in SQL Server 2005 READ COMMITED SNAPSHOT ISOLATION will reduce
> deadlocks by eliminating the shared locks.
> David
>|||"Zen" <zen@.nononospam.com> wrote in message
news:u0yhdnEeGHA.3348@.TK2MSFTNGP03.phx.gbl...
> That sounds great, how do we ensure that the lock is released at the end
> of the transaction regardless of what happens? From what I understand some
> fatal error can stop sql execution immediately and the next error catching
> statement might not be executed, so trapping error and calling
> sp_releaseapplock doesn't always work. Any idea? thanks!!
>
I would never use sp_releaseapplock in the first place.
You can use sp_getapplock to create session-level or transaction-level
application locks. session-level locks are tricky because of the issue you
identified (you must somehow guarantee that the lock gets released). So I
would never use a session-level application lock. Instead use a
transaction-level application lock, which is released automatically when
your transaction ends.
If you start a transaction and use the default value for the @.LockOwner
argument to sp_getapplock, then the app lock will be enlisted into your
transaction like any other lock. It will automatically be released when
your transaction is either commited or rolled back.
David
Sunday, February 12, 2012
AutoStart Failed When Rebooted
I have the SQL account services set to autostart via sql server properties.
Question, what would be the reason why it would not autostart when I reboote
d
the server? I noticed when they installed SQL server that the program file
s
was pointed to the D drive (SAN).
Thanks,
LDSQL Sever is trying to start before the os connects to the SAN
What type of SAN are you using?
--
paulz
"LITO" wrote:
> I have the SQL account services set to autostart via sql server properties
.
> Question, what would be the reason why it would not autostart when I reboo
ted
> the server? I noticed when they installed SQL server that the program fi
les
> was pointed to the D drive (SAN).
>
> Thanks,
> LD|||LITO
Here is more info about my last reply
For a windows 2003
Start Registry Editor.
Locate and then click the following subkey:
HKEY_LOCAL_MACHINE\System\CurrentControl
Set\Services\MSSQLSERVER
On the Edit menu, point to New, and then click Multi-String Value.
Type DependOnService to name the new registry value, and then press ENTER.
Double-click DependOnService, type MSiSCSI in the Value data box, and then
click OK.
http://support.microsoft.com/Default.aspx?kbid=870964
--
Paul Zanbaka
"paulz" wrote:
[vbcol=seagreen]
> SQL Sever is trying to start before the os connects to the SAN
> What type of SAN are you using?
> --
> paulz
>
> "LITO" wrote:
>
Question, what would be the reason why it would not autostart when I reboote
d
the server? I noticed when they installed SQL server that the program file
s
was pointed to the D drive (SAN).
Thanks,
LDSQL Sever is trying to start before the os connects to the SAN
What type of SAN are you using?
--
paulz
"LITO" wrote:
> I have the SQL account services set to autostart via sql server properties
.
> Question, what would be the reason why it would not autostart when I reboo
ted
> the server? I noticed when they installed SQL server that the program fi
les
> was pointed to the D drive (SAN).
>
> Thanks,
> LD|||LITO
Here is more info about my last reply
For a windows 2003
Start Registry Editor.
Locate and then click the following subkey:
HKEY_LOCAL_MACHINE\System\CurrentControl
Set\Services\MSSQLSERVER
On the Edit menu, point to New, and then click Multi-String Value.
Type DependOnService to name the new registry value, and then press ENTER.
Double-click DependOnService, type MSiSCSI in the Value data box, and then
click OK.
http://support.microsoft.com/Default.aspx?kbid=870964
--
Paul Zanbaka
"paulz" wrote:
[vbcol=seagreen]
> SQL Sever is trying to start before the os connects to the SAN
> What type of SAN are you using?
> --
> paulz
>
> "LITO" wrote:
>
AutoStart Failed When Rebooted
I have the SQL account services set to autostart via sql server properties.
Question, what would be the reason why it would not autostart when I rebooted
the server? I noticed when they installed SQL server that the program files
was pointed to the D drive (SAN).
Thanks,
LD
SQL Sever is trying to start before the os connects to the SAN
What type of SAN are you using?
paulz
"LITO" wrote:
> I have the SQL account services set to autostart via sql server properties.
> Question, what would be the reason why it would not autostart when I rebooted
> the server? I noticed when they installed SQL server that the program files
> was pointed to the D drive (SAN).
>
> Thanks,
> LD
|||LITO
Here is more info about my last reply
For a windows 2003
Start Registry Editor.
Locate and then click the following subkey:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Servic es\MSSQLSERVER
On the Edit menu, point to New, and then click Multi-String Value.
Type DependOnService to name the new registry value, and then press ENTER.
Double-click DependOnService, type MSiSCSI in the Value data box, and then
click OK.
http://support.microsoft.com/Default.aspx?kbid=870964
Paul Zanbaka
"paulz" wrote:
[vbcol=seagreen]
> SQL Sever is trying to start before the os connects to the SAN
> What type of SAN are you using?
> --
> paulz
>
> "LITO" wrote:
Question, what would be the reason why it would not autostart when I rebooted
the server? I noticed when they installed SQL server that the program files
was pointed to the D drive (SAN).
Thanks,
LD
SQL Sever is trying to start before the os connects to the SAN
What type of SAN are you using?
paulz
"LITO" wrote:
> I have the SQL account services set to autostart via sql server properties.
> Question, what would be the reason why it would not autostart when I rebooted
> the server? I noticed when they installed SQL server that the program files
> was pointed to the D drive (SAN).
>
> Thanks,
> LD
|||LITO
Here is more info about my last reply
For a windows 2003
Start Registry Editor.
Locate and then click the following subkey:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Servic es\MSSQLSERVER
On the Edit menu, point to New, and then click Multi-String Value.
Type DependOnService to name the new registry value, and then press ENTER.
Double-click DependOnService, type MSiSCSI in the Value data box, and then
click OK.
http://support.microsoft.com/Default.aspx?kbid=870964
Paul Zanbaka
"paulz" wrote:
[vbcol=seagreen]
> SQL Sever is trying to start before the os connects to the SAN
> What type of SAN are you using?
> --
> paulz
>
> "LITO" wrote:
AutoStart Failed When Rebooted
I have the SQL account services set to autostart via sql server properties.
Question, what would be the reason why it would not autostart when I rebooted
the server? I noticed when they installed SQL server that the program files
was pointed to the D drive (SAN).
Thanks,
LDSQL Sever is trying to start before the os connects to the SAN
What type of SAN are you using?
--
paulz
"LITO" wrote:
> I have the SQL account services set to autostart via sql server properties.
> Question, what would be the reason why it would not autostart when I rebooted
> the server? I noticed when they installed SQL server that the program files
> was pointed to the D drive (SAN).
>
> Thanks,
> LD|||LITO
Here is more info about my last reply
For a windows 2003
Start Registry Editor.
Locate and then click the following subkey:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSQLSERVER
On the Edit menu, point to New, and then click Multi-String Value.
Type DependOnService to name the new registry value, and then press ENTER.
Double-click DependOnService, type MSiSCSI in the Value data box, and then
click OK.
http://support.microsoft.com/Default.aspx?kbid=870964
--
Paul Zanbaka
"paulz" wrote:
> SQL Sever is trying to start before the os connects to the SAN
> What type of SAN are you using?
> --
> paulz
>
> "LITO" wrote:
> > I have the SQL account services set to autostart via sql server properties.
> > Question, what would be the reason why it would not autostart when I rebooted
> > the server? I noticed when they installed SQL server that the program files
> > was pointed to the D drive (SAN).
> >
> >
> > Thanks,
> > LD
Question, what would be the reason why it would not autostart when I rebooted
the server? I noticed when they installed SQL server that the program files
was pointed to the D drive (SAN).
Thanks,
LDSQL Sever is trying to start before the os connects to the SAN
What type of SAN are you using?
--
paulz
"LITO" wrote:
> I have the SQL account services set to autostart via sql server properties.
> Question, what would be the reason why it would not autostart when I rebooted
> the server? I noticed when they installed SQL server that the program files
> was pointed to the D drive (SAN).
>
> Thanks,
> LD|||LITO
Here is more info about my last reply
For a windows 2003
Start Registry Editor.
Locate and then click the following subkey:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSQLSERVER
On the Edit menu, point to New, and then click Multi-String Value.
Type DependOnService to name the new registry value, and then press ENTER.
Double-click DependOnService, type MSiSCSI in the Value data box, and then
click OK.
http://support.microsoft.com/Default.aspx?kbid=870964
--
Paul Zanbaka
"paulz" wrote:
> SQL Sever is trying to start before the os connects to the SAN
> What type of SAN are you using?
> --
> paulz
>
> "LITO" wrote:
> > I have the SQL account services set to autostart via sql server properties.
> > Question, what would be the reason why it would not autostart when I rebooted
> > the server? I noticed when they installed SQL server that the program files
> > was pointed to the D drive (SAN).
> >
> >
> > Thanks,
> > LD
Subscribe to:
Posts (Atom)