Find last time when transaction log backup

Find last time when transaction log backup

b.type = 'L' means transaction log

b.type = 'D' means full

b.type = 'I' means differential

SELECT   d.name,

         MAX(b.backup_finish_date) AS backup_finish_date

FROM     master.sys.sysdatabases d WITH(NOLOCK)

         LEFT OUTER JOIN msdb..backupset b WITH(NOLOCK)

         ON       b.database_name = d.name

         AND      b.type          = 'L'

WHERE b.backup_finish_date IS NOT NULL        

GROUP BY d.name

ORDER BY backup_finish_date ASC

Uninstalling SQL Server Service Pack

Starting form SQL Server 2008 SP1, you will be able to uninstall service packs from Add/remove Programs and Features. Uninstall wizard will launch through this.

For all versions of SQL before SQL Server 2008 SP1 please refer to following link

http://support.microsoft.com/kb/314823

SQL Server 2012 Database mirroring error (Microsoft SQL Server, Error: 1418)

While setting up mirroring between 2 servers I came across following error

The server network address TCP://<servername>.<domain>.com:<portnumber> can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

After setting up database mirroring security configuration I started mirroring between 2 servers and immediately I got this error. The error however does not tell you much about the actual problem. I tried changing firewall setting, checked that the service account is correct on both primary and mirror. I also tried ping from either machines and it worked fine.

But after checking the BOL I found the issue. Before setting up mirroring you have full backup database on primary and restore it on mirror as “WITH NORECOVERY”.

NORECOVERY specifies that roll back not occur. This allows roll forward to continue with the next statement in the sequence. In this case, the restore sequence can restore other backups and roll them forward.

After restoring with NORECOVERY I did a log backup on primary and restored it on mirror. Once done mirroring worked as expected.

Please note that in SQL Server 2008 & R2 mirrored copy is not readable. In SQL Server 2012 with help of Always On availability groups you can make mirrored copy readable.

SQL Server 2012 Failover cluster error (Windows Event ID 1119)

Recently I installed SQL Server 2012 RTM on a Windows failover cluster. After installation when I tried to fail over SQL server resources from N1 to N2 I got following error

Cluster network name resource 'SQL Network Name (MyServer)' failed to register DNS name 'MyServer.' over adapter 'Local Area Connection*' for the following reason:  'DNS server failure.'

I found the event ID and resolution on technet

http://technet.microsoft.com/en-us/library/cc773512(v=ws.10).aspx

But it was nothing to do with the solution mentioned on technet article.

My problem was SQL server service account was not part of the Local Administrators group. (Which I should have done before installing…dumb!!)

So here is how you add SQL service account to local

1)      Go to Start à Administrative Tools à Server Manager

2)      Expand Configuration

3)      Expand Local Users and Groups

4)      Select Groups

5)      Double click on Administrators

6)      Click on Add button

7)      Enter SQL Service account (Account under which SQL services are running)

8)      Click OK

After doing this I was able to failover SQL Server to passive node

Microsoft Security Bulletin MS12-060 – Critical

On Aug 14^th 2012 Microsoft released security bulletin which includes an update for SQL Server. Please read the details in below link

http://technet.microsoft.com/en-us/security/bulletin/ms12-060

As stated updates included in KB2598041 which was part of security bulletin MS12-027 (http://www.microsoft.com/en-us/download/details.aspx?id=29372) are replaced with KB2687441 (http://support.microsoft.com/kb/2687441)

So important question is do you need to install this security update?

If you look under the replacement update KB2687441 then the it applies only to the host machines which have Microsoft Office installed on it. Specifically

2007 Microsoft Office Suite Service Pack 3
2007 Microsoft Office Suite Service Pack 2

Usually it is not a standard practice to install MS Office on database servers so most likely you do not need this security update.

In case you do have Microsoft Office installed on one of you database servers and have one of the following versions of SQL Server installed then you need to install this security update

Microsoft SQL Server 2000 Service Pack 4
Microsoft SQL Server 2000 Analysis Services Service Pack 4
Microsoft SQL Server 2005 Express Edition with Advanced Services Service Pack 4
Microsoft SQL Server 2005 for 32-bit Systems Service Pack 4
Microsoft SQL Server 2005 for x64-based Systems Service Pack 4
Microsoft SQL Server 2005 for Itanium-based Systems Service Pack 4
Microsoft SQL Server 2008 for 32-bit Systems Service Pack 2
Microsoft SQL Server 2008 for 32-bit Systems Service Pack 3
Microsoft SQL Server 2008 for x64-based Systems Service Pack 2
Microsoft SQL Server 2008 for x64-based Systems Service Pack 3
Microsoft SQL Server 2008 for Itanium-based Systems Service Pack 2
Microsoft SQL Server 2008 for Itanium-based Systems Service Pack 3
Microsoft SQL Server 2008 R2 for 32-bit Systems
Microsoft SQL Server 2008 R2 for 32-bit Systems Service Pack 1
Microsoft SQL Server 2008 R2 for 32-bit Systems Service Pack 2
Microsoft SQL Server 2008 R2 for x64-based Systems
Microsoft SQL Server 2008 R2 for x64-based Systems Service Pack 1
Microsoft SQL Server 2008 R2 for x64-based Systems Service Pack 2
Microsoft SQL Server 2008 R2 for Itanium-based Systems
Microsoft SQL Server 2008 R2 for Itanium-based Systems Service Pack 1
Microsoft SQL Server 2008 R2 for Itanium-based Systems Service Pack 2

How to find all tables and views used in a SQL Server Analysis services cube?

Someone asked me today if there is an easy way to find all tables, views used in a SSAS cube. You can always see XMLA file to find the tables or check the data source view in the cube definition. But we have some cube databases which have more than 100 tables and going through XMLA or DSV is time consuming.

Good thing is Microsoft provides DMVs explore metadata of a cube database. Here is a sample query to get meta data for a cube

1. Connect to the SSAS instance via SSMS.
2. Open new MDX query
3. Copy paste the query below
   SELECT * FROM $SYSTEM.MDSCHEMA_DIMENSIONS
   
   WHERE CUBE_NAME = 'cube_name'AND DIMENSION_NAME <> 'Measures'

4. Change the cube name and execute the query

SQL Server Integration Services name for clustered SQL Server installation or named instance of SQL

After you install SQL Server on an Active/passive cluster you need to provide cluster name for SQL Server Integration Services instance. Same is case for named instance of SQL server where you have to provide

Lets assume for a cluster with 2 nodes Server1N1 and Server1N2; the cluster name is Server1. Here is how your MsDtsSrvr.ini.xml should look like.

<?xml version="1.0" encoding="utf-8"?>

<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>

  <TopLevelFolders>

    <Folder xsi:type="SqlServerFolder">

      <Name>MSDB</Name>

      <ServerName>Server1</ServerName>

    </Folder>

    <Folder xsi:type="FileSystemFolder">

      <Name>File System</Name>

      <StorePath>..\Packages</StorePath>

    </Folder>

  </TopLevelFolders>

</DtsServiceConfiguration>

For a named instance is should be

<?xml version="1.0" encoding="utf-8"?>

<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>

  <TopLevelFolders>

    <Folder xsi:type="SqlServerFolder">

      <Name>MSDB</Name>

      <ServerName>Server1\InstanceName</ServerName>

    </Folder>

    <Folder xsi:type="FileSystemFolder">

      <Name>File System</Name>

      <StorePath>..\Packages</StorePath>

    </Folder>

  </TopLevelFolders>

</DtsServiceConfiguration>

MsDtsSrvr.ini.xml can be found under SQL server installation directory

C:\Program Files\Microsoft SQL Server\100\DTS\Binn

Once INI file is changed you need to restart SSIS service to it to take effect