Showing posts with label dear. Show all posts
Showing posts with label dear. Show all posts

Tuesday, March 27, 2012

Delete and import data in a table in replication environment

Dear All,
I have made a replication between Server A and B. It work well
One date, the customer need to renew all data in one table. So I run a
script to delete the data then try to import such data from another outside
server. I used Sql server enterprise tool to import data,I make it
successful. But after runed the replication. I found the data have been
deleted in the table in Server B. The customer complain me that they can not
retrieve data from Server B. It make me sad.
So I just want to ask whethe the Sql server enterprise tool import
function can not triger to replication?
Regards
Jackson Chan
You don't mention the replication type, but my guess is that it is merge. If
so, if you bulk insert the rows and choose the defaults, then FIRE_TRIGGERS
is false and consequently the rows are not added to MSmerge_contents. In
this case, you need to run sp_addtabletocontents to include the rows then
resynchronise.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Wednesday, March 21, 2012

Delay in inserting records continuously into the Database

Dear All,
I am having a process which continuously inserts a record into a single
table with the following table schema:
CREATE TABLE [MessageLog] (
[MessageIndex] [int] IDENTITY (1, 1) NOT NULL ,
[SegmentCode] [smallint] NULL ,
[SetID] [smallint] NULL ,
[ScripCode] [int] NULL ,
[ClientCode] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserID] [int] NULL ,
[OrderNumber] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TimeStamp1] [int] NULL ,
[MessageType] [int] NULL ,
[ValidityTime] [int] NULL ,
[Message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I have written an SP to update this table from my code. Most of the time to
execute this SP it is taking around 1 to 2 ms, but some time (10 out of 5000
inserts) it takes around 75, 100 or even 200 milliseconds also.
Database Properites:
General:
Maintanence Plan -> None
Collation Name -> SQL_Latin1_General_CP1_C1_AS
Both for Data file and Log File:
Automatic Grow File -> Set to Yes
File Growth -> By 10 Percent
Maximum File Size -> Unrestricted File Growth
Recovery Model -> Simple
Auto Update Statistics -> Set to True
Auto Create Statistics -> Set to True
All the other settings are set to false
Compatibility Level -> Database Compatibility Level 80
Can any one help me out to get a consistent of 1 to 2 ms every time?
Thanks and Regards,
PeriSome possible causes may be:
1)The logic organising data and then passes it to SQL Server to insert
2)triggers on the table
3)Complex foreign keys
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Peri" <Peri@.newsgroups.nospam> wrote in message
news:eIuYzCcaGHA.3740@.TK2MSFTNGP03.phx.gbl...
> Dear All,
> I am having a process which continuously inserts a record into a single
> table with the following table schema:
> CREATE TABLE [MessageLog] (
> [MessageIndex] [int] IDENTITY (1, 1) NOT NULL ,
> [SegmentCode] [smallint] NULL ,
> [SetID] [smallint] NULL ,
> [ScripCode] [int] NULL ,
> [ClientCode] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UserID] [int] NULL ,
> [OrderNumber] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TimeStamp1] [int] NULL ,
> [MessageType] [int] NULL ,
> [ValidityTime] [int] NULL ,
> [Message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> I have written an SP to update this table from my code. Most of the time
to
> execute this SP it is taking around 1 to 2 ms, but some time (10 out of
5000
> inserts) it takes around 75, 100 or even 200 milliseconds also.
> Database Properites:
> General:
> Maintanence Plan -> None
> Collation Name -> SQL_Latin1_General_CP1_C1_AS
> Both for Data file and Log File:
> Automatic Grow File -> Set to Yes
> File Growth -> By 10 Percent
> Maximum File Size -> Unrestricted File Growth
> Recovery Model -> Simple
> Auto Update Statistics -> Set to True
> Auto Create Statistics -> Set to True
> All the other settings are set to false
> Compatibility Level -> Database Compatibility Level 80
> Can any one help me out to get a consistent of 1 to 2 ms every time?
> Thanks and Regards,
> Peri
>|||Hi Peri,
Thank you for using MSDN Managed Newsgroup Support.
You may need to use the SQL Profiler to trace your SP running and check why
your SQL execution will take more time than usual. Basically, you may check
the CPU usage, disk reads and writes event.
Please note that:
Performance issues can be difficult to troubleshoot and resolve in a
newsgroup setting due to the number of variables and the amount of time
required to narrow down possible causes and observe the effects. We will
assist as best as we can, but you may wish to consider contacting CSS for a
more timely resolution for these type issues.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/defaul...US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com
for regional support phone numbers.
Thank you for your patience and understanding.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "Peri" <Peri@.newsgroups.nospam>
>Subject: Delay in inserting records continuously into the Database
>Date: Thu, 27 Apr 2006 11:36:14 +0530
>Lines: 49
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
>X-RFC2646: Format=Flowed; Original
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869
>Message-ID: <eIuYzCcaGHA.3740@.TK2MSFTNGP03.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.programming
>NNTP-Posting-Host: btnl-tn-dsl-dynamic-177.10.144.59.touchtelindia.net
59.144.10.177
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.programming:595672
>X-Tomcat-NG: microsoft.public.sqlserver.programming
>Dear All,
>I am having a process which continuously inserts a record into a single
>table with the following table schema:
>CREATE TABLE [MessageLog] (
> [MessageIndex] [int] IDENTITY (1, 1) NOT NULL ,
> [SegmentCode] [smallint] NULL ,
> [SetID] [smallint] NULL ,
> [ScripCode] [int] NULL ,
> [ClientCode] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UserID] [int] NULL ,
> [OrderNumber] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TimeStamp1] [int] NULL ,
> [MessageType] [int] NULL ,
> [ValidityTime] [int] NULL ,
> [Message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>GO
>I have written an SP to update this table from my code. Most of the time
to
>execute this SP it is taking around 1 to 2 ms, but some time (10 out of
5000
>inserts) it takes around 75, 100 or even 200 milliseconds also.
>Database Properites:
>General:
>Maintanence Plan -> None
>Collation Name -> SQL_Latin1_General_CP1_C1_AS
>Both for Data file and Log File:
>Automatic Grow File -> Set to Yes
>File Growth -> By 10 Percent
>Maximum File Size -> Unrestricted File Growth
>Recovery Model -> Simple
>Auto Update Statistics -> Set to True
>Auto Create Statistics -> Set to True
>All the other settings are set to false
>Compatibility Level -> Database Compatibility Level 80
>Can any one help me out to get a consistent of 1 to 2 ms every time?
>Thanks and Regards,
>Peri
>
>|||On Thu, 27 Apr 2006 11:36:14 +0530, "Peri" <Peri@.newsgroups.nospam>
wrote:

>Most of the time to
>execute this SP it is taking around 1 to 2 ms, but some time (10 out of 500
0
>inserts) it takes around 75, 100 or even 200 milliseconds also.
Perhaps the delay is waiting for more disl file space to be allocated,
either data or log. Log in particular can be a problem as the process
must wait for completion, and the log is always growing (and growing,
and growing...)
Roy Harvey
Beacon Falls, CT

Monday, March 19, 2012

Degenerate dimension with non-numeric data in SSAS

Dear,

I have the following problem/question:

I have a fact table that contains for example all orderline data, with next to that all skey's to my dimension tables (product, customer, date, ...). Next to the measures that are included in my fact table, I was planning to also include a degenerate dimension that contains my ordernumber. So far no problem creating the star schema in a relational database. But trying to set this up in SSAS, the problem comes up that my order number is not a real number but a string (eg 'ABC12345') and as far as I know, the fact table in SSAS cannot contain any string data (or am I wrong?).

Any suggestions?

Thank you in advance for your feedback!

Kind regards,

Jürgen

You are wrong :)

|||

OK, great! Can you also let me know how to add data formatted as string in my relational fact table to my SSAS cube (without creating an additional dimension)? I already tried a lot of things, but without any positive result...

Thx in advance,

Jürgen

|||I am not sure what you mean by "add data formatted as string". Are you talking about adding a varchar measure? I can't see any scenario where you would want to do that.|||

The notion of degenerate dimension in Analysis Services is modeled by using Fact dimension. Not sure why but legal guys didn't let us use "Degenerate" in the product . Must be the case of political correctness:)

You create a new dimension and you include all additional information you would like to see. You define a relationship between your dimension and the measure group as Fact. Then using Drillthrough functionatlity you should be able to provide your users access to this additional information.

Books online should have more information for you on the Fact dimensions and on the Drillthough.

Hope that helps.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Thanks for the useful information, Edward!

Sunday, March 11, 2012

Defrag Production SQL Box

Dear all
I have a production sql box (SQL Server 2005 SP2 and Windows Server 2003
64bit) that is heavily fragmented. This is the result from the built in
defrag.exe.
To defrag a machine like this, you have published some best practices,
right? Can you provide me with a link or could you provide me with the
appropriate steps I have to take?
Thanks in advance and kind regards,
Marc
Hi Marc,
You have to take SQL offline if you wish to defrag the data\log file with
the builtin defrag. There are 3rd party utilitized that say they can do it
online but I would make sure the backups are verified. Start off with larger
data files moving forward so it does not autogrow.
Jason Massie
http://statisticsio.com
http://twitter.com/statisticsio
http://friendfeed.com/statisticsio
http://feeds.feedburner.com/statisticsio
"Marc" <Rombooth1@.community.nospam> wrote in message
news:9911B546-137C-40C2-90A5-8528AA05B614@.microsoft.com...
> Dear all
> I have a production sql box (SQL Server 2005 SP2 and Windows Server 2003
> 64bit) that is heavily fragmented. This is the result from the built in
> defrag.exe.
> To defrag a machine like this, you have published some best practices,
> right? Can you provide me with a link or could you provide me with the
> appropriate steps I have to take?
> Thanks in advance and kind regards,
> Marc
>
>
|||> You have to take SQL offline if you wish to defrag the data\log file with
> the builtin defrag.
And you shouldn't do that anyway. I would configure the defragging tool to
not look at the data/log file folders, and rely on SQL Server's own
mechanisms to determine/repair fragmentation. Fragmentation within a
database is not quite the same thing as fragmentation in a file system...
they are similar, but they are not resolved in the same way. And windows or
diskeeper cannot alert you when you should defragment a table/index, but
they could be misleading you because they think your database file is
fragmented. Frankly, it's none of their business.
A
|||The OP is talking about file fragementation.
Jason Massie
http://statisticsio.com
http://twitter.com/statisticsio
http://friendfeed.com/statisticsio
http://feeds.feedburner.com/statisticsio
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:F8C822CD-EED4-49A2-B3DE-3B8ED255B6C6@.microsoft.com...
> And you shouldn't do that anyway. I would configure the defragging tool
> to not look at the data/log file folders, and rely on SQL Server's own
> mechanisms to determine/repair fragmentation. Fragmentation within a
> database is not quite the same thing as fragmentation in a file system...
> they are similar, but they are not resolved in the same way. And windows
> or diskeeper cannot alert you when you should defragment a table/index,
> but they could be misleading you because they think your database file is
> fragmented. Frankly, it's none of their business.
> A
>
|||Yes, but you were specifically talking about the SQL Server data/log file,
no?
[vbcol=seagreen]
> The OP is talking about file fragementation.
> --
> Jason Massie
> http://statisticsio.com
> http://twitter.com/statisticsio
> http://friendfeed.com/statisticsio
> http://feeds.feedburner.com/statisticsio
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:F8C822CD-EED4-49A2-B3DE-3B8ED255B6C6@.microsoft.com...
|||Depending on what kind of storage you are using, file system fragmentation
may or may not have much of a performance impact on your SQL workloads. I
know the file system defrag tool vendors are out there scaring people. You
have to take their ads with a huge grain of salt.
The point is that if you can afford to run some tests, I'd suggest you do
that first before you commit to some massive file system degrag operations.
Linchi
"Marc" wrote:

> Dear all
> I have a production sql box (SQL Server 2005 SP2 and Windows Server 2003
> 64bit) that is heavily fragmented. This is the result from the built in
> defrag.exe.
> To defrag a machine like this, you have published some best practices,
> right? Can you provide me with a link or could you provide me with the
> appropriate steps I have to take?
> Thanks in advance and kind regards,
> Marc
>
>
|||Thanks a lot for your comments. I should be more precise here. The file
system on the SQL Box is configured like this:
C: operating system, sql server and reporting services
E: Logs
F: Relational and analytical DB of SQL Server 2005
I ran the analysis part of defrag.exe on each drive. All are heavily
fragmented. I regurlary defragment the indexes on the relational db. The
analytical db gets calculated every night.
I’m considering defragmenting only the c drive and ignoring e and f drive.
Would you agree?
Kind regards,
Marc
|||"I regurlary defragment the indexes on the relational db"
I do this inside management studio.
Regards,
Marc
|||Hi Linchi

> Depending on what kind of storage you are using,
I'm using HDs in a Raid 1 configuration for the operating system and sql
server and a Raid 1 for the log. The SQL DB's (relational and analytical) run
on a Raid 10.
Marc
|||Hi Marc,
I totally agree with Aaron's suggestions. Please separate your file system
defragmentation from your SQL Server defragmentation. You may refer to the
section "Defragmentation as a Part of Your Infrastructure" in this article:
The Desktop Files The Truth about Defragmentation
http://technet.microsoft.com/en-us/magazine/cc137767.aspx
Also when you schedule a file system defragmentation or SQL Server
defragmentation, you should also consider performance impact. Please
arrange a not busy time for this job.
Please feel free to let us know if you have any other questions or
concerns. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#
notif
ications.
Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are
best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no
rights.
================================================== =======

Defrag Production SQL Box

Dear all
I have a production sql box (SQL Server 2005 SP2 and Windows Server 2003
64bit) that is heavily fragmented. This is the result from the built in
defrag.exe.
To defrag a machine like this, you have published some best practices,
right? Can you provide me with a link or could you provide me with the
appropriate steps I have to take?
Thanks in advance and kind regards,
MarcHi Marc,
You have to take SQL offline if you wish to defrag the data\log file with
the builtin defrag. There are 3rd party utilitized that say they can do it
online but I would make sure the backups are verified. Start off with larger
data files moving forward so it does not autogrow.
--
Jason Massie
http://statisticsio.com
http://twitter.com/statisticsio
http://friendfeed.com/statisticsio
http://feeds.feedburner.com/statisticsio
"Marc" <Rombooth1@.community.nospam> wrote in message
news:9911B546-137C-40C2-90A5-8528AA05B614@.microsoft.com...
> Dear all
> I have a production sql box (SQL Server 2005 SP2 and Windows Server 2003
> 64bit) that is heavily fragmented. This is the result from the built in
> defrag.exe.
> To defrag a machine like this, you have published some best practices,
> right? Can you provide me with a link or could you provide me with the
> appropriate steps I have to take?
> Thanks in advance and kind regards,
> Marc
>
>|||> You have to take SQL offline if you wish to defrag the data\log file with
> the builtin defrag.
And you shouldn't do that anyway. I would configure the defragging tool to
not look at the data/log file folders, and rely on SQL Server's own
mechanisms to determine/repair fragmentation. Fragmentation within a
database is not quite the same thing as fragmentation in a file system...
they are similar, but they are not resolved in the same way. And windows or
diskeeper cannot alert you when you should defragment a table/index, but
they could be misleading you because they think your database file is
fragmented. Frankly, it's none of their business.
A|||The OP is talking about file fragementation.
--
Jason Massie
http://statisticsio.com
http://twitter.com/statisticsio
http://friendfeed.com/statisticsio
http://feeds.feedburner.com/statisticsio
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:F8C822CD-EED4-49A2-B3DE-3B8ED255B6C6@.microsoft.com...
>> You have to take SQL offline if you wish to defrag the data\log file with
>> the builtin defrag.
> And you shouldn't do that anyway. I would configure the defragging tool
> to not look at the data/log file folders, and rely on SQL Server's own
> mechanisms to determine/repair fragmentation. Fragmentation within a
> database is not quite the same thing as fragmentation in a file system...
> they are similar, but they are not resolved in the same way. And windows
> or diskeeper cannot alert you when you should defragment a table/index,
> but they could be misleading you because they think your database file is
> fragmented. Frankly, it's none of their business.
> A
>|||Yes, but you were specifically talking about the SQL Server data/log file,
no?
> The OP is talking about file fragementation.
> --
> Jason Massie
> http://statisticsio.com
> http://twitter.com/statisticsio
> http://friendfeed.com/statisticsio
> http://feeds.feedburner.com/statisticsio
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:F8C822CD-EED4-49A2-B3DE-3B8ED255B6C6@.microsoft.com...
>> You have to take SQL offline if you wish to defrag the data\log file
>> with the builtin defrag.|||Depending on what kind of storage you are using, file system fragmentation
may or may not have much of a performance impact on your SQL workloads. I
know the file system defrag tool vendors are out there scaring people. You
have to take their ads with a huge grain of salt.
The point is that if you can afford to run some tests, I'd suggest you do
that first before you commit to some massive file system degrag operations.
Linchi
"Marc" wrote:
> Dear all
> I have a production sql box (SQL Server 2005 SP2 and Windows Server 2003
> 64bit) that is heavily fragmented. This is the result from the built in
> defrag.exe.
> To defrag a machine like this, you have published some best practices,
> right? Can you provide me with a link or could you provide me with the
> appropriate steps I have to take?
> Thanks in advance and kind regards,
> Marc
>
>|||Thanks a lot for your comments. I should be more precise here. The file
system on the SQL Box is configured like this:
C: operating system, sql server and reporting services
E: Logs
F: Relational and analytical DB of SQL Server 2005
I ran the analysis part of defrag.exe on each drive. All are heavily
fragmented. I regurlary defragment the indexes on the relational db. The
analytical db gets calculated every night.
Iâ'm considering defragmenting only the c drive and ignoring e and f drive.
Would you agree?
Kind regards,
Marc|||"I regurlary defragment the indexes on the relational db"
I do this inside management studio.
Regards,
Marc|||Hi Linchi
> Depending on what kind of storage you are using,
I'm using HDs in a Raid 1 configuration for the operating system and sql
server and a Raid 1 for the log. The SQL DB's (relational and analytical) run
on a Raid 10.
Marc|||Hi Marc,
I totally agree with Aaron's suggestions. Please separate your file system
defragmentation from your SQL Server defragmentation. You may refer to the
section "Defragmentation as a Part of Your Infrastructure" in this article:
The Desktop Files The Truth about Defragmentation
http://technet.microsoft.com/en-us/magazine/cc137767.aspx
Also when you schedule a file system defragmentation or SQL Server
defragmentation, you should also consider performance impact. Please
arrange a not busy time for this job.
Please feel free to let us know if you have any other questions or
concerns. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
===========================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#
notif
ications.
Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are
best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================This posting is provided "AS IS" with no warranties, and confers no
rights.
=========================================================|||Hi Charles
Thank you for your information - this is really helpful.
I am still not sure, what I should do with my disks (post above). I'm sure
about the system disk c: - I will defrag it with defrag.exe.
But with the log drive and database drive I'm not sure what to do:
Should I run first I sql script that rebuilts all indexes and when this is
done run defrag.exe? Or should I do it the other way around, meaning run
defrag.exe and run the sql script for rebuild indexes? Or should I use only
the sql script or only defrag.exe?
Thanks for your help,
Kind regards,
Marc|||Hi Marc,
If the log drive and the database drive are only dedicated for your SQL
Server, you can just use SQL Server to rebuild/defragment the indexes (see
DBCC INDEXDEFRAG/DBCC DBREINDEX). You need not to run defrag.exe for the
two drives in this case. I recommend that you leave them for SQL Server. If
you still want to defragment them with defrag.exe, please first perform a
full database backup.
Please feel free to let me know if you have any other questions or
concerns. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
=========================================================This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================|||Thanks a lot Charles!
Best regards,
Marc|||>If the log drive and the database drive are only dedicated for your SQL
Server, you can just use SQL Server to rebuild/defragment the indexes (see
DBCC INDEXDEFRAG/DBCC DBREINDEX).
You could use the DMV sys.dm_db_index_physical_stats to dynamically rebuild
or reorganize your indexes based on the fragmentation. I have a stored
procedure that could help you with this.
http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
http://blog.ola.hallengren.com/_attachments/3440068/Documentation.html
Ola Hallengren
http://ola.hallengren.com
"Marc" wrote:
> Thanks a lot Charles!
> Best regards,
> Marc|||Thanks Ola!
Will use this code.
Best Regards,
Marc

Friday, March 9, 2012

Defining Alerts

Dear All,
Recently all our production databases were marked
as 'Suspect', anyway after a bit of work it was repaired.
However the only reason we found this out was the users
couldn't get in.
What I would like to do is define some alerts such
as 'database Production is not working', or replication is
no longer working.
I know where to do this, what I cannot find is background
information on what alert to raise (I have tried BOL on
Microsoft). Can anyone provide me background info, for
instance does alert 025 mean any error to a database which
stops it working ?
Thanks
J
Hi
Alert 025 is reased when a Error, with severity level 25 occurs.
You must check for error levels 20 and higher, those are very critcal.
The DB suspect error is Error 926, Severity Level 14
Regards
Mike
"Julie" wrote:

> Dear All,
> Recently all our production databases were marked
> as 'Suspect', anyway after a bit of work it was repaired.
> However the only reason we found this out was the users
> couldn't get in.
> What I would like to do is define some alerts such
> as 'database Production is not working', or replication is
> no longer working.
> I know where to do this, what I cannot find is background
> information on what alert to raise (I have tried BOL on
> Microsoft). Can anyone provide me background info, for
> instance does alert 025 mean any error to a database which
> stops it working ?
> Thanks
> J
>
|||Thanks Mike,
Is there any background information I can read up on ?
J

>--Original Message--
>Hi
>Alert 025 is reased when a Error, with severity level 25
occurs.
>You must check for error levels 20 and higher, those are
very critcal.[vbcol=seagreen]
>The DB suspect error is Error 926, Severity Level 14
>Regards
>Mike
>"Julie" wrote:
repaired.[vbcol=seagreen]
is[vbcol=seagreen]
background[vbcol=seagreen]
which
>.
>
|||Hi
http://msdn.microsoft.com/library/de...ar_cs_6x0l.asp
There is not much other infromation out there apart from what is in BOL.
Regards
Mike
"Julie" wrote:

> Thanks Mike,
> Is there any background information I can read up on ?
> J
>
> occurs.
> very critcal.
> repaired.
> is
> background
> which
>
|||Thanks Mike

>--Original Message--
>Hi
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/architec/8_ar_cs_6x0l.asp
>There is not much other infromation out there apart from
what is in BOL.[vbcol=seagreen]
>Regards
>Mike
>"Julie" wrote:
25[vbcol=seagreen]
are[vbcol=seagreen]
users[vbcol=seagreen]
replication[vbcol=seagreen]
on[vbcol=seagreen]
for
>.
>

Defining Alerts

Dear All,
Recently all our production databases were marked
as 'Suspect', anyway after a bit of work it was repaired.
However the only reason we found this out was the users
couldn't get in.
What I would like to do is define some alerts such
as 'database Production is not working', or replication is
no longer working.
I know where to do this, what I cannot find is background
information on what alert to raise (I have tried BOL on
Microsoft). Can anyone provide me background info, for
instance does alert 025 mean any error to a database which
stops it working ?
Thanks
JHi
Alert 025 is reased when a Error, with severity level 25 occurs.
You must check for error levels 20 and higher, those are very critcal.
The DB suspect error is Error 926, Severity Level 14
Regards
Mike
"Julie" wrote:
> Dear All,
> Recently all our production databases were marked
> as 'Suspect', anyway after a bit of work it was repaired.
> However the only reason we found this out was the users
> couldn't get in.
> What I would like to do is define some alerts such
> as 'database Production is not working', or replication is
> no longer working.
> I know where to do this, what I cannot find is background
> information on what alert to raise (I have tried BOL on
> Microsoft). Can anyone provide me background info, for
> instance does alert 025 mean any error to a database which
> stops it working ?
> Thanks
> J
>|||Thanks Mike,
Is there any background information I can read up on ?
J
>--Original Message--
>Hi
>Alert 025 is reased when a Error, with severity level 25
occurs.
>You must check for error levels 20 and higher, those are
very critcal.
>The DB suspect error is Error 926, Severity Level 14
>Regards
>Mike
>"Julie" wrote:
>> Dear All,
>> Recently all our production databases were marked
>> as 'Suspect', anyway after a bit of work it was
repaired.
>> However the only reason we found this out was the users
>> couldn't get in.
>> What I would like to do is define some alerts such
>> as 'database Production is not working', or replication
is
>> no longer working.
>> I know where to do this, what I cannot find is
background
>> information on what alert to raise (I have tried BOL on
>> Microsoft). Can anyone provide me background info, for
>> instance does alert 025 mean any error to a database
which
>> stops it working ?
>> Thanks
>> J
>.
>|||Hi
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_cs_6x0l.asp
There is not much other infromation out there apart from what is in BOL.
Regards
Mike
"Julie" wrote:
> Thanks Mike,
> Is there any background information I can read up on ?
> J
>
> >--Original Message--
> >Hi
> >
> >Alert 025 is reased when a Error, with severity level 25
> occurs.
> >
> >You must check for error levels 20 and higher, those are
> very critcal.
> >
> >The DB suspect error is Error 926, Severity Level 14
> >
> >Regards
> >Mike
> >
> >"Julie" wrote:
> >
> >> Dear All,
> >> Recently all our production databases were marked
> >> as 'Suspect', anyway after a bit of work it was
> repaired.
> >> However the only reason we found this out was the users
> >> couldn't get in.
> >>
> >> What I would like to do is define some alerts such
> >> as 'database Production is not working', or replication
> is
> >> no longer working.
> >>
> >> I know where to do this, what I cannot find is
> background
> >> information on what alert to raise (I have tried BOL on
> >> Microsoft). Can anyone provide me background info, for
> >> instance does alert 025 mean any error to a database
> which
> >> stops it working ?
> >>
> >> Thanks
> >> J
> >>
> >.
> >
>|||Thanks Mike
>--Original Message--
>Hi
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/architec/8_ar_cs_6x0l.asp
>There is not much other infromation out there apart from
what is in BOL.
>Regards
>Mike
>"Julie" wrote:
>> Thanks Mike,
>> Is there any background information I can read up on ?
>> J
>>
>> >--Original Message--
>> >Hi
>> >
>> >Alert 025 is reased when a Error, with severity level
25
>> occurs.
>> >
>> >You must check for error levels 20 and higher, those
are
>> very critcal.
>> >
>> >The DB suspect error is Error 926, Severity Level 14
>> >
>> >Regards
>> >Mike
>> >
>> >"Julie" wrote:
>> >
>> >> Dear All,
>> >> Recently all our production databases were marked
>> >> as 'Suspect', anyway after a bit of work it was
>> repaired.
>> >> However the only reason we found this out was the
users
>> >> couldn't get in.
>> >>
>> >> What I would like to do is define some alerts such
>> >> as 'database Production is not working', or
replication
>> is
>> >> no longer working.
>> >>
>> >> I know where to do this, what I cannot find is
>> background
>> >> information on what alert to raise (I have tried BOL
on
>> >> Microsoft). Can anyone provide me background info,
for
>> >> instance does alert 025 mean any error to a database
>> which
>> >> stops it working ?
>> >>
>> >> Thanks
>> >> J
>> >>
>> >.
>> >
>.
>

Defining Alerts

Dear All,
Recently all our production databases were marked
as 'Suspect', anyway after a bit of work it was repaired.
However the only reason we found this out was the users
couldn't get in.
What I would like to do is define some alerts such
as 'database Production is not working', or replication is
no longer working.
I know where to do this, what I cannot find is background
information on what alert to raise (I have tried BOL on
Microsoft). Can anyone provide me background info, for
instance does alert 025 mean any error to a database which
stops it working ?
Thanks
JHi
Alert 025 is reased when a Error, with severity level 25 occurs.
You must check for error levels 20 and higher, those are very critcal.
The DB suspect error is Error 926, Severity Level 14
Regards
Mike
"Julie" wrote:

> Dear All,
> Recently all our production databases were marked
> as 'Suspect', anyway after a bit of work it was repaired.
> However the only reason we found this out was the users
> couldn't get in.
> What I would like to do is define some alerts such
> as 'database Production is not working', or replication is
> no longer working.
> I know where to do this, what I cannot find is background
> information on what alert to raise (I have tried BOL on
> Microsoft). Can anyone provide me background info, for
> instance does alert 025 mean any error to a database which
> stops it working ?
> Thanks
> J
>|||Thanks Mike,
Is there any background information I can read up on ?
J

>--Original Message--
>Hi
>Alert 025 is reased when a Error, with severity level 25
occurs.
>You must check for error levels 20 and higher, those are
very critcal.
>The DB suspect error is Error 926, Severity Level 14
>Regards
>Mike
>"Julie" wrote:
>
repaired.[vbcol=seagreen]
is[vbcol=seagreen]
background[vbcol=seagreen]
which[vbcol=seagreen]
>.
>|||Hi
http://msdn.microsoft.com/library/d...br />
6x0l.asp
There is not much other infromation out there apart from what is in BOL.
Regards
Mike
"Julie" wrote:

> Thanks Mike,
> Is there any background information I can read up on ?
> J
>
> occurs.
> very critcal.
> repaired.
> is
> background
> which
>|||Thanks Mike

>--Original Message--
>Hi
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/architec/8_ar_cs_6x0l.asp
>There is not much other infromation out there apart from
what is in BOL.
>Regards
>Mike
>"Julie" wrote:
>
25[vbcol=seagreen]
are[vbcol=seagreen]
users[vbcol=seagreen]
replication[vbcol=seagreen]
on[vbcol=seagreen]
for[vbcol=seagreen]
>.
>

Tuesday, February 14, 2012

Default Resolver

Dear all,
I'm just trying to do merge replication between to DB with Dialup connection.
- If any conflict occur, does the default resolver let the publisher win? or should we use another resolver? how?
- What will happen if the connection totally broken during running synchronization?
Thanks in advance.
ech
Yes, by default the publisher wins a conflict with a subscriber and the
first subscriber takes the place of the publisher in any conflicts with
other subscribers. You can use global conflict resolution (priority numbers
assigned to each subscriber), one of the many inbuilt conflict resolution
algorithms or use a custom resolver. If you want to use a custom resolver
there is an average price stored procedure in the samples which is good to
start with, or you might want to make a COM resolver.
If the connection is broken ('Network Failure') just restart the merge agent
once the connection is up and it will continue.
HTH,
Paul Ibison
|||It's helpful. Thanks Paul.