Downloading that cumulative update or hotfix…

I recently answered a question on Twitter regarding the availability of Cumulative Update downloads for specific platforms.

Starting from SQL Server 2005, our Release Services team moved to an Incremental Servicing Model to deliver fixes for issues identified on current releases of SQL Server[s]. We now release a Cumulative Update package for a Service Pack periodically which contains all the fixes released since the RTM version of the Service Pack and hence the name “Cumulative Update”.

 image

 image

Now you click on the link mentioned in the picture below, you will get an option to download the packages associated for all the platforms.

image
I know this is quite trivial information but I have answered this question a few times and hence the post.

Removing primary transaction log file

Recently I was discussing about removing transaction log files on the #sqlhelp hashtag on Twitter. The question was whether we could remove an existing transaction log file after adding a new one. During the course of the discussion, I learnt that the file that was being removed was the primary transaction log file of the database.

When you attempt to remove the primary log file of a database from Management Studio or using ALTER DATABASE command, you will receive the following error:

Drop failed for LogFile ‘dbLogTest_log’.  (Microsoft.SqlServer.Smo)

The primary data or log file cannot be removed from a database. (Microsoft SQL Server, Error: 5020)

This is by-design. We do not allow the removal of the primary transaction log file of user databases. This was first introduced in SQL Server 2000.

Tibor (Blog), a SQL Server MVP, has already blogged about how to remove transaction log files using T-SQL commands here. So, I am not going to duplicate the post here again by providing the same T-SQL commands.

The GenNext for SQL Server

The SQLPASS Keynote on Day 1 of the Summit kicked off a slew of announcements which would ensure that Twitter received it’s fair share of activity for SQL Server. Look up the #sqlpass hashtag on Twitter to understand what I am saying. There are over 4,500 registrations from 49 countries for the streaming keynotes.

Among all the announcements, the one that created the maximum amount of buzz was the next major version of SQL Server, code-named Denali CTP1. As soon as Ted Kummert, Senior VP at Microsoft, made the announcement, a flurry of Tweets went out from the Tweeps attending the Keynote announcing to the world about Denali. SQL MVP Aaron Bertrand (Twitter | Blog) posted walkthrough for installing Denali on his blog. Read his narration of the Keynote session here.

The key features of Denali are:

  • SQL Server AlwaysOn – A Mission Critical Platform that helps in increasing up-time, reduce unplanned downtime due to OS patching, simplify High Availability management, support for multiple secondaries and multi-site clustering.
  • Column-based query accelerator – This will help improve query performance manifold and reduce the time taken for performance tuning.
  • SQL Server Tools code-named “Juneau – A unified environment which is a one-stop-shop for your developers and helps accelerate your time-to-market.
  • IT administration enhancements – Improved security facilitated through various improvements built into the product. You can now take advantage of the powerful XEvents to monitor SQL activity.
  • Beyond relational enhancements – Performance improvements for FileStream, Full-text search, 2D-Spatial Support and FileTable.
  • Pervasive Insight – Expand the use of Business Intelligence to business users by providing new and engaging ways to discover insights through a drag-and-drop interface, smart and powerful data querying and interactive storyboarding to allow users to quickly create and share visual presentations of large datasets.
  • Read more about it here.

    Other announcements include:

    Microsoft Code-name “Atlanta”

    Microsoft codename Atlanta is a secure cloud service that proactively monitors your Microsoft SQL Server deployments. It helps you avoid configuration problems, reduce downtime, improve performance, and resolve issues faster. Atlanta agent can analyze both versions x86 and x64 versions of SQL Server 2008 and above. Now you get a vital insight into multiple known issues that you could plague your SQL Server installation without having to manually check for known issues. Atlanta will re-define pro-active monitoring for SQL Server instances.

    SQL Server 2008 R2 Parallel Data Warehouse (PDW)

    Microsoft SQL Server 2008 R2 Parallel Data Warehouse (previously code named project “Madison”) is a highly scalable appliance that delivers performance at low cost through a massively parallel processing (MPP).

    New Path to Microsoft Certified Master: Microsoft SQL Server 2008

    If you have an aspiration to earn the coveted title of a Microsoft Certified Master for SQL Server, then there is a new path to do so. Click on the above sub-title to learn more.

    If you feel that you need more food for thought, then read the Press Pass: Microsoft Introduces New Offerings for Business Intelligence and Mission-Critical Workloads

     Watch Rushab Mehta, SQL Pass President, and Ted Kummert, Senior VP at Microsoft, discuss SQL Server:

    

    Data Type Mapping for OLEDB Providers

    I recently had a question on the #sqlhelp hashtag on Twitter regarding how OLE DB Providers map the data columns to SQL Server data types.

    The Data Type Mappings for SQL Server for distributed queries are mentioned here. The DBType values for each data that you are retrieving from a non-SQL data source like Oracle, Excel, Access can be found out using RowSet Viewer. The Microsoft® Developer Network (MSDN®) Platform SDK contains an OLE DB RowsetViewer sample application written in Microsoft Visual C++®. This application enables you to connect to either the Microsoft OLE DB Provider for AS/400 and VSAM or the Microsoft OLE DB Provider for DB2, open a table window, type the host file name or DB2 database, return a rowset, and browse the contents.

    Using Rowset Viewer you can get the DBType of each column returned from the remote data source. The function used is IColumnsRowset::GetColumnsRowset. Using this you can create the necessary schema on the SQL Server database which will act as the destination for the data received from the remote data source.

    Happy DB Scheming! Smile

    How fetch sysaltfiles information using Powershell

    I recently have developed an affinity for using Powershell. I saw a question on #sqlhelp hashtag for fetching database properties using Powershell. There are multiple posts out there on the web to do this using SMO. A crude way would be to use the Invoke-Sqlcmd cmdlet to do this.

    Command:

    Invoke-Sqlcmd -Query "SELECT filename,size,dbid FROM sys.sysaltfiles;"

    If you wanted a cleaner output or some post processing done on the results fetched and wanted to use foreach, them this could also be done:

    Example:

    $dbprop = Invoke-Sqlcmd -Query "SELECT filename,size,dbid FROM sys.sysaltfiles;"
    foreach ($db in $dbprop)
    {
    Write-Host $db.filename
    }

    If you are using SQLPS, then the above command to give the information that you want by invoking SQLCMD using Powershell.

    Other ways to do this are mentioned here:

    Get SQL database size using Windows Powershell

    Get database properties using PowerShell in SQL Server 2008 by Tim Chapman (Blog)

    How to find out BINN folder path using WMI

    A reply to a Tweet on #sqlhelp prompted me to look this up. The question was on finding out the SQL Server BINN folder path. This can be done using WMI in the following manner for SQL Server 2008:

    
    strComputer = "."
    
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement10")
    
    Set colItems = objWMIService.ExecQuery( _
    
    "SELECT * FROM SqlService WHERE SQLServiceType = 1 and ServiceName = 'MSSQLSERVER'",,48)
    
    For Each objItem in colItems
    
    Wscript.Echo "-----------------------------------"
    
    Wscript.Echo "SqlService instance"
    
    Wscript.Echo "-----------------------------------"
    
    Wscript.Echo "BinaryPath: " & MID(objItem.BinaryPath,1,InStr(objItem.BinaryPath,"sqlservr.exe")-1)
    
    Next
    
    

    The SQL Server 2008/R2 WMI namespace has visibility for SQL Server 2005 also. So the above snippet of code can be used to retrieve the BINN path (PathName property contains the fully qualified path to the service binary file that implements the service) for the SQL instance. You can change the SQLServiceType to a different value to get the Binary Path folder for other services as well. The value is the above code is set to 1 for the Database Engine. The ServiceName parameter can be used to filter down the results.

    For a named instance, you would need to change the service name to MSSQL$INSTA if your instance is called INSTA.

    The same is possible through the much talked about Powershell as well.

    Other ways to do this would be to use the xp_instance_regread XSP which is not recommended as it is an undocumented command. An example is show here.

    Thanks to @afernandez for pointing out another way through a CLR TVF.