SIVA SQL Server

Friday, October 18, 2013

Query to get the latest FULL backup details

SELECT
cast ((backup_finish_date - backup_start_date) AS TIME(0)) as DURATION,
SERVER_NAME,b.DATABASE_NAME,[TYPE],backup_set_id,
b.backup_start_date,b.backup_finish_date,
Round(b.backup_size/(1024*1024),2) as Size_n_MB,
Physical_device_name
FROM msdb.dbo.backupset b,
( select a.DATABASE_NAME, MAX(a.backup_set_id) id
from msdb.dbo.backupset a
WHERE a.TYPE = 'D' and
a.DATABASE_NAME = DATABASE_NAME
group by a.DATABASE_NAME ) cc,
msdb.dbo.backupmediafamily f
WHERE TYPE = 'D'
AND b.backup_set_id = cc.id
AND b.media_set_id = f.media_set_id

Find time difference between two dates

SELECT
b.backup_finish_date,
b.backup_start_date,
CAST ((b.backup_finish_date - b.backup_start_date) AS TIME(0))
FROM backupset b
WHERE convert(varchar(12),b.backup_finish_date,101) = '10/13/2013'

Wednesday, September 25, 2013

Update job notification

Following Query will update the Existing job name starts with ‘WIN%’ as on complete to notify the email operator 'SQLDBASupport'

select
'EXEC msdb.dbo.sp_update_job @job_name=N'+CHAR(39)+name+CHAR(39)
+' ,
        @notify_level_email=3,
        @notify_level_netsend=2,
        @notify_level_page=2,
        @notify_email_operator_name=N'+char(39)+'SQLDBASupport'+char(39)
from msdb.dbo.sysjobs
where name like 'WIN%'
and notify_email_operator_id =0

Thursday, August 15, 2013

SQL Server: Query to check if given column exists

select  
        s.[name]            'Schema',
        t.[name]            'Table',
        c.[name]            'Column',
        d.[name]            'Data Type',
        d.[max_length]      'Max Length',
        d.[precision]       'Precision',
        c.[is_identity]     'Is Id',
        c.[is_nullable]     'Is Nullable',
        c.[is_computed]     'Is Computed',
        d.[is_user_defined] 'Is UserDefined',
        t.[modify_date]     'Date Modified',
        t.[create_date]     'Date created'
from        sys.schemas s
inner join  sys.tables  t
on s.schema_id = t.schema_id
inner join  sys.columns c
on t.object_id = c.object_id
inner join  sys.types   d
on c.user_type_id = d.user_type_id
where c.name like 'Person%'

Tuesday, April 30, 2013

How to: search for a word or column name across the Procedure, Function, Trigger, View

DECLARE @SearchPhrase nvarchar(1000)

SET @SearchPhrase = N'backup' -- Place your search string

SELECT
DISTINCT sysobjects.name AS [Object Name],
CASE
WHEN sysobjects.xtype = 'P' THEN 'Stored Proc'
WHEN sysobjects.xtype = 'TF' THEN 'Function'
WHEN sysobjects.xtype = 'TR' THEN 'Trigger'
WHEN sysobjects.xtype = 'V' THEN 'View'
END as [Object Type],
(SELECT ParentTable.[Name]
FROM sysobjects ParentTable
WHERE ParentTable.id = sysobjects.Parent_obj) ParentTable
FROM sysobjects,
syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type in ('P','TF','TR', 'V')
--AND sysobjects.category = 0 --- To restrict only user defined object
AND CHARINDEX(@SearchPhrase, syscomments.text) > 0

How to: find when the database last got restored?

   1:  SELECT destination_database_name,max(restore_date) as restore_date

   2:  FROM msdb.dbo.restorehistory

   3:  WHERE destination_database_name = 'testDB’  ---- Place your database name here

   4:  GROUP BY destination_database_name

The result will look like;

destination_database_name restore_date

testDB 2013-04-29 11:49:19.940

SQL 2008 Management Studio does not allow to save changes to tables

In SQL Server 2008 Management Studio,

We can not do

•Change the Allow Nulls setting for a column

•Reorder the columns

•Change the column data type

•Add a new column

How to fix / Resolution:

•Go to Tools -> Options -> Designers -> Tables and Designers

•Uncheck the Prevent Saving Changes that require table re-creation

Wednesday, March 20, 2013

SQL Server Contained Database Authentication process flow

Source: http://blogs.msdn.com/b/sqlsecurity/archive/2010/12/08/contained-database-authentication-in-depth.aspx

Monday, December 03, 2012

The operation cannot be performed on a database with database snapshots or active DBCC replicas

When trying to restore the database with snapshots got the following error SQL message

SQL Server T-SQL Command

restore database PRD_Accounts from disk ='H:\PRD_Accounts_backup_daily.bak' with replace, stats

Error:

Msg 5094, Level 16, State 2, Line 1
The operation cannot be performed on a database with database snapshots or active DBCC replicas.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Resolution

Since we cannot restore a database if we have any snapshots.
Please drop all snapshots and try database restore again.

1. To check the database snapshot and it’s source database

select database_id, name,source_database_id,Source_DBName=DB_NAME(source_database_id)
from master.sys.databases
where source_database_id IS NOT NULL

2. Drop the Snapshot database

DROP DATABASE Accounts_Rpt

3. Create script of snapshot database, before dropping it. If at all we need to re-establish the snapshot after restore we can use it

USE master
GO

/****** Object: Database [Benefits_Rpt_sol48u] Script Date: 12/03/2012 00:01:02 ******/
CREATE DATABASE [Accounts_Rpt] ON
( NAME = N'PRD_Accounts', FILENAME = N'E:\MSSQL\Data\Accounts_Rpt.ss1' ),
( NAME = N'indexdat1', FILENAME = N'E:\MSSQL\Data\Accounts_Rpt.ss2' ) AS SNAPSHOT OF [PRD_Accounts]
GO

Thursday, November 08, 2012

Error on executing sp_cycle_agent_errorlog Msg 22022, Level 16, State 1, Line 0

sp_cycle_agent_errorlog

Every time SQL Server Agent is started, the current SQL Server Agent error log is renamed to SQLAgent.1; SQLAgent.1 becomes SQLAgent.2, SQLAgent.2 becomes SQLAgent.3, and so on. sp_cycle_agent_errorlog enables you to cycle the error log files without stopping and starting the server.

This stored procedure must be run from the msdb database.

Error on executing of sp_cycle_agent_errorlog

USE msdb

EXEC sp_cycle_agent_errorlog

Msg 22022, Level 16, State 1, Line 0

SQLServerAgent Error: Access is denied.

Each time the stored procedure was run it just overwritten the current agent log without renaming it and because of this only one SQLAGENTOUT file was left.

Wednesday, November 07, 2012

SQL Mail: The mail could not be sent to the recipients because of the mail server failure.

SQL Mail Sent fail with errors below;

Error from the SQL Mail log:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2012-11-07T22:09:50). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it 205.191.22.44:25). )

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2012-01-17T11:13:58). Exception Message: Could not connect to mail server. (No such host is known). )

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2012-01-17T11:12:44). Exception Message: Cannot send mails to mail server. (Failure sending mail.). )

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2010-10-24T19:55:11). Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 16.236.32.51:25). )

Resolution:

From the error message it is clear that it is a communication issue between SQL Server host and the Mail Exchange Server.

Problem was related to Exchange Server not allowing relaying from SQL Server.

Please engage Network Administrator to add SQL Server host IP Address to allow to send emails taking below steps, like

On Mail server > Exchange Server Manager > SMTP Connector Properties
> Access Properties > Access Tab > Relay button > Add IP Address of SQL Server

Also cross check if the Anti Virus (AV) program running on the SQL Server host. AV blocks services from sending on TCP-25. Adjust the AV software accordingly. Add DATABASEMAIL90.EXE to the list of innocent programs in your AV.

Tuesday, November 06, 2012

Query to find the Linked Servers with given remote login

It would be useful to cross check, when the Linked Server remote login password has changed.
In this case, linked server will wail due to wrong password. To fix this we have reset the correct password.

SELECT s.server_id,S.name,S.product,S.provider,l.remote_name,'Remote_Login_Modified_Dt'=l.modify_date,s.modify_date
FROM SYS.SERVERS S
INNER JOIN SYS.LINKED_LOGINS L
ON s.server_id = L.server_id
WHERE S.server_id > 0
AND S.product ='SQL Server'
AND L.remote_name ='sa'

Returning the parameters for a specified stored procedure or function

Before you run the following query, replace <database_name> and <schema_name.object_name> with valid names.

USE <database_name>;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
    ,o.name AS object_name
    ,o.type_desc
    ,p.parameter_id
    ,p.name AS parameter_name
    ,TYPE_NAME(p.user_type_id) AS parameter_type
    ,p.max_length
    ,p.precision
    ,p.scale
    ,p.is_output
FROM sys.objects AS o
INNER JOIN sys.parameters AS p ON o.object_id = p.object_id
WHERE o.object_id = OBJECT_ID('<schema_name.object_name>')
ORDER BY schema_name, o.object_name, p.parameter_id;
GO

Reference: http://msdn.microsoft.com/en-us/library/ms190324.aspx

List database objects modified in the last ‘N’ days

Before you run the following query, replace <database_name> and <n_days> with valid values.

USE <database_name>;
GO
SELECT name AS object_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.objects
WHERE modify_date > GETDATE() - <n_days>
ORDER BY modify_date;
GO

Source: http://msdn.microsoft.com/en-us/library/ms190324.aspx

Thursday, November 01, 2012

Script to check SQL Server Linked Server Status

It leverages on the system stored procedure sys.sp_testlinkedserver

SET NOCOUNT ON;
DECLARE @LinkName NVARCHAR (128), @retval INT, @msg varchar(300)
IF (SELECT COUNT (srvname) FROM sys.sysservers WHERE srvname <> @@SERVERNAME) = 0 RETURN

DECLARE srvname INSENSITIVE CURSOR FOR
        (SELECT srvname FROM sys.sysservers WHERE srvname <> @@SERVERNAME) FOR READ ONLY
OPEN srvname
    FETCH NEXT FROM srvname INTO @LinkName
WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        EXEC @retval = sys.sp_testlinkedserver @LinkName
    END TRY

    BEGIN CATCH
        IF @LinkName IS NULL RETURN
        SET @retval = SIGN(@@ERROR)
        IF @retval <> 0
        BEGIN
          SET @msg = 'Unable to connect to the Linked server : ' + @LinkName
          RAISERROR (@msg, 16, 2 )
        END
    END CATCH
    FETCH NEXT FROM srvname INTO @LinkName

END

CLOSE srvname
DEALLOCATE srvname

Wednesday, October 03, 2012

Failed to decrypt protected XML node "PassWord" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available

SSIS Package – SQL Agent Job Error:

Executed as user: PRODUCTION\SQLSrvr. ...te Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:08:22 PM Error: 2012-07-25 22:08:23.31 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "PassWord" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2012-07-25 22:08:23.32 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "PassWord" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error DTExec: The package execution returned DTSER_SUCCESS (0). Started: 10:08:22 PM Finished: 10:. The step succeeded.

Solution:

If you are using SSIS 2005 - you need to save the packages as Do Not save sensitive - which you are doing.

When you import the package into the SSIS system you need to make sure you alter the package protection level there to do not save sensitive too. In SQL Server 2008 and above this is the default.

Reference: http://support.microsoft.com/kb/918760

Tuesday, September 25, 2012

How to find on which node SQL Server instance was running before restart?

In situation, where SQL Server instance running on a node is restarted usually after Windows Operating System patching or for some other reason, there may be need to find to which node the SQL Server instance was running on before.

Here is the solution for this;

By default whenever SQL Server instance restarted on Clustered Environment, on SQL Server Error Log it will post the message below;

The NETBIOS name of the local node that is running the server is 'ClusterNode1'.

So, making use of the xp_ReadErrorLog we can query the ErrorLog and find the details.

-- To know the list available Error log files
exec xp_enumerrorlogs 1

-- Search the available error log files
exec xp_ReadErrorLog 0, 1 , 'NETBIOS name of the local node'
exec xp_ReadErrorLog 1, 1 , 'NETBIOS name of the local node'
exec xp_ReadErrorLog 2, 1 , 'NETBIOS name of the local node'
exec xp_ReadErrorLog 3, 1 , 'NETBIOS name of the local node'
exec xp_ReadErrorLog 4, 1 , 'NETBIOS name of the local node'
exec xp_ReadErrorLog 5, 1 , 'NETBIOS name of the local node'

Thursday, September 06, 2012

How to connect to Oracle Database from SQL Server instance and query the Oracle Database?

Linked Server is the solution for this.
A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created, distributed queries can be run against this server, and queries can join tables from more than one data source. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed.

A linked server definition specifies the following objects:

· An OLE DB provider : is a DLL that manages and interacts with a specific data source

· An OLE DB data source : identifies the specific database that can be accessed through OLE DB

Basics of Linked Server Configuration:

Advantages of Linked Server:

· The ability to access data from outside of SQL Server.

· The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.

· The ability to address diverse data sources similarly.

Pre-requisite

· Install Oracle Client

· Configure tnsnames.ora

Example

STG21.SIVA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = oraDBsrvr.siva.com)(PORT = 60000))

)

(CONNECT_DATA =

(SERVICE_NAME = stg01.siva)

)

Validate tnsnames.ora entry

On Command Prompt

Tnsping STG21.SIVA

Notes:

Here we are going to use Microsoft ODBC for Oracle as the OLE DB Provider.
The STG21.SIVA is going to be OLE DB Data Source pointing to the Oracle Database STG01.SIVA

Open up ODBC