long processing time

Hi all,
I have a datawarehouse which contain a historical table holding close to 100
million records... this table is growing exponentially. Everytime I process
my cube, data has to be read from this table and it is taking very long
before data is returned (between 2-3 hours)
Is there anyway for me to speed this up?
Troubled,
NestorIf you have the Enterprise edition of SQL Server you should look into
partitioning. With partitions you can process things in parallel and you
can also possibly only re-process recent data.
Depending on your situation you may also be able to look into
incremental processing, but this only works if your dimensions do not
change (you can add new members, but moving existing members under new
parents will break incremental processing)
You should also make sure your schema is optimized so that you are
minimizing the joins that have to be done against the relational data
source.
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
In article <edXVSA44FHA.1416@.TK2MSFTNGP09.phx.gbl>, n3570r@.yahoo.com
says...
> Hi all,
> I have a datawarehouse which contain a historical table holding close to 1
00
> million records... this table is growing exponentially. Everytime I proces
s
> my cube, data has to be read from this table and it is taking very long
> before data is returned (between 2-3 hours)
> Is there anyway for me to speed this up?
> Troubled,
> Nestor
>
>

long processing time

Hi all,
I have a datawarehouse which contain a historical table holding close to 100
million records... this table is growing exponentially. Everytime I process
my cube, data has to be read from this table and it is taking very long
before data is returned (between 2-3 hours)
Is there anyway for me to speed this up?
Troubled,
Nestor
If you have the Enterprise edition of SQL Server you should look into
partitioning. With partitions you can process things in parallel and you
can also possibly only re-process recent data.
Depending on your situation you may also be able to look into
incremental processing, but this only works if your dimensions do not
change (you can add new members, but moving existing members under new
parents will break incremental processing)
You should also make sure your schema is optimized so that you are
minimizing the joins that have to be done against the relational data
source.
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
In article <edXVSA44FHA.1416@.TK2MSFTNGP09.phx.gbl>, n3570r@.yahoo.com
says...
> Hi all,
> I have a datawarehouse which contain a historical table holding close to 100
> million records... this table is growing exponentially. Everytime I process
> my cube, data has to be read from this table and it is taking very long
> before data is returned (between 2-3 hours)
> Is there anyway for me to speed this up?
> Troubled,
> Nestor
>
>
sql

LOGS Files

Hi All,
SQL 2000 sp4 under Windows 2000 server sp4.
I have problem with my logs files, they are growing so much. I have
optimization job schedule once a week but looks like it's no helping with
logs files.
To fix a problem this morning on my server we have to detach the data,
delete the log file and attach the data again to be able to work.
Should I create a script to detach, delete the log files and attach the data
again everyday at midnight?
I have a low budget so I can't use third party software
It's a better way to fix this?
Tks in advance.
JFBJFB,
What is your recovery model set to. Is it set to full? if yes I am
assuming this is a production database.
Have you tried using DBCC SHRINKFILE as a nightly job, rather than detaching
and reattaching data everyday.
Thanks,
Rocky A
"JFB" wrote:

> Hi All,
> SQL 2000 sp4 under Windows 2000 server sp4.
> I have problem with my logs files, they are growing so much. I have
> optimization job schedule once a week but looks like it's no helping with
> logs files.
> To fix a problem this morning on my server we have to detach the data,
> delete the log file and attach the data again to be able to work.
> Should I create a script to detach, delete the log files and attach the da
ta
> again everyday at midnight?
> I have a low budget so I can't use third party software
> It's a better way to fix this?
> Tks in advance.
> JFB
>
>|||Run regular transaction log backups to address the issues
with the size of the log files.
-Sue
On Mon, 1 May 2006 17:43:35 -0400, "JFB" <help@.jfb.com>
wrote:

>Hi All,
>SQL 2000 sp4 under Windows 2000 server sp4.
>I have problem with my logs files, they are growing so much. I have
>optimization job schedule once a week but looks like it's no helping with
>logs files.
>To fix a problem this morning on my server we have to detach the data,
>delete the log file and attach the data again to be able to work.
>Should I create a script to detach, delete the log files and attach the dat
a
>again everyday at midnight?
>I have a low budget so I can't use third party software
>It's a better way to fix this?
>Tks in advance.
>JFB
>|||If you not require transaction log backup set recovery model to simple.
else if recovery model is not simple
Take transaction log backup regularaly , it will truncate commited log.
or in critical condition you can use
backup log <dbname> with no_log.
This will truncate log without backup , but its not recommended , since
it will break chain of transaction log.
Dont play with database by detaching backup and deleting log files.
Regards
Amish shah|||Tks for reply.
I have daily log backups as part of my maintenance schedule jobs using this
EXEC msdb..prc_DBMaint_SLSBackup '-PlanID
581A1AC8-129F-4814-BBA3-07B066A49109 -WriteHistory -VrfyBackup -BkUpOnlyIfC
lean
-CkDB -BkUpMedia DISK -BkUpLog "E:\MSSQL\BackupLogs" -DelBkUps
2WEEKS -CrBkSubDir -BkExt "TRN"'
But looks like it's not truncated commited log.
It's a way to include the no_log in this command line?
Rgds
JFB
"amish" <shahamishm@.gmail.com> wrote in message
news:1146568036.357821.223340@.v46g2000cwv.googlegroups.com...
> If you not require transaction log backup set recovery model to simple.
> else if recovery model is not simple
> Take transaction log backup regularaly , it will truncate commited log.
> or in critical condition you can use
> backup log <dbname> with no_log.
> This will truncate log without backup , but its not recommended , since
> it will break chain of transaction log.
> Dont play with database by detaching backup and deleting log files.
> Regards
> Amish shah
>|||Did you verify that transaction log backups are actually
going to the BackupLogs directory?
You may also need to increase the frequency of your log
backups.
With maintenance plans, you don't have as much flexibility
on the various commands. There is no option to do a no_log
or truncate_only through maintenance plans.
-Sue
On Tue, 2 May 2006 10:37:43 -0400, "JFB" <help@.jfb.com>
wrote:

>Tks for reply.
>I have daily log backups as part of my maintenance schedule jobs using this
>EXEC msdb..prc_DBMaint_SLSBackup '-PlanID
>581A1AC8-129F-4814-BBA3-07B066A49109 -WriteHistory -VrfyBackup -BkUpOnlyIf
Clean
> -CkDB -BkUpMedia DISK -BkUpLog "E:\MSSQL\BackupLogs" -DelBkUps
>2WEEKS -CrBkSubDir -BkExt "TRN"'
>But looks like it's not truncated commited log.
>It's a way to include the no_log in this command line?
>Rgds
>JFB
>
>"amish" <shahamishm@.gmail.com> wrote in message
>news:1146568036.357821.223340@.v46g2000cwv.googlegroups.com...
>|||Tks for you reply Sue,
So how can I do it on a store procedure?
Any link or example... I'm going to install probably another HD to my raid
to fix this problem temporaly but I need to cut this logs..
Please help
Rgds
JFB
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:j44i52d1v8ta0lch1tm8od9di8vdsd911t@.
4ax.com...
> Did you verify that transaction log backups are actually
> going to the BackupLogs directory?
> You may also need to increase the frequency of your log
> backups.
> With maintenance plans, you don't have as much flexibility
> on the various commands. There is no option to do a no_log
> or truncate_only through maintenance plans.
> -Sue
> On Tue, 2 May 2006 10:37:43 -0400, "JFB" <help@.jfb.com>
> wrote:
>
>|||The SQL statement would be:
Backup Log YourDB
with truncate_only
-Sue
On Fri, 5 May 2006 21:30:00 -0400, "JFB" <help@.jfb.com>
wrote:

>Tks for you reply Sue,
>So how can I do it on a store procedure?
>Any link or example... I'm going to install probably another HD to my raid
>to fix this problem temporaly but I need to cut this logs..
>Please help
>Rgds
>JFB
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:j44i52d1v8ta0lch1tm8od9di8vdsd911t@.
4ax.com...
>

LOGS Files

Hi All,
SQL 2000 sp4 under windows 2000 server sp4.
I have problem with my logs files, they are growing so much. I have
optimization job schedule once a week but looks like it's no helping with
logs files.
To fix a problem this morning on my server we have to detach the data,
delete the log file and attach the data again to be able to work.
Should I create a script to detach, delete the log files and attach the data
again everyday at midnight?
I have a low budget so I can't use third party software :(
It's a better way to fix this?
Tks in advance.
JFBJFB,
What is your recovery model set to. Is it set to full? if yes I am
assuming this is a production database.
Have you tried using DBCC SHRINKFILE as a nightly job, rather than detaching
and reattaching data everyday.
Thanks,
Rocky A
"JFB" wrote:
> Hi All,
> SQL 2000 sp4 under windows 2000 server sp4.
> I have problem with my logs files, they are growing so much. I have
> optimization job schedule once a week but looks like it's no helping with
> logs files.
> To fix a problem this morning on my server we have to detach the data,
> delete the log file and attach the data again to be able to work.
> Should I create a script to detach, delete the log files and attach the data
> again everyday at midnight?
> I have a low budget so I can't use third party software :(
> It's a better way to fix this?
> Tks in advance.
> JFB
>
>|||Run regular transaction log backups to address the issues
with the size of the log files.
-Sue
On Mon, 1 May 2006 17:43:35 -0400, "JFB" <help@.jfb.com>
wrote:
>Hi All,
>SQL 2000 sp4 under windows 2000 server sp4.
>I have problem with my logs files, they are growing so much. I have
>optimization job schedule once a week but looks like it's no helping with
>logs files.
>To fix a problem this morning on my server we have to detach the data,
>delete the log file and attach the data again to be able to work.
>Should I create a script to detach, delete the log files and attach the data
>again everyday at midnight?
>I have a low budget so I can't use third party software :(
>It's a better way to fix this?
>Tks in advance.
>JFB
>|||If you not require transaction log backup set recovery model to simple.
else if recovery model is not simple
Take transaction log backup regularaly , it will truncate commited log.
or in critical condition you can use
backup log <dbname> with no_log.
This will truncate log without backup , but its not recommended , since
it will break chain of transaction log.
Dont play with database by detaching backup and deleting log files.
Regards
Amish shah|||Tks for reply.
I have daily log backups as part of my maintenance schedule jobs using this
EXEC msdb..prc_DBMaint_SLSBackup '-PlanID
581A1AC8-129F-4814-BBA3-07B066A49109 -WriteHistory -VrfyBackup -BkUpOnlyIfClean
-CkDB -BkUpMedia DISK -BkUpLog "E:\MSSQL\BackupLogs" -DelBkUps
2WEEKS -CrBkSubDir -BkExt "TRN"'
But looks like it's not truncated commited log.
It's a way to include the no_log in this command line?
Rgds
JFB
"amish" <shahamishm@.gmail.com> wrote in message
news:1146568036.357821.223340@.v46g2000cwv.googlegroups.com...
> If you not require transaction log backup set recovery model to simple.
> else if recovery model is not simple
> Take transaction log backup regularaly , it will truncate commited log.
> or in critical condition you can use
> backup log <dbname> with no_log.
> This will truncate log without backup , but its not recommended , since
> it will break chain of transaction log.
> Dont play with database by detaching backup and deleting log files.
> Regards
> Amish shah
>|||Did you verify that transaction log backups are actually
going to the BackupLogs directory?
You may also need to increase the frequency of your log
backups.
With maintenance plans, you don't have as much flexibility
on the various commands. There is no option to do a no_log
or truncate_only through maintenance plans.
-Sue
On Tue, 2 May 2006 10:37:43 -0400, "JFB" <help@.jfb.com>
wrote:
>Tks for reply.
>I have daily log backups as part of my maintenance schedule jobs using this
>EXEC msdb..prc_DBMaint_SLSBackup '-PlanID
>581A1AC8-129F-4814-BBA3-07B066A49109 -WriteHistory -VrfyBackup -BkUpOnlyIfClean
> -CkDB -BkUpMedia DISK -BkUpLog "E:\MSSQL\BackupLogs" -DelBkUps
>2WEEKS -CrBkSubDir -BkExt "TRN"'
>But looks like it's not truncated commited log.
>It's a way to include the no_log in this command line?
>Rgds
>JFB
>
>"amish" <shahamishm@.gmail.com> wrote in message
>news:1146568036.357821.223340@.v46g2000cwv.googlegroups.com...
>> If you not require transaction log backup set recovery model to simple.
>> else if recovery model is not simple
>> Take transaction log backup regularaly , it will truncate commited log.
>> or in critical condition you can use
>> backup log <dbname> with no_log.
>> This will truncate log without backup , but its not recommended , since
>> it will break chain of transaction log.
>> Dont play with database by detaching backup and deleting log files.
>> Regards
>> Amish shah
>|||Tks for you reply Sue,
So how can I do it on a store procedure?
Any link or example... I'm going to install probably another HD to my raid
to fix this problem temporaly but I need to cut this logs..
Please help
Rgds
JFB
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:j44i52d1v8ta0lch1tm8od9di8vdsd911t@.4ax.com...
> Did you verify that transaction log backups are actually
> going to the BackupLogs directory?
> You may also need to increase the frequency of your log
> backups.
> With maintenance plans, you don't have as much flexibility
> on the various commands. There is no option to do a no_log
> or truncate_only through maintenance plans.
> -Sue
> On Tue, 2 May 2006 10:37:43 -0400, "JFB" <help@.jfb.com>
> wrote:
>>Tks for reply.
>>I have daily log backups as part of my maintenance schedule jobs using
>>this
>>EXEC msdb..prc_DBMaint_SLSBackup '-PlanID
>>581A1AC8-129F-4814-BBA3-07B066A49109 -WriteHistory -VrfyBackup -BkUpOnlyIfClean
>> -CkDB -BkUpMedia DISK -BkUpLog "E:\MSSQL\BackupLogs" -DelBkUps
>>2WEEKS -CrBkSubDir -BkExt "TRN"'
>>But looks like it's not truncated commited log.
>>It's a way to include the no_log in this command line?
>>Rgds
>>JFB
>>
>>"amish" <shahamishm@.gmail.com> wrote in message
>>news:1146568036.357821.223340@.v46g2000cwv.googlegroups.com...
>> If you not require transaction log backup set recovery model to simple.
>> else if recovery model is not simple
>> Take transaction log backup regularaly , it will truncate commited log.
>> or in critical condition you can use
>> backup log <dbname> with no_log.
>> This will truncate log without backup , but its not recommended , since
>> it will break chain of transaction log.
>> Dont play with database by detaching backup and deleting log files.
>> Regards
>> Amish shah
>>
>|||The SQL statement would be:
Backup Log YourDB
with truncate_only
-Sue
On Fri, 5 May 2006 21:30:00 -0400, "JFB" <help@.jfb.com>
wrote:
>Tks for you reply Sue,
>So how can I do it on a store procedure?
>Any link or example... I'm going to install probably another HD to my raid
>to fix this problem temporaly but I need to cut this logs..
>Please help
>Rgds
>JFB
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:j44i52d1v8ta0lch1tm8od9di8vdsd911t@.4ax.com...
>> Did you verify that transaction log backups are actually
>> going to the BackupLogs directory?
>> You may also need to increase the frequency of your log
>> backups.
>> With maintenance plans, you don't have as much flexibility
>> on the various commands. There is no option to do a no_log
>> or truncate_only through maintenance plans.
>> -Sue
>> On Tue, 2 May 2006 10:37:43 -0400, "JFB" <help@.jfb.com>
>> wrote:
>>Tks for reply.
>>I have daily log backups as part of my maintenance schedule jobs using
>>this
>>EXEC msdb..prc_DBMaint_SLSBackup '-PlanID
>>581A1AC8-129F-4814-BBA3-07B066A49109 -WriteHistory -VrfyBackup -BkUpOnlyIfClean
>> -CkDB -BkUpMedia DISK -BkUpLog "E:\MSSQL\BackupLogs" -DelBkUps
>>2WEEKS -CrBkSubDir -BkExt "TRN"'
>>But looks like it's not truncated commited log.
>>It's a way to include the no_log in this command line?
>>Rgds
>>JFB
>>
>>"amish" <shahamishm@.gmail.com> wrote in message
>>news:1146568036.357821.223340@.v46g2000cwv.googlegroups.com...
>> If you not require transaction log backup set recovery model to simple.
>> else if recovery model is not simple
>> Take transaction log backup regularaly , it will truncate commited log.
>> or in critical condition you can use
>> backup log <dbname> with no_log.
>> This will truncate log without backup , but its not recommended , since
>> it will break chain of transaction log.
>> Dont play with database by detaching backup and deleting log files.
>> Regards
>> Amish shah
>>
>sql