Disk Queue Length counters not meaningful in SQL 2000?

Yeah that's what I said...what are they talking about. I am in the
process of doing some research on multiple files vs. multiple
filgroups. I have seen it discussed a bunch on these groups but I need
to perform some tests to back it up. During my research I came across
the following from the "SQL Server 2000 Operations Guide"
"Note on Disk Queue Length
This counter no longer yields meaningful results in interpreting disk
I/O problems with SQL Server 2000. This is because the SQL Server
engines dynamically manage disk I/O, which is why the Max Async I/O
setting no longer exists. A simplified explanation is that SQL Server
issues an order for disk I/O, but does not wait for that process to
complete before issuing another request. When the I/O request has
completed its work, the database engine is notified and processes the
results. SQL Server monitors the throughput on these requests, and will
manage the amount of I/O that the disks can effectively handle."
You can see it here
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx
can anyone make any sense of this.
"SQL Server monitors the throughput on these requests, and will manage
the amount of I/O that the disks can effectively handle."
ok what does this mean? it will mange the amount of IO...ok how.
Has anyone seen this, or has an explanation?
Thanks
BertDisk queue length isn't particularly interesting because SQL Server throws a
lot of IOs at the disk and then goes to do other work. For instance,
read-ahead is a good example: if the server has a reasonable idea that it
will need several pages from disk in order to complete a scan, it will issue
the IOs for those pages before it actually needs them, with the expectation
that when we actually do need the pages, the IOs will have completed and we
won't have to wait. Here's the executive summary, I guess: by design, SQL
Server loads up the disk subsystem with IO requests so that we don't have to
wait for the pages when we need them. That causes IO queue lengths to
increase since there are typically always outstanding IOs posted by the
server. In some applications this might indicate a bottleneck, but SQL
Server is smart about it and uses asynchronous IO to its benefit.
Perhaps the following article (just recently posted) might illuminate the
subject a bit:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx.
--
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
<bertcord@.gmail.com> wrote in message
news:1106861683.038716.139100@.c13g2000cwb.googlegroups.com...
> Yeah that's what I said...what are they talking about. I am in the
> process of doing some research on multiple files vs. multiple
> filgroups. I have seen it discussed a bunch on these groups but I need
> to perform some tests to back it up. During my research I came across
> the following from the "SQL Server 2000 Operations Guide"
> "Note on Disk Queue Length
> This counter no longer yields meaningful results in interpreting disk
> I/O problems with SQL Server 2000. This is because the SQL Server
> engines dynamically manage disk I/O, which is why the Max Async I/O
> setting no longer exists. A simplified explanation is that SQL Server
> issues an order for disk I/O, but does not wait for that process to
> complete before issuing another request. When the I/O request has
> completed its work, the database engine is notified and processes the
> results. SQL Server monitors the throughput on these requests, and will
> manage the amount of I/O that the disks can effectively handle."
> You can see it here
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx
> can anyone make any sense of this.
> "SQL Server monitors the throughput on these requests, and will manage
> the amount of I/O that the disks can effectively handle."
> ok what does this mean? it will mange the amount of IO...ok how.
> Has anyone seen this, or has an explanation?
> Thanks
> Bert
>|||This means SQL Server 2000 tries not to issue 200 IO per second if your IO
system can only handle 100 IO per second. So normally you would not see
average disk queue length to be more than 1. However sometimes disk queue
length can be high (more than 1) in some heavily loaded systems. If that
happens then you know you have an IO bottleneck. The % disk busy is also a
good indication and it should be used in conjunction with average disk queue
length.
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
<bertcord@.gmail.com> wrote in message
news:1106861683.038716.139100@.c13g2000cwb.googlegroups.com...
> Yeah that's what I said...what are they talking about. I am in the
> process of doing some research on multiple files vs. multiple
> filgroups. I have seen it discussed a bunch on these groups but I need
> to perform some tests to back it up. During my research I came across
> the following from the "SQL Server 2000 Operations Guide"
> "Note on Disk Queue Length
> This counter no longer yields meaningful results in interpreting disk
> I/O problems with SQL Server 2000. This is because the SQL Server
> engines dynamically manage disk I/O, which is why the Max Async I/O
> setting no longer exists. A simplified explanation is that SQL Server
> issues an order for disk I/O, but does not wait for that process to
> complete before issuing another request. When the I/O request has
> completed its work, the database engine is notified and processes the
> results. SQL Server monitors the throughput on these requests, and will
> manage the amount of I/O that the disks can effectively handle."
> You can see it here
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx
> can anyone make any sense of this.
> "SQL Server monitors the throughput on these requests, and will manage
> the amount of I/O that the disks can effectively handle."
> ok what does this mean? it will mange the amount of IO...ok how.
> Has anyone seen this, or has an explanation?
> Thanks
> Bert
>|||Thanks for responding. I read the article today and it was very good.
I am however interested in how SQL manages IO when working with
multiple files in a single filegroup.
For example how Flushing Pages to disk is affected when a database has
multiple files. My assumption is that it is the same except that the
process performing the write (lazy write, Checkpoint or Eager write)
would pass off the write requests to the thread that is working on the
specific file. Based on my test results writing to multiple files was
always a tiny bit slower if the disk subsystem was not under heavy
load.
I am aware that here is some confusion about how SQL server issue
threads for reads. Some people feel that having more files is the only
way SQL will spawn more threads. Andrew Kelly pointed out in the
following Google threads this is not correct for SQL2000.
http://linktrim.com/x2
http://linktrim.com/x3
For writes I confirmed that SQL does write to all files in parallel. I
used fn_virtualfilestats to test. I had a Query Analyzer windows open
and I held down the F5 key so it was constantly refreshing, I could see
that the NumberReads column for all the files where incrementing on
each refresh. I do admit that my method was not 100% scientific but it
was the only way I could think of to confirm this.
The specific things I want to test are.
1. Do multiple files on the same drive perform better than a single
file
2. Do multiple files spread on separate drives perform better than a
single file.
My test server configuration is as follows
Dell 6650, 4 Xeon 1595, 5.2 GB of Ram
Disk configuration as follows
ArrayGroup0 - Channel1 E: RAID5 - 3 Drives
ArrayGroup1 - Channel0 F: RAID5 - 7 Drives
ArrayGroup1 - Channel1 J: RAID5 - 7 Drives
ArrayGroup2 - Channel0 G: RAID10 - 12 Drives
Drives E,F, and J only have data files for the test databases. Drive G
contains the tempDB log files for the test databases and all BULK
INSERT files.
I created 3 separate databases all having a single fileGroup, the file
configuration is as follows.
1. BulkInOut1 - One data File on the E drive.
2. BulkInOut2 - Three datafiles, one each on E, F and J
3. BulkInOut3 - Three datafiles all on the E drive.
Test1 - Bulk inserting 11,000,000 into an empty table with no indexes.
I ran each test 5 times and here are the results averaged in seconds.
BulkInOut1 - 165 (One File)
BulkInOut2 - 173 (3 files sep drives)
BulkInOut3 - 172 (3 files same drive)
Raw Results
BulkInOut1 - 164,162,169,163,165
BulkInOut2 - 169,175,184,166,169
BulkInOut3 - 172,170,168,180,171
Test2 - Create indexes on table from test 1. Create Clustered and two
non-clustered.
BulkInOut1 - 127 (One File)
BulkInOut2 - 98 (3 files sep drives)
BulkInOut3 - 132 (3 files same drive)
Raw Results
BulkInOut1 - 126,129,126
BulkInOut2 - 101,98,96
BulkInOut3 - 135,132,130
Based on these two test I am make the following conclusions.
1. Multiple files on the same drive is always slower do not do it.
2. Having multiple files spread onto multiple drives can perform
better. What happened in my test cases is that test one hardly
stressed the disk-subsystem. Therefore the multiple disks performed
slower. This I assume is due to the overhead of context switching. It
test two IO demands increased and disk queuing occurred on BulkInOut3
(2 files same drive), this caused it to be the slowest.
Next week I am going to perform some more tests with a better simulated
load. I am going to write an app that opens multiple connections and
inserts and selects data.
Any comments on my results or methodology would be appreciated. IF any
one else has performance data on file IO test I would love to see them
Thanks for you time
Bert Corderman|||I have completed a few more tests and here are the results. As a recap
my drive config is as follows
ArrayGroup0 - Channel1 E: RAID5 - 3 Drives
ArrayGroup1 - Channel0 F: RAID5 - 7 Drives
ArrayGroup1 - Channel1 J: RAID5 - 7 Drives
ArrayGroup2 - Channel0 G: RAID10 - 12 Drives
Test DB's
1. BulkInOut1 - One data File on the E drive.
2. BulkInOut2 - Three data files, one each on E, F and J
3. BulkInOut3 - Three data files all on the E drive.
Creation of Clustered Indexes only
BulkInOut1 - 73 (One File)
BulkInOut2 - 48 (3 files sep drives)
BulkInOut3 - 78 (3 files same drive)
As another test I wanted to see the performance of creating a separate
Filegroup for the non-clustered indexes. I tested a few configuration.
For these tests I used the database with one data file on E
(BulkInOut1)
The first test was a baseline
1. Non-Clustered Indexes Created on Primary filegroup - 66
The following tests added the Index filegroup with different file
placement.
2. 1 File on J = 62
3. 2 Files on J = 62
4. 1 File on J, one on F = 60
5. 5 Files on J = 65
Again my tests show that having multiple files in the same FileGroup
does not perform any better than having only one file, and in some
cases will even perform slower.
Any suggestions or input would be appreciated
Thanks
Bert|||I don't know, Bert. You've seemed to have done the most methodical and
exhaustive test that I've seen reported to in this forum.
If you've seen better, or at least equal performance, using single file
filegroups, Occam's Razor would say to go that route... KISS principle,
right?
Also, did you try reformatting the drives using 64 KB clusters? We have
experience quite a bit of throughput increase using this cluster allocation.
If you can afford it, use RAID 10 for everything. However, you should be
able to get buy with RAID 5 for data files, RAID 10 for log files, and RAID
1 for the tempdb, which should be on a dedicated set of disks.
Also, having more active spindles per database file is more important than
files themselves. If you can keep each processor running I/O against each
spindle, then whenever I/O requests come in, the more efficient they will
be. Second, having as many channels per parallel request will also increase
your throughput.
Check out this link for some Case Studies on this topic. Granted, the cover
a broad range of installation issues, but all of them have disk and file
layout strategies. I found the EMC CLARiiON whitepaper extremely
interesting. They introduce a term called "Plaiding," which is a
combination of RAID striping at the hardware level and multiple SQL Server
file placements at the software level, similar to a plaid pattern. This way
you maximize spindle usage per request.
There is A LOT of information on this site. Take your time to check out
each and every one. I have and it has provided quite a bit of information
to consider.
http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp
See what you think.
Sincerely,
Anthony Thomas
"bert" <bertcord@.gmail.com> wrote in message
news:1107304742.349333.272980@.z14g2000cwz.googlegroups.com...
I have completed a few more tests and here are the results. As a recap
my drive config is as follows
ArrayGroup0 - Channel1 E: RAID5 - 3 Drives
ArrayGroup1 - Channel0 F: RAID5 - 7 Drives
ArrayGroup1 - Channel1 J: RAID5 - 7 Drives
ArrayGroup2 - Channel0 G: RAID10 - 12 Drives
Test DB's
1. BulkInOut1 - One data File on the E drive.
2. BulkInOut2 - Three data files, one each on E, F and J
3. BulkInOut3 - Three data files all on the E drive.
Creation of Clustered Indexes only
BulkInOut1 - 73 (One File)
BulkInOut2 - 48 (3 files sep drives)
BulkInOut3 - 78 (3 files same drive)
As another test I wanted to see the performance of creating a separate
Filegroup for the non-clustered indexes. I tested a few configuration.
For these tests I used the database with one data file on E
(BulkInOut1)
The first test was a baseline
1. Non-Clustered Indexes Created on Primary filegroup - 66
The following tests added the Index filegroup with different file
placement.
2. 1 File on J = 62
3. 2 Files on J = 62
4. 1 File on J, one on F = 60
5. 5 Files on J = 65
Again my tests show that having multiple files in the same FileGroup
does not perform any better than having only one file, and in some
cases will even perform slower.
Any suggestions or input would be appreciated
Thanks
Bert|||Thanks for the input Anthony...
Yes KISS is what I want to do...but again if the difference is that
much greater sometimes it just isn't simple any more.
>>Also, did you try reformatting the drives using 64 KB clusters?
No I have not tried this yet...this is actually the first I have heard
of this but will definitely look into it. They actually talk of using
64kb clusters in the Rosetta Genomics 10-Terabyte Human Genome Database
white paper. (From the other link you mentioned) I had actually
thought of this but I did not know it was possible to format a drive
like this. For example sitting on my XP machine at home when I go to
format a drive the only options I have are 512, 1024, 2048 and 4096
bytes... I will have to look at this some more.
>>Also, having more active spindles per database file is more important
than files themselves.
I agree 110%. I actually performed a bunch more tests than what I
posted here. Having multiple files spread to different arrays only
increased performance if there was enough IO to saturate the IO of one
drive\controller. IF the IO wasn't enough the performance was
actually slower. The KISS philosophy leads me to recommend only
creating multiple files per file group if the IO generated can saturate
a single drive. Before adding more files I would recommend first
placing heavily accessed tables onto a new file group onto a separate
drive. But again I would like to do more research in this area. For
example lets say you have a big database with only 2 tables (simple
example) and you have 2 drives. Would you a) create one file group
with a file on each drive or b) two file groups with a file on each
drive?
>>They introduce a term called "Plaiding," which is a combination of
RAID striping at the hardware level and multiple SQL Server file
placements at the >>software level, similar to a plaid pattern.
Hum this sound very interesting. Unfortunately the link from the
Microsoft website no longer works. I tried searching the EMC site for
the article by searching for the article title and for the term plaid
and Plaiding... no dice... you don't happen to have the article
saved anywhere do you? I most cases I would prefer to let the hardware
perform the striping...but I would love to read the article
Thanks
Bert|||For the 64 KB allocation units (disk clusters), as well as many other useful
operational tips, check out the Microsoft SQL Server 2000 Operations Manual.
Be aware, however, that once you go above 4 KB clusters, disk compression
and defragmentation are no longer available through the native Windows
tools.
As far as your question concerning the two big tables, the answer depends on
the usage, as always. If you were to join the two tables frequently, I
would place each table within a sperate, single file, filegroup, one placed
on each drive in order to get simultaneous read-ahead reads for the JOIN
operation. However, an option not provided, with only two drives, I'd want
to maximize spindle usage; so, I'd stripe the two dirves together, at the
hardware level, of course, and then one file, one filegroup against the
stripe set.
As far as the "plaiding" article is concerned, yes, I noticed EMC moved the
site. I have the .pdf file and will forward it to you when I get a chance.
When I "Googled" the net, I did come across an article between Dell and
Oracle that explained similar features.
Also, know that if you have DAS, it would make no sense to "plaid" because
you would have total control over the layout of the disks and could
manipulate partitions for logs and data files and control the striping;
however, in a SAN situation, you oftentimes do not have this luxary. In
this scenario, "plaiding" can not only potentially increase the spindle
usage, but also give back to the DBA some measure of I/O performance
control, especially given the DBA is in the prime position to determine the
"changing" environmental charactersitics of the DBMS that no Storage
Administrator could possibly invision.
Sincerely,
Anthony Thomas
"bert" <bertcord@.gmail.com> wrote in message
news:1109015102.338558.43420@.l41g2000cwc.googlegroups.com...
Thanks for the input Anthony...
Yes KISS is what I want to do...but again if the difference is that
much greater sometimes it just isn't simple any more.
>>Also, did you try reformatting the drives using 64 KB clusters?
No I have not tried this yet...this is actually the first I have heard
of this but will definitely look into it. They actually talk of using
64kb clusters in the Rosetta Genomics 10-Terabyte Human Genome Database
white paper. (From the other link you mentioned) I had actually
thought of this but I did not know it was possible to format a drive
like this. For example sitting on my XP machine at home when I go to
format a drive the only options I have are 512, 1024, 2048 and 4096
bytes... I will have to look at this some more.
>>Also, having more active spindles per database file is more important
than files themselves.
I agree 110%. I actually performed a bunch more tests than what I
posted here. Having multiple files spread to different arrays only
increased performance if there was enough IO to saturate the IO of one
drive\controller. IF the IO wasn't enough the performance was
actually slower. The KISS philosophy leads me to recommend only
creating multiple files per file group if the IO generated can saturate
a single drive. Before adding more files I would recommend first
placing heavily accessed tables onto a new file group onto a separate
drive. But again I would like to do more research in this area. For
example lets say you have a big database with only 2 tables (simple
example) and you have 2 drives. Would you a) create one file group
with a file on each drive or b) two file groups with a file on each
drive?
>>They introduce a term called "Plaiding," which is a combination of
RAID striping at the hardware level and multiple SQL Server file
placements at the >>software level, similar to a plaid pattern.
Hum this sound very interesting. Unfortunately the link from the
Microsoft website no longer works. I tried searching the EMC site for
the article by searching for the article title and for the term plaid
and Plaiding... no dice... you don't happen to have the article
saved anywhere do you? I most cases I would prefer to let the hardware
perform the striping...but I would love to read the article
Thanks
Bert|||I am still digging over all the white papers on the MS site...that is a
great link thanks
>>However, an option not provided, with only two drives, I'd want to
maximize spindle usage; so, I'd stripe the two >>drives together, at
the hardware level, of course, and then one file, one filegroup against
the stripe set.
I guess my question about the 1 tables and two drives was a bit too
simplistic. Maybe I can ask the question in a different way as I am
curios as to what your conclusions would be.
How do you maximize spindle usage when you have created arrays as large
as the controller can support? In my simple example above lets say the
two drives are actually two arrays configured as large as possible (15
drives in RAID5 or RAID10), would you still want one filegroup with the
2 tables on one array and a second filegroup with the NonClustered
Indexes on the second filegroup? At first glance this is what I would
think also but...lets say the NonClustered indexes are very large it
is possible that when inserting data, or when joining more IO is
generated on The NonClustedIndex array. You could theoretically end up
in a situation where the NONClustered array is becoming a bottleneck
but the TableArray is fine
Wouldn't it make more sense to just create one filegroup with a single
file on each array and place all tables and indexes on this single
filegroup. Let that hardware handle the striping?
It becomes even much more complicated when you add multiple tables to
the file groups.
The best reason I can think of for using multiple user defined
filegroups, is if you are creating the FileGroup for a single object,
and you know that the data is going to accessed sequentially.
>>I have the .pdf file and will forward it to you when I get a chance.
Thanks that would be great
>>Also, know that if you have DAS,...
I am not sure what you are referring to when you say DAS, what does it
stand for?
Bert|||I would keep it simple. Put your system and user databases' data files on
one RAID 5 or RAID 10 volume. Put you system and user databases' log files
on one RAID 10 volume. Put your TempDB data file on one RAID 1 volume and
the TempDB log file on another RAID 1 volume.
You could then go with, for all of user databases only, for the data file(s)
only, leave the system tables and objects in the PRIMARY FILEGROUP with one
file on one disk. Put the clustered indexes in one file in a SECONDARY or
TABLE FILEGROUP on one disk. Then, finally, put all the non-clustered
indexes in one file in a TIRTIARY or INDEX FILEGROUP.
Whenever you perform CRUD operations, ALL INDEXES WILL BE UPDATED, both
clustered and non-clustered indexes. Only the UPDATE operations could
possibly be directed at non-clustered index(es) only. If the clustered
index is augmented or modified, so will the non-clustered indexes. Also, if
you have a mix of large and small Read operations, large queries would force
scans, which would use the clustered index, small queries would be directed
at the non-clustered indexes or an index seek of the clustered index.
It only makes sense to use "plaiding," multiple files per filegroup, when
you have limited control over the SAN. Another potential use would be in a
consolidated environment where a single SS instance could be hosting
numerous application databases. We have one where we host nearly 100 vended
applicaiton databases. "Plaiding" helps us further reduce potential SAN
hotspots from the pseudo-random drive usage.
Also keep in mind, that for vended databases, you will have little control
over the use of FILEGROUPs and object placement on those file groups. The
vendors tend to through everything into a single file PRIMARY FILEGROUP and
you would have to modify their script to get the objects created elsewhere.
"Plaiding" isolates the file layouts from the database object definitions.
I also sent you that .pdf whitepaper by private communication.
Sincerely,
Anthony Thomas
"bert" <bertcord@.gmail.com> wrote in message
news:1109115612.962063.112300@.c13g2000cwb.googlegroups.com...
I am still digging over all the white papers on the MS site...that is a
great link thanks
>>However, an option not provided, with only two drives, I'd want to
maximize spindle usage; so, I'd stripe the two >>drives together, at
the hardware level, of course, and then one file, one filegroup against
the stripe set.
I guess my question about the 1 tables and two drives was a bit too
simplistic. Maybe I can ask the question in a different way as I am
curios as to what your conclusions would be.
How do you maximize spindle usage when you have created arrays as large
as the controller can support? In my simple example above lets say the
two drives are actually two arrays configured as large as possible (15
drives in RAID5 or RAID10), would you still want one filegroup with the
2 tables on one array and a second filegroup with the NonClustered
Indexes on the second filegroup? At first glance this is what I would
think also but...lets say the NonClustered indexes are very large it
is possible that when inserting data, or when joining more IO is
generated on The NonClustedIndex array. You could theoretically end up
in a situation where the NONClustered array is becoming a bottleneck
but the TableArray is fine
Wouldn't it make more sense to just create one filegroup with a single
file on each array and place all tables and indexes on this single
filegroup. Let that hardware handle the striping?
It becomes even much more complicated when you add multiple tables to
the file groups.
The best reason I can think of for using multiple user defined
filegroups, is if you are creating the FileGroup for a single object,
and you know that the data is going to accessed sequentially.
>>I have the .pdf file and will forward it to you when I get a chance.
Thanks that would be great
>>Also, know that if you have DAS,...
I am not sure what you are referring to when you say DAS, what does it
stand for?
Bertsql

Discovering SQL databases

Hi, I'm in the process of migrating / decommissioning a series of SQL
servers.
Is there / are there any tools to sniff out any existing eroneous SQL
databases which people may have created by themselves?
TIAThere are several ways to accomplish the task. Here are a couple ideas.
User SQLPing. Read about its usage here:
http://www.sqlteam.com/item.asp?ItemID=5403
A simple bat file idea:
http://www.databasejournal.com/feat...cle.php/3397981
And of course, any decent network scanning tool.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Stu" <lbwer@.blah.com> wrote in message
news:uzQExf$rGHA.2464@.TK2MSFTNGP03.phx.gbl...
> Hi, I'm in the process of migrating / decommissioning a series of SQL
> servers.
> Is there / are there any tools to sniff out any existing eroneous SQL
> databases which people may have created by themselves?
> TIA
>|||I suspect you mean sniff out instances of SQL Server, not databases.
I use SQL Recon: http://www.specialopssecurity.com/labs/sqlrecon/
Great utility to find any instance running on the network.
"Stu" <lbwer@.blah.com> wrote in message
news:uzQExf$rGHA.2464@.TK2MSFTNGP03.phx.gbl...
> Hi, I'm in the process of migrating / decommissioning a series of SQL
> servers.
> Is there / are there any tools to sniff out any existing eroneous SQL
> databases which people may have created by themselves?
> TIA
>sql

Disaster Recovery Process - Cannot restore Master

Hello All,

I have a production SQL 2005 sp1 server running on Windows 2003. The SQL server was upgraded from SQL 2000.

In SQL 2000 our disaster recovery plan consisted of making daily backups of all system databases and user databases.

If we did have a system failure, we would copy those databases to a different physical server, Open SQL in single user mode and restore master.We would then restart the SQL service in normal mode and continue restoring the system databases then the user databases.

Now with SQL 2005, I am testing this process by attempting to restore all of the databases into our rebuild DEV server.I have SQL 2005 installed and patched to the same level as the production SQL server (9.0.2047).I can start SQL 2005 in single user mode, open SQL Server Management Studio, browse to master, right click master, click tasks ->restore ->Database.I choose “From Device” and add the BAK file from last nights backups.I select Master as the restore to database, put a check in the backup set to restore and press ok.Below is the error that I am getting:

“Restore failed for Server ‘SERVERNAME’ – System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'master' database. (Microsoft.SqlServer.Smo)

Do I have a real error, or am I going about the process incorrectly for SQL 2005?

Here is the SQL Script that my actions are producing:

RESTORE DATABASE [master] FROMDISK = N'D:\ProductionSQLBackupforDEV\ \MSSQL\BACKUP\master\master_backup_200605110020.bak' WITHFILE = 1,NOUNLOAD,STATS = 10

GO

Also, all of our databases, system and user, are stored in a different directory than c:\program files\mssql……We have moved them to D:\directory.

If I need to re evaluate our “Backup” Procedures to be able to perform these actions please let me know.

Thank you for any assistance you can provide.

Keith

Does anyone have any ideas on this problem?

Can the master database from SQL2005 on server1 be restored to SQL2005 on server2?

If so, how do you do it?

If not, how do you recover from a disaster where you are forced to restore from backups?

Thank you,

Keith

|||

Did you try the WITH REPLACE option?

That should work.

|||

Thank you,

That was exactly what I was looking for.

Keith

Disaster Recovery Process - Cannot restore Master

Hello All,

I have a production SQL 2005 sp1 server running on Windows 2003. The SQL server was upgraded from SQL 2000.

In SQL 2000 our disaster recovery plan consisted of making daily backups of all system databases and user databases.

If we did have a system failure, we would copy those databases to a different physical server, Open SQL in single user mode and restore master.We would then restart the SQL service in normal mode and continue restoring the system databases then the user databases.

Now with SQL 2005, I am testing this process by attempting to restore all of the databases into our rebuild DEV server.I have SQL 2005 installed and patched to the same level as the production SQL server (9.0.2047).I can start SQL 2005 in single user mode, open SQL Server Management Studio, browse to master, right click master, click tasks ->restore ->Database.I choose “From Device” and add the BAK file from last nights backups.I select Master as the restore to database, put a check in the backup set to restore and press ok.Below is the error that I am getting:

“Restore failed for Server ‘SERVERNAME’ – System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'master' database. (Microsoft.SqlServer.Smo)

Do I have a real error, or am I going about the process incorrectly for SQL 2005?

Here is the SQL Script that my actions are producing:

RESTORE DATABASE [master] FROMDISK = N'D:\ProductionSQLBackupforDEV\ \MSSQL\BACKUP\master\master_backup_200605110020.bak' WITHFILE = 1,NOUNLOAD,STATS = 10

GO

Also, all of our databases, system and user, are stored in a different directory than c:\program files\mssql……We have moved them to D:\directory.

If I need to re evaluate our “Backup” Procedures to be able to perform these actions please let me know.

Thank you for any assistance you can provide.

Keith

Does anyone have any ideas on this problem?

Can the master database from SQL2005 on server1 be restored to SQL2005 on server2?

If so, how do you do it?

If not, how do you recover from a disaster where you are forced to restore from backups?

Thank you,

Keith

|||

Did you try the WITH REPLACE option?

That should work.

|||

Thank you,

That was exactly what I was looking for.

Keith

Disaster Recovery and Bulk Loads

We are in the process of migrating our database to SQL Server 2005 Standard
and I have a question regarding disaster recovery and bulk loads. We
periodically do bulk loads of data from files (csv's, flat files, etc). Our
current database product requires that we maintain a copy of the source file
used for these bulk data loads as part of its database recovery process. Th
e
question is - Does SQL Server require that we keep a copy of the source file
used for bulk data loads in order to recover a database using the log files?Hi Darren
I believe that's depend on how you set your recovery mode setting on your
SQL Server database (Full, Bulk-Logged, or Simple). If you set it up with
Full recovery, all the transaction done to SQL server get logged. And as lon
g
as the SQL Server transaction log get backed up regularly, you should be abl
e
to restore the database to point in time pretty easily, and you don't need t
o
have the source files for the bulk load. However, the downside of this would
be the log files for your SQL Server database would grow rapidly, especially
when you are performing the bulk load.
The Bulk Logged recovery has some limitation. Where it might not logged all
the bulk load transaction.
For more info on the recovery mode, you can check your SQL Server Book On
Line. I personally think that it would not hurt if you store your source
files of the bulk transaction.
Lucas
"Darren" wrote:
> We are in the process of migrating our database to SQL Server 2005 Standar
d
> and I have a question regarding disaster recovery and bulk loads. We
> periodically do bulk loads of data from files (csv's, flat files, etc). O
ur
> current database product requires that we maintain a copy of the source fi
le
> used for these bulk data loads as part of its database recovery process.
The
> question is - Does SQL Server require that we keep a copy of the source fi
le
> used for bulk data loads in order to recover a database using the log files?[/colo
r]

Disappearing data

We have been running an ETL process to load data into a SQL Server database for quite some time. Sometimes, for no logical reason, entire tables will get truncated. We took all ETL processes out of the scheduler and instead loaded the tables manually.
The tables are still getting truncated, even with no one on the box, which leads to the notion it was not the ETL process doing the truncating. We're having trouble looking at the logs to determine why these tables are getting truncated.
Has anyone experienced a spontaneous truncating/deletion of data within a subset of tables? Could this be a virus? We thought about attaching a trigger to one of the tables, writing some associated info into another table, to see if we could get a bette
r probe. Any ideas? This is driving us nuts!
DDR
Stange, have you run SQL Server Profiler during the loading to see what is
going on?
"DDR" <DDR@.discussions.microsoft.com> wrote in message
news:0D68E7D9-6781-4D8F-94CC-8AA191CE44EE@.microsoft.com...
> We have been running an ETL process to load data into a SQL Server
database for quite some time. Sometimes, for no logical reason, entire
tables will get truncated. We took all ETL processes out of the scheduler
and instead loaded the tables manually. The tables are still getting
truncated, even with no one on the box, which leads to the notion it was not
the ETL process doing the truncating. We're having trouble looking at the
logs to determine why these tables are getting truncated.
> Has anyone experienced a spontaneous truncating/deletion of data within a
subset of tables? Could this be a virus? We thought about attaching a
trigger to one of the tables, writing some associated info into another
table, to see if we could get a better probe. Any ideas? This is driving
us nuts!
|||Get a log reading tool, you should be able to track it down.
There are four such tools listed here: http://www.aspfaq.com/2449
(BTW, I've never experienced this; it is most likely some rogue process
you've forgotten about or that is misbehaving.)
http://www.aspfaq.com/
(Reverse address to reply.)
"DDR" <DDR@.discussions.microsoft.com> wrote in message
news:0D68E7D9-6781-4D8F-94CC-8AA191CE44EE@.microsoft.com...
> We have been running an ETL process to load data into a SQL Server
database for quite some time. Sometimes, for no logical reason, entire
tables will get truncated. We took all ETL processes out of the scheduler
and instead loaded the tables manually. The tables are still getting
truncated, even with no one on the box, which leads to the notion it was not
the ETL process doing the truncating. We're having trouble looking at the
logs to determine why these tables are getting truncated.
> Has anyone experienced a spontaneous truncating/deletion of data within a
subset of tables? Could this be a virus? We thought about attaching a
trigger to one of the tables, writing some associated info into another
table, to see if we could get a better probe. Any ideas? This is driving
us nuts!

Disappearing data

We have been running an ETL process to load data into a SQL Server database for quite some time. Sometimes, for no logical reason, entire tables will get truncated. We took all ETL processes out of the scheduler and instead loaded the tables manually. The tables are still getting truncated, even with no one on the box, which leads to the notion it was not the ETL process doing the truncating. We're having trouble looking at the logs to determine why these tables are getting truncated.
Has anyone experienced a spontaneous truncating/deletion of data within a subset of tables? Could this be a virus? We thought about attaching a trigger to one of the tables, writing some associated info into another table, to see if we could get a better probe. Any ideas? This is driving us nuts!DDR
Stange, have you run SQL Server Profiler during the loading to see what is
going on?
"DDR" <DDR@.discussions.microsoft.com> wrote in message
news:0D68E7D9-6781-4D8F-94CC-8AA191CE44EE@.microsoft.com...
> We have been running an ETL process to load data into a SQL Server
database for quite some time. Sometimes, for no logical reason, entire
tables will get truncated. We took all ETL processes out of the scheduler
and instead loaded the tables manually. The tables are still getting
truncated, even with no one on the box, which leads to the notion it was not
the ETL process doing the truncating. We're having trouble looking at the
logs to determine why these tables are getting truncated.
> Has anyone experienced a spontaneous truncating/deletion of data within a
subset of tables? Could this be a virus? We thought about attaching a
trigger to one of the tables, writing some associated info into another
table, to see if we could get a better probe. Any ideas? This is driving
us nuts!|||Get a log reading tool, you should be able to track it down.
There are four such tools listed here: http://www.aspfaq.com/2449
(BTW, I've never experienced this; it is most likely some rogue process
you've forgotten about or that is misbehaving.)
--
http://www.aspfaq.com/
(Reverse address to reply.)
"DDR" <DDR@.discussions.microsoft.com> wrote in message
news:0D68E7D9-6781-4D8F-94CC-8AA191CE44EE@.microsoft.com...
> We have been running an ETL process to load data into a SQL Server
database for quite some time. Sometimes, for no logical reason, entire
tables will get truncated. We took all ETL processes out of the scheduler
and instead loaded the tables manually. The tables are still getting
truncated, even with no one on the box, which leads to the notion it was not
the ETL process doing the truncating. We're having trouble looking at the
logs to determine why these tables are getting truncated.
> Has anyone experienced a spontaneous truncating/deletion of data within a
subset of tables? Could this be a virus? We thought about attaching a
trigger to one of the tables, writing some associated info into another
table, to see if we could get a better probe. Any ideas? This is driving
us nuts!

Disappearing data

We have been running an ETL process to load data into a SQL Server database
for quite some time. Sometimes, for no logical reason, entire tables will g
et truncated. We took all ETL processes out of the scheduler and instead lo
aded the tables manually.
The tables are still getting truncated, even with no one on the box, which l
eads to the notion it was not the ETL process doing the truncating. We're h
aving trouble looking at the logs to determine why these tables are getting
truncated.
Has anyone experienced a spontaneous truncating/deletion of data within a su
bset of tables? Could this be a virus? We thought about attaching a trigge
r to one of the tables, writing some associated info into another table, to
see if we could get a bette
r probe. Any ideas? This is driving us nuts!DDR
Stange, have you run SQL Server Profiler during the loading to see what is
going on?
"DDR" <DDR@.discussions.microsoft.com> wrote in message
news:0D68E7D9-6781-4D8F-94CC-8AA191CE44EE@.microsoft.com...
> We have been running an ETL process to load data into a SQL Server
database for quite some time. Sometimes, for no logical reason, entire
tables will get truncated. We took all ETL processes out of the scheduler
and instead loaded the tables manually. The tables are still getting
truncated, even with no one on the box, which leads to the notion it was not
the ETL process doing the truncating. We're having trouble looking at the
logs to determine why these tables are getting truncated.
> Has anyone experienced a spontaneous truncating/deletion of data within a
subset of tables? Could this be a virus? We thought about attaching a
trigger to one of the tables, writing some associated info into another
table, to see if we could get a better probe. Any ideas? This is driving
us nuts!|||Get a log reading tool, you should be able to track it down.
There are four such tools listed here: http://www.aspfaq.com/2449
(BTW, I've never experienced this; it is most likely some rogue process
you've forgotten about or that is misbehaving.)
http://www.aspfaq.com/
(Reverse address to reply.)
"DDR" <DDR@.discussions.microsoft.com> wrote in message
news:0D68E7D9-6781-4D8F-94CC-8AA191CE44EE@.microsoft.com...
> We have been running an ETL process to load data into a SQL Server
database for quite some time. Sometimes, for no logical reason, entire
tables will get truncated. We took all ETL processes out of the scheduler
and instead loaded the tables manually. The tables are still getting
truncated, even with no one on the box, which leads to the notion it was not
the ETL process doing the truncating. We're having trouble looking at the
logs to determine why these tables are getting truncated.
> Has anyone experienced a spontaneous truncating/deletion of data within a
subset of tables? Could this be a virus? We thought about attaching a
trigger to one of the tables, writing some associated info into another
table, to see if we could get a better probe. Any ideas? This is driving
us nuts!

Disabling Named Pipes

In the process of doing some routine monitoring/clean-up we've
discovered that several (many?) users are apparently set to access our
SQL Server 2000 database instances via the Named Pipes protocol. In
readings and recommendations we've decided that our WAN would be best
served if we use the less "chatty" TCP/IP.

As such we've also decided to try to enforce this decision to use
TCP/IP exclusively using the domain login script used by all of our
end-users.

Question: does anyone know what registry entries are created/used to
indicate that TCP/IP is enabled and is the default protocol for SQL
Server 2000? Our environment is: XP Pro SP2 and SQL Server 2000
(typically SP3).

TIA
Glenn - newbie DBA(glenn.owens@.gmail.com) writes:
> In the process of doing some routine monitoring/clean-up we've
> discovered that several (many?) users are apparently set to access our
> SQL Server 2000 database instances via the Named Pipes protocol. In
> readings and recommendations we've decided that our WAN would be best
> served if we use the less "chatty" TCP/IP.
> As such we've also decided to try to enforce this decision to use
> TCP/IP exclusively using the domain login script used by all of our
> end-users.
> Question: does anyone know what registry entries are created/used to
> indicate that TCP/IP is enabled and is the default protocol for SQL
> Server 2000? Our environment is: XP Pro SP2 and SQL Server 2000
> (typically SP3).

The brutal variant would be to open the Server Network Utility on
the server box, and disable Named Pipes (server restart needed, as I
recall.)

On the clients it could be a good thing to use the Client Network
Utility to disable Named Pipes, or put it lower in priority than
TCP/IP.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland, I appreciate the input but we're not looking at wiping out the
use of named pipes all-together (there may be background processes and
legacy applications which use/require the protocol). We're hoping only
to disable Named Pipes for End-Users who login via the default domain.
So, the question still stands...

Question: does anyone know what registry entries are created/used to
indicate that TCP/IP is enabled and is the default protocol for SQL
Server 2000? Our environment is: XP Pro SP2 and SQL Server 2000
(typically SP3).

Erland Sommarskog wrote:
> (glenn.owens@.gmail.com) writes:
> > In the process of doing some routine monitoring/clean-up we've
> > discovered that several (many?) users are apparently set to access
our
> > SQL Server 2000 database instances via the Named Pipes protocol. In
> > readings and recommendations we've decided that our WAN would be
best
> > served if we use the less "chatty" TCP/IP.
> > As such we've also decided to try to enforce this decision to use
> > TCP/IP exclusively using the domain login script used by all of our
> > end-users.
> > Question: does anyone know what registry entries are created/used
to
> > indicate that TCP/IP is enabled and is the default protocol for SQL
> > Server 2000? Our environment is: XP Pro SP2 and SQL Server 2000
> > (typically SP3).
> The brutal variant would be to open the Server Network Utility on
> the server box, and disable Named Pipes (server restart needed, as I
> recall.)
> On the clients it could be a good thing to use the Client Network
> Utility to disable Named Pipes, or put it lower in priority than
> TCP/IP.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||(gowens@.nixonpeabody.com) writes:
> Erland, I appreciate the input but we're not looking at wiping out the
> use of named pipes all-together (there may be background processes and
> legacy applications which use/require the protocol). We're hoping only
> to disable Named Pipes for End-Users who login via the default domain.
> So, the question still stands...
> Question: does anyone know what registry entries are created/used to
> indicate that TCP/IP is enabled and is the default protocol for SQL
> Server 2000? Our environment is: XP Pro SP2 and SQL Server 2000
> (typically SP3).

I'm still not sure what you are asking for. Your question make it
seem like you want to do this on the server level, but this appears
to be impossible, since you want to keep Named Pipes for some clients.

Thus, your only option is to change this at the clients. The supported
way to do this, is to use the Client Network Utility.

If you want to hack the registry yourself, it appeats that the relevant
values and keys are under
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Client\SuperSocketNetLib
The value Protocol Order appears to be the priority order, and should
thus have "tcp" in the beginning. Under the subkey Tcp, there should
be a REG_DWORD value DefaulPort with the value of 0x00000599 (1433.)

I recommend you to play around from the Client Network Utility a little
more, before you start any remote registry hacking or whatever you are
planning to do.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland, what I'm suggesting is that we do NOT do this at the server
level but rather on a per user basis using our standard login script.

We have several hundred end-users in geographically different
locations. Virtually none of the users have access to, or have
knowledge of, the SQL Client Utility. So, the idea is that when they
logon to their PCs a scriptlet will be run which will disable Named
Pipes and/or insure that TCP/IP is the default protocol.

I was hoping that someone could suggest/provide such a script or
specify the WIN XP registry key/values that define the protocol, etc.

I hope that this clears thing up.

Thanks

Erland Sommarskog wrote:
> (gowens@.nixonpeabody.com) writes:
> > Erland, I appreciate the input but we're not looking at wiping out
the
> > use of named pipes all-together (there may be background processes
and
> > legacy applications which use/require the protocol). We're hoping
only
> > to disable Named Pipes for End-Users who login via the default
domain.
> > So, the question still stands...
> > Question: does anyone know what registry entries are created/used
to
> > indicate that TCP/IP is enabled and is the default protocol for SQL
> > Server 2000? Our environment is: XP Pro SP2 and SQL Server 2000
> > (typically SP3).
> I'm still not sure what you are asking for. Your question make it
> seem like you want to do this on the server level, but this appears
> to be impossible, since you want to keep Named Pipes for some
clients.
> Thus, your only option is to change this at the clients. The
supported
> way to do this, is to use the Client Network Utility.
> If you want to hack the registry yourself, it appeats that the
relevant
> values and keys are under
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Client\SuperSocketNetLib
> The value Protocol Order appears to be the priority order, and should
> thus have "tcp" in the beginning. Under the subkey Tcp, there should
> be a REG_DWORD value DefaulPort with the value of 0x00000599 (1433.)
> I recommend you to play around from the Client Network Utility a
little
> more, before you start any remote registry hacking or whatever you
are
> planning to do.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||(gowens@.nixonpeabody.com) writes:
> I was hoping that someone could suggest/provide such a script or
> specify the WIN XP registry key/values that define the protocol, etc.

My post contained some hints in this direction, and my hope was that
you could work from this suggestion. If you want something ready of
the shelf, I'm afraid that I don't have any thing in store.

A quick search lead me to:
http://msdn.microsoft.com/library/d...dbnetlib_22.asp

There you find some more information about the registrty entries.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Disabling automatic kill of a deadlocked process

Is there a way in sql server of disabling the feature,where a process is
automatically chosen as a victim, when a deadlock occurs?
I would like to have the deadlock left untouched so that it can be manually
investigated and resolved.
No; leaving it untouched would mean leaving the condition in place and
keeping locks on those objects until someone can come clean it up by
manually killing them. Not a good idea, IMO. This could mean that entire
tables will remain locked until you come fix it.
"RyGuy Van" <RyGuy Van@.discussions.microsoft.com> wrote in message
news:0980BFE6-A1F5-43B5-A09C-5AE7364A0B3D@.microsoft.com...
> Is there a way in sql server of disabling the feature,where a process is
> automatically chosen as a victim, when a deadlock occurs?
> I would like to have the deadlock left untouched so that it can be
manually
> investigated and resolved.
|||The reason I am asking is that for some reason the automatic kill is never
occuring on the customer site and their dba doesn't know how this was
disabled.
If I could figure how this is disabled, I could then hopefully get it
reenabled over there.
"Adam Machanic" wrote:

> No; leaving it untouched would mean leaving the condition in place and
> keeping locks on those objects until someone can come clean it up by
> manually killing them. Not a good idea, IMO. This could mean that entire
> tables will remain locked until you come fix it.
>
> "RyGuy Van" <RyGuy Van@.discussions.microsoft.com> wrote in message
> news:0980BFE6-A1F5-43B5-A09C-5AE7364A0B3D@.microsoft.com...
> manually
>
>
|||> The reason I am asking is that for some reason the automatic kill is never
> occuring
Are you 100% certain is really was a *deadlock* and not just ordinary blocking?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RyGuyVan" <RyGuyVan@.discussions.microsoft.com> wrote in message
news:6CBF3ECC-13BE-4EE1-95F4-2AC478F32461@.microsoft.com...[vbcol=seagreen]
> The reason I am asking is that for some reason the automatic kill is never
> occuring on the customer site and their dba doesn't know how this was
> disabled.
> If I could figure how this is disabled, I could then hopefully get it
> reenabled over there.
> "Adam Machanic" wrote:
|||The latest "deadlock" which came from the customer indicates that it is in
fact blocking.
Change of subject, but do you know if sql server socket based drivers can
hang on reading/writing of data? The one in question is Microsoft's latest
jdbc driver.
I would rather not add in a timeout into the transaction, but that might be
the next option.
"HttpProcessor[6300][26]" daemon prio=5 tid=0x3537cf28 nid=0xcf4 runnable
[4482e000..4482fd90]
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(SocketInputStream. java:129)
at com.microsoft.util.UtilSocketDataProvider.getArray OfBytes(Unknown
Source)
at
com.microsoft.util.UtilBufferedDataProvider.cacheN extBlock(Unknown Source)
at
com.microsoft.util.UtilBufferedDataProvider.getArr ayOfBytes(Unknown Source)
at
com.microsoft.jdbc.sqlserver.SQLServerDepacketizin gDataProvider.signalStartOfPacket(Unknown Source)
"Tibor Karaszi" wrote:

> Are you 100% certain is really was a *deadlock* and not just ordinary blocking?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "RyGuyVan" <RyGuyVan@.discussions.microsoft.com> wrote in message
> news:6CBF3ECC-13BE-4EE1-95F4-2AC478F32461@.microsoft.com...
>
>
|||> Change of subject, but do you know if sql server socket based drivers can
> hang on reading/writing of data?
Sorry, but I don't know that. I do know that badly written applications can cause the client to stop
processing data in the clients input buffer. It is important to in the application process the rows
returned from a SELECT statement as fast as possible.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RyGuyVan" <RyGuyVan@.discussions.microsoft.com> wrote in message
news:5A311425-3E52-47F0-AB05-67E338DEBB8D@.microsoft.com...
> The latest "deadlock" which came from the customer indicates that it is in
> fact blocking.
> Change of subject, but do you know if sql server socket based drivers can
> hang on reading/writing of data? The one in question is Microsoft's latest
> jdbc driver.
> I would rather not add in a timeout into the transaction, but that might be
> the next option.
> "HttpProcessor[6300][26]" daemon prio=5 tid=0x3537cf28 nid=0xcf4 runnable
> [4482e000..4482fd90]
> at java.net.SocketInputStream.socketRead0(Native Method)
> at java.net.SocketInputStream.read(SocketInputStream. java:129)
> at com.microsoft.util.UtilSocketDataProvider.getArray OfBytes(Unknown
> Source)
> at
> com.microsoft.util.UtilBufferedDataProvider.cacheN extBlock(Unknown Source)
> at
> com.microsoft.util.UtilBufferedDataProvider.getArr ayOfBytes(Unknown Source)
> at
> com.microsoft.jdbc.sqlserver.SQLServerDepacketizin gDataProvider.signalStartOfPacket(Unknown
Source)[vbcol=seagreen]
> "Tibor Karaszi" wrote:
|||The "deadlock" or spid process lock is happening at my
office everyday ... what can i do ? any patch ?

>--Original Message--
>The latest "deadlock" which came from the customer
indicates that it is in
>fact blocking.
>Change of subject, but do you know if sql server socket
based drivers can
>hang on reading/writing of data? The one in question is
Microsoft's latest
>jdbc driver.
>I would rather not add in a timeout into the
transaction, but that might be
>the next option.
>"HttpProcessor[6300][26]" daemon prio=5 tid=0x3537cf28
nid=0xcf4 runnable
>[4482e000..4482fd90]
> at java.net.SocketInputStream.socketRead0(Native
Method)
> at java.net.SocketInputStream.read
(SocketInputStream.java:129)
> at
com.microsoft.util.UtilSocketDataProvider.getArray OfBytes
(Unknown
>Source)
> at
>com.microsoft.util.UtilBufferedDataProvider.cache NextBloc
k(Unknown Source)
> at
>com.microsoft.util.UtilBufferedDataProvider.getAr rayOfByt
es(Unknown Source)
> at
>com.microsoft.jdbc.sqlserver.SQLServerDepacketizi ngDataPr
ovider.signalStartOfPacket(Unknown Source)[vbcol=seagreen]
>"Tibor Karaszi" wrote:
automatic kill is never[vbcol=seagreen]
not just ordinary blocking?[vbcol=seagreen]
in message[vbcol=seagreen]
2AC478F32461@.microsoft.com...[vbcol=seagreen]
automatic kill is never[vbcol=seagreen]
know how this was[vbcol=seagreen]
hopefully get it[vbcol=seagreen]
condition in place and[vbcol=seagreen]
come clean it up by[vbcol=seagreen]
could mean that entire[vbcol=seagreen]
wrote in message[vbcol=seagreen]
5AE7364A0B3D@.microsoft.com...[vbcol=seagreen]
feature,where a process is[vbcol=seagreen]
occurs?[vbcol=seagreen]
so that it can be
>.
>

Disable Job Still Executing

I have data archive process that disables the backup job then enables the
job when the process has completed. One thing the job still executes as
schedule. I tried disabling the schedule as well as the job which did not
help.
I'm running SQL Server 2000 SP 4 on Windows Server 2003 SP 1.
Thanks in advance
BruceHi Bruce
You should be ok with just disabling the schedule with a command such as
EXEC msdb..sp_update_jobschedule @.job_name = 'My Backup Job',
@.name ='Nighly
Backup @. 22:00',
@.enabled = 0
Make sure that the job history shows that the jobs were run and that when
you display the steps, the step to disable the job was run. You may also wan
t
to log this to a file.
John
"Bruce" wrote:

> I have data archive process that disables the backup job then enables the
> job when the process has completed. One thing the job still executes as
> schedule. I tried disabling the schedule as well as the job which did not
> help.
> I'm running SQL Server 2000 SP 4 on Windows Server 2003 SP 1.
> Thanks in advance
> Bruce
>
>

Disable Job Still Executing

I have data archive process that disables the backup job then enables the
job when the process has completed. One thing the job still executes as
schedule. I tried disabling the schedule as well as the job which did not
help.
I'm running SQL Server 2000 SP 4 on Windows Server 2003 SP 1.
Thanks in advance
BruceHi Bruce
You should be ok with just disabling the schedule with a command such as
EXEC msdb..sp_update_jobschedule @.job_name = 'My Backup Job',
@.name ='Nighly
Backup @. 22:00',
@.enabled = 0
Make sure that the job history shows that the jobs were run and that when
you display the steps, the step to disable the job was run. You may also want
to log this to a file.
John
"Bruce" wrote:
> I have data archive process that disables the backup job then enables the
> job when the process has completed. One thing the job still executes as
> schedule. I tried disabling the schedule as well as the job which did not
> help.
> I'm running SQL Server 2000 SP 4 on Windows Server 2003 SP 1.
> Thanks in advance
> Bruce
>
>