MS SQL SERVER DBA

Shrinking a SQL Server Log File with Database in Always ON Availability.                    Once we had a situation in our production server that the database which is in Always ON Availability group has been grown to the maximum size.  Many of them say that we can't shrink the log file as the database is in Always ON and we should take the downtime to perform the operation. But we shrink-ed the database log file to 5 GB from 180 GB. Steps are as below: --To Check the Number of pages allocated by the LogFile. DBCC LOGINFO --To check the file sixe sp_helpdb [SQLPantry] --To check the Log file Size DBCC SQLPERF(LOGSPACE) The above statement will show the logspace of all db at present situation. --To check the Log_reuse_wait description of a log file. select log_reuse_wait_desc,* from sys.databases The above statement will show the status of the log whether it has to be backuped i.e., LogBackup, Nothing, Ac...

There are many reasons which cause the SQL Server Log file to grow to unbelievable size i.e., the space available in the Disk. 1. whenever we have not scheduled a Log_backup on the server the log file size grows bigger and occupies the disk on the specific drive. 2. If we have any repplication setup. Then we will be have log file growth. 3. Due to long running transaction also that causes the Log file grows. All the transactions that are performed on the SQL are done at the log files, after a commit transaction i.e.., from code or the SQL Server check point the data is updated to the Data file of the database. To Check the Growth of the Database Log files. DBCC SQLPERF(LOGSPACE) The Log Size (MB) column shows the size of the log files The Log Space Used(%) column shows how much of the log space is used by the SQL Server Log Files. Example: In some environment we will be able to see the below Log Size (MB): 43562.19 Log Space Used: 1000.45 When ever we take ...

SELECT DISTINCT TOP 10 t.TEXT QueryName, s.execution_count AS ExecutionCount, s.max_elapsed_time AS MaxElapsedTime, ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime, s.creation_time AS LogCreatedOn, ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec FROM sys.dm_exec_query_stats s CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t ORDER BY s.max_elapsed_time DESC GO To find the Active Running Transactions on SQL Server. https://youtu.be/6jPl1jKRfvU Please Like, Subscribe, share the video for more videos...!!!

The below query shows the output of the transactions that are actively running on the Server. SELECT sqltext.TEXT, dm.session_id, dm.status, dm.command, dm.cpu_time, dm.total_elapsed_time FROM sys.dm_exec_requests dm CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext   When we run the above query it shows the output what are the transactions that are running on the server. And it varies from time to time. As we refresh the query we will find the new query's executing on the server. This can be experienced when we run the query on the Production environment.  The same can be implemented on the stand-alone system i..e., personal computer where we schedule some jobs with SQL Script that inserts some information and some query's to retrieved the data.

Report layout can be boiled down to a few, simple design patterns. These simple styles also can be combined with others to form composite reports and more advanced layout to visualize data. This section offers a quick review of the report layout types, followed by some examples.     Tabular Reports   Tabular reports have been around for thousands of years. Well, they may not have been reports in the modern sense, but when you think about it, some common reports are really little more than  “ lists of stuff ”  organized into rows and columns. Ever since early merchants began trading seashells or precious gems for beaver pelts or goat cheese, someone was recording the transaction in some kind of list, be it on papyrus, stone tablets, or a tablet PC.   Ever since VisiCalc, the predecessor to Lotus 123 and Microsoft Excel, was released in 1981, the tabular spreadsheet format has become the way many computer users are accustomed to v...

Reporting Services Tools Reporting Services supports the full reporting life cycle. This support is provided through a collection of tools that come with Reporting Services, as identified in the following table. Authoring Management Delivery Report Designer Report Manager Report Manager Report Builder1.0 SharePoint reports library HTML Viewer Report Builder 2.0 Reporting Services Configuration Manager SharePoint libraries and web parts Third – party authoring tools SQL Server management applications Command - line utilities Report Viewer controls Reporting Services Web service Subscriptions

  The reporting life cycle is often described as a process consisting of three sequential phases. A report is first designed and developed in the authoring phase, made accessible to end-  users in the management phase, and then placed in the hands of end - users in the delivery phase. Authoring • End-User • Report Specialist | Management • Content  • System  | Delivery • On-Demand (Pull)  • Subscription (Push)   Authoring   The  authoring phase  of the reporting life cycle starts with the gathering of requirements through formal and informal processes. These requirements then drive the design of queries that provide data for the report. Data is integrated with charts, tables, matrices, or other presentation elements to form the basic report. Formatting and layout adjustments are then applied to produce a draft report that is validated for accuracy and consistency with the requirements before being published t...

 (SSRS) Reporting Services runs in one of two modes: Native or SharePoint Integrated. In the Native mode, Reporting Services manages its content using its own internal, or “  native, ”  functionality. This is the traditional mode under which Reporting Services instances have been run.  Reporting Services deployments using Enterprise, Developer, Enterprise Evaluation, or Standard editions are capable of running in the SharePoint Integrated mode. In this mode, content management is handled through SharePoint. Native content management interfaces, such as Report Manager, are no longer accessible.  The SharePoint Integrated mode is a very appealing option for many organizations that want to leverage SharePoint as their enterprise content - management solution; however, there are some limitations, such as the lack of support for linked reports in this mode.  For those organizations that want to run Reporting Services in Native mode but still want to disp...

SQL Server 2008 comes in nine editions, the following seven of which include Reporting Services:     Enterprise   Standard   Developer   Enterprise Evaluation   Workgroup   Web   Express with Advanced Services      The Enterprise and Standard editions are the only editions supported in a production environment. The Enterprise edition provides access to the full set of features available with Reporting Services and runs on 32 - bit, x64 64-  bit, and IA64 64 - bit platforms. The Standard edition provides access to a reduced feature set and does not support the IA64 64 - bit platform. It costs less than the Enterprise edition and may be more appropriate for smaller installations.   The Developer and Enterprise Evaluation editions provide access to the same features available through the Enterprise edition. The Developer edition is very inexpensive and is intended for development and testing environ...

Dedicated Administrator Connection      It is rare, but SQL Server can stop responding to normal queries and appear to be frozen. How could this happen? Consider that when you bought the system that houses SQL Server, you likely planned for future growth and usage. No matter how much time and effort you put into this plan, though, some companies just outgrow their servers too quickly, and the servers can’t handle the workload. In a scenario like this, it is possible that SQL Server might stop responding to normal queries. To troubleshoot this issue, or any kind of problem where SQL Server just isn’t responding to normal calls, you need the Dedicated Administrator Connection (DAC).      The DAC is a special diagnostics connection that is always available for connection, even under normal operating circumstances. As the name implies, only administrators (members of the sysadmin server role) can connect to the DAC to run diagnostic queries and trou...

By taking appropriate precautions, most of these breaches can be prevented or detected before they get out of hand.  1. Use a dedicated server for your database Host your database on a dedicated server. Whether it is local or in the cloud, spend the extra cash on a dedicated server to prevent security leaks and breaches. 2. Harden the Operating System On your dedicated server, the first step is to implement operating system hardening. Many hardening techniques exist. At a minimum, you need to: Change the default ports, as described below. Hide SQL instances from showing in the network, as described below. Allow only network protocols that are needed. CONNECT permission should be granted only on endpoints to logins that need to use them. If there is a need to work with SQL Login, install an SSL certificate from a trusted CA rather than SQL Server's self-signed certificates. Avoid the exposure of SQL Server to the public internet/intranet. Change default ...