I had a DTS task running which was doing an insert (over 17.5 million rows) in a database for which the auto shrink option was enabled.
After a few hours I noticed that the insert was blocking the autoshrink.
Since I didn't want to kill my insert, I tried to kill the autoshrink from the query analyzer, but when trying to do so I got the message that only user processes can be killed.
Does anyone have similar experiences? Should you consider enabling the autoshrink option or is this just trouble?
Is there a way to send out an email woth deadlock information (victim query, winner query, process id's and resources on which the deadlock occurred) as soon as a deadlock occurs in a database or at instance level?I currently has trace flag 1222 turned on. And also created an alert that send me an email whenever a deadlock occurs. but it just says that a deadlock occurred and I log into sql server error log and review the information.
Ran the Andrew Zanevsky script to shrink the Tran log, this works great, but say the log gets shrinked to 30MB, I then expand it to 40MB, then sometime within a 24 hour period it magically gets reset to 30MB. The database is a standalone DB, and is on SQL7 SP1 the script I ran can be found @ http://www.pinnaclepublishing.com/sq/SQMag.nsf/WebIndexByIssue/F36C151BD80FAD8F852568D0007799BE?open. Can anyone please give some insight into this or maybe a flag that got reset somewhere? Thanks in advance Pat
I've got a database that needs to run 24/7. I'm looking into maintanence options and wanted to run the following by y'all:
Ok, I've read the MSDN "Maintaining databases" article and noticed the following statement about autoshrinking: "This technique uses almost no processor time and memory". I also searched these forums and found that many users say autoshrinking heavily lags down sql transfers. So who's right? And if it does lag transfers, by how much?
Another question I have about autoshrink is fragmentation. It would seem to me that over time solely depending on autoshrink would cripple a server in terms of fragmentation; is this the case?
Also, does autoshrink (or manual shrinking or compacting) update the statistics?
Final question!!! I'm programming in native c++, is there a way for me to run commands such as "DBCC SHRINKDATABASE" in native OLE DB code?
I have a database that has Truncate on Checkpoint set for the Log file. The Log file is set to AutoGrow. Is it necessary to to run dbcc shrinkdb (or the like) to get Log file to contract? Is there any harm in not contracting the Log file? I'm looking for best efficiency and least-likely-to-fail path as DB sits 'really remote' and there is little opportunity for observation.
Does anyone have any recommendations on re-indexing? I have one table that bears the most growth. It has a clustered index. What would be a suitable data point to watch? I run a SP to save DBCC SHOWCONTIG info along with the duration of a test query, but haven't seen a clear breakover point.
Does anyone have any benchmarks for the amount of overhead caused by autoshrink of the log and having autostats enabled? We have a customer that insists that turning off these options was necessary to eliminate a performance problem they were having (Query timeouts), but we are not convinced that these two options would have generated enough overhead to have been the root cause (they also rebuilt all their indexes and made some other unspecified changes that more likely solved the problem).
We are hestitant to have them continue with these options disabled because then we need to rely on them to keep the log file shrunk and the statistics updated and because of the data changes during the day, would prefer to have stats updated automatically rather than on a fixed schedule that may not be as appropriate.
Anyway, if anyone has any feedback on overhead generated and potential performance implications of having either of these options enabled, it would be greatly appreciated.
I have a test database and I detach and reattch this database during my testing on my test system. This database is originally from my production system. I have a couple questions that came up during my testing:
1. When you detach/attach a database from a different server with the same setup, does it gather it database setup information from the model database on the current server?
2. When you switch the autoshrink database option from enable to disable, do you have to restart the server?
3. How to you change the default make of the model database?
Hello All,I have been encountering trouble with a SQL Server 2000 Transactionlog file, mainly with the constant growth and lack of the autoshrinkoption. Here are the details:1.)OS is Windows 2000 server sp32.)SQL Server 2000 (Build 2195) sp33.)Database Recovery Mode is set to Full4.)Maintenance Plan for "Transaction Log Backup" is set to removefiles older then 1hr.5.)The "AutoShrink" option is on for the DB properties.Also as part of the Maintenance plan the database in question isbacked up nightly. The problem is that the transaction log file hasgrown so much that it was consuming all of the free space on the harddrive so I then restricted the file growth. When all of the drivespace was consumed or the "database log file is full" message wasencountered I would run the following Query scripts to shrink the filemanually:First – Backup Log <database name> With Truncate_OnlySecond – DBCC Shrinkfile (<database name>_log, 200)Which brings the transaction log file down to 200mbs. I need toautomate this so that I don't have to keep babysitting this databaseor manually shrinking the log file.
Hi everyone In my SqlServer Management Studio Express, on start up it shows the server type option, but greyed.So that value is fixed to database engine. ( I'm trying to work on an SqlServer Compact Edition database through the SSMStudiothat's why I'm trying to get this to change.)Besides, after I connect i go to the Object Explorer, expand the server node, and go to Replication.When i expand replication, i get the "Local Subscription" option, but nothng for Publication.( I want to work on Merge Replication, that's why I desparately need Publication to work)Am i missing something here? I did not install SqlServer separately, I only have what comes bundled with the Visual Studio 2005 Setup.
So I started a new job recently and have noticed a few strange configurations. Typically I would never mess with min memory per query option and index create memory option configuration because i just haven't seen any need to. My typical thought is that if it isn't broke... They have been modified on every single server in my environment.
From Books Online: • This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician. • The index create memory option is self-configuring and usually works without requiring adjustment. However, if you experience difficulties creating indexes, consider increasing the value of this option from its run value.
In a high traffic environment, deadlocks eventually occur as number of data processes increase. How can deadlocks be avoided, minimized and resolved. Please kindly provide scenario examples and samples of T-SQL code. Thanks much.
Iam trying to bcp a table(residing on my prod server to my local machine from command prompt) .Actually the table iam trying to bcp has heavy updates and selects, from users (70 users). The users complain that system becomes slow.Is it got anything to do with my trying to bcp the mentioned table(table has 170,000 records).Also whenever i try to bcp this table, only after being chosen as the deadlock victim by Sql server,for 3 or 4 times that iam able to bcp the table.
Any help regarding this will be very much appreciated TIA kinnu
I am unable to control the granularity of locks in our queries. We are running queries through MTS and are getting deadlocks.
The batch includes two inserts and one select query - all are hitting on only one table. This table has a unique clustered and a unique nonclustered index as well as a primary key.
Within the batch, I have given a table hint to set transaction isolation level to READCOMMITTED, ROWLOCK for the insert statements, like this
INSERT INTO ib_price with (READCOMMITTED,ROWLOCK)........
and the same for the Select statement.
SELECT retail_price, price_status_id FROM ib_price with (READCOMMITTED,ROWLOCK)
When I run sp_lock on the spid, I get output indicating that SS7 is placing a IX lock on the table. I'm pretty sure this is a big contributor to the deadlock.
I get the deadlock when I try to run more than one client with similar insert parameters.
How can I control the granularity to just rowlocks?
I am getting deadlock running a stored procedure from two machines. Looking at the error log (generated using trace flag 1204 and 3605), it seems the deadlock is on a key. But what I fail to understand is how come sql server granted exclusive lock on the key to both connections. The grant list shows that lock with Mode X is granted to both connection.
Can anyone help me resolve deadlock with following text
Parallel Query worker thread Involved in deadlock.
I am particularly interested in resolving details of above mentioned line,as I started getting dead lock more frequently now and when I look into query involved blocking and victim I see nothing that can cause deadlock they are update insert and select statement which were fine for long and all of sudden started giving problem.
Thanks in advance, for any knowledge share my mail id scraval@hotmail.com
Hi, When many users run some stored procedures I 've got some deadlocks. How to avoid that? We run large stored procedures code which are using sometime the same table. What is the best way for using the transaction isolation level, fillfactor indexes, procedure cache configuration ...etc to avoid that.
In addition, I am using MTS and sometimes the Tempdb is also locked, is it a Microsoft bug (again) ?
We had a dead lock every night 9:00pm. I found out Server/Current Activity --Object Locks : The error log showing error 17824, severity:10, state 0 DNCC TRACEON 208, SPID 28 DBCCTRACEOFF 208, SPID 28 In current activity --object locks and reapetedly showing "tempdb.dbo.sysobjects/sysindexes/syscolumns" 28:sa.master.dbo /INSERT /SQL_servername (MS SQLEW)
Sorry for bombading the forum with all these questions, but i am relatively new to sql 2000.
I am getting dead lock on the following procedure.
important background information 1. this is a multi user web-based call centre application 2. this procedure loads up a new contact based on priority
I see no reason how a dead lock could occur. does any one have any idea. could it be something else that is locking up resource used by this procedure?
CREATE PROCEDURE topcat.getNewContactInfo ( @contact_id int ) AS BEGIN begin transaction
declare @id int
set @id = (SELECT TOP 1 _id FROM class_contact WHERE (status IS NULL OR status='New Contact' OR status = 'No Connect' OR status='callback') AND (checked_in IS NULL OR checked_in <> 1) AND (callback_date >= (getdate() + 1) OR callback_date IS NULL ) ORDER BY priority DESC)
UPDATE class_contact SET checked_in = 1 WHERE _id = @id SELECT TOP 1 * FROM class_contact WHERE _id = @id
commit END GO
wat i dont' get is that, this procedure only has one update statement, this is the only statement that could possibly hold a lock on another resource (i think) , i can't see how a dead lock can happen in this case since this procedure doesn't hold up 2 resources at a time.
Using SQL Server 2000 SP3a, I run the following in 2 query analizerwindows on the Northwind database, the second one always gets thedeadlock Msg 1205:Window 1:declare @cnt intselect @cnt = 5while @cnt > 0beginbegin transactionselect * from orders (updlock) where employeeid = 1update orders set employeeid = 1 where employeeid = 1waitfor delay '00:00:03'commitselect @cnt = @cnt -1endWindow 2:declare @cnt intselect @cnt = 5while @cnt > 0beginbegin transactionselect * from orders (updlock) where employeeid = 1 and customerid ='ERNSH'waitfor delay '00:00:02'commitselect @cnt = @cnt -1endThe query in the first window gets 123 rows and places update locks onthem, then updates them and commits. The query in the second windowgets a subset (about 5) of the results that window 1 gets also tryingto place update locks on the same rows. Shouldn't the query in window 2just wait for the transaction in window 1 to finish? why would itdeadlock?you can also get rid of the delay in the second window and it willdeadlock faster.thanks in advance.Eugene
Hi.create table joe(c1 integer not null, c2 integer not null)Two sessions:Session 1:BEGIN TRANinsert into joe (c1,c2) values (1,2)Session 2:BEGIN TRANinsert into joe (c1,c2) values (3,4)Session 1:select * from joeSession 2:select * from joeOne of the sessions gets a deadlock victim message.thanks,Joe
I have a table that every 30 minutes needs to be repopulated fromanother table that is recreated from scratch just before.What I did was this:CREATE PROCEDURE BatchUpdProducts ASbegin transactiondelete productsinsert into productsselect * from productsTempcommit transactionGOThis takes about 30 seconds to run. I tried it doing it with a cursor,row by row, but it took like 30 minutes to run instead. The problemis with the fast approach is, once in a while I get a deadlock errorin different areas trying to access the products table. Using SQLServer 2000 by the way.Any ideas?
Here is the exception I am getting, any idea why this might be happening:
Exception: ThisMethod is done: let this go: Exception: Transaction (Process ID 74) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Stack Trace: at
Hi, I need to trace deadlock, one of article was mentioning “QL Server Profiler's Create Trace Wizard to run the "Identify The Cause of a Deadlock" for SQL Server 7.0, is there any way I can do this in Sql Server 2000?
Hi Guys. I just want to ask some insights on SQL Server Deadlock and what is the best way to handle deadlock in asp.net? Or something like a Try... Catch.. statement to handle the error? Please advice. Thanks in advance.
I have set up an alert to detect when Page Deadlocks rise above 0. Overnight I have DTS packages populating SQL Server and various other jobs (Cognos Cube Builds etc.). My alert detected a Deadlock during the night but all of my processes completed fine. My problem/misunderstanding is that my alert is still popping up every 5 mins saying there is a Deadlock yet there is nothing running or no-one accessing SQL server and I cannot see any trace of the Deadlock in the Current Activity. Is this normal or is it a bug?
