Backing up database over network

I am trying to get my database to backup to a network
path (\\myserver\backup\db.bak) instead of a local path.
But, unfortunately it is not working, I keep the message
that it cannot connect to the device or the device is
offline.
Here is my backup statement
BACKUP DATABASE test
TO DISK = 'c:\temp\test.bak'
WITH FORMAT,
NAME = 'Test Backup'
Any Ideas?The account that sqls erver runs under is the one that is used when backups
occur. So you need to ensure that this account has permissions to the
network share you are attempting to backup to. Chances are the account is
the local admin account which has no domain privileges.
--
Andrew J. Kelly
SQL Server MVP
"Ben Skolmoski" <ben.skolmoski@.imcentric.com> wrote in message
news:02e901c3a581$03d068a0$a601280a@.phx.gbl...
> I am trying to get my database to backup to a network
> path (\\myserver\backup\db.bak) instead of a local path.
> But, unfortunately it is not working, I keep the message
> that it cannot connect to the device or the device is
> offline.
> Here is my backup statement
> BACKUP DATABASE test
> TO DISK = 'c:\temp\test.bak'
> WITH FORMAT,
> NAME = 'Test Backup'
> Any Ideas?|||I have already checked all of that and the account has
full access to my network share.
>--Original Message--
>The account that sqls erver runs under is the one that
is used when backups
>occur. So you need to ensure that this account has
permissions to the
>network share you are attempting to backup to. Chances
are the account is
>the local admin account which has no domain privileges.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Ben Skolmoski" <ben.skolmoski@.imcentric.com> wrote in
message
>news:02e901c3a581$03d068a0$a601280a@.phx.gbl...
>> I am trying to get my database to backup to a network
>> path (\\myserver\backup\db.bak) instead of a local
path.
>> But, unfortunately it is not working, I keep the
message
>> that it cannot connect to the device or the device is
>> offline.
>> Here is my backup statement
>> BACKUP DATABASE test
>> TO DISK = 'c:\temp\test.bak'
>> WITH FORMAT,
>> NAME = 'Test Backup'
>> Any Ideas?
>
>.
>|||Ben,
The example you showed was not using UNC and was obviously not the real
code. It helps to show the actual code to alleviate any assumptions or the
possibility of simply a typo that doesn't get relayed with a simpler
example. So I assume from what you state you have logged on to the server
under the account sqls erver is running under and were able to copy a file
with the exact name of the backup to the UNC you want. If not then check to
ensure there isn't already a file with that name that is locked by some
other resource.
--
Andrew J. Kelly
SQL Server MVP
"Ben Skolmoski" <ben.skolmoski@.imcentric.com> wrote in message
news:037301c3a589$687422d0$a601280a@.phx.gbl...
> I have already checked all of that and the account has
> full access to my network share.
>
> >--Original Message--
> >The account that sqls erver runs under is the one that
> is used when backups
> >occur. So you need to ensure that this account has
> permissions to the
> >network share you are attempting to backup to. Chances
> are the account is
> >the local admin account which has no domain privileges.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Ben Skolmoski" <ben.skolmoski@.imcentric.com> wrote in
> message
> >news:02e901c3a581$03d068a0$a601280a@.phx.gbl...
> >> I am trying to get my database to backup to a network
> >> path (\\myserver\backup\db.bak) instead of a local
> path.
> >> But, unfortunately it is not working, I keep the
> message
> >> that it cannot connect to the device or the device is
> >> offline.
> >>
> >> Here is my backup statement
> >>
> >> BACKUP DATABASE test
> >> TO DISK = 'c:\temp\test.bak'
> >> WITH FORMAT,
> >> NAME = 'Test Backup'
> >>
> >> Any Ideas?
> >
> >
> >.
> >|||If you log on to the SQL Server machine with the SQL Server startup account
can you access the remote share that you are trying to restore to/
Rand
This posting is provided "as is" with no warranties and confers no rights.sql

Backing up an active database

I have an active database running on SQL Server 2000. I use the backup
software Backup Exec 9. When I do my backups, I backup all working
directories. When I do a test restore, I notice that the database does not
backup but all the other files in the directory does. What am I not doing
correctly? Any help is appreciated.Do a SQL Backup. Backup Exec is trying to copy the sql files that are in use
by SQL Server so you won't be able to copy them.
Set up a maintenance plan to do sql backups and use backup exec to backup
the SQL backup files.
"Peter McCaul" <compcoff@.hotmail.com> wrote in message
news:e3ZNoJLHEHA.2472@.TK2MSFTNGP10.phx.gbl...
> I have an active database running on SQL Server 2000. I use the backup
> software Backup Exec 9. When I do my backups, I backup all working
> directories. When I do a test restore, I notice that the database does
not
> backup but all the other files in the directory does. What am I not doing
> correctly? Any help is appreciated.
>|||Hi,
R U trying to backup .mdf and .ldf files online?. Very few
backup softwares support Open File backup. Look into the
log of backup tool if any errors are thrown. Also it is
not a good idea to take backups of open files. you can
generate backup of the databases using maintenance plans
or scripts then use the backup tool to move them into
DATs/LTO's.
If you still insist to backup the .mdf and ldf files,which
I would not prefer for larger databases, you can schedule
as
1.Detach Database(s)
2.Backup .mdf & .ldf files using the backup software.
3.Attach Database(s)& corresponding tran. logs.
Someone may also suggest to stop SQL Server and backup
the .mdf and .ldf files, but there are chances for
failures while attaching them.
HTH
Regards
Thirumal
>--Original Message--
>I have an active database running on SQL Server 2000. I
use the backup
>software Backup Exec 9. When I do my backups, I backup
all working
>directories. When I do a test restore, I notice that the
database does not
>backup but all the other files in the directory does.
What am I not doing
>correctly? Any help is appreciated.
>
>.
>

Backing up a database?

I've got two developers that are working on a asp.net app that use SQL
Server 2000. They've both got the developer edition on each of their PCs.
I'd like to know what is the best way to "backup" the database so that 1) we
can put this backup on the server so our tape backup picks it up each night
and 2) so a developer can "synch" his version of the database with the other
developer (i.e. developer A does a backup and stores it on a network share
then developer B uses that backup to recreate developer a's database on his
PC).
I've used the backup command, and it seems that it does backup the data and
the schema to a location on the local disk. Then I copied that to the other
developer's PC and did a restore into a new database. That worked well, but
when I tried to restore the same backup file into a second new database on
the other developer's PC, SQL Server complained that the first database
(that I restored to) was using this file. So, something just seemed odd
about that. I also see there is an export wizard, but it seemed like that
was geared towards exporting the raw data to some other format.
Any help would be much appreciated. Thanks!
Yikes. What happens when you have 5 developers? 20? 50? Maintaining that
many development databases will be a nightmare!
Here are my suggestions:
Buy a development database server that everyone can use.
Obtain a source control product such as Visual Source Safe.
When a developer creates a stored procedure or view have them create the
text for the object using Query Analyzer. Save the script to a file (with
the same name as the object) and have him check the file into VSS. When you
need to modify an object grab the script from source control, make the
change, create/recreate the object within the database, save the script
file, and check it back in to source control (with comments of course).
That will give you the ability to know what changed, who changed it, and
why.
Start backing up your database and your source control repository. Make
sure that they get to tape.
Keith
"epigram" <nospam@.spammy.com> wrote in message
news:1111753952.477f69973b1b43c598d9c65d29aec71a@.b ubbanews...
> I've got two developers that are working on a asp.net app that use SQL
> Server 2000. They've both got the developer edition on each of their PCs.
> I'd like to know what is the best way to "backup" the database so that 1)
we
> can put this backup on the server so our tape backup picks it up each
night
> and 2) so a developer can "synch" his version of the database with the
other
> developer (i.e. developer A does a backup and stores it on a network share
> then developer B uses that backup to recreate developer a's database on
his
> PC).
> I've used the backup command, and it seems that it does backup the data
and
> the schema to a location on the local disk. Then I copied that to the
other
> developer's PC and did a restore into a new database. That worked well,
but
> when I tried to restore the same backup file into a second new database on
> the other developer's PC, SQL Server complained that the first database
> (that I restored to) was using this file. So, something just seemed odd
> about that. I also see there is an export wizard, but it seemed like that
> was geared towards exporting the raw data to some other format.
> Any help would be much appreciated. Thanks!
>

Backing up a database?

I've got two developers that are working on a asp.net app that use SQL
Server 2000. They've both got the developer edition on each of their PCs.
I'd like to know what is the best way to "backup" the database so that 1) we
can put this backup on the server so our tape backup picks it up each night
and 2) so a developer can "synch" his version of the database with the other
developer (i.e. developer A does a backup and stores it on a network share
then developer B uses that backup to recreate developer a's database on his
PC).
I've used the backup command, and it seems that it does backup the data and
the schema to a location on the local disk. Then I copied that to the other
developer's PC and did a restore into a new database. That worked well, but
when I tried to restore the same backup file into a second new database on
the other developer's PC, SQL Server complained that the first database
(that I restored to) was using this file. So, something just seemed odd
about that. I also see there is an export wizard, but it seemed like that
was geared towards exporting the raw data to some other format.
Any help would be much appreciated. Thanks!Yikes. What happens when you have 5 developers? 20? 50? Maintaining that
many development databases will be a nightmare!
Here are my suggestions:
Buy a development database server that everyone can use.
Obtain a source control product such as Visual Source Safe.
When a developer creates a stored procedure or view have them create the
text for the object using Query Analyzer. Save the script to a file (with
the same name as the object) and have him check the file into VSS. When you
need to modify an object grab the script from source control, make the
change, create/recreate the object within the database, save the script
file, and check it back in to source control (with comments of course).
That will give you the ability to know what changed, who changed it, and
why.
Start backing up your database and your source control repository. Make
sure that they get to tape.
Keith
"epigram" <nospam@.spammy.com> wrote in message
news:1111753952.477f69973b1b43c598d9c65d29aec71a@.bubbanews...
> I've got two developers that are working on a asp.net app that use SQL
> Server 2000. They've both got the developer edition on each of their PCs.
> I'd like to know what is the best way to "backup" the database so that 1)
we
> can put this backup on the server so our tape backup picks it up each
night
> and 2) so a developer can "synch" his version of the database with the
other
> developer (i.e. developer A does a backup and stores it on a network share
> then developer B uses that backup to recreate developer a's database on
his
> PC).
> I've used the backup command, and it seems that it does backup the data
and
> the schema to a location on the local disk. Then I copied that to the
other
> developer's PC and did a restore into a new database. That worked well,
but
> when I tried to restore the same backup file into a second new database on
> the other developer's PC, SQL Server complained that the first database
> (that I restored to) was using this file. So, something just seemed odd
> about that. I also see there is an export wizard, but it seemed like that
> was geared towards exporting the raw data to some other format.
> Any help would be much appreciated. Thanks!
>

Backing up a database?

I've got two developers that are working on a asp.net app that use SQL
Server 2000. They've both got the developer edition on each of their PCs.
I'd like to know what is the best way to "backup" the database so that 1) we
can put this backup on the server so our tape backup picks it up each night
and 2) so a developer can "synch" his version of the database with the other
developer (i.e. developer A does a backup and stores it on a network share
then developer B uses that backup to recreate developer a's database on his
PC).
I've used the backup command, and it seems that it does backup the data and
the schema to a location on the local disk. Then I copied that to the other
developer's PC and did a restore into a new database. That worked well, but
when I tried to restore the same backup file into a second new database on
the other developer's PC, SQL Server complained that the first database
(that I restored to) was using this file. So, something just seemed odd
about that. I also see there is an export wizard, but it seemed like that
was geared towards exporting the raw data to some other format.
Any help would be much appreciated. Thanks!Yikes. What happens when you have 5 developers? 20? 50? Maintaining that
many development databases will be a nightmare!
Here are my suggestions:
Buy a development database server that everyone can use.
Obtain a source control product such as Visual Source Safe.
When a developer creates a stored procedure or view have them create the
text for the object using Query Analyzer. Save the script to a file (with
the same name as the object) and have him check the file into VSS. When you
need to modify an object grab the script from source control, make the
change, create/recreate the object within the database, save the script
file, and check it back in to source control (with comments of course).
That will give you the ability to know what changed, who changed it, and
why.
Start backing up your database and your source control repository. Make
sure that they get to tape.
Keith
"epigram" <nospam@.spammy.com> wrote in message
news:1111753952. 477f69973b1b43c598d9c65d29aec71a@.bubbane
ws...
> I've got two developers that are working on a asp.net app that use SQL
> Server 2000. They've both got the developer edition on each of their PCs.
> I'd like to know what is the best way to "backup" the database so that 1)
we
> can put this backup on the server so our tape backup picks it up each
night
> and 2) so a developer can "synch" his version of the database with the
other
> developer (i.e. developer A does a backup and stores it on a network share
> then developer B uses that backup to recreate developer a's database on
his
> PC).
> I've used the backup command, and it seems that it does backup the data
and
> the schema to a location on the local disk. Then I copied that to the
other
> developer's PC and did a restore into a new database. That worked well,
but
> when I tried to restore the same backup file into a second new database on
> the other developer's PC, SQL Server complained that the first database
> (that I restored to) was using this file. So, something just seemed odd
> about that. I also see there is an export wizard, but it seemed like that
> was geared towards exporting the raw data to some other format.
> Any help would be much appreciated. Thanks!
>

back up without ent manager.

As per earlier post my ent manager has stopped working and I have not been
ale to re-install it. I want to make full back ups of the database but have
never done this without ent manager.
Can I just copy the files in the data folder? or is there a better way.
JohnYou can easily use a batch file and OSQL
osql -S. -E -Q"BACKUP DATABASE Pubs TO DISK= 'd:\PubDeleteMePlease.bak'"
You could write something in VB and use the DMO library.
I would first though, and I haven't read you previous posts that you refer
to, I don't think, investigate why my EM is not working.
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"John Pether" <john@.john.com> wrote in message
news:#onkIILXDHA.384@.TK2MSFTNGP12.phx.gbl...
> As per earlier post my ent manager has stopped working and I have not been
> ale to re-install it. I want to make full back ups of the database but
have
> never done this without ent manager.
> Can I just copy the files in the data folder? or is there a better way.
> John
>|||Thanks for the help guys:) I have been trying to find out the reason ent
manager stopped working lal day...but its holding me up and I may be better
off just re-installing the whole thing(is on my dev machine)
The problem is the mmc snap in will not open, i get the following error:
MMC cannot open the file c:\program Files\Microsoft SQL
Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC
This may be because the file does not exist, is not an MMC console, or was
created by a later version of MMC. This may also be because you do not have
sufficient access rights to the file.
I tried as local administrator and get the following error:
c:\program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server Enterprise
Manager.MSC
The service cannot be started, either because it is disabled or because it
has no enabled devices associated with it.
If anyone has any suggestions I would appreciate the help other wise i'm
just gonna re-install the whole thing, I have already re-installed client
tools without success..
John
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eQ9wXtLXDHA.1900@.TK2MSFTNGP10.phx.gbl...
> You can easily use a batch file and OSQL
> osql -S. -E -Q"BACKUP DATABASE Pubs TO DISK= 'd:\PubDeleteMePlease.bak'"
> You could write something in VB and use the DMO library.
> I would first though, and I haven't read you previous posts that you refer
> to, I don't think, investigate why my EM is not working.
>
>
> --
> --
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "John Pether" <john@.john.com> wrote in message
> news:#onkIILXDHA.384@.TK2MSFTNGP12.phx.gbl...
> > As per earlier post my ent manager has stopped working and I have not
been
> > ale to re-install it. I want to make full back ups of the database but
> have
> > never done this without ent manager.
> >
> > Can I just copy the files in the data folder? or is there a better way.
> >
> > John
> >
> >
>|||In addition to what the others have said, you might want to look at =using WITH INIT
as in BACKUP DATABASE pubs TO DISK=3D'c:\pubs_fullbackup.bak' WITH INIT
This initializes (clears) the backup file each time.
If you don't do this...
Lets say that pubs is 15MB.
The first time that you issue a backup your backup file will be 15MB. =The second time that you issue your statement the backup file will =contain both backups -- 30MB. The third time that you issue your backup =statement it will be 45MB, the fourth time, 60MB....(plus any increase =in the size of the database along the way).
If you use WITH INIT your backup file will only contain one database =backup.
-- Keith, SQL Server MVP
"John Pether" <john@.john.com> wrote in message =news:%23onkIILXDHA.384@.TK2MSFTNGP12.phx.gbl...
> As per earlier post my ent manager has stopped working and I have not =been
> ale to re-install it. I want to make full back ups of the database but =have
> never done this without ent manager.
> > Can I just copy the files in the data folder? or is there a better =way.
> > John
> >sql

Back up database

Hi everyone,

I am working in a company where data is changed daily (IT department of a shopping center) where item prices change everyday, the quantity available in stock vary daily,...
What is the scenarios available for the back up on SQL Server 2000 (I am a developer not a database administrator, but I have a task to do which is to make a back up for the critical data) .

could u help me with some links or idea.
I was thinking of doing a full back up one time everyweek and daily I will do a differential backup?
is that the best solution?
Thanks.

The Backup and Restore Wizard is one of the best things in the product, create job schedules for your backups and it will run. I have covered most of your questions in this thread below. If you have more questions post again. Hope this helps.
http://forums.asp.net/1050678/showpost.aspx

AWE working?

Hi all,

This is a question related to AWE settings. I am on windows 2003 ent version with SQL Server 2005 std version. I have configured SQL Server to use AWE and min memery as 22G and max as 26G (I have 32G in total as physical memory). I do see the message in log as "Windows Address ... Enabled", but when I am using performance monitor to view total memory in kb for SQL Server, it never starts at 22G (usually at 1G). And I doubt why? The account I am running SQL Server is an admin account, so it can definitely locked down the pages in memory. Also, I do think AWE is working somehow, after several hours in running, the total memory will up to 8, 9 or 11G. So, apparently, it is over 2G per process limitation. I still want to know.

1. Is my AWE setting working?

2. If not, how to fix that?

3. How to confirm how many memory is actaully using by SQL Server?

Thanks,

NIng

(The comment below assumes 32 bit OS and 32 bit SQL Server.)

1. Since you see that SQL Server's memory usage grows up to 11GB, it is obvious that AWE is working. SQL Server only requests memory from the OS as it is needed -once allocated, it usually keeps it.

2. Nothing to do

3. Performance Monitor, as you are using, indicates SQL Server's memory usage. For more detailed memory usage analysis, you can look at the various SQL Server Memory counters in Performance Monitor, and you can use some the the system functions that will give you memory data. Look at the System Views. (Click on Database, Views, System Views.) Try:

SELECT * FROM sys.dm_os_performance_counter

Looking specifically at the SQL Server Memory Manager objects.

|||Are you sure this is by design?

My performance is awful after I have upgraded. SQL Server 2000 used to locked the memory as it starts, so the production server (4 cpus and 32 G memory) was doing a lot better than my development server with (1cpu and 2G memory). However, after upgrade, the performance level of the production server is awful. For example, a query need 9 seconds on test server used to run only 2 seconds on live server. Now it needs 8 seconds ...

|||

After upgrading, it is NECESSARY to rebuild ALL the indexes, and update the statistics. If you have not done that, your performace will often be signicantly worse.

And even then, some queries will run faster on SQL 2005, and some (albeit few) will run slower. It may be necessary to 'revise' queries that are signicantly slower in order to use the new ways that SQL 2005 produces execution plans.

AWE and set working set size

Hello everybody!
System description:
OS: Windows Server 2003 Enterprise Edition SP1
RAM: 16 Gb
SQL: SQL Server 2000 Enterprise Edition SP3
SQL Server has "awe enabled"=1 and "set working set size"=1 at the same time.
"Max server memory" is limited by 12 Gb. SQLServer:MemoryManager:Total
Server Memory counter shows that SQL Server consumes all of the 12 Gb memory.
Allmost every day the client receives ODBC error 1222 (lock timeout) even if
only one user is connected to the database.
BOL says that if "awe enabled"=1 memory is not swapped out by default.
www.microsoft.com says that if you change "set working set size" from its
default value it may involve 844 and 845 errors (page latch timeout).
So the questions are:
1. Can two simulaneously enabled options ("awe enabled" and "set working set
size") result in such conflict?
2. Can enabling set working set size result in ODBC error 1222?
Thank you
I don't know if that configuration will cause that error but I doubt it.
There is no reason to have SET Working Set Size set to 1 when you have AWE
since AWE in 2000 is not dynamic to begin with. AWE also allocates all the
memory up to the Max memory setting on startup. I would turn it off and see
if the errors go away and I see no need to have in on anyway.
Andrew J. Kelly SQL MVP
"RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
news:CFCDD61D-ACE4-4C25-ABF1-50395C763B8E@.microsoft.com...
> Hello everybody!
> System description:
> OS: Windows Server 2003 Enterprise Edition SP1
> RAM: 16 Gb
> SQL: SQL Server 2000 Enterprise Edition SP3
> SQL Server has "awe enabled"=1 and "set working set size"=1 at the same
> time.
> "Max server memory" is limited by 12 Gb. SQLServer:MemoryManager:Total
> Server Memory counter shows that SQL Server consumes all of the 12 Gb
> memory.
> Allmost every day the client receives ODBC error 1222 (lock timeout) even
> if
> only one user is connected to the database.
> BOL says that if "awe enabled"=1 memory is not swapped out by default.
> www.microsoft.com says that if you change "set working set size" from its
> default value it may involve 844 and 845 errors (page latch timeout).
> So the questions are:
> 1. Can two simulaneously enabled options ("awe enabled" and "set working
> set
> size") result in such conflict?
> 2. Can enabling set working set size result in ODBC error 1222?
> Thank you
>
|||Thank you, Mr.Kelly. Before I read you answer I had decided to act by trial
and error and I advice to turn "working set size" off. My client informed me
that the error did not disappear but it got less frequent.
|||The Lock Timeout setting is infinite by default in SQL Server. IF they are
getting this message that means someone somewhere changed the setting. I
would look for any code that has SET LOCK_TIMEOUT in it and find out why and
what value they set it to.
Andrew J. Kelly SQL MVP
"RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
news:69717477-0287-4FB8-9F65-96168F25F2BB@.microsoft.com...
> Thank you, Mr.Kelly. Before I read you answer I had decided to act by
> trial
> and error and I advice to turn "working set size" off. My client informed
> me
> that the error did not disappear but it got less frequent.
|||Yes, I see, but can only one user working with the server lock himself? I
mean there are situations when only one user works with the database but the
error occurs all the same.
"Andrew J. Kelly" wrote:

> The Lock Timeout setting is infinite by default in SQL Server. IF they are
> getting this message that means someone somewhere changed the setting. I
> would look for any code that has SET LOCK_TIMEOUT in it and find out why and
> what value they set it to.
> --
> Andrew J. Kelly SQL MVP
>
> "RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
> news:69717477-0287-4FB8-9F65-96168F25F2BB@.microsoft.com...
>
>
|||Sure. If the user is trying to get an exclusive table lock there are lots of
things that can prevent him from getting one. I think you need to see the
code and why they are setting this and under what conditions it is
happening.
Andrew J. Kelly SQL MVP
"RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
news:057E64D8-ED9F-4442-9623-FECE3BDB865E@.microsoft.com...[vbcol=seagreen]
> Yes, I see, but can only one user working with the server lock himself? I
> mean there are situations when only one user works with the database but
> the
> error occurs all the same.
> "Andrew J. Kelly" wrote:
|||There is a general interface setting which allow users to set time-out (via
the setting the code receives time interval for SET LOCK_TIMEOUT statement).
The only thing I'm going to recommend is to increase the value. Thank you,
Mr.Kelly, you have been very helpful.
"Andrew J. Kelly" wrote:

> Sure. If the user is trying to get an exclusive table lock there are lots of
> things that can prevent him from getting one. I think you need to see the
> code and why they are setting this and under what conditions it is
> happening.
> --
> Andrew J. Kelly SQL MVP
>
> "RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
> news:057E64D8-ED9F-4442-9623-FECE3BDB865E@.microsoft.com...
>
>

AWE and set working set size

Hello everybody!
System description:
OS: Windows Server 2003 Enterprise Edition SP1
RAM: 16 Gb
SQL: SQL Server 2000 Enterprise Edition SP3
SQL Server has "awe enabled"=1 and "set working set size"=1 at the same time.
"Max server memory" is limited by 12 Gb. SQLServer:MemoryManager:Total
Server Memory counter shows that SQL Server consumes all of the 12 Gb memory.
Allmost every day the client receives ODBC error 1222 (lock timeout) even if
only one user is connected to the database.
BOL says that if "awe enabled"=1 memory is not swapped out by default.
www.microsoft.com says that if you change "set working set size" from its
default value it may involve 844 and 845 errors (page latch timeout).
So the questions are:
1. Can two simulaneously enabled options ("awe enabled" and "set working set
size") result in such conflict?
2. Can enabling set working set size result in ODBC error 1222?
Thank youI don't know if that configuration will cause that error but I doubt it.
There is no reason to have SET Working Set Size set to 1 when you have AWE
since AWE in 2000 is not dynamic to begin with. AWE also allocates all the
memory up to the Max memory setting on startup. I would turn it off and see
if the errors go away and I see no need to have in on anyway.
--
Andrew J. Kelly SQL MVP
"RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
news:CFCDD61D-ACE4-4C25-ABF1-50395C763B8E@.microsoft.com...
> Hello everybody!
> System description:
> OS: Windows Server 2003 Enterprise Edition SP1
> RAM: 16 Gb
> SQL: SQL Server 2000 Enterprise Edition SP3
> SQL Server has "awe enabled"=1 and "set working set size"=1 at the same
> time.
> "Max server memory" is limited by 12 Gb. SQLServer:MemoryManager:Total
> Server Memory counter shows that SQL Server consumes all of the 12 Gb
> memory.
> Allmost every day the client receives ODBC error 1222 (lock timeout) even
> if
> only one user is connected to the database.
> BOL says that if "awe enabled"=1 memory is not swapped out by default.
> www.microsoft.com says that if you change "set working set size" from its
> default value it may involve 844 and 845 errors (page latch timeout).
> So the questions are:
> 1. Can two simulaneously enabled options ("awe enabled" and "set working
> set
> size") result in such conflict?
> 2. Can enabling set working set size result in ODBC error 1222?
> Thank you
>|||Thank you, Mr.Kelly. Before I read you answer I had decided to act by trial
and error and I advice to turn "working set size" off. My client informed me
that the error did not disappear but it got less frequent.|||The Lock Timeout setting is infinite by default in SQL Server. IF they are
getting this message that means someone somewhere changed the setting. I
would look for any code that has SET LOCK_TIMEOUT in it and find out why and
what value they set it to.
--
Andrew J. Kelly SQL MVP
"RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
news:69717477-0287-4FB8-9F65-96168F25F2BB@.microsoft.com...
> Thank you, Mr.Kelly. Before I read you answer I had decided to act by
> trial
> and error and I advice to turn "working set size" off. My client informed
> me
> that the error did not disappear but it got less frequent.|||Yes, I see, but can only one user working with the server lock himself? I
mean there are situations when only one user works with the database but the
error occurs all the same.
"Andrew J. Kelly" wrote:
> The Lock Timeout setting is infinite by default in SQL Server. IF they are
> getting this message that means someone somewhere changed the setting. I
> would look for any code that has SET LOCK_TIMEOUT in it and find out why and
> what value they set it to.
> --
> Andrew J. Kelly SQL MVP
>
> "RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
> news:69717477-0287-4FB8-9F65-96168F25F2BB@.microsoft.com...
> > Thank you, Mr.Kelly. Before I read you answer I had decided to act by
> > trial
> > and error and I advice to turn "working set size" off. My client informed
> > me
> > that the error did not disappear but it got less frequent.
>
>|||Sure. If the user is trying to get an exclusive table lock there are lots of
things that can prevent him from getting one. I think you need to see the
code and why they are setting this and under what conditions it is
happening.
--
Andrew J. Kelly SQL MVP
"RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
news:057E64D8-ED9F-4442-9623-FECE3BDB865E@.microsoft.com...
> Yes, I see, but can only one user working with the server lock himself? I
> mean there are situations when only one user works with the database but
> the
> error occurs all the same.
> "Andrew J. Kelly" wrote:
>> The Lock Timeout setting is infinite by default in SQL Server. IF they
>> are
>> getting this message that means someone somewhere changed the setting. I
>> would look for any code that has SET LOCK_TIMEOUT in it and find out why
>> and
>> what value they set it to.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
>> news:69717477-0287-4FB8-9F65-96168F25F2BB@.microsoft.com...
>> > Thank you, Mr.Kelly. Before I read you answer I had decided to act by
>> > trial
>> > and error and I advice to turn "working set size" off. My client
>> > informed
>> > me
>> > that the error did not disappear but it got less frequent.
>>|||There is a general interface setting which allow users to set time-out (via
the setting the code receives time interval for SET LOCK_TIMEOUT statement).
The only thing I'm going to recommend is to increase the value. Thank you,
Mr.Kelly, you have been very helpful.
"Andrew J. Kelly" wrote:
> Sure. If the user is trying to get an exclusive table lock there are lots of
> things that can prevent him from getting one. I think you need to see the
> code and why they are setting this and under what conditions it is
> happening.
> --
> Andrew J. Kelly SQL MVP
>
> "RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
> news:057E64D8-ED9F-4442-9623-FECE3BDB865E@.microsoft.com...
> > Yes, I see, but can only one user working with the server lock himself? I
> > mean there are situations when only one user works with the database but
> > the
> > error occurs all the same.
> >
> > "Andrew J. Kelly" wrote:
> >
> >> The Lock Timeout setting is infinite by default in SQL Server. IF they
> >> are
> >> getting this message that means someone somewhere changed the setting. I
> >> would look for any code that has SET LOCK_TIMEOUT in it and find out why
> >> and
> >> what value they set it to.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
> >> news:69717477-0287-4FB8-9F65-96168F25F2BB@.microsoft.com...
> >> > Thank you, Mr.Kelly. Before I read you answer I had decided to act by
> >> > trial
> >> > and error and I advice to turn "working set size" off. My client
> >> > informed
> >> > me
> >> > that the error did not disappear but it got less frequent.
> >>
> >>
> >>
>
>

AWE and set working set size

Hello everybody!
System description:
OS: Windows Server 2003 Enterprise Edition SP1
RAM: 16 Gb
SQL: SQL Server 2000 Enterprise Edition SP3
SQL Server has "awe enabled"=1 and "set working set size"=1 at the same time
.
"Max server memory" is limited by 12 Gb. SQLServer:MemoryManager:Total
Server Memory counter shows that SQL Server consumes all of the 12 Gb memory
.
Allmost every day the client receives ODBC error 1222 (lock timeout) even if
only one user is connected to the database.
BOL says that if "awe enabled"=1 memory is not swapped out by default.
www.microsoft.com says that if you change "set working set size" from its
default value it may involve 844 and 845 errors (page latch timeout).
So the questions are:
1. Can two simulaneously enabled options ("awe enabled" and "set working set
size") result in such conflict?
2. Can enabling set working set size result in ODBC error 1222?
Thank youI don't know if that configuration will cause that error but I doubt it.
There is no reason to have SET Working Set Size set to 1 when you have AWE
since AWE in 2000 is not dynamic to begin with. AWE also allocates all the
memory up to the Max memory setting on startup. I would turn it off and see
if the errors go away and I see no need to have in on anyway.
Andrew J. Kelly SQL MVP
"RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
news:CFCDD61D-ACE4-4C25-ABF1-50395C763B8E@.microsoft.com...
> Hello everybody!
> System description:
> OS: Windows Server 2003 Enterprise Edition SP1
> RAM: 16 Gb
> SQL: SQL Server 2000 Enterprise Edition SP3
> SQL Server has "awe enabled"=1 and "set working set size"=1 at the same
> time.
> "Max server memory" is limited by 12 Gb. SQLServer:MemoryManager:Total
> Server Memory counter shows that SQL Server consumes all of the 12 Gb
> memory.
> Allmost every day the client receives ODBC error 1222 (lock timeout) even
> if
> only one user is connected to the database.
> BOL says that if "awe enabled"=1 memory is not swapped out by default.
> www.microsoft.com says that if you change "set working set size" from its
> default value it may involve 844 and 845 errors (page latch timeout).
> So the questions are:
> 1. Can two simulaneously enabled options ("awe enabled" and "set working
> set
> size") result in such conflict?
> 2. Can enabling set working set size result in ODBC error 1222?
> Thank you
>|||Thank you, Mr.Kelly. Before I read you answer I had decided to act by trial
and error and I advice to turn "working set size" off. My client informed me
that the error did not disappear but it got less frequent.|||The Lock Timeout setting is infinite by default in SQL Server. IF they are
getting this message that means someone somewhere changed the setting. I
would look for any code that has SET LOCK_TIMEOUT in it and find out why and
what value they set it to.
Andrew J. Kelly SQL MVP
"RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
news:69717477-0287-4FB8-9F65-96168F25F2BB@.microsoft.com...
> Thank you, Mr.Kelly. Before I read you answer I had decided to act by
> trial
> and error and I advice to turn "working set size" off. My client informed
> me
> that the error did not disappear but it got less frequent.|||Yes, I see, but can only one user working with the server lock himself? I
mean there are situations when only one user works with the database but the
error occurs all the same.
"Andrew J. Kelly" wrote:

> The Lock Timeout setting is infinite by default in SQL Server. IF they are
> getting this message that means someone somewhere changed the setting. I
> would look for any code that has SET LOCK_TIMEOUT in it and find out why a
nd
> what value they set it to.
> --
> Andrew J. Kelly SQL MVP
>
> "RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
> news:69717477-0287-4FB8-9F65-96168F25F2BB@.microsoft.com...
>
>|||Sure. If the user is trying to get an exclusive table lock there are lots of
things that can prevent him from getting one. I think you need to see the
code and why they are setting this and under what conditions it is
happening.
Andrew J. Kelly SQL MVP
"RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
news:057E64D8-ED9F-4442-9623-FECE3BDB865E@.microsoft.com...[vbcol=seagreen]
> Yes, I see, but can only one user working with the server lock himself? I
> mean there are situations when only one user works with the database but
> the
> error occurs all the same.
> "Andrew J. Kelly" wrote:
>|||There is a general interface setting which allow users to set time-out (via
the setting the code receives time interval for SET LOCK_TIMEOUT statement).
The only thing I'm going to recommend is to increase the value. Thank you,
Mr.Kelly, you have been very helpful.
"Andrew J. Kelly" wrote:

> Sure. If the user is trying to get an exclusive table lock there are lots
of
> things that can prevent him from getting one. I think you need to see the
> code and why they are setting this and under what conditions it is
> happening.
> --
> Andrew J. Kelly SQL MVP
>
> "RDBSAdmin" <RDBSAdmin@.discussions.microsoft.com> wrote in message
> news:057E64D8-ED9F-4442-9623-FECE3BDB865E@.microsoft.com...
>
>

AVG MDX Function

I have been trying to solve a calculated measure using both the sum/count methods and the avg function, but neither is working for the result that I want. We are trying to come up with an average course score based on an average test score. Example: user takes two tests (test1 twice for 50% and 100% - avg = 75% test2 once for 100%) and the course average would be (75+100)/2 = 87.5%. In our MDX we are getting (50+100+100)/3 = 83.33% instead.

We do have a hierarchy set based on course-->test-->test iteration where test iteration is each individual score. The overall average works the way you would typically thing with the sum/count method, but not based on how we want it to calculate AVG(AVG(test1 scores) + AVG(Test2 scorces)). I tried to use the AVG function, but this only works at the lowest level in the hierarchy and then appears to SUM up the results as you go up into the hierarchy (avg test1 75% test 2 100% course 175% -- not sure why the AVG works that way).

Can anyone tell me if this is even possible to get the average the way I am proposing or do I need to create some type of an aggregated take will calculations in the data warehouse to reference.

Thanks.

Seems like what you need to do is to define formula at the Test level to be Avg(Test.Children, Measures.CurrentMember), where at TestIteration level it will be Sum/Count formula. This will give you the results you are looking for.|||

I really appreciate your response and it is an honor to have you reply to this thread. I am sorry to sound stupid, but how do I define the calculations at the different levels and I am a bit confused with the Measures.CurrentMember. I guess I don't see how the testiteration level formula in the hierarchy gets passed to the other formula.

Thanks in advance.

|||

Here is a piece of MDX script which should do it:

CREATE AvgGrade = Measures.SumGrade / Measures.CountGrade;

(Test.Test.Test.MEMBERS, AvgGrade) = AVG(EXISTING Test.TestIteration.TestIteration.MEMBERS, AvgGrade);

|||

Thanks again for the quick response and I really appreciate you taking the time to assist me with this calculation. I just want to make sure I am doing this correctly. Here is what I put in the script editor:

CREATE AvgScore = Measures.[Test Scores] / Measures.[Completed Test Count];

([Fact Test Instance].[Hierarchy].[Course].[Test].Members, AvgScore) =

AVG(EXISTING [Fact Test Instance].[Hierarchy].[Course].[Test].[Iteration].Members, AvgScore)

We are currently using a hierarchy called [Hierarchy] (made up of Course-->Test-->Iteration) created off of a fact called [Fact Test Instance]. When I deploy this and drilldown to the test level I get a #VALUE!, so I am assuming I am doing something incorrectly. The other levels show the AvgScore result. Is there something wrong with my expression or does it have something to do with the solve order?

|||

The [Fact Test Instance].[Hierarchy].[Course].[Test] piece looks too long - there should be 3 parts - dimension, hierarchy, level and you have four parts. Is [Fact Test Instance] name of the dimension ? Then it should be

([Fact Test Instance].[Test].[Test].Members, AvgScore) = AVG(EXISTING [Fact Test Instance].[Iteration].[Iteration].Members, AvgScore);

Or another way to write it is

([Fact Test Instance].[Hierarchy].[Test].Members, AvgScore) = AVG([Fact Test Instance].[Hierarchy].CurrentMember.Children, AvgScore);

You will also need to do something similar about Course level too.

|||

Thanks again for your response and assistance. I modified the script to be the same as your second option and I am still getting the overall average at the test level when I browse the data within the BIDS browser using the AvgScore measure and the hierarchy called [Hierarchy] within the [Fact Test Instance] dimension. I have a student that has taken two different tests. Test1 taken twice - 33.33% and 100% for AVG 66.67% ; Test2 taken once - 90% for AVG 90% and the overall average would be (33.33+100+90)/3 = 74.44% and we would still like to get (66.67+90)/2 = 78.33%.

This definitely sounds like it should work, so is there something that I am missing or how can I validate that it is actually using this calculation at this specified level?

|||

From your description it sounds like you are looking above Test level, i.e. at Course level, since you want to see aggregate of two different tests. So you will also need

(AvgScore, [Fact Test Instance].[Hierarchy].Course.MEMBERS) = AVG([Fact Test Instance].[Hierarchy].Children, AvgScore);

|||

Once again I want to thank you so much for your assistance and you are absolutely right. I put in the additional script:

([Fact Test Instance].[Hierarchy].Course.MEMBERS, AvgScore) = AVG([Fact Test Instance].[Hierarchy].Children, AvgScore);

and I am now seeing the result that we are looking for. I am assuming that I simply have to repeat this up the hierarchy to keep this working this way. I really appreciate you taking the time to help me out with this issue.

AVG MDX Function

I have been trying to solve a calculated measure using both the sum/count methods and the avg function, but neither is working for the result that I want. We are trying to come up with an average course score based on an average test score. Example: user takes two tests (test1 twice for 50% and 100% - avg = 75% test2 once for 100%) and the course average would be (75+100)/2 = 87.5%. In our MDX we are getting (50+100+100)/3 = 83.33% instead.

We do have a hierarchy set based on course-->test-->test iteration where test iteration is each individual score. The overall average works the way you would typically thing with the sum/count method, but not based on how we want it to calculate AVG(AVG(test1 scores) + AVG(Test2 scorces)). I tried to use the AVG function, but this only works at the lowest level in the hierarchy and then appears to SUM up the results as you go up into the hierarchy (avg test1 75% test 2 100% course 175% -- not sure why the AVG works that way).

Can anyone tell me if this is even possible to get the average the way I am proposing or do I need to create some type of an aggregated take will calculations in the data warehouse to reference.

Thanks.

Seems like what you need to do is to define formula at the Test level to be Avg(Test.Children, Measures.CurrentMember), where at TestIteration level it will be Sum/Count formula. This will give you the results you are looking for.|||

I really appreciate your response and it is an honor to have you reply to this thread. I am sorry to sound stupid, but how do I define the calculations at the different levels and I am a bit confused with the Measures.CurrentMember. I guess I don't see how the testiteration level formula in the hierarchy gets passed to the other formula.

Thanks in advance.

|||

Here is a piece of MDX script which should do it:

CREATE AvgGrade = Measures.SumGrade / Measures.CountGrade;

(Test.Test.Test.MEMBERS, AvgGrade) = AVG(EXISTING Test.TestIteration.TestIteration.MEMBERS, AvgGrade);

|||

Thanks again for the quick response and I really appreciate you taking the time to assist me with this calculation. I just want to make sure I am doing this correctly. Here is what I put in the script editor:

CREATE AvgScore = Measures.[Test Scores] / Measures.[Completed Test Count];

([Fact Test Instance].[Hierarchy].[Course].[Test].Members, AvgScore) =

AVG(EXISTING [Fact Test Instance].[Hierarchy].[Course].[Test].[Iteration].Members, AvgScore)

We are currently using a hierarchy called [Hierarchy] (made up of Course-->Test-->Iteration) created off of a fact called [Fact Test Instance]. When I deploy this and drilldown to the test level I get a #VALUE!, so I am assuming I am doing something incorrectly. The other levels show the AvgScore result. Is there something wrong with my expression or does it have something to do with the solve order?

|||

The [Fact Test Instance].[Hierarchy].[Course].[Test] piece looks too long - there should be 3 parts - dimension, hierarchy, level and you have four parts. Is [Fact Test Instance] name of the dimension ? Then it should be

([Fact Test Instance].[Test].[Test].Members, AvgScore) = AVG(EXISTING [Fact Test Instance].[Iteration].[Iteration].Members, AvgScore);

Or another way to write it is

([Fact Test Instance].[Hierarchy].[Test].Members, AvgScore) = AVG([Fact Test Instance].[Hierarchy].CurrentMember.Children, AvgScore);

You will also need to do something similar about Course level too.

|||

Thanks again for your response and assistance. I modified the script to be the same as your second option and I am still getting the overall average at the test level when I browse the data within the BIDS browser using the AvgScore measure and the hierarchy called [Hierarchy] within the [Fact Test Instance] dimension. I have a student that has taken two different tests. Test1 taken twice - 33.33% and 100% for AVG 66.67% ; Test2 taken once - 90% for AVG 90% and the overall average would be (33.33+100+90)/3 = 74.44% and we would still like to get (66.67+90)/2 = 78.33%.

This definitely sounds like it should work, so is there something that I am missing or how can I validate that it is actually using this calculation at this specified level?

|||

From your description it sounds like you are looking above Test level, i.e. at Course level, since you want to see aggregate of two different tests. So you will also need

(AvgScore, [Fact Test Instance].[Hierarchy].Course.MEMBERS) = AVG([Fact Test Instance].[Hierarchy].Children, AvgScore);

|||

Once again I want to thank you so much for your assistance and you are absolutely right. I put in the additional script:

([Fact Test Instance].[Hierarchy].Course.MEMBERS, AvgScore) = AVG([Fact Test Instance].[Hierarchy].Children, AvgScore);

and I am now seeing the result that we are looking for. I am assuming that I simply have to repeat this up the hierarchy to keep this working this way. I really appreciate you taking the time to help me out with this issue.

Autoslicing not working

I have around 36 partitions in the 2005 cube. Each partition is roughly 20M fact rows.

The partitions are differentiated by a date member dimension.

When I run a query which selects by a distinct date, SSAS tries to scan all partitions (for inclusions?). This introduces a huge performance impact. It takes many seconds to run a single query.

I have aggregations designed specifically for relevant dimensions. The Profiler shows that SSAS reads from aggregations. It does not read from facts.

The storage mode is MOLAP.

Do I need to use the partition slice property ? I am seeing on forums from time to time that this attribute is not used in MOLAP.

I just need to make sure that my queries are fast.

I don't see where it could hurt to set this property, though BOL indicates this is only used by SSAS for ROLAP partitions. I thought I had heard that with MOLOP and/or HOLAP, that SSAS maintained an internal set of information regarding the contents of a slice that would handle this function for you. Maybe someone else out there could chime in on this one.

Regarding the query you specify above, are you specifying a date member from the specific cube dimension used to partition your cube? You probably are but it wasn't explicitly stated. Date dimensions are often used in a role-playing capacity which introduces some confusion.

Thanks,
Bryan

|||

can anyone clarify if the Slice property on Partition is used in MOLAP or not?

I checked info....xml files for my slicing dimension min/max values and they seem correct, however with explicit queries, SSAS still reads a bunch of partitions...