Database Clustering

Dear Guys.
Can you guys help me regarding Database Server Clustering and Database
server federation
Thanks
NOOR
Sure. What is the problem? You might want to post any clustering related
problems to .clustering group. sql-server-performance.com has clustering
related content. Books Online and KB have a lot of information on clustering
too.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Noor" <noor@.ngsol.com> wrote in message
news:OxgLR7phEHA.3148@.TK2MSFTNGP10.phx.gbl...
Dear Guys.
Can you guys help me regarding Database Server Clustering and Database
server federation
Thanks
NOOR
|||Thanks Narayana but can you give me some real world example like where we
have to use Database Server Clustering and Database server federation
Thanks
NOOR
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ekcbvEqhEHA.712@.tk2msftngp13.phx.gbl...
> Sure. What is the problem? You might want to post any clustering related
> problems to .clustering group. sql-server-performance.com has clustering
> related content. Books Online and KB have a lot of information on
clustering
> too.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Noor" <noor@.ngsol.com> wrote in message
> news:OxgLR7phEHA.3148@.TK2MSFTNGP10.phx.gbl...
> Dear Guys.
> Can you guys help me regarding Database Server Clustering and Database
> server federation
> Thanks
> NOOR
>
>
|||Clustering is used for ensuring high availability of SQL Server, while
fererations are meant for distributed processing, to improve performance.
See "Failover Clustering" in SQL Server 2000 Books Online. Also see
"Designing Federated Database Servers" in Books Online.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Noor" <noor@.ngsol.com> wrote in message
news:eF6Q0uqhEHA.2764@.TK2MSFTNGP11.phx.gbl...
Thanks Narayana but can you give me some real world example like where we
have to use Database Server Clustering and Database server federation
Thanks
NOOR
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ekcbvEqhEHA.712@.tk2msftngp13.phx.gbl...
> Sure. What is the problem? You might want to post any clustering related
> problems to .clustering group. sql-server-performance.com has clustering
> related content. Books Online and KB have a lot of information on
clustering
> too.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "Noor" <noor@.ngsol.com> wrote in message
> news:OxgLR7phEHA.3148@.TK2MSFTNGP10.phx.gbl...
> Dear Guys.
> Can you guys help me regarding Database Server Clustering and Database
> server federation
> Thanks
> NOOR
>
>

database blocked - URGENT!!

Hi guys,

we have a database here and something happened which causes a database block. We tried to run the 'sp_who' 'active' command to see the spid which locked the database, and we found out that some transaction is blocking another transaction. The following is the sample data results from the sp_who 'active'

spid ecid status loginame hostname blk dbname cmd

52 0 sleeping HOSTING\SQLMonitor BLUE2 185 tempdb INSERT
53 0 sleeping sa 10.10.10.106 185 mfgq_live SELECT
56 0 sleeping sa 10.10.10.106 175 mfgq_live UPDATE
57 0 sleeping sa 10.10.10.143 185 mfgq_live SELECT

We killed all spid which casuse the blocking, but they are keep on coming.

Does anybody have any idea on what casuses this problem or a teporary solution for this? Please help.

Thx

To find the cause, you need to find the lead blocker (the first spid that starts the blocking chain), find out what that spid is executing and take a look at the locks the spid is taking out on whatever objects and then you go from there. The following article will walk you through the process to do this:

http://support.microsoft.com/?id=224453

-Sue

Database access

Is there any way to query SQL Server to see the last time a given database w
as access?
thanks guys Hi,
SQL Server will not store this information into any tables. Only way is to
run the profiler or write you own triger to monitor the master..sysprocesses
table
and log the audit information into a table. Later you could query the table
to get the last access date and time for the database.
Thanks
Hari
MCDBA
"Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
news:1978F2D2-6D5D-45E9-8DAC-F9C548B5E0A3@.microsoft.com...
> Is there any way to query SQL Server to see the last time a given database
was access?
> thanks guys |||No.
What I do is run a stored proc executed via SQL Agent that polls the
sysprocesses table hourly for db connections and I write the info to a table
I've defined.
"Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
news:1978F2D2-6D5D-45E9-8DAC-F9C548B5E0A3@.microsoft.com...
> Is there any way to query SQL Server to see the last time a given database
was access?
> thanks guys |||Hari,
Thanks so much for the help. I think I like the trigger idea. In any case, i
t will sharpen my scripting skills, such as they are.
Sara Beth
"Hari Prasad" wrote:

> Hi,
> SQL Server will not store this information into any tables. Only way is to
> run the profiler or write you own triger to monitor the master..sysprocess
es
> table
> and log the audit information into a table. Later you could query the tabl
e
> to get the last access date and time for the database.
> Thanks
> Hari
> MCDBA
> "Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
> news:1978F2D2-6D5D-45E9-8DAC-F9C548B5E0A3@.microsoft.com...
> was access?
>
>|||Simon,
Thank you for the suggestion. I see from your answer and Hari's that this wi
ll be a wee bit of work for me. Ah, but that's ok. It'll keep me busy.
Sara Beth
"Simon" wrote:

> No.
> What I do is run a stored proc executed via SQL Agent that polls the
> sysprocesses table hourly for db connections and I write the info to a tab
le
> I've defined.
> "Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
> news:1978F2D2-6D5D-45E9-8DAC-F9C548B5E0A3@.microsoft.com...
> was access?
>
>|||I believe the sysprocesses table is a virtual table and triggers cannot be
defined on them.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:e0SwCIbbEHA.1248@.TK2MSFTNGP11.phx.gbl...
> Hi,
> SQL Server will not store this information into any tables. Only way is to
> run the profiler or write you own triger to monitor the
master..sysprocesses
> table
> and log the audit information into a table. Later you could query the
table
> to get the last access date and time for the database.
> Thanks
> Hari
> MCDBA
> "Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
> news:1978F2D2-6D5D-45E9-8DAC-F9C548B5E0A3@.microsoft.com...
database[vbcol=seagreen]
> was access?
>|||Hi Simon
This is correct, but not the whole story.
Triggers cannot be created on ANY system table, not just the virtual tables.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Simon" <me@.me.me> wrote in message
news:OtiIOXcbEHA.252@.TK2MSFTNGP10.phx.gbl...
> I believe the sysprocesses table is a virtual table and triggers cannot be
> defined on them.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:e0SwCIbbEHA.1248@.TK2MSFTNGP11.phx.gbl...
to[vbcol=seagreen]
> master..sysprocesses
> table
> database
>|||Hi Kalen... "Simon" is my pen name... you may remember me
from our discussions via email a couple weeks back revolving
around the aging out of statistics that Microsoft changed on you
after your book went to press... You may remember me!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O$7w0rcbEHA.644@.tk2msftngp13.phx.gbl...
> Hi Simon
> This is correct, but not the whole story.
> Triggers cannot be created on ANY system table, not just the virtual
tables.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Simon" <me@.me.me> wrote in message
> news:OtiIOXcbEHA.252@.TK2MSFTNGP10.phx.gbl...
be[vbcol=seagreen]
is[vbcol=seagreen]
> to
>|||Hi Simon / Kalen,
Thanks for pointing out my error.
Sara Beth,
Like Simon suggested, you could create a table with Login name ,
database_name, login_time ,last_batch_time in some database and populate the
table using sysprocesses. Schedule the acticvity using sql agent jobs
Create the below table in some database.
---
Create table audit_database(login varchar(256),
dbname varchar(128),
login_time datetime,
last_batch_time datetime)
Schedule the below script to execute using SQL Agent -- Jobs
---
insert into dbname..audit_database select loginame
,db_name(dbid),login_time,last_batch from master..sysprocesses
change the dbname.. withoriginal database name in which the audit_database
table resides.
Use the below query to get the last login name for database wise.
----
select dbname, max(login_time) as last_login_time from audit_database
group by dbname
Thanks
Hari
MCDBA
"Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
news:0ACD84B6-3BAF-4C58-AC57-20D17606B0DF@.microsoft.com...
> Simon,
> Thank you for the suggestion. I see from your answer and Hari's that
this will be a wee bit of work for me. Ah, but that's ok. It'll keep me
busy.[vbcol=seagreen]
> Sara Beth
> "Simon" wrote:
>
table[vbcol=seagreen]
database[vbcol=seagreen]

Database access

Is there any way to query SQL Server to see the last time a given database was access?
thanks guys
Hi,
SQL Server will not store this information into any tables. Only way is to
run the profiler or write you own triger to monitor the master..sysprocesses
table
and log the audit information into a table. Later you could query the table
to get the last access date and time for the database.
Thanks
Hari
MCDBA
"Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
news:1978F2D2-6D5D-45E9-8DAC-F9C548B5E0A3@.microsoft.com...
> Is there any way to query SQL Server to see the last time a given database
was access?
> thanks guys
|||No.
What I do is run a stored proc executed via SQL Agent that polls the
sysprocesses table hourly for db connections and I write the info to a table
I've defined.
"Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
news:1978F2D2-6D5D-45E9-8DAC-F9C548B5E0A3@.microsoft.com...
> Is there any way to query SQL Server to see the last time a given database
was access?
> thanks guys
|||Hari,
Thanks so much for the help. I think I like the trigger idea. In any case, it will sharpen my scripting skills, such as they are.
Sara Beth
"Hari Prasad" wrote:

> Hi,
> SQL Server will not store this information into any tables. Only way is to
> run the profiler or write you own triger to monitor the master..sysprocesses
> table
> and log the audit information into a table. Later you could query the table
> to get the last access date and time for the database.
> Thanks
> Hari
> MCDBA
> "Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
> news:1978F2D2-6D5D-45E9-8DAC-F9C548B5E0A3@.microsoft.com...
> was access?
>
>
|||Simon,
Thank you for the suggestion. I see from your answer and Hari's that this will be a wee bit of work for me. Ah, but that's ok. It'll keep me busy.
Sara Beth
"Simon" wrote:

> No.
> What I do is run a stored proc executed via SQL Agent that polls the
> sysprocesses table hourly for db connections and I write the info to a table
> I've defined.
> "Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
> news:1978F2D2-6D5D-45E9-8DAC-F9C548B5E0A3@.microsoft.com...
> was access?
>
>
|||I believe the sysprocesses table is a virtual table and triggers cannot be
defined on them.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:e0SwCIbbEHA.1248@.TK2MSFTNGP11.phx.gbl...
> Hi,
> SQL Server will not store this information into any tables. Only way is to
> run the profiler or write you own triger to monitor the
master..sysprocesses
> table
> and log the audit information into a table. Later you could query the
table[vbcol=seagreen]
> to get the last access date and time for the database.
> Thanks
> Hari
> MCDBA
> "Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
> news:1978F2D2-6D5D-45E9-8DAC-F9C548B5E0A3@.microsoft.com...
database
> was access?
>
|||Hi Simon
This is correct, but not the whole story.
Triggers cannot be created on ANY system table, not just the virtual tables.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Simon" <me@.me.me> wrote in message
news:OtiIOXcbEHA.252@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> I believe the sysprocesses table is a virtual table and triggers cannot be
> defined on them.
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:e0SwCIbbEHA.1248@.TK2MSFTNGP11.phx.gbl...
to
> master..sysprocesses
> table
> database
>
|||Hi Kalen... "Simon" is my pen name... you may remember me
from our discussions via email a couple weeks back revolving
around the aging out of statistics that Microsoft changed on you
after your book went to press... You may remember me!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O$7w0rcbEHA.644@.tk2msftngp13.phx.gbl...
> Hi Simon
> This is correct, but not the whole story.
> Triggers cannot be created on ANY system table, not just the virtual
tables.[vbcol=seagreen]
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Simon" <me@.me.me> wrote in message
> news:OtiIOXcbEHA.252@.TK2MSFTNGP10.phx.gbl...
be[vbcol=seagreen]
is
> to
>
|||Hi Simon / Kalen,
Thanks for pointing out my error.
Sara Beth,
Like Simon suggested, you could create a table with Login name ,
database_name, login_time ,last_batch_time in some database and populate the
table using sysprocesses. Schedule the acticvity using sql agent jobs
Create the below table in some database.
Create table audit_database(login varchar(256),
dbname varchar(128),
login_time datetime,
last_batch_time datetime)
Schedule the below script to execute using SQL Agent -- Jobs
insert into dbname..audit_database select loginame
,db_name(dbid),login_time,last_batch from master..sysprocesses
change the dbname.. withoriginal database name in which the audit_database
table resides.
Use the below query to get the last login name for database wise.
select dbname, max(login_time) as last_login_time from audit_database
group by dbname
Thanks
Hari
MCDBA
"Sara Beth" <Sara Beth@.discussions.microsoft.com> wrote in message
news:0ACD84B6-3BAF-4C58-AC57-20D17606B0DF@.microsoft.com...
> Simon,
> Thank you for the suggestion. I see from your answer and Hari's that
this will be a wee bit of work for me. Ah, but that's ok. It'll keep me
busy.[vbcol=seagreen]
> Sara Beth
> "Simon" wrote:
table[vbcol=seagreen]
database[vbcol=seagreen]

Data types and its flaws?

Hey guys,

I have a table with the following information.

A1,A2,A3,A10,A11,A12,A14,A20,A21
B1,B2,B3,B10,B11,B12,B14,B20,B21
C1,C2,C3,C10,C11,C12,C14,C20,C21

Now if I assign this field as a char,varchar,text field then when sorting it turns out
A1,A10,A11,A12,A14,A2,A20 etc. So in order to go around this issue, I placed a blank ' ' (space) before the items that are less than 10 so that it'll sort as
' A1', ' A2', ' A3' , 'A10' etc. However, now the problem is since the B and C series also have this space it actually sorts as
' A1', ' A2', ' A3', ' B1', ' B2' ... 'A11','A12' etc. Now, I know why it is doing that. I'm trying to figure out if there is a solution to this issue. I've considered having two fields instead, one for the letter and another for the number, However, I need to display them together and I also need to do some reporting on these figures, so even if i concatenate the two fields together, it'll be a string field and sort by the same way. Any ideas?

Here is your table and sample data.

CREATE TABLE [dbo].[colSort$](
[col1] [nvarchar](5) )

INSERT [dbo].[colSort$] ([col1]) VALUES (N'A1')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'A2')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'A3')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'A10')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'A11')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'A12')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'A14')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'A20')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'A21')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'B1')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'B2')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'B3')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'B10')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'B11')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'B12')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'B14')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'B20')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'B21')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'C1')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'C2')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'C3')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'C10')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'C11')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'C12')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'C14')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'C20')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'C21')

Solution:

SELECT col1FROM dbo.colSort$

ORDERBYLeft(col1,1),CAST(RIGHT(col1,Len(col1)-1)asINT)