Category Archives: Sql Server 2008/r2

Lesson learnt – changing the root drive for SQL Server cluster instance

Jun 21, 2014 21:41 ⋅ Leave a Comment ⋅

Playing around with your live SQL Server cluster is always something to be extra careful with. Especially when you have to remove some old storage from your Windows Cluster group (Service/Application). Today I had a scenario where I had to move the system databases and folders from one drive to another. So the process is as usual – change the physical file paths for model, msdb (and tempdb in my case), take the SQL Server down, copy the files to the new location (system folders like Log, FTData and database files of msdb and model), edit the SQL Server service from the SQL Server Configuration Manager and then simply boot the SQL Server. Sounds like a fairly easy job. But it has some lessons to learn – let’s explain a bit further the order –

1. Changing the system databases physical file location (MSDB, Model and tempdb) – following this link will guide you through the process. No surprises here.

2. Changing the location of the master database and ERROR log – again use the above link, just note that it is better to have the SQL Server instance down while operating.

3. Take down (if still not) you SQL Server instance. Copy the system folder Fix folder permissions – make sure you fix all permissions and inheritance on NTFS level for the copied. (Depending on your installation you might need to move the DATA, Log, FTData, Repldata folders).

4. Change registry values for the FTData folder – open the registry editor and edit the following path – HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance id>\Setup\FullTextDefaultPath

5. Change the output of your SQL Server Agent error log – here is a great article describing in details how to do it.

6. Start your SQL Server and cross your fingers everything is alright. Personally I am changing/renaming the old location of everything I moved, so I am certain that the SQL Server instance is still not using it for something.

7. At this point you should be ready to go and remove the old drive from the SQL Server cluster group. Now here is a great deal of attention you need to pay! First check this article on how to remove an unused drive from a WS 2008 Cluster. Now here is the deal – you need to take the resource offline before doing anything. Otherwise, you are getting ALL YOUR RESOURCES MOVED TO Available Storage group. Nice, huh? I actually had several more drives to remove, mounted to the root drive for SQL Server, so I took the first one, made sure it is not used (it had no files on it whatsoever) and just tried Remove from the GUI. Imagine my surprise, when an elementary enough (from my perspective) action of removing a disk made my group empty! So this disk was dependent on the root drive, which in terms was a dependency of the SQL Server, which in terms is depending on the IP and VCN (in short – everything is connected in a logical dependency chain). On top of that, the GUI asked if I am really sure if I want to delete it and if I am – then it will remove the dependencies and remove the resource from my group. Well…it did not! So, I have my SQL cluster group sitting there empty. I tried the suggestion from the above article to move the root disk back to my group, however, due to the large dependency chain, there was no simple way of doing it – it simply failed with error “Details: Error code 0x800700b7 Cannot create a file when that file already exists”.

Taking a step back on this error and looking at the logic of things, I decided that I need to move the SQL Server service resource back to the group. Well, that was not an easy task, because the resource itself is not available in the user interface. I used Get-ClusterResource (reference here) to see the exact naming of the SQL Server instance resource and then, using Move-ClusterResource (reference here) I moved the SQL Server resource to my SQL group, which in its turn took all its dependent resources with it. Voila! Everything was back in its place and the SQL Server was up and running.

 

 

 

Posted in: Database administration, How To, SQL Server, SQL Server 2008/R2

Enable additional logging for SQL Server management data warehouse collection sets

Nov 16, 2012 22:45 ⋅ Leave a Comment ⋅

Today I finally found some spare time to dig into some SQL Server management data warehouse issues we were experiencing. The collection jobs failed for no visible reason and with (believe me) no meaningful error in the logs. So I wanted to enable some additional logging and while searching, I found this (I lost the link, so I cannot reference it :\ … )

If you want to enable additional logging for any of the collection sets you are running, you can use the below statement.

update msdb.dbo.syscollector_collection_sets
set logging level = 2
where collection_set_id = <set id>

If you are wondering what set_id to use, the default ones are:

  • Server Activity – 1
  • Disk Activity – 2
  • Query Activity – 3
  • Utility information – 4

The rest can be discovered using the msdb.dbo.syscollector_collection_sets view.

The difference in logging is that actually if you enable advanced logging, you will be able to see the output of each single component of the SSIS package, used to run the upload jobs for example.

BEFORE:

AFTER:

 

Posted in: Administration, SQL Server 2008/R2, SQL Server 2012, Troubleshooting

SQL Server 2012 Virtual labs

Nov 16, 2012 10:38 ⋅ Leave a Comment ⋅

If you do not have your own lab and you want to access SQL Server 2012 state-of-the art features (such as AlwaysOn), you can easily use Microsoft Virtual Labs. I have to say it is really convenient :) And slick :) I have never used it before, but I am just taking the AlwaysOn lab and I got 3 VMs for a period of 150 minutes, Lab scenarios and additional materials.

Posted in: SQL Server 2008/R2, SQL Server 2012

SQL Server 2008 SP3 available for download

Oct 7, 2011 21:18 ⋅ Leave a Comment ⋅

It is been a long time since my last post and my list for blog article candidates constantly keep growing Smile I hope soon I will have enough time to return back to normal blogging speed.

Now I would like to share the new Service pack release for Microsoft SQL Server 2008 – list of all fixes can be found here. Actually as far as I could see those changes actually include all fixes till CU4 of SP2. So if you already have CU5 in place then applying SP3 is not really of a use. The SQL Server build version of SQL Server 2008 SP3 is 10.0.5500 (neat one, right? Smile)

Download SQL Server 2008 SP3 RTM

And happy patching Open-mouthed smile

Posted in: Installation and configuration, SQL Server 2008/R2

What is new in SQL Server 2008 R2 Service Pack 1

Jul 13, 2011 23:01 ⋅ Leave a Comment ⋅

I just patched my dev instance with Service Pack 1 for SQL Server 2008 R2 and in this post I will share some first impressions and facts about it.

After you apply the patch the first thing that comes to your attention is the version of the SQL Server. The new version of R2 with SP1 is 10.50.2500. I have to say I like that number Smile

Next thing taken out from BOL and the SP1 release notes is the modified DMV sys.dm_exec_query_stats. In SP1 we have four new columns introduced: total_rows, last_rows, min_rows, max_rows. Those columns provide information on returned rows statistics of a plan since its been last compiled.

There are couple of new interesting DMVs which might come handy from time to time:

- sys.dm_os_windows_info –> returns information on Windwos release, service pack level and language

- sys.dm_os_volume_stats –> this is a new DMF which returns information on the partitions SQL databases are hosted on. The valuable info here is the new possibility to check the free space on that partition.

[sourcecode language="sql"]
SELECT 
   vs.volume_mount_point, -- e.g. C:
   vs.volume_id,
   vs.logical_volume_name,
   vs.file_system_type,  -- e.g. NTFS
   db = DB_NAME(f.dbid), 
   [file] = f.[name],
   f.[filename],
   file_size_MB = f.[size] / 128,
   drive_size_MB = vs.total_bytes/1024/1024, 
   drive_free_space_MB = vs.available_bytes/1024/1024, 
   drive_percent_free = CONVERT(DECIMAL(5,2), vs.available_bytes * 100.0 / vs.total_bytes),
   vs.supports_compression, 
   vs.supports_alternate_streams, 
   vs.supports_sparse_files, 
   vs.is_read_only, 
   vs.is_compressed
FROM
   sys.sysaltfiles AS f
CROSS APPLY
   sys.dm_os_volume_stats(f.dbid, f.fileid) AS vs
WHERE
   f.dbid < 32767
ORDER BY
   drive_percent_free DESC,
   db,
   [file];
[/sourcecode]

*The above script is taken from the original post of Aaron Bertrand without any change whatsoever

- sys.dm_server_registry –> this one is AMAZING. It has been a nightmare if you want to check any SQL Server related registry setting. Now you are able to check many of them just using this simple DMV

- sys.dm_server_memory_dumps –> this one is keeping info on SQL Server dumps created during a crash so you can find easily the relevant info on the dump such as filename, when it was created and so on. At least now you do not have to browse to the error log folder and browse through all the files there looking for the one created around the crash you’ve been investigating.

- sys.dm_server_services –> another just marvelous new DMV! With this one you can check all running services on the given server, their startup type, startup account, startup time and so on. Just keep in mind that this DMV just lists SQL Server and SQL Server Agent services.

– sys.dm_os_sys_info –> this one is not new but has two new very important columns concerning virtualization – virtual_machine_type and virtual_machine_type_desc.

The rest of the stuff is for Extended Events and so far this is not my strong side so I will not just copy/paste the new stuff here trying to impress you Smile

Having said that I already have some great improvements in scripts I am regularly using in reports or ad-hoc information collection. The bad thing is that I still cannot lay hands on many (or more than one actually) SQL Server 2008 R2 SP1. I guess that is soon to be changed!

Posted in: SQL Server 2008/R2

SQL Server 2008 R2 Service Pack 1 Released for download

Jul 13, 2011 00:31 ⋅ Leave a Comment ⋅

Following the release news around SQL Server it seems that this week is quite busy with the CTP3 of SQL Server Code Name “Denali” and now the brand new Service Pack for SQL Server 2008 R2.

You can download it here.

So what is new in SP1? Well I did not have time to install it (still downloading Smile ) but according to some articles there is going to be a change in the DMVs we are so used to and keen on. One of them would be “sys.dm_exec_query_stats DMV which is extended with additional columns to improve supportabilities over troubleshooting long-running queries”. Of course there is and quite a long list with fixed bugs but I will leave to you to check them out here.

Posted in: SQL Server 2008/R2

How to check what SQL Server network protocol is used by connections

May 28, 2011 18:12 ⋅ 1 Comment ⋅

In this post I would like to share a simple query showing currently running statements (using sys.dm_exec_requsts DMV) along with the connection type, address and network protocol used form the session, executing this statement. The script itself you can find below:

USE [master]
GO
SELECT [servername]= @@servername, 
command,
s.text,
start_time,
connect_time,
net_transport,
protocol_type,
auth_scheme,
client_net_address,
client_tcp_port, 
local_net_address,
local_tcp_port 
percent_complete, 
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time 
FROM sys.dm_exec_requests r
inner join sys.dm_exec_connections c
	on c.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s

Now why would you need that – well, sometimes applications suffer from the fact that the IIS they are using for example, connect to the SQL using Named pipes over network (yes, this is possible). Despite it should be controlled from the application’s connection string, this is not always true and it is left to the DBA to identify and restrict such scenarios as it can really hurt performance or lead to “general network errors” we have all seen. For additional information on SQL Server protocols, their use and explanation – please visit this MSDN article.

Additionally – here is a small article describing how to define connection protocol in the connection string: http://www.connectionstrings.com/Articles/Show/define-sql-server-network-protocol

I am writing this post as for last several weeks I am involved in an application issue that for me was quite difficult to troubleshoot and track down the issue. The application (a simple IIS one) was raising timeout and connection errors such as “[Microsoft][ODBC SQL Server Driver]Communication link failure SQL State: 08S01 EDL error… While investigating with the application team, though, we noticed one strange thing when collecting trace data – despite the application was using SQL login in the SQL Server Profiler trace column NTUserName was sometime blank, sometimes with ANONYMOUS LOGON user. This came very strange to me because I would expect this column to be always blank when we are using SQL Authentication. However I tracked this “effect” to be due the network protocol, used by the connection – Named pipes. It turned out that when Named pipes is used, the connection is triggering NT authentication for the user performing the connection. If (as in our case) this is IIS with identity configured as Network Service, then ANONYMOUS LOGON is the expected result. So as a bottom line – in order to find out the network protocol used by a certain remote connection/session, you can use the query above (extracting running statements with connection info), the DMV sys.dm_exec_connections or use SQL Server profiler. For Profiler have in mind that:

  • if NTUserName is blank and in LoginName you have SQL Login – then it is SQL Authentication through TCP/IP
  • if NTUserName is not blank and in LoginName you have SQL Login – then it is SQL Authentication Over Named Pipes
Posted in: Database administration, SQL Server 2005, SQL Server 2008/R2, SQL Server 2012, Troubleshooting

Creating dual Y-axis chart in SSRS 2008

Mar 18, 2010 19:55 ⋅ 4 Comments ⋅

In this post I will show you how to create a simple report with dual Y-axis column chart. It is a very common requirement in reports and to be honest – quite difficult to find out how. I’ve been struggling for a while to get to the desired result. First let me share what kind of data I prepared for my demo and what will be the end result.

1. The data – it as a simple dataset with three columns – one for month and two for measures.

select 'Jan' as Month,10200 as Measure1, 11528 as Measure2
union all
select 'Feb' as Month,6247 as Measure1, 3098 as Measure2
union all
select 'March' as Month,5982 as Measure1,6814 as Measure2
union all
select 'Apr' as Month,5200 as Measure1, 2528 as Measure2
union all
select 'May' as Month,5000 as Measure1, 1000 as Measure2
union all
select 'Jun' as Month,4000 as Measure1, 413 as Measure2

2. The end result:

5_1

 

So how to do it? Of course First you create a new report and enter the dataset.

1) Add a simple column, add Month filed to the Category box and Measure1 and Measure2 – to Data box

5_2

2) Right click on the second data series (in my case – the yellow columns). Right-click on the selected series and go to Series Properties –> Axis and Chart area.Change the value axis to Secondary and hit OK. The result should be in showing a second axis on the right of the chart just like below

5_3

3) After adding the secondary axis, again select the second series (yellow ones), right-click on it and go to Change Chart Type. Then select some of the line chart templates and hit OK button. I am using the first and the simplest one.

5_4

And that is how you can create a dual Y-Axis column chart which can help you in better visualization of certain data. Of course till getting the report done for end-user running, you can add some labels, include Markers and Data Labels for better reading. But it is up to you and your organization’s requirements how functional and “colorful” a report to be.

Posted in: Business Intelligence, SQL Server 2008/R2, SQL Server Reporting Services