SQL 2012 :: Measuring Server Uptime Over A Quarter
Sep 29, 2014
Here is a brief description what I am actually looking at. As we all have SLA's to understand how much uptime/downtime we can afford maybe per year/per quarter. I am keenly interested in finding out the way of calculating the sql server uptime. I googled for this and didn't find an appropriate solution that can justify my needs.
I am looking at a way that can give me a historical view of the uptime (possibly aggregated over time), considering all the facts for e.g I am not considering the maintenance that we do for keeping our servers up to date which includes patching and stuff, instead I am more focused on the historical view that for e.g if my manager asks me to give him a report stating the uptime for all the sql servers that we have for the current quarter.
Hence, I would basically some kind of script wherein I am storing the history somewhere and at a later date if my manager asks me to give a quarterly uptime report I can pull out that aggregated data, and generate a pie chart or something from that data to show the uptime and downtime for the same. I don't want to use 3rd party tool and I know there are quite a few, but company won't afford it.
View 0 Replies
ADVERTISEMENT
Jun 5, 2014
I have a process to rollover prior quarter data to new quarter in a table.
For example, i have a table with (col1, col2, year, qtr) with data like ( Note: col1 is identity(1,1) )
1,'today',2014,1
2,'tomorrow,2014,1
3,'friday',2014,1
Now when i run my process, above 3 records will be rolled over new quarter 2014 Q2 and the table will be like
1,'today',2014,1
2,'tomorrow,2014,1
3,'friday',2014,1
4,'today',2014,2
5,'tomorrow,2014,2
6,'friday',2014,2
Row 1 with identity 1 has rolled over to new quarter row 4 with identity 4 ( qtr fields are changed )
Row 2 with identity 2 has rolled over to new quarter row 5 with identity 5. Same with last row as well.
Here, i have another table called "ident_map" with columns like (old identity, new identity ) and during rollover i am supposed to load ident_map table with old and new identity. So after rollover is complete, ident_map table should look like
1,4
2,5
3,6
I know using output clause I can capture the new identity values. 4,5,6 in this case. But is there any way to capture both old identity and new identity during rollover so that i can load the ident_map table with old and new identity.
View 9 Replies
View Related
Nov 1, 2002
anyone know where to find the uptime utility for the commandline. This gives you the server's uptime in days. Thanks for your help.
View 3 Replies
View Related
Apr 7, 2006
To view the system uptime.
The @noprocs variable needs to be set to the number of processors the server has.
Also the convert function has to have the correct mask for the regional settings of the os.
I suggest you just run
"net statistics server" from the command prompt and see what the time format is for the server.
(103 is for British/French setting)
CREATE procedure dba_uptime as
set nocount on
create table #output(outp nvarchar(1000))
insert #output(outp)
exec master.dbo.xp_cmdshell N'net statistics server'
declare @upsince datetime, @sqlupsince datetime, @noprocs int
set @noprocs = 8
set @upsince =
(
selectconvert(datetime,replace(outp,'Statistics since ',''),103) as system_up_since
from#output
whereoutp like('Statistics since %')
)
drop table #output
set @sqlupsince =
(
selectdateadd(s,-(32.0/@noprocs)*(cast(@@idle as float)+@@cpu_busy+@@io_busy)/1000.0,getdate())
)
select@upsince as systemUpSince
,@sqlupsince as sqlServerUpSince
go
exec dba_uptime
/*
systemUpSince sqlServerUpSince
----------------------- -----------------------
2006-02-19 03:23:00.000 2006-03-29 08:35:26.803
*/
rockmoose
Edit: fixed convert to int overflow, should work for appx 68yrs uptime now.
View 8 Replies
View Related
Oct 26, 2006
Does anyone know of a way to use a funtion for returning records based on fiscal reporting periods like Quickbooks uses for example "This Month", "Last Month", "This Quarter", "Last Quarter", "This Year", "Last Year". While I realize that I can create a very long date time parsing routine for this but it is not very elegant or useful. I thought there might be a way to do this already with an existing function.I have created a stored procedure that I pass a @ViewRange Parameter to and it returns the records that I want but I need this ability in several procedures and wanted to turn it into a stored procedure.IF @ViewRange = 'This Month' SELECT TOP 20 Customer.LastName AS Customer, SUM(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE (MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP)) AND (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY SUM(Sales.AmtCharge) DESC;IF @ViewRange = 'Last Month' SELECT TOP 20 Customer.LastName AS Customer, Sum(Sales.AmtCharge) AS Amount FROM Customer INNER JOIN Sales ON Customer.CustNo = Sales.CustNo WHERE(MONTH(Sales.InvDate) = MONTH(CURRENT_TIMESTAMP) - 1) And (YEAR(Sales.InvDate) = YEAR(CURRENT_TIMESTAMP)) GROUP BY Customer.LastName ORDER BY Sum(Sales.AmtCharge) DESC; Any ideas?
View 8 Replies
View Related
Sep 5, 2007
Hi friends,
I need a query to find out the server uptime and downtime of the server from MOM database, i don't know in which tables MOM actually stores this infomation.
I need this very urgently.
Thanks in advance
You can use this code to find out the information stored in the MOM tables:-
############################################################################
create PROC [dbo].[SearchMyTables]
(
@SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END############################################################################
View 1 Replies
View Related
Feb 26, 2015
We have this query that pulls number of days worked from the current Quarter to Date.
(SELECT COUNT(DISTINCT daysworked) AS 'Days Worked'
FROM (SELECT CAST(DATEPART(MM, DATEADD(HOUR, -8, ActualEnd)) AS VARCHAR) + '/' + CAST(DATEPART(DD, DATEADD(HOUR, -8, ActualEnd)) AS VARCHAR) + '/' + CAST(DATEPART(YYYY, DATEADD(HOUR, -8,ActualEnd))
AS VARCHAR) AS daysworked, ActivityId AS totalcalls
FROM PhoneCall AS p
WHERE (DATEPART(QUARTER, DATEADD(HOUR, - 8, ActualEnd)) = DATEPART(QUARTER, DATEADD(QUARTER, -1, GETDATE()))) AND (DATEPART(YEAR,
DATEADD(HOUR, - 8, ActualEnd)) = DATEPART(YEAR, DATEADD(QUARTER, -1, GETDATE()))) AND (OwnerId = x.SystemUserId)) AS tb)
AS [Days Worked],
I need changing it to bring up LAST Quarter's data.
View 1 Replies
View Related
Oct 24, 2006
Hi there.
I work for a charitable organization, am new to this form (and sql programming) and trying to create a flag for unique records indicating the number of consecutive years a donor has given.
I have create a sample db idenifying donor, giving year and total pledges with multiple donor records existing for multiple years having donated.
CREATE TABLE mygifts06 (Donor_id varchar (10), Gift_yr nvarchar (4), Tot_pledges numeric (16,2))
INSERT INTO mygifts06 (Id,Gift_yr,Pledges)
SELECT 155758,2005,15.00 UNION ALL
SELECT 155759,2004,25.00 UNION ALL
SELECT 155758,2004,40.00 UNION ALL
SELECT 155757,2005,100.00 UNION ALL
SELECT 155758,2002,30.00 UNION ALL
SELECT 155758,2001,120.00 UNION ALL
SELECT 155755,2003,15.00 UNION ALL
SELECT 155758,2006,80.00 UNION ALL
SELECT 155757,2003,65.00 UNION ALL
SELECT 155759,2005,400.00
For the above dataset, I am trying to create the following output
Donor_id 2_consec_gifts 3_consec_gifts 4 consec_gifts
--------- -------------- -------------- --------------
155755000
155757000
155758110
155759100
Do I need to use a cursor for this task? I lack experienced in using cursors is there an alternative method someone could suggest?
Thanks in advance.
View 9 Replies
View Related
May 29, 2007
Hello,
is there any way to measure the progress of a synchronization operation so as to show a progress bar to the user?
Best regards,
Marcos Ruano
View 5 Replies
View Related
Mar 20, 2015
We had a requirement that need to sum the data based on quater we will be having 12 months data in the system for an year suppose we have 12 records for 2014 year. jan month sales data should be same when we were in feb month it should sum jan+feb sales and should show in sales column whereas we were in march month it should sum jan+feb+mar sales, then same for next quater also apr month it wil be same value in may it should be apr+may in may sales value etc ....
We will be having date column values as 201401,201402,.....
How can we implement in sql sever performance should be good.
View 1 Replies
View Related
Aug 12, 2015
I am getting the time difference between two dates using
DATEDIFF(second,Information.[Start Time],Information.[End Time]) / 60.00 / 60.00 AS hours,
My output looks like
1.33
0.17
1.50
etc
I'd like to round to the nearest quarter hour
1.50
0.25
.150
etc
View 4 Replies
View Related
Oct 4, 2006
I know the rule of thumb is to use T-SQL when manipulating data and to use SQLCLR for conditionals, looping, etc. My question is how much slower (percentage, factor of, anything!) is SQLCLR for doing SELECT, INSERT, and UPDATE commands?
Is the performance difference *that* much greater that the simplicity of SQLCLR doesn't apply?
View 6 Replies
View Related
Jul 21, 2001
Is there any way to measure the progress of a long running query,
for instance, to find where in a query plan a query is in SQL 7.0?
I have a query I am running that is currently 2 1/2 hours into the
query. Since it's joining three large tables, one with 42 million rows
and two with 7 million rows, I'm expecting the query to take a
while. However, I have no way of estimating exactly how long
it will take. Before I ran it, I optimized it the best I could in
Query Analyzer using an estimated query plan, making sure I had
all the right indexes, etc. I've been trying to use the estimated cost
to project query time, but that hasn't been working since queries
with similar costs can take radically different amounts of time to
execute.
Now I'm sitting here waiting, wondering if the query is just taking
too long, and I should stop it and work on optimizing it some more
(since I will have to run a couple more queries like it), or let it
finish. But I have no clue how close it is to finishing. I've tried looking
at the Physical I/O given by sp_who2 and then trying to calculate the
number of pages it would have to read if it had to read everything
from disk, then estimating it's progress by that, but this seems dubious
at best, since I don't know a whole slew of factors (ie: how many
pages are being read from the cache, is my page calculation correct,
etc).
So, does anyone know of any way to figure out how soon a long
running query will finish in SQL 7.0?
Thanks.
--
Trevor Lohrbeer
trevor@truepeers.com
View 2 Replies
View Related
Apr 16, 2007
I want to measure updates/Insertion rate of my databse in order to measure that how percent the databses is booked for insert and update.
can some one can suggest me the mechanism or other resource for doing this work...
thanxs in advance
View 4 Replies
View Related
Sep 12, 2014
How I can measure the volume of data created temporarily to replace usage of physical tables in an SQL query.
View 1 Replies
View Related
May 19, 2004
Hello, everyone:
There is a table named INCOME that has INCOME column for each day and DATE column starting from Aug. 29 1980. How to calculate income summary by each quarter? Thanks.
ZYT
View 9 Replies
View Related
Jan 28, 2007
Hi,
I need to retrieve both Mont and Quarter from an MS SQL server field containing a date as ''01/01/2007 12:10:00'.
Retrieving the Month is working properly, but getting some sql error with the Quarter function.
I will appreciate if someone can indicate me how should I do in order to retrieve the Quarter from a date.
To retrieve the month, I am using:
& "MONTH(STOCK.VALUEDATE) AS 'Month'"
To retrieve the Quarter, I am trying (but getting error):
& "QUARTER(STOCK.VALUEDATE) AS Quarter"
Thanks,
Aldo.
View 3 Replies
View Related
Feb 12, 2006
Hi there everbody,
i am trying to get the last day of the Q + 1 month
the first part i have succeeded (dateadd(ms,-3,DATEADD(qq, DATEDIFF(qq,0,getdate() )+1, 0))) ,but how do i add 1 more month ?
would appreciate any suggestion
regards
Yoav
View 2 Replies
View Related
Apr 19, 2007
It's been a long time since I've built tough queries and need some help.
I need to report all sales that happened within a quarter but were reported after the quarter end.
Quarters
Q1 - 1/1 thru 3/31
Q2 - 4/1 thru 6/30
Q3 - 7/1 thru 9/30
Q4 - 10/1 thru 12/31
Sample Data
ticketid salespersonid saledate sale_entereddate
1234 bsmith 1/1/2006 2/1/2006
1235 jgarcia 3/31/2006 4/1/2006
1236 bsmith 1/1/2006 2/3/2006
1237 jdoe 6/23/2006 7/1/2006
1238 bsmith 8/5/2006 8/6/2006
1239 bsmith 10/1/2006 1/1/2007
View 4 Replies
View Related
Dec 4, 2007
I have a transaction file with Company_code,Gl_code,Amount,Transaction_Date
I want to sum the amount based on a Company_Code,Glcode for every financial quarter(01/04/2007-30/06/2007,01/07/2007-30/09/2007 and so on).
Expected Output
Company_Code,Gl_code,Quarter_1,Amount_1,Quarter_2,Amount_2,Quarter_3,Amount_3,Quarter_4,Amount_4
Regards
Nirene
View 6 Replies
View Related
Aug 1, 2007
I am uisng VS 2005 to build reports for SQL 2005. The problem is with getting the Quarter() function, mentioned in the BOL, to work in the report builder. When I build an expression and try to use the function as noted in the BOL I receive an error stating that the name "Quarter" is not declaired. When I use the expression builder to build the expression the function is not available in the builder, which is probably why I get the error.
I am assuming there is some sort of service pack of hot fix I need to apply to my VS but have not been able to locate which one. I have verified that I have all of the recent service packs applied for VS and SQL server 2005 but I am still unable to see/use this function.
Any help would be greatly appreciated. I need this function for my financial reports.
View 1 Replies
View Related
Apr 4, 2008
Hi,
I am using QUARTER(startdate) formula to retrieve the quarter of a specific datetime and to store in my calculated field in dataset. But while running its showing "error: [BC30451] Name 'QUARTER' is not declared".
Can anyone show me some light on this problem?
View 5 Replies
View Related
Aug 18, 2015
There is a way to get the non empty max calendar quarter of the year and the last year. For example, the max calendar quarter of the last year should be 4, and in this moment the max quarter of this year should be 3.
I am building a report in SSRS and trying to avoid query the DWH database. I want to run every query against the cube.
View 3 Replies
View Related
Oct 2, 2014
I am trying to always get the start of the week of the current quarter in my criteria
This is the statement for the current quarter
Dateadd(qq, Datediff(qq,0,GetDate()), 0)
This is the statement for the current week
DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0)
How to calculate from the start of the week of the current quarter...
View 1 Replies
View Related
Mar 24, 2014
I need accumulate the months by Quarter measurement. For example we're in March now and I need put these code in Where clause like:
Where MyDate > DATEADD(mm, -11, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) ...
here DATEADD(mm, -11, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) makes Apr of last year included because that is the first month in 2nd Quarter of last year. But in next month we're in Apr and I need make -11 as -12 in above code to include Apr of last year. And same reason if we're in Feb. 2014 then I need change -11 to -10.
So how can I calculate this to make the number fit into the code or some other way to get the same effect? I need get all last 4 quarters based on current month (include current Quarter).
View 2 Replies
View Related
Aug 20, 2014
I am trying to group counts by week,month,quarter, year for a particular activity type and I'm having issues.Here's my code so far:
SELECT
distinct
EmailAddressID,
emailaddress,
SUM(CASE WHEN [ActivityDate] >= DATEADD(WEEK, DATEDIFF(WEEK, 0, @DT), 0)
THEN
SUM(CASE WHEN EmailActivityType = 'OPEN' THEN 1 ELSE 0 END) END AS WeekTotalOpens
FROM EmailActivity
WHERE DATEPART(YEAR, [ActivityDate]) = DATEPART(YEAR, @DT)
GROUP BY EmailAddressID,emailaddress
Desired Output:
EmailAddressId EmailAddress WeekTotalOpens MonthTotalOpens etc. then WeekTotalClicks and so on....
SQL doesn't seem to like the sub-aggregate. What is the best way to approach?
View 2 Replies
View Related
Jul 23, 2005
Hello,I have a query that I would like to schedule in DTS. The criteria ofthis query checks for records in the table that are within the currentquarter. Here is what I have.WHERE submit_date BETWEEN '01/01/2005' AND '03/31/2005'I would like to dynamically generate the Quarter End and QuarterBeginning dates within my where clause based on the date that DTWSpackage is being executed on. Can anyone show me how this can beaccomplished?Thank You.
View 3 Replies
View Related
Jun 10, 2015
I am using the following expression to calculate the last day of the last quarter
=dateadd("s",-1,dateadd("q",datediff("q","1/1/1900",today()),"1/1/1900"))
It returns 3/31/2015 11:59:59 PM
What needs to change so that it return 3/31/2015 12:00:00 AM
View 4 Replies
View Related
Feb 28, 2005
I have a program that calls queries (OLAP system) the system includes a dimension of date: Year, Quater, Month, Week
When the result appears in the table, it is not in order? Only the year is in oredr and after that each heirachy is wrong and not in order....not sure how to do this!!!
any help would be grateful!!! not sure what I should be looking at.....
View 14 Replies
View Related
Sep 3, 2007
Hello,
what's best for year resp. quarter Field, char(4) resp. char(1), integer or other?
Both are part of a composite index.
Thanks
Silas
View 6 Replies
View Related
Aug 22, 2014
How do I get first day of last month of previous quarter from today's date? I know my question is little confusing. I need to get 06/01/2014 using t-sql.
View 2 Replies
View Related
Sep 3, 2014
I am working on a report and the data source is Teradata. now I have situation where I want to get order id details based on the current quarter and year I am posting this same data. For TD related queries I do not where to post.
ACCT_ID ACCT_NMORD_NBRORD_DT ORD_AMT_USD
595709114ASDASD444447/28/2014 546
2224809440ASDASD444445/2/2012 546
1724031572ASDASD444446/22/2011 546
1702887651ASDASD444447/3/2014 546
1724020508ASDASD444447/16/2012 546
1148151895ASDASD444449/18/2013 546
2125154824ASDASD444449/2/2014 546
1503552723ASDASD4444412/20/2011 546
2224689808ASDASD4444410/4/2010 546
931387698ASDASD4444412/31/2010 546
View 4 Replies
View Related
May 14, 2015
I have a text field which displays quarter using YYYYQ (i.e. 20142, 20151, etc...)
I need to convert the field to a datetime representing the last day of that quarter. For example 20142 would be 06-30-2014 and 20151 would be 03-31-2015.
View 7 Replies
View Related
