Folks
I have two queries
Select Account_Id , Branch_Cd from Accounts
SELECT SUM (dbo.HOLDING.Shares_Par_Value_Qty * dbo.ASSET.Current_Prc) AS MarketValue
FROM dbo.HOLDING INNER JOIN
dbo.ASSET ON dbo.HOLDING.Property_Num = dbo.ASSET.Property_Num
Group by dbo.HOLDING.Account_ID
Account_ID is the same in both the queries ie in both the tables
Holding and Account.
I need the output like this
Select Account_Id, Branch_Cd, MarketValue from ---
But MarketValue should be calculated exactly in the above method.
How do I combine these two queries. I need it asap.
Help me out.
ThanksCheck my reply to your SUM post. If it isn't clear from that then get back to me.
Ursus|||How about:
SELECT A.Account_Id , A.Branch_Cd, B.MarketValue
FROM Accounts AS A
INNER JOIN
( SELECT SUM (dbo.HOLDING.Shares_Par_Value_Qty * dbo.ASSET.Current_Prc) AS MarketValue
FROM dbo.HOLDING
INNER JOIN dbo.ASSET
ON dbo.HOLDING.Property_Num = dbo.ASSET.Property_Num
GROUP BY dbo.HOLDING.Account_ID ) AS B
ON A.Account_ID = B.Account_ID
Showing posts with label urgent. Show all posts
Showing posts with label urgent. Show all posts
Monday, March 19, 2012
Friday, March 9, 2012
Needs urgent help sql 2000+replication+error no 20598
Hi,
I am getting an error while replicating the sql 2000 database.
The error no is 20598...It stopped replicating the database...
Can any body tell me what is this pls...
Tks in advance...
JoyceIf you provide the answers for the following questions, it will be easier for others to help you.
What kind of replication are you using?
Which service pack has been installed?|||If you provide the answers for the following questions, it will be easier for others to help you.
What kind of replication are you using?
Which service pack has been installed?
I am using Transactional replication|||Hey, I came up with the link at Microsoft support:
http://support.microsoft.com/search/default.aspx?InCC_hdn=True&Catalog=LCID%3D1033%26CDID%3DEN-US-KB%26PRODLISTSRC%3DON&Product=&KeywordType=ALL&Titles=false&numDays=&maxResults=25&Queryl=sql+server+replication+error+%2320598&Query=sql+server+replication+error+%2320598&QuerySource=gsfxSearch_Query&srchExtraQry=
Nothing at lazydba, Looks like it's a service pack issue. I'd check with the network group before continueing.|||Come on!!!!!!!!!!!!!!, We can solve this. Let's do the damn thing.
sp_scriptdynamicupdproc is used in transactional replication. The default MCALL scripting logic includes all columns within the UPDATE statement and uses a bitmap to determine the columns that have changed. If a column did not change, the column is set back to itself, which usually causes no problems. If the column is indexed, extra processing occurs. By contrast, this stored procedure uses a dynamic approach: it includes only the columns that have changed, which provides an optimal UPDATE string. However, extra processing is incurred at run time when the dynamic UPDATE statement is built. It is recommended that you test both the dynamic stored procedure approach and the static default approach, and then choose the optimal solution for your particular needs.
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598|||Come on!!!!!!!!!!!!!!, We can solve this. Let's do the damn thing.
sp_scriptdynamicupdproc is used in transactional replication. The default MCALL scripting logic includes all columns within the UPDATE statement and uses a bitmap to determine the columns that have changed. If a column did not change, the column is set back to itself, which usually causes no problems. If the column is indexed, extra processing occurs. By contrast, this stored procedure uses a dynamic approach: it includes only the columns that have changed, which provides an optimal UPDATE string. However, extra processing is incurred at run time when the dynamic UPDATE statement is built. It is recommended that you test both the dynamic stored procedure approach and the static default approach, and then choose the optimal solution for your particular needs.
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
Hi,
My replication was working fine without any problem. But suddenly the error came (erro numbar 20598).
My replication is transactional replication..
Any body knows this pls.help...
I am getting an error while replicating the sql 2000 database.
The error no is 20598...It stopped replicating the database...
Can any body tell me what is this pls...
Tks in advance...
JoyceIf you provide the answers for the following questions, it will be easier for others to help you.
What kind of replication are you using?
Which service pack has been installed?|||If you provide the answers for the following questions, it will be easier for others to help you.
What kind of replication are you using?
Which service pack has been installed?
I am using Transactional replication|||Hey, I came up with the link at Microsoft support:
http://support.microsoft.com/search/default.aspx?InCC_hdn=True&Catalog=LCID%3D1033%26CDID%3DEN-US-KB%26PRODLISTSRC%3DON&Product=&KeywordType=ALL&Titles=false&numDays=&maxResults=25&Queryl=sql+server+replication+error+%2320598&Query=sql+server+replication+error+%2320598&QuerySource=gsfxSearch_Query&srchExtraQry=
Nothing at lazydba, Looks like it's a service pack issue. I'd check with the network group before continueing.|||Come on!!!!!!!!!!!!!!, We can solve this. Let's do the damn thing.
sp_scriptdynamicupdproc is used in transactional replication. The default MCALL scripting logic includes all columns within the UPDATE statement and uses a bitmap to determine the columns that have changed. If a column did not change, the column is set back to itself, which usually causes no problems. If the column is indexed, extra processing occurs. By contrast, this stored procedure uses a dynamic approach: it includes only the columns that have changed, which provides an optimal UPDATE string. However, extra processing is incurred at run time when the dynamic UPDATE statement is built. It is recommended that you test both the dynamic stored procedure approach and the static default approach, and then choose the optimal solution for your particular needs.
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598|||Come on!!!!!!!!!!!!!!, We can solve this. Let's do the damn thing.
sp_scriptdynamicupdproc is used in transactional replication. The default MCALL scripting logic includes all columns within the UPDATE statement and uses a bitmap to determine the columns that have changed. If a column did not change, the column is set back to itself, which usually causes no problems. If the column is indexed, extra processing occurs. By contrast, this stored procedure uses a dynamic approach: it includes only the columns that have changed, which provides an optimal UPDATE string. However, extra processing is incurred at run time when the dynamic UPDATE statement is built. It is recommended that you test both the dynamic stored procedure approach and the static default approach, and then choose the optimal solution for your particular needs.
if @.@.rowcount = 0
if @.@.microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
Hi,
My replication was working fine without any problem. But suddenly the error came (erro numbar 20598).
My replication is transactional replication..
Any body knows this pls.help...
Wednesday, March 7, 2012
Need urgent MDX help on using Aggreate funtion in SSAS 2005
Hi,
I need a functionality similar to Named Member in ProClarity in SSAS 2005.
In Proclarity I can define a named memeber by selecting a few levels of a dimension. The underlying MDX uses the Aggregate function something like this
Aggregate({ [Channel].[Channel].[CON - Contractor], [Channel].[Channel].[DIS - Distributor], [Channel].[Channel].[END - End-User], [Channel].[Channel].[GRP - Group], [Channel].[Channel].[OEM - OEM], [Channel].[Channel].[PLA - Private Label], [Channel].[Channel].[SER - Service Provider], [Channel].[Channel].[SYS - System builder] })
Once the named member is selected the calculated measures show the aggreated (sum) results based on the named member and the underlying selected levels in the aggreate function.
How can I do something like this in SSAS 2005. I dont want a named set. And I tired created a calculated member (which i knew will not work but still gave a shot)
Aggregate({ [Channel].[Channel].[CON - Contractor], [Channel].[Channel].[DIS - Distributor], [Channel].[Channel].[END - End-User], [Channel].[Channel].[GRP - Group], [Channel].[Channel].[OEM - OEM], [Channel].[Channel].[PLA - Private Label], [Channel].[Channel].[SER - Service Provider], [Channel].[Channel].[SYS - System builder] })
I also tried specifying the measure in the second parameter
Aggregate({ [Channel].[Channel].[CON - Contractor], [Channel].[Channel].[DIS - Distributor], [Channel].[Channel].[END - End-User], [Channel].[Channel].[GRP - Group], [Channel].[Channel].[OEM - OEM], [Channel].[Channel].[PLA - Private Label], [Channel].[Channel].[SER - Service Provider], [Channel].[Channel].[SYS - System builder] }
, [Measures].[Orders Received Local])
Is it possible to use the aggreate function dynamically as its used in ProClarity?
Thanks in advance for help
The aggregate function should work fine, can you tell us exactly what error you are getting. One thing about the aggregate function is that you would need to create the calculated member on a dimension other than the measures dimension.
Something like the following should report on a dynamically created aggregate member:
Code Snippet
WITH
MEMBER [Channel].[Channel].[AggTest]
as Aggregate({
[Channel].[Channel].[CON - Contractor]
, [Channel].[Channel].[DIS - Distributor]
, [Channel].[Channel].[END - End-User]
, [Channel].[Channel].[GRP - Group]
, [Channel].[Channel].[OEM - OEM]
, [Channel].[Channel].[PLA - Private Label]
, [Channel].[Channel].[SER - Service Provider]
, [Channel].[Channel].[SYS - System builder]
})
SELECT
{[Channel].[Channel].[AggTest]} ON COLUMNS
FROM <Cube>
|||Thanks. yes it worked this way. i made the mistake of creating it on the measures dimension. after changing the dimension to Channel it works fine.Saturday, February 25, 2012
Need Urgent Help...............
Hi i am manish,working wth sql server2000
Need Urgent Help............
Currently,we are preparing our one software,
I want to enfore some security check ,so that user of that software not able
to watch what kind of data that software contains
security i need for that
1) application level: no other external software are allowed
2)default Windows NT groups are to be disable
3) user should able to execute any kind select ,etc query
Regards
ManishHave a look at Application Roles in BOL. By activating an application role
from your application and by only granting permissions to that application
role, you should be able to get the behaviour you require
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Manish Mokadam" <manish.mokadam@.honeywell.com> wrote in message
news:471E447A-D6B2-4361-8814-AF59362FA34D@.microsoft.com...
> Hi i am manish,working wth sql server2000
> Need Urgent Help............
> Currently,we are preparing our one software,
> I want to enfore some security check ,so that user of that software not
able to watch what kind of data that software contains
> security i need for that
> 1) application level: no other external software are allowed
> 2)default Windows NT groups are to be disable
> 3) user should able to execute any kind select ,etc query
>
> Regards
> Manish
Need Urgent Help............
Currently,we are preparing our one software,
I want to enfore some security check ,so that user of that software not able
to watch what kind of data that software contains
security i need for that
1) application level: no other external software are allowed
2)default Windows NT groups are to be disable
3) user should able to execute any kind select ,etc query
Regards
ManishHave a look at Application Roles in BOL. By activating an application role
from your application and by only granting permissions to that application
role, you should be able to get the behaviour you require
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Manish Mokadam" <manish.mokadam@.honeywell.com> wrote in message
news:471E447A-D6B2-4361-8814-AF59362FA34D@.microsoft.com...
> Hi i am manish,working wth sql server2000
> Need Urgent Help............
> Currently,we are preparing our one software,
> I want to enfore some security check ,so that user of that software not
able to watch what kind of data that software contains
> security i need for that
> 1) application level: no other external software are allowed
> 2)default Windows NT groups are to be disable
> 3) user should able to execute any kind select ,etc query
>
> Regards
> Manish
Need urgent help.. for SQL Server reporting services installation..
hi,
I have SQL Server Express edition installed on my PC , but now I need to use SQL Server reporting service, there is SP1 for express edition contains reporting service with express edition. Here..
http://msdn.microsoft.com/vstudio/express/sql/download/
Do I need to download 213 MB SQL Server 2005 Express Edition Toolkit SP1 ?
or there is any other method to use reporting services ?
Thanks
You will have to download the full install again, there is no way of installing the components seperatly. With SQl 2000 there used to be a seperate DL for Reporting Services, but in the 2005 stream they are all included in the one package.
Need URGENT help.
Hello-
I am trying to restore a large sized database (100+ GB) on a machine that
didn't have database in the beginning, so its a create DB + restore operation.
Below is the machine configuration:
Machine Configuration: quad processor (Pentium 3 Xeon)
Memory: 2 GB
HDD: Fiber Array Channel with separate controllers.
Version: SQL Server 2000/SP3 (Standard Version)
OS: Windows 2003/SP1 (sp presence is a guess).
I understand the process will take some time to create database first before
starting restoration. So far, its been running for over 2 1/2 hours without
any sign of restoration (I have kept STATS=1 for getting any indication of
restoration start).
Now, I have three(3) questions:
1. What should be the approximate DB creation time in such type of
environment? 3-4 hours'
2. How come I know if there is a problem? Currently the restore process has
wait type of ASYNC_IO_COMPLETION with very large wait time.
3. Can upgrading to Enterprise version help parallelize ANY workload (here
db creation/restoration) across all processers? OR this is only true in some
instances say index rebuild?
Thanks in advance!!!
--
Regards,
MZeeshanHi
Fist SQL Server has to create the DB and allocate all the pages for the data
and log. This can take the longest.
Once this is done, the restore can really happen.
A 100GB DB can take anything between 30 minutes and 10 hours to create,
depending on the IO performance of the disk subsystem.
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/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
> Hello-
> I am trying to restore a large sized database (100+ GB) on a machine that
> didn't have database in the beginning, so its a create DB + restore
> operation.
> Below is the machine configuration:
> Machine Configuration: quad processor (Pentium 3 Xeon)
> Memory: 2 GB
> HDD: Fiber Array Channel with separate controllers.
> Version: SQL Server 2000/SP3 (Standard Version)
> OS: Windows 2003/SP1 (sp presence is a guess).
> I understand the process will take some time to create database first
> before
> starting restoration. So far, its been running for over 2 1/2 hours
> without
> any sign of restoration (I have kept STATS=1 for getting any indication of
> restoration start).
> Now, I have three(3) questions:
> 1. What should be the approximate DB creation time in such type of
> environment? 3-4 hours'
> 2. How come I know if there is a problem? Currently the restore process
> has
> wait type of ASYNC_IO_COMPLETION with very large wait time.
> 3. Can upgrading to Enterprise version help parallelize ANY workload (here
> db creation/restoration) across all processers? OR this is only true in
> some
> instances say index rebuild?
> Thanks in advance!!!
> --
> Regards,
> MZeeshan|||Thanks!
Yes, it took just around 5 hours to create the database and currently in
restoration phase.
About my last question: Have you ever noticed any visible improvement in any
system when license is upgraded from Standard to Enterprise?
Database creation, backup/restoration and index rebuilds are some of the
common activities happening on this box.
--
Regards,
MZeeshan
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Fist SQL Server has to create the DB and allocate all the pages for the data
> and log. This can take the longest.
> Once this is done, the restore can really happen.
> A 100GB DB can take anything between 30 minutes and 10 hours to create,
> depending on the IO performance of the disk subsystem.
> 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/
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
> > Hello-
> >
> > I am trying to restore a large sized database (100+ GB) on a machine that
> > didn't have database in the beginning, so its a create DB + restore
> > operation.
> >
> > Below is the machine configuration:
> >
> > Machine Configuration: quad processor (Pentium 3 Xeon)
> > Memory: 2 GB
> > HDD: Fiber Array Channel with separate controllers.
> > Version: SQL Server 2000/SP3 (Standard Version)
> > OS: Windows 2003/SP1 (sp presence is a guess).
> >
> > I understand the process will take some time to create database first
> > before
> > starting restoration. So far, its been running for over 2 1/2 hours
> > without
> > any sign of restoration (I have kept STATS=1 for getting any indication of
> > restoration start).
> >
> > Now, I have three(3) questions:
> >
> > 1. What should be the approximate DB creation time in such type of
> > environment? 3-4 hours'
> >
> > 2. How come I know if there is a problem? Currently the restore process
> > has
> > wait type of ASYNC_IO_COMPLETION with very large wait time.
> >
> > 3. Can upgrading to Enterprise version help parallelize ANY workload (here
> > db creation/restoration) across all processers? OR this is only true in
> > some
> > instances say index rebuild?
> >
> > Thanks in advance!!!
> >
> > --
> > Regards,
> > MZeeshan
>
>|||Those operations generally do not get helped by parallelism due to their IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does more
to help than STD/EE editions upgrade.
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/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Fist SQL Server has to create the DB and allocate all the pages for the
>> data
>> and log. This can take the longest.
>> Once this is done, the restore can really happen.
>> A 100GB DB can take anything between 30 minutes and 10 hours to create,
>> depending on the IO performance of the disk subsystem.
>> 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/
>> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
>> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
>> > Hello-
>> >
>> > I am trying to restore a large sized database (100+ GB) on a machine
>> > that
>> > didn't have database in the beginning, so its a create DB + restore
>> > operation.
>> >
>> > Below is the machine configuration:
>> >
>> > Machine Configuration: quad processor (Pentium 3 Xeon)
>> > Memory: 2 GB
>> > HDD: Fiber Array Channel with separate controllers.
>> > Version: SQL Server 2000/SP3 (Standard Version)
>> > OS: Windows 2003/SP1 (sp presence is a guess).
>> >
>> > I understand the process will take some time to create database first
>> > before
>> > starting restoration. So far, its been running for over 2 1/2 hours
>> > without
>> > any sign of restoration (I have kept STATS=1 for getting any indication
>> > of
>> > restoration start).
>> >
>> > Now, I have three(3) questions:
>> >
>> > 1. What should be the approximate DB creation time in such type of
>> > environment? 3-4 hours'
>> >
>> > 2. How come I know if there is a problem? Currently the restore process
>> > has
>> > wait type of ASYNC_IO_COMPLETION with very large wait time.
>> >
>> > 3. Can upgrading to Enterprise version help parallelize ANY workload
>> > (here
>> > db creation/restoration) across all processers? OR this is only true in
>> > some
>> > instances say index rebuild?
>> >
>> > Thanks in advance!!!
>> >
>> > --
>> > Regards,
>> > MZeeshan
>>|||This is a multi-part message in MIME format.
--=_NextPart_000_0595_01C56968.94534E90
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Also, not only do you have to wait for all data files and transaction log
files to be created and zeroed out, SQL Server will first run a BACKUP
VERIFICATION and you will first have to wait for SQL Server to read through
the backup file first. The file read itself can take minutes to hours
depending on size, disk throughput, and backup file multiplexing.
If you need fast recovery, you should consider backing up to multiple files
per backup. This will allow you to run parallel read operations for the
verification and actual restore phases; however, the raw database creation
will still be solely dependent on the disk subsystem throughput.
If you also need fast backup times, consider multiple data files per
filegroup. This will allow SQL Server to run parallel operations for the
backup process.
An alternative to these methods would be to use one of several 3rd-party
backup tools. Lightspeed by Imceda would be a good candidate.
Sincerely,
Anthony Thomas
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eVKp0GXaFHA.2996@.TK2MSFTNGP10.phx.gbl...
Those operations generally do not get helped by parallelism due to their
IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does
more
to help than STD/EE editions upgrade.
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/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...
> Thanks!
>
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
>
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
>
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
>
> --
> Regards,
> MZeeshan
>
>
> "Mike Epprecht (SQL MVP)" wrote:
>
>> Hi
>>
>> Fist SQL Server has to create the DB and allocate all the pages for the
>> data
>> and log. This can take the longest.
>>
>> Once this is done, the restore can really happen.
>>
>> A 100GB DB can take anything between 30 minutes and 10 hours to create,
>> depending on the IO performance of the disk subsystem.
>>
>> 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/
>>
>> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
>> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
>> > Hello-
>> >
>> > I am trying to restore a large sized database (100+ GB) on a machine
>> > that
>> > didn't have database in the beginning, so its a create DB + restore
>> > operation.
>> >
>> > Below is the machine configuration:
>> >
>> > Machine Configuration: quad processor (Pentium 3 Xeon)
>> > Memory: 2 GB
>> > HDD: Fiber Array Channel with separate controllers.
>> > Version: SQL Server 2000/SP3 (Standard Version)
>> > OS: Windows 2003/SP1 (sp presence is a guess).
>> >
>> > I understand the process will take some time to create database first
>> > before
>> > starting restoration. So far, its been running for over 2 1/2 hours
>> > without
>> > any sign of restoration (I have kept STATS=1 for getting any
indication
>> > of
>> > restoration start).
>> >
>> > Now, I have three(3) questions:
>> >
>> > 1. What should be the approximate DB creation time in such type of
>> > environment? 3-4 hours'
>> >
>> > 2. How come I know if there is a problem? Currently the restore
process
>> > has
>> > wait type of ASYNC_IO_COMPLETION with very large wait time.
>> >
>> > 3. Can upgrading to Enterprise version help parallelize ANY workload
>> > (here
>> > db creation/restoration) across all processers? OR this is only true
in
>> > some
>> > instances say index rebuild?
>> >
>> > Thanks in advance!!!
>> >
>> > --
>> > Regards,
>> > MZeeshan
>>
>>
>>
--=_NextPart_000_0595_01C56968.94534E90
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Also, not only do you have to wait =for all data files and transaction log files to be created and zeroed out, SQL Server =will first run a BACKUP VERIFICATION and you will first have to wait for SQL =Server to read through the backup file first. The file read itself can =take minutes to hours depending on size, disk throughput, and =backup file multiplexing.
If you need fast recovery, you should =consider backing up to multiple files per backup. This will allow you to =run parallel read operations for the verification and actual restore phases; =however, the raw database creation will still be solely dependent on the =disk subsystem throughput.
If you also need fast backup times, =consider multiple data files per filegroup. This will allow SQL Server to =run parallel operations for the backup process.
An alternative to these methods would =be to use one of several 3rd-party backup tools. Lightspeed by Imceda =would be a good candidate.
Sincerely,
Anthony Thomas
--
"Mike Epprecht (SQL MVP)" wrote in =message news:eVKp0GXaFHA.2996=@.TK2MSFTNGP10.phx.gbl...Those operations generally do not get helped by parallelism due to their IO loads.Getting the fastest disk subsystem, and configuring =it correctly, does more to help than STD/EE editions upgrade.Regards-- --Mike = Epprecht, Microsoft SQL Server MVPZurich, SwitzerlandIM: =mike@.epprecht.netMVP =Program: http://www.microsoft.com/mvpBlog: http://www.msmvps.com/epprecht/<=/A>"MZeeshan" Thanks!>> Yes, it took just around 5 hours to create the = database and currently in> restoration phase.>> =About my last question: Have you ever noticed any visible improvement in => any> system when license is upgraded from Standard to Enterprise?>> Database creation, backup/restoration and =index rebuilds are some of the> common activities happening on this box.>> -- > Regards,> MZeeshan>>> "Mike Epprecht (SQL MVP)" wrote:>> Hi>> Fist SQL Server =has to create the DB and allocate all the pages for the > =data> and log. This can take the longest.>> Once this =is done, the restore can really happen.>> A 100GB DB can =take anything between 30 minutes and 10 hours to create,> =depending on the IO performance of the disk subsystem.>> Regards> -- > --> Mike Epprecht, Microsoft =SQL Server MVP> Zurich, Switzerland>> IM: =mike@.epprecht.net>&g=t;> MVP Program: http://www.microsoft.com/mvp>> Blog: http://www.msmvps.com/epprecht/<=/A>>> "MZeeshan" news:188=596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...> > Hello-> >> > I am trying to restore a =large sized database (100+ GB) on a machine > > =that> > didn't have database in the beginning, so its a create DB + restore> > operation.> >> > =Below is the machine configuration:> >> > Machine Configuration: quad processor (Pentium 3 Xeon)> > =Memory: 2 GB> > HDD: Fiber Array Channel with separate controllers.> > Version: SQL Server 2000/SP3 (Standard Version)> > OS: Windows 2003/SP1 (sp presence is a guess).> >> > I understand the process =will take some time to create database first> > before> => starting restoration. So far, its been running for over 2 1/2 hours> > without> > any sign of =restoration (I have kept STATS=3D1 for getting any indication > > =of> > restoration start).> >> > Now, I have = three(3) questions:> >> > 1. What should =be the approximate DB creation time in such type of> > =environment? 3-4 hours'> >> > 2. How come I know if there =is a problem? Currently the restore process> > =has> > wait type of ASYNC_IO_COMPLETION with very large wait =time.> >> > 3. Can upgrading to Enterprise version help =parallelize ANY workload > > (here> > db =creation/restoration) across all processers? OR this is only true in> > some> > instances say index rebuild?> >> > Thanks in advance!!!> =>> > -- > > Regards,> > MZeeshan>>> =
--=_NextPart_000_0595_01C56968.94534E90--|||This is a multi-part message in MIME format.
--=_NextPart_000_0024_01C569A2.12FAEC60
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
ANthonys suggestion of using striped backups to improve restore speed is =a great idea...
And in response to your third question, you will not see an difference =in backup/restore speeds if you upgrade to Enterprise Edition.
-- Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message =news:eSs5dLZaFHA.3184@.TK2MSFTNGP15.phx.gbl...
Also, not only do you have to wait for all data files and transaction =log files to be created and zeroed out, SQL Server will first run a =BACKUP VERIFICATION and you will first have to wait for SQL Server to =read through the backup file first. The file read itself can take =minutes to hours depending on size, disk throughput, and backup file =multiplexing.
If you need fast recovery, you should consider backing up to multiple =files per backup. This will allow you to run parallel read operations =for the verification and actual restore phases; however, the raw =database creation will still be solely dependent on the disk subsystem =throughput.
If you also need fast backup times, consider multiple data files per =filegroup. This will allow SQL Server to run parallel operations for =the backup process.
An alternative to these methods would be to use one of several =3rd-party backup tools. Lightspeed by Imceda would be a good candidate.
Sincerely,
Anthony Thomas
-- "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message =news:eVKp0GXaFHA.2996@.TK2MSFTNGP10.phx.gbl...
Those operations generally do not get helped by parallelism due to =their IO loads.
Getting the fastest disk subsystem, and configuring it correctly, =does more to help than STD/EE editions upgrade.
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/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...
> Thanks!
>
> Yes, it took just around 5 hours to create the database and =currently in
> restoration phase.
>
> About my last question: Have you ever noticed any visible =improvement in > any
> system when license is upgraded from Standard to Enterprise?
>
> Database creation, backup/restoration and index rebuilds are some =of the
> common activities happening on this box.
>
> -- > Regards,
> MZeeshan
>
>
> "Mike Epprecht (SQL MVP)" wrote:
>
>> Hi
>>
>> Fist SQL Server has to create the DB and allocate all the pages =for the >> data
>> and log. This can take the longest.
>>
>> Once this is done, the restore can really happen.
>>
>> A 100GB DB can take anything between 30 minutes and 10 hours to =create,
>> depending on the IO performance of the disk subsystem.
>>
>> 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/
>>
>> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
>> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
>> > Hello-
>> >
>> > I am trying to restore a large sized database (100+ GB) on a =machine >> > that
>> > didn't have database in the beginning, so its a create DB + =restore
>> > operation.
>> >
>> > Below is the machine configuration:
>> >
>> > Machine Configuration: quad processor (Pentium 3 Xeon)
>> > Memory: 2 GB
>> > HDD: Fiber Array Channel with separate controllers.
>> > Version: SQL Server 2000/SP3 (Standard Version)
>> > OS: Windows 2003/SP1 (sp presence is a guess).
>> >
>> > I understand the process will take some time to create database =first
>> > before
>> > starting restoration. So far, its been running for over 2 1/2 =hours
>> > without
>> > any sign of restoration (I have kept STATS=3D1 for getting any =indication >> > of
>> > restoration start).
>> >
>> > Now, I have three(3) questions:
>> >
>> > 1. What should be the approximate DB creation time in such type =of
>> > environment? 3-4 hours'
>> >
>> > 2. How come I know if there is a problem? Currently the restore =process
>> > has
>> > wait type of ASYNC_IO_COMPLETION with very large wait time.
>> >
>> > 3. Can upgrading to Enterprise version help parallelize ANY =workload >> > (here
>> > db creation/restoration) across all processers? OR this is only =true in
>> > some
>> > instances say index rebuild?
>> >
>> > Thanks in advance!!!
>> >
>> > -- >> > Regards,
>> > MZeeshan
>>
>>
>>
--=_NextPart_000_0024_01C569A2.12FAEC60
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
ANthonys suggestion of using striped =backups to improve restore speed is a great idea...
And in response to your third question, =you will not see an difference in backup/restore speeds if you upgrade to =Enterprise Edition.
-- Wayne Snyder, MCDBA, SQL Server MVPMariner, =Charlotte, NChttp://www.mariner-usa.com">www.mariner-usa.com(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it'scommunity of SQL Server professionals.http://www.sqlpass.org">www.sqlpass.org
"Anthony Thomas" wrote in =message news:eSs5dLZaFHA.3184=@.TK2MSFTNGP15.phx.gbl...
Also, not only do you have to wait =for all data files and transaction log files to be created and zeroed out, SQL =Server will first run a BACKUP VERIFICATION and you will first have to wait for =SQL Server to read through the backup file first. The file read itself can =take minutes to hours depending on size, disk throughput, and =backup file multiplexing.
If you need fast recovery, you =should consider backing up to multiple files per backup. This will allow you to =run parallel read operations for the verification and actual restore =phases; however, the raw database creation will still be solely dependent on =the disk subsystem throughput.
If you also need fast backup times, =consider multiple data files per filegroup. This will allow SQL Server to =run parallel operations for the backup process.
An alternative to these methods =would be to use one of several 3rd-party backup tools. Lightspeed =by Imceda would be a good candidate.
Sincerely,
Anthony Thomas
--
"Mike Epprecht (SQL MVP)" wrote in =message news:eVKp0GXaFHA.2996=@.TK2MSFTNGP10.phx.gbl...Those operations generally do not get helped by parallelism due to their =IO loads.Getting the fastest disk subsystem, and =configuring it correctly, does more to help than STD/EE editions upgrade.Regards-- =--Mike Epprecht, Microsoft SQL Server MVPZurich, SwitzerlandIM: =mike@.epprecht.netMVP =Program: http://www.microsoft.com/mvpBlog: http://www.msmvps.com/epprecht/<=/A>"MZeeshan" Thanks!>> Yes, it took just around 5 hours to create =the database and currently in> restoration phase.>> =About my last question: Have you ever noticed any visible improvement in => any> system when license is upgraded from Standard to Enterprise?>> Database creation, backup/restoration =and index rebuilds are some of the> common activities happening on this = box.>> -- > Regards,> MZeeshan>>> "Mike Epprecht (SQL MVP)" wrote:>> Hi>> Fist SQL =Server has to create the DB and allocate all the pages for the > data> and log. This can take the =longest.>> Once this is done, the restore can really =happen.>> A 100GB DB can take anything between 30 minutes and 10 hours to create,> depending on the IO performance of the disk subsystem.>> Regards> -- => --> Mike Epprecht, =Microsoft SQL Server MVP> Zurich, Switzerland>> =IM: mike@.epprecht.net>&g=t;> MVP Program: http://www.microsoft.com/mvp>> Blog: http://www.msmvps.com/epprecht/<=/A>>> "MZeeshan" news:188=596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...> > Hello-> >> > I am trying to restore =a large sized database (100+ GB) on a machine > > =that> > didn't have database in the beginning, so its a create DB + restore> > operation.> >> => Below is the machine configuration:> >> > =Machine Configuration: quad processor (Pentium 3 Xeon)> > =Memory: 2 GB> > HDD: Fiber Array Channel with separate controllers.> > Version: SQL Server 2000/SP3 (Standard = Version)> > OS: Windows 2003/SP1 (sp presence is a guess).> >> > I understand the process =will take some time to create database first> > =before> > starting restoration. So far, its been running for over 2 1/2 hours> > without> > any sign of =restoration (I have kept STATS=3D1 for getting any indication > > of> > restoration start).> >> => Now, I have three(3) questions:> >> > 1. =What should be the approximate DB creation time in such type =of> > environment? 3-4 hours'> >> > 2. How =come I know if there is a problem? Currently the restore =process> > has> > wait type of ASYNC_IO_COMPLETION with very =large wait time.> >> > 3. Can upgrading to =Enterprise version help parallelize ANY workload > > =(here> > db creation/restoration) across all processers? OR this is only =true in> > some> > instances say index rebuild?> >> > Thanks in advance!!!> >> > -- > > Regards,> > =MZeeshan>>>
--=_NextPart_000_0024_01C569A2.12FAEC60--
I am trying to restore a large sized database (100+ GB) on a machine that
didn't have database in the beginning, so its a create DB + restore operation.
Below is the machine configuration:
Machine Configuration: quad processor (Pentium 3 Xeon)
Memory: 2 GB
HDD: Fiber Array Channel with separate controllers.
Version: SQL Server 2000/SP3 (Standard Version)
OS: Windows 2003/SP1 (sp presence is a guess).
I understand the process will take some time to create database first before
starting restoration. So far, its been running for over 2 1/2 hours without
any sign of restoration (I have kept STATS=1 for getting any indication of
restoration start).
Now, I have three(3) questions:
1. What should be the approximate DB creation time in such type of
environment? 3-4 hours'
2. How come I know if there is a problem? Currently the restore process has
wait type of ASYNC_IO_COMPLETION with very large wait time.
3. Can upgrading to Enterprise version help parallelize ANY workload (here
db creation/restoration) across all processers? OR this is only true in some
instances say index rebuild?
Thanks in advance!!!
--
Regards,
MZeeshanHi
Fist SQL Server has to create the DB and allocate all the pages for the data
and log. This can take the longest.
Once this is done, the restore can really happen.
A 100GB DB can take anything between 30 minutes and 10 hours to create,
depending on the IO performance of the disk subsystem.
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/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
> Hello-
> I am trying to restore a large sized database (100+ GB) on a machine that
> didn't have database in the beginning, so its a create DB + restore
> operation.
> Below is the machine configuration:
> Machine Configuration: quad processor (Pentium 3 Xeon)
> Memory: 2 GB
> HDD: Fiber Array Channel with separate controllers.
> Version: SQL Server 2000/SP3 (Standard Version)
> OS: Windows 2003/SP1 (sp presence is a guess).
> I understand the process will take some time to create database first
> before
> starting restoration. So far, its been running for over 2 1/2 hours
> without
> any sign of restoration (I have kept STATS=1 for getting any indication of
> restoration start).
> Now, I have three(3) questions:
> 1. What should be the approximate DB creation time in such type of
> environment? 3-4 hours'
> 2. How come I know if there is a problem? Currently the restore process
> has
> wait type of ASYNC_IO_COMPLETION with very large wait time.
> 3. Can upgrading to Enterprise version help parallelize ANY workload (here
> db creation/restoration) across all processers? OR this is only true in
> some
> instances say index rebuild?
> Thanks in advance!!!
> --
> Regards,
> MZeeshan|||Thanks!
Yes, it took just around 5 hours to create the database and currently in
restoration phase.
About my last question: Have you ever noticed any visible improvement in any
system when license is upgraded from Standard to Enterprise?
Database creation, backup/restoration and index rebuilds are some of the
common activities happening on this box.
--
Regards,
MZeeshan
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Fist SQL Server has to create the DB and allocate all the pages for the data
> and log. This can take the longest.
> Once this is done, the restore can really happen.
> A 100GB DB can take anything between 30 minutes and 10 hours to create,
> depending on the IO performance of the disk subsystem.
> 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/
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
> > Hello-
> >
> > I am trying to restore a large sized database (100+ GB) on a machine that
> > didn't have database in the beginning, so its a create DB + restore
> > operation.
> >
> > Below is the machine configuration:
> >
> > Machine Configuration: quad processor (Pentium 3 Xeon)
> > Memory: 2 GB
> > HDD: Fiber Array Channel with separate controllers.
> > Version: SQL Server 2000/SP3 (Standard Version)
> > OS: Windows 2003/SP1 (sp presence is a guess).
> >
> > I understand the process will take some time to create database first
> > before
> > starting restoration. So far, its been running for over 2 1/2 hours
> > without
> > any sign of restoration (I have kept STATS=1 for getting any indication of
> > restoration start).
> >
> > Now, I have three(3) questions:
> >
> > 1. What should be the approximate DB creation time in such type of
> > environment? 3-4 hours'
> >
> > 2. How come I know if there is a problem? Currently the restore process
> > has
> > wait type of ASYNC_IO_COMPLETION with very large wait time.
> >
> > 3. Can upgrading to Enterprise version help parallelize ANY workload (here
> > db creation/restoration) across all processers? OR this is only true in
> > some
> > instances say index rebuild?
> >
> > Thanks in advance!!!
> >
> > --
> > Regards,
> > MZeeshan
>
>|||Those operations generally do not get helped by parallelism due to their IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does more
to help than STD/EE editions upgrade.
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/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Fist SQL Server has to create the DB and allocate all the pages for the
>> data
>> and log. This can take the longest.
>> Once this is done, the restore can really happen.
>> A 100GB DB can take anything between 30 minutes and 10 hours to create,
>> depending on the IO performance of the disk subsystem.
>> 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/
>> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
>> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
>> > Hello-
>> >
>> > I am trying to restore a large sized database (100+ GB) on a machine
>> > that
>> > didn't have database in the beginning, so its a create DB + restore
>> > operation.
>> >
>> > Below is the machine configuration:
>> >
>> > Machine Configuration: quad processor (Pentium 3 Xeon)
>> > Memory: 2 GB
>> > HDD: Fiber Array Channel with separate controllers.
>> > Version: SQL Server 2000/SP3 (Standard Version)
>> > OS: Windows 2003/SP1 (sp presence is a guess).
>> >
>> > I understand the process will take some time to create database first
>> > before
>> > starting restoration. So far, its been running for over 2 1/2 hours
>> > without
>> > any sign of restoration (I have kept STATS=1 for getting any indication
>> > of
>> > restoration start).
>> >
>> > Now, I have three(3) questions:
>> >
>> > 1. What should be the approximate DB creation time in such type of
>> > environment? 3-4 hours'
>> >
>> > 2. How come I know if there is a problem? Currently the restore process
>> > has
>> > wait type of ASYNC_IO_COMPLETION with very large wait time.
>> >
>> > 3. Can upgrading to Enterprise version help parallelize ANY workload
>> > (here
>> > db creation/restoration) across all processers? OR this is only true in
>> > some
>> > instances say index rebuild?
>> >
>> > Thanks in advance!!!
>> >
>> > --
>> > Regards,
>> > MZeeshan
>>|||This is a multi-part message in MIME format.
--=_NextPart_000_0595_01C56968.94534E90
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Also, not only do you have to wait for all data files and transaction log
files to be created and zeroed out, SQL Server will first run a BACKUP
VERIFICATION and you will first have to wait for SQL Server to read through
the backup file first. The file read itself can take minutes to hours
depending on size, disk throughput, and backup file multiplexing.
If you need fast recovery, you should consider backing up to multiple files
per backup. This will allow you to run parallel read operations for the
verification and actual restore phases; however, the raw database creation
will still be solely dependent on the disk subsystem throughput.
If you also need fast backup times, consider multiple data files per
filegroup. This will allow SQL Server to run parallel operations for the
backup process.
An alternative to these methods would be to use one of several 3rd-party
backup tools. Lightspeed by Imceda would be a good candidate.
Sincerely,
Anthony Thomas
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eVKp0GXaFHA.2996@.TK2MSFTNGP10.phx.gbl...
Those operations generally do not get helped by parallelism due to their
IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does
more
to help than STD/EE editions upgrade.
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/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...
> Thanks!
>
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
>
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
>
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
>
> --
> Regards,
> MZeeshan
>
>
> "Mike Epprecht (SQL MVP)" wrote:
>
>> Hi
>>
>> Fist SQL Server has to create the DB and allocate all the pages for the
>> data
>> and log. This can take the longest.
>>
>> Once this is done, the restore can really happen.
>>
>> A 100GB DB can take anything between 30 minutes and 10 hours to create,
>> depending on the IO performance of the disk subsystem.
>>
>> 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/
>>
>> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
>> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
>> > Hello-
>> >
>> > I am trying to restore a large sized database (100+ GB) on a machine
>> > that
>> > didn't have database in the beginning, so its a create DB + restore
>> > operation.
>> >
>> > Below is the machine configuration:
>> >
>> > Machine Configuration: quad processor (Pentium 3 Xeon)
>> > Memory: 2 GB
>> > HDD: Fiber Array Channel with separate controllers.
>> > Version: SQL Server 2000/SP3 (Standard Version)
>> > OS: Windows 2003/SP1 (sp presence is a guess).
>> >
>> > I understand the process will take some time to create database first
>> > before
>> > starting restoration. So far, its been running for over 2 1/2 hours
>> > without
>> > any sign of restoration (I have kept STATS=1 for getting any
indication
>> > of
>> > restoration start).
>> >
>> > Now, I have three(3) questions:
>> >
>> > 1. What should be the approximate DB creation time in such type of
>> > environment? 3-4 hours'
>> >
>> > 2. How come I know if there is a problem? Currently the restore
process
>> > has
>> > wait type of ASYNC_IO_COMPLETION with very large wait time.
>> >
>> > 3. Can upgrading to Enterprise version help parallelize ANY workload
>> > (here
>> > db creation/restoration) across all processers? OR this is only true
in
>> > some
>> > instances say index rebuild?
>> >
>> > Thanks in advance!!!
>> >
>> > --
>> > Regards,
>> > MZeeshan
>>
>>
>>
--=_NextPart_000_0595_01C56968.94534E90
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Also, not only do you have to wait =for all data files and transaction log files to be created and zeroed out, SQL Server =will first run a BACKUP VERIFICATION and you will first have to wait for SQL =Server to read through the backup file first. The file read itself can =take minutes to hours depending on size, disk throughput, and =backup file multiplexing.
If you need fast recovery, you should =consider backing up to multiple files per backup. This will allow you to =run parallel read operations for the verification and actual restore phases; =however, the raw database creation will still be solely dependent on the =disk subsystem throughput.
If you also need fast backup times, =consider multiple data files per filegroup. This will allow SQL Server to =run parallel operations for the backup process.
An alternative to these methods would =be to use one of several 3rd-party backup tools. Lightspeed by Imceda =would be a good candidate.
Sincerely,
Anthony Thomas
--
"Mike Epprecht (SQL MVP)"
--=_NextPart_000_0595_01C56968.94534E90--|||This is a multi-part message in MIME format.
--=_NextPart_000_0024_01C569A2.12FAEC60
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
ANthonys suggestion of using striped backups to improve restore speed is =a great idea...
And in response to your third question, you will not see an difference =in backup/restore speeds if you upgrade to Enterprise Edition.
-- Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message =news:eSs5dLZaFHA.3184@.TK2MSFTNGP15.phx.gbl...
Also, not only do you have to wait for all data files and transaction =log files to be created and zeroed out, SQL Server will first run a =BACKUP VERIFICATION and you will first have to wait for SQL Server to =read through the backup file first. The file read itself can take =minutes to hours depending on size, disk throughput, and backup file =multiplexing.
If you need fast recovery, you should consider backing up to multiple =files per backup. This will allow you to run parallel read operations =for the verification and actual restore phases; however, the raw =database creation will still be solely dependent on the disk subsystem =throughput.
If you also need fast backup times, consider multiple data files per =filegroup. This will allow SQL Server to run parallel operations for =the backup process.
An alternative to these methods would be to use one of several =3rd-party backup tools. Lightspeed by Imceda would be a good candidate.
Sincerely,
Anthony Thomas
-- "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message =news:eVKp0GXaFHA.2996@.TK2MSFTNGP10.phx.gbl...
Those operations generally do not get helped by parallelism due to =their IO loads.
Getting the fastest disk subsystem, and configuring it correctly, =does more to help than STD/EE editions upgrade.
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/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...
> Thanks!
>
> Yes, it took just around 5 hours to create the database and =currently in
> restoration phase.
>
> About my last question: Have you ever noticed any visible =improvement in > any
> system when license is upgraded from Standard to Enterprise?
>
> Database creation, backup/restoration and index rebuilds are some =of the
> common activities happening on this box.
>
> -- > Regards,
> MZeeshan
>
>
> "Mike Epprecht (SQL MVP)" wrote:
>
>> Hi
>>
>> Fist SQL Server has to create the DB and allocate all the pages =for the >> data
>> and log. This can take the longest.
>>
>> Once this is done, the restore can really happen.
>>
>> A 100GB DB can take anything between 30 minutes and 10 hours to =create,
>> depending on the IO performance of the disk subsystem.
>>
>> 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/
>>
>> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
>> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
>> > Hello-
>> >
>> > I am trying to restore a large sized database (100+ GB) on a =machine >> > that
>> > didn't have database in the beginning, so its a create DB + =restore
>> > operation.
>> >
>> > Below is the machine configuration:
>> >
>> > Machine Configuration: quad processor (Pentium 3 Xeon)
>> > Memory: 2 GB
>> > HDD: Fiber Array Channel with separate controllers.
>> > Version: SQL Server 2000/SP3 (Standard Version)
>> > OS: Windows 2003/SP1 (sp presence is a guess).
>> >
>> > I understand the process will take some time to create database =first
>> > before
>> > starting restoration. So far, its been running for over 2 1/2 =hours
>> > without
>> > any sign of restoration (I have kept STATS=3D1 for getting any =indication >> > of
>> > restoration start).
>> >
>> > Now, I have three(3) questions:
>> >
>> > 1. What should be the approximate DB creation time in such type =of
>> > environment? 3-4 hours'
>> >
>> > 2. How come I know if there is a problem? Currently the restore =process
>> > has
>> > wait type of ASYNC_IO_COMPLETION with very large wait time.
>> >
>> > 3. Can upgrading to Enterprise version help parallelize ANY =workload >> > (here
>> > db creation/restoration) across all processers? OR this is only =true in
>> > some
>> > instances say index rebuild?
>> >
>> > Thanks in advance!!!
>> >
>> > -- >> > Regards,
>> > MZeeshan
>>
>>
>>
--=_NextPart_000_0024_01C569A2.12FAEC60
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
ANthonys suggestion of using striped =backups to improve restore speed is a great idea...
And in response to your third question, =you will not see an difference in backup/restore speeds if you upgrade to =Enterprise Edition.
-- Wayne Snyder, MCDBA, SQL Server MVPMariner, =Charlotte, NChttp://www.mariner-usa.com">www.mariner-usa.com(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it'scommunity of SQL Server professionals.http://www.sqlpass.org">www.sqlpass.org
"Anthony Thomas" wrote in =message news:eSs5dLZaFHA.3184=@.TK2MSFTNGP15.phx.gbl...
Also, not only do you have to wait =for all data files and transaction log files to be created and zeroed out, SQL =Server will first run a BACKUP VERIFICATION and you will first have to wait for =SQL Server to read through the backup file first. The file read itself can =take minutes to hours depending on size, disk throughput, and =backup file multiplexing.
If you need fast recovery, you =should consider backing up to multiple files per backup. This will allow you to =run parallel read operations for the verification and actual restore =phases; however, the raw database creation will still be solely dependent on =the disk subsystem throughput.
If you also need fast backup times, =consider multiple data files per filegroup. This will allow SQL Server to =run parallel operations for the backup process.
An alternative to these methods =would be to use one of several 3rd-party backup tools. Lightspeed =by Imceda would be a good candidate.
Sincerely,
Anthony Thomas
--
"Mike Epprecht (SQL MVP)"
--=_NextPart_000_0024_01C569A2.12FAEC60--
Need URGENT help.
Hello-
I am trying to restore a large sized database (100+ GB) on a machine that
didn't have database in the beginning, so its a create DB + restore operatio
n.
Below is the machine configuration:
Machine Configuration: quad processor (Pentium 3 Xeon)
Memory: 2 GB
HDD: Fiber Array Channel with separate controllers.
Version: SQL Server 2000/SP3 (Standard Version)
OS: Windows 2003/SP1 (sp presence is a guess).
I understand the process will take some time to create database first before
starting restoration. So far, its been running for over 2 1/2 hours without
any sign of restoration (I have kept STATS=1 for getting any indication of
restoration start).
Now, I have three(3) questions:
1. What should be the approximate DB creation time in such type of
environment? 3-4 hours'
2. How come I know if there is a problem? Currently the restore process has
wait type of ASYNC_IO_COMPLETION with very large wait time.
3. Can upgrading to Enterprise version help parallelize ANY workload (here
db creation/restoration) across all processers? OR this is only true in some
instances say index rebuild?
Thanks in advance!!!
Regards,
MZeeshanHi
Fist SQL Server has to create the DB and allocate all the pages for the data
and log. This can take the longest.
Once this is done, the restore can really happen.
A 100GB DB can take anything between 30 minutes and 10 hours to create,
depending on the IO performance of the disk subsystem.
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/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
> Hello-
> I am trying to restore a large sized database (100+ GB) on a machine that
> didn't have database in the beginning, so its a create DB + restore
> operation.
> Below is the machine configuration:
> Machine Configuration: quad processor (Pentium 3 Xeon)
> Memory: 2 GB
> HDD: Fiber Array Channel with separate controllers.
> Version: SQL Server 2000/SP3 (Standard Version)
> OS: Windows 2003/SP1 (sp presence is a guess).
> I understand the process will take some time to create database first
> before
> starting restoration. So far, its been running for over 2 1/2 hours
> without
> any sign of restoration (I have kept STATS=1 for getting any indication of
> restoration start).
> Now, I have three(3) questions:
> 1. What should be the approximate DB creation time in such type of
> environment? 3-4 hours'
> 2. How come I know if there is a problem? Currently the restore process
> has
> wait type of ASYNC_IO_COMPLETION with very large wait time.
> 3. Can upgrading to Enterprise version help parallelize ANY workload (here
> db creation/restoration) across all processers? OR this is only true in
> some
> instances say index rebuild?
> Thanks in advance!!!
> --
> Regards,
> MZeeshan|||Thanks!
Yes, it took just around 5 hours to create the database and currently in
restoration phase.
About my last question: Have you ever noticed any visible improvement in any
system when license is upgraded from Standard to Enterprise?
Database creation, backup/restoration and index rebuilds are some of the
common activities happening on this box.
Regards,
MZeeshan
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Fist SQL Server has to create the DB and allocate all the pages for the da
ta
> and log. This can take the longest.
> Once this is done, the restore can really happen.
> A 100GB DB can take anything between 30 minutes and 10 hours to create,
> depending on the IO performance of the disk subsystem.
> 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/
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
>
>|||Those operations generally do not get helped by parallelism due to their IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does more
to help than STD/EE editions upgrade.
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/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...[vbcol=seagreen]
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
>|||Also, not only do you have to wait for all data files and transaction log
files to be created and zeroed out, SQL Server will first run a BACKUP
VERIFICATION and you will first have to wait for SQL Server to read through
the backup file first. The file read itself can take minutes to hours
depending on size, disk throughput, and backup file multiplexing.
If you need fast recovery, you should consider backing up to multiple files
per backup. This will allow you to run parallel read operations for the
verification and actual restore phases; however, the raw database creation
will still be solely dependent on the disk subsystem throughput.
If you also need fast backup times, consider multiple data files per
filegroup. This will allow SQL Server to run parallel operations for the
backup process.
An alternative to these methods would be to use one of several 3rd-party
backup tools. Lightspeed by Imceda would be a good candidate.
Sincerely,
Anthony Thomas
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eVKp0GXaFHA.2996@.TK2MSFTNGP10.phx.gbl...
Those operations generally do not get helped by parallelism due to their
IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does
more
to help than STD/EE editions upgrade.
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/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...[vbcol=seagreen]
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
>
indication[vbcol=seagreen]
process[vbcol=seagreen]
in[vbcol=seagreen]|||ANthonys suggestion of using striped backups to improve restore speed is a g
reat idea...
And in response to your third question, you will not see an difference in ba
ckup/restore speeds if you upgrade to Enterprise Edition.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message news:eSs5dLZaFHA.3184
@.TK2MSFTNGP15.phx.gbl...
Also, not only do you have to wait for all data files and transaction log fi
les to be created and zeroed out, SQL Server will first run a BACKUP VERIFIC
ATION and you will first have to wait for SQL Server to read through the bac
kup file first. The file read itself can take minutes to hours depending on
size, disk throughput, and backup file multiplexing.
If you need fast recovery, you should consider backing up to multiple files
per backup. This will allow you to run parallel read operations for the ver
ification and actual restore phases; however, the raw database creation will
still be solely dependent on the disk subsystem throughput.
If you also need fast backup times, consider multiple data files per filegro
up. This will allow SQL Server to run parallel operations for the backup pr
ocess.
An alternative to these methods would be to use one of several 3rd-party bac
kup tools. Lightspeed by Imceda would be a good candidate.
Sincerely,
Anthony Thomas
--
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message news:eVKp0GXa
FHA.2996@.TK2MSFTNGP10.phx.gbl...
Those operations generally do not get helped by parallelism due to their IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does more
to help than STD/EE editions upgrade.
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/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...[vbcol=seagreen]
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
>
I am trying to restore a large sized database (100+ GB) on a machine that
didn't have database in the beginning, so its a create DB + restore operatio
n.
Below is the machine configuration:
Machine Configuration: quad processor (Pentium 3 Xeon)
Memory: 2 GB
HDD: Fiber Array Channel with separate controllers.
Version: SQL Server 2000/SP3 (Standard Version)
OS: Windows 2003/SP1 (sp presence is a guess).
I understand the process will take some time to create database first before
starting restoration. So far, its been running for over 2 1/2 hours without
any sign of restoration (I have kept STATS=1 for getting any indication of
restoration start).
Now, I have three(3) questions:
1. What should be the approximate DB creation time in such type of
environment? 3-4 hours'
2. How come I know if there is a problem? Currently the restore process has
wait type of ASYNC_IO_COMPLETION with very large wait time.
3. Can upgrading to Enterprise version help parallelize ANY workload (here
db creation/restoration) across all processers? OR this is only true in some
instances say index rebuild?
Thanks in advance!!!
Regards,
MZeeshanHi
Fist SQL Server has to create the DB and allocate all the pages for the data
and log. This can take the longest.
Once this is done, the restore can really happen.
A 100GB DB can take anything between 30 minutes and 10 hours to create,
depending on the IO performance of the disk subsystem.
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/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
> Hello-
> I am trying to restore a large sized database (100+ GB) on a machine that
> didn't have database in the beginning, so its a create DB + restore
> operation.
> Below is the machine configuration:
> Machine Configuration: quad processor (Pentium 3 Xeon)
> Memory: 2 GB
> HDD: Fiber Array Channel with separate controllers.
> Version: SQL Server 2000/SP3 (Standard Version)
> OS: Windows 2003/SP1 (sp presence is a guess).
> I understand the process will take some time to create database first
> before
> starting restoration. So far, its been running for over 2 1/2 hours
> without
> any sign of restoration (I have kept STATS=1 for getting any indication of
> restoration start).
> Now, I have three(3) questions:
> 1. What should be the approximate DB creation time in such type of
> environment? 3-4 hours'
> 2. How come I know if there is a problem? Currently the restore process
> has
> wait type of ASYNC_IO_COMPLETION with very large wait time.
> 3. Can upgrading to Enterprise version help parallelize ANY workload (here
> db creation/restoration) across all processers? OR this is only true in
> some
> instances say index rebuild?
> Thanks in advance!!!
> --
> Regards,
> MZeeshan|||Thanks!
Yes, it took just around 5 hours to create the database and currently in
restoration phase.
About my last question: Have you ever noticed any visible improvement in any
system when license is upgraded from Standard to Enterprise?
Database creation, backup/restoration and index rebuilds are some of the
common activities happening on this box.
Regards,
MZeeshan
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Fist SQL Server has to create the DB and allocate all the pages for the da
ta
> and log. This can take the longest.
> Once this is done, the restore can really happen.
> A 100GB DB can take anything between 30 minutes and 10 hours to create,
> depending on the IO performance of the disk subsystem.
> 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/
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
>
>|||Those operations generally do not get helped by parallelism due to their IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does more
to help than STD/EE editions upgrade.
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/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...[vbcol=seagreen]
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
>|||Also, not only do you have to wait for all data files and transaction log
files to be created and zeroed out, SQL Server will first run a BACKUP
VERIFICATION and you will first have to wait for SQL Server to read through
the backup file first. The file read itself can take minutes to hours
depending on size, disk throughput, and backup file multiplexing.
If you need fast recovery, you should consider backing up to multiple files
per backup. This will allow you to run parallel read operations for the
verification and actual restore phases; however, the raw database creation
will still be solely dependent on the disk subsystem throughput.
If you also need fast backup times, consider multiple data files per
filegroup. This will allow SQL Server to run parallel operations for the
backup process.
An alternative to these methods would be to use one of several 3rd-party
backup tools. Lightspeed by Imceda would be a good candidate.
Sincerely,
Anthony Thomas
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eVKp0GXaFHA.2996@.TK2MSFTNGP10.phx.gbl...
Those operations generally do not get helped by parallelism due to their
IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does
more
to help than STD/EE editions upgrade.
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/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...[vbcol=seagreen]
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
>
indication[vbcol=seagreen]
process[vbcol=seagreen]
in[vbcol=seagreen]|||ANthonys suggestion of using striped backups to improve restore speed is a g
reat idea...
And in response to your third question, you will not see an difference in ba
ckup/restore speeds if you upgrade to Enterprise Edition.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message news:eSs5dLZaFHA.3184
@.TK2MSFTNGP15.phx.gbl...
Also, not only do you have to wait for all data files and transaction log fi
les to be created and zeroed out, SQL Server will first run a BACKUP VERIFIC
ATION and you will first have to wait for SQL Server to read through the bac
kup file first. The file read itself can take minutes to hours depending on
size, disk throughput, and backup file multiplexing.
If you need fast recovery, you should consider backing up to multiple files
per backup. This will allow you to run parallel read operations for the ver
ification and actual restore phases; however, the raw database creation will
still be solely dependent on the disk subsystem throughput.
If you also need fast backup times, consider multiple data files per filegro
up. This will allow SQL Server to run parallel operations for the backup pr
ocess.
An alternative to these methods would be to use one of several 3rd-party bac
kup tools. Lightspeed by Imceda would be a good candidate.
Sincerely,
Anthony Thomas
--
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message news:eVKp0GXa
FHA.2996@.TK2MSFTNGP10.phx.gbl...
Those operations generally do not get helped by parallelism due to their IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does more
to help than STD/EE editions upgrade.
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/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...[vbcol=seagreen]
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
>
Need URGENT help.
Hello-
I am trying to restore a large sized database (100+ GB) on a machine that
didn't have database in the beginning, so its a create DB + restore operation.
Below is the machine configuration:
Machine Configuration: quad processor (Pentium 3 Xeon)
Memory: 2 GB
HDD: Fiber Array Channel with separate controllers.
Version: SQL Server 2000/SP3 (Standard Version)
OS: Windows 2003/SP1 (sp presence is a guess).
I understand the process will take some time to create database first before
starting restoration. So far, its been running for over 2 1/2 hours without
any sign of restoration (I have kept STATS=1 for getting any indication of
restoration start).
Now, I have three(3) questions:
1. What should be the approximate DB creation time in such type of
environment? 3-4 hours?
2. How come I know if there is a problem? Currently the restore process has
wait type of ASYNC_IO_COMPLETION with very large wait time.
3. Can upgrading to Enterprise version help parallelize ANY workload (here
db creation/restoration) across all processers? OR this is only true in some
instances say index rebuild?
Thanks in advance!!!
Regards,
MZeeshan
Hi
Fist SQL Server has to create the DB and allocate all the pages for the data
and log. This can take the longest.
Once this is done, the restore can really happen.
A 100GB DB can take anything between 30 minutes and 10 hours to create,
depending on the IO performance of the disk subsystem.
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/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
> Hello-
> I am trying to restore a large sized database (100+ GB) on a machine that
> didn't have database in the beginning, so its a create DB + restore
> operation.
> Below is the machine configuration:
> Machine Configuration: quad processor (Pentium 3 Xeon)
> Memory: 2 GB
> HDD: Fiber Array Channel with separate controllers.
> Version: SQL Server 2000/SP3 (Standard Version)
> OS: Windows 2003/SP1 (sp presence is a guess).
> I understand the process will take some time to create database first
> before
> starting restoration. So far, its been running for over 2 1/2 hours
> without
> any sign of restoration (I have kept STATS=1 for getting any indication of
> restoration start).
> Now, I have three(3) questions:
> 1. What should be the approximate DB creation time in such type of
> environment? 3-4 hours?
> 2. How come I know if there is a problem? Currently the restore process
> has
> wait type of ASYNC_IO_COMPLETION with very large wait time.
> 3. Can upgrading to Enterprise version help parallelize ANY workload (here
> db creation/restoration) across all processers? OR this is only true in
> some
> instances say index rebuild?
> Thanks in advance!!!
> --
> Regards,
> MZeeshan
|||Thanks!
Yes, it took just around 5 hours to create the database and currently in
restoration phase.
About my last question: Have you ever noticed any visible improvement in any
system when license is upgraded from Standard to Enterprise?
Database creation, backup/restoration and index rebuilds are some of the
common activities happening on this box.
Regards,
MZeeshan
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Fist SQL Server has to create the DB and allocate all the pages for the data
> and log. This can take the longest.
> Once this is done, the restore can really happen.
> A 100GB DB can take anything between 30 minutes and 10 hours to create,
> depending on the IO performance of the disk subsystem.
> 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/
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
>
>
|||Those operations generally do not get helped by parallelism due to their IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does more
to help than STD/EE editions upgrade.
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/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...[vbcol=seagreen]
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
|||Also, not only do you have to wait for all data files and transaction log
files to be created and zeroed out, SQL Server will first run a BACKUP
VERIFICATION and you will first have to wait for SQL Server to read through
the backup file first. The file read itself can take minutes to hours
depending on size, disk throughput, and backup file multiplexing.
If you need fast recovery, you should consider backing up to multiple files
per backup. This will allow you to run parallel read operations for the
verification and actual restore phases; however, the raw database creation
will still be solely dependent on the disk subsystem throughput.
If you also need fast backup times, consider multiple data files per
filegroup. This will allow SQL Server to run parallel operations for the
backup process.
An alternative to these methods would be to use one of several 3rd-party
backup tools. Lightspeed by Imceda would be a good candidate.
Sincerely,
Anthony Thomas
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eVKp0GXaFHA.2996@.TK2MSFTNGP10.phx.gbl...
Those operations generally do not get helped by parallelism due to their
IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does
more
to help than STD/EE editions upgrade.
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/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...[vbcol=seagreen]
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
indication[vbcol=seagreen]
process[vbcol=seagreen]
in[vbcol=seagreen]
|||ANthonys suggestion of using striped backups to improve restore speed is a great idea...
And in response to your third question, you will not see an difference in backup/restore speeds if you upgrade to Enterprise Edition.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message news:eSs5dLZaFHA.3184@.TK2MSFTNGP15.phx.gbl...
Also, not only do you have to wait for all data files and transaction log files to be created and zeroed out, SQL Server will first run a BACKUP VERIFICATION and you will first have to wait for SQL Server to read through the backup file first. The file read itself can take minutes to hours depending on size, disk throughput, and backup file multiplexing.
If you need fast recovery, you should consider backing up to multiple files per backup. This will allow you to run parallel read operations for the verification and actual restore phases; however, the raw database creation will still be solely dependent on the disk subsystem throughput.
If you also need fast backup times, consider multiple data files per filegroup. This will allow SQL Server to run parallel operations for the backup process.
An alternative to these methods would be to use one of several 3rd-party backup tools. Lightspeed by Imceda would be a good candidate.
Sincerely,
Anthony Thomas
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message news:eVKp0GXaFHA.2996@.TK2MSFTNGP10.phx.gbl...
Those operations generally do not get helped by parallelism due to their IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does more
to help than STD/EE editions upgrade.
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/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...[vbcol=seagreen]
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
I am trying to restore a large sized database (100+ GB) on a machine that
didn't have database in the beginning, so its a create DB + restore operation.
Below is the machine configuration:
Machine Configuration: quad processor (Pentium 3 Xeon)
Memory: 2 GB
HDD: Fiber Array Channel with separate controllers.
Version: SQL Server 2000/SP3 (Standard Version)
OS: Windows 2003/SP1 (sp presence is a guess).
I understand the process will take some time to create database first before
starting restoration. So far, its been running for over 2 1/2 hours without
any sign of restoration (I have kept STATS=1 for getting any indication of
restoration start).
Now, I have three(3) questions:
1. What should be the approximate DB creation time in such type of
environment? 3-4 hours?
2. How come I know if there is a problem? Currently the restore process has
wait type of ASYNC_IO_COMPLETION with very large wait time.
3. Can upgrading to Enterprise version help parallelize ANY workload (here
db creation/restoration) across all processers? OR this is only true in some
instances say index rebuild?
Thanks in advance!!!
Regards,
MZeeshan
Hi
Fist SQL Server has to create the DB and allocate all the pages for the data
and log. This can take the longest.
Once this is done, the restore can really happen.
A 100GB DB can take anything between 30 minutes and 10 hours to create,
depending on the IO performance of the disk subsystem.
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/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
> Hello-
> I am trying to restore a large sized database (100+ GB) on a machine that
> didn't have database in the beginning, so its a create DB + restore
> operation.
> Below is the machine configuration:
> Machine Configuration: quad processor (Pentium 3 Xeon)
> Memory: 2 GB
> HDD: Fiber Array Channel with separate controllers.
> Version: SQL Server 2000/SP3 (Standard Version)
> OS: Windows 2003/SP1 (sp presence is a guess).
> I understand the process will take some time to create database first
> before
> starting restoration. So far, its been running for over 2 1/2 hours
> without
> any sign of restoration (I have kept STATS=1 for getting any indication of
> restoration start).
> Now, I have three(3) questions:
> 1. What should be the approximate DB creation time in such type of
> environment? 3-4 hours?
> 2. How come I know if there is a problem? Currently the restore process
> has
> wait type of ASYNC_IO_COMPLETION with very large wait time.
> 3. Can upgrading to Enterprise version help parallelize ANY workload (here
> db creation/restoration) across all processers? OR this is only true in
> some
> instances say index rebuild?
> Thanks in advance!!!
> --
> Regards,
> MZeeshan
|||Thanks!
Yes, it took just around 5 hours to create the database and currently in
restoration phase.
About my last question: Have you ever noticed any visible improvement in any
system when license is upgraded from Standard to Enterprise?
Database creation, backup/restoration and index rebuilds are some of the
common activities happening on this box.
Regards,
MZeeshan
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Fist SQL Server has to create the DB and allocate all the pages for the data
> and log. This can take the longest.
> Once this is done, the restore can really happen.
> A 100GB DB can take anything between 30 minutes and 10 hours to create,
> depending on the IO performance of the disk subsystem.
> 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/
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
>
>
|||Those operations generally do not get helped by parallelism due to their IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does more
to help than STD/EE editions upgrade.
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/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...[vbcol=seagreen]
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
|||Also, not only do you have to wait for all data files and transaction log
files to be created and zeroed out, SQL Server will first run a BACKUP
VERIFICATION and you will first have to wait for SQL Server to read through
the backup file first. The file read itself can take minutes to hours
depending on size, disk throughput, and backup file multiplexing.
If you need fast recovery, you should consider backing up to multiple files
per backup. This will allow you to run parallel read operations for the
verification and actual restore phases; however, the raw database creation
will still be solely dependent on the disk subsystem throughput.
If you also need fast backup times, consider multiple data files per
filegroup. This will allow SQL Server to run parallel operations for the
backup process.
An alternative to these methods would be to use one of several 3rd-party
backup tools. Lightspeed by Imceda would be a good candidate.
Sincerely,
Anthony Thomas
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eVKp0GXaFHA.2996@.TK2MSFTNGP10.phx.gbl...
Those operations generally do not get helped by parallelism due to their
IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does
more
to help than STD/EE editions upgrade.
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/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...[vbcol=seagreen]
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
indication[vbcol=seagreen]
process[vbcol=seagreen]
in[vbcol=seagreen]
|||ANthonys suggestion of using striped backups to improve restore speed is a great idea...
And in response to your third question, you will not see an difference in backup/restore speeds if you upgrade to Enterprise Edition.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message news:eSs5dLZaFHA.3184@.TK2MSFTNGP15.phx.gbl...
Also, not only do you have to wait for all data files and transaction log files to be created and zeroed out, SQL Server will first run a BACKUP VERIFICATION and you will first have to wait for SQL Server to read through the backup file first. The file read itself can take minutes to hours depending on size, disk throughput, and backup file multiplexing.
If you need fast recovery, you should consider backing up to multiple files per backup. This will allow you to run parallel read operations for the verification and actual restore phases; however, the raw database creation will still be solely dependent on the disk subsystem throughput.
If you also need fast backup times, consider multiple data files per filegroup. This will allow SQL Server to run parallel operations for the backup process.
An alternative to these methods would be to use one of several 3rd-party backup tools. Lightspeed by Imceda would be a good candidate.
Sincerely,
Anthony Thomas
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message news:eVKp0GXaFHA.2996@.TK2MSFTNGP10.phx.gbl...
Those operations generally do not get helped by parallelism due to their IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does more
to help than STD/EE editions upgrade.
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/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...[vbcol=seagreen]
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
Need urgent help, this is driving me crazy. SSRS 2000
Hi all,
I am trying to build a reportviewer so that I could use reporting services (due to company's security policy).
I am getting a VERY weird error here.
Before I get on ahead, here is the scenario.
I had created a rdl that will give a certain report based on two parameters. At the moment, I didn't assign the parameters to any default values since i don't want the report to start running. These two parameters are run based on stored procedures to get the starting date and an ending date.
I had tried to view this report on Report Manager and it works.
I had set the parameters this way. NOTE: ReportingServer is the disgused name of the reportserver I am using for privacy purposes
Code Snippet
Dim rs As New ReportingServer.ReportingService
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
'Render arguments
Dim results() As Byte
Dim format As String = "HTML4.0"
Dim historyID As String = Nothing
'PDF Rendering extensions don't have any useful DeviceInfo settings
'the toolbar shown below only affects HTML rendering
Dim devInfo As String = "<DeviceInfo><HTMLFragment>True</HTMLFragment><JavaScript>True</JavaScript><Toolbar>True</Toolbar><Parameters>True</Parameters></DeviceInfo>"
Dim credentials() As ReportingServer.DataSourceCredentials = New ReportingServer.DataSourceCredentials() {}
Dim showHideToggle As String = ""
Dim encoding As String = ""
Dim mimeType As String = ""
Dim warnings() As ReportingServer.Warning = New ReportingServer.Warning(0) {}
Dim reportHistoryParameters() As ReportingServer.ParameterValue = New ReportingServer.ParameterValue() {}
Dim streamIDs() As String = Nothing
Dim sh As ReportingServer.SessionHeader = New ReportingServer.SessionHeader
rs.SessionHeaderValue = sh
But when I started building this reportviewer.aspx, it gives me this error on the following line:-
Code Snippet
results = rs.Render(ReportPath, format, historyID, devInfo, Parameters, credentials, _
showHideToggle, encoding, mimeType, reportHistoryParameters, warnings, streamIDs)
with the error
{"This report requires a default or user-defined value for the report parameter 'start_date'. To run or subscribe to this report, you must provide a parameter value. --> This report requires a default or user-defined value for the report parameter 'start_date'. To run or subscribe to this report, you must provide a parameter value. --> This report requires a default or user-defined value for the report parameter 'start_date'. To run or subscribe to this report, you must provide a parameter value."}
this is occuring on the line where it needs to render the report.
As simple as it is, i tried the following:-
NOTE: some variables has been changed to protect some identity.
Code Snippet
Dim ReportRenderer As New RenderSQLReports
Dim Parameters As int.rrd.sqlreporting_test.ParameterValue() = Nothing
ReportRenderer.RenderReport(Parameters, "/Folder/reportname", "pdffilename.pdf")
What I don't get is that i made sure that it doesn't have any default values.
So I went into this scenario and tried teh following:-
Code Snippet
Dim ReportRenderer As New RenderSQLReports
Dim Parameters() As Reportingservices.ParameterValue = New Reportingservices.ParameterValue(1) {}
Parameters(0) = New Reportingservices.ParameterValue
Parameters(0).Label = "start_date"
Parameters(0).Name = "start_date"
Parameters(0).Value = "10/1/1999 12:00:00 AM"
Parameters(1) = New Reportingservices.ParameterValue
Parameters(1).Label = "end_date"
Parameters(1).Name = "end_date"
Parameters(1).Value = "11/1/1999 12:00:00 AM"
ReportRenderer.RenderReport(Parameters, "/Folder/reportname", "pdffilename.pdf")
And this is what happens. It blows up on this line again. with this error.
Code Snippet
results = rs.Render(ReportPath, format, historyID, devInfo, Parameters, credentials, _
showHideToggle, encoding, mimeType, reportHistoryParameters, warnings, streamIDs)
With the errors
{"Default value or value provided for the report parameter 'start_date' is not a valid value. --> Default value or value provided for the report parameter 'start_date' is not a valid value. --> Default value or value provided for the report parameter 'start_date' is not a valid value."}
This is probably occuring because the date I had provided is not found in the list of dates. I really don't want to default a date at the moment, to allow the user to select a date.
So I finally gave it a date...... a valid date.
Code Snippet
Dim ReportRenderer As New RenderSQLReports
Dim Parameters() As Reportingservices.ParameterValue = New Reportingservices.ParameterValue(1) {}
Parameters(0) = New Reportingservices.ParameterValue
Parameters(0).Label = "start_date"
Parameters(0).Name = "start_date"
Parameters(0).Value = "10/1/2004 12:00:00 AM"
Parameters(1) = New Reportingservices.ParameterValue
Parameters(1).Label = "end_date"
Parameters(1).Name = "end_date"
Parameters(1).Value = "11/1/2004 12:00:00 AM"
ReportRenderer.RenderReport(Parameters, "/Folder/reportname", "pdffilename.pdf")
LO and behold, it works BUT . now i get this error in the aspx code.
Runtime error has occured.
Microsoft JScript runtime error: 'Report' is undefined
on this weird line:- (that was generated automatically)
</script><script language="javascript" type="text/javascript" src="?rs:Command=Get&rc:GetImage=8.00.1038.00Report.js">
</script><script language="javascript" type="text/javascript">
<!--
function OnLoadReport()
{
var pageHits = null;
var rep = new Report(1, 16, pageHits, false, docMapIds); <-where error is occuring.
if (parent != self) parent.OnLoadReport(rep);
}
//-->
</script>
It did show the report, but I don't understand why it is still getting an error here. Moreover, I didn't even get the HTMLViewer too even when I had assigned the devinfo....
Really need some help here, as this is quite urgent.... I am at a loss at this, and maybe that there is an option that I am not seeing or something I am missing. please help .
Bernard
-So close yet so far, How cruel are the software bugs-alright ..
I am solving this bit by bit, but the most important is that I want to get the html report with the Toolbar and parameters to show.
So far I am setting up the default values at the moment, and so far it is rendering the report.
How do I show te Toolbar (HtmlVIEwer) and the Parameters section to show on html ?
I Tried the following in the Devinfo
Dim devInfo As String = "<DeviceInfo><Toolbar>True</Toolbar></DeviceInfo>"
And I got the following error in the Aspx code.
Microsoft JScript runtime error: 'Report' is undefined
on this weird line:- (that was generated automatically)
<script language="javascript" type="text/javascript" src="?rs:Command=Get&rc:GetImage=8.00.1038.00Report.js">
</script><script language="javascript" type="text/javascript">
<!--
function OnLoadReport()
{
var pageHits = null;
var rep = new Report(1, 16, pageHits, false, docMapIds); <-where error is occuring.
if (parent != self) parent.OnLoadReport(rep);
}
//-->
</script>
if anybody could tell me what i need to do, that will be really useful and helpful.
Thanks !
Bernard
-So close yet so far, How cruel are the software bugs-
|||
Btong wrote:
alright ..
I am solving this bit by bit, but the most important is that I want to get the html report with the Toolbar and parameters to show.
So far I am setting up the default values at the moment, and so far it is rendering the report.
How do I show te Toolbar (HtmlVIEwer) and the Parameters section to show on html ?
I Tried the following in the Devinfo
Dim devInfo As String = "<DeviceInfo><Toolbar>True</Toolbar></DeviceInfo>"
And I got the following error in the Aspx code.
Microsoft JScript runtime error: 'Report' is undefinedon this weird line:- (that was generated automatically)
<script language="javascript" type="text/javascript" src="?rs:Command=Get&rc:GetImage=8.00.1038.00Report.js">
</script><script language="javascript" type="text/javascript">
<!--
function OnLoadReport()
{
var pageHits = null;
var rep = new Report(1, 16, pageHits, false, docMapIds); <-where error is occuring.
if (parent != self) parent.OnLoadReport(rep);
}
//-->
</script>
if anybody could tell me what i need to do, that will be really useful and helpful.
Thanks !
Bernard
-So close yet so far, How cruel are the software bugs-
Alright Folks.
Got some good news, Managed to build my own HTMLViewer and ReportViewer with some available source code. I am able to set up the HTML for HTMLViewer and ReportViewer. However, in the course of doing so, I came across a very good question.
I had tried to set up the ASMX and use the property "Parameters" and "Toolbar" to set up the HTMLViewer but to no avail.
Setting "<Parameter>True</Parameter>" in the Devinfo string did not make the Parameters section appear in the HTML.
Setting "<Toolbar>True</Toolbar>" in the devinfo string caused a javascript error as described below:-
Microsoft JScript runtime error: 'Report' is undefined
this occured in :-
<script language="javascript" type="text/javascript" src="?rs:Command=Get&rc:GetImage=8.00.1038.00Report.js">
</script><script language="javascript" type="text/javascript">
<!--
function OnLoadReport()
{
var pageHits = null;
var rep = new Report(1, 16, pageHits, false, docMapIds); <-where error is occuring.
if (parent != self) parent.OnLoadReport(rep);
}
//-->
</script>
If I set these parameters to false, My report does generate in html.
I am wondering whether is this a bug? if this is so, is there a hotfix for it ? Or am I missing something crucial at this point ?
Can someone confirm this ? Thanks !
Bernard
-So close yet so far, How cruel are the software bugs-
|||hi All,
Just had a very interesting conversation with one of the SSRS coders/creators and I am very enlightened in this situation.
I had just confirmed the following:-
1) This is not a bug per say. It was never considered that the rendering the report through API will let you set up the PArameters or the toolbar section.
2) the devinfo is really use for the URL access for the reports.
3) If rendering the reports through API, you have to either set almost all properties to False OR set a null in the "<devinfo></devinfo>" in the devinfo string or it won't render. (getting the javascript error as described as above)
4) This devinfo only works if you are on the Report Manager page and won't work if you are trying to view the htmlviewer in another browser.
There are probably a lot of SSRS developers who may wonder "Why go through all the trouble trying to figure this out" OR "so what?"
Well I could see a couple of scenarios here.
1) Clients have full access to Report Manager. (hence the URL access)
2) Developers have to come up with some way to retrieve the parameters, render the report once. (this prevents the clients from accessing the report manager)
Now I could have gone through the second route, but I figured that maybe I could use the HTMLViewer option you would see in the Report Manager without having to expose the URL directly to the clients. (This is due to Company's policy, firewall issues as well as security issues and sensitive data issues).
I didn't want to build a dozen "middlemen" pages just to collect the parameters and then throw it to the rdl via asmx. (waste of time modifying all of them and really unsensible too).
In the end, I managed to build a class that will help in this situation. Not exposing the url directly when a user tries to view the source, and no one has to know where or what the url is, my ReportViewer page prevents that.
I thought that by using the devinfo through ASMX would provide some sort of Report Manager capability without exposing the direct URL.
No one has yet to ask if there is ever a fix that would allow the devinfo string to be utilized via ASMX (it seems that I was the first one to bring this up). So hopefully this may or may not provide some sort of closure to anyone who might be in the same situation as I am.
Thanks for reading this post !
Need Urgent Help!
I imported xl spreadsheet data into sql server. Some data is converted with
<NULL> value even though the data exists in the xl spreadsheet column.
The funny part is it is not <NULL> for the entire column. For eg. I have a
column called 'Part Number' , some rows have the correct 'Part Number'
whereas others have the 'NULL' value, even though the Part Number is not null.
This happens for a few columns.
I already tried converting problem column formatting to text or numeric, but
it didn't make any difference.
Does someone have any idea why it must be happening and how I can import all
data.
Thank you in advance,
-Me
http://www.sqldts.com/default.aspx?254
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Me" <Me@.discussions.microsoft.com> wrote in message
news:B059ABB1-DF2B-44AE-BAC8-52343F72EDB7@.microsoft.com...
>I imported xl spreadsheet data into sql server. Some data is converted with
> <NULL> value even though the data exists in the xl spreadsheet column.
> The funny part is it is not <NULL> for the entire column. For eg. I have a
> column called 'Part Number' , some rows have the correct 'Part Number'
> whereas others have the 'NULL' value, even though the Part Number is not
> null.
> This happens for a few columns.
> I already tried converting problem column formatting to text or numeric,
> but
> it didn't make any difference.
> Does someone have any idea why it must be happening and how I can import
> all
> data.
> Thank you in advance,
> -Me
>
<NULL> value even though the data exists in the xl spreadsheet column.
The funny part is it is not <NULL> for the entire column. For eg. I have a
column called 'Part Number' , some rows have the correct 'Part Number'
whereas others have the 'NULL' value, even though the Part Number is not null.
This happens for a few columns.
I already tried converting problem column formatting to text or numeric, but
it didn't make any difference.
Does someone have any idea why it must be happening and how I can import all
data.
Thank you in advance,
-Me
http://www.sqldts.com/default.aspx?254
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Me" <Me@.discussions.microsoft.com> wrote in message
news:B059ABB1-DF2B-44AE-BAC8-52343F72EDB7@.microsoft.com...
>I imported xl spreadsheet data into sql server. Some data is converted with
> <NULL> value even though the data exists in the xl spreadsheet column.
> The funny part is it is not <NULL> for the entire column. For eg. I have a
> column called 'Part Number' , some rows have the correct 'Part Number'
> whereas others have the 'NULL' value, even though the Part Number is not
> null.
> This happens for a few columns.
> I already tried converting problem column formatting to text or numeric,
> but
> it didn't make any difference.
> Does someone have any idea why it must be happening and how I can import
> all
> data.
> Thank you in advance,
> -Me
>
Need Urgent Help!
I imported xl spreadsheet data into sql server. Some data is converted with
<NULL> value even though the data exists in the xl spreadsheet column.
The funny part is it is not <NULL> for the entire column. For eg. I have a
column called 'Part Number' , some rows have the correct 'Part Number'
whereas others have the 'NULL' value, even though the Part Number is not nul
l.
This happens for a few columns.
I already tried converting problem column formatting to text or numeric, but
it didn't make any difference.
Does someone have any idea why it must be happening and how I can import all
data.
Thank you in advance,
-Mehttp://www.sqldts.com/default.aspx?254
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Me" <Me@.discussions.microsoft.com> wrote in message
news:B059ABB1-DF2B-44AE-BAC8-52343F72EDB7@.microsoft.com...
>I imported xl spreadsheet data into sql server. Some data is converted with
> <NULL> value even though the data exists in the xl spreadsheet column.
> The funny part is it is not <NULL> for the entire column. For eg. I have a
> column called 'Part Number' , some rows have the correct 'Part Number'
> whereas others have the 'NULL' value, even though the Part Number is not
> null.
> This happens for a few columns.
> I already tried converting problem column formatting to text or numeric,
> but
> it didn't make any difference.
> Does someone have any idea why it must be happening and how I can import
> all
> data.
> Thank you in advance,
> -Me
>
<NULL> value even though the data exists in the xl spreadsheet column.
The funny part is it is not <NULL> for the entire column. For eg. I have a
column called 'Part Number' , some rows have the correct 'Part Number'
whereas others have the 'NULL' value, even though the Part Number is not nul
l.
This happens for a few columns.
I already tried converting problem column formatting to text or numeric, but
it didn't make any difference.
Does someone have any idea why it must be happening and how I can import all
data.
Thank you in advance,
-Mehttp://www.sqldts.com/default.aspx?254
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Me" <Me@.discussions.microsoft.com> wrote in message
news:B059ABB1-DF2B-44AE-BAC8-52343F72EDB7@.microsoft.com...
>I imported xl spreadsheet data into sql server. Some data is converted with
> <NULL> value even though the data exists in the xl spreadsheet column.
> The funny part is it is not <NULL> for the entire column. For eg. I have a
> column called 'Part Number' , some rows have the correct 'Part Number'
> whereas others have the 'NULL' value, even though the Part Number is not
> null.
> This happens for a few columns.
> I already tried converting problem column formatting to text or numeric,
> but
> it didn't make any difference.
> Does someone have any idea why it must be happening and how I can import
> all
> data.
> Thank you in advance,
> -Me
>
Need Urgent Help!
I imported xl spreadsheet data into sql server. Some data is converted with
<NULL> value even though the data exists in the xl spreadsheet column.
The funny part is it is not <NULL> for the entire column. For eg. I have a
column called 'Part Number' , some rows have the correct 'Part Number'
whereas others have the 'NULL' value, even though the Part Number is not null.
This happens for a few columns.
I already tried converting problem column formatting to text or numeric, but
it didn't make any difference.
Does someone have any idea why it must be happening and how I can import all
data.
Thank you in advance,
-Mehttp://www.sqldts.com/default.aspx?254
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Me" <Me@.discussions.microsoft.com> wrote in message
news:B059ABB1-DF2B-44AE-BAC8-52343F72EDB7@.microsoft.com...
>I imported xl spreadsheet data into sql server. Some data is converted with
> <NULL> value even though the data exists in the xl spreadsheet column.
> The funny part is it is not <NULL> for the entire column. For eg. I have a
> column called 'Part Number' , some rows have the correct 'Part Number'
> whereas others have the 'NULL' value, even though the Part Number is not
> null.
> This happens for a few columns.
> I already tried converting problem column formatting to text or numeric,
> but
> it didn't make any difference.
> Does someone have any idea why it must be happening and how I can import
> all
> data.
> Thank you in advance,
> -Me
>
<NULL> value even though the data exists in the xl spreadsheet column.
The funny part is it is not <NULL> for the entire column. For eg. I have a
column called 'Part Number' , some rows have the correct 'Part Number'
whereas others have the 'NULL' value, even though the Part Number is not null.
This happens for a few columns.
I already tried converting problem column formatting to text or numeric, but
it didn't make any difference.
Does someone have any idea why it must be happening and how I can import all
data.
Thank you in advance,
-Mehttp://www.sqldts.com/default.aspx?254
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Me" <Me@.discussions.microsoft.com> wrote in message
news:B059ABB1-DF2B-44AE-BAC8-52343F72EDB7@.microsoft.com...
>I imported xl spreadsheet data into sql server. Some data is converted with
> <NULL> value even though the data exists in the xl spreadsheet column.
> The funny part is it is not <NULL> for the entire column. For eg. I have a
> column called 'Part Number' , some rows have the correct 'Part Number'
> whereas others have the 'NULL' value, even though the Part Number is not
> null.
> This happens for a few columns.
> I already tried converting problem column formatting to text or numeric,
> but
> it didn't make any difference.
> Does someone have any idea why it must be happening and how I can import
> all
> data.
> Thank you in advance,
> -Me
>
need urgent help with sa account...
background sql2k on win2k3
i started getting this error msg when accessing properties page of 'sa'
and 'buildin/administrators' accounts in sql.
Error 21776 [SQL-DMO]the name 'dbo' was not found in the Users
Collection. If the name is qualified name,
use [] to separate various parts of the name, and try again.
i previously disabled the sa right for 'buildin/administrators' and now
i can't add the sa rights back to the group because of this err. i
can't modifiy the 'sa' account for the same reason. i tried to delete
'buildin/administrators' group and add it back but now i can't add it
back either.
1. how to add back the 'buildin/administrators' group?
2. how to fix this error? it doesn't happen to other non-sa accounts.
thank you!!What commadn where you executing to add the security group to sql server
again, Where you able to connect anyway (so did you turned on mixed auth. ?)
Jens Suessmeyer.
"=== Steve L ===" <steve.lin@.powells.com> schrieb im Newsbeitrag
news:1114720021.174631.14500@.g14g2000cwa.googlegroups.com...
> background sql2k on win2k3
> i started getting this error msg when accessing properties page of 'sa'
> and 'buildin/administrators' accounts in sql.
> Error 21776 [SQL-DMO]the name 'dbo' was not found in the Users
> Collection. If the name is qualified name,
> use [] to separate various parts of the name, and try again.
> i previously disabled the sa right for 'buildin/administrators' and now
> i can't add the sa rights back to the group because of this err. i
> can't modifiy the 'sa' account for the same reason. i tried to delete
> 'buildin/administrators' group and add it back but now i can't add it
> back either.
> 1. how to add back the 'buildin/administrators' group?
> 2. how to fix this error? it doesn't happen to other non-sa accounts.
> thank you!!
>|||> 1. how to add back the 'buildin/administrators' group?
EXEC sp_grantlogin 'BUILTIN\Administrators'
EXEC sp_addsrvrolemember 'BUILTIN\Administrators', 'sy
min'
> 2. how to fix this error? it doesn't happen to other non-sa accounts.
This error may be caused by an incorrect database owner in one or more of
your databases. You can correct the owner with sp_changedbowner like the
example below. See http://support.microsoft.com/kb/q218172/ for more
information.
USE MyDatabase
EXEC sp_changedbowner ''MyOwnerLogin'
Hope this helps.
Dan Guzman
SQL Server MVP
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1114720021.174631.14500@.g14g2000cwa.googlegroups.com...
> background sql2k on win2k3
> i started getting this error msg when accessing properties page of 'sa'
> and 'buildin/administrators' accounts in sql.
> Error 21776 [SQL-DMO]the name 'dbo' was not found in the Users
> Collection. If the name is qualified name,
> use [] to separate various parts of the name, and try again.
> i previously disabled the sa right for 'buildin/administrators' and now
> i can't add the sa rights back to the group because of this err. i
> can't modifiy the 'sa' account for the same reason. i tried to delete
> 'buildin/administrators' group and add it back but now i can't add it
> back either.
> 1. how to add back the 'buildin/administrators' group?
> 2. how to fix this error? it doesn't happen to other non-sa accounts.
> thank you!!
>|||Dan, Thanks for saving my day!!! simply executed the two commands you
sent me. 'BUILTIN\Administrators' was back and all the problems
mysteriously went away. i did detach like 8 databases from the other
server and attached to this server before this happened. i think that
might be one of the problems. although i'm not sure in general, who
should be the owner of all the databases in SQL Server land. since my
accout has sa right, quite often it showes i'm the owner of a database,
but sometimes it's sa, and sometimes it's the domain account created
for running the service (with sa privileges).|||I usually specify the 'sa' login as the owner of production databases and
make it a practice to change the owner following a database restore or
attach. This works well when only symin role members can create
dbo-owned objects. In cases where non-symin role members create
dbo-owned objects, you can create a login that is used only for database
ownership.
Hope this helps.
Dan Guzman
SQL Server MVP
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1114788026.433970.131740@.o13g2000cwo.googlegroups.com...
> Dan, Thanks for saving my day!!! simply executed the two commands you
> sent me. 'BUILTIN\Administrators' was back and all the problems
> mysteriously went away. i did detach like 8 databases from the other
> server and attached to this server before this happened. i think that
> might be one of the problems. although i'm not sure in general, who
> should be the owner of all the databases in SQL Server land. since my
> accout has sa right, quite often it showes i'm the owner of a database,
> but sometimes it's sa, and sometimes it's the domain account created
> for running the service (with sa privileges).
>
i started getting this error msg when accessing properties page of 'sa'
and 'buildin/administrators' accounts in sql.
Error 21776 [SQL-DMO]the name 'dbo' was not found in the Users
Collection. If the name is qualified name,
use [] to separate various parts of the name, and try again.
i previously disabled the sa right for 'buildin/administrators' and now
i can't add the sa rights back to the group because of this err. i
can't modifiy the 'sa' account for the same reason. i tried to delete
'buildin/administrators' group and add it back but now i can't add it
back either.
1. how to add back the 'buildin/administrators' group?
2. how to fix this error? it doesn't happen to other non-sa accounts.
thank you!!What commadn where you executing to add the security group to sql server
again, Where you able to connect anyway (so did you turned on mixed auth. ?)
Jens Suessmeyer.
"=== Steve L ===" <steve.lin@.powells.com> schrieb im Newsbeitrag
news:1114720021.174631.14500@.g14g2000cwa.googlegroups.com...
> background sql2k on win2k3
> i started getting this error msg when accessing properties page of 'sa'
> and 'buildin/administrators' accounts in sql.
> Error 21776 [SQL-DMO]the name 'dbo' was not found in the Users
> Collection. If the name is qualified name,
> use [] to separate various parts of the name, and try again.
> i previously disabled the sa right for 'buildin/administrators' and now
> i can't add the sa rights back to the group because of this err. i
> can't modifiy the 'sa' account for the same reason. i tried to delete
> 'buildin/administrators' group and add it back but now i can't add it
> back either.
> 1. how to add back the 'buildin/administrators' group?
> 2. how to fix this error? it doesn't happen to other non-sa accounts.
> thank you!!
>|||> 1. how to add back the 'buildin/administrators' group?
EXEC sp_grantlogin 'BUILTIN\Administrators'
EXEC sp_addsrvrolemember 'BUILTIN\Administrators', 'sy
min'> 2. how to fix this error? it doesn't happen to other non-sa accounts.
This error may be caused by an incorrect database owner in one or more of
your databases. You can correct the owner with sp_changedbowner like the
example below. See http://support.microsoft.com/kb/q218172/ for more
information.
USE MyDatabase
EXEC sp_changedbowner ''MyOwnerLogin'
Hope this helps.
Dan Guzman
SQL Server MVP
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1114720021.174631.14500@.g14g2000cwa.googlegroups.com...
> background sql2k on win2k3
> i started getting this error msg when accessing properties page of 'sa'
> and 'buildin/administrators' accounts in sql.
> Error 21776 [SQL-DMO]the name 'dbo' was not found in the Users
> Collection. If the name is qualified name,
> use [] to separate various parts of the name, and try again.
> i previously disabled the sa right for 'buildin/administrators' and now
> i can't add the sa rights back to the group because of this err. i
> can't modifiy the 'sa' account for the same reason. i tried to delete
> 'buildin/administrators' group and add it back but now i can't add it
> back either.
> 1. how to add back the 'buildin/administrators' group?
> 2. how to fix this error? it doesn't happen to other non-sa accounts.
> thank you!!
>|||Dan, Thanks for saving my day!!! simply executed the two commands you
sent me. 'BUILTIN\Administrators' was back and all the problems
mysteriously went away. i did detach like 8 databases from the other
server and attached to this server before this happened. i think that
might be one of the problems. although i'm not sure in general, who
should be the owner of all the databases in SQL Server land. since my
accout has sa right, quite often it showes i'm the owner of a database,
but sometimes it's sa, and sometimes it's the domain account created
for running the service (with sa privileges).|||I usually specify the 'sa' login as the owner of production databases and
make it a practice to change the owner following a database restore or
attach. This works well when only sy
min role members can createdbo-owned objects. In cases where non-sy
min role members createdbo-owned objects, you can create a login that is used only for database
ownership.
Hope this helps.
Dan Guzman
SQL Server MVP
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1114788026.433970.131740@.o13g2000cwo.googlegroups.com...
> Dan, Thanks for saving my day!!! simply executed the two commands you
> sent me. 'BUILTIN\Administrators' was back and all the problems
> mysteriously went away. i did detach like 8 databases from the other
> server and attached to this server before this happened. i think that
> might be one of the problems. although i'm not sure in general, who
> should be the owner of all the databases in SQL Server land. since my
> accout has sa right, quite often it showes i'm the owner of a database,
> but sometimes it's sa, and sometimes it's the domain account created
> for running the service (with sa privileges).
>
need urgent help with directory structure...
I accidently changed the directory sub folders orders in the
c:\Program Files\Microsoft SQL Server\MSSQL.3 folder (i believe it's
for reporting service)
now the reporting service won't start.
can anyone tell me what the sub direcotries should look like under that
directory?
thank you!!!Reporting Services
\LogFiles
\ReportManager
\...
\ReportServer
\...
\RSTempFiles
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1143834522.169647.108420@.i39g2000cwa.googlegroups.com...
>I accidently changed the directory sub folders orders in the
> c:\Program Files\Microsoft SQL Server\MSSQL.3 folder (i believe it's
> for reporting service)
> now the reporting service won't start.
> can anyone tell me what the sub direcotries should look like under that
> directory?
> thank you!!!
>
c:\Program Files\Microsoft SQL Server\MSSQL.3 folder (i believe it's
for reporting service)
now the reporting service won't start.
can anyone tell me what the sub direcotries should look like under that
directory?
thank you!!!Reporting Services
\LogFiles
\ReportManager
\...
\ReportServer
\...
\RSTempFiles
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1143834522.169647.108420@.i39g2000cwa.googlegroups.com...
>I accidently changed the directory sub folders orders in the
> c:\Program Files\Microsoft SQL Server\MSSQL.3 folder (i believe it's
> for reporting service)
> now the reporting service won't start.
> can anyone tell me what the sub direcotries should look like under that
> directory?
> thank you!!!
>
Need urgent help with a db restore
using sql server 2000.
we have a table (that used to have 2.7 million records) which now has none.
We did a full db backup monday night at 7pm, did the weekly trans log backup
monday at 7:30pm.
The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
lost (using Export data) by trying to move a stored procedure (as an object)
from the development server to the production server. The process somehow
cleared out the table (in production) the stored procedure uses.
I know I can recover the table (actually the who db) via the 7pm full
database backup and the 7:30 transaction log backup, but how do I recover the
data that was entered after the 7:30 trans log backup up to 4:40pm the next
day (Tuesday)?
Thanks for any and all help.
Russ...add-on to the first message.
There was also a tlog backup done Tuesday night at 7:30pm
thanks
russ...
"Russ" wrote:
> using sql server 2000.
> we have a table (that used to have 2.7 million records) which now has none.
> We did a full db backup monday night at 7pm, did the weekly trans log backup
> monday at 7:30pm.
> The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
> lost (using Export data) by trying to move a stored procedure (as an object)
> from the development server to the production server. The process somehow
> cleared out the table (in production) the stored procedure uses.
> I know I can recover the table (actually the who db) via the 7pm full
> database backup and the 7:30 transaction log backup, but how do I recover the
> data that was entered after the 7:30 trans log backup up to 4:40pm the next
> day (Tuesday)?
> Thanks for any and all help.
> Russ...|||Perform a log backup now and restore the latest db backup, and all subsequent tlog backups. For the
very last one, specify a STOPAT value which is just before the accident. More info and options:
http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Russ" <Russ@.discussions.microsoft.com> wrote in message
news:2038FEBB-D723-4797-8BC4-9319299A1809@.microsoft.com...
> using sql server 2000.
> we have a table (that used to have 2.7 million records) which now has none.
> We did a full db backup monday night at 7pm, did the weekly trans log backup
> monday at 7:30pm.
> The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
> lost (using Export data) by trying to move a stored procedure (as an object)
> from the development server to the production server. The process somehow
> cleared out the table (in production) the stored procedure uses.
> I know I can recover the table (actually the who db) via the 7pm full
> database backup and the 7:30 transaction log backup, but how do I recover the
> data that was entered after the 7:30 trans log backup up to 4:40pm the next
> day (Tuesday)?
> Thanks for any and all help.
> Russ...
we have a table (that used to have 2.7 million records) which now has none.
We did a full db backup monday night at 7pm, did the weekly trans log backup
monday at 7:30pm.
The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
lost (using Export data) by trying to move a stored procedure (as an object)
from the development server to the production server. The process somehow
cleared out the table (in production) the stored procedure uses.
I know I can recover the table (actually the who db) via the 7pm full
database backup and the 7:30 transaction log backup, but how do I recover the
data that was entered after the 7:30 trans log backup up to 4:40pm the next
day (Tuesday)?
Thanks for any and all help.
Russ...add-on to the first message.
There was also a tlog backup done Tuesday night at 7:30pm
thanks
russ...
"Russ" wrote:
> using sql server 2000.
> we have a table (that used to have 2.7 million records) which now has none.
> We did a full db backup monday night at 7pm, did the weekly trans log backup
> monday at 7:30pm.
> The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
> lost (using Export data) by trying to move a stored procedure (as an object)
> from the development server to the production server. The process somehow
> cleared out the table (in production) the stored procedure uses.
> I know I can recover the table (actually the who db) via the 7pm full
> database backup and the 7:30 transaction log backup, but how do I recover the
> data that was entered after the 7:30 trans log backup up to 4:40pm the next
> day (Tuesday)?
> Thanks for any and all help.
> Russ...|||Perform a log backup now and restore the latest db backup, and all subsequent tlog backups. For the
very last one, specify a STOPAT value which is just before the accident. More info and options:
http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Russ" <Russ@.discussions.microsoft.com> wrote in message
news:2038FEBB-D723-4797-8BC4-9319299A1809@.microsoft.com...
> using sql server 2000.
> we have a table (that used to have 2.7 million records) which now has none.
> We did a full db backup monday night at 7pm, did the weekly trans log backup
> monday at 7:30pm.
> The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
> lost (using Export data) by trying to move a stored procedure (as an object)
> from the development server to the production server. The process somehow
> cleared out the table (in production) the stored procedure uses.
> I know I can recover the table (actually the who db) via the 7pm full
> database backup and the 7:30 transaction log backup, but how do I recover the
> data that was entered after the 7:30 trans log backup up to 4:40pm the next
> day (Tuesday)?
> Thanks for any and all help.
> Russ...
Need urgent help with a db restore
using sql server 2000.
we have a table (that used to have 2.7 million records) which now has none.
We did a full db backup monday night at 7pm, did the weekly trans log backup
monday at 7:30pm.
The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
lost (using Export data) by trying to move a stored procedure (as an object)
from the development server to the production server. The process somehow
cleared out the table (in production) the stored procedure uses.
I know I can recover the table (actually the who db) via the 7pm full
database backup and the 7:30 transaction log backup, but how do I recover th
e
data that was entered after the 7:30 trans log backup up to 4:40pm the next
day (Tuesday)?
Thanks for any and all help.
Russ...add-on to the first message.
There was also a tlog backup done Tuesday night at 7:30pm
thanks
russ...
"Russ" wrote:
> using sql server 2000.
> we have a table (that used to have 2.7 million records) which now has none
.
> We did a full db backup monday night at 7pm, did the weekly trans log back
up
> monday at 7:30pm.
> The data from the table (Dailylog) was lost at 4:42pm tuesday. The data w
as
> lost (using Export data) by trying to move a stored procedure (as an objec
t)
> from the development server to the production server. The process somehow
> cleared out the table (in production) the stored procedure uses.
> I know I can recover the table (actually the who db) via the 7pm full
> database backup and the 7:30 transaction log backup, but how do I recover
the
> data that was entered after the 7:30 trans log backup up to 4:40pm the nex
t
> day (Tuesday)?
> Thanks for any and all help.
> Russ...|||Perform a log backup now and restore the latest db backup, and all subsequen
t tlog backups. For the
very last one, specify a STOPAT value which is just before the accident. Mor
e info and options:
http://www.karaszi.com/SQLServer/in...veral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Russ" <Russ@.discussions.microsoft.com> wrote in message
news:2038FEBB-D723-4797-8BC4-9319299A1809@.microsoft.com...
> using sql server 2000.
> we have a table (that used to have 2.7 million records) which now has none
.
> We did a full db backup monday night at 7pm, did the weekly trans log back
up
> monday at 7:30pm.
> The data from the table (Dailylog) was lost at 4:42pm tuesday. The data w
as
> lost (using Export data) by trying to move a stored procedure (as an objec
t)
> from the development server to the production server. The process somehow
> cleared out the table (in production) the stored procedure uses.
> I know I can recover the table (actually the who db) via the 7pm full
> database backup and the 7:30 transaction log backup, but how do I recover
the
> data that was entered after the 7:30 trans log backup up to 4:40pm the nex
t
> day (Tuesday)?
> Thanks for any and all help.
> Russ...
we have a table (that used to have 2.7 million records) which now has none.
We did a full db backup monday night at 7pm, did the weekly trans log backup
monday at 7:30pm.
The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
lost (using Export data) by trying to move a stored procedure (as an object)
from the development server to the production server. The process somehow
cleared out the table (in production) the stored procedure uses.
I know I can recover the table (actually the who db) via the 7pm full
database backup and the 7:30 transaction log backup, but how do I recover th
e
data that was entered after the 7:30 trans log backup up to 4:40pm the next
day (Tuesday)?
Thanks for any and all help.
Russ...add-on to the first message.
There was also a tlog backup done Tuesday night at 7:30pm
thanks
russ...
"Russ" wrote:
> using sql server 2000.
> we have a table (that used to have 2.7 million records) which now has none
.
> We did a full db backup monday night at 7pm, did the weekly trans log back
up
> monday at 7:30pm.
> The data from the table (Dailylog) was lost at 4:42pm tuesday. The data w
as
> lost (using Export data) by trying to move a stored procedure (as an objec
t)
> from the development server to the production server. The process somehow
> cleared out the table (in production) the stored procedure uses.
> I know I can recover the table (actually the who db) via the 7pm full
> database backup and the 7:30 transaction log backup, but how do I recover
the
> data that was entered after the 7:30 trans log backup up to 4:40pm the nex
t
> day (Tuesday)?
> Thanks for any and all help.
> Russ...|||Perform a log backup now and restore the latest db backup, and all subsequen
t tlog backups. For the
very last one, specify a STOPAT value which is just before the accident. Mor
e info and options:
http://www.karaszi.com/SQLServer/in...veral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Russ" <Russ@.discussions.microsoft.com> wrote in message
news:2038FEBB-D723-4797-8BC4-9319299A1809@.microsoft.com...
> using sql server 2000.
> we have a table (that used to have 2.7 million records) which now has none
.
> We did a full db backup monday night at 7pm, did the weekly trans log back
up
> monday at 7:30pm.
> The data from the table (Dailylog) was lost at 4:42pm tuesday. The data w
as
> lost (using Export data) by trying to move a stored procedure (as an objec
t)
> from the development server to the production server. The process somehow
> cleared out the table (in production) the stored procedure uses.
> I know I can recover the table (actually the who db) via the 7pm full
> database backup and the 7:30 transaction log backup, but how do I recover
the
> data that was entered after the 7:30 trans log backup up to 4:40pm the nex
t
> day (Tuesday)?
> Thanks for any and all help.
> Russ...
Need urgent help with a db restore
using sql server 2000.
we have a table (that used to have 2.7 million records) which now has none.
We did a full db backup monday night at 7pm, did the weekly trans log backup
monday at 7:30pm.
The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
lost (using Export data) by trying to move a stored procedure (as an object)
from the development server to the production server. The process somehow
cleared out the table (in production) the stored procedure uses.
I know I can recover the table (actually the who db) via the 7pm full
database backup and the 7:30 transaction log backup, but how do I recover the
data that was entered after the 7:30 trans log backup up to 4:40pm the next
day (Tuesday)?
Thanks for any and all help.
Russ...
add-on to the first message.
There was also a tlog backup done Tuesday night at 7:30pm
thanks
russ...
"Russ" wrote:
> using sql server 2000.
> we have a table (that used to have 2.7 million records) which now has none.
> We did a full db backup monday night at 7pm, did the weekly trans log backup
> monday at 7:30pm.
> The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
> lost (using Export data) by trying to move a stored procedure (as an object)
> from the development server to the production server. The process somehow
> cleared out the table (in production) the stored procedure uses.
> I know I can recover the table (actually the who db) via the 7pm full
> database backup and the 7:30 transaction log backup, but how do I recover the
> data that was entered after the 7:30 trans log backup up to 4:40pm the next
> day (Tuesday)?
> Thanks for any and all help.
> Russ...
|||Perform a log backup now and restore the latest db backup, and all subsequent tlog backups. For the
very last one, specify a STOPAT value which is just before the accident. More info and options:
http://www.karaszi.com/SQLServer/inf...eral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Russ" <Russ@.discussions.microsoft.com> wrote in message
news:2038FEBB-D723-4797-8BC4-9319299A1809@.microsoft.com...
> using sql server 2000.
> we have a table (that used to have 2.7 million records) which now has none.
> We did a full db backup monday night at 7pm, did the weekly trans log backup
> monday at 7:30pm.
> The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
> lost (using Export data) by trying to move a stored procedure (as an object)
> from the development server to the production server. The process somehow
> cleared out the table (in production) the stored procedure uses.
> I know I can recover the table (actually the who db) via the 7pm full
> database backup and the 7:30 transaction log backup, but how do I recover the
> data that was entered after the 7:30 trans log backup up to 4:40pm the next
> day (Tuesday)?
> Thanks for any and all help.
> Russ...
we have a table (that used to have 2.7 million records) which now has none.
We did a full db backup monday night at 7pm, did the weekly trans log backup
monday at 7:30pm.
The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
lost (using Export data) by trying to move a stored procedure (as an object)
from the development server to the production server. The process somehow
cleared out the table (in production) the stored procedure uses.
I know I can recover the table (actually the who db) via the 7pm full
database backup and the 7:30 transaction log backup, but how do I recover the
data that was entered after the 7:30 trans log backup up to 4:40pm the next
day (Tuesday)?
Thanks for any and all help.
Russ...
add-on to the first message.
There was also a tlog backup done Tuesday night at 7:30pm
thanks
russ...
"Russ" wrote:
> using sql server 2000.
> we have a table (that used to have 2.7 million records) which now has none.
> We did a full db backup monday night at 7pm, did the weekly trans log backup
> monday at 7:30pm.
> The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
> lost (using Export data) by trying to move a stored procedure (as an object)
> from the development server to the production server. The process somehow
> cleared out the table (in production) the stored procedure uses.
> I know I can recover the table (actually the who db) via the 7pm full
> database backup and the 7:30 transaction log backup, but how do I recover the
> data that was entered after the 7:30 trans log backup up to 4:40pm the next
> day (Tuesday)?
> Thanks for any and all help.
> Russ...
|||Perform a log backup now and restore the latest db backup, and all subsequent tlog backups. For the
very last one, specify a STOPAT value which is just before the accident. More info and options:
http://www.karaszi.com/SQLServer/inf...eral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Russ" <Russ@.discussions.microsoft.com> wrote in message
news:2038FEBB-D723-4797-8BC4-9319299A1809@.microsoft.com...
> using sql server 2000.
> we have a table (that used to have 2.7 million records) which now has none.
> We did a full db backup monday night at 7pm, did the weekly trans log backup
> monday at 7:30pm.
> The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
> lost (using Export data) by trying to move a stored procedure (as an object)
> from the development server to the production server. The process somehow
> cleared out the table (in production) the stored procedure uses.
> I know I can recover the table (actually the who db) via the 7pm full
> database backup and the 7:30 transaction log backup, but how do I recover the
> data that was entered after the 7:30 trans log backup up to 4:40pm the next
> day (Tuesday)?
> Thanks for any and all help.
> Russ...
Need urgent help to sort this out!
I've made this example and it loads a picture into a database. (MsSql )
Take a look at the code, it works just fine however it leaves a process in sleeping mode "avaiting command" in Enterprise manager under "Management/current Activity/Process Info"
Is it supposed to be like this or is it supposed to be reemoved after .net is finished??
Code snip
_______________________________________________________
|||Thanks for the reply Morton!
I suspect that you mean something like this.
----------------
----------------
However it still leaves a sleeping process, but it will eventually time out, and be killed, I suppose.
I dont think this would bee such a good solution tough, what if the server is short off memory and there is a lot of sleeping processes that just waits to be timed out. The system would eventually freeze I think. Both IIS and MsSql harvests memory if I remember right.
Anyway the question would still be, Should the .net SqlClient leave a sleeping process on the server when the SqlClient is finished with all its doings?
Regards
Tombola|||I believe what you are seeing is a phenomenon of connection pooling. This is a good thing.
You should likely add a cmd.Dispose() after the conn.Close(). AndI would also add a conn.Dispose() after the conn.Close() for goodmeasure. It's a good idea to Dispose any object which implementthe IDisposable interface once you are done with it.
Take a look at the code, it works just fine however it leaves a process in sleeping mode "avaiting command" in Enterprise manager under "Management/current Activity/Process Info"
Is it supposed to be like this or is it supposed to be reemoved after .net is finished??
Code snip
_______________________________________________________
Dim connAsNew SqlConnection("Data Source = (local);Initial Catalog = " & "test;User ID = NAME; Password=PASSWORD;")
Dim cmdAsNew SqlCommand("Select * from tab_bild", cnn)
Try
conn.Open()
Dim myDatareaderAs SqlDataReader
myDatareader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
DoWhile (myDatareader.Read())
Response.ContentType = myDatareader.Item("PersonImageType")
Response.BinaryWrite(myDatareader.Item("PersonImage"))
Loop
conn.Close()
Response.Write("Picture info succesfully retrieved")
Catch SQLexcAs SqlException
Response.Write("Read failed, Reason: " & SQLexc.ToString())
EndTry
EndSub
________________________________________________________________
Please can someone explain this for me or sort this out for me.
All help is welcome even if its only points me too a direction.
Regards
Tombola
|||Thanks for the reply Morton!
I suspect that you mean something like this.
----------------
Try
conn.Open()
Dim myDatareaderAs SqlDataReader
myDatareader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
DoWhile (myDatareader.Read())
Response.ContentType = myDatareader.Item("PersonImageType")
Response.BinaryWrite(myDatareader.Item("PersonImage"))
Loop
Response.Write("Bild info succesfully retrieved")
Catch SQLexcAs SqlException
Response.Write("Read failed, Reason: " & SQLexc.ToString())
Finally
conn.Close()
EndTry
----------------
However it still leaves a sleeping process, but it will eventually time out, and be killed, I suppose.
I dont think this would bee such a good solution tough, what if the server is short off memory and there is a lot of sleeping processes that just waits to be timed out. The system would eventually freeze I think. Both IIS and MsSql harvests memory if I remember right.
Anyway the question would still be, Should the .net SqlClient leave a sleeping process on the server when the SqlClient is finished with all its doings?
Regards
Tombola|||I believe what you are seeing is a phenomenon of connection pooling. This is a good thing.
You should likely add a cmd.Dispose() after the conn.Close(). AndI would also add a conn.Dispose() after the conn.Close() for goodmeasure. It's a good idea to Dispose any object which implementthe IDisposable interface once you are done with it.
Subscribe to:
Posts (Atom)