Showing posts with label decided. Show all posts
Showing posts with label decided. Show all posts

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

Wednesday, March 7, 2012

Different Results With View Versus UDF

I have two views, for various reasons I decided to wrap a Select * From View with two UDFs. When run on their own, the UDFs return the exact same resultset as their respective view (did a compare and stuff to make sure). However, when I join them together, what was once 130,000 records (when joining the views) skyrockets into millions of records, and the resulting resultset is filled with duplicates. What is going on?Firstly I would use a stored procedure to return result sets, not UDF's. Secondly, it looks like you are getting a cartesian join, where every row in one table joins to many or every row in the other table. This is caused by incorrectly joining the two table (or views). This is illustrated by the duplicate rows you are getting. Check your joins, and make sure you join primary key to foreign key. To test the query, change the select list to a select count(*), and then run the query with just the first join condition. Then add each condition one by one. At some point the rowcount will explode. However, if the first join produces a massive row count, experiment with adding additional joins until you get the correct amount of records returned.

HTH

For more SQL tips, check out my blog:|||The exact same join with the exact same conditions using the views (which return an identical result set) returns the proper result.|||that's weird, you could have been executing a cross join|||You will have to post a repro script to demonstrate the problem. Otherwise it will be hard to guess what might be wrong. Your SELECT statement could be incorrect, the UDF could be wrong and so on.