October 2003 Blog Posts
Have you ever needed to quickly disable all jobs on a SQL Server? If you have a lot of jobs to disable, then this stored procedure will help you out. We move our systems to our disaster recovery site twice per year for disaster recovery testing. As part of this process, we need to disable all jobs on our SQL Servers. It doesn't take a whole lot of time to do this inside Enterprise Manager, but when your goal is to complete your work quickly so that the customer impact is minimal, you want to save all of the seconds...
So how do you DELETE thousands/millions of rows from a table without blocking anyone for long periods of time?
Well, here is an example:
DECLARE @Count INT DECLARE @Error INT
SELECT @Count = COUNT(*)FROM SomeTable MLEWHERE SomeDateColumn < (GETDATE() - 182) -- anything older than 6 months
SET ROWCOUNT 10000
WHILE @Count > 0BEGIN
BEGIN TRAN
DELETE FROM SomeTableWHERE SomeDateColumn < (GETDATE() - 182)
SELECT @Error = @@ERROR
IF @@ERROR = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN
CHECKPOINT
SELECT @Count = COUNT(*)FROM SomeTableWHERE SomeDateColumn < (GETDATE() - 182)
END
SET ROWCOUNT 0SET NOCOUNT OFF
Well today has definitely been interesting. I have been working with a co-worker who is responsible for trouble shooting customer problems. This specific customer is running our product with MSDE. We do not have dial-up access to the server, so everything is being done over the phone, plus the customer speaks Spanish, which I do not. It's hard enough explaining what to type at a cmd window to correct this problem and not knowing what they are typing cuz you can't see the screen. All that we are trying to do is recreate a user and grant sysadmin rights. I...