SQL Server Admin 2014 :: Deleting Job History For Particular Time Period
Feb 8, 2015How to delete the job history for particular runs.
Suppose if a job run 3 times/day. I want to keep the oldest information and delete the recent 2 runs history.
ADVERTISEMENT
SQL Server Admin 2014 :: Deny Access To AD Login For Certain Period Of Time
Apr 23, 2015SQL server job or SP to deny access to an AD login for certain period of time to SQL server instance...i.e. to deny access to login ADxyz from 12 PM to 10 PM and revoke access to same login at 10:01 PM...
View 3 Replies View RelatedSQL Server Admin 2014 :: Job History Filter By Run Date?
Mar 31, 2015I have this script that I'm trying to filter the results of the Job History to the day prior at 1800 hours.
It return dates prior to what I have in the WHERE clause.
What should the WHERE Clause look like?
USE msdb
Go
SELECT j.name JobName,h.step_name StepName,
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,
STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,
h.run_duration StepDuration,
case h.run_status when 0 then 'failed'
[code]....
SQL Server Admin 2014 :: Timeout Period Elapsed Prior To Completion Of Operation
Jul 28, 2014I am currently running SQL 2014 in my production environment. Both machines are configured for Windows Failover Cluster Services so that we could take advantage of AlwaysOn Availability Groups.
Currently we are experiencing a issue where our web application is throwing an error, "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." I am pretty sure that the error is being thrown from the application, but I can't prove it. Because the error states it is SQL, the consensus seems to be that SQL is having the issue. I need to know how to troubleshoot it to ensure that SQL is not the problem.
SQL Server Admin 2014 :: Restore Database Without Backup History In MSDB
Jul 23, 2014What is the best way to restore a database from a folder of backups (including full, diff and log backups) without using the backup history in msdb?
I have a restore process that restores all backups on a regular schedule in order to fully verify their integrity. To do this, I use the backup history in msdb on each server that I'm monitoring. I had a thought the other day that I would be in trouble if I lost msdb. Then my backup history would only be as good as the last backup of msdb.
What I'd like to do is read a folder of backup files and generate a restore script up to a specified time. Would I use RESTORE HEADERONLY to do this? If so, would I use PowerShell to traverse each file in the folder?
SQL Server Admin 2014 :: Schema Change History - Identify Who Dropped A Table?
Jun 30, 2015How can I easily identify who dropped a table?
View 8 Replies View RelatedSQL Server Admin 2014 :: Time Zone On Reporting Services?
Oct 6, 2014I've migrated a server with SQL Server 2008R2 and Reporting services into a new box with SQL Server 2014, but forgot to change the timezone to the correct one. I've changed it later, but it seems like the reports are running by the old default timezone. The schedule says that the report should run at 6:30am, but the Last Run column shows 8:30 PM.
I need to fix it without manually updating each subscription with some date/time conversion.
SQL Server Admin 2014 :: How To Get Number Of Execution In Specific Time - Not From First
Apr 7, 2015I have this query
SELECT top 100 Ltrim([text]),objectid,total_rows,total_logical_reads , execution_count
FROM sys.dm_exec_query_stats AS a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS b
where last_execution_time >= '2015-04-07 10:01:01.01'
ORDER BY execution_count DESC
But the result of execution count is from the first. I want to know it only one day.
SQL Server Admin 2014 :: AlwaysOn Query Which Would Tell Failover Time
Jul 27, 2015Is there any single TSQL query which provides below info.When did my AlwaysOn Availability group failed over and from which node it failed to which new node(i.e. replica)?
View 3 Replies View RelatedSQL Server Admin 2014 :: Concatenate Random Date And Time
Oct 20, 2015I've used some info on here to generate random dates within a given range and also random times - independently they work fine, but I can't seem to join them into a single field of datetime. I'm not sure why. The following snippet works fine as two independent fields:
select CAST(CAST(ABS(CHECKSUM(NEWID()))%(780)+(33968) AS DATETIME) as DATE) as theDate,
CAST(CAST(DATEADD(milliSECOND,ABS(CHECKSUM(NEWID()))%86400000 ,'00:00') AS TIME) as varchar(50)) as theTimeBut when I try to make it a single datetime field:
select CAST(cast(cast(CAST(ABS(CHECKSUM(NEWID()))%(780)+(33968) AS DATETIME) as date) as varchar(50)) + ' ' + cast(CAST(CAST(DATEADD(milliSECOND,ABS(CHECKSUM(NEWID()))%86400000 ,'00:00') AS TIME) as varchar(50)) as varchar(50)) as datetime)
Which returns with: Conversion failed when converting date and/or time from character string.
So what I am really looking for is a way to join those two values into a single datetime field... Or failing that that how to generate random dates within a range including random times...
SQL Server Admin 2014 :: Cursor Stop Query At Specific Time
Oct 29, 2015This store procedure will get some executable queries from the select statement, the cursor will fetch each rows to execute the query and insert the queries into table_3 to mark as 'E'. Until 17:00, this store procedure will stop execute the queries and just get the queries from select statement insert into table_3 to mark as 'C'.
I don't know why the outputs in table_3 are quiet different than I think. This store procedure comes out with two exactly same queries and one marked as C and another marked as E.
CREATE PROCEDURE procedure1
AS
DECLARE cursor_1 CURSOR FOR
SELECT
'This is a executable query'
FROM table_1
DECLARE @table_2
DECLARE @stoptime DATETIME = NULL;
[Code] ....
SQL Server Admin 2014 :: Point In Time Restores Referencing Copy-only Backups?
Nov 25, 2014On one of our SQL Server 2014 boxes each database has a copy-only full backup made every night, in addition to the maintenance plan schedule of a full backup weekly, daily differential backups and log backups.
When performing a PIT restore in SSMS the restore file list lists the most recent copy-only backup as the full backup to use, not the most recent plan full backup. I noticed that using SSMS 2008 to start a PIT restore on the 2014 box does not have this problem, and lists the correct restore file sequence (ignores the copy-only backups).
SQL Server Admin 2014 :: Is Bulk Logged Recovery Model Support Point In Time Recovery
Dec 23, 2014is bulk logged recovery model support point in time recovery
View 9 Replies View RelatedSQL Server Admin 2014 :: Does Security-admin Role Plus Deny Alter Any Login Cancel Each Other Out
Aug 27, 2015I want to set up a database role so that users can use sp_readerrorlog through SSMS. It does a check on membership in the securityadmin role.
I have tested it and can see you can grant execute on xp_readerrorlog but the SSMS GUI uses sp_readerrorlog.
I thought I could create a user/certificate and add the signature to sp_readerrorlog but it's not permitted (likely because it's not a normal database object).
So the other solution is to add the users to the securityadmin role but then explicitly deny alter any login (best done with a custom server role in 2012+ but otherwise just manually in 2008). I tested this out and it works, I'm not able to alter any logins or increase my own permissions, I also did a check of what's reported from fn_my_permissions(null, null) and it shows minimal permissions like I'd expect.
T-SQL (SS2K8) :: Deleting Only 1 Row At A Time Instead Of Using Condition And Deleting Many Rows?
Jul 18, 2014/****** Object: StoredProcedure [dbo].[dbo.ServiceLog] Script Date: 07/18/2014 14:30:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[ServiceLogPurge]
-- Purge records dbo.ServiceLog older than 3 months:
-- Purge records in small portions to avoid locking production tables
-- for a long time. The process takes longer, but can co-exist with
-- normal usage of the tables.
[Code] ...
*** Getting this error below when executing the code ***
Msg 102, Level 15, State 1, Procedure ServiceLogPurge, Line 45
Incorrect syntax near 'Failed:'.
SQL Server 2014 :: Gathering Stored Procedure Execution Time In Real Time?
Jun 11, 2015Is there a way to keep track in real time on how long a stored procedure is running for? So what I want to do is fire off a trace in a stored procedure if that stored procedure is running for over like 5 minutes.
View 5 Replies View RelatedSQL Server Admin 2014 :: Encryption Key Not Known
Apr 14, 2015I inherited a lot of Servers to upgrade to 2014 to include an SSRS Server.
The encryption Key was never backed up and it seems that no one knows what the password is?
Do I have to manually load the reports? There are a lot of Reports.
[URL]
SQL Server Admin 2014 :: BCP Aborts On First FK Violation
Sep 26, 2013I want to use BCP to load data from a text file.
By default, constraints are turned off in bcp, so I use the CHECK_CONSTRAINTS hint.
bcp aborts if ANY of the rows contains a FK violation. No data get loaded.
So if I add the -b 1 batch size option, it loads all data UNTIL the first FK violation, but nothing after that.
I want to load EVERYTHING ... except for the violations. But bcp won't let me. Is there a way?
SQL Server Admin 2014 :: What Is The Default SA Password
Jan 13, 2014If I install an instance with Windows Only authentication, and then change it to Mixed Mode, if I enable the sa login, the password has already been set. What is the default? If it's generated, how secure is it? Is the password generated? What algorithm is used for that?
View 9 Replies View RelatedSQL Server Admin 2014 :: How To Restore MDF File
Mar 21, 2014My sql databases in SQL Server 2014 has the status "suspend" as I saw in SQL Management Studio. I can't restore to serviceable condition sql databases through standard procedures. I need to restore .mdf file.
View 9 Replies View RelatedSQL Server Admin 2014 :: Return One Field / One Row
Jun 18, 2014I am using a monitoring system where I can monitor a numeric SQL result assuming the result is one field and one row.I would like to do this to say monitor the free available space or percentage on say the Master database. DBCC SQLPERF gives me a few columns and results for all databases on the server.
View 2 Replies View RelatedSQL Server Admin 2014 :: DNS Pointing To A Listener
Jun 25, 2014In our environment applications are using a DNS name which points to the physical server ip address. Now we are planning to move to 2014. We are planning to have servers in different subnets so we will be having two ip adresses for listener. How we can point the DNS to the listener ips? If failover happens can the DNS point to the exact ip address of the listener where it's primary node?
View 1 Replies View RelatedSQL Server Admin 2014 :: How To Schedule A Job To Run At Different Intervals
Jul 31, 2014Is there a way to schedule a sql job to run at different intervals
For eg:
The job should run at
7:00 Am
8:00 AM
and then at 10:00 Am
SQL Server Admin 2014 :: Non-yielding On Scheduler
Nov 19, 2014We installed the Sql 2014 in Test server.
We observed frequently
"Process 0:0:0 (0x1e10) Worker 0x00000006B6D341A0 appears to be non-yielding on Scheduler 13. Thread creation time: 12906028806348. Approx Thread CPU Used: kernel 0 ms, user 0 ms. Process Utilization 13%. System Idle 84%. Interval: 70189 ms."
Is it better to run the profiler or performan counter?
What are the filters we have to select in the profiler to monitor the Sql server
SQL Server Admin 2014 :: Reporting Services Through IIS?
Dec 8, 2014I have a SQL server box running 2014 reporting services. I have another server running IIS v8.
I would like to be able to connect to the IIS site and be given the SSRS report browser.
So externally if I browse to [URL], I am presented with the report server interface, the same as if I browse to http://xxx.xxx.xxx.xxx/reports internally.
What are my options?
SQL Server Admin 2014 :: DB Read Only Copy
Dec 11, 2014What is the best approach for a read only copy of a database that is ~ 1TB. The primary database is fed nightly with an ETL process. We are currently trying to duplicate the ETL to read only server but that process is not going well. So we are looking at other options to let SQL make the copy.
The primary database is on a Win12R2 with SQL 12 or 14, a 2 node A/P failover cluster.
The read only copy will be on a Win12R2 with SQL 12 or 14. It is not a requirement to fail over to the read only copy if the primary should go down.
What would best the approach to accomplish the end result?
SQL Server Admin 2014 :: Possible Errors In AlwaysOn
Jan 12, 2015I did not worked on AVG in sql 2012, what are the possible errors and how to resolve in always on in sql 2012 .
View 1 Replies View RelatedSQL Server Admin 2014 :: Script To Failover
Jan 18, 2015I have 10 databases which are configured as principal in mirroring I need to failover all the databases as part of failover , instead of writing query each database as parner failover, is an script which will generate the databases as principal to failover ?
View 3 Replies View RelatedSQL Server Admin 2014 :: Delay In Between Replicas In AG
Feb 18, 2015How to check the delay in between replicas in AG?
View 3 Replies View RelatedSQL Server Admin 2014 :: OST File Is Not Accessible
Feb 26, 2015my 4 GB .OST file is corrupted and i am not able to access my mails and contacts.
View 2 Replies View RelatedSQL Server Admin 2014 :: How To Identify The Job With The Code
Apr 7, 2015I got query from sysproccesses and the result for one spid is : SQLAgent - TSQL JobStep (Job 0xF3BDA6DFF9DCF94D81FF97C49EDA7C61 : Step 1
Which job has this code : 0xF3BDA6DFF9DCF94D81FF97C49EDA7C61
SQL Server Admin 2014 :: SSMS Will Only Run As Administrator
May 28, 2015After installing SSMS on some computers - the only way we can get SSMS to run correctly is to run it as the administrator. Is there a way where you don't have to do that? These end users are logging as themselves and have accounts in SQL Server all set up - but SSMS will only launch for them if we right click and select "run as administrator".After doing some digging - it seems that this is a common problem out there.
View 3 Replies View RelatedSQL Server Admin 2014 :: Old Backups Will Not Delete
Jun 19, 2015Have a SQL 2014 install and cannot for the life of me get the maintenance plan to remove old backups. I've tried everything. Rights to the folder where the backups are stored are adequate, extension set in the clean up task is as it should be, etc. Log shows the job ran successfully. Running the command manually shows successful completion, but backups are still not removed.
View 9 Replies View Related