I just now had the strangest error, for some reason a table in my DB was locked by two processes, there is no use with BEGIN TRANSACTION in the queries that execute on the DB and while it suddnely happaned no heavy process was running... I was just browsing the table with the enterprise manager.
After viewing the processes with sp_lock I found the two processes that caused the locking and killed them, this fixed the problem, but I would like to know what caused it...
When viewing the process info with sp_who the status was "sleeping" and in sp_lock in one process the type was "key" and the status was "wait" (mode "s").
I need to restore a DB but it was prevented by a background process of "Ghost Cleanup". server is SQL2000 ENT. sp4.
It could not be killed, neither it was stoped after restart the server. Is there a way to change its running schedule and/or to kill it when I need to restore the db?
I have a test environment that we rebuild servers on a regular basis.To streamline the process we use ghost. We will be installing SQL onthe servers and want to build a ghost image with that build. We havetested it by doing the build, loading the data, and then stopping allthe services and setting them to manual. After we Ghost the machine,we start up the services and reset them to Automatic. Seems to work.My question is:Are there any risks? Should I expect any adverse affects?Thanks for all your help!
Hello all, Where do I start? My son and I wrote a small ASP.net 2.0 website on XP IIS 5. Works ok with the standard login controls. I FTP'ed the files up to a Windows server2003. I installed the SQL publishing wizard. I created a .sql file of the data base. I ran the file in SQL2005 manager to build the data base on SQL express running on the Windows 2003 server. It installed, no errors. great! The website would not run with Data base connection errors , like files or data base was read only, duplicate database , can not run mydatabase.create. something like that. I created a new app pool and made sure network service was the account. Gave network server permissions to the folders, it started working great. Now sit down. I created a few accounts using the website ok, no errors, Great! Then check the database to see if the passwords were encrypted. The new accounts were not there. But the accounts went somewhere???? Must be two databases???? One hidden? I detached the database I created with the .sql script file.... to see if I would get an error on the browser. nope still worked. Hmmmm other hidden database still working.... I stopped database server in the management tool, It still worked....Ok, I'll get the house check for poltergiests. I went to SERVICES. The service was stopped(SQLEXPRESS).. I changed auto start to manual. Browsed back to the site again, finally an error. I restarted the service, website worked again, but the database, the only user database, is still detached. Created more user accounts with no error.... Seached the hard drives for .MDF files. Found the file I detached and also the ASPNETDB.MDF that I orginally FTP'ed to the server earlier in the APP_DATA folder of the website. I renamed the MDF file to see if then I would get an error, yep I did. It then automatcly re-created the MDF file exactly the same size. Two files in the directory now and now a new error in the browser about a database mismatch. Ok that makes sense. But it will not allow me to rename the new file so I can rename the original. Question is how does SQLEXPRESS run the ASPNETDB.MDF file in the web folder which is not attached to the database engine? Why use a SQL pub wizard if ASP.NET creates the database automaticly? Or just "Runs" it? AM I HIGH? Thanks in advance, before I take drugs and sit in a dark closet. -Wade
I have problem with 'Ghost cleanup' system process. It is locking up my tables and user transactions are keep getting wait status. So is there any way to disable or change the schedule of ghost cleaner? Thanks in advance..
Hello,I have a table 'customers', with 2 records:SELECT * FROM customers;customerID | customerName------------------+-------------------------myFriend | myFriend's Nametest | testing user(2 rows)but when I'm asking about customerID column, I get the answer:SELECT customerID FROM customers;ERROR: column "customerid" does not existWhat happens? I'm using PostgreSQL 8.1.3
I have problem with 'Ghost cleanup' system process. It is locking up my tables and user transactions are keep getting wait status. So is there any way to disable or change the schedule of ghost cleaner? Thanks in advance..
I have a VB program which uses ADODB.Connection to make connection to SQL Server DB. However, under some circumstances, orphan / ghost connections remain in DB even after the VB program closed.
Here are the facts...
(1) Windows Server 2000 + SQL Server 2000 - run the VB program and made connection to the DB, leave it for 3 minutes, the connection closes when the program closes. - run the VB program and made connection to the DB, leave it for 3 hours, the connection closes when the program closes.
(2) Windows Server 2003 + SQL Server 2000 - run the VB program and made connection to the DB, leave it for 3 minutes, the connection closes when the program closes. - run the VB program and made connection to the DB, leave it for 3 hours, the connection does not close even after the program is closed.
Does anyone know if there is any problem with Windows Server 2003 + SQL Server 2000 using ADODB.Connection? Or is there some kind of "timeout" parameter for which I can set to resolve such issue?
I noticed that several connections were blocked by something called Ghost Cleanup (or something like that). I know what the cleanup does, but it often causes blocking for quite a while . . .
Hi everyone, I have a question about SQL Server 2005. I have written an ASP.Net 2.0 Web Application and it is using SQL Server 2005 as Database. In the last few days I noticed that the app is down sometimes. To analyze the problem I looked at the activity monitor in SQL Management Studio. I can see there approximately 170 processinfos. I want to describe the column values of the process infos: Process-ID: Unique ID and a red down-showing-arrow-icon User: My UserDatabase: My DatabaseStatus: sleepingCommand: AWAITING COMMANDApplication: .Net SqlClient Data Provider When I click Locks by Object, I can see the IDs of the Processinfos. Again I will show some colums:Type: DATABASERequirementtype: LOCKRequirementstate: GRANTOwnertype: SHARED_TRANSACTION_WORKSPACEDatabase: My Database So my question is, does this mean, that i have locked the db? How are they handled? For example I have a windows service, which is doing checks in db every 10 seconds. I can see, that each check generates a new processinfo? Is this usual, or am I doing something wrong? Thnaks for help,Byeee
When I run a select statement : select 'X' from table1 where c1 = condition locking on indexes behaves as expected
However if I run select 1 from table1 where c1 = condition locking on indexes goes wild locking pages and rows on indexes that are not even referenced in the query. Any ideas Why?
Hello All, I'm just migrating from oracle to SQL.Can anybody tell me that how effectively I can use Row level locking in SQL? If tow users are attemping to Moify same record how i can deal it in Back end(SQL)? Thanks in Advance. Suchee
i have an application in production(sql 6.5 ) which causes locking which times out my other processes , iwant to capture time the locking takes place i have found in bol that i can get time deadlock occurs using trace flag 3605 in sql7.0 ,if i have to use trace flag is it ok with dbcc traceon or -T option in startup is recommended. any advice would be appreciated tia ram
I have used DTS in the past to copy information in certain tables in production over the top of those same tables in test. However, the process is now failing. Does DTS require an exclusive lock on the source table, as well as the destination table during the export process? Will shared locks on the table I need to copy prevent DTS from completing the process?
We are running out of locks while updating a particular table (table name = history, rows = 25,000,000) in SQL Server 6.5.
LE threshold maximum is set to 200. LE threshold minimum is set to 20. LE threshold percentage is set to 0.
Locks is set to 0.
I have also included the stored procedure, which we use to update the history table.
As you can see, from the first four lines, we ran this SP 4 times processing around 6 million rows at a time. It runs out of locks once it is around 5.5 to 6.5 million rows. Is there a way of locking the table so that this SP can be run just once which will effectively process all the 26 million rows in one go?
Any help will be greatly appreciated.
Winston
--declare minihist cursor for (select uin,uan,mailingdate from history(tablock)where rowno between 5635993 and 12000000) --declare minihist cursor for (select uin,uan,mailingdate from history(tablock)where rowno between 12000001 and 19000000) declare minihist cursor for (select uin,uan,mailingdate from history(tablock)where rowno > 19000000)
open minihist fetch next from minihist into @huin,@huan,@hmailingdate while (@@fetch_status <> -1) begin
if (@@fetch_status <> -2) begin
select @mailtot = 1 select @mail12m = 0
/*** Get the gender ***/ select @sex = gender from name where uin = @huin
/*** Calculate if mailed in the last twelwe months ***/ if (@hmailingdate <> null) and (@hmailingdate > '19980524') select @mail12m = @mail12m +1
/*** Get info for this uan from address_summary ***/ select @mailtot = (@mailtot+mailed_total), @mail12m = (@mail12m+mailed_12months), @lastday = last_date from address_summary where uan = @huan
/*** Insert a row into address_summary if doesn't exist ***/ IF @@rowcount = 0
Hi, We are running SQL 6.5 in Produciton and I'm getting one blocking problem but mostly I kill the process and whenever I check the SQL Error Log I see this message : Error : 17824, Severity: 10, State: 0 Unable to write to ListenOn connection '1433', loginname 'XXXY', hostname 'DT SA'. OS Error : 64, The specified network name is no longer available.
I'm trying to use the pessimistic row locking of SQL to get following result.
When a customer form is openend, the row should be locked for writing. This lock should be left open until the user closes the customer form.
I cannot use transactions because there can be more then 1 customer form open in the same app. In ADO a connection is IN transaction or is NOT, nested transactions are not supported.
How can I keep this row locked on SQL and this until I unlock it or the connection is broken ( in case of problems on client machine )? And how can I see on another machine of this row ( customer ) is already locked so I can open him in read-only?
For the moment I'm using extra fields that hold the info wether the customer is locked en by whom. But that's on application level, not on DB-level.
Ok, this may be a brain dead question but I can't seem to figure out what it is I am doing wrong. I have a stored proc which has multiple inserts and updates and deletes. However, I do not want to commit until the end of the procedure. So near the end if no error has been return by a particular insert, update, delete I tell it to COMMIT TRAN. My problem is that it seems to run and run and run and run. I take out the Begin Tran and boom it runs fast and completes.
But if there is a problem near the end then those other statements will be committed. I wish to avoid that. I have an error routine at the end of the SP and I have if statement to GOTO sp_error: if @@error produces a non zero value. I am sure I am doing something goofy but can seem to see it. I know it has come down to the Begin Tran. Is it that I have too many uncommitted transactions? Or perhaps I am locking something up. I know its hard to tell without seeing what I am doing but is there something simple to remember about using explicit transactions that I am forgetting. Any help is appreciated.
Hello . I am using SQL Server 2000 in order to create a multi user program that accesses data. The problem is that multiple users will update and select data at the same time at the same table.
Is there a way to avoid deadlocks ? I heard about two ways: using a temporary table to store data and then write the data only when the user finished the update. and the other is using xml to write the database to a xml file that is stored locally. do the updates on the file and then after completion insert the xml file into the database.
does anybody know much about these ways? do you know where i can find code for this ?
Hi all, firstly I would like to apologise because I don't actually use sql or know diddly squat about it. I am a network administrator and have a problem with a user's domain account getting locked out everytime he starts his sqlagent service (we are running a windows 2003 domain). I know this a vary vague post and I am sorry for that. I am just after some general ideas/information on why this keeps happening. Any help greatly appreciated.
deepak writes "how to lock the record while using a query "select id,name from students" i want to know various locks in sqlserver and and each of its use in insert ,update,delete and select etc. i am using it from visual basic 6.0
use DB1; select * from [Jobs] select resource_type, request_mode, request_status, request_session_id from sys.dm_tran_locks
It produces the following results when run:
|resource_type | request_mode | request_status | request_session_id |Database | S | Grant | 51 |Database | S | Grant | 54
What is "S"? what are the other possibilities and their meaning for this field. And.. 51 and 54...what are they exactly? Are they individual people or user ids? For example, could 51 be "Advanced users" and 54 be "Generic Users" under SQL security?
My next question is... I suspect i have too many Indexes on my table "Jobs". I suspect it is causing page locks. Especially when someone is updating the records. I will run this query when users complain to me about not being able to edit records.
Ok..Question is...if i have a PageLocking entrant.. Through SQL manager..is it possible to boot a user off temporarily..? How do you do it?
Hi All, Please help me out how to implement the locking in below scenarioReq - There are two tables Table1 & Table2 If I will insert in table1 then related data fields will be auto updated in table2 , similarly based on the data in table2 table1 data needs to be updated. Now the sync of table1 & table2 is working fine.My prob is we are handling the updation/insertion from the UI screens . Two separate screen for each table. When we have multiple user accessing the screens say - User1 updates table1 and User2 updates table2 then we need to implement the locking so that at one time one screen will allow updation in the table1 and hence table2.The other screen shouldnt allow updation in table2 and hence in table1.This is very common locking functionality ...but am not getting any way to implement it , Please advise.Srain.
I need to secure an sqlserver database such that it can only be accessed from an application and to prevent anyone with full admin rights on their local machine and an sqlserver licence from getting in to the database.
I am struggling with controlling access to the database from the sa account. If I attach to the database from a second instance of sqlserver which is different than that where the database was created then I am able to gain full access no problems, which is of course The Problem.
From what I can work out.
1. sa is dbo (and this cannot be changed) 2. dbo has the role of db_owner (and this cannot be changed) 3. the permissions for the db_owner role cannot be changed. 4. the password for sa is set at the level of sqlserver and not per database
.....so any sa can access any database.
I don't believe this so have to be missing something significant, any light on the subject would be gratefully received.
Hi!This is a very simple question and I'm sure you guys will help me a lot.I'm using Visual Basic 2005 for programming. I have one table on my MS SQL 2005 database that has an int column with a counter that needs to be incremented when a user registers.So when reading the value I use a simple SQL query like this: SELECT counter FROM companies WHERE company=0 then I store the value in a local int variable and then I increment it. Then I update the incremented value. UPDATE companies ... I need every single customer to have an individual value. My question is how can I prevent an error, data corruption or whatever if two or more users want to register at the same time? I've been reading about lock update but I'm not sure how to implement it on Visual Basic 2005 and I don't want to store scripts on SQL Server. I'll appreciate your comments and help on this situation.
I have a busy transactional table , I wanna use row level locking mechanism in msSQL. SELECT * FROM PARTY WITH (UPDLOCK ROWLOCK) where LastName ='Clinton' is there any downsides of this approach?
I'm using Sql Server 2005... I'm creating a transaction and enlisting the commands inside vb.net code as well as surrounding the t-sql in an "Begin Trans --- Commit Trans" block. I also have the Isolation level set to the highest (Serializable) in the vb.net code and the sprocs. I'm running 4 instances of the app on 1 server and 4 instances of the app on another server. I am handling the lockouts just fine and writing them to an error table within the db. The app keeps spinning and producing data just fine. There are 3 places where the locking may occur within the app. Two of them are just fine (which is a select and and insert). The app will eventually cycle around and pick up the records taht may have been locked out. My concern is the Update portion which updates stats based off the Insert done previously. If the records never get updated, the only way I would know if they were processed would be to check in our Error table to see if the record exists. I would like to know if there is any way possible to cut down on the number of lockouts (which may be perfectly normal) and to get a way to update that table I just talked about. Should I be using different isolation levels, etc. --- anything of importance might be useful.
What I'm trying to do os this: have an application set a lock on a specific row in a table, so other applications can see it's busy. So, I use "SELECT * FROM mytable WITH(ROWLOCK, HOLDLOCK) WHERE condition" to set the lock. That should lock a row until I close this recordset (me thinks anyway...) Then to detect I use "SELECT * FROM sametable WITH(READPAST) WHERE samecondition". If the row I'm looking for is locked, this select will skip that row, so I get an empty selection.
That's what I want to happen anyway, but in the real world this doesn't seem to work. It doesn't lock, or it doesn't skip....
I have a table X with 61390 rows on it. IT is a crucial table which is being read and updated constantly. If I create a clustered index on the Primary Key (identity column) and run UPDATE x SET c1 = 1234 where PKCOL = 4321 the best lock I can get is a table Lock. If I create a compound non-clustered index on C1 and PKCOL the best lock I can get is a Page Lock. PAge Lock is OK but as this table is in High demand I want a row lock so others can carry on referenceing other parts of the table. WHy does it take a page lock and not a Row lock?
