SQL Server FAQ : Single line Answers

Log Shipping: Log Shipping provides a means to maintain a secondary server on an automated basis using a chain of transaction log backups.

Replication: Distribute data from Master database to one or more Secondary databases.

Data Mining: Making predictions about data.

Full Text Indexes: Index that give ability to query large volumes of unstructured data rapidly.

Clustering: To protect system against hardware failures.

Trouble Shooting tools:

Dynamic Management Views (DMV) and Functions (DMF)
System Monitor
SQL Server Profiler
Database Tuning Advisor

Clustered Index :A clustered index is an index where the leaf level of the index contains the actual data rows of the table

Heap: A table that has no index is referred to as a heap

Difference Between Clustered Index and Heap:
A clustered index has the data stored logically in the order of the index key, a heap has no ordering of rows or pages

Difference Between Len () and DataLength ():

Len() does not work on text, N text, image data types. But DATALENGTH() does work

ACID stands for Atomicity, Consistency, Isolation, and Durability

Transaction : A smallest unit of work in database

Lock Modes: The way in which a lock shares, or does not share records it is currently working on, are called Lock Modes

Index :An index is a structure within SQL that is used to quickly locate specific rows within a table

Deadlock: Itrefers to the condition in which one resource is waiting on the action of a second, while that second action is waiting on the first

Index key : An index is defined on one or more columns

Locks: The SQL default method of controlling data integrity.

Transactions: TSQL's smallest unit of work.

ACID: An acronym used to describe a perfect and consistent transaction.

Lock Modes: The way a lock shares or does not share records its working on.

Blocking: When a transaction must wait for a record.

Deadlocks: An error condition occurring when two locks are stuck in a circular loop.

Lock Hints: TSQL commands to override SQL's default locking behavior.

Row Versioning: The new ability in SQL 2005 to keep copies of data changes.

SQL Server Reporting Services (SSRS) : List of All Reports, Subscriptions, and Their Schedules

Its very common running and managing hundreds of reports as a BI developer. But its very hard to maintain all subscriptions as reporting services doesn't provide a sophisticated user interface. But as alternate we can do work with report server system tables. Even though this script is very easy and known by all I am just writing the Script here just like that. To find list of subscriptions and their schedules.

SELECT
C.Name,
C.[Path] ObjectPath,
U.UserName,
SB.[Description],
S.StartDate,
S.NextRunTime,
S.LastRunTime,
S.EndDate,
S.RecurrenceType,
S.LastRunStatus,
S.MinutesInterval,
S.DaysInterval,
S.WeeksInterval,
S.DaysOfWeek,
S.DaysOfMonth,
S.[Month],
S.MonthlyWeek
FROM ReportServer..Catalog c WITH (NOLOCK)
INNER JOIN ReportServer..ReportSchedule R ON R.ReportID = c.ItemID
INNER JOIN ReportServer..Schedule S WITH (NOLOCK) ON R.ScheduleID = S.ScheduleID
INNER JOIN ReportServer.dbo.Subscriptions SB ON SB.SubscriptionID = R.SubscriptionID
INNER JOIN ReportServer.dbo.Users U ON U.UserID = SB.OwnerID


Thanks,

How to do Performance Tuning a SSIS package? or How to troubleshoot a SSIS package?

Sometimes it may happen we are doing required trouble shooting to a SSIS package when it fails. But when we are at interview we cann't remember all the trouble shooting procedures we have done. So here I am hilighting all troubleshooting procedures only at high level , not drill down into detail level

Troubleshooting Package Development

Troubleshooting Design-time Validation Issues

 Work Offline when data sources are not available
 Configure the Delay Validation property on package elements that are not valid until run time
Troubleshooting Control Flow

 Set breakpoints on tasks, containers, and the package
 Use the debugging windows
 Review the information on the Progress tab

Troubleshooting Data Flow

 Test with only a subset of your data
 Use data viewers to monitor data as it moves through the data flow
 Configure error outputs on data flow components that support them
 Capture the count of rows processed
 Review the information on the Progress tab

Troubleshooting Scripts

• Set breakpoints in script in Script tasks
Troubleshooting Errors without a Description

Troubleshooting Package Execution

Catching and Handling Package Errors by Using Event Handlers
• Create an event handler for the On Error event
Troubleshooting Bad Data by Using Error Outputs
• Capture bad data by using error outputs
• Add friendly information to the error outputs
• Add the description of the error
• Add the name of the error column

Troubleshooting Package Execution by Using Logging

 Enable logging
 Select the package's Diagnostic event to troubleshoot provider issues
 Enhance the default logging output
1. Create a parent table that logs each execution of a package
2. Add auditing information to the data flow
3. Consider capturing row count data

Troubleshooting Package Execution by Using Debug Dump Files

Troubleshooting Run-time Validation Issues

Troubleshooting Run-time Permissions Issues

Troubleshooting 64-bit Issues

Troubleshooting the Integration Services Service

Troubleshooting Service Startup

Verify the startup type of the service

Troubleshooting Service Permissions

Finally Working with Debug Dump Files

Thanks,