Last time publication/subscription replicated data

Is there somewhere that I can tell when the last time either the publication or better yet a subscrition replicated data (in a system table or view maybe)?

I want to set up monitoring to make sure I am aware if something for some reason does not replicate

OH and it is on SQL 2005 enterprise... an

If synchronization is processed by SQL Server Agent Job, the best choice is to view job state.

Here is example how to do it, if there is Merge publication with Merge Push Subscriptions:

1. Call sp_MSEnum_Merge_subscriptions in Distribution database to determine job ID's of the Push subscriptions:

Get PushSubscriptions Dataset

exec distribution..sp_MSEnum_Merge_subscriptions 'publisher_server','pub_database','publication_name'

2. The returning dataset contains a column named [job_id], that contains Agent Job ID in Binary format. You must convert it to hexadecimal string format (for example, 0x8392E005C4941A4C9A279A68EFC9F3D8)

3. Execute sp_help_job in MSDB database to retrieve current job state:

Get Job State

exec msdb..sp_help_job @.job_id = 0x8392E005C4941A4C9A279A68EFC9F3D8 , @.job_aspect ='job'

The SP returns dataset, containing number of information on the job: date_created, last_run_date, last_run_time, next_run_date, next_run_time, last_run_outcome etc...

I think, this will be helpful!

Regards,

|||

Also check out MSMerge_history in the distribution database. Fill history of the job in there.

Martin

Last time publication/subscription replicated data

Is there somewhere that I can tell when the last time either the publication or better yet a subscrition replicated data (in a system table or view maybe)?

I want to set up monitoring to make sure I am aware if something for some reason does not replicate

OH and it is on SQL 2005 enterprise... an

If synchronization is processed by SQL Server Agent Job, the best choice is to view job state.

Here is example how to do it, if there is Merge publication with Merge Push Subscriptions:

1. Call sp_MSEnum_Merge_subscriptions in Distribution database to determine job ID's of the Push subscriptions:

Get PushSubscriptions Dataset

exec distribution..sp_MSEnum_Merge_subscriptions 'publisher_server', 'pub_database', 'publication_name'

2. The returning dataset contains a column named [job_id], that contains Agent Job ID in Binary format. You must convert it to hexadecimal string format (for example, 0x8392E005C4941A4C9A279A68EFC9F3D8)

3. Execute sp_help_job in MSDB database to retrieve current job state:

Get Job State

exec msdb..sp_help_job @.job_id = 0x8392E005C4941A4C9A279A68EFC9F3D8 , @.job_aspect = 'job'

The SP returns dataset, containing number of information on the job: date_created, last_run_date, last_run_time, next_run_date, next_run_time, last_run_outcome etc...

I think, this will be helpful!

Regards,

|||

Also check out MSMerge_history in the distribution database. Fill history of the job in there.

Martin

sql

Last logon time

How do I find out the last time a SQL Server login id was
used? Is there a system table that will show last login
time? Thanks.The easiest way would be to enable auditing for successfull logins on SQL.
This will print the info you want in both the NT Application event log and
the SQL Errorlog. Otherwise, you could use Profiler to capture
login/logoff events and write them to your own table.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Check the actual SQL logs themselves. I believe by default SS retains 6
copies of the log since sp_cycle_errorlog was run or the instance was
restarted.
hth
Eric
"Jammin" <anonymous@.discussions.microsoft.com> wrote in message
news:079f01c3bf39$06e26fd0$a001280a@.phx.gbl...

quote:

---

> How do I find out the last time a SQL Server login id was
> used? Is there a system table that will show last login
> time? Thanks.

---

Last Logon Date

Is there a system table that shows the last logon date for a login?
Or is there a way to setup an alert or something to keep track of logon date
for a login?
Thanks,
Ken
Ken,
You can trace this using Profiler or you can set up auditing for successful
and/or failed login attempts on the EM - Server Properties - Security tab -
Audit Level.
HTH
Jerry
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:806D9945-7352-49F3-B43C-97B3F838F9F2@.microsoft.com...
> Is there a system table that shows the last logon date for a login?
> Or is there a way to setup an alert or something to keep track of logon
> date
> for a login?
> Thanks,
> Ken
|||I setup an alert for error 18453 Login succeeded trusted connection.
This works fine -- I get notified by an email. But I want to execute a job
from this alert to save the login information into a table so that I can
remove the login if it has not been used for a number of months. This is
part of our security audit.
My question is -- how can I pass the login info from an alert to a job?
Thanks,
Ken
"Jerry Spivey" wrote:

> Ken,
> You can trace this using Profiler or you can set up auditing for successful
> and/or failed login attempts on the EM - Server Properties - Security tab -
> Audit Level.
> HTH
> Jerry
> "Ken" <Ken@.discussions.microsoft.com> wrote in message
> news:806D9945-7352-49F3-B43C-97B3F838F9F2@.microsoft.com...
>
>

Last Logon Date

Is there a system table that shows the last logon date for a login?
Or is there a way to setup an alert or something to keep track of logon date
for a login?
Thanks,
KenKen,
You can trace this using Profiler or you can set up auditing for successful
and/or failed login attempts on the EM - Server Properties - Security tab -
Audit Level.
HTH
Jerry
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:806D9945-7352-49F3-B43C-97B3F838F9F2@.microsoft.com...
> Is there a system table that shows the last logon date for a login?
> Or is there a way to setup an alert or something to keep track of logon
> date
> for a login?
> Thanks,
> Ken|||I setup an alert for error 18453 Login succeeded trusted connection.
This works fine -- I get notified by an email. But I want to execute a job
from this alert to save the login information into a table so that I can
remove the login if it has not been used for a number of months. This is
part of our security audit.
My question is -- how can I pass the login info from an alert to a job?
Thanks,
Ken
"Jerry Spivey" wrote:

> Ken,
> You can trace this using Profiler or you can set up auditing for successfu
l
> and/or failed login attempts on the EM - Server Properties - Security tab
-
> Audit Level.
> HTH
> Jerry
> "Ken" <Ken@.discussions.microsoft.com> wrote in message
> news:806D9945-7352-49F3-B43C-97B3F838F9F2@.microsoft.com...
>
>

Last Logon Date

Is there a system table that shows the last logon date for a login?
Or is there a way to setup an alert or something to keep track of logon date
for a login?
Thanks,
KenKen,
You can trace this using Profiler or you can set up auditing for successful
and/or failed login attempts on the EM - Server Properties - Security tab -
Audit Level.
HTH
Jerry
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:806D9945-7352-49F3-B43C-97B3F838F9F2@.microsoft.com...
> Is there a system table that shows the last logon date for a login?
> Or is there a way to setup an alert or something to keep track of logon
> date
> for a login?
> Thanks,
> Ken|||I setup an alert for error 18453 Login succeeded trusted connection.
This works fine -- I get notified by an email. But I want to execute a job
from this alert to save the login information into a table so that I can
remove the login if it has not been used for a number of months. This is
part of our security audit.
My question is -- how can I pass the login info from an alert to a job?
Thanks,
Ken
"Jerry Spivey" wrote:
> Ken,
> You can trace this using Profiler or you can set up auditing for successful
> and/or failed login attempts on the EM - Server Properties - Security tab -
> Audit Level.
> HTH
> Jerry
> "Ken" <Ken@.discussions.microsoft.com> wrote in message
> news:806D9945-7352-49F3-B43C-97B3F838F9F2@.microsoft.com...
> > Is there a system table that shows the last logon date for a login?
> > Or is there a way to setup an alert or something to keep track of logon
> > date
> > for a login?
> >
> > Thanks,
> > Ken
>
>

Last login in sql server

Hello,
Is there any way to tell when a user last logged into sql server/system
without auditing set? Are there any system tables or sp that will give me
that information? Thanks.
--
New SQL Server DBANewbie,
For current processes:
SELECT LOGINAME,LOGIN_TIME FROM MASTER..SYSPROCESSES
For logins in the past...not that I'm aware of without using built-in
auditing, a custom auditing solution or third-party auditing software. A
shot in the dark, you might be able to determine this by evaulating the
t-log backups in the past with a third-party log viewer tool.
HTH
Jerry
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:7692EDE5-82DC-4538-91A0-0E380D41862F@.microsoft.com...
> Hello,
> Is there any way to tell when a user last logged into sql server/system
> without auditing set? Are there any system tables or sp that will give me
> that information? Thanks.
> --
> New SQL Server DBA

Last login in sql server

Hello,
Is there any way to tell when a user last logged into sql server/system
without auditing set? Are there any system tables or sp that will give me
that information? Thanks.
--
New SQL Server DBANewbie,
For current processes:
SELECT LOGINAME,LOGIN_TIME FROM MASTER..SYSPROCESSES
For logins in the past...not that I'm aware of without using built-in
auditing, a custom auditing solution or third-party auditing software. A
shot in the dark, you might be able to determine this by evaulating the
t-log backups in the past with a third-party log viewer tool.
HTH
Jerry
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:7692EDE5-82DC-4538-91A0-0E380D41862F@.microsoft.com...
> Hello,
> Is there any way to tell when a user last logged into sql server/system
> without auditing set? Are there any system tables or sp that will give me
> that information? Thanks.
> --
> New SQL Server DBA

Last login in sql server

Hello,
Is there any way to tell when a user last logged into sql server/system
without auditing set? Are there any system tables or sp that will give me
that information? Thanks.
New SQL Server DBA
Newbie,
For current processes:
SELECT LOGINAME,LOGIN_TIME FROM MASTER..SYSPROCESSES
For logins in the past...not that I'm aware of without using built-in
auditing, a custom auditing solution or third-party auditing software. A
shot in the dark, you might be able to determine this by evaulating the
t-log backups in the past with a third-party log viewer tool.
HTH
Jerry
"Newbie" <Newbie@.discussions.microsoft.com> wrote in message
news:7692EDE5-82DC-4538-91A0-0E380D41862F@.microsoft.com...
> Hello,
> Is there any way to tell when a user last logged into sql server/system
> without auditing set? Are there any system tables or sp that will give me
> that information? Thanks.
> --
> New SQL Server DBA

large varchar update fails on sql 2000 server, works on dev. machine

A production system (server 2003 enterprise edition) sql 2000 server,
service pack 4 won't update a sort of large varchar field, which works
fine on my development machine, (xp pro sql 2000).
try this in the query analyzer:
CREATE TABLE [dbo].[tbl_why] (
[pr_id] [int] IDENTITY (1, 1) NOT NULL ,
[pr_user_snapshot] [varchar] (2000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
--create a row by entering something in the snapshot field like
"what the fu!$*!?"
--test this
declare @.pr_user_snapshot varchar(2000)
set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
associated with these applications:</td><td class=InputLabel
>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
starting on:</td><td class=InputLabel >JAN 1</td></
tr><tr><td>There is a cost per item of:</td><td class=InputLabel
>200.00 </td></tr><tr><td>There is a fixed cost of:</td><td
class=InputLabel > </td></tr><tr><td>There is a minimum cost of:</
td><td class=InputLabel > </td></tr><tr><td>There is a maximum
cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
tr><tr><td>Proceeds applied to Financial Account:</td><td
class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
applied to Financial Account:</td><td class=InputLabel >20256200
Building Legalization (Penalty) </td></tr>'
print len(@.pr_user_snapshot)
UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id =
1
...not brain surgery...
It updates to a blank, not null result. Somewhere around 800
characters the thing starts working on the server. Increasing the
field size makes no difference.
Is there some server setting I've missed that is set on my dev. box?
I can't find anything.
What's with this' Is this me'
ThanksOn Apr 11, 3:26 pm, "tkent" <t...@.aceinc.com> wrote:
> A production system (server 2003 enterprise edition) sql 2000 server,
> service pack 4 won't update a sort of large varchar field, which works
> fine on my development machine, (xp pro sql 2000).
> try this in the query analyzer:
> CREATE TABLE [dbo].[tbl_why] (
> [pr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [pr_user_snapshot] [varchar] (2000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> --create a row by entering something in the snapshot field like
> "what the fu!$*!?"
> --test this
> declare @.pr_user_snapshot varchar(2000)
> set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
> class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
> associated with these applications:</td><td class=InputLabel>Elevator</td>
</tr><tr><td>The renewal cycle is:</td><td
> class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
> starting on:</td><td class=InputLabel >JAN 1</td></
> tr><tr><td>There is a cost per item of:</td><td class=InputLabel>200.00&am
p;nbsp;</td></tr><tr><td>There is a fixed cost of:</td><td
> class=InputLabel > </td></tr><tr><td>There is a minimum cost of:<
/
> td><td class=InputLabel > </td></tr><tr><td>There is a maximum
> cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
> basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
> colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
> Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
> td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
> Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
> colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
> td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
> td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
> td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
> tr><tr><td>Proceeds applied to Financial Account:</td><td
> class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
> applied to Financial Account:</td><td class=InputLabel >20256200
> Building Legalization (Penalty) </td></tr>'
> print len(@.pr_user_snapshot)
> UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id =
> 1
> --
> ...not brain surgery...
> It updates to a blank, not null result. Somewhere around 800
> characters the thing starts working on the server. Increasing the
> field size makes no difference.
> Is there some server setting I've missed that is set on my dev. box?
> I can't find anything.
> What's with this' Is this me'
> Thanks
[vbcol=seagreen]
Perhaps, did you notice that the table is empty? This means you can't
update can you?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||On Apr 11, 3:26 pm, "tkent" <t...@.aceinc.com> wrote:
> A production system (server 2003 enterprise edition) sql 2000 server,
> service pack 4 won't update a sort of large varchar field, which works
> fine on my development machine, (xp pro sql 2000).
> try this in the query analyzer:
> CREATE TABLE [dbo].[tbl_why] (
> [pr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [pr_user_snapshot] [varchar] (2000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> --create a row by entering something in the snapshot field like
> "what the fu!$*!?"
> --test this
> declare @.pr_user_snapshot varchar(2000)
> set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
> class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
> associated with these applications:</td><td class=InputLabel>Elevator</td>
</tr><tr><td>The renewal cycle is:</td><td
> class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
> starting on:</td><td class=InputLabel >JAN 1</td></
> tr><tr><td>There is a cost per item of:</td><td class=InputLabel>200.00&am
p;nbsp;</td></tr><tr><td>There is a fixed cost of:</td><td
> class=InputLabel > </td></tr><tr><td>There is a minimum cost of:<
/
> td><td class=InputLabel > </td></tr><tr><td>There is a maximum
> cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
> basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
> colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
> Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
> td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
> Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
> colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
> td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
> td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
> td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
> tr><tr><td>Proceeds applied to Financial Account:</td><td
> class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
> applied to Financial Account:</td><td class=InputLabel >20256200
> Building Legalization (Penalty) </td></tr>'
> print len(@.pr_user_snapshot)
> UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id =
> 1
> --
> ...not brain surgery...
> It updates to a blank, not null result. Somewhere around 800
> characters the thing starts working on the server. Increasing the
> field size makes no difference.
> Is there some server setting I've missed that is set on my dev. box?
> I can't find anything.
> What's with this' Is this me'
> Thanks
I added this before the update
insert tbl_why
select 'bla'
and had no problem
So again are you sure the table is not empty?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks for checking.
I am sure the table is not empty.
As I said, it will start updating at around 800 characters, but the
string I'm using is showing a len of around 1550.
Are YOU on a server? - this works for me on my local machine but not
on either of 2 servers (as described) I need to get this going on.
Seems like a setting but what?

> I added this before the update
> insert tbl_why
> select 'bla'
> and had no problem
> So again are you sure the table is not empty?|||Script the entire DDL for the table, including triggers. I suspect either a
poorly written trigger or multiple tables with the same name but different
owners. Both are a frequent cause of unexpected behavior. Your own
statements did not fully qualify the table.
If you still can't figure it out, then I suggest you create/run a complete
script to reproduce the problem. This includes the table creation DDL,
which you yourself run on the "server" in question and verify as acting in
the manner described. A script similar to the one your posted (but with
an insert statement and fully qualified tablenames) works correctly on 2000
sp3a. I seriously doubt that the version of sql server or the OS has
anything to do with the issue.

large varchar update fails on sql 2000 server, works on dev. machine

A production system (server 2003 enterprise edition) sql 2000 server,
service pack 4 won't update a sort of large varchar field, which works
fine on my development machine, (xp pro sql 2000).
try this in the query analyzer:
CREATE TABLE [dbo].[tbl_why] (
[pr_id] [int] IDENTITY (1, 1) NOT NULL ,
[pr_user_snapshot] [varchar] (2000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
--create a row by entering something in the snapshot field like
"what the fu!$*!?"
--test this
declare @.pr_user_snapshot varchar(2000)
set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
associated with these applications:</td><td class=InputLabel
>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
starting on:</td><td class=InputLabel >JAN&nbsp;1</td></
tr><tr><td>There is a cost per item of:</td><td class=InputLabel
>200.00&nbsp;</td></tr><tr><td>There is a fixed cost of:</td><td
class=InputLabel >&nbsp;</td></tr><tr><td>There is a minimum cost of:</
td><td class=InputLabel >&nbsp;</td></tr><tr><td>There is a maximum
cost of:</td><td class=InputLabel >&nbsp;</td></tr><tr><td>Late charge
basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
tr><tr><td>Proceeds applied to Financial Account:</td><td
class=InputLabel >20255001 Elevator&nbsp;</td></tr><tr><td>Penalties
applied to Financial Account:</td><td class=InputLabel >20256200
Building Legalization (Penalty)&nbsp;</td></tr>'
print len(@.pr_user_snapshot)
UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id =
1
...not brain surgery...
It updates to a blank, not null result. Somewhere around 800
characters the thing starts working on the server. Increasing the
field size makes no difference.
Is there some server setting I've missed that is set on my dev. box?
I can't find anything.
What's with this? Is this me?
Thanks
On Apr 11, 3:26 pm, "tkent" <t...@.aceinc.com> wrote:
> A production system (server 2003 enterprise edition) sql 2000 server,
> service pack 4 won't update a sort of large varchar field, which works
> fine on my development machine, (xp pro sql 2000).
> try this in the query analyzer:
> CREATE TABLE [dbo].[tbl_why] (
> [pr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [pr_user_snapshot] [varchar] (2000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> --create a row by entering something in the snapshot field like
> "what the fu!$*!?"
> --test this
> declare @.pr_user_snapshot varchar(2000)
> set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
> class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
> associated with these applications:</td><td class=InputLabel>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
> class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
> starting on:</td><td class=InputLabel >JAN&nbsp;1</td></
> tr><tr><td>There is a cost per item of:</td><td class=InputLabel>200.00&nbsp;</td></tr><tr><td>There is a fixed cost of:</td><td
> class=InputLabel >&nbsp;</td></tr><tr><td>There is a minimum cost of:</
> td><td class=InputLabel >&nbsp;</td></tr><tr><td>There is a maximum
> cost of:</td><td class=InputLabel >&nbsp;</td></tr><tr><td>Late charge
> basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
> colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
> Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
> td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
> Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
> colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
> td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
> td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
> td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
> tr><tr><td>Proceeds applied to Financial Account:</td><td
> class=InputLabel >20255001 Elevator&nbsp;</td></tr><tr><td>Penalties
> applied to Financial Account:</td><td class=InputLabel >20256200
> Building Legalization (Penalty)&nbsp;</td></tr>'
> print len(@.pr_user_snapshot)
> UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id =
> 1
> --
> ...not brain surgery...
> It updates to a blank, not null result. Somewhere around 800
> characters the thing starts working on the server. Increasing the
> field size makes no difference.
> Is there some server setting I've missed that is set on my dev. box?
> I can't find anything.
> What's with this? Is this me?
> Thanks
[vbcol=seagreen]
Perhaps, did you notice that the table is empty? This means you can't
update can you?
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||On Apr 11, 3:26 pm, "tkent" <t...@.aceinc.com> wrote:
> A production system (server 2003 enterprise edition) sql 2000 server,
> service pack 4 won't update a sort of large varchar field, which works
> fine on my development machine, (xp pro sql 2000).
> try this in the query analyzer:
> CREATE TABLE [dbo].[tbl_why] (
> [pr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [pr_user_snapshot] [varchar] (2000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> --create a row by entering something in the snapshot field like
> "what the fu!$*!?"
> --test this
> declare @.pr_user_snapshot varchar(2000)
> set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
> class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
> associated with these applications:</td><td class=InputLabel>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
> class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
> starting on:</td><td class=InputLabel >JAN&nbsp;1</td></
> tr><tr><td>There is a cost per item of:</td><td class=InputLabel>200.00&nbsp;</td></tr><tr><td>There is a fixed cost of:</td><td
> class=InputLabel >&nbsp;</td></tr><tr><td>There is a minimum cost of:</
> td><td class=InputLabel >&nbsp;</td></tr><tr><td>There is a maximum
> cost of:</td><td class=InputLabel >&nbsp;</td></tr><tr><td>Late charge
> basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
> colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
> Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
> td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
> Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
> colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
> td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
> td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
> td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
> tr><tr><td>Proceeds applied to Financial Account:</td><td
> class=InputLabel >20255001 Elevator&nbsp;</td></tr><tr><td>Penalties
> applied to Financial Account:</td><td class=InputLabel >20256200
> Building Legalization (Penalty)&nbsp;</td></tr>'
> print len(@.pr_user_snapshot)
> UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id =
> 1
> --
> ...not brain surgery...
> It updates to a blank, not null result. Somewhere around 800
> characters the thing starts working on the server. Increasing the
> field size makes no difference.
> Is there some server setting I've missed that is set on my dev. box?
> I can't find anything.
> What's with this? Is this me?
> Thanks
I added this before the update
insert tbl_why
select 'bla'
and had no problem
So again are you sure the table is not empty?
Denis the SQL Menace
http://sqlservercode.blogspot.com/
|||Thanks for checking.
I am sure the table is not empty.
As I said, it will start updating at around 800 characters, but the
string I'm using is showing a len of around 1550.
Are YOU on a server? - this works for me on my local machine but not
on either of 2 servers (as described) I need to get this going on.
Seems like a setting but what?

> I added this before the update
> insert tbl_why
> select 'bla'
> and had no problem
> So again are you sure the table is not empty?
|||Script the entire DDL for the table, including triggers. I suspect either a
poorly written trigger or multiple tables with the same name but different
owners. Both are a frequent cause of unexpected behavior. Your own
statements did not fully qualify the table.
If you still can't figure it out, then I suggest you create/run a complete
script to reproduce the problem. This includes the table creation DDL,
which you yourself run on the "server" in question and verify as acting in
the manner described. A script similar to the one your posted (but with
an insert statement and fully qualified tablenames) works correctly on 2000
sp3a. I seriously doubt that the version of sql server or the OS has
anything to do with the issue.

large varchar update fails on sql 2000 server, works on dev. machine

A production system (server 2003 enterprise edition) sql 2000 server,
service pack 4 won't update a sort of large varchar field, which works
fine on my development machine, (xp pro sql 2000).
try this in the query analyzer:
CREATE TABLE [dbo].[tbl_why] (
[pr_id] [int] IDENTITY (1, 1) NOT NULL ,
[pr_user_snapshot] [varchar] (2000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
--create a row by entering something in the snapshot field like
"what the fu!$*!?"
--test this
declare @.pr_user_snapshot varchar(2000)
set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
associated with these applications:</td><td class=InputLabel
>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
starting on:</td><td class=InputLabel >JAN 1</td></
tr><tr><td>There is a cost per item of:</td><td class=InputLabel
>200.00 </td></tr><tr><td>There is a fixed cost of:</td><td
class=InputLabel > </td></tr><tr><td>There is a minimum cost of:</
td><td class=InputLabel > </td></tr><tr><td>There is a maximum
cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
tr><tr><td>Proceeds applied to Financial Account:</td><td
class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
applied to Financial Account:</td><td class=InputLabel >20256200
Building Legalization (Penalty) </td></tr>'
print len(@.pr_user_snapshot)
UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id = 1
--
...not brain surgery...
It updates to a blank, not null result. Somewhere around 800
characters the thing starts working on the server. Increasing the
field size makes no difference.
Is there some server setting I've missed that is set on my dev. box?
I can't find anything.
What's with this' Is this me'
ThanksOn Apr 11, 3:26 pm, "tkent" <t...@.aceinc.com> wrote:
> A production system (server 2003 enterprise edition) sql 2000 server,
> service pack 4 won't update a sort of large varchar field, which works
> fine on my development machine, (xp pro sql 2000).
> try this in the query analyzer:
> CREATE TABLE [dbo].[tbl_why] (
> [pr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [pr_user_snapshot] [varchar] (2000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> --create a row by entering something in the snapshot field like
> "what the fu!$*!?"
> --test this
> declare @.pr_user_snapshot varchar(2000)
> set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
> class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
> associated with these applications:</td><td class=InputLabel>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
> class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
> starting on:</td><td class=InputLabel >JAN 1</td></
> tr><tr><td>There is a cost per item of:</td><td class=InputLabel>200.00 </td></tr><tr><td>There is a fixed cost of:</td><td
> class=InputLabel > </td></tr><tr><td>There is a minimum cost of:</
> td><td class=InputLabel > </td></tr><tr><td>There is a maximum
> cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
> basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
> colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
> Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
> td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
> Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
> colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
> td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
> td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
> td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
> tr><tr><td>Proceeds applied to Financial Account:</td><td
> class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
> applied to Financial Account:</td><td class=InputLabel >20256200
> Building Legalization (Penalty) </td></tr>'
> print len(@.pr_user_snapshot)
> UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id => 1
> --
> ...not brain surgery...
> It updates to a blank, not null result. Somewhere around 800
> characters the thing starts working on the server. Increasing the
> field size makes no difference.
> Is there some server setting I've missed that is set on my dev. box?
> I can't find anything.
> What's with this' Is this me'
> Thanks
>>What's with this' Is this me'
Perhaps, did you notice that the table is empty? This means you can't
update can you?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||On Apr 11, 3:26 pm, "tkent" <t...@.aceinc.com> wrote:
> A production system (server 2003 enterprise edition) sql 2000 server,
> service pack 4 won't update a sort of large varchar field, which works
> fine on my development machine, (xp pro sql 2000).
> try this in the query analyzer:
> CREATE TABLE [dbo].[tbl_why] (
> [pr_id] [int] IDENTITY (1, 1) NOT NULL ,
> [pr_user_snapshot] [varchar] (2000) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> --create a row by entering something in the snapshot field like
> "what the fu!$*!?"
> --test this
> declare @.pr_user_snapshot varchar(2000)
> set @.pr_user_snapshot = '<tr><td>This renewal type named:</td><td
> class=InputLabel >Annual Elevator Permit</td></tr><tr><td>is
> associated with these applications:</td><td class=InputLabel>Elevator</td></tr><tr><td>The renewal cycle is:</td><td
> class=InputLabel >Fixed Starting Date</td></tr><tr><td>Annual renewal,
> starting on:</td><td class=InputLabel >JAN 1</td></
> tr><tr><td>There is a cost per item of:</td><td class=InputLabel>200.00 </td></tr><tr><td>There is a fixed cost of:</td><td
> class=InputLabel > </td></tr><tr><td>There is a minimum cost of:</
> td><td class=InputLabel > </td></tr><tr><td>There is a maximum
> cost of:</td><td class=InputLabel > </td></tr><tr><td>Late charge
> basis:</td><td class=InputLabel >Per Item</td></tr><tr><td
> colspan=2><table border=0 width=100%><tr><td colspan=4>1st Late
> Window</td></tr><tr><td>1 Day</td><td>And Later</td><td>$200.00</
> td><td>Late fee per elevator</td></tr><tr><td colspan=4>2nd Late
> Window</td></tr><tr><td></td><td></td><td>$</td><td></td></tr><tr><td
> colspan=4>3rd Late Window</td></tr><tr><td></td><td></td><td>$</
> td><td></td></tr><tr><td colspan=4>4th Late Window</td></tr><tr><td></
> td><td></td><td>$</td><td></td></tr><tr><td colspan=4>5th Late Window</
> td></tr><tr><td></td><td></td><td>$</td><td></td></tr></table></td></
> tr><tr><td>Proceeds applied to Financial Account:</td><td
> class=InputLabel >20255001 Elevator </td></tr><tr><td>Penalties
> applied to Financial Account:</td><td class=InputLabel >20256200
> Building Legalization (Penalty) </td></tr>'
> print len(@.pr_user_snapshot)
> UPDATE tbl_why SET pr_user_snapshot = @.pr_user_snapshot WHERE pr_id => 1
> --
> ...not brain surgery...
> It updates to a blank, not null result. Somewhere around 800
> characters the thing starts working on the server. Increasing the
> field size makes no difference.
> Is there some server setting I've missed that is set on my dev. box?
> I can't find anything.
> What's with this' Is this me'
> Thanks
I added this before the update
insert tbl_why
select 'bla'
and had no problem
So again are you sure the table is not empty?
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks for checking.
I am sure the table is not empty.
As I said, it will start updating at around 800 characters, but the
string I'm using is showing a len of around 1550.
Are YOU on a server? - this works for me on my local machine but not
on either of 2 servers (as described) I need to get this going on.
Seems like a setting but what?
> I added this before the update
> insert tbl_why
> select 'bla'
> and had no problem
> So again are you sure the table is not empty?|||Script the entire DDL for the table, including triggers. I suspect either a
poorly written trigger or multiple tables with the same name but different
owners. Both are a frequent cause of unexpected behavior. Your own
statements did not fully qualify the table.
If you still can't figure it out, then I suggest you create/run a complete
script to reproduce the problem. This includes the table creation DDL,
which you yourself run on the "server" in question and verify as acting in
the manner described. A script similar to the one your posted (but with
an insert statement and fully qualified tablenames) works correctly on 2000
sp3a. I seriously doubt that the version of sql server or the OS has
anything to do with the issue.

Large TempDB

Hi
I have many reports running from a SQL Server DB, from Crystal Enterprise
reporting system. However I have noticed some performance degradation, and
I noticed that my TempDB had ballooned in size. Is there a way to reset
this? I tried Shrink, but this did not reduce the size of the DB?
Kind Regards
Ricky
(SQL2K-SP3A/WIN2K-Client/WIN2K-Server/Crystal Enterprise 9.0)http://www.aspfaq.com/show.asp?id=2446
David Portas
SQL Server MVP
--
"rikesh patel" <trikky@.rukp28.freeserve.co.uk> wrote in message
news:ubgD8RnxFHA.3124@.TK2MSFTNGP12.phx.gbl...
> Hi
> I have many reports running from a SQL Server DB, from Crystal Enterprise
> reporting system. However I have noticed some performance degradation,
> and
> I noticed that my TempDB had ballooned in size. Is there a way to reset
> this? I tried Shrink, but this did not reduce the size of the DB?
> Kind Regards
> Ricky
> (SQL2K-SP3A/WIN2K-Client/WIN2K-Server/Crystal Enterprise 9.0)
>

Large table performance

I have a table that will be the most used table in a system that I am
developing. The table will be used by around 10 users for both read
and write operations and is likely that it might grow to have millions
of records and it will have around 6 columns, most of them numeric.
I was considering partitioning this table horizontally but that might
make the front end application a lot more difficult to develop.
So I wonder if proper indexing the table or any other database trick
can allow me to keep a good performance on this table or if I
necessarily should partition. Ideas anybody?On Fri, 15 Jun 2007 17:31:19 -0700, Artificer
<eliezerfigueroa@.gmail.com> wrote:

>I have a table that will be the most used table in a system that I am
>developing. The table will be used by around 10 users for both read
>and write operations and is likely that it might grow to have millions
>of records and it will have around 6 columns, most of them numeric.
>I was considering partitioning this table horizontally but that might
>make the front end application a lot more difficult to develop.
>So I wonder if proper indexing the table or any other database trick
>can allow me to keep a good performance on this table or if I
>necessarily should partition. Ideas anybody?
What you describe does not sound particularly demanding. Ten users is
not many. Six columns is narrow. Millions of narrow rows simply
means it needs the proper indexing. You are a long way from having to
consider partitioning, though it is is always something to keep in
mind if performance problems occur that are not fixed with simpler
measures.
If you post the table definition, describe the data, and outline the
usage you will probably get some good advice on indexing the table.
Roy Harvey
Beacon Falls, CT|||An index is actually an ordered set of paritions. Think about it - the first
column in the index groups all rows with the same values. The second column
groups rows of equal values as sub-ranges of the prior column and so on.
Assuming you design the right index/s for your query/s you should never have
a need to partition, unless you need to load & unload substantial numbers of
rows en-mass.
Regards,
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
http://www.SQLBenchmarkPro.com
"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1181953879.072353.197600@.m36g2000hse.googlegroups.com...
>I have a table that will be the most used table in a system that I am
> developing. The table will be used by around 10 users for both read
> and write operations and is likely that it might grow to have millions
> of records and it will have around 6 columns, most of them numeric.
> I was considering partitioning this table horizontally but that might
> make the front end application a lot more difficult to develop.
> So I wonder if proper indexing the table or any other database trick
> can allow me to keep a good performance on this table or if I
> necessarily should partition. Ideas anybody?
>

Large table performance

I have a table that will be the most used table in a system that I am
developing. The table will be used by around 10 users for both read
and write operations and is likely that it might grow to have millions
of records and it will have around 6 columns, most of them numeric.
I was considering partitioning this table horizontally but that might
make the front end application a lot more difficult to develop.
So I wonder if proper indexing the table or any other database trick
can allow me to keep a good performance on this table or if I
necessarily should partition. Ideas anybody?
On Fri, 15 Jun 2007 17:31:19 -0700, Artificer
<eliezerfigueroa@.gmail.com> wrote:

>I have a table that will be the most used table in a system that I am
>developing. The table will be used by around 10 users for both read
>and write operations and is likely that it might grow to have millions
>of records and it will have around 6 columns, most of them numeric.
>I was considering partitioning this table horizontally but that might
>make the front end application a lot more difficult to develop.
>So I wonder if proper indexing the table or any other database trick
>can allow me to keep a good performance on this table or if I
>necessarily should partition. Ideas anybody?
What you describe does not sound particularly demanding. Ten users is
not many. Six columns is narrow. Millions of narrow rows simply
means it needs the proper indexing. You are a long way from having to
consider partitioning, though it is is always something to keep in
mind if performance problems occur that are not fixed with simpler
measures.
If you post the table definition, describe the data, and outline the
usage you will probably get some good advice on indexing the table.
Roy Harvey
Beacon Falls, CT

Large table performance

I have a table that will be the most used table in a system that I am
developing. The table will be used by around 10 users for both read
and write operations and is likely that it might grow to have millions
of records and it will have around 6 columns, most of them numeric.
I was considering partitioning this table horizontally but that might
make the front end application a lot more difficult to develop.
So I wonder if proper indexing the table or any other database trick
can allow me to keep a good performance on this table or if I
necessarily should partition. Ideas anybody?On Fri, 15 Jun 2007 17:31:19 -0700, Artificer
<eliezerfigueroa@.gmail.com> wrote:
>I have a table that will be the most used table in a system that I am
>developing. The table will be used by around 10 users for both read
>and write operations and is likely that it might grow to have millions
>of records and it will have around 6 columns, most of them numeric.
>I was considering partitioning this table horizontally but that might
>make the front end application a lot more difficult to develop.
>So I wonder if proper indexing the table or any other database trick
>can allow me to keep a good performance on this table or if I
>necessarily should partition. Ideas anybody?
What you describe does not sound particularly demanding. Ten users is
not many. Six columns is narrow. Millions of narrow rows simply
means it needs the proper indexing. You are a long way from having to
consider partitioning, though it is is always something to keep in
mind if performance problems occur that are not fixed with simpler
measures.
If you post the table definition, describe the data, and outline the
usage you will probably get some good advice on indexing the table.
Roy Harvey
Beacon Falls, CT|||An index is actually an ordered set of paritions. Think about it - the first
column in the index groups all rows with the same values. The second column
groups rows of equal values as sub-ranges of the prior column and so on.
Assuming you design the right index/s for your query/s you should never have
a need to partition, unless you need to load & unload substantial numbers of
rows en-mass.
Regards,
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
http://www.SQLBenchmarkPro.com
"Artificer" <eliezerfigueroa@.gmail.com> wrote in message
news:1181953879.072353.197600@.m36g2000hse.googlegroups.com...
>I have a table that will be the most used table in a system that I am
> developing. The table will be used by around 10 users for both read
> and write operations and is likely that it might grow to have millions
> of records and it will have around 6 columns, most of them numeric.
> I was considering partitioning this table horizontally but that might
> make the front end application a lot more difficult to develop.
> So I wonder if proper indexing the table or any other database trick
> can allow me to keep a good performance on this table or if I
> necessarily should partition. Ideas anybody?
>

Large System Databases

I manage a SQL Server where the master database is almost 3 times
larger than any other database and the tempdb is even bigger than that.
I have not been monitoring the size of these databases for very long
so I don't have a good baseline. However, it seems like a problem. I
would appreciate any help.
Thanks!
What is large? Does the app use master to store user data?
Andrew J. Kelly SQL MVP
"shanghai360" <scott.seely@.sfsoma.com> wrote in message
news:1143653999.425234.24030@.t31g2000cwb.googlegro ups.com...
>I manage a SQL Server where the master database is almost 3 times
> larger than any other database and the tempdb is even bigger than that.
> I have not been monitoring the size of these databases for very long
> so I don't have a good baseline. However, it seems like a problem. I
> would appreciate any help.
> Thanks!
>
|||The Master database is 3.6 GB and Tempdb is 4.5 GB...perhaps not so big
for some environments.
You are right, our Sitescope monitoring software maintains a log table
in the Master database that is 3.3 GB alone. Is that a good practice?
Thank you for your help Sir!
|||No that is very poor practice. Why would you use the Master database as a
user db? If that is third party software then you should ask for your money
back and tell them to call you when they get a clue. It is impossible to
say why tempdb is that big and if it is needed that big or not without much
more information. Check to be sure there are no long running open
transactions in Tempdb.
Andrew J. Kelly SQL MVP
"shanghai360" <scott.seely@.sfsoma.com> wrote in message
news:1143749039.920309.50530@.v46g2000cwv.googlegro ups.com...
> The Master database is 3.6 GB and Tempdb is 4.5 GB...perhaps not so big
> for some environments.
> You are right, our Sitescope monitoring software maintains a log table
> in the Master database that is 3.3 GB alone. Is that a good practice?
> Thank you for your help Sir!
>
|||It turned out that it was 3rd party software and consultant that
installed it in the Master. Thanks to your help we were able to
resolve the problem and move the table into a user database. We regard
to temp there was no long running open transactions...I restarted the
sql service and it shrunk to a resonable size, I'm monitoring it now.
Thanks again!

Large System Databases

I manage a SQL Server where the master database is almost 3 times
larger than any other database and the tempdb is even bigger than that.
I have not been monitoring the size of these databases for very long
so I don't have a good baseline. However, it seems like a problem. I
would appreciate any help.
Thanks!What is large? Does the app use master to store user data?
--
Andrew J. Kelly SQL MVP
"shanghai360" <scott.seely@.sfsoma.com> wrote in message
news:1143653999.425234.24030@.t31g2000cwb.googlegroups.com...
>I manage a SQL Server where the master database is almost 3 times
> larger than any other database and the tempdb is even bigger than that.
> I have not been monitoring the size of these databases for very long
> so I don't have a good baseline. However, it seems like a problem. I
> would appreciate any help.
> Thanks!
>|||The Master database is 3.6 GB and Tempdb is 4.5 GB...perhaps not so big
for some environments.
You are right, our Sitescope monitoring software maintains a log table
in the Master database that is 3.3 GB alone. Is that a good practice?
Thank you for your help Sir!|||No that is very poor practice. Why would you use the Master database as a
user db? If that is third party software then you should ask for your money
back and tell them to call you when they get a clue. It is impossible to
say why tempdb is that big and if it is needed that big or not without much
more information. Check to be sure there are no long running open
transactions in Tempdb.
--
Andrew J. Kelly SQL MVP
"shanghai360" <scott.seely@.sfsoma.com> wrote in message
news:1143749039.920309.50530@.v46g2000cwv.googlegroups.com...
> The Master database is 3.6 GB and Tempdb is 4.5 GB...perhaps not so big
> for some environments.
> You are right, our Sitescope monitoring software maintains a log table
> in the Master database that is 3.3 GB alone. Is that a good practice?
> Thank you for your help Sir!
>|||It turned out that it was 3rd party software and consultant that
installed it in the Master. Thanks to your help we were able to
resolve the problem and move the table into a user database. We regard
to temp there was no long running open transactions...I restarted the
sql service and it shrunk to a resonable size, I'm monitoring it now.
Thanks again!

Large System Databases

I manage a SQL Server where the master database is almost 3 times
larger than any other database and the tempdb is even bigger than that.
I have not been monitoring the size of these databases for very long
so I don't have a good baseline. However, it seems like a problem. I
would appreciate any help.
Thanks!What is large? Does the app use master to store user data?
Andrew J. Kelly SQL MVP
"shanghai360" <scott.seely@.sfsoma.com> wrote in message
news:1143653999.425234.24030@.t31g2000cwb.googlegroups.com...
>I manage a SQL Server where the master database is almost 3 times
> larger than any other database and the tempdb is even bigger than that.
> I have not been monitoring the size of these databases for very long
> so I don't have a good baseline. However, it seems like a problem. I
> would appreciate any help.
> Thanks!
>|||The Master database is 3.6 GB and Tempdb is 4.5 GB...perhaps not so big
for some environments.
You are right, our Sitescope monitoring software maintains a log table
in the Master database that is 3.3 GB alone. Is that a good practice?
Thank you for your help Sir!|||No that is very poor practice. Why would you use the Master database as a
user db? If that is third party software then you should ask for your money
back and tell them to call you when they get a clue. It is impossible to
say why tempdb is that big and if it is needed that big or not without much
more information. Check to be sure there are no long running open
transactions in Tempdb.
Andrew J. Kelly SQL MVP
"shanghai360" <scott.seely@.sfsoma.com> wrote in message
news:1143749039.920309.50530@.v46g2000cwv.googlegroups.com...
> The Master database is 3.6 GB and Tempdb is 4.5 GB...perhaps not so big
> for some environments.
> You are right, our Sitescope monitoring software maintains a log table
> in the Master database that is 3.3 GB alone. Is that a good practice?
> Thank you for your help Sir!
>|||It turned out that it was 3rd party software and consultant that
installed it in the Master. Thanks to your help we were able to
resolve the problem and move the table into a user database. We regard
to temp there was no long running open transactions...I restarted the
sql service and it shrunk to a resonable size, I'm monitoring it now.
Thanks again!

Large scale changes of data in SQL Server 2005

Hi
We are using a Helpdesk system which uses SQL Server 2005 as the back
end. Our office is the group HQ for all our subsidaries and affiliates,
so we tend to get a lot of IT support calls from different
organisations.
When HD agents log the call, a field exists to identify which
organisation this is, e.g. Company A, Company B and so on.
Company A has decided to change their name to Company Z. This needs to
be reflected in the software not just from this point on, but also
historically, i.e. even a called in 2005 by an employee of Company A
should now read as a call logged by an employee of Company Z.
What is the best method to change this in the database? Luckily,
Company A has been one of the smaller companies, so the total amount of
calls by them that exist is about 40.
Many thanks in advance.
SJ
Hi
I hope you do have Company table where you store the names of the companies
and their ID
UPDATE Company SET [Name]='Company Z' WHERE [Name]='Company B'
If your database design is proper you don't need change anything because
the table is refernced by ID anot by NAME
<smokejo@.googlemail.com> wrote in message
news:1163971948.529460.64020@.f16g2000cwb.googlegro ups.com...
> Hi
> We are using a Helpdesk system which uses SQL Server 2005 as the back
> end. Our office is the group HQ for all our subsidaries and affiliates,
> so we tend to get a lot of IT support calls from different
> organisations.
> When HD agents log the call, a field exists to identify which
> organisation this is, e.g. Company A, Company B and so on.
> Company A has decided to change their name to Company Z. This needs to
> be reflected in the software not just from this point on, but also
> historically, i.e. even a called in 2005 by an employee of Company A
> should now read as a call logged by an employee of Company Z.
> What is the best method to change this in the database? Luckily,
> Company A has been one of the smaller companies, so the total amount of
> calls by them that exist is about 40.
> Many thanks in advance.
> SJ
>
|||Uri Dimant wrote:

> Hi
> I hope you do have Company table where you store the names of the companies
> and their ID
> UPDATE Company SET [Name]='Company Z' WHERE [Name]='Company B'
> If your database design is proper you don't need change anything because
> the table is refernced by ID anot by NAME
>
We have a Company table and within it columns for CompanyID and
CompanyName.
I have a couple of questions though...
i) How is it possible to 'see' what values there are for the Company
Name?
ii) Where do I run the command that you mentioned?
iii) Is it possible to actually see all the data in the database and,
if so, how can I do that? For instance, in MS Access you can view the
database and all the info in it...
Sorry, I'm not really an SQL man, so have no idea!
Thanks so much in advance...
|||> i) How is it possible to 'see' what values there are for the Company
> Name?
SELECT * FROM Table WHERE NOT EXISTS (SELECT * FROM Company C WHERE
C.CompanyID=Table.CompanyID)

> ii) Where do I run the command that you mentioned?
Query Analyzer

> iii) Is it possible to actually see all the data in the database and,
What did you mean?

> if so, how can I do that? For instance, in MS Access you can view the
> database and all the info in it...
Enterprise Manager?
<smokejo@.googlemail.com> wrote in message
news:1164110500.619811.76700@.j44g2000cwa.googlegro ups.com...
> Uri Dimant wrote:
> We have a Company table and within it columns for CompanyID and
> CompanyName.
> I have a couple of questions though...
> i) How is it possible to 'see' what values there are for the Company
> Name?
> ii) Where do I run the command that you mentioned?
> iii) Is it possible to actually see all the data in the database and,
> if so, how can I do that? For instance, in MS Access you can view the
> database and all the info in it...
> Sorry, I'm not really an SQL man, so have no idea!
> Thanks so much in advance...
>