Insert Trigger on Linked Server during a FULL DB Backup

I have two servers, Server A and B. Server B is linked to Server A as a linked Server and there is one table TableA on ServerA and ServerB.
TableA receives contineous Inserts and there is an Insert trigger written on TableA on ServerA to insert the same data to ServerB using the INSERTED table. This equation works fine for most of the day, but whenever I run a Full Database backup on ServerB, the whole Trigger Insert process slows. I wonder why? Any idea, any suggestion?During backup server is kind of busy ;) and if there is only one processor and not fast I/O device ...

Insert timeout on test db

I created a new test database on my database server using a daily backup of the live database. I did an structure and data compare and it is identical. From looking at the permissions it looks identical too. the problem is when I run an update proc the database connection times out. Ive changed the connection string to use the sa login and is still timesout. Ive also tested it by changing the database name to the live db and it works fine then. I must be missing something. Ive also tried to run "Exec sp_change_users_login 'auto_fix', 'sa' to see if it would work but nothing. The select statements seem to work though. Thanks for nay help in advance!

RyanCan we see the code and the exact error message?|||Have you tried executing the update manually? Also, you might want to update the timeout on the sql command to something longer, and see if it works.|||Instead of using the stored procedure I tried to execute a single update statement using c# code and the same one using query analyzer. The query analyzer one worked fine everytime with 2 different update statements but the code timed out on one. Both were just updating one field in one record. Both tables have triggers going on to, but Im using the same admin password in the code as Im using to login with query analyzer. The only other difference is that some stored procedures are encrypted and some are not But Im not sure all the procs and tables the triggers handle. I figured that wouldnt matter since im using an admin password. Does anyone know what is going on? Thanks

Ryan|||

ryanoc:

Does anyone know what is going on?

Not without seeing your code, no.|||The first update will always timeout, but the second one wont. Also, I created a database locally and restored it using the same file as the one im having problems with and I now have no timeout issues. ahhh!

//SqlConnection connRDK = new SqlConnection("Server=my_server;Database=ad_xx_beta;Persist Security Info=False;user id=sa;Password=xxx");

String sql;

sql = "UPDATE VHSLSFIN SET AmtPriceVehicle = '116907' WHERE SlsId = 'V01001878'";
//sql = "UPDATE COEMP SET NameNick = 'Ryan C' WHERE empid = '163'";

try
{

SqlCommand commRDK = new SqlCommand(sql, connRDK);

connRDK.Open();
commRDK.ExecuteNonQuery();
}
catch (Exception ex)
{
string x = (ex.Message);
}
finally
{
//done
}|||I think your catch statement is "swallowing" any exceptions you are receiving. What are you doing with the string x? Are you displaying it anywhere?|||Not using it anywhere except in debug mode to view the exeption which is allway server timeout on one of the queries|||

You can check the log file of the database to see whether it is always full

|||Where do I find it? What does full mean?|||

ryanoc:

Where do I find it? What does full mean?

Open your database because the Taskpad is context sensitive click on view at the top of Management Studio and you will see the Taskpad, click on it and you will see all the file allocation of the open database. You can increase the file size by changing from your existing size to something bigger. Hope this helps.

|||There is a big difference in the databases in question.

Live database with no timeouts:
allocated: 12.37mb
used: 3mb
free: 9.3mb

Test database with some timouts:
allocated: .99mb
used: .49mb
free: .5mb

Does this major difference have to do with my timeout problem? If so, how do I increase the transaction log for the test database? thanks very much!

Ryan|||I increased the size and still get the timeout :(|||

Try increasing both files the MDF(Microsoft data file) and the LDF(log data file) by changing the size of the files. Hope this helps.

|||Its the same as the other files. One other thing I noticed is that my database was created using my username, but the other database was created using the admin username.

insert record from another database

hi. we have 1 production database and a 2nd backup database. one of our users accidentally deleted a case and we would like to get it back. our production database is updated nightly so none of the case's information would've changed.

how can i insert the data from the backup database to the production database? i need to copy one row. thanks for your help!just one row?

how about a simple insert statement?