Waiting for MSSQL Database Restores to complete?

In my current role, we can have a lot of restores in progress during a maintenance window. This handy script allows us to calculate how long the restores will take and when our coffee break will end!

— Check Restores in Progress
SELECT
         r.session_id
        ,r.command
        ,CONVERT(NUMERIC(38,2),r.percent_complete) AS [Percent Complete]
        ,CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time]
        ,CONVERT(NUMERIC(38,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min]
        ,CONVERT(NUMERIC(38,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min]
        ,CONVERT(NUMERIC(38,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours]
        ,CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
                                                                CASE WHEN r.statement_end_offset = -1 THEN 1000
                                                                ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
                                                        FROM
                                                                sys.dm_exec_sql_text(sql_handle)))
FROM
        sys.dm_exec_requests r
WHERE
        command LIKE ‘%restore%’;
GO

Did you miss SQLSaturday #501 in Dublin?

As a previous organiser of SQL Saturday events in Dublin, I was bitterly disappointed to miss being an ordinary attendee at this year’s SQL Saturday  ( http://www.sqlsaturday.com/501/eventhome.aspx ). I had registered many months ago. Unfortunately, there was an unavoidable planned monthly maintenance window which lasted the whole day. I had hoped to get away and join the event in the afternoon but it was not to be.

But the great news is that some of the events were recorded and broadcast live on Microsoft’s Channel 9 and they are still available on the website – https://channel9.msdn.com/Events/SQL-Saturday/Learn-more-about-SQL-2016-Live-at-SQL-Saturday-Dublin-Ireland

Thank you to Microsoft and SQLSaturday Dublin for making this possible.

Removing an MSSQL file that was accidently or incorrectly added to a database

Recently, I have had a number of occasions where a new file was accidently added to the SQL Server database; for example, to the wrong file group.

The problem is slightly compounded if there is Transaction Logging in place.

There are number of steps to follow to correct the error

1 – Force the file to be emptied of any data

USE [InsertDatabaseNameHere]
GO
DBCC SHRINKFILE(‘InsertLogicalFileNameHere’, EMPTYFILE);

2 – Remove the file from the database

USE [master]
GO
ALTER DATABASE InsertDatabaseNameHere REMOVE FILE InsertLogicalFileNameHere;

3- If there is Transaction Logging or Log Shipping for this database, run an extra log backup. One cannot remove a file and then create it again within the same transaction log.

4- Add the file again with the correct settings. Change the values below to suit your configuration,

USE [master]
GO

ALTER DATABASE [InsertDatabaseNameHere]
ADD FILE
(
    NAME = N’InsertLogicalFileNameHere’,
    FILENAME = N’Z:\InsertDirectoryPathHere\InsertPhysicalFileNameHere.ndf’,
    SIZE = 1024MB , 
    MAXSIZE = 10240MB ,
    FILEGROWTH = 1024MB
) TO FILEGROUP InsertFileGroupNameHere;
GO

Winners of the competition to attend Kevin Kline’s “From Good to Great: Productivity and Influence Skills for the IT Professional” pre-conference event

Last month, the SQL Saturday committee and SQL Server Ireland User Group held a competition to win a free place at the SQL Saturday 229 pre-conference – Kevin Kline’s “From Good to Great: Productivity and Influence Skills for the IT Professional”.

Kevin has been an awesome ambassador to Ireland in the SQL community and we wanted to celebrate that fact.

So the question was “How many times has Kevin Kline visited Ireland as a SQL speaker prior to this?”

Only one person, David Henley, got the correct answer of 3 visits :  Dublin, Cork, Galway & Belfast user group roadshow in 2007, a Dublin user group in 2011, & the Dublin SQL Saturday in 2012.

A second entrant, Yibo Hu, answered 6 times which is the correct number of events that he attended.

We decided to award a free entry to the event to both entrants.

Congratulations to David & Yibo.

To learn more about the event, visit the SQL Saturday 229 (http://www.sqlsaturday.com/229/) website for details.

SQL Saturday Dublin 2013 – One Month To Go

The SQL Saturday is now only a month away tomorrow and the committee has been very busy.

The biggest news is that the session builder is now available to all those registered for SQL Saturday 229 (http://www.sqlsaturday.com/229/). Whether you have already booked or have yet to book, please help us to plan room sizes by letting us know what sessions you will be attending.

SQL Saturday was oversubscribed last year so book quickly to ensure your place.

In other good news, we have extended the early bird on the pre-conferences until the end of May. There are still places left for each of the pre-conferences.

If you wish to attend any of the pre-conferences, the details for these can be found at:

Kevin Kline’s “From Good to Great: Productivity and Influence Skills for the IT Professional” http://www.prodata.ie/Events/SqlSat229/Precon1.aspx

Chris Webb’s “Self-Service BI and Office 2013” http://www.prodata.ie/Events/SqlSat229/Precon2.aspx

Matt Masson’s “A Day of Enterprise Information Management – SSIS, MDS and DQS” http://www.prodata.ie/Events/SqlSat229/Precon3.aspx

Win a free place at Kevin Kline’s “From Good to Great: Productivity and Influence Skills for the IT Professional” pre-conference

The SQL Saturday Dublin committee has completed the session schedule for SQL Saturday 229. In addition, we have extended the early bird on the pre-conferences until the end of May.

So what more could we do? We decided to hold a competition to win a free place at the SQL Saturday 229 pre-conference – Kevin Kline’s “From Good to Great: Productivity and Influence Skills for the IT Professional”.

Kevin has been an awesome ambassador to Ireland in the SQL community and we wanted to celebrate that fact.

So the question is “How many times has Kevin Kline visited Ireland as a SQL speaker prior to this?”

All you have to do is email “ssiug at live.ie” with “Competition” in the subject and let us know the answer.

We will complete the draw on the last day of May.

Niall Flanagan revisits Indexing

The next SQL Server Ireland User Group meeting is “Niall Flanagan revisits Indexing” on Tuesday, April 30th, 2013 at the usual location in Auditorium, on the Ground Floor of the Microsoft Atrium building, in Sandyford.

This topic will cover the performance benefits and important considerations of good indexing design in SQL Server including

  • Considerations, Benefits & Concepts
  • Statistics
  • Table/Index Design
  • Clustered, Non-clustered, & Heap
  • Fragmentation
  • Why cheap hardware is not always the answer

When:             April 30th,  2013 at 18.30

Where:            Microsoft Atrium, Carmanhall Road, Sandyford, Dublin 18. A limited amount of underground parking is available via the rear of the building.

Cost:               FREE Community Event

Registration:    Not required. Contact ssiug at live.ie for more details or visit the Microsoft Technology User Groups site (www.mtug.ie )

Niall Flanagan leads a team that manages production Oracle, Sybase and SQL Server databases in a large Irish retail bank. Niall founded the SQL Server Ireland User Group in early 2006 and continues to lead it. In addition, he presents on SQL Server to various user groups and conferences throughout Ireland. Niall is a Microsoft SQL Server Most Valued Professional (MVP) since October 2006.

Pre-conferences for SQL Saturday Dublin (#229) now open for registration

Last year, we had just one pre-conference for SQL Saturday Dublin, which was presented by Matt Masson. But this year, you will have to choose between three!

On Friday, June 21st 2013, we are holding the SQL Saturday #229 Preconference Day with a fantastic choice of full-day training sessions:
Productivity and Leadership Skills for the IT Professional by Kevin Kline
Self-Service BI and Office 2013 by Chris Webb
SSIS, MDS and DQS by Matt Masson

These full-day sessions can be booked for an early bird price of just €199 till 1st of May, full price after this date is €249, so register early!

You can register for these events via the SQL Saturday website or through Prodata’s event site

SQL Saturday is back in Dublin on June 22nd 2013

The SQL Saturday (www.sqlsaturday.com) one day event was a huge success in Dublin last year as it is all over the world. Almost 500 registered for that event and many SQL Server speakers from around the world were delighted to come to present in Dublin.

We have announced a 2nd Irish SQL Saturday in Dublin on June 22nd, 2013. You can register at http://www.sqlsaturday.com/229/

This year we hope to be better still. Speaker submissions are already arriving and we are organising THREE full day pre-conference seminars on the Friday. On the Saturday we will have FOUR tracks covering core database, BI, Developer and cloud/special interest.

I would like to thank my fellow committee members (Sandra, Marco, Bob, Inga, & Carmel) for the hard work they do to make this all possible again.

This is the must attend event for SQL Server here in Ireland in 2013.

Twitter – #SQLSatDublin

Facebook – https://www.facebook.com/pages/SQLSaturday-229-Dublin/272299059478083

SQL 2012 Always On Deep Dive and Windows Azure for SQL Folk in Belfast

Our very own SQL Server MVP ( as well as MCA, MCM & now Analysis Services “Maestro” certified) , Bob Duffy is heading North to present at the 3rd Belfast SQL Server User Group on April 11th at the LIT offices.

Bob currently is the lead database architect at the Prodata SQL Centre of Excellence in Dublin, Ireland where he helps enterprise customers with design, performance tuning, benchmarking and pretty much anything to do with SQL Server or Business Intelligence.

SQL 2012 Always On Deep Dive (Beginner/Intermediate)

Always On Availability Groups offers a huge leap forward in terms of high availability. This sessions is a demo based introduction to the high availability changes in Denali emphasising key features and benefits.

During the demonstration we will show how to seamlessly upgrade a database from SQL 2005+ to Denali with ZERO down time and then use the Always on features to simplify reporting, availability and disaster recovery.

This session will be useful for anyone who is working in a high availability environment or currently using replication to provide reporting and looking for a neater solution.

Windows Azure for SQL Folk (Beginner)

So what is this cloud stuff and how does it affect database professionals?

In this session we aim to cover the fundamental concepts that you should know on the Cloud and Windows Azure. In this demo based hour we will step through building Azure applications, deploying and managing them and what storage choices you have when developing applications in the cloud.

This sessions is Ideally suited to anyone who wants to understand more behind how Azure works, what it offers and what is probably just hype.

Date: Thursday 11th April 2013
Time: 5:45pm-8:45pm
Venue: Liberty IT, Adelaide Exchange, 24-26 Adelaide St, Belfast.

Registration is at http://www.eventbrite.com/event/5705276636#

%d bloggers like this: