| Author |
BizTalkDTADb too large!
|
|
|
| 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
| |
| Mike Watson 2004-10-21, 2:47 am |
| 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 reduce
> the size of the database or restrict it's growth? Are options to restrict
> it's growth available in HAT?
>
> thanks,
>
> hasan
| |
|
| 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:
[vbcol=seagreen]
> Hi,
>
> Take a look @ this
> http://weblogs.asp.net/christof_cla.../19/187776.aspx
>
> Cheers,
> Mike Watson
>
>
> "hasan" wrote:
>
| |
| Sukesh Shetty 2004-10-21, 7:47 am |
| 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:
>
| |
| Vladimir Pogrebinsky 2004-10-21, 5:51 pm |
| 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]
| |
|
| 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
>
>
| |
| Sukesh Shetty 2004-10-25, 2:46 am |
| 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 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:
>
| |
| Sukesh Shetty 2004-10-25, 2:46 am |
| 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 the
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 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:
>
| |
| Vladimir Pogrebinsky 2004-10-28, 5:49 pm |
| 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]
| |
| ChrisTucker 2004-12-31, 5:48 pm |
| 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 times
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 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
>
>
>
> --------------------
> <SukeshShetty@discussions.microsoft.com>
> <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>
> in
> database
> 10
> Something
> I be
> month
> whole
> left!!!
> troubleshooting
> time
> steps
> than say
> disk
> http://weblogs.asp.net/christof_cla.../19/187776.aspx
> way I
> to
>
>
|
|
|
|