Basic logging/processing db question

I have many worker systems logging data to a central database.
The central database needs to periodically process all that data. During this processing time, new records can't be added part way through and the workers still need to log the data somewhere.

I was thinking of the following approach:

1) Rename the table the worker logs to. Let's say from LogTable to ProcessingTable
2) Immediately create a new and empty table for the worker to log to (LogTable). Possibly wrap this and the preceeding rename step in a transaction which may provide some atomicity.
3) Process the ProcessingTable
4) Drop ProcessingTable.

To the db experts here, does this sound like a decent approach or are there problems with this?Sounds a bit dirty
can you give a bit more info on the type of processing. is this cube population or just calculations for oltp?
how often? and how much(data)?

I would prefer to put the new data in table A
use dts to process the data every hour and place it in the destination table b
and have querying happen full time on table b

this way you only have 1 hour of latency in your data
if the info is not as volitile then schedule processing more closely together.|||Problem #1 - you haven't paid the bill. Do you need an official invoice?
Problem #2 - you can't count on free advice while supporting your production.
Problem #3 - when advice is given and it's proven to be the right solution, - you need to follow it rather than getting yourself into unchartered waters with bigger problems on your hands.

Is that enough?|||sounds to me like someone is preaching the brainwashing he just got at his "Fanatical Customer Service" Based Job :p|||No, Scott, this has nothing to do with "Fanatical" brainwashing, it has to do rather with the guy paying the bill for the services that he received from me. Rings the bell now?|||oh what was i thinking poking fun at you? :rolleyes:

i'm just full of sour grapes because they ..... well ill save that story for next week when i get back and we get drunk. :D|||Hey, can't wait!!!

Kurt,

It's really not difficult, just dial the number and say that you guys were VERY busy, and the hole thing will go away, you know?!sql

Bad Update Performance with very large recordsets

When updating a table with 80 million records we are experiencing very bad
times. We found using maxdop1 to keep the process in one thread helpful,
but something is still causing problems.
Updating one bigint field which is nullable to a bigint value from a table
inner joined with the current table is crawling.
Should we remove the nullability of the field and set it to a default of -1?
Would this ensure space is allocated for the field? What else can we look
for?
What about NO LOCK or Serializable? Could
What would be the *fastest* way to update records in a table assuming only 1
connection to the database? How do fields allowing NULLs affect this?
Thanks.Never try to update 80 million rows at once if you can avoid it. Wrap the
updates in smaller batches of say 10, 50 or 100K in size. If no one else
needs to use the table while this is happening you can use TABLOCK hint to
help.
--
Andrew J. Kelly
SQL Server MVP
"Kory Yingling" <kory@.removeme-mlsc.com> wrote in message
news:u$j1RcBbDHA.1744@.TK2MSFTNGP12.phx.gbl...
> When updating a table with 80 million records we are experiencing very bad
> times. We found using maxdop1 to keep the process in one thread helpful,
> but something is still causing problems.
> Updating one bigint field which is nullable to a bigint value from a table
> inner joined with the current table is crawling.
> Should we remove the nullability of the field and set it to a default
of -1?
> Would this ensure space is allocated for the field? What else can we look
> for?
> What about NO LOCK or Serializable? Could
> What would be the *fastest* way to update records in a table assuming only
1
> connection to the database? How do fields allowing NULLs affect this?
> Thanks.
>

Backups causing connectivity issues

I'm maintaining a database with an automated backup process.

Periodically, it does a "BACKUP DATABASE" to a local file and then do a file copy across the network to another system. This file is 2.7GB and when it is being copied it causes network problems; specifically ADO connections to the database get broken.

Does anyone have experience with this kind of issue?A DOS Copy is causing a network problem?

I wouldn't think so.

Where's the code, and what's the specific error message.|||A DOS Copy is causing a network problem?

I wouldn't think so.

Where's the code, and what's the specific error message.

I have a C++ ADO client application that gets:

Database COM exception.
HRESULT = -2147467259 (0x80004005)
[DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.

This client application is running 24/7 on many different workstations. They all encounter this error and shutdown at the same time. It seems to only encounters this error during the file copy operation I mentioned. I assume that the 2.7GB file copy consumes so much bandwidth that it disrupts other network activity. CPU use on the server is low. I wouldn't have guessed this as the problem but that is certainly what the symptoms suggest.|||I still wouldn't put any money down on that...

But as check, why not copy the file over during periods of low activity...|||I still wouldn't put any money down on that...

But as check, why not copy the file over during periods of low activity...

We do. We copy it over at midnight. The C++ client app I mentioned needs to run 24/7/365 though and it can't lose network connectivity to the database (this is all on a LAN and has UPS).|||OK...out of the box thinking...is there anything else affected?

You sure it's not something to do with the way the app is constructed?

I'm no network guy, but can you put a sniffer on the network?|||DOS copy may be the same as Windows copy. As I understand things, Windows copy eats a lot of bandwidth in parity checks, and whatnot. Can you use FTP to deliver the database to an FTP server? That may be a lighter connection.|||Thanks guys. Those suggestions might work but the network guys agreed to just backup to a separate local hard drive. That will definitely spare the network.|||For files of these sizes to move over LAN people usually set up a dedicated NIC on a different subnet. If you spare a couple of bucks wisely (SQLLiteSpeed) you may shrink the size of the backup file down to around 500MB.

Backups

I'm in the process of setting up a backup strategy. I would like to store
all backups (full, diff, and transaction logs) for a single day in a single
file/dumpdevice.
However, I work for someone that INSISTS that every backup should be stored
in a seperate file. For example today for our server we would have 26 files
(not including master and msdb backups):
MyDatabase Full 2004-05-21 00.15.00.bak
Mydatabase Differential 2004-05-21 12.15.00.bak
Mydatabase Transactions 2004-05-21 00.59.00.bak
Mydatabase Transactions 2004-05-21 01.59.00.bak
..
..
..
Mydatabase Transactions 2004-05-21 23.59.00.bak
I think it would be nicer and easier to manager a single file 'MyDatabase
2004-05-21.bak' that contained all backups for the day or at least one file
that contained the full and differentials and one file that contained the
transactions.
Has anyone EVER had and problems with multiple backups in a single file?
Any other comments or suggestions are welcome.
Thanks!
I think you might be right about it is easier to manage one backup file
instead of multiple ones, but consider these thing:
1) When copying the backup file from one place to another the file will be
bigger, and therefore take more time. Plus all the backups will be moved
when you might only need a handfull of backups to do the restore.
2) It may take longer to read thorough the multiple files to restore just
the file you are looking for.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mark" <abc@.xyz.com> wrote in message
news:eXa3%23EzPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> I'm in the process of setting up a backup strategy. I would like to store
> all backups (full, diff, and transaction logs) for a single day in a
single
> file/dumpdevice.
> However, I work for someone that INSISTS that every backup should be
stored
> in a seperate file. For example today for our server we would have 26
files
> (not including master and msdb backups):
> MyDatabase Full 2004-05-21 00.15.00.bak
> Mydatabase Differential 2004-05-21 12.15.00.bak
> Mydatabase Transactions 2004-05-21 00.59.00.bak
> Mydatabase Transactions 2004-05-21 01.59.00.bak
> .
> .
> .
> Mydatabase Transactions 2004-05-21 23.59.00.bak
> I think it would be nicer and easier to manager a single file 'MyDatabase
> 2004-05-21.bak' that contained all backups for the day or at least one
file
> that contained the full and differentials and one file that contained the
> transactions.
> Has anyone EVER had and problems with multiple backups in a single file?
> Any other comments or suggestions are welcome.
> Thanks!
>
|||One thing you might want to consider is to have separate file for db backup vs. log backups. If the last db
backup is damaged, you can always to back to the one before that and then apply all subsequent log backups
(skipping the damaged db backup). IOW, a db backup doesn't break the chain of log backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark" <abc@.xyz.com> wrote in message news:eXa3%23EzPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> I'm in the process of setting up a backup strategy. I would like to store
> all backups (full, diff, and transaction logs) for a single day in a single
> file/dumpdevice.
> However, I work for someone that INSISTS that every backup should be stored
> in a seperate file. For example today for our server we would have 26 files
> (not including master and msdb backups):
> MyDatabase Full 2004-05-21 00.15.00.bak
> Mydatabase Differential 2004-05-21 12.15.00.bak
> Mydatabase Transactions 2004-05-21 00.59.00.bak
> Mydatabase Transactions 2004-05-21 01.59.00.bak
> .
> .
> .
> Mydatabase Transactions 2004-05-21 23.59.00.bak
> I think it would be nicer and easier to manager a single file 'MyDatabase
> 2004-05-21.bak' that contained all backups for the day or at least one file
> that contained the full and differentials and one file that contained the
> transactions.
> Has anyone EVER had and problems with multiple backups in a single file?
> Any other comments or suggestions are welcome.
> Thanks!
>

Backups

I'm in the process of setting up a backup strategy. I would like to store
all backups (full, diff, and transaction logs) for a single day in a single
file/dumpdevice.
However, I work for someone that INSISTS that every backup should be stored
in a seperate file. For example today for our server we would have 26 files
(not including master and msdb backups):
MyDatabase Full 2004-05-21 00.15.00.bak
Mydatabase Differential 2004-05-21 12.15.00.bak
Mydatabase Transactions 2004-05-21 00.59.00.bak
Mydatabase Transactions 2004-05-21 01.59.00.bak
.
.
.
Mydatabase Transactions 2004-05-21 23.59.00.bak
I think it would be nicer and easier to manager a single file 'MyDatabase
2004-05-21.bak' that contained all backups for the day or at least one file
that contained the full and differentials and one file that contained the
transactions.
Has anyone EVER had and problems with multiple backups in a single file?
Any other comments or suggestions are welcome.
Thanks!I think you might be right about it is easier to manage one backup file
instead of multiple ones, but consider these thing:
1) When copying the backup file from one place to another the file will be
bigger, and therefore take more time. Plus all the backups will be moved
when you might only need a handfull of backups to do the restore.
2) It may take longer to read thorough the multiple files to restore just
the file you are looking for.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mark" <abc@.xyz.com> wrote in message
news:eXa3%23EzPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> I'm in the process of setting up a backup strategy. I would like to store
> all backups (full, diff, and transaction logs) for a single day in a
single
> file/dumpdevice.
> However, I work for someone that INSISTS that every backup should be
stored
> in a seperate file. For example today for our server we would have 26
files
> (not including master and msdb backups):
> MyDatabase Full 2004-05-21 00.15.00.bak
> Mydatabase Differential 2004-05-21 12.15.00.bak
> Mydatabase Transactions 2004-05-21 00.59.00.bak
> Mydatabase Transactions 2004-05-21 01.59.00.bak
> .
> .
> .
> Mydatabase Transactions 2004-05-21 23.59.00.bak
> I think it would be nicer and easier to manager a single file 'MyDatabase
> 2004-05-21.bak' that contained all backups for the day or at least one
file
> that contained the full and differentials and one file that contained the
> transactions.
> Has anyone EVER had and problems with multiple backups in a single file?
> Any other comments or suggestions are welcome.
> Thanks!
>|||One thing you might want to consider is to have separate file for db backup
vs. log backups. If the last db
backup is damaged, you can always to back to the one before that and then ap
ply all subsequent log backups
(skipping the damaged db backup). IOW, a db backup doesn't break the chain o
f log backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark" <abc@.xyz.com> wrote in message news:eXa3%23EzPEHA.3016@.TK2MSFTNGP10.phx.gbl...seagreen">
> I'm in the process of setting up a backup strategy. I would like to store
> all backups (full, diff, and transaction logs) for a single day in a singl
e
> file/dumpdevice.
> However, I work for someone that INSISTS that every backup should be store
d
> in a seperate file. For example today for our server we would have 26 fil
es
> (not including master and msdb backups):
> MyDatabase Full 2004-05-21 00.15.00.bak
> Mydatabase Differential 2004-05-21 12.15.00.bak
> Mydatabase Transactions 2004-05-21 00.59.00.bak
> Mydatabase Transactions 2004-05-21 01.59.00.bak
> .
> .
> .
> Mydatabase Transactions 2004-05-21 23.59.00.bak
> I think it would be nicer and easier to manager a single file 'MyDatabase
> 2004-05-21.bak' that contained all backups for the day or at least one fil
e
> that contained the full and differentials and one file that contained the
> transactions.
> Has anyone EVER had and problems with multiple backups in a single file?
> Any other comments or suggestions are welcome.
> Thanks!
>

Backups

I'm in the process of setting up a backup strategy. I would like to store
all backups (full, diff, and transaction logs) for a single day in a single
file/dumpdevice.
However, I work for someone that INSISTS that every backup should be stored
in a seperate file. For example today for our server we would have 26 files
(not including master and msdb backups):
MyDatabase Full 2004-05-21 00.15.00.bak
Mydatabase Differential 2004-05-21 12.15.00.bak
Mydatabase Transactions 2004-05-21 00.59.00.bak
Mydatabase Transactions 2004-05-21 01.59.00.bak
.
.
.
Mydatabase Transactions 2004-05-21 23.59.00.bak
I think it would be nicer and easier to manager a single file 'MyDatabase
2004-05-21.bak' that contained all backups for the day or at least one file
that contained the full and differentials and one file that contained the
transactions.
Has anyone EVER had and problems with multiple backups in a single file?
Any other comments or suggestions are welcome.
Thanks!I think you might be right about it is easier to manage one backup file
instead of multiple ones, but consider these thing:
1) When copying the backup file from one place to another the file will be
bigger, and therefore take more time. Plus all the backups will be moved
when you might only need a handfull of backups to do the restore.
2) It may take longer to read thorough the multiple files to restore just
the file you are looking for.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mark" <abc@.xyz.com> wrote in message
news:eXa3%23EzPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> I'm in the process of setting up a backup strategy. I would like to store
> all backups (full, diff, and transaction logs) for a single day in a
single
> file/dumpdevice.
> However, I work for someone that INSISTS that every backup should be
stored
> in a seperate file. For example today for our server we would have 26
files
> (not including master and msdb backups):
> MyDatabase Full 2004-05-21 00.15.00.bak
> Mydatabase Differential 2004-05-21 12.15.00.bak
> Mydatabase Transactions 2004-05-21 00.59.00.bak
> Mydatabase Transactions 2004-05-21 01.59.00.bak
> .
> .
> .
> Mydatabase Transactions 2004-05-21 23.59.00.bak
> I think it would be nicer and easier to manager a single file 'MyDatabase
> 2004-05-21.bak' that contained all backups for the day or at least one
file
> that contained the full and differentials and one file that contained the
> transactions.
> Has anyone EVER had and problems with multiple backups in a single file?
> Any other comments or suggestions are welcome.
> Thanks!
>|||One thing you might want to consider is to have separate file for db backup vs. log backups. If the last db
backup is damaged, you can always to back to the one before that and then apply all subsequent log backups
(skipping the damaged db backup). IOW, a db backup doesn't break the chain of log backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mark" <abc@.xyz.com> wrote in message news:eXa3%23EzPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> I'm in the process of setting up a backup strategy. I would like to store
> all backups (full, diff, and transaction logs) for a single day in a single
> file/dumpdevice.
> However, I work for someone that INSISTS that every backup should be stored
> in a seperate file. For example today for our server we would have 26 files
> (not including master and msdb backups):
> MyDatabase Full 2004-05-21 00.15.00.bak
> Mydatabase Differential 2004-05-21 12.15.00.bak
> Mydatabase Transactions 2004-05-21 00.59.00.bak
> Mydatabase Transactions 2004-05-21 01.59.00.bak
> .
> .
> .
> Mydatabase Transactions 2004-05-21 23.59.00.bak
> I think it would be nicer and easier to manager a single file 'MyDatabase
> 2004-05-21.bak' that contained all backups for the day or at least one file
> that contained the full and differentials and one file that contained the
> transactions.
> Has anyone EVER had and problems with multiple backups in a single file?
> Any other comments or suggestions are welcome.
> Thanks!
>

backup to device

I am using a device to backup my user database for both data and the transaction log. I want to automate this process and I understand the syntax for the backup of log and data. I want to know what the syntax is for having the complete backup overwrite the existing backup in the device after a number of backups have occured. I can't find anything in the users manual for this. I also want to have my transaction log backups be overwritten periodically. With my current setup the backup device grows and grows
For example
after the third data backup I want the first backup to be overwritten so the device only contains that last three backups. I will backup the transaction log 2x per day, and I want to keep that last 6 transaction log backups to be stored on the device and then the oldest transaction log backup in the device will be overwritten.
thanks>after the third data backup I want the first backup to be overwritten so
the device only contains that last three backups.
I don't think this is doable. When you do backup you use WITH INIT or WITH
NOINIT to tell backup to orverwrite or append to the backup device. No way
you can tell it to purge the first backup set (if there are 3 exist) and
append a new backup set to the backup device. Similar to the log backup.
One thing you can do is you have 3 backup devices for each day. Lets say
you have BACKUP1, BACKUP2, BACKUP3. Do a full backup and 2 log backups
(appended) to each backup device every day. Schedule a job to run full
backup and another job to do log backup. Before each backup do an IF..ELSE
to find out what backup device was used the day before so your backup will
know what backup device to use today.
hth,
"Stephen Harris" <anonymous@.discussions.microsoft.com> wrote in message
news:2C185839-AF58-4269-B4B3-EA0335036FB3@.microsoft.com...
> I am using a device to backup my user database for both data and the
transaction log. I want to automate this process and I understand the
syntax for the backup of log and data. I want to know what the syntax is
for having the complete backup overwrite the existing backup in the device
after a number of backups have occured. I can't find anything in the users
manual for this. I also want to have my transaction log backups be
overwritten periodically. With my current setup the backup device grows and
grows.
> For example,
> after the third data backup I want the first backup to be overwritten so
the device only contains that last three backups. I will backup the
transaction log 2x per day, and I want to keep that last 6 transaction log
backups to be stored on the device and then the oldest transaction log
backup in the device will be overwritten.
> thanks