any way to prevent SSAS memory from paging?

This morning msmdsrv.exe looked like the following in Task Manager:

Memory Usage: 600,000 K

VM Size: 2,500,000 K

If I understand correctly, that means that a good deal of SSAS memory has been swapped to disk by the OS. Is there any way to prevent this? (I have read about using the Lock Pages In Memory privilege at http://msdn2.microsoft.com/en-us/library/ms179301.aspx which lets SQL prevent the OS from paging sqlservr.exe memory. I don't suppose there's an equivalent SSAS setting.)

In particular, the symptom I'm seeing is that after we finish processing, all that paged memory has to be loaded back into memory before the transaction can be committed. Committing the transaction is usually very quick. But when most of the SSAS process memory is pages, it takes quite a while, during which I can see the Memory Usage number in Task Manager growing. (I don't think it's a blocked transaction commit.)

Looks like maybe there is a way to do this in SP2. Can anyone explain the PreAllocate setting further?

http://www.sdsqlug.org/presentations/November2006/November_2006_64-bit_SQL_Server.pdf (page 36)

Any way to insert page breaks between tables?

I have about 5 tables on a report.
Is there a way to insert a break between the tables to prevent tables
from being chopped on a page break?
Thank you!Pull up table properties dialog and check "insert page break after this
table" checkbox and also "fit this table on one page if possible" checkbox.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"The Whistler" <sharris@.SLeasynews.com> wrote in message
news:bg48i09pocfef9h7ot6rmklf02uas8dl49@.4ax.com...
> I have about 5 tables on a report.
> Is there a way to insert a break between the tables to prevent tables
> from being chopped on a page break?
> Thank you!
>sql

any way to check the duplicated rows in destination before loading data?

Hi. As the title, I am try to figure out how to write script to prevent duplicated rows before loading data from couple csv files to the OLE database table.
Another quick question, when I use Data Conversion to convert data from string to datetime or decimal type, it always return error like potential data loss.

For your first question, probably an easier approach is to use a SORT transform to remove duplicate records before loading data into your destination.

For the other question, I think it's a matter of which format you used in your source strings. Firstly pls be aware we use locale information when doing converting strings to date types or decimals. Secondly, when converting string to date types, you have two options: normal conversion and fast-parse conversion. Normal conversion supports standard oledb formats while fastparse supports ISO 8601. (fastparse option is on the DataConversion output columns)

You'll need to get more detailed helps on this from SQLServer Books On Line. e.g. For fastparse, pls refer ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/bed6e2c1-791a-4fa1-b29f-cbfdd1fa8d39.htm

thanks

wenyang

|||Thanks for your fast response. My first question is to load date from CSV files to the table, but don't insert the duplicated rows which are already existed in the table.|||

I see. you want to avoid inserting rows which'll duplicate rows in your existing destination table. In that case, you can do a lookup first, then leading only those "failing" rows to destination. Remember to set Lookup's error flow handling to Redirect.

thanks

wenyang

Any other patch need to installed besides SP3a?

Hi,
I installed SP3a for MS SQL 2000. Do I need to install any other tools to prevent from hacking?
e.g. IIS Lockdown Tool for windows 2000 server IIS 5
Thanks for advice
You should check all your servers with these security tools.
http://www.microsoft.com/downloads/d...displaylang=en
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Tom" <kerocow@.yahoo.com> wrote in message
news:EEC32889-130C-4327-8AA2-A1F41BFD1430@.microsoft.com...
> Hi,
> I installed SP3a for MS SQL 2000. Do I need to install any other tools to
prevent from hacking?
> e.g. IIS Lockdown Tool for windows 2000 server IIS 5
> Thanks for advice
|||Tom,
You might want to install the MS03-031 security update.
http://www.microsoft.com/technet/sec.../MS03-031.mspx
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Tom wrote:
> Hi,
> I installed SP3a for MS SQL 2000. Do I need to install any other tools to prevent from hacking?
> e.g. IIS Lockdown Tool for windows 2000 server IIS 5
> Thanks for advice
|||Thanks for help

Any other patch need to installed besides SP3a?

Hi,
I installed SP3a for MS SQL 2000. Do I need to install any other tools to pr
event from hacking?
e.g. IIS Lockdown Tool for Windows 2000 server IIS 5
Thanks for adviceYou should check all your servers with these security tools.
CDE4D933600&displaylang=en" target="_blank">http://www.microsoft.com/downloads/...&displaylang=en
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Tom" <kerocow@.yahoo.com> wrote in message
news:EEC32889-130C-4327-8AA2-A1F41BFD1430@.microsoft.com...
> Hi,
> I installed SP3a for MS SQL 2000. Do I need to install any other tools to
prevent from hacking?
> e.g. IIS Lockdown Tool for Windows 2000 server IIS 5
> Thanks for advice|||Tom,
You might want to install the MS03-031 security update.
http://www.microsoft.com/technet/se...n/MS03-031.mspx
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Tom wrote:
> Hi,
> I installed SP3a for MS SQL 2000. Do I need to install any other tools to
prevent from hacking?
> e.g. IIS Lockdown Tool for Windows 2000 server IIS 5
> Thanks for advice|||Thanks for help

ANSWER: Re: SQLVDI error in eventlogs, caused by ntbackup

I wanted to prevent ntbackup from locking my databases during the nightly backup, so I excluded the ...\Data folder from the backup set. However, I think that ntbackup is backing up the databases during the volume shadow copy phase of the backup, even though their folder was excluded from the backup set (I verified this setting in the .bks file). Although the lock only lasts a few seconds, the error message is worrisome and I would prefer to skip the databases completely. How can I stop the volume shadow copy from doing this?

Below is the the sequence of events leading up to the error, most recent first:

Event Type: Error
Event Source: SQLVDI
Event Category: None
Event ID: 1
Date: 3/23/2007
Time: 3:01:49 AM
Description:
SQLVDI: Loc=CVDS::Cleanup. Desc=Release(ClientAliveMutex). ErrorCode=(288)Attempt to release mutex not owned by caller.
. Process=8684. Thread=7556. Client. Instance=. VD=.

Event Type: Information
Event Source: MSSQLSERVER
Event Category: (6)
Event ID: 18264
Date: 3/23/2007
Time: 3:01:48 AM
Description:
Database backed up. Database: master, creation date(time): 2007/03/12(15:08:23), pages dumped: 1, first LSN: 268:352:37, last LSN: 269:24:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{66DC3082-FB76-4312-AD74-4BDAD9FC7209}1'}). This is an informational message only. No user action is required.

Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 3198
Date: 3/23/2007
Time: 3:01:47 AM
Description:
I/O was resumed on database master. No user action is required.

Event Type: Information
Event Source: ESENT
Event Category: ShadowCopy
Event ID: 2003
Date: 3/23/2007
Time: 3:01:47 AM
Description:
lsass (956) Shadow copy 24 freeze stopped.

Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 3197
Date: 3/23/2007
Time: 3:01:45 AM
Description:
I/O is frozen on database master. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.

Event Type: Information
Event Source: ESENT
Event Category: ShadowCopy
Event ID: 2001
Date: 3/23/2007
Time: 3:01:45 AM
Description:
lsass (956) Shadow copy 24 freeze started.

Event Type: Information
Event Source: NTBackup
Event Category: None
Event ID: 8018
Date: 3/23/2007
Time: 3:00:14 AM
Description:
Begin Operation

This can be done programmatically, but I don't think that ntbackup has the user-accessable controls to accomlish this.

VSS is designed to make a snapshot of a volume with all data on it being guaranteed consistent.

ntbackup then takes that snapshot and backs up what you tell it to (in this case, excluding master)

So, the exclusion is happening after VSS has already done its work.

In reality, the VSS freeze doesn't stop transactions from continuing against pages in the SQL buffer pool.

The only thing which is frozen is IO between the bufferpool and disk. 99% of the time, the apps won't be impacted at all. For that 1%, the interruption is on the order of 1-2 seconds. If your user database isn't on this volume, the applications won't be impacted at all.

|||

Thanks

I tried to say in my message that I understand that the VSS is doing its job - The part that bothers me is that there is an Error in the event log.

Is the error normal and expected? Since it appears to have no impact, I would like to know how to stop the error from occuring, or at least prevent it from creating error-level severity in the event log.

Since I am backing up my databases using sql agent jobs in the sql server, I don't really need a shadow backup.

I am also uncertain if the shadow backups are going to have any negative impact on my sql server differential backups. There is a knowledgebase article regarding a hotfix that was not clear on that issue.

|||

My apologies, I missed that.

This is a bug for which there is a hotfix on the way, 50000895.

The good news is that the message is harmless, and the operation succeeds.

Kevin

|||

Thanks very much for the reply, I'll be looking forward to the hotfix!

|||Where can I find the hotfix for download?
|||hotfix going to release soon?|||

Kevin,

This was helpful as I'm seeing this same error but while using the SQLVDI through a hotsnap application. I'll also be waiting for the hotfix on this but it's good to know there is no harm with the message.

Thx.

|||I am getting the same error when trying to backup with Symantec Backup Exec 11d. The backup works the first night and the second night it crashes the backup engine. Any ideas when the hotfix will be released? Thanks.|||I have SQLVDI errors which appear to come from Backup Exec 11d also.|||

Hello,

Do you know when the hotfix be expected, we have the same problem.

Nico

|||I downloaded and installed the KB921896 SP2 and it still have the same problem.|||

The hotfix is 934396 released MAY 14th 2007 ref:- http://support.microsoft.com/default.aspx?scid=kb;en-us;934396

However, it seems you have to contact MS to get it!

How do you do that without paying support costs?

Would appreciate any help on obtaining this.

Thanks.

|||

I've found that apparently it doesn't cost anything - all you do is phone and quote the HF number for an email link/password.

Cheers.

|||Microsoft wrote:

Hotfix information
A supported hotfix is now available from Microsoft. However, it is intended to correct only the problem that is described in this article. Apply it only to systems that are experiencing this specific problem. This hotfix may receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next SQL Server 2005 service pack that contains this hotfix.
To resolve this problem immediately, contact Microsoft Customer Support Services to obtain the hotfix. For a complete list of Microsoft Customer Support Services telephone numbers and information about support costs, visit the following Microsoft Web site:
http://support.microsoft.com/contactus/?ws=support

Have someone already tried this hotfix?