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

Advertisements

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

Wayne McCrum talks about Microsoft Premier Support’s Risk Assessment Program

The next SQL Server Ireland User Group meeting is on Microsoft Premier Support’s “Risk Assessment Program” on Tuesday, September 10th, 2013 in the Microsoft Atrium building, in Sandyford.

Wayne will discuss the program and discuss some of the common misconfigurations seen in the field.

When: September 10th, 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: http://ssiug130903.eventbrite.ie/

Wayne McCrum has been working with SQL Server since 2004 in a variety of SQL orientated roles. He currently works as a SQL Server Field Engineer for Microsoft assisting customers within Western Europe on both proactive and reactive engagements around the SQL Server product.

Ian Meade on Flexible Data Modelling with EAV & other technologies

Ian Meade, from Ergo, will present on “Flexible Data Modelling with EAV & other technologies” in the Auditorium on the Ground Floor of the Microsoft Atrium on Tuesday, August 6th, 2013 at 18:30

The event is free and details can be found at http://www.mtug.ie/UserGroups/sqlserver/Lists/Calendar/DispForm.aspx?ID=7

The MTUG (www.mtug.ie) website has been updated with the details of all forthcoming SQL Server Ireland User Group meetings for 2014.

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.

Neil Hambly on Effective Index Partitioning, Compression Strategy

The next SQL Server Ireland User Group meeting is “Effective Index Partitioning, Compression Strategy” on Tuesday, May 28th, 2013 at the usual location in Auditorium, on the Ground Floor of the Microsoft Atrium building, in Sandyford.

We all know that ‘Indexing’ is KING when it comes to achieving high levels of performance in SQL Server.

When Indexing also combines 2 of the Enterprise features: Partitioning & Compression, we can often see substantial gains.

Learn how to identify those objects that benefit greatly from being Partitioned or Compressed, OR combining both of these features to even greater effect.

Using Demos to illustrate the performance gains with real-world examples,

Take away advanced scripts for use in your own environments.

When:             May 28th,  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 )

Neil Hambly is a Senior DBA @ Confio Software, having held various SQL Server roles during the last 14 years @ market leading companies (Accenture, ABN AMRO, BBC, iProfile, MDSL) He has considerable Experience in SQL Server starting with version 6.5 through to the latest 2012 release. A regular International speaker @ SQL conference & User Group, he also leads the SQL London PASS Chapter (UK). Frequently found studying the inner workings of SQL Server, as well as an avid Tweeter @Neil_Hambly.

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.

%d bloggers like this: