aiDeeva.com

Category: SQL Server 2008

  • Useful queries while troubleshooting Database Mirroring

    Useful queries while troubleshooting Database Mirroring

    Query to check the associated ports with DB Mirroring

    SELECT type_desc, port FROM sys.tcp_endpoints;
GO

    Query to check the state of the DB Mirroring

    SELECT state_desc FROM sys.database_mirroring_endpoints
GO

    Query to check the service account connect permission on the DB Mirror endpoints

    SELECT 'Metadata Check';
SELECT EndPnt.name, SvrPerm.STATE,
   CONVERT(nvarchar(38), suser_name(SvrPerm.grantor_principal_id))
    AS GRANTOR,
   SvrPerm.TYPE AS PERMISSION,
   CONVERT(nvarchar(46),suser_name(SvrPerm.grantee_principal_id))
    AS GRANTEE
   FROM sys.server_permissions SvrPerm, sys.endpoints EndPnt
   WHERE SvrPerm.major_id = EndPnt.endpoint_id
   ORDER BY Permission, grantor, grantee;
GO

    Query to check the DB Mirror timeout and resetting the DB Mirror timeout

    SELECT mirroring_connection_timeout
FROM
sys.database_mirroring
GO

ALTER DATABASE SQLDBPOOL SET PARTNER TIMEOUT 15
GO

  • How to configure SQL to listen on Multiple Ports?

    Problem
    Recently I came across a situation where an existing production SQL Server default instance was configured to use static TCP/IP port 48030 and the default 1433 port was disabled. A number of database applications are hosted on the same default SQL instance and these applications are connecting to SQL Server through port 48030. As per a new business requirement we need to host a new application database on the same SQL instance, but the application is unable to connect to the SQL instance because it us hard corded to use the default port 1433. In this tip we walk through how to configure a SQL instance to listen on multiple TCP/IP ports.

    Solution
    For solution please check http://www.mssqltips.com/sqlservertip/2493/configuring-sql-server-to-use-multiple-ports/ URL.

  • Script to Get Available and Free Disk Space for SQL Server

    Problem
    Often we face the situation where we need to check the total disk space and available disk space for both physical and LUN/Mount drives. The extended stored procedure XP_FixedDrives is unable to help us in this scenario, so we have to log into the machine to check the total and free disk space for each physical/LUN/mount drive. In this tip, I show how this can be done using PowerShell.

    Solution
    http://www.mssqltips.com/tip.asp?tip=2444

  • Difference between temporary table and table variable

    Temporary TablesThere are two types of temporary tables:

    Local Temporary Table: Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server

    You can create local temporary table adding # sign again the table name

    Global Temporary Table: Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

    You can create global temporary table adding ## sign again the table name

    What are the things you can do with the temporary tables?
    -Add/drop constraints except foreign key
    -You can perform DDL statements (Alter, Drop)
    -Create clustered and non-clustered indexes
    -Use identity columns
    -Use it in transaction and it support transaction
    -Perform any DML operations (SELECT, INSERT, UPDATE, DELETE)
    -Create the table with same name using different session; make sure constraint name must be different in the table.

    -- Adding the constraint primary key and unique key
-- constraint will create the cluster and non-cluster index
create table #temptbl
(id int identity (100,1) Primary key,
data varchar(20) constraint UK Unique
)

insert into #temptbl values ('Jugal')
insert into #temptbl values ('Jugal1')
insert into #temptbl values ('Jugal2')

select * from #temptbl
sp_help #temptbl

--Adding column to existing temporary table
alter table #temptbl
Add Name varchar(20) null

-- Modifying column
alter table #temptbl
alter column name varchar(30)

--adding index
create nonclustered index UK2 on #temptbl (name)

--checking indexes
sp_helpindex #temptbl

-- Supports transaction
begin tran
insert into #temptbl values('sqldbpool','sqldbpool')
rollback

select * from #temptbl

--Checking for the foreign key
create table #temptbl1
(
id int constraint FK1 references #temptbl1(id),
value int
)
--Skipping FOREIGN KEY constraint 'FK1' definition for temporary table. FOREIGN KEY constraints are not enforced on local or global temporary tables.

insert into #temptbl1 values(2000,10)

--DML Opertaions
select * from #temptbl

update #temptbl set data = 'SQLDBPool'
where id = 100

select * from #temptbl

delete from #temptbl where id = 101

select * from #temptbl
--dropping temporary table
drop table #temptbl

    Table variableThe syntax for creating table variables is quite similar to creating either regular or temporary tables. The only differences involve a naming convention unique to variables in general, and the need to declare the table variable as you would any other local variable in Transact SQL. Life span of the table variable is limited to life of the transaction. You can only create the cluster index on table variable.

    -- creating table variable
declare @var table 
(id int identity(100,1) primary key, 
 data varchar(20) default 'hi'
 )

--Checking DML Statement --working fine
insert into @var values('jugal')
insert into @var values('sqldbpool')
delete from @var where id = 100
update @var set data = 'sqldbpool-1' where id = 101
select * from @var


-- Checking transaction support/doesn't support transaction
begin tran
declare @var table 
(id int identity(100,1) primary key, 
 data varchar(20) default 'hi'
 )

insert into @var values('jugal')
insert into @var values('sqldbpool')
insert into @var values (DEFAULT)
delete from @var where id = 100
update @var set data = 'sqldbpool-1' where id = 101

rollback
select * from @var
-- Checking alter failed/doesn't support DDL
alter table @var
alter column data varchar(30)

alter table @var
Add Name varchar(20) null

    Similarities between temporary tables and table variable:– Both are created in tempdb
    – You can create constraint like primary key, default and check on both but the table variable has certain limitation for the default and check constrain where you can not use UDF
    – Clustered indexes can be created on table variables and temporary tables
    – Both are logged in the transaction log but the tempDB recovery model is SIMPLE, log will be truncated once the trasaction get complete.
    Just as with temp and regular tables, users can perform all Data Modification Language (DML) queries against a table variable: SELECT, INSERT, UPDATE, and DELETE.

    Differences
    – You can not create non-cluster index and statistics on table variable but you can create it on temporary table.
    – You can not use DDL statement on table variable but you can use it on temporary table.
    – Table variable doesn’t support transaction wheras temporary table supports.

  • Restoring a SQLServer database that uses Change Data Capture

    Problem
    When restoring a database that uses Change Data Capture (CDC), restoring a backup works differently depending on where the database is restored. In this tip we take a look at different scenarios when restoring a database when CDC is enabled.

    Solution
    For solution, please check my new article on MSSQLTips.com

    http://mssqltips.com/tip.asp?tip=2421