SQL server

SSRS not working properly after upgrade to IE10

I was doing some work on SSRS (SQL Server Reporting Services) recently, and found that some of the features were not working as they should. I use IE (Internet Explorer) to do my report management functions, but for some reason, I was not able to see drop down lists or certain menu items.

I was checking my settings and then I suddenly remembered that an automatic update had kicked off on my laptop. I checked and found that this auto update upgraded IE from version 9 to 10. Now IE 10 for some reason does not support all the features of SSRS! I was using 2008 R2.

The good news is that there is an easy fix to this situation. Open up IE and hit the F12 key to open up the developer tools (also available under the tools menu). A developer window will open up on the bottom of the IE screen. Click on the Browser Mode: IE10 menu item and select IE 9. Your browser window will be refreshed and you should have all the features of SSRS back to how they were.


SQL server, SQLServerPedia Syndication

SQL server edition comparisions.

I had created an old post about Enterprise only features of SQL server. As I found out interesting features, I added it to that old post. Today, I found out an MSDN link that has a feature comparison table which summarizes everything I was trying to capture in that post.

The link above is for the 2008 R2 version. You can switch between 2008, 2008 R2, and 2012.

As of 2012, the different editions of SQL server are:

  • Enterprise
  • Business Intelligence
  • Standard
  • Web
  • Express with Advanced Services
  • Express with Tools
  • Express

I thought the PDW was like the top most edition, but apparently, it is marketed as an appliance.

SQL server, SQLServerPedia Syndication

Changing the name of the SQL server (@@servername)

I was in the process of installing SCOM2012 and came to the part where I had to specify the SQL server name. I quickly logged onto the SQL server and ran SELECT @@servername to double check the SQL server name. Surprisingly, the server name was wrong. The OS server name was sqlcloud, but the SQL server name was testserver.

The SQL server name will usually be the name of the host/physical server. In my case the name was showing something different. I tried to add my server name using the query: sp_addserver ‘sqlcould’, ‘local’; but got the error:

Msg 15028, Level 16, State 1, Procedure sp_addserver, Line 74

The server ‘sqlcould’ already exists

For some reason the right name was stuck up somewhere, and not allowing me to change to the correct name. What to do in this scenario is to drop the server name (sqlcould and testserver) and then add back only sqlcloud.

To change the server name, do the queries below:

sp_dropserver ‘testserver’; sp_dropserver ‘sqlcould’ — remove the conflicting names (don’t drop any linked servers)

sp_addserver ‘sqlcould’, ‘local’ — add in the correct name

Take extra care not to go ahead and drop all the server names you find. Some of them might be linked servers that are required by queries/functions.

After restarting the SQL server I was able to see the correct server name.

Other topics

TRIM (Trimming) for SSDs.

In Windows 7 and newer operating systems, the TRIM command can be used to maximize the life and performance of Solid State Drives (SSDs). Using this command is known as  trimming the SSDs. This command is useful as SSDs operate differently (internally) from HDDs (Hard Disk Drives). The TRIM command will notify the SSD on which blocks no longer contain data that needs to be persisted, and a few low level operations later, the data on these blocks can be safely deleted.

In windows, to see if you have trimming enabled, run the following command on the command line:

fsutil behavior query DisableDeleteNotify

If the result is a 0, it means that TRIM is enabled. A 1 indicates that TRIM is not enabled. To enable trimming, run the command below:

fsutil behavior set DisableDeleteNotify 0

Happy trimming.

SQL server

EXECUTE permission was denied on the object ‘xp_logevent’

If you are faced with a similar error below:

The EXECUTE permission was denied on the object ‘xp_logevent’, database ‘mssqlsystemresource’, schema ‘sys’

A quick fix can be used. Please make sure there are no security concerns with granting this access to the respective account. There will an account that is running a script that is returning this error. Let’s say that account is domain\user. Run the query:  grant execute on xp_logevent to [domain\user] in the context of the master DB.

This should resolve the issue.

SQL server

Log shipping monitoring and status.

So you have setup log shipping between a primary and a secondary server. Everything went smoothly and the secondary DB is being updated with transaction logs. Now what? I.e. how do you monitor the status of the log shipping? What is the throughput? What is the current status of the log shipping backup/copy/restore?

I have researched and collected a set of commands that helps me with finding this information. They were tested on SQL 2005.

Task: First I need to know how many DBs on this server are being log shipped?
use msdb
select * from log_shipping_monitor_primary
The primary_database column will give you the names of the DBs. The last_backup_file column will show you the location to where the transaction logs are being backed up to. The last_backup_date will show you the date of the last log file that was backed up. This date will come in handy as you can find out if you have any old log shipping jobs that you do not need anymore.  I can then go to the last_backup_file location and possibly clear up a lot of space.

Task: Where are the secondary servers to these primary DBs?
use msdb
select * from log_shipping_primary_secondaries
The secondary_server column will give you the name of the server(s) to which your logs are being shipped to.

Task: How has log shipping being doing so far?
use msdb
select * from log_shipping_monitor_history_detail
The agent_type column stands for: 0 = Backup,1 = Copy, 2 = Restore. The session_status column stands for: 0 = Starting, 1 = Running, 2 = Success, 3 = Error, 4 = Warning (so look for 3 and 4). The message column will give you some detail on what the issue is.

Task: Are there any current issues with the log shipping?
use msdb
select * from log_shipping_monitor_error_detail
This table will return a list of any errors associated with log shipping. You can filter by the database_name if you are concerned with any DB in particular. I usually order using log_time desc. Once again, the message column will give some helpful information.

Armed with these queries, you will be able to take over a new server and get up to speed with the log shipping setup on that server. I have referenced the system tables that related to log shipping. There are SPs that you can use which will be more dynamic across the different versions of SQL. My next step will be to measure log shipping performance/ throughput which I will blog about later.

The BOL link containing detailed information about log shipping monitoring is here.


SQL server

Moving a database between SQL instances.

Moving a DB between two SQL instances can be done with a simple backup and restore, but there are a number of dependencies on the DB that are required to be working to have a full cycle connection between the end user and the DB. One example is the server level logins which are not moved with the DB. This data is saved in the master DB and will have to be explicitly copied over and linked with the DB users in the new instance. Another example is the SQL jobs there were running on the first instance. These jobs are saved in MSDB  and need to be recreated on the second instance. There are others like encryption keys, full text settings, etc.

Below is a list of entities/objects that are stored outside the DB:

  • Server configuration settings
  • Credentials
  • Cross-database queries
  • Distributed queries/linked servers
  • Encrypted data
  • User-defined error messages
  • Event notifications and Windows Management Instrumentation (WMI) events (at server level)
  • Extended stored procedures
  • Full-text engine for SQL Server (MSFTESQL) service properties
  • Jobs
  • Logins
  • Permissions
  • Replication settings
  • Service Broker applications
  • Startup procedures
  • Triggers (at server level)

This list was compiled on BOL and the details of these items can be viewed here.