November 5, 2009 at 4:20 pm
I have a sql 2000 server (version may not be relevant...)
I am restoring a database to it. On restoring the database, there is at least 1 table (could be more - which is worrying as this server goes live to the world next week if tested) that is giving very strange behaviour.
If I run a simple Update statement on the table, it locks everything up. Everything, even unrelated objects, lock up behind it. If I see the command on DBCC INPUTBUFFER it doesn't list the t-sql it simply has the word "PASSWORD" (I am updating a txtPassword field, but I have not seen this result from dbcc inputbuffer before....).
It eventually times out, or I have to kill it.
Now if I DROP and RECREATE the table, then repopulate the data via a dts import, the UPDATE statement runs in seconds and the table is back to normal.
What gives?
November 8, 2009 at 1:57 pm
Major issue for me, so hoping someone may have seen this behaviour before?!?
November 8, 2009 at 2:05 pm
please post in SQL2000 forums for SQL2000 related questions.
@@version info does matter !! Please provide it.
- Did you perform any maintenance after the restore ?
- did you update statistics ?
- did you update usage info ?
- is there any cpu / memory / io pressure on that sql instance ?
- which isolation level is your application using ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 9, 2009 at 8:59 am
What does sp_lock return for the update SPID? Every question ALZDBA has asked is relevant.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 9, 2009 at 9:12 am
How about encryption??
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 9, 2009 at 9:15 am
ALZDBA (11/8/2009)
please post in SQL2000 forums for SQL2000 related questions.@@version info does matter !! Please provide it.
8.00.2039 on new server.
8.00.2273 on "old server"! AHA!
Downloading that hotfix now to keep them in line (the old server was maintained by an outsource firm.)
- Did you perform any maintenance after the restore ?
- did you update statistics ?
- did you update usage info ?
No but have since tried updating stats, then reindex, then checkdb but to no avail.
haven't run update useage yet but will do so to test.
- is there any cpu / memory / io pressure on that sql instance ?
Despite being the same spec, this server is reporting IO delay occurences in the early hours of the morning when our backups run. I have learnt that the new server out hardware guys have not given us a 3 array setup like on the old server. Old server we had System, Data and Logs on 3 phjysical drives. This one is running with 2 physical drives to System, and then Data and Logs together.
- which isolation level is your application using ?
Just using the default so Read Committed.
November 18, 2009 at 8:23 am
Still working on this. Versions are now the same. Haven't changed the disk setup yet but ultimately this shouldn't be happening, and if it was a disk issue it wouldn't resolve from dropping and creating the object.
I notice that the log file on this db is spiralling out of control despite backups in place. After backup, DBCC SHRINKFILE just says "Completed" but doesn't do anything. The database is constantly autogrowing when running the command and I think this is causing the massive delays and locking.
November 18, 2009 at 12:26 pm
replication maybe?
Really starting to sound like you need either Microsoft support or a consultant on board to get this resolved.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 19, 2009 at 12:31 am
"...constantly autogrowing..."
- Run dbcc opentran in the troublesome database and see what it comes up with
- I've seen this kind of behaviour a couple of times in some of our logging databases because DTS/SSIS packages which copy the data from the server at the production line to a central "history" server. After we had to restore the db, we noticed the backup must have been produced during the run of such a package, because when we ran the package afterward, after 60000 rows it rolled back the whole operation because of duplicate keys...
These packages run every x minutes, so the log (full logging) filled up after a while ...
Even tough the application responsables received a mail of their packages failing, they did not respond !
Only after the dba-team received the autogrow alerts, things got sorted out .....
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 19, 2009 at 3:03 am
You sirs have helped me a great deal!!!
There are some old non-distributed replication transactions in the database (it is a replicated db where I bring it from, but it is not replicating whilst in this dev environment)
So Now I just need to clear/move on from these old transactions to get the log to truncate!!!
Not quite sure how but working on that.
Cheers :hehe:
November 19, 2009 at 3:20 am
- Maybe this thread will help our: http://www.sqlservercentral.com/Forums/Topic645979-7-1.aspx
- How to manually remove a replication in SQL Server 2000 or in SQL Server 2005
http://support.microsoft.com/kb/324401
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 19, 2009 at 3:29 am
Well the log kept wanting to autogrow and filling up so thanks for the tips on that.
Using dbcc opentran to confirm there are non distributed replication transactions
--Set the db to published temporarily
sp_dboption 'databasename', published, true
GO
--Mark the db replication as done
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
--Set db back to not published
sp_dboption 'databasename', published, false
I then issued a log backup and a shrinkfile - got it down to 1gb from 70gb!!!
However - I just ran the update against our users table, and it is still taking 4 minutes to issue 900 updates. So I still have the original issue, and as before - dropping and recreating and repopulating this table will fix it. so very odd.
November 19, 2009 at 3:36 am
- probably performing a little maintenance (rebuild indexes, sp_updatestats) will help out !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 19, 2009 at 3:43 am
Hmmmm now when I run the update statement I'm getting an autogrow on the data file. Why would this keep requiring to increase based on 900 updates?
November 19, 2009 at 4:05 am
*Slaps forehead*
Data file was growing because I'd switched the Recovery Model to Simple in trying to resolve earlier issue. Therefore all activity was being logged in the database.
Now I switched this off, the update is still taking longer than it should. But is much improved. it should be instant though therefore I'm still investigating.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply