Defination of IAM, GAM and SGAM

GAM: Global Allocation Map

Provides the information about the extents which are available for allocation.
Covers 4GB interval of Data.

SGAM: Shared Global Allocation Map


Provides the information about the mix extents which have at least 1 page available for allocation.
Covers 4GB interval of Data.

PFS:
PFS stands for Page Free Space, but the PFS page tracks much more than that. As well as GAM intervals, every database file is also split (conceptually) into PFS intervals. A PFS interval is 8088 pages, or about 64MB. A PFS page doesn’t have a bitmap – it has a byte-map, with one byte for each page in the PFS interval (not including itself).

Used to track how much free space is on pages
Covers 64MB interval of Data.

IAM: Index Allocation Map

Special internal page on data file that tracks all extent allocation for tables, indexes and partitions.
Basically, lets SQL Server know what extent belongs to what specific entity.
Covers 4GB interval of Data.

PFS,GAM and SGAM help SQL Server to determine where free space is and how much so that it can allocate it appropriately without having to scan all the pages.
IAM pages and Indexes help SQL Server fulfill queries

Findind when the statistics have updated.

select si.name as index_name, STATS_DATE(si.OBJECT_ID,index_id) as statsupdatedate, OBJECT_NAME(si.object_id) as tablename,sch.name as scheemaname
from sys.indexes si, sys.schemas sch,sys.tables st where si.object_id=st.object_id and st.schema_id=sch.schema_id
update statistics owner.objectname

How to remove a specific execution plan from the SQL Server query cache

Below query will provide you the list of query plans available in the instance:

select plan_handle, creation_time, last_execution_time, execution_count, qt.text
FROM 
   sys.dm_exec_query_stats qs
   CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt

from the output select the appropriate plan_handle based on query text. and run below query

DBCC FREEPROCCACHE (plan_handle_id_goes_here) 

Issue with Default language of a Login

Recently we have faced one issue with converting the datetime to varchar.
When we are trying to execute the query it is throwing the error like conversion failed. out of range etc...

The database was migrated from SQL Server 2008 to 2014.

In 2008 it was executing fine without any issues. But it is not working in SQL Server 2014.

We tried to modify the query but not worked. We tried all the possible ways.

In last we have compared the properties of the application login in 2008 and 2014. In 2008 default language for that login is British English. In 2014, when migrated we have did not changed it to British English. Once we changed that to British English problem solved and every thing working as expected.  :)

So now on wards we need to check the Login default language also while migrating.

Replication Issues

https://technet.microsoft.com/en-us/library/ms152532(v=sql.105).aspx

Data Is Not Being Delivered to Subscribers

·         The data is not being applied due to filtering, an agent issue, or another replication error.

·         The data is being deleted at the Subscriber after it has been applied.

Explanation

There are a number of possible causes for data not being delivered to Subscribers:

·         The table is filtered, and there are no changes to deliver to a given Subscriber.

·         One or more agents are not running or are failing with an error.

·         A transactional subscription was initialized without a snapshot, and changes have occurred on the Publisher since the publication was created.

·         Replication of stored procedure execution for a transactional publication produces different results at the Subscriber.

·         The INSERT stored procedure used by a transactional article includes a condition that is not met.

·         Data is deleted by a user, a replication script, or another application.

·         Data is deleted by a trigger, or a trigger includes a ROLLBACK statement.

https://technet.microsoft.com/en-us/library/ms151272(v=sql.105).aspx

Data at the Publisher and Subscriber Do Not Match

Data at the Publisher and Subscriber is considered non-convergent (in other words the data does not match) if:

·         There are a different number of rows at the Subscriber than at the Publisher, and the publication is not filtered. If the publication is filtered, it might be expected that the number of rows differs.

·         The data in one or more rows is different in content at the Publisher and Subscriber.

Explanation

Data at the Publisher and Subscriber can be non-convergent for a number of reasons:

·         Data is updated at a Subscriber that should be treated as read-only. The subscription database should be treated as read-only unless you are using merge replication, transactional replication with updatable subscriptions, or peer-to-peer transactional replication.

·         Triggers are used at the Subscriber. Triggers can modify data at the Subscriber and also prevent the data from being updated if the trigger issues a ROLLBACK.

·         Scripts are executed by replication at the Subscriber but not at the Publisher.

·         Replication of stored procedure execution for a transactional publication produces different results at the Subscriber.

·         Constraint violations or other issues prevent rows from being inserted, updated, or deleted at the Subscriber.

https://technet.microsoft.com/en-us/library/ms152522(v=sql.105).aspx

The System Has a Performance Issue

Replication performance can be measured along the following five dimensions:

·         Latency: the amount of time it takes for a data change to be propagated between nodes in a replication topology.

·         Throughput: the amount of replication activity (measured in commands delivered over a period of time) a system can sustain over time.

·         Concurrency: the number of replication processes that can operate on a system simultaneously.

·         Duration of synchronization: how long it takes a given synchronization to complete.

·         Resource consumption: hardware and network resources used as a result of replication processing.

Replication performance is affected by the following factors:

·         Server and network hardware

·         Database design

·         Distributor configuration

·         Publication design and options

·         Filter design and use

·         Subscription options

·         Snapshot options

·         Agent parameters

·         Maintenance

A Slow Network Is Causing Problems

 

Security Issues Are Preventing Data from Being Replicated

https://technet.microsoft.com/en-us/library/ms152519(v=sql.105).aspx

 

The password has changed for an account used by one or more replication agents.

An account used by a replication agent is no longer valid and must be changed.

A replication agent does not have sufficient permissions to connect to a computer in the topology or perform operations in a database.

The Snapshot Agent cannot write to the snapshot folder; the Distribution Agent or Merge Agent cannot read from the snapshot folder.

https://technet.microsoft.com/en-us/library/ms152544(v=sql.105).aspx

Running a Large Number of Agents Is Causing Memory Problems

In some cases, you might need to run a large number of replication agents simultaneously on a single computer. For example, if you are generating snapshots for a large number of publications or you are synchronizing a large number of push subscriptions at the Distributor. In these situations, the non-interactive desktop heap (a memory structure in Microsoft Windows) can run out of memory.

Question to new customer from SQL side

1. What is the environment.  Are there any Architecture diagrams available ?
2. No.Of   Preprod /UAT/Testing/PROD servers. Versions of SQL Server
3. In these what are the High critical, medium and low servers and databases. Also is there any DR structure available or planning to set up now.
4. Are service accounts configured independetly to each server/instance or one for domain. Password reset policy.
5. What is load on the servers, average requests on the database. Avg growth of the databases.
6. standard maintenance jobs scheduled on each SQL Server.
7. Important list of jobs running on the servers
8. How many SQL instances installed per Server.
9. Any specific standards for installing SQL Server(Pre and Post).  -- Client level
10. What is the storage structure.
11. Uptime of database High critical, medium and low servers.
12. Backup startegery for disaster recovery.
13. What is the prefered SQL Server edition.
14. Database monitoring mechanism. Is there any tool to monitor and how it works.
15. What is the Windows version, CPU and RAM configurations.
16. What are the high availability techniques are in use in current environment.
17. What level of permissions required for database users.
18.   Any Troubleshooting guide they follow?
19. IS there any inventory of the Database/Instances/Servers. Which database relates to which application
20.   What are the issues /tickets we get usually.
21. SSIS,RS,AS
22. Any SQL Server/OS up-gradation plans.

23. Is there any incoming data from other interfaces

24.  About Monitoring Tools and ticketing system.

SQL Server Intigration Services Connectivity Issue

I am trying to connect SSIS 2012 from management studio on my local system.

But I am receiving below error while connecting

TITLE: Connect to Server
------------------------------

Cannot connect to D-113069721.

------------------------------
ADDITIONAL INFORMATION:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

------------------------------

Connecting to the Integration Services service on the computer "D-113069721" failed with the following error: "Access is denied."

By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service. See the help topic for information on how to configure access to the service.


For help, click: http://go.microsoft.com/fwlink/?LinkId=220763

------------------------------

Connecting to the Integration Services service on the computer "D-113069721" failed with the following error: "Access is denied."

By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service. See the help topic for information on how to configure access to the service.


------------------------------
BUTTONS:

OK
------------------------------

I followed below method to over come this access issue:

Below is the source URL:

https://msdn.microsoft.com/en-us/library/hh213130.aspx

1. Run Dcomcnfg.exe. Dcomcnfg.exe provides a user interface for modifying certain settings in the registry.
2. In the Component Services dialog, expand the Component Services > Computers > My Computer > DCOM Config node.
3. Right-click Microsoft SQL Server Integration Services 11.0, and then click Properties.
4. On the Security tab, click Edit in the Launch and Activation Permissions area.
5. Add users and assign appropriate permissions, and then click Ok.
6. Repeat steps 4 - 5 for Access Permissions.
7. Restart SQL Server Management Studio.
8. Restart the Integration Services Service.
   
   
   Same process has to be followed all the options in security tab(Access Permissions and Configuration Permissions) .
   
   
   This resolved my connectivity issue.

Shrink all databases log file at a time

drop table #f
go
create table #f (name sysname, fileid int, filename sysname, filegroup sysname null,
size sysname, maxsize sysname, growth sysname, usage sysname)
go
exec sp_MSforeachdb 'declare @s varchar(MAX); use [?];
truncate table #f; INSERT #f (name, fileid, filename, filegroup, size, maxsize, growth, usage )
exec sp_helpfile
--select * from #f

 select @s = ''use [?]; DBCC SHRINKFILE ('' + name +'',0,truncateonly )'' from #f where usage = ''log only''
print @s'

Trace 3604 and 3605

Some of the DBCC commands wont show the total results when we execute. like DBCC PAGE.

If we execute DBCC PAGE, SQL Server show the result as DBCC execution completed.

So If you want to results to Display in result pane, need to enable trace flag 3604.

DBCC TRACEON(3604)

If you want to results to Display in error Log, need to enable trace flag 3605.

DBCC TRACEON(3605)

DBCC FREEZE_IO(DBNAME) AND DBCC THAW_IO(DBNAME)

These are undocumented DBCC commands and not supported by Microsft. So please be careful while using.

If we want to suspend write operations on a DB normally we will put that DB in ReadOnly Mode.

We have one more option using undocumented commands:

DBCC FREEZE_IO(DBNAME)   ---  Suspends all the write operations on the specified database.

DBCC THAW_IO(DBNAME)   --- Resumes the database for write operations.

To check Authentication level of SQL Server

SELECT SERVERPROPERTY ('IsIntegratedSecurityOnly')

0--- Is Mixed mode
1--- is WIndows Authentication

Reading Default Tracefile

Below are examples for Find Permission Changes In The Default Trace

Type1:

declare @tracefile varchar(100)

set @tracefile=(select * from ::fn_trace_getinfo(default) where traceid=1 and property=2 )

select * from sys.trace_events

select * from ::fn_trace_gettable('E:\MSSQL12.VAS1P\MSSQL\Log\log_15.trc',default) where TextData like ('%alter%')

Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050). The step failed

As part of one of requirements we have to configure a job in which we need to use DBMail part.

From the DBMail we need to send a query results. 

Query is like below:

exec sp_send_dbmail @profile_name='SQL Server Alerts',

@recipients = 'mailed@mailed.com;',

@subject ='Job failures on server',

@query='select [Job Name],[last start date] from jobstatus where [Job Name] NOT LIKE (''DBA -%'') and [last run message] like ''the job faile%''',

@body='Jobs Failure list';

But it is failed With below Error:

failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050).  The step failed.

When I googled in some place I found one work around for this:

We need to mention the Table name as:

Dbname.owner.tablename

Here in my scenario it is:

msdb.dbo.jobstatus 

Query looks like below:

@query='select [Job Name],[last start date] from msdb.dbo.jobstatus where [Job Name] NOT LIKE (''DBA -%'') and [last run message] like ''the job faile%'''


This work around was very useful but at one of the server, again same error was thrown.
After researching and googling in one of the article mentioned to run the profiler, which resolved the issue.

SQL Server agent service accounts were mapped to local system account.

And the query was running by NT Authority/System user which have only public role, nothing else.

So I have given DBOwner privilages on MSDB database to that account.

Immediately issue got resolved.

Finding shared path information from Command Prmopt

From Command Prompt:

net share

Finding out SQL Server Service account using T-SQL

select * from sys.dm_server_services

Finding Sysadmin on a SQL Server Instance

T-SQL

SELECT NAME, isntname FROM SYSLOGINS
WHERE sysadmin = 1 and hasaccess = 1

EXEC sp_helpsrvrolemember 'sysadmin'

Finding the table name and column name where a specific string has been used

Finding the table name and column name where a specific string has been used:

----- Updated One ----

Set NOCOUNT ON
Declare @count bigint
declare @setvalue bigint
declare @countvalue varchar(max)
DECLARE @TProduct TABLE
(
count bigint identity (1,1),
query varchar(max)
) 
--Insert data to Table variable @Product 
INSERT INTO @TProduct(query)
select 'select @countvalue=count (['+COLUMN_NAME+']) from ['+TABLE_NAME+'] where ['+COLUMN_NAME+'] like ''%@%.com''' from INFORMATION_SCHEMA.COLUMNS isc, sysobjects so where isc.TABLE_NAME=so.name and so.xtype='U' --where COLUMN_NAME like '%mail%'
--select 'select distinct '+COLUMN_NAME+' from '+TABLE_NAME+' where '+COLUMN_NAME+' like ''%.com''' from INFORMATION_SCHEMA.COLUMNS --where COLUMN_NAME like '%mail%'
--Select data
--exec (@TProduct)
--Select * from @TProduct
set @setvalue=1
set @count=(select count(*) from @TProduct)
--select * from @TProduct
--print @count
while (@setvalue <=@count )
begin
Declare @query nvarchar(500)
set @query=(SELECT query FROM @TProduct where count=@setvalue)
--print @query
exec sp_executesql @query, N'@countvalue int out', @countvalue out
--print @countvalue
--declare @queryres table (Queryres bigint)
--insert @queryres exec (@query)
--select * from @queryres
if(@countvalue > 0)
print @query
--delete from @queryres
set @setvalue=@setvalue+1

end

----- Updated One ----

Set NOCOUNT ON
Declare @count bigint
declare @setvalue bigint
DECLARE @TProduct TABLE
(
count bigint identity (1,1),
query varchar(max)
) 
--Insert data to Table variable @Product 
INSERT INTO @TProduct(query)
select 'select count ('+COLUMN_NAME+') from ['+TABLE_NAME+'] where ['+COLUMN_NAME+'] like ''%@%.com''' from INFORMATION_SCHEMA.COLUMNS isc, sysobjects so where isc.TABLE_NAME=so.name and so.xtype='U' --where COLUMN_NAME like '%mail%'
--select 'select distinct '+COLUMN_NAME+' from '+TABLE_NAME+' where '+COLUMN_NAME+' like ''%.com''' from INFORMATION_SCHEMA.COLUMNS --where COLUMN_NAME like '%mail%'
--Select data
--exec (@TProduct)
--Select * from @TProduct
set @setvalue=1
set @count=(select count(*) from @TProduct)
--select * from @TProduct
--print @count
while (@setvalue <=@count )
begin
Declare @query varchar(max)
set @query=(SELECT query FROM @TProduct where count=@setvalue)
--print @query
declare @queryres table (Queryres bigint)
insert @queryres exec (@query)
--select * from @queryres
if((select queryres from @queryres) > 0)
print @query
delete from @queryres
set @setvalue=@setvalue+1

end



----- Updated One ----
Below script checks the count of the value we passed in all tables and all columns. If count is greater than zero it will display the select query on that column and table.

Set NOCOUNT ON
DECLARE @TProduct TABLE
(
query varchar(max)
)
--Insert data to Table variable @Product
INSERT INTO @TProduct(query)
select 'select count ('+COLUMN_NAME+') from ['+TABLE_NAME+'] where ['+COLUMN_NAME+'] like ''String value''' from INFORMATION_SCHEMA.COLUMNS isc, sysobjects so where isc.TABLE_NAME=so.name and so.xtype='U' --where COLUMN_NAME like '%mail%'
--select 'select distinct '+COLUMN_NAME+' from '+TABLE_NAME+' where '+COLUMN_NAME+' like ''%.com''' from INFORMATION_SCHEMA.COLUMNS --where COLUMN_NAME like '%mail%'
--Select data
--exec (@TProduct)
--Select * from @TProduct
Declare @query varchar(max)
declare @queryres table (Queryres bigint)
DECLARE db_cursor CURSOR FOR
SELECT query
FROM @TProduct
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @query
WHILE @@FETCH_STATUS = 0
BEGIN
--print (@query)
--exec (@query)
insert @queryres exec (@query)
--select * from @queryres
if((select queryres from @queryres) > 0)
print @query
Delete from @queryres
FETCH NEXT FROM db_cursor INTO @query
END
CLOSE db_cursor
DEALLOCATE db_cursor


go


---- Previous Script ------

DECLARE @TProduct TABLE
(
query varchar(max)
)
--Insert data to Table variable @Product
INSERT INTO @TProduct(query)
select 'select ['+COLUMN_NAME+'] from ['+TABLE_NAME+'] where ['+COLUMN_NAME+'] like ''string value''' from INFORMATION_SCHEMA.COLUMNS isc, sysobjects so where isc.TABLE_NAME=so.name and so.xtype='U' --where COLUMN_NAME like '%mail%'
--select 'select distinct '+COLUMN_NAME+' from '+TABLE_NAME+' where '+COLUMN_NAME+' like ''%.com''' from INFORMATION_SCHEMA.COLUMNS --where COLUMN_NAME like '%mail%'
--Select data
--exec (@TProduct)
--Select * from @TProduct
Declare @query varchar(max)
declare @queryres bigint
DECLARE db_cursor CURSOR FOR
SELECT query
FROM @TProduct
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @query
WHILE @@FETCH_STATUS = 0
BEGIN
print (@query)
exec (@query)
FETCH NEXT FROM db_cursor INTO @query
END
CLOSE db_cursor
DEALLOCATE db_cursor


go

Retrieving Object Defination

Using below methods we can retrieve object defination of particular database.


Exec Sp_help text 'Object name'

OBJECT_DEFINITION ( object_id )

select so.name,definition from sys.sql_modules sm, sys.objects so where sm.object_id=so.object_id

INFORMATION_SCHEMA.ROUTINES:

Returns one row for each stored procedure and function that can be accessed by the current user in the current database. The columns that describe the return value apply only to functions. For stored procedures, these columns will be NULL.

What's the strongest HASH SQL Server 2014

Source: SQL Server Central QOD Jan st 2016.

The HASHBYTES function in SQL Server returns a hash of a string. In SQL Server 2014, the strongest algorithm is the SHA2 algorithm at 512 bytes.

Ref: HASHBYTES - https://msdn.microsoft.com/en-us/library/ms174415.aspx