Ramblings of a DBA

Tara Kizer
posts - 166, comments - 837, trackbacks - 75

My Links



Search this Blog


Post Categories


October 2003 Blog Posts

Disable all jobs on a SQL Server

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...

posted @ Friday, October 17, 2003 1:41 PM | Feedback (3) | Filed Under [ SQL Server - Database Administration ]

Controlled DELETE

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

posted @ Wednesday, October 15, 2003 1:09 PM | Feedback (15) | Filed Under [ SQL Server - General ]

Something that I am dealing with today

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...

posted @ Tuesday, October 07, 2003 2:56 PM | Feedback (3) | Filed Under [ Gripes ]

Powered by:
Powered By Subtext Powered By ASP.NET