Hi,

My BizTalkDTADb database is growing too fast. Is there any way I can reduce
the size of the database or restrict it's growth? Are options to restrict
it's growth available in HAT?

thanks,

hasan

[ Post a follow-up to this message ]

Hi,

Take a look @ this
http://weblogs.asp.net/christof_cla.../19/187776.aspx

Cheers,
Mike Watson


"hasan" wrote:

> Hi,
>
> My BizTalkDTADb database is growing too fast. Is there any way I can reduc
e
> the size of the database or restrict it's growth? Are options to restrict
> it's growth available in HAT?
>
> thanks,
>
> hasan

[ Post a follow-up to this message ]

thanks mike....I tried this but I run out of space on my hard disk before th
e
query completes. any ideas?

rgds,
hasan

"Mike Watson" wrote:
[vbcol=seagreen]
> Hi,
>
> Take a look @ this
> http://weblogs.asp.net/christof_cla.../19/187776.aspx
>
> Cheers,
> Mike Watson
>
>
> "hasan" wrote:
> 

[ Post a follow-up to this message ]

Set a date such that you delete some portion of the database at a time
for example instead of

DECLARE @Today datetime
SET @Today = GETDATE()
EXEC dtasp_PruneTrackingDatabase @Today

do the following

DECLARE @Today datetime
SET @Today = GETDATE() - 30
EXEC dtasp_PruneTrackingDatabase @Today

This will delete any data older than 30 days. Then you can go ahead steps of
10 days each. Like below

DECLARE @Today datetime
SET @Today = GETDATE() - 20
EXEC dtasp_PruneTrackingDatabase @Today

Once you have done this schedule a sql job to delete data older than say
10(it could be any number) days. This will help in maintaining the database
size.

"hasan" wrote:
[vbcol=seagreen]
> thanks mike....I tried this but I run out of space on my hard disk before 
the
> query completes. any ideas?
>
> rgds,
> hasan
>
> "Mike Watson" wrote:
> 

[ Post a follow-up to this message ]

You can turn off tracking if you don't need it. HAT provides a way to
change tracking configuration. Disabling tracking can make troubleshooting
harder however.

--------------------[vbcol=seagreen] 
<SukeshShetty@discussions.microsoft.com>[vbcol=seagreen] 
<1821DBDA-6140-4D9D-8693-9718DEB55B4B@microsoft.com>
<95D65769-5060-4E6B-B1AB-EC1FB137A86A@microsoft.com>[vbcol=seagreen] 
of[vbcol=seagreen] 
database[vbcol=seagreen] 
before the[vbcol=seagreen] 
http://weblogs.asp.net/christof_cla.../19/187776.aspx[vbcol=seagreen] 
can reduce[vbcol=seagreen] 
restrict[vbcol=seagreen] 

[ Post a follow-up to this message ]

Vladmir, how do I turn of tracking using HAT? If I do turn it of, will I be
able to see suspended messages?

Sukesh, I did something similar to what you said, only I did it at one month
intervals by SET @Today = 2004-10-21
but my BizTalkDTADb.mdf is still large. It took ages to execute the whole
procedure to the current date but my database has not reduced in size -
infact its even grown. Its now at 11GB and I have just 2GB of space left!!!

what now??

thanks,
--hasan

"Vladimir Pogrebinsky" wrote:

> You can turn off tracking if you don't need it. HAT provides a way to
> change tracking configuration. Disabling tracking can make troubleshooting
> harder however.
>
> -------------------- 
> <SukeshShetty@discussions.microsoft.com> 
> <1821DBDA-6140-4D9D-8693-9718DEB55B4B@microsoft.com>
> <95D65769-5060-4E6B-B1AB-EC1FB137A86A@microsoft.com> 
> of 
> database 
> before the 
> http://weblogs.asp.net/christof_cla.../19/187776.aspx 
> can reduce 
> restrict 
>
>

[ Post a follow-up to this message ]

Hi Hasan,

How old is your database. I would suggest you to write a SQL script which in
a loop passes a date parameter to the pruning script. Hence if your database
is like 60 day old. write a script which loops with a counter from 60 to 10
and pass this number to determine how old dat you want to delete. Something
like this:

DECLARE @Today datetime
DECLARE @Counter int

SET @Counter=60
WHILE @Counter>10
BEGIN
SET @Today = GETDATE() - @Counter
EXEC dtasp_PruneTrackingDatabase @Today
END

This will delete the HAT data for each day since last 60 days.

Sukesh.

"hasan" wrote:
[vbcol=seagreen]
> Vladmir, how do I turn of tracking using HAT? If I do turn it of, will I b
e
> able to see suspended messages?
>
> Sukesh, I did something similar to what you said, only I did it at one mon
th
> intervals by SET @Today = 2004-10-21
> but my BizTalkDTADb.mdf is still large. It took ages to execute the whole
> procedure to the current date but my database has not reduced in size -
> infact its even grown. Its now at 11GB and I have just 2GB of space left!!
!
>
> what now??
>
> thanks,
> --hasan
>
> "Vladimir Pogrebinsky" wrote:
> 

[ Post a follow-up to this message ]

I forgot to add this part. Doing all these steps will delete the data from
the database but it will still show you database size 11GB. Most of the size
shown is free and the database will reuse this space as it grows and not
increase the datafile size. If you wish to know how to reduce the size of th
e
datafile it would be a question for a SQL DBA. However Enterprise manager
does give you an option to shrink the database. You can try that once you
have cleaned the unwanted stuff by running the script mentioned earlier.

Sukesh.

"Sukesh Shetty" wrote:
[vbcol=seagreen]
> Hi Hasan,
>
> How old is your database. I would suggest you to write a SQL script which 
in
> a loop passes a date parameter to the pruning script. Hence if your databa
se
> is like 60 day old. write a script which loops with a counter from 60 to 1
0
> and pass this number to determine how old dat you want to delete. Somethin
g
> like this:
>
> DECLARE @Today datetime
> DECLARE @Counter int
>
> SET @Counter=60
> WHILE @Counter>10
>   BEGIN
>     SET @Today = GETDATE() - @Counter
>     EXEC dtasp_PruneTrackingDatabase @Today
>   END
>
> This will delete the HAT data for each day since last 60 days.
>
> Sukesh.
>
> "hasan" wrote:
> 

[ Post a follow-up to this message ]

In order to turn off tracking options, you need to go to HAT, and go throug
all the menu items under "Configuration" main menu.
WMI has a property that allows to globally turn off/on all the messages
(GlobalTrackingOption property on the MSBTS_GroupSetting WMI class). You
cannot set it through UI however.

Regarding purging I'm not sure if looping and purging a day worth of data
every time is a good idea. Reason is that purge will do a table scan
regardless of of the purge cut-off date. Making each purge smaller (e.g.
purge only one day of data) won't nessesarily improve the perf. However
increasing number of iterations will defenitely increase time. So I would
measure that first.

You can find some more info on purging here:
http://weblogs.asp.net/christof_cla.../19/187776.aspx

and here:
http://download.microsoft.com/downl...9a-8349-bf352fb
3d802/DTASizingGuidelines.exe



--------------------[vbcol=seagreen] 
<SukeshShetty@discussions.microsoft.com>[vbcol=seagreen] 
<1821DBDA-6140-4D9D-8693-9718DEB55B4B@microsoft.com>
<95D65769-5060-4E6B-B1AB-EC1FB137A86A@microsoft.com>
<2D487ACD-D360-40F7-A59A-EED762C8487E@microsoft.com>
<Vg8zkt4tEHA.3600@cpmsftngxa10.phx.gbl>
<AB661808-8F20-4251-AA11-E0B0B19EDFAD@microsoft.com>[vbcol=seagreen] 
in[vbcol=seagreen] 
database[vbcol=seagreen] 
10[vbcol=seagreen] 
Something[vbcol=seagreen] 
I be[vbcol=seagreen] 
month[vbcol=seagreen] 
whole[vbcol=seagreen] 
left!!![vbcol=seagreen] 
troubleshooting[vbcol=seagreen] 
time[vbcol=seagreen] 
steps[vbcol=seagreen] 
than say[vbcol=seagreen] 
disk[vbcol=seagreen] 
http://weblogs.asp.net/christof_cla.../19/187776.aspx[vbcol=seagreen] 
way I[vbcol=seagreen] 
to[vbcol=seagreen] 

[ Post a follow-up to this message ]

I agree, I think what is important to him though is the waiting for the
completion, it may seem easier to run something that takes 10 minutes 3 time
s
than to run something once that takes 30 minutes.

For optimum performance, one should build a script to prune the database
messages in a manner that keeps an acceptable time period of tracking,
balanced with an acceptable space requirement. then use that script on a
periodic basis, perhaps as a scheduled event or job.

Thanks

Chris

"Vladimir Pogrebinsky" wrote:

> In order to turn off tracking options, you need to go to HAT, and go throu
g
> all the menu items under "Configuration" main menu.
> WMI has a property that allows to globally turn off/on all the messages
> (GlobalTrackingOption property on the MSBTS_GroupSetting WMI class). You
> cannot set it through UI however.
>
> Regarding purging I'm not sure if looping and purging a day worth of data
> every time is a good idea. Reason is that purge will do a table scan
> regardless of of the purge cut-off date. Making each purge smaller (e.g.
> purge only one day of data) won't nessesarily improve the perf. However
> increasing number of iterations will defenitely increase time. So I would
> measure that first.
>
> You can find some more info on purging here:
> http://weblogs.asp.net/christof_cla.../19/187776.aspx
>
> and here:
> http://download.microsoft.com/downl.../19/187776.aspx 
> way I 
> to 
>
>

[ Post a follow-up to this message ]