0
votes
0answers
64 views

Transaction log backups during index maintenance

We have a database with large and complex files and fragmented indexes. The environment is SQL 2008 Full recovery model and Mirrored which limits me to reorganizing. The backup strategy is Full ...
0
votes
2answers
2k views

Linked Servers pointing to same server (localhost) causing “Transaction context in use by another session” error

We are using SQL Server 2008. Normally our app that is using different databases distributed over the network and some of the stored procedures we call therefore make use of linked servers. Now I ...
4
votes
3answers
3k views

sleeping SPID blocking other transactions

I'm really having trouble tracking down some blocking we are experiencing. The root blocking SPID's status is 'sleeping', the cmd is 'AWAITING COMMAND', and the sqltext is 'SET TRANSACTION ISOLATION ...
0
votes
1answer
415 views

Query notifications and connection options

I'm investigating a problem with query notifications. The platform is SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Standard Edition. .NET errors show a problem reported from SqlNotificationInfo ...
3
votes
1answer
203 views

SQL Server 2008 - Adding a column and a filtered index within a single transaction?

This is more of a general question. Why can you not add a filtered index within the same transaction scope as adding the new column in the first place? The solution is of course simple, just make two ...
2
votes
1answer
143 views

Transaction level difference between using a large IN filter VS. “BEGIN TRAN/COMMIT”

I would like to delete about 100,000 records with minimal server overhead. I've had a few nagging questions I haven't been able to test properly so I figured I'd ask some experts here. Which would ...
2
votes
2answers
5k views

Table Lock on INSERT - How to optimize

we have a few long running procedures in the database, that cause an instant Table Lock on our main text storage table, which causes all other requests of the web app to wait (because the table is ...
2
votes
1answer
654 views

Rollback generate scripts

During the development of applications users often want to add new modules, business rules etc. So I've often used SQL Server 2008 option to create scripts from the development DB to generate new ...
3
votes
3answers
600 views

Why am i getting deadlocks with 1 read and 1 write thread in SQL

if anyone could please help me understand why the following transactions deadlock? I am providing the 2 transactions and the indexes for each table: Transaction 1: SELECT blockId, blockData, ...
4
votes
2answers
356 views

How do I query only for committed data while avoid a deadlock

I have a system writing batches of queries as transactions (to maintain coherency), these can be sets of 30-100 queries per transaction. These are done on multiple tables At the same time there are ...
5
votes
1answer
189 views

Does the transaction isolation level affect indices as well?

Here's one for the SQL Server gurus: if I set my SQL Server 2008 transaction isolation level to READ UNCOMMITTED, does that also affect the index pages? E.g. using ISOLATION LEVEL READ UNCOMMITTED, ...
2
votes
1answer
1k views

Save Transaction: Is the name local to the stored procedure or not?

Some foot shooting just occured on a production server. I fixed the problem, but I'm now rather confused. There is a subset of stored procedures that preserve state on failure. That is, if the ...
3
votes
1answer
253 views

Does blocking always mean open transaction?

This may seem a bit dull question but does blocking always mean that there is open transaction and that may cause transaction log grow to infinity because open transaction prevents log truncation ...
12
votes
5answers
2k views

Is ROLLBACK a fast operation?

Is it true that RDBMS systems are optimized for COMMIT operations? How much slower/faster are ROLLBACK operations and why?
2
votes
1answer
408 views

Locking, deadlocking workaround while process data at the same table

My database has a table: tableX. Task1 makes intensive INSERTs (1000 records per minute) as records to process and very seldom UPDATEs (1-2 records per minute) as records to recalculate. At the ...
7
votes
1answer
3k views

How to use transactions with SQL Server DDL?

I have a login table into which all inserts are done by a single stored procedure. CREATE TABLE dbo.LogTable( LogRefnr int IDENTITY(1, 1) NOT NULL, LogQuery varchar(255) NOT NULL, LogTime ...
5
votes
3answers
2k views

Will the transaction log shrink automagically in SQL Server?

When SQL Server database in a SIMPLE mode, you don't have to care about the transaction log bakcups. But in a SIMPLE mode, the transaction log seems to grow as it does in FULL mode. Does is truncate ...
6
votes
6answers
3k views

On DB2, who updated a record and what did the record look like before the update?

(SQL Server 2008 R2 Standard, database under full recovery) I have a table with fields id, firstname, lastname. A statement is executed: insert into dbo.sometable (id, firstname, lastname) values ...
5
votes
3answers
703 views

Deadlock Error from SQL Server 2000 to SQL Server 2008

After migrating data from SQL Server 2000 to SQL Server 2008,when I am doing any a transaction in the table which find out more row on the database,that's gives me error. Transaction (Process ID ...