New Database Request form

Name

Owner

Collation

Database

• Initial size
• Growth increment
• Max size
• Drive\Volume

Transaction log

• Initial size
• Growth increment
• Max size
• Drive\Volume

Security

• Initial security needed

Backup schedule

• Days of week
• Time of day
• Type (Full, Differential, Transaction Log)
• Location (Volume or Drive or Tape)

High Availability related

• Include in log shipping
• Include in database mirroring

Business related data

• Purpose
• Related application
• Business Owner
• Priority
• User location
• Operating hours
• Maintenance hours
• Development Point of Contact
• DBA Point of Contact

Reference: http://sqlserverquestions.mssqltips.com/7211/new-database-request-form/

Script to find Duplicate Values in SQL Server

Find Duplicate Values in SQL Server
SELECT COLUMN_NAME, COUNT(COLUMN_NAME) AS NumOccurrences
FROM  [TABLE_NAME]GROUP

BY COLUMN_NAME
HAVING ( COUNT(COLUMN_NAME) > 1 )

Peer to Peer Transactional Replication

"Microsoft.SqlServer.ConnectionInfo
Cannot insert the value NULL into column 'article_id'.table'distribution.dbo.MSrepl_commands'; the column does not allow NULLLS. INSERT fails. The subscription does not exist. The subscription could not be found. (Microsoft SQL Server, Error 515)


Solution: Dont back and restore database on Second node without creating Publicatiion on first Node,
Normally you should back up after creating the publication on the first node.

It solved my issue.

Error[21355] SQL-DMO: The new DB file size must be larger than the current size

Error[21355] SQL-DMO: The new DB file size must be larger than the current size.

Reduce the initial size of mdf file in sql server.



-- get the file#s / names of all the db's files
USE pubs
EXEC sp_helpfile



--Then choose the file# or filename you want to shrink. For example, to shrink the primary file to 2GB:


DBCC SHRINKFILE ( 1, 20000)

Finding the Port Number for a particular SQL Server Instance

DECLARE @tcp_port NVARCHAR(5)

EXEC xp_regread @rootkey = ‘HKEY_LOCAL_MACHINE’
 ,@key = ‘SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP’
 ,@value_name = ‘TcpPort’
 ,@value = @tcp_port OUTPUT

SELECT @tcp_port

Free ebook: Introducing Microsoft SQL Server 2012

Free ebook: Introducing Microsoft SQL Server 2012

http://blogs.msdn.com/b/microsoft_press/archive/2012/03/15/free-ebook-introducing-microsoft-sql-server-2012.aspx

SSIS Package in SQL Server Agent Job Fails

Error:

Argument "xyz" for option "connection" is not valid.  The command line parameters are invalid.  The step failed.

Solution:

·         If your SQL Server is 64 bit

·         In SQL Server JOB-->Properties-->Steps--->

·         In the Execution Option Tab -- Check "Use 32 bit runtime"

·         In the Data Sources Tab -- Uncheck the checkboxes of the connection managers if already checked.

·         Schedule the Job and run it.