History Table Design Issue

Hi all,

this is more of a design issue for a History table.

Suppose if i have a transaction table and then based on the transactions i want to keep a history of those do i need to define Primary Key and Foreign Key for history table.

Regards,

General Problem

As a general rule of thumb, EVERY table should have a primary key.

I'm sure that there are exceptions (it's a scary world out there!), but the design assumption should be "A primary key is required!" until proven otherwise.

As for foreign keys, that's a different issue.

Let's say you have an employee table.

If you set up an FK from your history table to your employee table, you will not be able to delete an employee from the employee table as long as you maintain those history recorrds.

If you intended for the employee table to be defined as "A list of current employees of the business.", you have just changed the definition to "A list of current and past employees of the business."

That may, or may not, be what you want!

Now, if the primary key of the employee is a meaningless number, and you need to know which employee did that transaction, you might be stuck with keeping past employees in the employee table. Or, of course, you could add the employee ssn and name to the history table. If you used a natural key (like their SSN), then someone could always look it up in the paper trail. Lots of options here!

highly appreciated !

You are the DBA for a busy order entry database. You want to provide a
method for automatically backing up the transaction log for you database in
case it becomes more than 90 percent full prior to the next regularly
scheduled transaction log backup. What is the best way to accomplish this?
1.. Create an alert in Performance Monitor to call an OSQL script to
backup log the transaction log when it becomes more than 90% full.
2.. Create al alert in SQL Enterprise Manager that will call a job to
backup the transaction log when it becomes more than 90% full.
3.. Create an alert in SQL Enterpise Manager that will call an OSQL script
to backup the transaction log when it becomes more than 90% full.
4.. Create an alert in Performance Monitor that will call SQLALRTR.EXE to
trigger a SQL Server alert to backup the transaction log when it becomes
more than 90% full.
5.. Create a schedule transaction log backup for every 10 minutes.
6.. You cannot do this.
"Roy" schrieb:

> You are the DBA for a busy order entry database. You want to provide a
> method for automatically backing up the transaction log for you database in
> case it becomes more than 90 percent full prior to the next regularly
> scheduled transaction log backup. What is the best way to accomplish this?
> 1.. Create an alert in Performance Monitor to call an OSQL script to
> backup log the transaction log when it becomes more than 90% full.
> 2.. Create al alert in SQL Enterprise Manager that will call a job to
> backup the transaction log when it becomes more than 90% full.
> 3.. Create an alert in SQL Enterpise Manager that will call an OSQL script
> to backup the transaction log when it becomes more than 90% full.
> 4.. Create an alert in Performance Monitor that will call SQLALRTR.EXE to
> trigger a SQL Server alert to backup the transaction log when it becomes
> more than 90% full.
> 5.. Create a schedule transaction log backup for every 10 minutes.
> 6.. You cannot do this.
6 is out of question!!!
2 sounds reasonable. Attention: That will not be an event alert (there is no
event / error code for 'log 90% full') but a performance condition alert!

High CPU during transaction log backups!

High CPU during transaction log backups!
I'm having issues with a SQL server where during
transaction log backups, the CPU usage us on average 20%-
25% higher than during normal. We have timed this out
using performance monitor and know for sure it's the
transaction logs at this point. If we turn off transaction
logs, the CPUs continue normal without the 20%-25% spike.
We have our data volume physically separate from our log
volume, and our backup volume again separate from both of
these. The log volume and the backup volume also are on
separate scsi channel.
While watching the performance monitor, the physical drive
stats are flat line with no queued requests on any of the
volumes so it does not appear to be disk io issue. All
raid is HW raid on a controller with 128 meg of cache,
which according to its stats, the controller is barely
breathing hard.
When watching the sysprocess, the last wait type is always
ASYNC_DISKPOOL_LOCK, which I cannot find much info on
other than it occurs during backups, and look at disk io
issues, which again, don't appear to be the issue.
I'm at a total loss. I have 8 other SQL servers with
similar configs that do not show this behavior. All boxes
are configed the same, meaning HW, software an settings.
Any other ideas?!?!
Configuration:
HP Proliant
Quad 700 Xeons
2.5 gig ram
Data drive 4 * 72 gig 10k scsi drives raid 5
Log drive 2 * 72 gig 10k scsi drives raid 1
Backup drive 3 * 72 gig 10k scsi drives raid 5
Win2000 SP2
SQL2K SP3a
-RossRoss
Is it sill under 80%-90% ? If it is, don't worry.
"Ross" <RMNornesNOSPAM@.NOSPAMjtmd.com> wrote in message
news:1e2d101c45578$fa214f60$a601280a@.phx.gbl...
> High CPU during transaction log backups!
> I'm having issues with a SQL server where during
> transaction log backups, the CPU usage us on average 20%-
> 25% higher than during normal. We have timed this out
> using performance monitor and know for sure it's the
> transaction logs at this point. If we turn off transaction
> logs, the CPUs continue normal without the 20%-25% spike.
> We have our data volume physically separate from our log
> volume, and our backup volume again separate from both of
> these. The log volume and the backup volume also are on
> separate scsi channel.
> While watching the performance monitor, the physical drive
> stats are flat line with no queued requests on any of the
> volumes so it does not appear to be disk io issue. All
> raid is HW raid on a controller with 128 meg of cache,
> which according to its stats, the controller is barely
> breathing hard.
> When watching the sysprocess, the last wait type is always
> ASYNC_DISKPOOL_LOCK, which I cannot find much info on
> other than it occurs during backups, and look at disk io
> issues, which again, don't appear to be the issue.
> I'm at a total loss. I have 8 other SQL servers with
> similar configs that do not show this behavior. All boxes
> are configed the same, meaning HW, software an settings.
> Any other ideas?!?!
> Configuration:
> HP Proliant
> Quad 700 Xeons
> 2.5 gig ram
> Data drive 4 * 72 gig 10k scsi drives raid 5
> Log drive 2 * 72 gig 10k scsi drives raid 1
> Backup drive 3 * 72 gig 10k scsi drives raid 5
> Win2000 SP2
> SQL2K SP3a
> -Ross

High CPU during transaction log backups!

High CPU during transaction log backups!
I'm having issues with a SQL server where during
transaction log backups, the CPU usage us on average 20%-
25% higher than during normal. We have timed this out
using performance monitor and know for sure it's the
transaction logs at this point. If we turn off transaction
logs, the CPUs continue normal without the 20%-25% spike.
We have our data volume physically separate from our log
volume, and our backup volume again separate from both of
these. The log volume and the backup volume also are on
separate scsi channel.
While watching the performance monitor, the physical drive
stats are flat line with no queued requests on any of the
volumes so it does not appear to be disk io issue. All
raid is HW raid on a controller with 128 meg of cache,
which according to its stats, the controller is barely
breathing hard.
When watching the sysprocess, the last wait type is always
ASYNC_DISKPOOL_LOCK, which I cannot find much info on
other than it occurs during backups, and look at disk io
issues, which again, don't appear to be the issue.
I'm at a total loss. I have 8 other SQL servers with
similar configs that do not show this behavior. All boxes
are configed the same, meaning HW, software an settings.
Any other ideas?!?!
Configuration:
HP Proliant
Quad 700 Xeons
2.5 gig ram
Data drive 4 * 72 gig 10k scsi drives raid 5
Log drive 2 * 72 gig 10k scsi drives raid 1
Backup drive 3 * 72 gig 10k scsi drives raid 5
Win2000 SP2
SQL2K SP3a
-RossRoss
Is it sill under 80%-90% ? If it is, don't worry.
"Ross" <RMNornesNOSPAM@.NOSPAMjtmd.com> wrote in message
news:1e2d101c45578$fa214f60$a601280a@.phx
.gbl...
> High CPU during transaction log backups!
> I'm having issues with a SQL server where during
> transaction log backups, the CPU usage us on average 20%-
> 25% higher than during normal. We have timed this out
> using performance monitor and know for sure it's the
> transaction logs at this point. If we turn off transaction
> logs, the CPUs continue normal without the 20%-25% spike.
> We have our data volume physically separate from our log
> volume, and our backup volume again separate from both of
> these. The log volume and the backup volume also are on
> separate scsi channel.
> While watching the performance monitor, the physical drive
> stats are flat line with no queued requests on any of the
> volumes so it does not appear to be disk io issue. All
> raid is HW raid on a controller with 128 meg of cache,
> which according to its stats, the controller is barely
> breathing hard.
> When watching the sysprocess, the last wait type is always
> ASYNC_DISKPOOL_LOCK, which I cannot find much info on
> other than it occurs during backups, and look at disk io
> issues, which again, don't appear to be the issue.
> I'm at a total loss. I have 8 other SQL servers with
> similar configs that do not show this behavior. All boxes
> are configed the same, meaning HW, software an settings.
> Any other ideas?!?!
> Configuration:
> HP Proliant
> Quad 700 Xeons
> 2.5 gig ram
> Data drive 4 * 72 gig 10k scsi drives raid 5
> Log drive 2 * 72 gig 10k scsi drives raid 1
> Backup drive 3 * 72 gig 10k scsi drives raid 5
> Win2000 SP2
> SQL2K SP3a
> -Ross

High CPU during transaction log backups!

High CPU during transaction log backups!
I'm having issues with a SQL server where during
transaction log backups, the CPU usage us on average 20%-
25% higher than during normal. We have timed this out
using performance monitor and know for sure it's the
transaction logs at this point. If we turn off transaction
logs, the CPUs continue normal without the 20%-25% spike.
We have our data volume physically separate from our log
volume, and our backup volume again separate from both of
these. The log volume and the backup volume also are on
separate scsi channel.
While watching the performance monitor, the physical drive
stats are flat line with no queued requests on any of the
volumes so it does not appear to be disk io issue. All
raid is HW raid on a controller with 128 meg of cache,
which according to its stats, the controller is barely
breathing hard.
When watching the sysprocess, the last wait type is always
ASYNC_DISKPOOL_LOCK, which I cannot find much info on
other than it occurs during backups, and look at disk io
issues, which again, don't appear to be the issue.
I'm at a total loss. I have 8 other SQL servers with
similar configs that do not show this behavior. All boxes
are configed the same, meaning HW, software an settings.
Any other ideas?!?!
Configuration:
HP Proliant
Quad 700 Xeons
2.5 gig ram
Data drive 4 * 72 gig 10k scsi drives raid 5
Log drive 2 * 72 gig 10k scsi drives raid 1
Backup drive 3 * 72 gig 10k scsi drives raid 5
Win2000 SP2
SQL2K SP3a
-Ross
Ross
Is it sill under 80%-90% ? If it is, don't worry.
"Ross" <RMNornesNOSPAM@.NOSPAMjtmd.com> wrote in message
news:1e2d101c45578$fa214f60$a601280a@.phx.gbl...
> High CPU during transaction log backups!
> I'm having issues with a SQL server where during
> transaction log backups, the CPU usage us on average 20%-
> 25% higher than during normal. We have timed this out
> using performance monitor and know for sure it's the
> transaction logs at this point. If we turn off transaction
> logs, the CPUs continue normal without the 20%-25% spike.
> We have our data volume physically separate from our log
> volume, and our backup volume again separate from both of
> these. The log volume and the backup volume also are on
> separate scsi channel.
> While watching the performance monitor, the physical drive
> stats are flat line with no queued requests on any of the
> volumes so it does not appear to be disk io issue. All
> raid is HW raid on a controller with 128 meg of cache,
> which according to its stats, the controller is barely
> breathing hard.
> When watching the sysprocess, the last wait type is always
> ASYNC_DISKPOOL_LOCK, which I cannot find much info on
> other than it occurs during backups, and look at disk io
> issues, which again, don't appear to be the issue.
> I'm at a total loss. I have 8 other SQL servers with
> similar configs that do not show this behavior. All boxes
> are configed the same, meaning HW, software an settings.
> Any other ideas?!?!
> Configuration:
> HP Proliant
> Quad 700 Xeons
> 2.5 gig ram
> Data drive 4 * 72 gig 10k scsi drives raid 5
> Log drive 2 * 72 gig 10k scsi drives raid 1
> Backup drive 3 * 72 gig 10k scsi drives raid 5
> Win2000 SP2
> SQL2K SP3a
> -Ross

High Availability with Log Shipping

I have implemented log shipping between 2 databases, the transaction log size normally between 10mb - 50mb every 15 minutes during normal working hours, but it grows to 9GB when we run database optimization job and that makes it hard and long to transfer and apply the transaction log on the other database. Does anybody encountered a situation like this and is there a way to minimize the size of the Trans log after the optimization job?

ThanxUse the shrink command: You can look it up. It allow for a backup of the log then it's shrunk to the specified size. Please look up on Book on line or ms sql server support. I suggest shrinking before the other processes.

BACKUP LOG wslogdb62 WITH TRUNCATE_ONLY
DBCC SHRINKFILE('wslogdb62_log.ldf', 1)