I need something to automatically stop my index maintenance job on Saturday night. How do I do that? How can I check automatically if my index maintenance job is still running and if it's running, stop it every Saturday night before 12 am?   Right now I do it manually using:  USE msdb GO EXEC dbo.sp_stop_job N'The name of the job'   How can I automate stopping the job at a certain time ?
122401
I’m looking for a single file that does these things in order.   Declare a variable to hold a job name  Run a query to check whether that job declared is currently running Set up an if loop If the job is running, execute the stored procedure that kills it.   My question is to write a query that declare a variable to hold a job name and gives us result the job is still running or not if the job is running execute the stored proc to kill it.  My manager was asking me to listing running jobs and suspended jobs and declare a variable to hold job name and setup an IF loop like if job is running run proc to kill it.
79946
This has always been a mystery to me and with all the snow we're getting I'd like to work from home but the boss man wants us to turn our computers off a night to save energy.  Figured I'd like to try Wake-On LAN, but not sure where to start.
71779
I'm looking for best practice in dealing with scheduled SQL Server Agent jobs in SQL Server 2012 availability groups. Maybe I missed something, however at the current state I feel that SQL Server Agent is not really integrated with this great SQL2012 feature.  How can I make a scheduled SQL agent job aware of a node switch?  For example I have a job running on the primary node which loads data each hour. Now if the primary goes down, how can I activate the job on the secondary which now becomes primary?   If I schedule the job always on the secondary it fails because then the secondary is read-only.
20629
We have a 2012 server that has about 20 scheduled tasks that are used for monitoring.  We have built a 2016 server to replace it and as I move tasks to the new server I have run into an issue.  Most of our tasks run every few minutes.      In Server 2012 we would schedule the task to start at 1200AM of the current day (so a time in the past) and reoccur every X minutes.  The task would start at its next scheduled time.  Perfect.  In Server 2016 schedules that start in the past don't run.  So we had to schedule it to start in the future.  This is fine until you reboot.  Now the start time is in the past and it won't run.  It doesn't even register a failed run.  The Next Run Time column shows that it should run but doesn't.    Besides creating two triggers, one that starts in the future and another that starts on reboot (which we don't want to do since some tasks should only run at specified times), is there a better way?  This seems to be a bug.  The 2012 version worked great.  UPDATE  There is a workaround for this.  Create a one time schedule that runs indefinitely.  This works after restarts just like you would expect.  I know this isn't great for schedules that are "weird" but works fine for things that we want to run every 3 minutes or whatever.
93224
Is there any way to force SQL Server NOT to mark subscriptions as inactive, ever?  It happens sporadically when there are connection issues and I don't want to have to reinitialize the subscription every time.  Note, I'm not talking about the subscriptions being marked as expired...just as inactive.  We have retention set to 0 (infinite). I'd rather have a big distribution database than have to reinitialize a 15GB database to a subscriber over the WAN. Anyway, the real issue here is that they are getting marked as inactive within just a few hours sometimes...or sometimes a few days...it seems sporadic. Is there any way to tell WHY SQL decided in its wisdom to mark them inactive?
109787
I'd like to know if there is a way to send a notification on deadlock? If so what queries would be required. I understand that SQL Server takes care of deadlocks, I simply would like information on the queries involved.  I found the following to determine long-running queries:  SELECT      creation_time     ,last_execution_time     ,total_physical_reads     ,total_logical_reads     ,total_logical_writes     , execution_count     , total_worker_time     , total_elapsed_time     , total_elapsed_time / execution_count avg_elapsed_time     ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,     ((CASE statement_end_offset         WHEN -1 THEN DATALENGTH(st.text)         ELSE qs.statement_end_offset END     - qs.statement_start_offset)/2) + 1) AS statement_text FROM sys.dm_exec_query_stats AS qs     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st   where total_elapsed_time &gt;= 300000000 --5 min ORDER BY total_elapsed_time / execution_count DESC;    I'd like to know if the above is the right way to go, or is there a better way to determine if any query takes longer than a specific interval say 5 min as shown?  Thanks
119075
How do I limit a SQL Server Profiler trace to a specific database? I can't see how to filter the trace to not see events for all databases on the instance I connect to.
93360
I'm trying to run a command at 10 minutes after 2am every weekday only.  In my launchd .plist file, how can I specify this?    I don't want it to run every 2 hours and 10 minutes, just once every weekday at the specified time.
86329
We have a customer server containing SQL Server 2008R2 with about 50 databases. The issue is that the CPU usage reaches 100% (or close to it) and it lasts for a couple hours. This typically happens in the morning (usually somewhere around 6am - 9am). Average CPU usage is 30%  We looked at the scheduled reports and couldn't find anything - we did decide to schedule them earlier in the morning and that didn't do anything.  I'm going to remote in tomorrow to investigate the issue. We've rebuilt the indexes and that didn't help. I have a set of DMVs to check for queries with highest worker time and longest running time.  Do the SQL Server track all the queries run so that the DMVs can access the information? What other sql snippets would be helpful to diagnose the problem?   edit: Memory usage is pretty high too - about 80-90%. Actually another server is experiencing the same issue at the same time, I believe they're both hosted on a citrix server.  We've also done an index rebuild.
110474
Everyday, I usually start my Mac Mini at 16:00 and shutdown it at 2:00 using the Energy Saver schedule settings.  I would like to be able to set a different behavior on the Weekends starting Mac Mini at 9:00 for example. Do you know any way to achieve that (third party software, cron job, superuser trick)?
69478
I often see questions where people want to know if a certain thing happened, or when it happened, or who performed the action. In a lot of cases, SQL Server just doesn't track this information on its own. For example:   Who last executed stored procedure dbo.MyProcedure? Who updated the salary column in the dbo.Employees table? Who last queried the dbo.Orders table from Management Studio?   But there are several other events that SQL Server does track temporarily by default, and can natively answer questions about, such as:   When was the last time an auto-grow happened in the AdventureWorks database, and how long did it take? Who deleted the dbo.EmployeeAuditData table and when? How many memory-related errors have happened today?   How do I get this information, and how long does it stay available?
37848
I am trying to automate the refreshing of awstats for my webserver and understand that I need to use cron to set that up.  So each user has a crontab file? I was following the instructions  community/AWStats And it says to go to the file at: /etc/crontab That file already has some information in it.  Is this the main crontab file or something?  I have seen some instructions online to use the file that pops up with crontab -e. What is the proper file to use and the best way to do this? If I schedule a task as my non-root user, will it run by itself at the time interval as specified, with no issues? Will I need to restart the server after saving the cron job in the crontab file before it will start working?
93683
I'm having trouble with the design of a temporal database. I need to know how to make sure I have only one active record for any given timeframe for a store. I have read , but I'm afraid I can't wrap my head around how the trigger would work. Particularly, how I would work that trigger into my existing one that prevents updates to records, and inserts a new record instead. My real problem is that I do not know how to prevent a Store from having more than one effective date when the finished date is null. (i.e. prevent 2 active records for a store).  This is what I have, but it allows me to insert a new record for a store with a different effective date.  Table Definition:  /****** Object:  Table [PCR].[Z_STORE_TEAM]    Script Date: 05/09/2014 13:05:57 ******/ IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Z_STORE_TEAM]') AND type in (N'U')) DROP TABLE [Z_STORE_TEAM] GO  IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Z_STORE_TEAM]') AND type in (N'U')) BEGIN CREATE TABLE [Z_STORE_TEAM](     [STORENUM] [int] NOT NULL,     [TEAM] [varchar](10) NULL,     [EFFECTIVE] [date] NOT NULL,     [FINISHED] [date] NULL, PRIMARY KEY CLUSTERED  (     [STORENUM] ASC,     [EFFECTIVE] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY] END GO   Sample Data:  INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (1, N'1', CAST(0x01380B00 AS Date), CAST(0x81380B00 AS Date)) INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (1, N'2', CAST(0x81380B00 AS Date), NULL) INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (2, N'1', CAST(0x01380B00 AS Date), NULL) INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (2, N'2', CAST(0x20380B00 AS Date), NULL)   Instead of Update Trigger:  CREATE TRIGGER [tr_ZStoreTeam_update]     ON  [Z_STORE_TEAM]    INSTEAD OF UPDATE AS  BEGIN     -- SET NOCOUNT ON added to prevent extra result sets from     -- interfering with SELECT statements.     SET NOCOUNT ON;      -- Insert statements for trigger here     INSERT INTO PCR.Z_STORE_TEAM(STORENUM,TEAM,EFFECTIVE)     SELECT I.STORENUM,I.TEAM,GETDATE() AS EFFECTIVE     FROM inserted I     INNER JOIN PCR.Z_STORE_TEAM ST         ON I.STORENUM = ST.STORENUM      UPDATE ST     SET FINISHED = GETDATE()     FROM PCR.Z_STORE_TEAM ST     INNER JOIN inserted I         ON ST.STORENUM = I.STORENUM         AND ST.EFFECTIVE = I.EFFECTIVE END  GO
13012
I have a few cleanup commands to run for my datacaching scheme.  I was thinking a bash script would be an easy way to check if the full clean up job needed to be run.  But the cache is very time sensitive, so the check script needs to run every second.  What's the best way to do this?  Crontab only goes down to minutes, which makes sense for most applications.  Can I install a bash script as a background service?  On second though, can I just start it with the &amp; command and make it loop forever with a one second sleep?
16258
I am working on a Data warehouse. I have tables with up to 200M records. Some of these tables have around 20+ indexes (I can't provide a reason why they have been created in the first place). This is making the job of maintaining these indexes too painful and has a direct impact on the DWH import job in both performance and run time.  How can I find the least used indexes on each table? (in order to get rid of them)
90855
Recently I've been running into issues where deployments were blocked due to a Schedulable job pending or in progress when I'd already cleared the Schedulable job from that class.  Any tips for resolving this issue?  I've had success in the past by querying all CronTrigger objects and aborting the jobs that don't have a status of 'Deleted', but that has the unwanted side effect of cancelling all Dashboard Refreshes as well.  I'm sure there is just a stuck job in there somewhere, but was curious if any of you code heads out there had other ideas ...
68757
I have a SQL query that I am trying to optimize:  DECLARE @Id UNIQUEIDENTIFIER = 'cec094e5-b312-4b13-997a-c91a8c662962'  SELECT    Id,   MIN(SomeTimestamp),   MAX(SomeInt) FROM dbo.MyTable WHERE Id = @Id   AND SomeBit = 1 GROUP BY Id   MyTable has two indexes:  CREATE NONCLUSTERED INDEX IX_MyTable_SomeTimestamp_Includes ON dbo.MyTable (SomeTimestamp ASC) INCLUDE(Id, SomeInt)  CREATE NONCLUSTERED INDEX IX_MyTable_Id_SomeBit_Includes ON dbo.MyTable (Id, SomeBit) INCLUDE (TotallyUnrelatedTimestamp)   When I execute the query exactly as written above, SQL Server scans the first index, resulting in 189,703 logical reads and a 2-3 second duration.   When I inline the @Id variable and execute the query again, SQL Server seeks the second index, resulting in only 104 logical reads and a 0.001 second duration (basically instant).  I need the variable, but I want SQL to use the good plan. As a temporary solution I put an index hint on the query, and the query is basically instant. However, I try to stay away from index hints when possible. I usually assume that if the query optimizer is unable to do its job, then there is something I can do (or stop doing) to help it without explicitly telling it what to do.  So, why does SQL Server come up with a better plan when I inline the variable?