Calling Webservice: disk

Showing posts with label disk. Show all posts

Tuesday, March 27, 2012

Can I force a checkpoint.

Our application is having problems due to what appears to be a flush to disk
during a checkpoint. The flush lasts 2-6 seconds and then every thing
continues on as before with no delay.
I tried creating a job to run every two minutes (just during our stress test).
All the job does is call "CHECKPOINT". Looking at the logs during my stress
tests, there is no difference in the behavior.
So, how can I get the DB to do checkpoints more often to prevent such a
large lump write ?
Or, am I totally off in the wrong direction on this ?
Any thoughts would be greatly appreciated.
Mark.
Notes: all databases are running in simple mode.I am looking at checkpoint pages/sec and pages writes/sec along with
physical read and write.
Also, I am monitoring the log %used on all DB's. I can see that when one of
my DB's hits
70% it appears to be related to this dump which seems to go along with the
docs for
checkpoint and db set to simple.
I am 100% sure it is not related to a query. It might not have anything to
do with a checkpoint,
but when the log file grows to 70% and gets reset to 20% I can see this huge
write and
I get a major backup. I see a similar relation on another DB.
Should I just shrink the log file, will this help decrease the number of
pages to write?
It appears to be true on a different DB with a smaller log file the writes
following
the truncation of the log is very small in comparison, does that make sense?
Thanks,
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:Oz3lJpYUDHA.484@.TK2MSFTNGP09.phx.gbl...
> how do you know for sure it's related to checkpoint? What counters are you
> basing that on? CHECKPOINT will run the checkpoint process so I'm not
really
> sure what you'e asking... you can run it as frequently as you want...
> also... I might take a look at SQL Profiler to see if there is some other
> query that is perhaps related to the IO... it might not be checkpoint
> related...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Mark DeBusschere" <developer@.line-4.com> wrote in message
> news:uTEjGgXUDHA.1552@.TK2MSFTNGP10.phx.gbl...
> > Our application is having problems due to what appears to be a flush to
> disk
> > during a checkpoint. The flush lasts 2-6 seconds and then every thing
> > continues on as before with no delay.
> >
> > I tried creating a job to run every two minutes (just during our stress
> test).
> > All the job does is call "CHECKPOINT". Looking at the logs during my
> stress
> > tests, there is no difference in the behavior.
> >
> > So, how can I get the DB to do checkpoints more often to prevent such a
> > large lump write ?
> >
> > Or, am I totally off in the wrong direction on this ?
> >
> > Any thoughts would be greatly appreciated.
> >
> > Mark.
> >
> > Notes: all databases are running in simple mode.
> >
> >
>|||Hi Mark,
My name is Michael and I would like to thank you for using Microsoft VAP
newsgroup.
I will update you later. In the mean time, if you have any other useful
information, please feel free to let me know.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Monday, March 19, 2012

can i add more files to the filegroup?

hi all,
2 CPU , 1G memory , the storage device is Disk Array
(RAID5)
The PRIMARY filegroup contains one datafile , i want to
add more files to the PRIMARY and rebuild the index in
another filegroup FGINDX (contains more files) to get
better performance
Can i need to modify the database to get better
management and better performance '
Thanks in advance.Rainbow
You can have more than one datafile in the same filegroup.
What you can not do is have one data file in more than one
filegroup.
Be aware you can only place non-clustered indexes in a
seperate filegroup.
Hope this helps
John|||i wanna better performance , expand the data to more
file '
>--Original Message--
>Rainbow
>You can have more than one datafile in the same
filegroup.
>What you can not do is have one data file in more than
one
>filegroup.
>Be aware you can only place non-clustered indexes in a
>seperate filegroup.
>Hope this helps
>John
>.
>|||Yes you may add more data files to a filegroup.
If you wish existing data to be spread across the new files, you must re-add
the data ( perhaps dropping/re-creating the clust index).
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"rainbow" <genghongjie@.163.com> wrote in message
news:061701c38e03$4c9a42d0$a401280a@.phx.gbl...
> i wanna better performance , expand the data to more
> file '
> >--Original Message--
> >Rainbow
> >
> >You can have more than one datafile in the same
> filegroup.
> >What you can not do is have one data file in more than
> one
> >filegroup.
> >
> >Be aware you can only place non-clustered indexes in a
> >seperate filegroup.
> >
> >Hope this helps
> >
> >John
> >.
> >

Wednesday, March 7, 2012

Can BULK INSERT be used like TEXTCOPY?

(SQL Server 2000, SP3a)
Hello all!
Is there any way to use BULK INSERT like TEXTCOPY? I have a series of files (on disk),
and I'd like to squirt them in to a table that has an IMAGE column. I could "shell" out
to use TEXTCOPY, but was wondering if I could leverage some built-in SQL construct. BULK
INSERT looked close, but it also looks like the built-in analogue to BCP. That is, it
processes the contents of the file, and I really just want to squirt the file in to the
column verbatim.
Thanks for any help you can provide!
John PetersonYes. You can use bulk insert but you need a format file.
e.g.
1 SQLIMAGE 0 999999 "" 2 coln ""
1= entire file
SQLIMAGE= datatype
0= prefix length
999999= file length/size
""= no terminator
2= column ordinal
coln= column name
""= no collation
Thus, the bulk insert looks like this:
bulk insert tb
from 'file.img'
with(formatfile='fmt.fmt')
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> Is there any way to use BULK INSERT like TEXTCOPY? I have a series of
files (on disk),
> and I'd like to squirt them in to a table that has an IMAGE column. I
could "shell" out
> to use TEXTCOPY, but was wondering if I could leverage some built-in SQL
construct. BULK
> INSERT looked close, but it also looks like the built-in analogue to BCP.
That is, it
> processes the contents of the file, and I really just want to squirt the
file in to the
> column verbatim.
> Thanks for any help you can provide!
> John Peterson
>|||John,
See this thread for an example:
http://groups.google.com/groups?q=405F-B2C5-7256A4B9870A
Steve Kass
Drew University
John Peterson wrote:
>(SQL Server 2000, SP3a)
>Hello all!
>Is there any way to use BULK INSERT like TEXTCOPY? I have a series of files (on disk),
>and I'd like to squirt them in to a table that has an IMAGE column. I could "shell" out
>to use TEXTCOPY, but was wondering if I could leverage some built-in SQL construct. BULK
>INSERT looked close, but it also looks like the built-in analogue to BCP. That is, it
>processes the contents of the file, and I really just want to squirt the file in to the
>column verbatim.
>Thanks for any help you can provide!
>John Peterson
>
>|||Thanks oj and Steve! I think that'll do the trick, even if it's a little awkward. :-)
BTW: Do you need an *exact* binary file size in the format file, as Steve's link
suggests? Or will "oversizing" it suffice?
Thanks again!
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> Is there any way to use BULK INSERT like TEXTCOPY? I have a series of files (on disk),
> and I'd like to squirt them in to a table that has an IMAGE column. I could "shell" out
> to use TEXTCOPY, but was wondering if I could leverage some built-in SQL construct.
> BULK INSERT looked close, but it also looks like the built-in analogue to BCP. That is,
> it processes the contents of the file, and I really just want to squirt the file in to
> the column verbatim.
> Thanks for any help you can provide!
> John Peterson
>|||John,
It's got to be exact. If you oversize it, you get an unexpected
end-of-file:
Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give
any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows
returned 0x80004005: The provider did not give any information about
the error.].
The statement has been terminated.
SK
John Peterson wrote:
>Thanks oj and Steve! I think that'll do the trick, even if it's a little awkward. :-)
>BTW: Do you need an *exact* binary file size in the format file, as Steve's link
>suggests? Or will "oversizing" it suffice?
>Thanks again!
>
>"John Peterson" <j0hnp@.comcast.net> wrote in message
>news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
>
>>(SQL Server 2000, SP3a)
>>Hello all!
>>Is there any way to use BULK INSERT like TEXTCOPY? I have a series of files (on disk),
>>and I'd like to squirt them in to a table that has an IMAGE column. I could "shell" out
>>to use TEXTCOPY, but was wondering if I could leverage some built-in SQL construct.
>>BULK INSERT looked close, but it also looks like the built-in analogue to BCP. That is,
>>it processes the contents of the file, and I really just want to squirt the file in to
>>the column verbatim.
>>Thanks for any help you can provide!
>>John Peterson
>>
>>
>
>|||Thanks, Steve!
One other option I'm exploring is using a stored procedure with an IMAGE parameter. But,
I have very little experiencing in using VBScript (this is going to be called from a Web
page) and ADO Parameter objects for an IMAGE data. I've been searching on the Web, and
I've seen a few snippets -- but not a comprehensive example. Do you have any
recommendations/links on that route, perchance?
"Steve Kass" <skass@.drew.edu> wrote in message
news:OfadH8WvEHA.2616@.TK2MSFTNGP10.phx.gbl...
> John,
> It's got to be exact. If you oversize it, you get an unexpected end-of-file:
> Server: Msg 4832, Level 16, State 1, Line 1
> Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give any information
> about the error.
> OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005:
> The provider did not give any information about the error.].
> The statement has been terminated.
> SK
> John Peterson wrote:
>>Thanks oj and Steve! I think that'll do the trick, even if it's a little awkward. :-)
>>BTW: Do you need an *exact* binary file size in the format file, as Steve's link
>>suggests? Or will "oversizing" it suffice?
>>Thanks again!
>>
>>"John Peterson" <j0hnp@.comcast.net> wrote in message
>>news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
>>(SQL Server 2000, SP3a)
>>Hello all!
>>Is there any way to use BULK INSERT like TEXTCOPY? I have a series of files (on disk),
>>and I'd like to squirt them in to a table that has an IMAGE column. I could "shell"
>>out to use TEXTCOPY, but was wondering if I could leverage some built-in SQL construct.
>>BULK INSERT looked close, but it also looks like the built-in analogue to BCP. That
>>is, it processes the contents of the file, and I really just want to squirt the file in
>>to the column verbatim.
>>Thanks for any help you can provide!
>>John Peterson
>>
>>
>>|||John,
I haven't done this myself, but you should be able to pass the image
data with a adLongVarBinary parameter.
You might need to create the parameter 1 byte longer than the length of
the data you're storing, according to
http://support.microsoft.com/default.aspx?scid=kb;en-us;190450
I also saw one suggestion that this meant you have to read back all but
the last byte of the stored image
value when retrieving the data, so you should be watchful:
http://groups.google.com/groups?hl=en&lr=&safe=off&threadm=uMwa0SoD%24GA.235%40cppssbbsa02.microsoft.com&rnum=6&prev=/groups%3Fhl%3Den%26lr%3D%26safe%3Doff%26q%3Dsqlserver%2Bstored%2Bprocedure%2Bimage%2Badlongvarbinary
SK
John Peterson wrote:
>Thanks, Steve!
>One other option I'm exploring is using a stored procedure with an IMAGE parameter. But,
>I have very little experiencing in using VBScript (this is going to be called from a Web
>page) and ADO Parameter objects for an IMAGE data. I've been searching on the Web, and
>I've seen a few snippets -- but not a comprehensive example. Do you have any
>recommendations/links on that route, perchance?
>
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:OfadH8WvEHA.2616@.TK2MSFTNGP10.phx.gbl...
>
>>John,
>> It's got to be exact. If you oversize it, you get an unexpected end-of-file:
>>Server: Msg 4832, Level 16, State 1, Line 1
>>Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
>>Server: Msg 7399, Level 16, State 1, Line 1
>>OLE DB provider 'STREAM' reported an error. The provider did not give any information
>>about the error.
>>OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005:
>>The provider did not give any information about the error.].
>>The statement has been terminated.
>>SK
>>John Peterson wrote:
>>
>>Thanks oj and Steve! I think that'll do the trick, even if it's a little awkward. :-)
>>BTW: Do you need an *exact* binary file size in the format file, as Steve's link
>>suggests? Or will "oversizing" it suffice?
>>Thanks again!
>>
>>"John Peterson" <j0hnp@.comcast.net> wrote in message
>>news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
>>
>>(SQL Server 2000, SP3a)
>>Hello all!
>>Is there any way to use BULK INSERT like TEXTCOPY? I have a series of files (on disk),
>>and I'd like to squirt them in to a table that has an IMAGE column. I could "shell"
>>out to use TEXTCOPY, but was wondering if I could leverage some built-in SQL construct.
>>BULK INSERT looked close, but it also looks like the built-in analogue to BCP. That
>>is, it processes the contents of the file, and I really just want to squirt the file in
>>to the column verbatim.
>>Thanks for any help you can provide!
>>John Peterson
>>
>>
>>
>>
>
>|||Great stuff! Thanks again, Steve! :-)
"Steve Kass" <skass@.drew.edu> wrote in message
news:eXQUuyjvEHA.2804@.TK2MSFTNGP14.phx.gbl...
> John,
> I haven't done this myself, but you should be able to pass the image data with a
> adLongVarBinary parameter.
> You might need to create the parameter 1 byte longer than the length of the data you're
> storing, according to
> http://support.microsoft.com/default.aspx?scid=kb;en-us;190450
> I also saw one suggestion that this meant you have to read back all but the last byte of
> the stored image
> value when retrieving the data, so you should be watchful:
> http://groups.google.com/groups?hl=en&lr=&safe=off&threadm=uMwa0SoD%24GA.235%40cppssbbsa02.microsoft.com&rnum=6&prev=/groups%3Fhl%3Den%26lr%3D%26safe%3Doff%26q%3Dsqlserver%2Bstored%2Bprocedure%2Bimage%2Badlongvarbinary
> SK
>
> John Peterson wrote:
>>Thanks, Steve!
>>One other option I'm exploring is using a stored procedure with an IMAGE parameter.
>>But, I have very little experiencing in using VBScript (this is going to be called from
>>a Web page) and ADO Parameter objects for an IMAGE data. I've been searching on the
>>Web, and I've seen a few snippets -- but not a comprehensive example. Do you have any
>>recommendations/links on that route, perchance?
>>
>>"Steve Kass" <skass@.drew.edu> wrote in message
>>news:OfadH8WvEHA.2616@.TK2MSFTNGP10.phx.gbl...
>>John,
>> It's got to be exact. If you oversize it, you get an unexpected end-of-file:
>>Server: Msg 4832, Level 16, State 1, Line 1
>>Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
>>Server: Msg 7399, Level 16, State 1, Line 1
>>OLE DB provider 'STREAM' reported an error. The provider did not give any information
>>about the error.
>>OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005:
>>The provider did not give any information about the error.].
>>The statement has been terminated.
>>SK
>>John Peterson wrote:
>>
>>Thanks oj and Steve! I think that'll do the trick, even if it's a little awkward.
>>:-)
>>BTW: Do you need an *exact* binary file size in the format file, as Steve's link
>>suggests? Or will "oversizing" it suffice?
>>Thanks again!
>>
>>"John Peterson" <j0hnp@.comcast.net> wrote in message
>>news:OtfVd1TvEHA.908@.TK2MSFTNGP11.phx.gbl...
>>
>>(SQL Server 2000, SP3a)
>>Hello all!
>>Is there any way to use BULK INSERT like TEXTCOPY? I have a series of files (on
>>disk), and I'd like to squirt them in to a table that has an IMAGE column. I could
>>"shell" out to use TEXTCOPY, but was wondering if I could leverage some built-in SQL
>>construct. BULK INSERT looked close, but it also looks like the built-in analogue to
>>BCP. That is, it processes the contents of the file, and I really just want to
>>squirt the file in to the column verbatim.
>>Thanks for any help you can provide!
>>John Peterson
>>
>>
>>
>>

Can BULK INSERT be used like TEXTCOPY?

Can backup to disk, but not to tape - Access Denied

I have been trying to use the Maintenance Wizard to setup a backup job to
tape however I continually get an Access Denied message for the failure.
Even when I try to insert a previously successful tape and try to see what
is on it I still get the Access denied message (Bad Password). I am able to
backup to a folder without any problem.
Both SQL server and Agent start with a Domain Admin account and I have
created these jobs while logging in with the accounts. I have even created
the databases with this account as well. No problem accessing the data,
just backing up to tape.
Windows 2003 Server SP1
SQL 2000 SP3
All latest Windows UpdatesJordan wrote:
> I have been trying to use the Maintenance Wizard to setup a backup job to
> tape however I continually get an Access Denied message for the failure.
> Even when I try to insert a previously successful tape and try to see what
> is on it I still get the Access denied message (Bad Password). I am able to
> backup to a folder without any problem.
> Both SQL server and Agent start with a Domain Admin account and I have
> created these jobs while logging in with the accounts. I have even created
> the databases with this account as well. No problem accessing the data,
> just backing up to tape.
> Windows 2003 Server SP1
> SQL 2000 SP3
> All latest Windows Updates
>
>
For what it's worth, most folks don't write SQL backups directly to
tape. Write your SQL backups to disk, and then let your tape backup
software backup those backup files to tape.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I found that even though the services are run as MYDOMAIN\Administrator the
backup task kept getting set to run as Administrator@.mydomain.com. When I
manually changed the job to MYDOMAIN\Administrator it started to work.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4526493E.6080206@.realsqlguy.com...
> Jordan wrote:
>> I have been trying to use the Maintenance Wizard to setup a backup job to
>> tape however I continually get an Access Denied message for the failure.
>> Even when I try to insert a previously successful tape and try to see
>> what is on it I still get the Access denied message (Bad Password). I am
>> able to backup to a folder without any problem.
>> Both SQL server and Agent start with a Domain Admin account and I have
>> created these jobs while logging in with the accounts. I have even
>> created the databases with this account as well. No problem accessing
>> the data, just backing up to tape.
>> Windows 2003 Server SP1
>> SQL 2000 SP3
>> All latest Windows Updates
>>
>>
> For what it's worth, most folks don't write SQL backups directly to tape.
> Write your SQL backups to disk, and then let your tape backup software
> backup those backup files to tape.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Can backup to disk, but not to tape - Access Denied

I have been trying to use the Maintenance Wizard to setup a backup job to
tape however I continually get an Access Denied message for the failure.
Even when I try to insert a previously successful tape and try to see what
is on it I still get the Access denied message (Bad Password). I am able to
backup to a folder without any problem.
Both SQL server and Agent start with a Domain Admin account and I have
created these jobs while logging in with the accounts. I have even created
the databases with this account as well. No problem accessing the data,
just backing up to tape.
Windows 2003 Server SP1
SQL 2000 SP3
All latest Windows Updates
Jordan wrote:
> I have been trying to use the Maintenance Wizard to setup a backup job to
> tape however I continually get an Access Denied message for the failure.
> Even when I try to insert a previously successful tape and try to see what
> is on it I still get the Access denied message (Bad Password). I am able to
> backup to a folder without any problem.
> Both SQL server and Agent start with a Domain Admin account and I have
> created these jobs while logging in with the accounts. I have even created
> the databases with this account as well. No problem accessing the data,
> just backing up to tape.
> Windows 2003 Server SP1
> SQL 2000 SP3
> All latest Windows Updates
>
>
For what it's worth, most folks don't write SQL backups directly to
tape. Write your SQL backups to disk, and then let your tape backup
software backup those backup files to tape.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||I found that even though the services are run as MYDOMAIN\Administrator the
backup task kept getting set to run as Administrator@.mydomain.com. When I
manually changed the job to MYDOMAIN\Administrator it started to work.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4526493E.6080206@.realsqlguy.com...
> Jordan wrote:
> For what it's worth, most folks don't write SQL backups directly to tape.
> Write your SQL backups to disk, and then let your tape backup software
> backup those backup files to tape.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Can backup to disk, but not to tape - Access Denied

I have been trying to use the Maintenance Wizard to setup a backup job to
tape however I continually get an Access Denied message for the failure.
Even when I try to insert a previously successful tape and try to see what
is on it I still get the Access denied message (Bad Password). I am able to
backup to a folder without any problem.
Both SQL server and Agent start with a Domain Admin account and I have
created these jobs while logging in with the accounts. I have even created
the databases with this account as well. No problem accessing the data,
just backing up to tape.
Windows 2003 Server SP1
SQL 2000 SP3
All latest Windows UpdatesJordan wrote:
> I have been trying to use the Maintenance Wizard to setup a backup job to
> tape however I continually get an Access Denied message for the failure.
> Even when I try to insert a previously successful tape and try to see what
> is on it I still get the Access denied message (Bad Password). I am able
to
> backup to a folder without any problem.
> Both SQL server and Agent start with a Domain Admin account and I have
> created these jobs while logging in with the accounts. I have even create
d
> the databases with this account as well. No problem accessing the data,
> just backing up to tape.
> Windows 2003 Server SP1
> SQL 2000 SP3
> All latest Windows Updates
>
>
For what it's worth, most folks don't write SQL backups directly to
tape. Write your SQL backups to disk, and then let your tape backup
software backup those backup files to tape.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I found that even though the services are run as MYDOMAIN\Administrator the
backup task kept getting set to run as Administrator@.mydomain.com. When I
manually changed the job to MYDOMAIN\Administrator it started to work.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:4526493E.6080206@.realsqlguy.com...
> Jordan wrote:
> For what it's worth, most folks don't write SQL backups directly to tape.
> Write your SQL backups to disk, and then let your tape backup software
> backup those backup files to tape.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Tuesday, March 27, 2012

Can I force a checkpoint.

Monday, March 19, 2012

can i add more files to the filegroup?

Wednesday, March 7, 2012

Can BULK INSERT be used like TEXTCOPY?

Can BULK INSERT be used like TEXTCOPY?

Can BULK INSERT be used like TEXTCOPY?

Can backup to disk, but not to tape - Access Denied

Can backup to disk, but not to tape - Access Denied

Can backup to disk, but not to tape - Access Denied

Calling Webservice

Blog Archive

About Me