Search

Monday, February 24, 2014

Last time SQL instance was restarted

You can use below methods to check the last time a SQL Server instance was started. 

Option 1 : Search the SQL Server error log for process start time. 

--search the current sql server error log for the the startup process id
DECLARE @ProcessType VARCHAR(64) = 'Server'
DECLARE @TextSearch VARCHAR(20) = 'Server process ID is'
DECLARE @LogType TINYINT = 1 -- 1 = SQL 2 = Agent
DECLARE @LogNum TINYINT = 0 -- 0 = current 1 =.1 etc
DECLARE @ErrLog AS TABLE([LogDate] DATETIME, [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))
INSERT INTO @ErrLog EXEC sys.xp_ReadErrorLog @LogNum, @LogType, @ProcessType, @TextSearch

--grab the first occurrence and report back the timestamp as the last startup
DECLARE @LastRestart DATETIME
SELECT @LastRestart = MIN(LogDate) FROM @ErrLog
SELECT @LastRestart AS [Last Restart]
GO

Option 1 : You can use below DMV also to check last start date of SQL Server instance

SELECT SQLServer_Start_Time AS [Last Start Date] FROM sys.dm_os_sys_info

Monday, February 17, 2014

Connect PerfMon to a Remote Server

I faced several connectivity issue during connecting Perfmon from remote server.

Server Access denied
Solution: PerfMon is trying to connect the remote server using logged in user account which have no access on remote server. Now, I need to run perfmon using a different user account which has access to the remote server.

DNS not able to resolve the server name
Solution: I have fixed the issue by adding an entry in the hosts file (C:\Windows\System32\drivers\etc).

Unable to connect the server
Solution: I have checked & found that Perfmon is trying to use Port 445 for connectivity. Telnet showed that port number 445 is blocked. I have added port into firewall exception & it worked.


Monday, February 10, 2014

Check Expiry Date of SQL Server Evaluation Version

SQL Server Evaluation Version is available free of cost for evaluating the Server. It is a trial version. It is valid for limited time (180 Days). The expiry date is always 180 days from the initial installation Date. The following query will return the expiry date of SQL Server Evaluation Version:

SELECT @@SERVERNAME AS Server_Name, Create_Date AS Installation_Date, 
ServerProperty('EDITION') AS [Version], DATEADD(DD, 180, Create_Date) AS [EXPIRY DATE]
FROM SYS.SERVER_PRINCIPALS 
WHERE SID = 0X010100000000000512000000

SID 0X010100000000000512000000 is associated with login 'NT AUTHORITYSYSTEM' and it is created at the time of installation only.

Monday, February 3, 2014

List all Disabled Constraint in a database

Use below query to list out the disabled constraint in a database:

SELECT OBJECT_NAME(CONSTID) AS Constraint_Name,
OBJECT_NAME(FKEYID) AS Table_Name,
COL_NAME(FKEYID, FKEY) AS Column_Name,
OBJECT_NAME(RKEYID) AS Referenced_Table_Name,
COL_NAME(RKEYID, RKEY) AS Referenced_Column_Name, 
(CASE WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED' ELSE 'DISABLED' END) AS [Status]
FROM SYSFOREIGNKEYS
WHERE (CASE WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED' ELSE 'DISABLED' END) = 'DISABLED'
ORDER BY Table_Name, Constraint_Name, Referenced_Table_Name