SQL Server Quest

What is the performance optimization available for the SQL server production server?

2022-12-27T02:24:00.003-08:00

here are several performance optimization options available for a SQL Server production server. Here are a few examples:

Indexing: Indexes can be used to improve the performance of queries by providing a faster way to retrieve data from tables.

Query optimization: You can use tools such as the SQL Server Query Optimizer to analyze and optimize the performance of queries.

Partitioning: You can use partitioning to divide a large table into smaller, more manageable pieces, which can improve the performance of queries and maintenance tasks.

Data compression: You can use data compression to reduce the size of data in tables and indexes, which can improve performance by reducing the amount of I/O required to access the data.

Hardware upgrades: Upgrading the hardware on the server, such as the processor, memory, and storage, can improve performance.

Load balancing: You can use load balancing to distribute the workload across multiple servers, which can improve performance.

Monitoring and tuning: Regularly monitoring the performance of the server and tuning the configuration and settings as needed can help to optimize performance.

By implementing these optimization options, you can improve the performance of a SQL Server production server.

What are performance options in azure data factory copy activity ?

2022-12-26T23:14:00.004-08:00

In Azure Data Factory, the Copy Activity is used to copy data from a source data store to a destination data store. There are several options that you can use to optimize the performance of the Copy Activity. These options include:

Data partitioning: You can use data partitioning to divide the data into smaller chunks, which can be processed in parallel to improve performance.

Parallel copy: You can use parallel copy to copy data from the source to the destination in parallel, which can improve performance.

Batch size: You can specify the batch size for the copy operation, which determines the number of rows that are processed in each batch. Increasing the batch size can improve performance, but it can also increase the risk of errors.

Concurrency: You can specify the number of concurrent copy operations that can be performed. Increasing the concurrency can improve performance, but it can also increase the risk of errors.

Data compression: You can use data compression to reduce the size of the data being transferred, which can improve performance.

Data caching: You can use data caching to store a copy of the data in memory, which can improve performance by reducing the number of read operations required.

Custom connectors: You can use custom connectors to optimize the copy operation for specific data stores or scenarios.

By using these options, you can optimize the performance of the Copy Activity in Azure Data Factory.

Azure Data Architect Interview Questions

2022-05-02T19:40:00.002-07:00

Py4JJavaError : An error occurred while calling o536.json. : Operation failed: "This request is not authorized to perform this operation.", 403, HEAD, https://storageaccount.dfs.core.windows.net/container/?upn=false&action=getAccessControl&timeout=90

2022-05-02T05:19:00.002-07:00

Py4JJavaError : An error occurred while calling o536.json. : Operation failed: "This request is not authorized to perform this operation.", 403, HEAD, https://storageaccount.dfs.core.windows.net/container/?upn=false&action=getAccessControl&timeout=90

If we try to access data from ADLS gen2 without the "Storage Blob Data Contributor" role on the storage account, they will receive the error message: Operation failed: "This request is not authorized to perform this operation.",403.

Once the storage account is created, select Access control (IAM) from the left navigation. Then assign the following roles or ensure they are already assigned. Assign yourself to the Storage Blob Data Owner role on the Storage Account.

LINQ to Entities does not recognize the method 'System.String GetUserId(System.Security.Principal.IIdentity)' method, and this method cannot be translated into a store expression. Source=EntityFramework

2022-05-02T04:50:00.002-07:00

This exception occurs because LINQ to SQL query internally builds an SQL query and executes it on the database directly and hence when one tries to use the ToString method which is a .Net data type conversion method and cannot be used for an SQL Query.

Solution to this problem is to use the StringConvert method of the SqlFunctions class for converting the Integer value to String.

How can I clean up the SSISDB?

2022-05-02T04:26:00.006-07:00

Below are the Queries to clean SSIS DB. These are the internal tables that can be deleted.

USE SSISDB
  GO
  DELETE FROM [internal].[executions]
  GO
  DELETE FROM [internal].[executable_statistics]
  GO
  DELETE FROM [internal].[execution_component_phases]
  GO
  DELETE FROM [internal].[execution_data_statistics]
  GO
  DELETE FROM [internal].[execution_data_taps]
  GO
  DELETE FROM [internal].[execution_parameter_values]
  GO
  DELETE FROM [internal].[execution_property_override_values]
  GO
  DELETE FROM [internal].[extended_operation_info]
  GO
  DELETE FROM [internal].[operation_messages]
  GO
  DELETE FROM [internal].[event_messages]
  GO
  DELETE FROM [internal].[event_message_context]
  GO
  DELETE FROM [internal].[operation_os_sys_info]
  GO
  DELETE FROM [internal].[operation_permissions]
  GO
  DELETE FROM [internal].[validations]
  GO

Azure Data Factory Interview Questions and Answers

2021-02-15T21:53:00.005-08:00

Azure Data Factory is an Orchestration tool for data transfer pipelines on the Azure portal. These pipelines are having multiple components and having an understanding of each component is quite important for showcasing the knowledge of azure data factory.

Below is the list of questions that must know to test one's knowledge test the azure data factory understanding.

These can help interviewers and interviewees both to understand the tool’s functionality.
What are the different components or Activities you have used in the azure data factory?
Explain the difference between the Dataset and Linked Service in Data Factory?
What is Integration Runtime in Azure data factory?
What are the different Integration runtimes in Azure Data Factory?
How does the if activity in the Azure data factory works? Explain with an example?
What if For Each Activity and when to use For Each Activity?
Have you used Lookup Activity in Azure Data Factory?
What are linked Services and How to create one?
What are datasets in Azure Data Factory? Please Explain?
How to declare parameters in Azure Data Factory
How to schedule a Pipeline in the Azure Data factory?
What is SSIS integration run time? How to configure it.
Have you used azure custom activity and what is its use?
How to handle fault tolerance in Azure Data Factory?
How do make pipeline parameters dynamically?

Tags: Azure Data Factory Interview Questions, ADF Interview Questions, ADF Question, ADF Questions and Answers, Azure Data factory Questions.

How to generate calendar in SQL Server?

2019-01-12T01:16:00.005-08:00

How to generate calendar dates between two given dates. This post will show the query to create a calendar table.
When there is any requirement to generate a calendar you can generate it programmatically and push it into a table.

CREATE TABLE CALENDAR(VDATE DATE)

DECLARE @MinDate DATE = '20181010',

@MaxDate DATE = '20191010';

INSERT INTO CALENDAR

SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)

Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)

FROM sys.all_objects a

CROSS JOIN sys.all_objects b;

Tags: Generate dates between two dates; calendar dates SQL server; SQL calendar ; all dates;

Error in rJava::.jcall(driver@jdrv, "Ljava/sql/Connection;", "connect", : java.sql.SQLException: Network error IOException: Connection refused: connect

2018-05-30T20:58:00.004-07:00

Error in rJava::.jcall(driver@jdrv, "Ljava/sql/Connection;", "connect", :

java.sql.SQLException: Network error IOException: Connection refused: connect

Why do I get java.sql.SQLException: "Network error IOException: Connection refused: connect" when trying to get a connection?

The "Connection refused" exception is thrown by jTDS when it is unable to connect to the server. There may be a number of reasons why this could happen:
The server name is misspelled or the port number is incorrect.
SQL Server is not configured to use TCP/IP. Either enable TCP/IP from SQL Server's Network Utility app or have jTDS connect via named pipes (see the URL format for information on how to do this).
There is a firewall blocking port 1433 on the server.

To check whether TCP/IP is enabled and the port is not blocked you can use "telnet <server_host> 1433". Until telnet doesn't connect, jTDS won't either. If you can't figure out why ask your network administrator for help.

Wake up and Learn Big Data

2017-09-08T20:46:00.003-07:00

Wake up and Learn BIG DATA:

I am going to start a tutorial series on big data for the SQL developers. This is the time you should learn big data and start improving your skills parallel to survive in current market scenario.

In this tutorial, I will be guide you through apache Hadoop installation, development and other big data technologies.

In current scenario, the companies are considering new technologies for their new solutions which will create new jobs so you need to consider these learnings for you profile improvement.

Course content:

1. What is big data:

2. Why Big Data is required.

3. Different big data technologies.

4. Back ground of apache Hadoop.

5. Prerequisite for installing Apache Hadoop.

6. Installing ubuntu with dual boot system.

7. Understanding basis architecture of Apache Hadoop.

8. Setting up machine for Hadoop.

9. Hadoop single node cluster installation.

What are the types of database schema in data warehouse ?

2017-06-01T05:53:00.005-07:00

Star Schema:

A star schema is the one in which a central fact table is sourrounded by denormalized dimensional tables. A star schema can be simple or complex. A simple star schema consists of one fact table where as a complex star schema have more than one fact table.

Snow Flake Schema:
A snow flake schema is an enhancement of star schema by adding additional dimensions. Snow flake schema are useful when there are low cardinality attributes in the dimensions.

Galaxy Schema:

Galaxy schema contains many fact tables with some common dimensions (conformed dimensions). This schema is a combination of many data marts.

Fact Constellation Schema:
The dimensions in this schema are segregated into independent dimensions based on the levels of hierarchy. For example, if geography has five levels of hierarchy like teritary, region, country, state and city; constellation schema would have five dimensions instead of one.

What are the limitations in SSRS on SQL Server express edition?

2016-08-15T03:12:00.002-07:00

Microsoft offers reporting services free as part of SQL Server Express with Advance Services edition. But it has the following limitations:

Management Studio cannot be used to administer report server
Report Models will not be available
Report Builder is not available
Caching, History and Delivery of Report is not available.
SQL Server agent is not available
No scheduling is possible
Remote server database is not available for Report Data Source (Local SQL Server is a only option,)
We cannot store the report server database on a remote server (it has to be local only)
Reports can be rendered only in Excel, PDF, Image formats only
Reporting Services will not be able to use more than 1 GB of RAM
No Subscriptions (Standard and Data Driven) can be made
Can not be integrated with Share Point
Can not implement Role based security
Only named instances is supported

Scale-out Report Servers will not be available

What is Statistic?

2016-06-01T09:56:00.003-07:00

The science that deals with the collection, classification, analysis, and interpretation of numerical facts or data, and that, by use of mathematical theories of probability, impose order and regularity on aggregates of more or less disparate elements.

There are 2 parts of definition

the collection, classification, analysis, and interpretation of numerical facts or data

the use of probability theory to impose order on aggregates of data

In simpler words, statistics deals with summarizing information about data in a meaningful and relevant way.

Statistics is also using data to predict things that are unknown. We can be 95% confident more people will vote for Candidate A than B.

Statistical analysis is like solving mysteries with data. We start with questions and attempt to answer them with data instead of our intuition. When we assemble enough data we make predictions.

With predictions, there's always a chance that we'll be wrong. Much of statistics is understanding what we know from data we do have, making our best prediction about data we don't have, and clearly understanding the chance that we're wrong and quantifying that

Different types of dimensions

2016-04-06T03:30:00.002-07:00

Conformed Dimension

A conformed dimension is a set of data attributes that have been physically implemented in multiple database tables using the same structure, attributes, domain values, definitions and concepts in each implementation. A conformed dimension cuts across many facts.

Example: Date Dimension table can be shared by multiple fact tables. Date Dimension table connected to sales fact is identical to the one connected to inventory facts.

Junk Dimension

A junk dimension is a convenient grouping of typically low-cardinality flags and indicators. A Junk Dimension is a dimension table consisting of attributes that do not belong in the fact table or in any of the existing dimension tables. The nature of these attributes is usually text or various flags,

Example: non-generic comments or just simple yes/no or true/false indicators.

Degenerated dimension
A degenerated dimension is data that is dimensional in nature but stored in fact table, doesn’t have any dimensional table on its own.

Example: A dimension having Order Number and Order line number having 1:1 relationship with fact table. Instead of creating as a separate dimension, containing billions of rows, those 2 columns can be included as part of the fact table.

Role Playing dimension

Dimensions that can be joined to the same fact table multiple times, each time to a different column. These dimensions are referred to as role playing dimensions, as the same dimension plays different roles.

Example: Suppose, if you want to examine sales by order date, ship date and delivery date. Instead of creating 3 different date dimensions, create a single date dimension and join it to the fact table 3 times in data source view.

Parent-child dimension
Based on self-referencing relationship

Example: If you have a employee dimension and want to add parent child relationship to employee manager, you must include both employee key and parent employee key in your dimension as attributes.

Time Dimension
SSAS provides 2 ways of creating time dimension

• Base the time dimension on a dimension table that exists in a relational warehouse
• Create the time dimension based on system generated time values. (server time dimension)

Different types of dimensions

2016-04-06T03:05:00.000-07:00

Conformed Dimension

A conformed dimension is a set of data attributes that have been physically implemented in multiple database tables using the same structure, attributes, domain values, definitions and concepts in each implementation. A conformed dimension cuts across many facts.

Example: Date Dimension table can be shared by multiple fact tables. Date Dimension table connected to sales fact is identical to the one connected to inventory facts.

Junk Dimension

A junk dimension is a convenient grouping of typically low-cardinality flags and indicators. A Junk Dimension is a dimension table consisting of attributes that do not belong in the fact table or in any of the existing dimension tables. The nature of these attributes is usually text or various flags,

Example: non-generic comments or just simple yes/no or true/false indicators.

Degenerated dimension

A degenerated dimension is data that is dimensional in nature but stored in fact table, doesn’t have any dimensional table on its own.

Example: A dimension having Order Number and Order line number having 1:1 relationship with fact table. Instead of creating as a separate dimension, containing billions of rows, those 2 columns can be included as part of the fact table.

Role Playing dimension

Dimensions that can be joined to the same fact table multiple times, each time to a different column. These dimensions are referred to as role playing dimensions, as the same dimension plays different roles.

Example: Suppose, if you want to examine sales by order date, ship date and delivery date. Instead of creating 3 different date dimensions, create a single date dimension and join it to the fact table 3 times in data source view.

Parent-child dimension

Based on self-referencing relationship

Example: If you have a employee dimension and want to add parent child relationship to employee manager, you must include both employee key and parent employee key in your dimension as attributes.

Time Dimension

SSAS provides 2 ways of creating time dimension

• Base the time dimension on a dimension table that exists in a relational warehouse

• Create the time dimension based on system generated time values. (server time dimension)

SSAS Client Server Architecture

2016-04-04T09:48:00.000-07:00

SSAS Architecture

2016-04-04T09:45:00.002-07:00

CHECKSUM In SQL Server

2016-03-07T21:35:00.002-08:00

CHECKSUM computes a hash value, called the checksum, over its list of arguments. The hash value is intended for use in building hash indexes. If the arguments to CHECKSUM are columns, and an index is built over the computed CHECKSUM value, the result is a hash index. This can be used for equality searches over the columns.

CHECKSUM satisfies the properties of a hash function: CHECKSUM applied over any two lists of expressions returns the same value if the corresponding elements of the two lists have the same type and are equal when compared using the equals (=) operator. For this definition, null values of a specified type are considered to compare as equal. If one of the values in the expression list changes, the checksum of the list also generally changes. However, there is a small chance that the checksum will not change. For this reason, we do not recommend using CHECKSUM to detect whether values have changed, unless your application can tolerate occasionally missing a change. Consider using HashBytes instead. When an MD5 hash algorithm is specified, the probability of HashBytes returning the same result for two different inputs is much lower than that of CHECKSUM.

The order of expressions affects the resultant value of CHECKSUM. The order of columns used with CHECKSUM(*) is the order of columns specified in the table or view definition. This includes computed columns.

@@ROWCount in SQL Server

2016-03-07T21:12:00.002-08:00

Transact-SQL statements can set the value in @@ROWCOUNT in the following ways:

· Set @@ROWCOUNT to the number of rows affected or read. Rows may or may not be sent to the client.
· Preserve @@ROWCOUNT from the previous statement execution.
· Reset @@ROWCOUNT to 0 but do not return the value to the client.

Statements that make a simple assignment always set the @@ROWCOUNT value to 1. No rows are sent to the client. Examples of these statements are: SET @local_variable, RETURN, READTEXT, and select without query statements such as SELECT GETDATE() or SELECT 'Generic Text'.

Statements that make an assignment in a query or use RETURN in a query set the @@ROWCOUNT value to the number of rows affected or read by the query, for example: SELECT @local_variable = c1 FROM t1.

Data manipulation language (DML) statements set the @@ROWCOUNT value to the number of rows affected by the query and return that value to the client. The DML statements may not send any rows to the client.

DECLARE CURSOR and FETCH set the @@ROWCOUNT value to 1.

EXECUTE statements preserve the previous @@ROWCOUNT.

Statements such as USE, SET <option>, DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to 0.

SSIS Performance Tips

2016-01-19T10:45:00.001-08:00

SSIS Package Design Time Considerations

#1, Extract data in parallel; SSIS provides the way to pull data in parallel using Sequence containers in control flow. You can design a package in such a way that it can pull data from non-dependent tables or files in parallel, which will help to reduce overall ETL execution time.

#2, Extract required data; pull only the required set of data from any table or file. You need to avoid the tendency to pull everything available on the source for now that you will use in future; it eats up network bandwidth, consumes system resources (I/O and CPU), requires extra storage, and it degrades the overall performance of ETL system.

If your ETL system is really dynamic in nature and your requirements frequently change, it would be better to consider other design approaches, like Meta Data driven ETL, etc. rather than design to pull everything in at one time.

#3, Avoid the use of Asynchronous transformation components; SSIS is a rich tool with a set of transformation components to achieve complex tasks during ETL execution but at the same time it costs you a lot if these components are not being used properly.

Two categories of transformation components are available in SSIS; Synchronous and Asynchronous.

Synchronous transformations are those components which process each row and push down to the next component/destination, it uses allocated buffer memory and doesn’t require additional memory as it is direct relation between input/output data row which fits completely into allocated memory. Components like Lookup, Derived Columns, and Data Conversion etc. fall into this category.

Asynchronous transformations are those components which first store data into buffer memory then process operations like Sort and Aggregate. Additional buffer memory is required to complete the task and until the buffer memory is available it holds up the entire data in memory and blocks the transaction, also known as blocking transformation. To complete the task SSIS engine (data flow pipeline engine) will allocate extra buffer memory, which is again an overhead to the ETL system. Components like Sort, Aggregate, Merge, Join, etc. fall into this category.

Overall, you should avoid Asynchronous transformations but still, if you get into a situation where you don’t have any other choice then you must aware of how to deal with the available property values of these components.

#4, Optimum use of event in event handlers; to track package execution progress or take any other appropriate action on a specific event, SSIS provides a set of events. Events are very useful but excess use of events will cost extra overhead on ETL execution.

Here, you need to validate all traits before enabling an event in the SSIS package.

#5, Need to be aware of the destination table schema when working on a huge volume of data. You need to think twice when you need to pull a huge volume of data from the source and push it into a data warehouse or data mart. You may see performance issues when trying to push huge data into the destination with a combination of insert, update and delete (DML) operations, as there could be a chance that the destination table will have clustered or non-clustered indexes, which may cause a lot of data shuffling in memory due to DML operations.

If ETL is having performance issues due to a huge amount of DML operations on a table that has an index, you need to make appropriate changes in the ETL design, like dropping existing clustered indexes in the pre-execution phase and re-create all indexes in the post-execute phase. You may find other better alternatves to resolve the issue based on your situation.

Configure Components Properties

#6, Control parallel execution of a task by configuring the MaxConcurrentExecutables and EngineThreads property. SSIS package and data flow tasks have a property to control parallel execution of a task; MaxConcurrentExecutables is the package level property and has a default value of -1, which means the maximum number of tasks that can be executed is equal to the total number of processors on the machine plus two;

Package

EngineThreads is a data flow task level property and has a default value of 10, which specifies the total number of threads that can be created for executing the data flow task.

Data Flow Task

You can change default values of these properties as per ETL needs and resources availability.

#7, Configure Data access mode option in OLEDB Destination. In the SSIS data flow task we can find the OLEDB destination, which provides a couple of options to push data into the destination table, under the Data access mode; first, the “Table or view“ option, which inserts one row at a time; second, the “Table or view fast load” option, which internally uses the bulk insert statement to send data into the destination table, which always provides better performance compared to other options. Once you choose the “fast load” option it gives you more control to manage the destination table behavior during a data push operation, like Keep identity, Keep nulls, Table lock and Check constraints.

OLE DB Destination Editor

It’s highly recommended that you use the fast load option to push data into the destination table to improve ETL performance.

#8, Configure Rows per Batch and Maximum Insert Commit Size in OLEDB destination. These two settings are important to control the performance of tempdb and transaction log because with the given default values of these properties it will push data into the destination table under one batch and one transaction. It will require excessive use of tembdb and transaction log, which turns into an ETL performance issue because of excessive consumption of memory and disk storage.

OLE DB Destination Editor

To improve ETL performance you can put a positive integer value in both of the properties based on anticipated data volume, which will help to divide a whole bunch of data into multiple batches, and data in a batch can again commit into thedestination table depending on the specified value. It will avoid excessive use of tempdb and transaction log, which will help to improve the ETL performance.

#9, Use of SQL Server Destination in a data flow task. When you want to push data into a local SQL Server database, it is highly recommended to use SQL Server Destination, as it provides many benefits to overcome other option’s limitations, which helps you to improve ETL performance. For example, it uses the bulk insert feature that is built into SQL Server but it gives you the option to apply transformation before loading data into the destination table. Apart from that, it gives you the option to enable/disable the trigger to be fired when loading data, which also helps to reduce ETL overhead.

SQL Server Destination Data Flow Component

#10, Avoid implicit typecast. When data comes from a flat file, the flat file connection manager treats all columns as a string (DS_STR) data type, including numeric columns. As you know, SSIS uses buffer memory to store the whole set of data and applies the required transformation before pushing data into the destination table. Now, when all columns are string data types, it will require more space in the buffer, which will reduce ETL performance.

To improve ETL performance you should convert all the numeric columns into the appropriate data type and avoid implicit conversion, which will help the SSIS engine to accommodate more rows in a single buffer.

TRIM Function in SQL Server

2015-12-13T21:03:00.003-08:00

For trim the spaces SQL Server has no function which can do trimming from both end instead it has function LTRIM and RTRIM.

You can create your own function and use it in your code or query whenever required. It will be termed as UDF.

CREATE FUNCTION dbo.TRIM(@string VARCHAR(MAX))

RETURNS VARCHAR(MAX)

BEGIN

RETURN LTRIM(RTRIM(@string))

END

This function will take input as string which need to be trimmed.

Restore SQL Server Database using tsql script

2015-12-10T23:26:00.003-08:00

Restore SQL Server Database using tsql script

Step 1: Retrive the Logical file name of the database from backup.
RESTORE FILELISTONLY
FROM DISK = 'D:\BackUpYourBaackUpFile.bak'
GO

Step 2: Use the values in the LogicalName Column in following Step.
----Make Database to single user Mode
ALTER DATABASE YourDB
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

----Restore Database
RESTORE DATABASE YourDB
FROM DISK = 'D:\BackUpYourBaackUpFile.bak'
WITH MOVE 'YourMDFLogicalName' TO 'D:\DataYourMDFFile.mdf',
MOVE 'YourLDFLogicalName' TO 'D:\DataYourLDFFile.ldf'

/*If there is no error in statement before database will be in multiuser
mode.
If error occurs please execute following command it will convert
database in multi user.*/
ALTER DATABASE YourDB SET MULTI_USER

Function for comma seprated values from table

2015-12-10T23:10:00.002-08:00

Generally we get the requirement that we need comma separated values on one column which we can combined with any other result set.

For example when we need to retrieve the all phone no of a customer or multiple line of address for the customer.

You can create a function in which you can pass a value on which you want all other values as comma separated. Once you create the function you can use in select statement to retrieve data in desired format.

These are the two ways to get string values in comma separated

Easy Ways for doing function
DECLARE @listStr VARCHAR(MAX)

SET @listStr = ''

SELECT @listStr = @listStr + NumberCols + ','

FROM NumberTable

SELECT SUBSTRING(@listStr , 1, LEN(@listStr)-1)

DECLARE @listStr VARCHAR(MAX)

SELECT @listStr = COALESCE(@listStr+',' , '') + NumberCols

FROM NumberTable

SELECT @listStr

SQL SERVER SHORTCUT KEYS

2015-12-09T10:28:00.002-08:00

Shortcut	Function	Shortcut	Function
ALT+BREAK	Cancel a query	CTRL+SHIFT+F2	Clear all bookmarks
ALT+F1	Database object information	CTRL+SHIFT+INSERT	Insert a template
ALT+F4	Exit	CTRL+SHIFT+L	Make selection lowercase
CTRL+A	Select all	CTRL+SHIFT+M	Replace template parameters
CTRL+B	Move the splitter	CTRL+SHIFT+P	Open
CTRL+C	Copy	CTRL+SHIFT+R	Remove comment
CTRL+D	Display results in grid format	CTRL+SHIFT+S	Show client statistics
CTRL+Delete	Delete through the end of the line	CTRL+SHIFT+T	Show server trace
CTRL+E	Execute query	CTRL+SHIFT+U	Make selection uppercase
CTRL+F	Find	CTRL+T	Display results in text format
CTRL+F2	Insert/remove bookmark	CTRL+U	Change database
CTRL+F4	Disconnect	CTRL+V	Paste
CTRL+F5	Parse query and check syntax	CTRL+W	Window selector
CTRL+G	Go to line	CTRL+X	Delete
CTRL+H	Replace	CTRL+Z	Undo
CTRL+I	Index Tuning Wizard	F1	Help for Query Analyzer
CTRL+K	Display/hide execution plan	F2	Move to next bookmark
CTRL+L	Display execution plan	F3	Repeat last search
CTRL+N	New query window	F4	Object Search
CTRL+O	Connect	F5	Execute a query
CTRL+P	Print	F6	Switch between query and result panes
CTRL+R	Show/Hide results pane	F8	Show/hide Object Browser
CTRL+S	Save	SHIFT+F1	Transact-SQL help
CTRL+SHIFT+0	Show options	SHIFT+F2	Move to previous bookmark
CTRL+SHIFT+C	Comment out code	SHIFT+TAB	Decrease indent
CTRL+SHIFT+DEL	Clear the active Editor pane	SHIFT+F6	Switch panes
CTRL+SHIFT+F	Save results to file	TAB	Increase indent

script to shrink log files

2015-12-09T10:24:00.002-08:00

USE
DatabaseName

GO

DBCC
SHRINKFILE(<TransactionLogName>, 1)

BACKUP
LOG <DatabaseName> WITH TRUNCATE_ONLY

DBCC
SHRINKFILE(<TransactionLogName>, 1)

GO