Monitoring Long Running Transactions in TempDB

TempDB database plays a major role in SQL Server. Therefore, it is extremely important to monitor the health of the TempDB database. One of the major challenges in TempDB is maintaining it's log file. If there are transactions that use the TempDB and if those are long-running transactions, there can be situations where the log file will grow. Since these transactions are not closing, log space will not be returned and the entire server will not be able to run queries that use the TempDB. 

Recently, one of the Clients had a similar problem. One query was running for more than four days and it had consumed TempDB log file. This has caused empty disk space and the entire server is halted for operations.

In this situation, the easiest and laziest thing to do is the restart the server. Restart will kill all the transactions and return TempDB back to the original size. This is not something that you can do for a system of 24x7. 

However, we choose not to restart but to identify the long-running query from the following simple query.

SELECT  se_tr.session_id,

sec.login_name,

trn.database_transaction_begin_lsn,

trn.database_transaction_begin_time,

trn.database_transaction_log_record_count,

 trn.database_transaction_log_bytes_used,

 trn.database_transaction_log_bytes_reserved,

 t.text,

 q.query_plan

FROM sys.dm_tran_database_transactions trn

INNER JOIN sys.dm_tran_session_transactions se_tr ON trn.transaction_id = se_tr.transaction_id

INNER JOIN sys.dm_exec_sessions sec ON se_tr.session_id = sec.session_id

INNER JOIN sys.dm_exec_connections con ON con.session_id = sec.session_id

LEFT OUTER JOIN sys.dm_exec_requests req ON req.session_id = sec.session_id

CROSS APPLY sys.dm_exec_sql_text  (con.most_recent_sql_handle) t

OUTER APPLY sys.dm_exec_query_plan (req.plan_handle) q

WHERE trn.database_id =DB_ID('TempDB') 

This gave the option to identify the long running query and we killed the relevent session. With that, TempDB log file was emptied and by shrinking the tempdb log file, we were able to gain the disk space. 

Further, we took a pro-active decision by enabling an alert, so that if a query runs for more than 8 hrs (configurable) that will be altered the DBA so that he can kill the session straightway. 

Windows And SQL Server 2008 And 2008 R2 End Of Life

Windows Server 2008/2008 R2 and SQL Server 2008/2008 R2 are on their extended support phase now. July 8, 2019 for SQL Server 2008/2008  R2 and January 14, 2020 for Windows Server 2008/2008 are the end-of-life dates. Once you hit these dates, you will essentially be running those machines at your own risk in this bloated age of cyber attacks. 

Following are the options you have
Path 1:  The first path is the traditional evolutionary path of upgrading to a newer version of Windows Server and SQL Server. 

Path 2:  If you do not want to upgrade to the latest server versions—don’t worry—Microsoft has you covered. Microsoft recently announced a few options for those who want to continue on Windows Server 2008/2008 R2 and SQL 2008/2008 R2. To save money, you could simply move your Windows Server 2008/2008 R2 and SQL 2008/2008 R2 to Azure by using the Azure Hybrid Use Benefit. The only cost you would incur would be computed and infrastructure.

Read more at https://community.connection.com/windows-sql-server-2008-2008-r2-end-life-coming-soon-now/

SSRS Migration from SQL Server 2008 R2 to SQL Server 2017

As you are aware SQL Server 2008 R2 Microsoft support coming close to a shutdown, now people are starting to move to higher versions. 

This client wants to move its 800+ SSRS from SQL Server 2008 R2. Followings are learnings from the exercise.

1. SQL Server 2017 SSRS does not come as a single bundle of SQL Server. This means that you need to install SSRS as a separate installation. 

2. Though there are several ways of SSRS upgrade, ReportsServer database backup and restore was chosen as the method. In this, it is important to note that ReportServerTemp database also needs to be restored along with the ReportServer. SSRS service should be stopped before the restoring and with the restart of SSRS, SSRS services are upgraded automatically. Also, subscriptions schedules will be created as well. 

Do you think, you have completed your work. NOPE. still, a few touches are needed.

3. Authentications will not be transferred as a security measure. Therefore, you need to configure all of your data sources. If you have not managed your data sources carefully like this client, you will be coping and pasting connection strings, user names and password for one million times. 

4. If you have set up subscriptions for reports then there are few more things to be done. In standard subscriptions with excel, in the SQL Server 2008 R2 it will be configured as Excel 2003. This will be moved to that new version as it is, but will not work. It has to be changed to Excel. 

5. If you think that it is a tedious task in standard subscription, hold on for a second. There is more to come. In the case of Data-Driven Subscription, all the Excel file types are changed to MHTL Archive. Well, something little difficult to understand but that is how it is 

Additional Controls for SSIS

COZYROC is the leading company providing advanced Microsoft SQL Server 2005-2014 Integration Services (SSIS) library of custom tasks, components and scripts for handling Zip, GZip, BZip2, Tar archives, enhanced script reuse and secure internet communications.

Details can be found at http://www.cozyroc.com/products

24 hours of SQLPass is coming

24 hours of SQL Pass is happening on Sept 17 & 18th.  These are short little 1-hour web casts, but may be of interest to someone.  Registration is FREEEEEEEE!!!  

Link to sessions:

http://www.sqlpass.org/24hours/2015/summitpreview/Schedule.aspx

SS SLUG August 2015 Meet-up

Wednesday,August,19

SS SLUG August 2015 Meet-up

• 11th Floor, DHPL Building
  No. 42 Nawam Mawatha
  Colombo 00200, Sri Lanka
  (map)
• 18:00 - 20:30 (UTC+05:30) Sri Jayawardenepura
• Language: English

We're back after a month's hiatus. We're sorry that we couldn't hold a meet-up last month. This month we've got two exciting sessions lined up for you...

Sessions:

SQL Server Full Text Search

Ravin Perera, Tech Lead Geveo Australasia

Natural text-search capabilities are a must in modern applications. As SQL Server developers, what are the technologies we can use to provide search-engine-like features in our applciations? "Lucene.Net" is very popular, but comes with a huge integration and administrative overhead. In this session we'll explore the SQL Server built-in Full-Text Search feature which allows developers to fulfill most of the common natural text search needs with zero management overhead. You can integrate natural text-serach capabilities into your databases right away!

About Ravin:
Ravin is a Microsoft certified developer and certified scrum master, who has worked on various types of projects. Connect with Ravin on LinkedIn and Facebook (https://lk.linkedin.com/pub/ravin-perera/2b/466/61b|https://www.facebook.com/ravinsp)

Data Mining: Microsoft Time Series

Dinesh Asanka, Senior Database Specialist Pearson Lanka

Continuing with his data mining series, Dinesh joins us with the Microsoft Time Series algorithm. This algorithm provides regression algorithms that are optimized for the forecasting of continuous values, such as product sales, over time. Learn how to predict trends based on the original data set and add new data to the model and automatically use this data in your analysis.

About Dinesh:
Dinesh is a SQL Server MVP and a long time database enthusiast, and has been contributing to the database community for several years through his blog, technical forums and various speaking engagements. He is also a visiting lecturer at the Sri Lanka Institute of Information Technology

Dealing with an Inherited SQL Server

If you work with SQL Server you’ve most likely been there at one time or another. Maybe you’ve just started a new job, or you might be a Windows administrator who has been tasked to manage SQL Server, or you might be a consultant who has been brought in to deal with a specific problem.  The end result is that you wind up working with a SQL Server instance and one or more databases that you know nothing about.

More at http://sqlmag.com/sql-server/dealing-inherited-sql-server

26 free helpful tips for SQL Server monitoring

Have you ever been caught out by a performance problem with SQL Server? Do you want to start monitoring your databases, but aren’t sure where to start? Do you need to know which problems to look for – and how to spot them?

Then this series of 26 free tips is for you - it covers every aspect of SQL Server monitoring, including CPU, IO, memory, backups, tuning, capacity planning, and common errors.

Plus, when you sign up, you get a free copy of our 30-page guide, 8 Steps to Effective SQL Server Monitoring.

And, if you're ready to start monitoring right away, grab a free trial of SQL Monitor, our web-based monitoring and alerting tool, for insights into your servers' performance right out of the box.

1. Sign up for 26 free helpful tips on SQL Server monitoring

• Free email series with 26 free tips, ideas, and articles on SQL Server performance monitoring
• Includes worked examples, practical advice, and covers the hows and whys of SQL Server monitoring
• Plus, get a free copy of Eight Steps to Effective SQL Server Monitoring when you sign up

2. Start monitoring today with a free trial of SQL Monitor for real-time monitoring and alerting

• Real-time SQL Server performance data and alerts
• Built with advice from SQL Server experts and MVPs
• See your servers' top 10 most expensive queries
• Automatically plot performance baselines
• Discover problems before anyone else notices

Sign up now and get a free copy of 8 Steps to Effective SQL Server Monitoring, a 30-page guide to the basics

How to Load Data From PDF file in SSIS

This is the question some one asked today. I haven’t done this but I can remember I read this some where. So thought of digging this today.

Some forums are suggesting a product.

http://www.datawatch.com/monarch-is-back/

Out of the box, Datawatch Monarch can work with a wide range of report formats including PDF, XML, HTML, text, spool and ASCII files. Access data from invoices, sales reports, balance sheets, customer lists, inventory, logs and more. The system is easy to use, allowing you to quickly select a file and automatically convert it into structured data for analysis.

Following blog includes how to import several types to SQL Server.

http://sqlage.blogspot.com/2013/12/ssis-how-to-import-files-text-pdf-excel.html

Following blogspot also have lot of links

http://www.forumarray.com/ssis-data-flow-source-component-to-read-a-pdf-file-226359

Following blog is the most relevant one for me. http://sql31.blogspot.com/2013/03/how-to-load-data-from-pdf-file-in-ssis.html

This users script task and you have the option of customizing it.

How To Remove the Identity Column in SQL Server

Setting the identity property on a table when it is created is a simple task. The below snippet will create table called SampleTable with ID column which has an identity property.

Read http://www.sql-server-performance.com/2015/remove-identity-column-sql-server/

Changing the SSRS Subscription Owner

When subscriptions are created, owner of that subscription will be the user who creates it. However, when that user account is disabled, subscriptions will be failed and there is no way from the user interface to change it. However these subscriptions are stored in subscriptions table of ReportServer database (default database of the SSRS. This may be different if you have changed it from the SSRS configuration manager). In the subscription table, user id is stored as a unique identifier which you should get from the users table. Update query is shown below.

USE ReportServer

GO

DECLARE @OldUserID uniqueidentifier

DECLARE @NewUserID uniqueidentifier

SELECT @OldUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAIN\previous_user'

SELECT @NewUserID = UserID FROM dbo.Users WHERE UserName = 'DOMAIN \ new_user’

UPDATE dbo.Subscriptions SET OwnerID = @NewUserID WHERE OwnerID = @OldUserID

No restart is of SSRS or SQL Server services are required. Soon after you update the userids subscriptions will start to work.

Is your organization ready to virtualize SQL Server?

virtualization has become a hot topic. Michael Webster is one of the authors of Virtualizing SQL Server With VMware, which guides administrators in the steps required to virtualize the popular database application. One section of the book centers on how performance of these virtualized programs hinges on proper storage architecture.Read more at http://searchvmware.techtarget.com/feature/Is-your-organization-ready-to-virtualize-SQL-Server

SQL Server String Functions Cheat Sheet

SqlBak.com presents you a free SQL Server String Functions Cheat Sheet.

http://sqlbak.com/blog/wp-content/uploads/2015/01/TSQL-STRING-FUNCTIONS-CHEAT-SHEET.pdf

Using QUOTENAME() to Protect Against SQLInjection

This short article describes you to simple way of protecting from SQLInjection.

http://www.sql-server-performance.com/2014/using-quotename-protect-sqlinjection/

Data Mining Cluster Analysis in SQL Server

Grouping is something we naturally do in our day to day life. We group foods depending on taste, we group friends depending on their different attributes. 

Clustering is an algorithm which finds natural groupings inside your data when these groupings are not obvious. It finds the hidden variable that accurately classifies your data.

Read the article on Clustering here.

Report SQL Server Percentage Backup Completion and Time Completion

When executing a backup from a t-sql command, it is difficult to know what percentage has completed and when the backup will be complete. This FAQ tells how to do it.

Default Database Issues and Best Practices

Default database is a feature which is not used by many. This article is about default databases.

Copy only backup feature Modification in SQL Server 2012

Copy only backups were introduced in SQL Server 2005 so that DBA has an option of taking ad-hoc backups without breaking the backup sequence. In SQL Server 2005, copy only backup was available only for as a T-SQL option and in SQL Server 2008, this feature came to UI.

In SQL Server 2012, there is another UI change!!!!

Copy only backup does not have a meaning for differential backups since ad-hoc differential backup does not effect any sequence.

However, in SQL Server 2008 still you can get a copy only differential backups.

In SQL Server 2012, we you select differential backups copy only backup option will be disabled.

you can read more about copy only backups from here.

Avoiding Parameter Sniffing in SQL Server

Parameter sniffing is when SQL Server compiles a stored procedure’s execution plan with the first parameter that has been used and then uses this plan for subsequent executions regardless of the parameters.  Read more about parameter sniffing and how to avoid it from here.

Compress Backup Information in Backup and Restore Events

In SSMS, there is report called , “Backup and Restore Events” report.

if you consider latest backups backup size are almost similar. (~  339 MB) However, if you really see the physical file size, it is 79 MB.

So the report and physical file size is different.

You can measure the file size by running a query.

SELECT 
    bs.media_set_id,
    bs.backup_finish_date,
    bs.type,
    bs.backup_size / 1024 backup_size_KB,
    bs.compressed_backup_size / 1024 compressed_backup_size_KB,
    mf.physical_device_name
FROM dbo.backupset AS bs
INNER JOIN dbo.backupmediafamily AS mf
ON bs.media_set_id = mf.media_set_id    
WHERE database_name = 'AdventureWorks2012'
ORDER BY backup_finish_date DESC;
GO

So , here you can see the correct value can be obtained you query compressed_backup_size column rather than querying backup_size column. so it seems like, SSMS report is using backup_size column rather than compressed_backup_size column which seems to be BUG!