Transact SQL :: Notify Only If Blocking Is Happening For More Than A Minute Or 30 Seconds

Aug 14, 2015

I have configured an alert like below to track all blocked events in SQL Server across all databases and then kick start a sql job when a blocking happens which inserts data to a table, when there is a blocking in SQL server , i get an email  --which is working fine and i am able to track all queries.

but, HOW to get notifications ONLY if BLOCKING IS HAPPENING FOR MORE THAN 30 SECONDS OR 1 MINUTE with out using sp_configure?

---ALERT
USE [msdb]
GO
EXEC msdb.dbo.sp_update_alert @name=N'Blocking Process', 
@message_id=0, 
@severity=0, 
@enabled=1, 

[Code] .....

View 4 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Difference In Seconds To Round To Minute?

May 20, 2014

I'm trying to get the difference between the two times in minutes with the seconds rounded up or down

As an example the difference in the times:

1900-01-01 09:27:49.000, 1900-01-01 09:32:28.000 is 279 seconds which is 4.65 minutes,

using

select
cast(round(datediff(second, convert(datetime, '1900-01-01 09:27:49.000'), convert(datetime, '1900-01-01 09:32:28.000')) / 60, 2) as numeric(18,2))

I am receiving the value 4.00 instead of 5 which I would like 4.65 to round to. How can I get the difference in seconds to round to the minute ?

View 5 Replies View Related

Changing Seconds To Hours And Minutes And Rounding To The Nearest 15 Minute.

Dec 6, 2007

I found in another forum that if I take the seconds and divide them by 15 then round up and multiply them by 4 I can get this done, but I can't figure out how to work it into my select statement. Anyhelp would be greatly appreciated. dbo.SLPTRANS.TimeSpent is the field I am trying to convert.

SELECT dbo.SLPTRANS.ClientID, SUM(dbo.SLPTRANS.TransValue) AS Expr1, dbo.SLPTRANS.TimeSpent AS Expr2
FROM dbo.SLPTRANS INNER JOIN
dbo.INVOICE ON dbo.SLPTRANS.InvoiceID = dbo.INVOICE.RecordID
GROUP BY dbo.SLPTRANS.ClientID
HAVING (dbo.SLPTRANS.ClientID = 405)

View 4 Replies View Related

SQL Server 2014 :: Query Causing Blocking / Locks Table For 1000 Seconds?

Feb 11, 2015

I have a sql snippet from a 3rd party application that will not complete its transaction. The SELECT statement executes but does not finish. Instead the statement just sits in AWAITING COMMAND for 1000 seconds then dies, thus killing the UPDATE statement that is supposed to follow.

The CROSS JOIN and CROSS APPLY seem suspect.

(
@p0 DATETIME,
@p1 INT,
@p2 INT,
@p3 NVARCHAR(4000),
@p4 INT,

[code]....

View 9 Replies View Related

Transact SQL :: Mail Alerts For Deadlocks Happening On System

Oct 22, 2015

I have a SQL server 2014 environment. How to set up a mechanism which will throw a mail alert whenever we get a deadlock on the system.

View 2 Replies View Related

Transact SQL :: Table Locking Happening When Removing Huge Data

May 14, 2015

declare @error int, @rowcount int
select @rowcount = COUNT(1) FROM  STG_BCDR;
while @rowcount > 0
begin
 BEGIN TRAN Deletion

[code]....

Above code i try to delete records batch by batch to avoid table locking at BCDR table.total records in this BCDR  table is 40,000 records.  However I run the code at execution plan, the BCDR table still clustered index scan which means that the locking still happend.

If i change the delete top (5000)...... to delete top (5).... then thre is clustered index seek, which is good..The problem here is  each time  only delete top 5 records which is means it will realy take very long time to remove those  data.

how to cater the situation inorder for me to delete those huge data without table locking happend. If table locking happend , then other user will not be able to access this table at the same time.

View 6 Replies View Related

Transact SQL :: Counting Blocks Of Time In 15 Minute Chunks

Feb 16, 2012

We have data that consists of an employee number, a start time and a finish time, similar to the example below

EMP   STARTTIME            ENDTIME

00001 10-Feb-2012 06:00:00 10-Feb-2012 10:00:00

00002 10-Feb-2012 07:15:00 10-Feb-2012 10:00:00

00003 10-Feb-2012 08:00:00 10-Feb-2012 10:00:00

I am trying to come up with a procedure in SQL that will give me each 15 minute block throughout the day and a count of how many employees are expected to be at work at the start of that 15 minute block. So, given the example above I would like to return

10-Feb-2012 00:00:00     0
10-Feb-2012 00:15:00     0
10-Feb-2012 06:00:00     1
10-Feb-2012 06:15:00     1

[code]....

I'm not too worried if the date part is not included in the result as this could be determined elsewhere, but how can I do this grouping/counting?

View 7 Replies View Related

Transact SQL :: Update Date With Specific Hour / Minute And Second

May 7, 2015

How I can update the following date to get the desired result with specific hour, minute and second
2014-01-01 00:00:00.000

Desired Result
2014-01-01 17:20:20.000

View 4 Replies View Related

Transact SQL :: Use Date / Hour And Minute In Datetime Column Type?

Nov 9, 2015

I just need the date, hour, and minute...not the micro seconds. Do I have to DATEPART and concatenate each or is there any way to simply truncate the milliseconds from it? Or is there a date format to put extract and report on it as...

MM/DD/CCYY HH:MM:SS AM

I see there is a format 131 that puts it in..

DD/MM/YYYY HH:MM:SS:MMMAM

View 7 Replies View Related

Transact SQL :: Only Store Datetime Values Down To Nearest Minute Automatically Without Using Trigger

Sep 25, 2015

Is there a way that I can do this at the table level to automatically handle the rounding of seconds, etc. down to the minute automatically without having to use a trigger?  

Here is a very basic example of what I am trying to do:

--example:  '09-22-2007 15:07:18.850' this is the value inserted into the table by the code
select getdate() 

 --example: '2007-09-22 15:07:00.000'  this is the value I want to store in the table
select dateadd(mi, datediff(mi, 0, getdate()), 0)

View 24 Replies View Related

Transact SQL :: How To Get Difference To Two Datatime In Seconds Using Logparser Query

Sep 23, 2015

In sql we have a built in function

DATEDIFF(second, startdate, enddate)

How can we achieve the same using Log parser query for date time in format (2015-09-22 10:25:30.5220)

Example:

startdate = 2015-09-22 10:25:30.5220.
Enddate = 2015-09-22 10:26:30.5220

After DateDiff I need to get 6 seconds.

View 5 Replies View Related

Transact SQL :: How To Run Query In BAT File In Task Scheduler At Every 10 Seconds

Mar 4, 2015

This is my sql script :

DECLARE @countRows INT,
@currDate DATE,
@checkForTasks INT,
@created_by_id INT,
@gst_ID int;
SET @currDate = Getdate()

This is resided in videos folder of windows , i have created a task schedular in windows 8.1 to run daily at every 10 seconds , but it is not working ...

This sql query is running perfectly in sql server. Query have no errors.

View 9 Replies View Related

Transact SQL :: Identify Blocking Process

May 7, 2015

Imagine I set a begin transaction on table a and updating a row and not committed and not roll backed--first connection

From second connection I am selecting same table (obviously it wait until first connection commits/rollback based on my transaction level: my isolation level is read committed).

1. How do I know second connection is to waiting to first connection to complete.
2. If I want to select rows that are not locked by update process how do i need to do(ex:row 1,2,3,4 and 1 is locked by update process(exclusive lock) and i want to leave that and i need to select 2,3,4 records).

View 6 Replies View Related

Convert Seconds To Hours:minutes:seconds

Jul 23, 2005

Hi all.If I've got a query which has a field with seconds in it... how will I usethe Convert function to get my field converted to the format: HH:MM:SS ?The field with the seconds in is called: "Diff"Thanks alotRudi

View 2 Replies View Related

Why Query Takes 0 Seconds And Stored Procedure Takes 16 Seconds Sql Server 2000

Sep 21, 2007



I have a Stored Procedure that has a query in it and it take 0 second and then a stored procedure that takes 16 seconds. From what I can tell they shoul be the same.

It doesn't recompile when i run the stored procedure, I checked that.

View 8 Replies View Related

Transact SQL :: How To List All Locks (including NON-BLOCKING Locks)

Aug 5, 2015

We are migrating our database(s) from ORACLE to SQL. In Oracle we were able to issue a SELECT statement and see all of the locks (Blocking and Non-Blocking) currently in the system.  The query also included the Process ID of the process we needed to kill in order to get rid of the lock.

We now need to create the same type of query for Microsoft SQL Server 2012. I have seen postings on different sites saying that this info can be obtained using SP_WHO2 or using the SQL Server Management Studio Activity Monitor's PROCESSES tab, but we are looking for a SELECT statement that will give us similar information.

View 7 Replies View Related

What's Happening To This?

Feb 25, 2006

I have 2 aspx pages. one is "login.aspx" and the second is "test_connection.aspx". the "login.aspx" is using the membership class for my website's security. if u have have restarted your computer and you first load this "login.aspx", this will work fine and you will see that you can create a user. when you load (or view) next "test_connection.aspx" you will get this error message:

System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.

Login failed for user 'sqluser1'.

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)



If I will restart my computer and I will load first the "test_connection.aspx", this page will work just fine and if you will load next "login.aspx", i will get this error message:



Cannot open user default database. Login failed.

Login failed for user 'YECIAASPNET'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.

Login failed for user 'YECIAASPNET'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

the page "test_connection.aspx" is using an sql username i have created. I am only using one database in this project and thats it ASPNET.mdf

what's happening? i cant understand. I dont know what im doing wrong. i am very new to this dotNET and MS-SQL. im not sure if this has relation to the other problem i have on the other thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=254568&SiteID=1).

PLS HELP.

View 4 Replies View Related

No Transactions Are Happening

Dec 24, 2007

i was working on a production server, and have stopped the sqlserver service along with the sql server agent. since i had to copy a MDF file . now i started the service again . but i find that there are no transactions happening ....what could be the reason.

View 8 Replies View Related

Insert Is Not Happening

Jan 12, 2008

Hi,

Im facing one problem When more than one user tries to insert record through the application only one users data is getting inserted. Wat might be the problem?

This is the stored proc im using..

create
proc [dbo].[GSK_insertregion] (@Country varchar(50),@Userid int,@RegionId varchar(50))
as
begin
insert
into UserHierarchy(Country,Userid,RegionId) values(@Country,@Userid,@RegionId)
end


Please help..

Sundaresan.R

View 4 Replies View Related

Logging Not Happening

May 23, 2008

Hi,

I set up my package for logging to SQL Server. I set up a connection manager for the logging, but did not specify a database. Doesn't that mean that the logging should default to msdb.sysdtslog90?

But when I check the table, there's nothing in it, after I run the package.

What gives?

View 1 Replies View Related

Database Connection Not Happening. Please Help!

Jan 13, 2004

Hi
I have deployed a website on a server having Windows2000, IIS5.0 . It uses SQL Server 2000 which is on another remote server. While developing I used the visual tools in VS.net to make a connection and have used DYnamic properties of the connection object to map the connection string to the entry in to the config file.
This works fine on my developement machine which has IIS and SQL Server 2000 on the same machine.
The entry in the web.config for my connection string is:

value= " server=xxx.yyy.com; Trusted_Connection=yes;provider=SQLOLEDB.1;Initial Catalog=events; User id=myuser; Password=password;"

where xxx.yyy.com is the server running SQL Server2000.

I do not get any error but the conncetion doesnot happen and my datagrid doesnot get filled.
The code for creating the connection is designer generated code.
Any clues?
-svp

View 20 Replies View Related

Mysterious Backups Happening

Jul 11, 2000

I'm no guru but this one has me stumped!!

Each day there is a backup occurring of a database but nothing is scheduled either as a Job or under the database backup scheduling option.

Any suggestions greatly appreciated.

Thanks.

View 1 Replies View Related

Can't Find Out Where This Insert Is Happening.....

Sep 6, 2007

Hello people, I don't expect anyone to know the answer to this but I guess we'll see huh?

I'm using Microsoft SQL Server Management Studio to do all my SQL stuff, and one of the tools that comes with this is a program called Microsoft SQL Profiler 2005. Anyways, so I'm using this profiler to capture SQL processes in the background while I work with the front end.

Here is a problem I'm facing... I am looking at this table right... and I see that when I do this certain process on the front end, it inserts 3 rows into the table. So I'm thinking "I know that on the tracer, I should be looking for an insert or a Stored Procedure with and insert in it."

So I use the tracer on the front end process and it shows me all the stored procedures that happen during the process of inserting these 3 rows into this table (btw this process is doing other things besides inserting stuff into this table, but it's what i'm currently working with at the moment.)

So what I do here at my job is I take this code, tweak it to work for our front end, and wah lah.... we're good to go. So in saying that, I make my own stored procedure and I all these stored procedures that are happening.

My result......

I get only 2 row inserts into the table.... the 1st row and the last row..... the middle row isn't inserted for some mysterious reason. I tried checking all the stored procedures for unique information pertaining to that specific row insert but to no avail I couldn't.

So my question to you guys is.... is there anything I'm overlooking that could possibly be inserting that row in the table? Thanks guys!

View 2 Replies View Related

Tables Seem Too Big - Timeouts Happening

Jul 23, 2005

I have two problems I need some help with.First, I've just inherited a system and am delving into a few timeoutproblems that are causing problems for the users.Now, if I do a simple select * from the table (which looks to be thecause of the problem at this stage) in QA, I get the results back inless than a second. If I open the table in EM it takes about 10. Isthere a difference in viewing the data this way ? I'm used to EM beingvirtually the same speed. There is only one row. Minor questionreally, just something I'd like to understand if there is adifference.CREATE TABLE [QUERY] ([QUERY_ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[CAT_ID] [numeric](18, 0) NOT NULL ,[QUERY_DESCR] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[USER_NAME] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[USER_ID] [int] NOT NULL ,[IND_EURO] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULLCONSTRAINT [DF_QUERY_IND_EURO] DEFAULT ('N'),[IND_DGCOLUMNS] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL CONSTRAINT [DF_QUERY_IND_DGCOLUMNS] DEFAULT ('N'),[NO_GROUPS] [int] NOT NULL CONSTRAINT [DF_QUERY_NO_GROUPS] DEFAULT(0),[NO_FIELDS] [int] NOT NULL CONSTRAINT [DF_QUERY_NO_FIELDS] DEFAULT(0),[NO_LINES] [int] NOT NULL CONSTRAINT [DF_QUERY_NO_LINES] DEFAULT (0),CONSTRAINT [PK_QUERY] PRIMARY KEY CLUSTERED([QUERY_ID]) WITH FILLFACTOR = 90 ON [PRIMARY] ,CONSTRAINT [FK_QUERY_QUERY_CATEGORY] FOREIGN KEY([CAT_ID]) REFERENCES [QUERY_CATEGORY] ([CAT_ID]) ON DELETE CASCADE ON UPDATE CASCADE) ON [PRIMARY]GOI don't think any re-indexing has been done on this (or the othertables in the db). I was wondering if constant adding/deleting rowscould cause the index to be massive and in need of a good clear out.Any pointers would be appreciated. From what I can tell, there wassome problems trying to get replication to work. I need to dig deeperto see if this is now correct.-------------------------Secondly, there is a another table in the same database.CREATE TABLE [FIELD_DATA] ([ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[DATA_ID] [numeric](18, 0) NOT NULL ,[FIELD_ID] [numeric](18, 0) NULL ,[FIELD_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[FIELD_VALUE] [numeric](15, 5) NULL ,CONSTRAINT [PK_FIELDDATA] PRIMARY KEY CLUSTERED([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]GOIt holds approx 4 million rows. The rest of the tables have minimaldata and about the same amount (consider them the same if you will).Now, another 'copy' of this database is held elsewhere (differentclient data) and this holds 40 million rows. The difference is thatthe first DB is 4.5GB and the second 6.5GB (approx). Does this provemy theory that re-indexing would be a good idea ?ThanksRyan

View 3 Replies View Related

What Will Be Happening To The SSRS While DB Mirroring?

Nov 2, 2006

hi there,

can the SSRS works with DB mirroring?
If yes, how to do it?

I just tried to enble mirroring for reportserver and reportservertempDB, but it doesnt work after failover.
Any idea ?

View 6 Replies View Related

Notify Operator

Dec 2, 2006

Hello,

I have a problem with the notification. I have enabled Database mail and i have created an Operator.

Under SQL Server AgentJobs and then in the job properties i have enabled notification but i don`t receive a mail. Test e-mail works fine but "real" e-mail not.

I have also tried with the Database Maintenance Wizard but even then it just not work.

I have noticed that others have experienced the same problem but i have seen no solution yet.

Could you please help me? What can cause this problem? What`s the best i can do to start searching for the solution? Thanks!

View 6 Replies View Related

Do You Know How To Solve This Event Warning From Happening?

Jan 17, 2003

18275 :
Unnamed tape (Family ID: 0xcfb8a96c, sequence 1) dismounted from tape drive ‘master tape backup’.


this also happens for the mounted part also, how do I name a tape drive? or stop these warnings in the event logs?

thanks for your help

View 1 Replies View Related

Update Not Happening Using Merge Statement

Feb 11, 2015

Even rowchecksum is different in target from source then also update is not happening

alter Procedure SP_Archive_using_merge
AS
BEGIN
SET NOCOUNT ON
Declare @Source_RowCount int
Declare @New_RowCount int

[Code] ....

View 1 Replies View Related

Newbie Tears, Or What's Happening With DECLARE?

Apr 12, 2008



declare @old varchar(30); set @old = 'old';


Command(s) completed successfully.


select @old;



Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable "@old".


Why?!

View 4 Replies View Related

Notify By Email Two Different People For The Same Job

Jun 6, 2001

Currently, I have set up a SQL 7.0 database backup job and have one person notified if the job succeeds, but HOW do I choose an option to notify a different person if the job fails ?

Thank you in advance

View 5 Replies View Related

HELP! Jobs Fail To Notify

Sep 25, 2001

I can not get SQLServerAgent to Send Mail. Help!

SQLMail started successfully.

In the server agent properties I use the Test button and I receive the
response.
"Successfully started(and stopped) a mail session with this profile."
Indicating that mail should work.

If I send a email notification from a scheduled job it gives me the error:
NOTE: Failed to notify 'operations' via email

In the SQL Server Agent Error log I get.
"[264] An attempt was made to send an email when no email session has been
established"

xp_sendmail works fine.

Any ideas would be greatly appreciated.

TIA...

View 1 Replies View Related

How To Notify A DBA When An Object Is Created ?*V *?

Aug 14, 2000

Hi Guys,

Is there a way to notify DBA when a new stored procedure,table,view is created by any user...

Help is greatly appericated..

Jessi..

View 1 Replies View Related

Using Trigger To Notify An Assembly Of Changes.

Oct 13, 2006

I have a requirement to create a sorted list of objects, stored withinan assembly, and I would like to use a After Insert/Update trigger tonotify that assembly that something has changed, rather than pollingthe database for changes all the time.My initial problem is that I need to create a static list so that itcan be dynamically looked at by other assemblies, but SQL Server won'tallow me to attach a dll which contains a static method.Has anyone done something like this? Are there any decent examplesavailable anywhere?any help will be appreciated.simo

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved