Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJ
Please post the exact DDL. Are these views that contain aggregates or are
you aggregating the data from the views?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<aj70000@.hotmail.com> wrote in message
news:1142034715.639722.315230@.u72g2000cwu.googlegr oups.com...
Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJ
|||they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ
|||Both of the views - revenue and expenses - are invalid in SQL Server. You
have a combination of aggregate and non-aggregate columns in the SELECT
lists, without having a GROUP BY. How about giving us the exact scripts
for those views?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<aj70000@.hotmail.com> wrote in message
news:1142040048.264954.327010@.i39g2000cwa.googlegr oups.com...
they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ
|||Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ
|||If I cut and paste those statements, they don't work.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<aj70000@.hotmail.com> wrote in message
news:1142046177.298720.116610@.i39g2000cwa.googlegr oups.com...
Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ
|||On 10 Mar 2006 15:51:55 -0800, aj70000@.hotmail.com wrote:
>Hi,
>-I have 2 views 1)revenue, 2)expenses
>-Columns are Client,Year,sum(Amount),Business unit. on both of them
>Need some help on writing the query.
>I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
>Thanks
>AJ
Hi AJ,
Try if this works for you. If not, see www.aspfaq.com.5006 to find out
how to post CREATE TABLE and CREATE VIEW statements for the table and
view structures, INSERT statements for sample data, and required output.
SELECT Client, Year, BU, SUM(Amount)
FROM (SELECT Client, Year, BU, Amount
FROM Revenue
UNION ALL
SELECT Client, Year, BU, -Amount
FROM Expenses) AS D
GROUP BY Client, Year, BU
Hugo Kornelis, SQL Server MVP
|||dept (BU) is a funny name for a column.
the logic is going to be tough to fgiure out, and even tougher to
maintain over time. How about you combine table one and table two,
have a dollar amount, include the account or a field that indicates
whether a row is expense or revenue?
Showing posts with label amount. Show all posts
Showing posts with label amount. Show all posts
Monday, March 19, 2012
differnce between 2 aggregate columns
Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJPlease post the exact DDL. Are these views that contain aggregates or are
you aggregating the data from the views?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142034715.639722.315230@.u72g2000cwu.googlegroups.com...
Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJ|||they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ|||Both of the views - revenue and expenses - are invalid in SQL Server. You
have a combination of aggregate and non-aggregate columns in the SELECT
lists, without having a GROUP BY. How about giving us the exact scripts
for those views?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142040048.264954.327010@.i39g2000cwa.googlegroups.com...
they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ|||Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ|||If I cut and paste those statements, they don't work.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142046177.298720.116610@.i39g2000cwa.googlegroups.com...
Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ|||On 10 Mar 2006 15:51:55 -0800, aj70000@.hotmail.com wrote:
>Hi,
>-I have 2 views 1)revenue, 2)expenses
>-Columns are Client,Year,sum(Amount),Business unit. on both of them
>Need some help on writing the query.
>I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
>Thanks
>AJ
Hi AJ,
Try if this works for you. If not, see www.aspfaq.com.5006 to find out
how to post CREATE TABLE and CREATE VIEW statements for the table and
view structures, INSERT statements for sample data, and required output.
SELECT Client, Year, BU, SUM(Amount)
FROM (SELECT Client, Year, BU, Amount
FROM Revenue
UNION ALL
SELECT Client, Year, BU, -Amount
FROM Expenses) AS D
GROUP BY Client, Year, BU
Hugo Kornelis, SQL Server MVP|||dept (BU) is a funny name for a column.
the logic is going to be tough to fgiure out, and even tougher to
maintain over time. How about you combine table one and table two,
have a dollar amount, include the account or a field that indicates
whether a row is expense or revenue?
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJPlease post the exact DDL. Are these views that contain aggregates or are
you aggregating the data from the views?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142034715.639722.315230@.u72g2000cwu.googlegroups.com...
Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJ|||they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ|||Both of the views - revenue and expenses - are invalid in SQL Server. You
have a combination of aggregate and non-aggregate columns in the SELECT
lists, without having a GROUP BY. How about giving us the exact scripts
for those views?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142040048.264954.327010@.i39g2000cwa.googlegroups.com...
they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ|||Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ|||If I cut and paste those statements, they don't work.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142046177.298720.116610@.i39g2000cwa.googlegroups.com...
Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ|||On 10 Mar 2006 15:51:55 -0800, aj70000@.hotmail.com wrote:
>Hi,
>-I have 2 views 1)revenue, 2)expenses
>-Columns are Client,Year,sum(Amount),Business unit. on both of them
>Need some help on writing the query.
>I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
>Thanks
>AJ
Hi AJ,
Try if this works for you. If not, see www.aspfaq.com.5006 to find out
how to post CREATE TABLE and CREATE VIEW statements for the table and
view structures, INSERT statements for sample data, and required output.
SELECT Client, Year, BU, SUM(Amount)
FROM (SELECT Client, Year, BU, Amount
FROM Revenue
UNION ALL
SELECT Client, Year, BU, -Amount
FROM Expenses) AS D
GROUP BY Client, Year, BU
Hugo Kornelis, SQL Server MVP|||dept (BU) is a funny name for a column.
the logic is going to be tough to fgiure out, and even tougher to
maintain over time. How about you combine table one and table two,
have a dollar amount, include the account or a field that indicates
whether a row is expense or revenue?
differnce between 2 aggregate columns
Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJPlease post the exact DDL. Are these views that contain aggregates or are
you aggregating the data from the views?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142034715.639722.315230@.u72g2000cwu.googlegroups.com...
Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJ|||they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ|||Both of the views - revenue and expenses - are invalid in SQL Server. You
have a combination of aggregate and non-aggregate columns in the SELECT
lists, without having a GROUP BY. How about giving us the exact scripts
for those views?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142040048.264954.327010@.i39g2000cwa.googlegroups.com...
they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ|||Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ|||If I cut and paste those statements, they don't work.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142046177.298720.116610@.i39g2000cwa.googlegroups.com...
Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ|||On 10 Mar 2006 15:51:55 -0800, aj70000@.hotmail.com wrote:
>Hi,
>-I have 2 views 1)revenue, 2)expenses
>-Columns are Client,Year,sum(Amount),Business unit. on both of them
>Need some help on writing the query.
>I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
>Thanks
>AJ
Hi AJ,
Try if this works for you. If not, see www.aspfaq.com.5006 to find out
how to post CREATE TABLE and CREATE VIEW statements for the table and
view structures, INSERT statements for sample data, and required output.
SELECT Client, Year, BU, SUM(Amount)
FROM (SELECT Client, Year, BU, Amount
FROM Revenue
UNION ALL
SELECT Client, Year, BU, -Amount
FROM Expenses) AS D
GROUP BY Client, Year, BU
--
Hugo Kornelis, SQL Server MVP|||dept (BU) is a funny name for a column.
the logic is going to be tough to fgiure out, and even tougher to
maintain over time. How about you combine table one and table two,
have a dollar amount, include the account or a field that indicates
whether a row is expense or revenue?
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJPlease post the exact DDL. Are these views that contain aggregates or are
you aggregating the data from the views?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142034715.639722.315230@.u72g2000cwu.googlegroups.com...
Hi,
-I have 2 views 1)revenue, 2)expenses
-Columns are Client,Year,sum(Amount),Business unit. on both of them
Need some help on writing the query.
I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
Thanks
AJ|||they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ|||Both of the views - revenue and expenses - are invalid in SQL Server. You
have a combination of aggregate and non-aggregate columns in the SELECT
lists, without having a GROUP BY. How about giving us the exact scripts
for those views?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142040048.264954.327010@.i39g2000cwa.googlegroups.com...
they are 2 different views and I am aggregating them in a new view
DDL
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
the result I want is
select sum(amount) from table 1 group by year,client,BU
minus
select sum(amount) from table 2 group by year,client,BU
Basically getting Net income or net loss (revenue-expenses)
Thanks
AJ|||Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ|||If I cut and paste those statements, they don't work.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<aj70000@.hotmail.com> wrote in message
news:1142046177.298720.116610@.i39g2000cwa.googlegroups.com...
Tom,
I already gave the DDL for the views.
create view revenue as select client,amount,year,dept (BU) from table1;
create view expenses as select client,amount,year,dept (BU) from
table2;
AJ|||On 10 Mar 2006 15:51:55 -0800, aj70000@.hotmail.com wrote:
>Hi,
>-I have 2 views 1)revenue, 2)expenses
>-Columns are Client,Year,sum(Amount),Business unit. on both of them
>Need some help on writing the query.
>I would like sum(a.amount)-sum(b.amount) group by year,bu,client.
>Thanks
>AJ
Hi AJ,
Try if this works for you. If not, see www.aspfaq.com.5006 to find out
how to post CREATE TABLE and CREATE VIEW statements for the table and
view structures, INSERT statements for sample data, and required output.
SELECT Client, Year, BU, SUM(Amount)
FROM (SELECT Client, Year, BU, Amount
FROM Revenue
UNION ALL
SELECT Client, Year, BU, -Amount
FROM Expenses) AS D
GROUP BY Client, Year, BU
--
Hugo Kornelis, SQL Server MVP|||dept (BU) is a funny name for a column.
the logic is going to be tough to fgiure out, and even tougher to
maintain over time. How about you combine table one and table two,
have a dollar amount, include the account or a field that indicates
whether a row is expense or revenue?
Sunday, March 11, 2012
Differential backups of sql2k w/BackupExec huge
I am running Sql2k to house my 7GB database. I ran a full backup this
morning and saw amount of data backed up was 7GB. I then decided to try
doing a differential backup to see if it might be a good mid-day solution.
Normally I would do several log backups during the day but because the
application does several unpredictable bulk inserts of external data daily,
my understanding is that this breaks the transaction log sequence and as
such log backup won't work.
Anyway, when I did this differential backup I was very surprised to find
that the 2 users on my system were able to make 2.35GB of changes in an hour
and a half. I know, that isn't necessarily what it means when you get such a
huge diff backup, but what I want to know is why could this be happening and
what options are available to me for mid-day backups. I ran another diff
backup an hour later and 2.4 GB was backed up.
My hunch is that something is causing checkpoints that are trunc'ing the log
such as the db or log auto-growing. To counter these possiblities, I
increased the size of the DB to 10GB and the log file is 500MB just to be
safe.
If I am right and something is causing checkpoints, how can I find out what
it is? I didn't write the app, I am just stuck supporting it.
Mike
<><Whether or not and when checkpoints are occurring does not affect
differential backups... Only data which has changed since the last complete
backup... so those users have changed mucho data...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Mike Strout" <m i k e s t r o u t @. h o t m a i l . c o m> wrote in message
news:vii9cd3i46t953@.corp.supernews.com...
> I am running Sql2k to house my 7GB database. I ran a full backup this
> morning and saw amount of data backed up was 7GB. I then decided to try
> doing a differential backup to see if it might be a good mid-day solution.
> Normally I would do several log backups during the day but because the
> application does several unpredictable bulk inserts of external data
daily,
> my understanding is that this breaks the transaction log sequence and as
> such log backup won't work.
> Anyway, when I did this differential backup I was very surprised to find
> that the 2 users on my system were able to make 2.35GB of changes in an
hour
> and a half. I know, that isn't necessarily what it means when you get such
a
> huge diff backup, but what I want to know is why could this be happening
and
> what options are available to me for mid-day backups. I ran another diff
> backup an hour later and 2.4 GB was backed up.
> My hunch is that something is causing checkpoints that are trunc'ing the
log
> such as the db or log auto-growing. To counter these possiblities, I
> increased the size of the DB to 10GB and the log file is 500MB just to be
> safe.
> If I am right and something is causing checkpoints, how can I find out
what
> it is? I didn't write the app, I am just stuck supporting it.
> Mike
> <><
>|||Is it possible that index rebuilds or some other bulk operation took place
that would have moved or changed a vast amount of pages in the database?
"Mike Strout" <m i k e s t r o u t @. h o t m a i l . c o m> wrote in message
news:vii9cd3i46t953@.corp.supernews.com...
> I am running Sql2k to house my 7GB database. I ran a full backup this
> morning and saw amount of data backed up was 7GB. I then decided to try
> doing a differential backup to see if it might be a good mid-day solution.
> Normally I would do several log backups during the day but because the
> application does several unpredictable bulk inserts of external data
daily,
> my understanding is that this breaks the transaction log sequence and as
> such log backup won't work.
> Anyway, when I did this differential backup I was very surprised to find
> that the 2 users on my system were able to make 2.35GB of changes in an
hour
> and a half. I know, that isn't necessarily what it means when you get such
a
> huge diff backup, but what I want to know is why could this be happening
and
> what options are available to me for mid-day backups. I ran another diff
> backup an hour later and 2.4 GB was backed up.
> My hunch is that something is causing checkpoints that are trunc'ing the
log
> such as the db or log auto-growing. To counter these possiblities, I
> increased the size of the DB to 10GB and the log file is 500MB just to be
> safe.
> If I am right and something is causing checkpoints, how can I find out
what
> it is? I didn't write the app, I am just stuck supporting it.
> Mike
> <><
>|||Mike Strout (m i k e s t r o u t @. h o t m a i l . c o m) writes:
> Normally I would do several log backups during the day but because the
> application does several unpredictable bulk inserts of external data
> daily, my understanding is that this breaks the transaction log sequence
> and as such log backup won't work.
No, that is not the case. Assuming of course, you are not running in
Simple recovery mode that is. And that you bulk-load operations does
not include BACKUP LOG db WITH NO_LOG.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
morning and saw amount of data backed up was 7GB. I then decided to try
doing a differential backup to see if it might be a good mid-day solution.
Normally I would do several log backups during the day but because the
application does several unpredictable bulk inserts of external data daily,
my understanding is that this breaks the transaction log sequence and as
such log backup won't work.
Anyway, when I did this differential backup I was very surprised to find
that the 2 users on my system were able to make 2.35GB of changes in an hour
and a half. I know, that isn't necessarily what it means when you get such a
huge diff backup, but what I want to know is why could this be happening and
what options are available to me for mid-day backups. I ran another diff
backup an hour later and 2.4 GB was backed up.
My hunch is that something is causing checkpoints that are trunc'ing the log
such as the db or log auto-growing. To counter these possiblities, I
increased the size of the DB to 10GB and the log file is 500MB just to be
safe.
If I am right and something is causing checkpoints, how can I find out what
it is? I didn't write the app, I am just stuck supporting it.
Mike
<><Whether or not and when checkpoints are occurring does not affect
differential backups... Only data which has changed since the last complete
backup... so those users have changed mucho data...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Mike Strout" <m i k e s t r o u t @. h o t m a i l . c o m> wrote in message
news:vii9cd3i46t953@.corp.supernews.com...
> I am running Sql2k to house my 7GB database. I ran a full backup this
> morning and saw amount of data backed up was 7GB. I then decided to try
> doing a differential backup to see if it might be a good mid-day solution.
> Normally I would do several log backups during the day but because the
> application does several unpredictable bulk inserts of external data
daily,
> my understanding is that this breaks the transaction log sequence and as
> such log backup won't work.
> Anyway, when I did this differential backup I was very surprised to find
> that the 2 users on my system were able to make 2.35GB of changes in an
hour
> and a half. I know, that isn't necessarily what it means when you get such
a
> huge diff backup, but what I want to know is why could this be happening
and
> what options are available to me for mid-day backups. I ran another diff
> backup an hour later and 2.4 GB was backed up.
> My hunch is that something is causing checkpoints that are trunc'ing the
log
> such as the db or log auto-growing. To counter these possiblities, I
> increased the size of the DB to 10GB and the log file is 500MB just to be
> safe.
> If I am right and something is causing checkpoints, how can I find out
what
> it is? I didn't write the app, I am just stuck supporting it.
> Mike
> <><
>|||Is it possible that index rebuilds or some other bulk operation took place
that would have moved or changed a vast amount of pages in the database?
"Mike Strout" <m i k e s t r o u t @. h o t m a i l . c o m> wrote in message
news:vii9cd3i46t953@.corp.supernews.com...
> I am running Sql2k to house my 7GB database. I ran a full backup this
> morning and saw amount of data backed up was 7GB. I then decided to try
> doing a differential backup to see if it might be a good mid-day solution.
> Normally I would do several log backups during the day but because the
> application does several unpredictable bulk inserts of external data
daily,
> my understanding is that this breaks the transaction log sequence and as
> such log backup won't work.
> Anyway, when I did this differential backup I was very surprised to find
> that the 2 users on my system were able to make 2.35GB of changes in an
hour
> and a half. I know, that isn't necessarily what it means when you get such
a
> huge diff backup, but what I want to know is why could this be happening
and
> what options are available to me for mid-day backups. I ran another diff
> backup an hour later and 2.4 GB was backed up.
> My hunch is that something is causing checkpoints that are trunc'ing the
log
> such as the db or log auto-growing. To counter these possiblities, I
> increased the size of the DB to 10GB and the log file is 500MB just to be
> safe.
> If I am right and something is causing checkpoints, how can I find out
what
> it is? I didn't write the app, I am just stuck supporting it.
> Mike
> <><
>|||Mike Strout (m i k e s t r o u t @. h o t m a i l . c o m) writes:
> Normally I would do several log backups during the day but because the
> application does several unpredictable bulk inserts of external data
> daily, my understanding is that this breaks the transaction log sequence
> and as such log backup won't work.
No, that is not the case. Assuming of course, you are not running in
Simple recovery mode that is. And that you bulk-load operations does
not include BACKUP LOG db WITH NO_LOG.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Subscribe to:
Posts (Atom)