More problems with activation

I have two databases on the same instance.

One is Basket_ODS and the other is Intelligence_ODS. I am using service broker activation on a queue to move data from the Basket_ODS table to the Intelligence_ODS database. Previously I was able to move from table to table in Basket_ODS, however now that I am moving it to another database on the same instance it is no longer working.

If I set my active connection in SQL Management Studio to this user(BrokerUser) and execute the "move" procedure it works. When activated by Service Broker however, it does not. Here is the error message:

2006-05-09 14:47:52.940 spid86s The activated proc [ODS].[ProcessOrderQueue] running on queue Basket_ODS.ODS.Order Process Queue output the following: 'The server principal "BrokerUser" is not able to access the database "Intelligence_ODS" under the current security context.'

I'm sure I missed something becasue it works fine in the same database. BrokerUser has datareader and datawriter in both databases.

Thanks for any help on this matter.

Gary

Activated task run under impersonated security context, similar to using the EXECUTE AS clause. Let me cross-post a reply from Remus:

<remus>
The explanation is detailed in the 'Extending Database Impersonation by Using EXECUTE AS' chapter in BOL (http://msdn2.microsoft.com/en-us/library/ms188304(en-us,VS.90).aspx)

A short explanation is this: when executing under an EXECUTE AS context (as activated procedures always are), the trust is given by the dbo of the database. Therefore, the procedure is trusted only at the level of the database, not at the level of the server. Server level views require server level trust, and you execution context is lacking it. You execution context behaves as if you logged in with [Public] in the server. By marking the database as trustworthy, the dbo of the database is trusted at the server level and you execution context inherits all the permissions you expect at the server level.

Marking the database trustworthy is quite a powerfull step. The dbo of that database can elevate itself to sysadmin, there's no way of preventing it. A more refined approach is to sign the activated procedure with a server level certificate that has proper rights (ADD SIGNATURE).
</remus>

While the above could solve your problem, it may be worthwhile trying to ask yourself, why is the service located in Basket_ODS instead of Intelligence_ODS.

Hope that helps,
Rushi

|||

I actually have a sample showing how to do this: http://blogs.msdn.com/remusrusanu/archive/2006/03/07/545508.aspx

HTH,
~ Remus

|||

Thanks Rushi,

As usual you do a great job with follow up. The activated stored procedure copies data from the Basket_ODS database to the Intelligence_ODS database. If I move the proc to the Basket_ODS database, I ran into the same issue when it tried to copy over.

I did read the information on BOL and your earlier post on this. I guess I'll have to work my way through signing the stored procedure. The trust database option really won't be a player for me I think. I did set up trust tonight and it worked though. I have to do another run in the morning but it was incredibly slower than the copy to the same database.

I'll try to worth through the signing tomorrow. That seems the way to go for me. We are trying to operate in least-trust mode. The DBAs start getting white hair when I ask for things like database trust.

Thank you so much for your help and your contribution to the community.

Gary

|||

Thank's Remus. Wow that's a great sample. I wish I found that earlier today. Thanks again for the great information.

Gary

|||

I created the certificates as noted in your scripts. The user associated with the certificate in my system is BrokerUser2. I do have a question though.

why can't I do this?

ALTER QUEUE ODS.[Order Process Queue] WITH

ACTIVATION (

STATUS = ON,

PROCEDURE_NAME = ODS.ProcessOrderQueue,

MAX_QUEUE_READERS = 4,

EXECUTE AS 'BrokerUser2'

)

when I try this it says BrokerUser2 does not exist or I do not have access to it. I can do an sp_helpuser 'BrokerUser2' and see his rights though.

It did appear to work if I did this:

ALTER QUEUE ODS.[Order Process Queue] WITH

ACTIVATION (

STATUS = ON,

PROCEDURE_NAME = ODS.ProcessOrderQueue,

MAX_QUEUE_READERS = 4, EXECUTE AS OWNER)

I did create the ODS.ProcessOrderQueue stored procedure [With Execute As 'BrokerUser2'].

|||

The user executing the ALTER QUEUE statement must have IMPERSONATE permission over 'BrokerUser2'.

HTH,
~ Remus

More conversation_endpoints

So I took the time to build a reproduction of the conversation_endpoint problem that was discussed in another thread. I build two databases, with a send and receive queue. This is essentially the way the code works here at my site. I have a script near the bottom that sends messages every 5 minutes for 2 hours. If there is any logic that removes conversation_endpoints 30 min then the Message record table will show them.

Please let me know what I am doing wrong, so I can change my production code to help eliminate the large buildup in the sys.conversation_endpoints.

Thanks!

use master

go

if exists ( select * from sys.databases where name = 'SBSource' )

drop database SBSource

go

if exists ( select * from sys.databases where name = 'SBTarget' )

drop database SBTarget

go

-- Setup environment for test

create database SBSource

GO

ALTER DATABASE SBSource SET ENABLE_BROKER

ALTER DATABASE SBSource SET TRUSTWORTHY ON

GO

create database SBTarget

GO

ALTER DATABASE SBTarget SET ENABLE_BROKER

ALTER DATABASE SBTarget SET TRUSTWORTHY ON

GO

use SBSource

go

CREATE MESSAGE TYPE [msgTest] AUTHORIZATION [dbo];

CREATE CONTRACT [Test] ( [msgTest] SENT BY ANY );

CREATE QUEUE dbo.[SourceQueue] WITH STATUS = ON , RETENTION = OFF;

CREATE SERVICE [SBSourceTest] authorization [dbo]

ON QUEUE [dbo].[SourceQueue]

( [Test] );

CREATE ROUTE [ToTarget] AUTHORIZATION [dbo] WITH SERVICE_NAME = N'SBTargetTest' , ADDRESS = N'LOCAL';

GO

create procedure dbo.ProcessEndDialogMessages

as

begin

set nocount on;

declare @.conversation_handle uniqueidentifier,

@.message_type sysname,

@.message_body xml;

begin transaction;

WAITFOR (

RECEIVE @.conversation_handle = [conversation_handle],

@.message_type = [message_type_name],

@.message_body = [message_body]

FROM dbo.[SourceQueue]), TIMEOUT 1000;

while @.conversation_handle IS NOT NULL

begin

end conversation @.conversation_handle;

commit;

begin transaction;

set @.conversation_handle = null;

WAITFOR (

RECEIVE @.conversation_handle = [conversation_handle],

@.message_type = [message_type_name],

@.message_body = [message_body]

FROM dbo.[SourceQueue]), TIMEOUT 1000;

end

commit;

end

go

Alter QUEUE dbo.[SourceQueue] WITH STATUS = ON, Activation ( STatus = on, procedure_name = dbo.ProcessEndDialogMessages, MAX_QUEUE_READERS = 2, EXECUTE AS 'dbo' )

go

use SBTarget

go

CREATE MESSAGE TYPE [msgTest] AUTHORIZATION [dbo];

CREATE CONTRACT [Test] ( [msgTest] SENT BY ANY );

CREATE QUEUE dbo.[TargetQueue] WITH STATUS = ON , RETENTION = OFF;

CREATE SERVICE [SBTargetTest] authorization [dbo]

ON QUEUE [dbo].[TargetQueue]

( [Test] );

CREATE ROUTE [ToSource] AUTHORIZATION [dbo] WITH SERVICE_NAME = N'SBSourceTest' , ADDRESS = N'LOCAL';

GO

create table dbo.MessageRecord ( Conversation_handle uniqueidentifier, Inserted datetime )

go

create procedure dbo.ProcessTargetQueue

as

begin

set nocount on;

declare @.conversation_handle uniqueidentifier,

@.message_type sysname,

@.message_body xml;

begin transaction;

WAITFOR (

RECEIVE @.conversation_handle = [conversation_handle],

@.message_type = [message_type_name],

@.message_body = [message_body]

FROM dbo.[TargetQueue]), TIMEOUT 1000;

while @.conversation_handle IS NOT NULL

begin

insert into dbo.MessageRecord ( Conversation_handle, Inserted ) values ( @.conversation_handle, getdate() );

end conversation @.conversation_handle;

commit;

begin transaction;

set @.conversation_handle = null;

WAITFOR (

RECEIVE @.conversation_handle = [conversation_handle],

@.message_type = [message_type_name],

@.message_body = [message_body]

FROM dbo.[TargetQueue]), TIMEOUT 1000;

end

commit;

end

GO

Alter QUEUE dbo.[TargetQueue] WITH STATUS = ON, Activation ( STatus = on, procedure_name = dbo.ProcessTargetQueue, MAX_QUEUE_READERS = 2, EXECUTE AS 'dbo' )

go

use sbsource

go

-- start sending messages, check count in conv_endpoints along the way

set xact_abort on

set nocount on

declare @.EndAt datetime,

@.msg xml,

@.ch uniqueidentifier;

set @.EndAt = DATEADD( hh, 2, getdate() )

set @.msg = '<message>dfsafa</message>';

while getdate() < @.EndAt

begin

set @.ch = null;

begin transaction;

begin dialog conversation @.ch

from service [SBSourceTest]

to service 'SBTargetTest'

on contract [Test]

with

encryption=off;

send on conversation @.ch message type [msgTest] (@.msg);

note the abscence of an end conversation, so no fire and forget!

commit;

waitfor delay '00:05:00'

end

GO

-- check on data after complete.

select state, count(*)

from SBSource.sys.conversation_endpoints

group by state;

select state, count(*)

from SBTarget.sys.conversation_endpoints c

inner join sbtarget.dbo.MessageRecord m on c.conversation_handle = m.conversation_handle

group by state;

select m.*, c.*

from SBTarget.sys.conversation_endpoints c

inner join sbtarget.dbo.MessageRecord m on c.conversation_handle = m.conversation_handle

select *

from SBTarget.dbo.targetqueue

Please use the SQLServer feedback site to report this issue: https://connect.microsoft.com/SQLServer/Feedback

Thanks,
~ Remus

Monster Replication

I'm trying to replicate databases from 7 serves into one main one. For example say data from servers A, B, C, D, E, F, G go into one main server, say, server Z. I'm using snapshot replication method. The final outcome of this replication should be as follows: Each server has tables in it, when these tables are replicated into Server Z, I want to add extra field in the table (on Server Z). This field will represent where the record came from, ie, either Server A,B.. etc. I cant add the extra column in the Publisher server tables (Server A,B..etc). The column MUST be added in the final replicated table on server Z.

Has ne one out there done sumthing similar to this? If so please help me out with. I've been struggling with this for a while now. So any kind of help will be appreciated.

Thanks in advance.can you use a separate non-replicated table on server z to perform the final insert from 7 tables using a union and adding this addition field at the time of select for each section of the union?|||You could use merge replication with horizontal filters. Add field hostname and use function host_name() for filtering on publisher.
Your main db will be publisher - another dbs - pull subscribers. Use guid (uniqueidentifier ) as id for all tables. This schema is working fine in one of my projects.|||but my understanding was that it's a reverse, - there are 7 publishers and 1 subscriber. plus, i don't think he is considering total system overhaul (changing structures, guids, etc.)|||Originally posted by snail
You could use merge replication with horizontal filters. Add field hostname and use function host_name() for filtering on publisher.
Your main db will be publisher - another dbs - pull subscribers. Use guid (uniqueidentifier ) as id for all tables. This schema is working fine in one of my projects.

So this idea has already been tested?... Newayz, the guid (uniqueidentifier) column would be default as newid() rite? Well I want a certain value to go in the column depending on the server the record came from.

Since you have done this kinda thing b4, i'd be askin alot of questions ... =)

Thanks in advance.

Monitoring More Than 1 DB's Growth Rate

Hello, Is there a Best Practice or a script I can run daily to monitor the
rate of growth of about 12 databases? Thanks, PanchoHi
Vyas' script may help!
http://vyaskn.tripod.com/track_sql_...file_growth.htm
John
"Pancho" wrote:

> Hello, Is there a Best Practice or a script I can run daily to monitor the
> rate of growth of about 12 databases? Thanks, Pancho|||Thanks, John. I'll give this a try!
"John Bell" wrote:
> Hi
> Vyas' script may help!
> http://vyaskn.tripod.com/track_sql_...file_growth.htm
> John
> "Pancho" wrote:
>

monitoring memory usage of particular database

We would like to individually monitor the memory usage of all the different
databases on a server. In Current Activity in Enterprise Manager I see Memor
y Usage for the databases - is there a way to log that information? I didn't
see Memory Usage as a data
column in the Trace Properties in the Profiler. Thank you.Could the Perfmon be of any help? You ahve database counters there, depends
on what information you need.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

monitoring memory usage of particular database

We would like to individually monitor the memory usage of all the different databases on a server. In Current Activity in Enterprise Manager I see Memory Usage for the databases - is there a way to log that information? I didn't see Memory Usage as a data
column in the Trace Properties in the Profiler. Thank you.
Could the Perfmon be of any help? You ahve database counters there, depends
on what information you need.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Monitoring free space in database and log files with script

I'd like a tool (I am willing to build it) that once a day goes out and
identifies how much free space is left in my databases and log files. I'm
aware that notification/alerts can be setup, but I don't need/want it to
constantly be monitoring the space. Is there a reliable way to script this?
Thanks in advance.
Mark
SQL2K:
For log space, use DBCC SQLPERF(logspace).
For database space, you can iterate each database, executing sp_spaceused.
That is not friendly ouput however, and it is not guaranteed to be
'correct'.
SQL2K5:
Check into the sys.dm_... dynamic management functions. This might do it:
select sum(reserved_page_count * 8192.0/1048576.0) from
mydb.sys.dm_db_partition_stats
You will again need to iterate through each database and do a dynamic
execution, since that function is specific to each database.
TheSQLGuru
President
Indicium Resources, Inc.
"Mark" <markfield88@.nospam.nospam> wrote in message
news:uFOWJKdgHHA.4844@.TK2MSFTNGP02.phx.gbl...
> I'd like a tool (I am willing to build it) that once a day goes out and
> identifies how much free space is left in my databases and log files. I'm
> aware that notification/alerts can be setup, but I don't need/want it to
> constantly be monitoring the space. Is there a reliable way to script
> this?
> Thanks in advance.
> Mark
>
|||Hi Mark
"Mark" wrote:

> I'd like a tool (I am willing to build it) that once a day goes out and
> identifies how much free space is left in my databases and log files. I'm
> aware that notification/alerts can be setup, but I don't need/want it to
> constantly be monitoring the space. Is there a reliable way to script this?
> Thanks in advance.
> Mark
>
You may want to look at
http://www.microsoft.com/technet/scriptcenter/scripts/sql/dbmgmt/sqldbvb03.mspx
John

Monitoring free space in database and log files with script

I'd like a tool (I am willing to build it) that once a day goes out and
identifies how much free space is left in my databases and log files. I'm
aware that notification/alerts can be setup, but I don't need/want it to
constantly be monitoring the space. Is there a reliable way to script this?
Thanks in advance.
MarkSQL2K:
For log space, use DBCC SQLPERF(logspace).
For database space, you can iterate each database, executing sp_spaceused.
That is not friendly ouput however, and it is not guaranteed to be
'correct'.
SQL2K5:
Check into the sys.dm_... dynamic management functions. This might do it:
select sum(reserved_page_count * 8192.0/1048576.0) from
mydb.sys.dm_db_partition_stats
You will again need to iterate through each database and do a dynamic
execution, since that function is specific to each database.
TheSQLGuru
President
Indicium Resources, Inc.
"Mark" <markfield88@.nospam.nospam> wrote in message
news:uFOWJKdgHHA.4844@.TK2MSFTNGP02.phx.gbl...
> I'd like a tool (I am willing to build it) that once a day goes out and
> identifies how much free space is left in my databases and log files. I'm
> aware that notification/alerts can be setup, but I don't need/want it to
> constantly be monitoring the space. Is there a reliable way to script
> this?
> Thanks in advance.
> Mark
>|||Hi Mark
"Mark" wrote:
> I'd like a tool (I am willing to build it) that once a day goes out and
> identifies how much free space is left in my databases and log files. I'm
> aware that notification/alerts can be setup, but I don't need/want it to
> constantly be monitoring the space. Is there a reliable way to script this?
> Thanks in advance.
> Mark
>
You may want to look at
http://www.microsoft.com/technet/scriptcenter/scripts/sql/dbmgmt/sqldbvb03.mspx
John

Monitoring free space in database and log files with script

I'd like a tool (I am willing to build it) that once a day goes out and
identifies how much free space is left in my databases and log files. I'm
aware that notification/alerts can be setup, but I don't need/want it to
constantly be monitoring the space. Is there a reliable way to script this?
Thanks in advance.
MarkSQL2K:
For log space, use DBCC SQLPERF(logspace).
For database space, you can iterate each database, executing sp_spaceused.
That is not friendly ouput however, and it is not guaranteed to be
'correct'.
SQL2K5:
Check into the sys.dm_... dynamic management functions. This might do it:
select sum(reserved_page_count * 8192.0/1048576.0) from
mydb.sys.dm_db_partition_stats
You will again need to iterate through each database and do a dynamic
execution, since that function is specific to each database.
TheSQLGuru
President
Indicium Resources, Inc.
"Mark" <markfield88@.nospam.nospam> wrote in message
news:uFOWJKdgHHA.4844@.TK2MSFTNGP02.phx.gbl...
> I'd like a tool (I am willing to build it) that once a day goes out and
> identifies how much free space is left in my databases and log files. I'm
> aware that notification/alerts can be setup, but I don't need/want it to
> constantly be monitoring the space. Is there a reliable way to script
> this?
> Thanks in advance.
> Mark
>|||Hi Mark
"Mark" wrote:

> I'd like a tool (I am willing to build it) that once a day goes out and
> identifies how much free space is left in my databases and log files. I'm
> aware that notification/alerts can be setup, but I don't need/want it to
> constantly be monitoring the space. Is there a reliable way to script thi
s?
> Thanks in advance.
> Mark
>
You may want to look at
[url]http://www.microsoft.com/technet/scriptcenter/scripts/sql/dbmgmt/sqldbvb03.mspx[/u
rl]
John

Monitoring database space usage

I'm interesting in monitoring database space usage in SQL Server 2005.
Ideally, once a day I'd like a job to run that looks for databases that are
within 20% of capacity. If one or more exists, I get an email. I don't
need/want it to monitor constantly as our business processes do not require
that. I do not want to have to manually monitor. For other business
reasons, our databases will be set to fixed size with autogrowth disabled,
hence our interest in monitoring.
What do you recommend?
Thanks,
Mark
Mark,
Take a look at the view sys.database_files, it should be fairly easy
to create a SP off of this data and then as long as you have your
database mail setup, you can e-mail yourself.
Here is an example to run:
use DBNAME
go
select
physical_name,
size * 8 AS [Current Size in KB],
max_size * 8 [Maximum Size in KB]
from sys.database_files
Note that size and max_size are the number of PAGES the files have,
and pages in SQL Server are 8K.
Hope this helps,
-Sean
On Mar 28, 10:58Xam, "Mark" <m...@.idonotlikespam.com> wrote:
> I'm interesting in monitoring database space usage in SQL Server 2005.
> Ideally, once a day I'd like a job to run that looks for databases that are
> within 20% of capacity. XIf one or more exists, I get an email. XI don't
> need/want it to monitor constantly as our business processes do not require
> that. XI do not want to have to manually monitor. XFor other business
> reasons, our databases will be set to fixed size with autogrowth disabled,
> hence our interest in monitoring.
> What do you recommend?
> Thanks,
> Mark
|||On Mar 28, 8:31Xam, Sean <ColdFusion...@.gmail.com> wrote:
> Mark,
> Take a look at the view sys.database_files, it should be fairly easy
> to create a SP off of this data and then as long as you have your
> database mail setup, you can e-mail yourself.
> Here is an example to run:
> use DBNAME
> go
> select
> physical_name,
> size * 8 AS [Current Size in KB],
> max_size * 8 [Maximum Size in KB]
> from sys.database_files
> Note that size and max_size are the number of PAGES the files have,
> and pages in SQL Server are 8K.
> Hope this helps,
> -Sean
> On Mar 28, 10:58Xam, "Mark" <m...@.idonotlikespam.com> wrote:
>
>
>
> - Show quoted text -
Mark, I don't think this is going to work. This will only tell you
what the size of the database file is - not how much space is actually
being used within that file.
There are a couple of options:
1) Look up FILEPROPERTY - this function has a property for returning
SpaceUsed.
2) Review the stored procedure sp_spaceused - create your own version
using the same logic
3) Look up DataSpaceUsage, IndexSpaceUsage and SpaceAvailable in
SMO. You can either create a program, or use Powershell to create a
script to capture the data.
I prefer the script method myself and use Powershell to pull this data
from all of our SQL Servers.
HTH,
Jeff

Monitoring database space usage

I'm interesting in monitoring database space usage in SQL Server 2005.
Ideally, once a day I'd like a job to run that looks for databases that are
within 20% of capacity. If one or more exists, I get an email. I don't
need/want it to monitor constantly as our business processes do not require
that. I do not want to have to manually monitor. For other business
reasons, our databases will be set to fixed size with autogrowth disabled,
hence our interest in monitoring.
What do you recommend?
Thanks,
MarkMark,
Take a look at the view sys.database_files, it should be fairly easy
to create a SP off of this data and then as long as you have your
database mail setup, you can e-mail yourself.
Here is an example to run:
use DBNAME
go
select
physical_name,
size * 8 AS [Current Size in KB],
max_size * 8 [Maximum Size in KB]
from sys.database_files
Note that size and max_size are the number of PAGES the files have,
and pages in SQL Server are 8K.
Hope this helps,
-Sean
On Mar 28, 10:58=A0am, "Mark" <m...@.idonotlikespam.com> wrote:
> I'm interesting in monitoring database space usage in SQL Server 2005.
> Ideally, once a day I'd like a job to run that looks for databases that ar=e
> within 20% of capacity. =A0If one or more exists, I get an email. =A0I don='t
> need/want it to monitor constantly as our business processes do not requir=e
> that. =A0I do not want to have to manually monitor. =A0For other business
> reasons, our databases will be set to fixed size with autogrowth disabled,=
> hence our interest in monitoring.
> What do you recommend?
> Thanks,
> Mark|||On Mar 28, 8:31=A0am, Sean <ColdFusion...@.gmail.com> wrote:
> Mark,
> Take a look at the view sys.database_files, it should be fairly easy
> to create a SP off of this data and then as long as you have your
> database mail setup, you can e-mail yourself.
> Here is an example to run:
> use DBNAME
> go
> select
> physical_name,
> size * 8 AS [Current Size in KB],
> max_size * 8 [Maximum Size in KB]
> from sys.database_files
> Note that size and max_size are the number of PAGES the files have,
> and pages in SQL Server are 8K.
> Hope this helps,
> -Sean
> On Mar 28, 10:58=A0am, "Mark" <m...@.idonotlikespam.com> wrote:
>
> > I'm interesting in monitoring database space usage in SQL Server 2005.
> > Ideally, once a day I'd like a job to run that looks for databases that =are
> > within 20% of capacity. =A0If one or more exists, I get an email. =A0I d=on't
> > need/want it to monitor constantly as our business processes do not requ=ire
> > that. =A0I do not want to have to manually monitor. =A0For other busines=s
> > reasons, our databases will be set to fixed size with autogrowth disable=d,
> > hence our interest in monitoring.
> > What do you recommend?
> > Thanks,
> > Mark- Hide quoted text -
> - Show quoted text -
Mark, I don't think this is going to work. This will only tell you
what the size of the database file is - not how much space is actually
being used within that file.
There are a couple of options:
1) Look up FILEPROPERTY - this function has a property for returning
SpaceUsed.
2) Review the stored procedure sp_spaceused - create your own version
using the same logic
3) Look up DataSpaceUsage, IndexSpaceUsage and SpaceAvailable in
SMO. You can either create a program, or use Powershell to create a
script to capture the data.
I prefer the script method myself and use Powershell to pull this data
from all of our SQL Servers.
HTH,
Jeff

Monitor Database up or down and email to DBA

HI,

I like to know what different tools and scripts different people are using to monitor their SQL 2005 Databases.

We need to monito two services they are up and running all the time.

MSSQLServer & SQLAgent

Currently we have 3 different sql 2005 server and each one have 4 databases, we like to monitor.

Thanks in advance

I hear good things about Quest's Spotlight and Idera's Diagnostic Manager, but haven't worked with either enough to be able to recommend them myself.

Ask in the tools group (http://groups.google.com/group/microsoft.public.sqlserver.tools) or the Tools forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1)

HTH...

Joe

Monitor Data and Log File Growth

Hi,
We are having more than 20 databases and we are merge replicating the
databases also. Currently for all databases we set the Data and Log file
growth as Automatically by percent. The size of each database is around 1277
MB.
Now if we Change the Data and Log File growth to resctricted mode is there
is any problem will cause, if not then how we will be able to monitor the
Data and Log file growth and when we should increase the File Size.
Thanks,
HerbertHerbert
If you open SQL Server Profiler you will find an event AutoGrowth (If I
remember well) , so you can track the info as well as querying sysfiles
system table (size column) (not recomended)
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:7973C596-C566-42F5-80CA-011D89D189AE@.microsoft.com...
> Hi,
> We are having more than 20 databases and we are merge replicating the
> databases also. Currently for all databases we set the Data and Log file
> growth as Automatically by percent. The size of each database is around
1277
> MB.
> Now if we Change the Data and Log File growth to resctricted mode is there
> is any problem will cause, if not then how we will be able to monitor the
> Data and Log file growth and when we should increase the File Size.
> Thanks,
> Herbert|||Hi,
As a good practice, I recommend you to monitor the MDF and LDF files daily
once or twice manually. The best approach for automatic monitoring is :-
1. Set the DB Size to a maximum size and make unrestricted growth by
percentage or MB
2. Then set up hard disk free space monitoring alert. So when ever your hard
disk goes beyond a specific amount you will get an alert.
See the below link to configure hard disk monitoring.
http://support.microsoft.com/?kbid=299921
Thanks
Hari
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxCBebdOFHA.3444@.tk2msftngp13.phx.gbl...
> Herbert
> If you open SQL Server Profiler you will find an event AutoGrowth (If I
> remember well) , so you can track the info as well as querying sysfiles
> system table (size column) (not recomended)
>
>
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:7973C596-C566-42F5-80CA-011D89D189AE@.microsoft.com...
>> Hi,
>> We are having more than 20 databases and we are merge replicating the
>> databases also. Currently for all databases we set the Data and Log file
>> growth as Automatically by percent. The size of each database is around
> 1277
>> MB.
>> Now if we Change the Data and Log File growth to resctricted mode is
>> there
>> is any problem will cause, if not then how we will be able to monitor the
>> Data and Log file growth and when we should increase the File Size.
>> Thanks,
>> Herbert
>|||Also read the following link
http://www.databasejournal.com/features/mssql/article.php/3339681
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eSLV51dOFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Hi,
> As a good practice, I recommend you to monitor the MDF and LDF files daily
> once or twice manually. The best approach for automatic monitoring is :-
> 1. Set the DB Size to a maximum size and make unrestricted growth by
> percentage or MB
> 2. Then set up hard disk free space monitoring alert. So when ever your
hard
> disk goes beyond a specific amount you will get an alert.
> See the below link to configure hard disk monitoring.
> http://support.microsoft.com/?kbid=299921
> Thanks
> Hari
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uxCBebdOFHA.3444@.tk2msftngp13.phx.gbl...
> > Herbert
> > If you open SQL Server Profiler you will find an event AutoGrowth (If I
> > remember well) , so you can track the info as well as querying sysfiles
> > system table (size column) (not recomended)
> >
> >
> >
> >
> >
> > "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> > news:7973C596-C566-42F5-80CA-011D89D189AE@.microsoft.com...
> >> Hi,
> >>
> >> We are having more than 20 databases and we are merge replicating
the
> >> databases also. Currently for all databases we set the Data and Log
file
> >> growth as Automatically by percent. The size of each database is
around
> > 1277
> >> MB.
> >>
> >> Now if we Change the Data and Log File growth to resctricted mode is
> >> there
> >> is any problem will cause, if not then how we will be able to monitor
the
> >> Data and Log file growth and when we should increase the File Size.
> >>
> >> Thanks,
> >> Herbert
> >
> >
>|||When your free space percentage on the disk gets below 20%, it is time to
start thinking about a new allocation.
For trend analysis, use the Backup repository in msdb. Every time a backup
is written, the size of the database files is logged.
Sincerely,
Anthony Thomas
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eSLV51dOFHA.2604@.TK2MSFTNGP10.phx.gbl...
Hi,
As a good practice, I recommend you to monitor the MDF and LDF files daily
once or twice manually. The best approach for automatic monitoring is :-
1. Set the DB Size to a maximum size and make unrestricted growth by
percentage or MB
2. Then set up hard disk free space monitoring alert. So when ever your hard
disk goes beyond a specific amount you will get an alert.
See the below link to configure hard disk monitoring.
http://support.microsoft.com/?kbid=299921
Thanks
Hari
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxCBebdOFHA.3444@.tk2msftngp13.phx.gbl...
> Herbert
> If you open SQL Server Profiler you will find an event AutoGrowth (If I
> remember well) , so you can track the info as well as querying sysfiles
> system table (size column) (not recomended)
>
>
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:7973C596-C566-42F5-80CA-011D89D189AE@.microsoft.com...
>> Hi,
>> We are having more than 20 databases and we are merge replicating the
>> databases also. Currently for all databases we set the Data and Log file
>> growth as Automatically by percent. The size of each database is around
> 1277
>> MB.
>> Now if we Change the Data and Log File growth to resctricted mode is
>> there
>> is any problem will cause, if not then how we will be able to monitor the
>> Data and Log file growth and when we should increase the File Size.
>> Thanks,
>> Herbert
>

Monitor Data and Log File Growth

Hi,
We are having more than 20 databases and we are merge replicating the
databases also. Currently for all databases we set the Data and Log file
growth as Automatically by percent. The size of each database is around 127
7
MB.
Now if we Change the Data and Log File growth to resctricted mode is there
is any problem will cause, if not then how we will be able to monitor the
Data and Log file growth and when we should increase the File Size.
Thanks,
HerbertHerbert
If you open SQL Server Profiler you will find an event AutoGrowth (If I
remember well) , so you can track the info as well as querying sysfiles
system table (size column) (not recomended)
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:7973C596-C566-42F5-80CA-011D89D189AE@.microsoft.com...
> Hi,
> We are having more than 20 databases and we are merge replicating the
> databases also. Currently for all databases we set the Data and Log file
> growth as Automatically by percent. The size of each database is around
1277
> MB.
> Now if we Change the Data and Log File growth to resctricted mode is there
> is any problem will cause, if not then how we will be able to monitor the
> Data and Log file growth and when we should increase the File Size.
> Thanks,
> Herbert|||Hi,
As a good practice, I recommend you to monitor the MDF and LDF files daily
once or twice manually. The best approach for automatic monitoring is :-
1. Set the DB Size to a maximum size and make unrestricted growth by
percentage or MB
2. Then set up hard disk free space monitoring alert. So when ever your hard
disk goes beyond a specific amount you will get an alert.
See the below link to configure hard disk monitoring.
http://support.microsoft.com/?kbid=299921
Thanks
Hari
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxCBebdOFHA.3444@.tk2msftngp13.phx.gbl...
> Herbert
> If you open SQL Server Profiler you will find an event AutoGrowth (If I
> remember well) , so you can track the info as well as querying sysfiles
> system table (size column) (not recomended)
>
>
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:7973C596-C566-42F5-80CA-011D89D189AE@.microsoft.com...
> 1277
>|||Also read the following link
http://www.databasejournal.com/feat...cle.php/3339681
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eSLV51dOFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Hi,
> As a good practice, I recommend you to monitor the MDF and LDF files daily
> once or twice manually. The best approach for automatic monitoring is :-
> 1. Set the DB Size to a maximum size and make unrestricted growth by
> percentage or MB
> 2. Then set up hard disk free space monitoring alert. So when ever your
hard
> disk goes beyond a specific amount you will get an alert.
> See the below link to configure hard disk monitoring.
> http://support.microsoft.com/?kbid=299921
> Thanks
> Hari
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uxCBebdOFHA.3444@.tk2msftngp13.phx.gbl...
the[vbcol=seagreen]
file[vbcol=seagreen]
around[vbcol=seagreen]
the[vbcol=seagreen]
>|||When your free space percentage on the disk gets below 20%, it is time to
start thinking about a new allocation.
For trend analysis, use the Backup repository in msdb. Every time a backup
is written, the size of the database files is logged.
Sincerely,
Anthony Thomas
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eSLV51dOFHA.2604@.TK2MSFTNGP10.phx.gbl...
Hi,
As a good practice, I recommend you to monitor the MDF and LDF files daily
once or twice manually. The best approach for automatic monitoring is :-
1. Set the DB Size to a maximum size and make unrestricted growth by
percentage or MB
2. Then set up hard disk free space monitoring alert. So when ever your hard
disk goes beyond a specific amount you will get an alert.
See the below link to configure hard disk monitoring.
http://support.microsoft.com/?kbid=299921
Thanks
Hari
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxCBebdOFHA.3444@.tk2msftngp13.phx.gbl...
> Herbert
> If you open SQL Server Profiler you will find an event AutoGrowth (If I
> remember well) , so you can track the info as well as querying sysfiles
> system table (size column) (not recomended)
>
>
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:7973C596-C566-42F5-80CA-011D89D189AE@.microsoft.com...
> 1277
>

Monitor Data and Log File Growth

Hi,
We are having more than 20 databases and we are merge replicating the
databases also. Currently for all databases we set the Data and Log file
growth as Automatically by percent. The size of each database is around 1277
MB.
Now if we Change the Data and Log File growth to resctricted mode is there
is any problem will cause, if not then how we will be able to monitor the
Data and Log file growth and when we should increase the File Size.
Thanks,
Herbert
Herbert
If you open SQL Server Profiler you will find an event AutoGrowth (If I
remember well) , so you can track the info as well as querying sysfiles
system table (size column) (not recomended)
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:7973C596-C566-42F5-80CA-011D89D189AE@.microsoft.com...
> Hi,
> We are having more than 20 databases and we are merge replicating the
> databases also. Currently for all databases we set the Data and Log file
> growth as Automatically by percent. The size of each database is around
1277
> MB.
> Now if we Change the Data and Log File growth to resctricted mode is there
> is any problem will cause, if not then how we will be able to monitor the
> Data and Log file growth and when we should increase the File Size.
> Thanks,
> Herbert
|||Hi,
As a good practice, I recommend you to monitor the MDF and LDF files daily
once or twice manually. The best approach for automatic monitoring is :-
1. Set the DB Size to a maximum size and make unrestricted growth by
percentage or MB
2. Then set up hard disk free space monitoring alert. So when ever your hard
disk goes beyond a specific amount you will get an alert.
See the below link to configure hard disk monitoring.
http://support.microsoft.com/?kbid=299921
Thanks
Hari
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxCBebdOFHA.3444@.tk2msftngp13.phx.gbl...
> Herbert
> If you open SQL Server Profiler you will find an event AutoGrowth (If I
> remember well) , so you can track the info as well as querying sysfiles
> system table (size column) (not recomended)
>
>
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:7973C596-C566-42F5-80CA-011D89D189AE@.microsoft.com...
> 1277
>
|||Also read the following link
http://www.databasejournal.com/featu...le.php/3339681
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eSLV51dOFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Hi,
> As a good practice, I recommend you to monitor the MDF and LDF files daily
> once or twice manually. The best approach for automatic monitoring is :-
> 1. Set the DB Size to a maximum size and make unrestricted growth by
> percentage or MB
> 2. Then set up hard disk free space monitoring alert. So when ever your
hard[vbcol=seagreen]
> disk goes beyond a specific amount you will get an alert.
> See the below link to configure hard disk monitoring.
> http://support.microsoft.com/?kbid=299921
> Thanks
> Hari
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uxCBebdOFHA.3444@.tk2msftngp13.phx.gbl...
the[vbcol=seagreen]
file[vbcol=seagreen]
around[vbcol=seagreen]
the
>
|||When your free space percentage on the disk gets below 20%, it is time to
start thinking about a new allocation.
For trend analysis, use the Backup repository in msdb. Every time a backup
is written, the size of the database files is logged.
Sincerely,
Anthony Thomas

"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eSLV51dOFHA.2604@.TK2MSFTNGP10.phx.gbl...
Hi,
As a good practice, I recommend you to monitor the MDF and LDF files daily
once or twice manually. The best approach for automatic monitoring is :-
1. Set the DB Size to a maximum size and make unrestricted growth by
percentage or MB
2. Then set up hard disk free space monitoring alert. So when ever your hard
disk goes beyond a specific amount you will get an alert.
See the below link to configure hard disk monitoring.
http://support.microsoft.com/?kbid=299921
Thanks
Hari
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxCBebdOFHA.3444@.tk2msftngp13.phx.gbl...
> Herbert
> If you open SQL Server Profiler you will find an event AutoGrowth (If I
> remember well) , so you can track the info as well as querying sysfiles
> system table (size column) (not recomended)
>
>
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:7973C596-C566-42F5-80CA-011D89D189AE@.microsoft.com...
> 1277
>