When was my SQL Server Database last accessed?

Few days back, one of my friends asked me whether there is a way to find the last accessed date of their SQL Server databases. What he really wanted was, finding out databases that are not being used. Nothing came into my mind immediately. I tried to remember a catalog view, a dynamic management function for this but nothing popped up.

Few things came to my mind later. First thing was SQL Server Auditing. I thought that there will be an AUDIT GROUP for database login. Bad luck, it is only available at server level only. I found out few posts related to this who have used DATABASE-LEVEL groups to track statements like SELECT, and most of the posts are for seeing the last access for tables, not for the database. Again, I thought it is bit difficult to implement, because my friend manages more than 60 databases :). So, I gave up the idea of using SQL Server Auditing for finding out the last accessed time for databases. If anyone has used auditing for seeing the last accessed date for databases, please let me know the way. Anyway, I ended up with this, though it has a problem too.

SELECT DatabaseName, MAX(LastAccessDate) LastAccessDate

FROM

    (SELECT

        DB_NAME(database_id) DatabaseName

        , last_user_seek

        , last_user_scan

        , last_user_lookup

        , last_user_update

    FROM sys.dm_db_index_usage_stats) AS PivotTable

UNPIVOT 

    (LastAccessDate FOR last_user_access IN

        (last_user_seek

        , last_user_scan

        , last_user_lookup

        , last_user_update)

    ) AS UnpivotTable

GROUP BY DatabaseName

HAVING DatabaseName NOT IN ('master', 'tempdb', 'model', 'msdb')

ORDER BY 2

What I have used here is sys.dm_db_index_usage_stats. This goes through all indexes (including heap) and shows last accessed time for them. By looking at these accessed time, we can determine the last accessed time for the databases, and can see whether the databases are being used or not. Here what I see when I run the script:

Drawbacks
Biggest problems with this technique is, if the SQL Server service is restarted, everything is reset. So, this is not applicable for servers which get restarted frequently. Other problem is related to Full-Text-Catalogs. If you have enabled Full-Text-Index on your tables, you might see a record saying that database has been accessed without any user operations. This is because of Full-Text-Indexes.

I’m fed up

I have worked with many products/projects and have played many roles in projects such as developer, tech-lead, DBE, DBA, project manager :), associate software architect, business intelligence architect, etc. Almost all projects I had involved were successful (so far, proudly say, NOTHING has been discarded but extended and enhanced), not because of me :), because of the strength of team, because of the proper design of the system, because of the collective decisions. Once a project is deployed, if you are a part of the team, you know that how it pleases your heart. It makes you proud, gives you industrial recognition, and knowledge (and salary increment if you are lucky :)). What if the project would be a failure :(………?

So far I do not have such an experience. If you have, please share the reasons for it and how you felt on it. It helps all of us to come back to the track if we are out of it.

What made me to make a post like this? I am bit fed up now. Currently I am working on few projects, some of them are Business Intelligence projects, Training related projects, some are DBA-Specific projects, and ETL projects. My involvement for these projects are in many ways such as a developer, an architect, a lecturer, and a consultant. What bother me? What annoy me? Here are some of them:

• Your are NOT the part of the project but have to be responsible
• Major changes after the design is completed, done, again changes, again, again, …… continues
• I see something as wrong, but everyone keep their mouth shut, pretending “something” is NOT wrong
• No one is responsible…… (who cares, as long as we get the salary at the end of month)
• Do the delivery as “someone” want, not as “I” want
• You have not done the job, wonder whether you are qualified enough
• “Someone” has done a mistake, you have been irresponsible, did not you see she/he making the mistake?
• Don’t talk, your words make this upset… later… why did not you point it out?
• etc.

Hello, did I say that all projects give me all these types of headaches? NO, that is not the case. I still enjoy with many but, you guys know, if there is one, I might be losing the balance going over the stepping-stones. So, wish me luck!

Reporting Services 2008: Report Templates

If you are working (or have worked) with a Reporting Services project, you know that most of the reports have similar characteristics such as items in header and footer. Are you spending time for adding such similar things for all reports? If yes, this post will be helpful to you.

This process can be speeded up by having a report with all common items as a template. What you have to do is, create a report with all common items and save it as something like ReportTemplate1.rdl.

Once you saved the file, you need to take the RDL file and place in template folder which is {drive}\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject.
Done. Open a Reporting Services project and open Add New Item dialog box. You should see the newly added report as a template. Get it and continue with your report creation.

Free SQL Server 2008 R2 eBook

Are you interesting in SQL Server 2008 R2 resources? Here is a good resource. It is a free eBook, published by Microsoft Press. Click on the image below for downloading.

SSRS 2008 R2: Sri Lanka Map Report

Just created a Polygon-Layer Sri Lanka map using SQL Server 2008 R2 for showing population by provinces. It is fun to work with R2, I will share the way of creating this with an article soon.

SSRS 2008: Disabling “Go to Report” action through expression

Have you ever come across a situation where you need to disable an action set to a series in Reporting Services? If so, you might know that Action property cannot be set with expressions. It has to be set through Series Properties and select either None or Go to report (or other). If you have the Action as  None, you cannot set it back to Go to report via an expression.

One of my colleges faced a situation where he had to disable the action based on a report parameter value. What he had done was, set the Action as Go to Report and had below expression in Specify a report.

   1: =iif(Parameters!Parameter1.Label="All", "", "NewReport")

This worked fine. It did not allow user to perform the action if the condition is satisfied. Problem arose when a tootip is set. If a tooltip is set to the series, it shows that action can be performed when the mouse is moved over, and leads to an error because it tries to open a report which has set with NO name. This can be sorted out too.

   1: =iif(Parameters!Parameter1.Label="All", Nothing, "NewReport")

This expression solved the problem. All I had to do is, replace the “” with Nothing.

Complete Farm SharePoint 2010 installation on Single Server using non-domain accounts

When I was installing and configuring SharePoint 2010 in my machine, the following error appeared, stopping my installation.

The specified user Administrator is a local account. Local accounts should only be used in stand alone mode.

The reason was, I was trying to install and configure complete farm installation on standalone machine. My machine is not a domain controller or connected to a domain. However I found a way to continue my installation. It explains here.

All you have to do is, do not continue the installation with configuration wizard, stop it and as above link explains, use New-SPConfigurationDatabase with PowerShell for specifying none domain account for the farm. Once added, start configuration with SharePoint 2010 Products Configuration Wizard which is in StartMenu – Microsoft SharePoint 2010 Products.

Configuring Windows 2008 R2 for SharePoint 2010

If you are planning for installing SharePoint 2010 on Windows 2008 R2, here are some installations should be done before starting the SharePoint 2010 installation.

• Open Server Manager and click on Add Roles.
• Select Application Server. When selected, it prompts for “Add Required Features”, accept it and continue.
• Under Application Server – Role Services, make sure below items are selected:
• .NET Framework 3.5.1
• Web Server (IIS) Support
• TCP Port Sharing
• HTTP Activation
• TCP Activation
• Named Pipes Activation
• Install SQL Server 2008 (or R2) as the default instance.
• Install ADOMD 10. You can download it from here.
• Install Geneva Framework Runtime. You can download it from here.
• Update windows via Automatic Updates.
• Install the hotfix Windows6.1-KB976462-x64. You can download it from here.

Once you have all installed, you should be able to start and continue SharePoint installation without any issue. Have fun :).

Reporting Services Error on Doughnut Chart when Tooltip is set: Fixed in 2008 R2?

If you have read my previous post “Reporting Services Error on Doughnut Chart when Tooltip is set: Parameter is not valid”, you know about this error. I just checked this with 2008 R2 and, wow, it has been fixed it seems. Great.

Reporting Services Error on Doughnut Chart when Tooltip is set: Parameter is not valid

One of my colleagues had been facing a funny issue since yesterday, and I had to find a solution for it. It is related to Reporting Services 2008. He had created a doughnut chart that shows some values for some categories and had set the tooltip for the series. The data source he had used was Analysis Services cube. Report preview was working fine but he got the issue when the mouse is moved over it too see the tooltip. Whenever the mouse is moved over the doughnut, chart is disappeared and error is shown as Parameter is not valid. This does not happen when the tooltip is NOT set. Funny?

I am not sure whether it is a bug or it is the standard behavior of it. As usual I googled to see a solution but no luck. Fortunately I found the issue. It is because of some categories contain values “0”. Since “0” values are not displayed with doughnut charts (this is same for pie charts too), it throws this error when the mouse is moved over it. Once the zero values are excluded from the data set, it started working fine.

Interesting, let me show you how it comes. Create a report and add a dataset with following query.

   1: SELECT 'A' AS Type, 200 AS Value

   2: UNION

   3: SELECT 'B' AS Type, 150 AS Value

   4: UNION

   5: SELECT 'C' AS Type, 0 AS Value

   6: UNION

   7: SELECT 'D' AS Type, 40 AS Value

   8: ORDER BY 1

Then add a doughnut chart to the layout and drag the “Type” and drop onto Category Field Section. Next, drag the “Value” and drop onto Data Field Section. Now get the “Series Properties” of the “Value” which is on Data Field Section. Set the “Tooltip” as “[Value]”. Click on Preview to see the chart. You should see something like this.

Move the mouse over it now. You should see this.

If you remove the third record from the dataset, you will not get this issue. Let me share the MDX query which had been written for the original chart and fixed one.

   1: // Old query. It returns some members with zero values

   2: SELECT NON EMPTY { [Measures].[Measure1] } ON COLUMNS

   3:     , NON EMPTY { ([DimDimension].[Dimension1].ALLMEMBERS ) } 

   4:         DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 

   5: FROM [Cube]

   6:  

   7:  

   8: // Modified query. It does not return members with zero values

   9: WITH MEMBER [Measures].[Measure1New] 

  10:     AS 'IIF([Measures].[Measure1] = 0

  11:             , NULL

  12:             , [Measures].[Measure1])'

  13:  

  14: SELECT NON EMPTY { [Measures].[Measure1] } ON COLUMNS

  15:     , NON EMPTY { ([DimDimension].[Dimension1].ALLMEMBERS ) } 

  16:         DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS 

  17: FROM [Cube]

Note that you will NOT get this issue if you enable 3D Effects on Doughnut :).
You may know some different reasons for this and different workaround for this. If so, share with us.

What is the correct way for setting values for datetime?

How do we make sure that we accept values for datetime columns correctly? Has the month stored as it has been sent? Has the day stored as it has been sent? As long as you control the client application and SQL Server modules, you are sure about SQL Server’s identification of each components of datetime value. What if SQL Server accepts values from third-party application? Will they always send the datetime value as you asked?

Here is an example. The variable @datetime accepts a value. Assume that you accept datetime values in mmddyyyy format. If SQL Server works on default setting, it will recognize the moth of value as July with first setting. If you get it in different format, as showed in second setting, SQL Server will take some other part as the month and store, which is wrong in this scenario.

   1: DECLARE @datetime datetime

   2:  

   3: SET @datetime = '7/6/2010 2:30:00 pm'

   4: SELECT DATENAME(M, @datetime) AS MonthOfDate

   5:  

   6: SET @datetime = '6/7/2010 2:30:00 pm'

   7: SELECT DATENAME(M, @datetime) AS MonthOfDate

We can force SQL Server to always read the values as mmddyyyy by setting the DATEFORMAT. But, again, if client application sends it in different format, there is a chance of storing wrong values.

   1: SET DATEFORMAT mdy

   2: GO

   3: DECLARE @datetime datetime

   4:  

   5: SET @datetime = '7/6/2010 2:30:00 pm'

   6: SELECT DATENAME(M, @datetime) AS MonthOfDate

   7:  

   8: SET @datetime = '6/7/2010 2:30:00 pm'

   9: SELECT DATENAME(M, @datetime) AS MonthOfDate

What would be the best way? Why do not we stick with international standard? Yes, SQL Server accepts datetime values in ISO 8601 format. If accept values from third-party application, we can ask them to send the values in ISO 8601 format. The format of it is YYYY-MM-DDThh:mm:ss.

   1: DECLARE @datetime datetime

   2:  

   3: SET @datetime = '2010-07-06T14:30:00'

   4: SELECT DATENAME(M, @datetime) AS MonthOfDate

If SQL Server receives values in ISO 8601 format, it ignores all datetime related format such as DATEFORMAT, LANGUAGE and accepts the first part as the Year, second part as the Month, and third part as the Day. Note that date and time part has to be separated with “T” and time format should be in 24-hour format.

MDX Error with OLEDB: The Parameter is incorrect

If you have read my previous post, you know why I went for “OLE DB - Microsoft OLE DB Provider for Analysis Services 10.0” instead of “Microsoft SQL Server Analysis Services - .NET Framework Data Provider for Microsoft Analysis Services” for querying data from a cube into SSRS reports. Unfortunately I have to give up the OLE DB Data Source Type because it does not support for parameterizing the MDX query. When a parameter is added, I get following error:

An error occurred during local report processing.
An error has occurred during report processing.
Query execution failed for dataset ‘DataSet1’.
The following system error occurred: The parameter is incorrect..
Error Code = 0x80070057, External Code = 0x00000000:.

As usual, I googled and found that it has been reported on January 2007 and has identified as a bug in .NET’s System.Data.OleDb. The thread has been updated again on April 2008, and seems not fixed. You can read the thread here.

Anyway, since it is related to .NET, I am going to start searching on .NET updates and fixes on this. I will share if I can find something and solve this issue with it.

MDX Error: First Axis of the query contains dimension other than Measure Dimension…

If you try to set some other dimension elements as the first axis other than measure dimension, you will receive an error like below:

I got this error with Reporting Services when I try to query the OLAP database since I had referred some other dimension in the first axis. How do we fix this:

All we have to do is, change the Data Source type for OLE DB and select the provider as Microsoft OLE DB Provider for Analysis Services 10.0. You may see some limitations with this :(.

Missing ADOMD.NET and SQLNCLI: PerformancePoint on Windows 2008 R2

If you see a “missing” issue with ADOMD.NET and SQLNCLI when configuring PerformancePoint 2007 on Windows Server 2008 R2, Download the latest releases from here and configure.

This is the Feature Pack for Microsoft SQL Server 2005 – December 2008. In addition to above files, it contains few more, including Microsoft OLEDB Provider for DB2 too.

SQL Server User Group Meeting – March 2010

Sri Lanka SQL Server User Group meeting will be held on Wednesday, 17th March, at Microsoft Sri Lanka. Dinesh Asanka will be doing a session related to Index-Filtering and Susanth Silva will be doing the next session on SQL Server Clustering. Read more on this…

If you interest on these subjects, want to learn, participate. As usual, we have great give-away items, pay attention and grab them.

Guys, http://sqlserveruniverse.com is up!!! Thanks for Gogula for hard working on this, and thanks for Preethi and Dinesh Asanka for helping him on this.

Does your DELETE operation deallocate empty data pages?

When we execute DELETE operations against tables, SQL Server is supposed to delete records and deallocate emptied, associated data pages. Deallocation is done, in order to make those emptied data pages available for other objects. If the pages are not released, the spaced used by the deleted records will be unusable. Because of that we need to make sure that pages are deallocated.

Why bother? SQL Server deallocates empty pages at the deletion!!! Yes, but there is an exception. It might not deallocate empty pages if the table is on the Heap. This is because SQL Server places locks on rows and pages when deleting on the heap, resulting empty pages remained allocated. Let me show you with an example:

Let’s create a table and insert some records. Note that no clustered index is defined, hence table is created on the heap.

   1: CREATE TABLE TestTable

   2: (

   3:     Id int identity(1,1)

   4:     ,Description varchar(4000)

   5: )

   6: GO

   7: INSERT INTO TestTable

   8:     (Description)

   9: VALUES

  10:     (REPLICATE('A', 4000))

  11:     ,(REPLICATE('B', 4000))

  12:     ,(REPLICATE('C', 4000))

  13:     ,(REPLICATE('D', 4000))

  14:     ,(REPLICATE('E', 4000))

  15:     ,(REPLICATE('F', 4000))

  16:     ,(REPLICATE('G', 4000))

  17:     ,(REPLICATE('H', 4000))

  18:     ,(REPLICATE('I', 4000))

When the table is queried, while IO statistics are on, you can see that 5 pages have been read for retrieving data.

It can be further checked by using DBCC IND too. When execute, it lists out 6 rows; 5 rows representing data pages (PageType = 1) and 1 row representing the IAM page (PageType = 10).

   1: SET STATISTICS IO ON

   2: SELECT * FROM TestTable

   3: SET STATISTICS IO OFF

   4:  

   5: DBCC IND('tempdb', TestTable, -1)

Now delete all records and run above codes again. You will still see that it reads 5 pages for data retrieval. If you run the DBCC IND again, you will see that same pages numbers are still allocated to this table. If you need to check and see whether the pages contain data, try with DBCC PAGE.

   1: -- Delete all records

   2: DELETE TestTable

   3:  

   4: -- This shows that it still

   5: -- reads 5 pages

   6: SET STATISTICS IO ON

   7: SELECT * FROM TestTable

   8: SET STATISTICS IO OFF

   9:  

  10: -- This shows that still the 

  11: -- pages are not deallocated

  12: DBCC IND('tempdb', TestTable, -1)

  13:  

  14: -- Check one page and see whether

  15: -- it contains data.

  16: -- Note that you have turn 3604 flag

  17: -- on to run DBCC PAGE

  18: -- Parameters are: Database, FileNumber,

  19: -- PageNumber, DisplayOption

  20: DBCC TRACEON(3604)

  21: DBCC PAGE ('tempdb', 1, 167, 3) WITH TABLERESULTS

See, pages are not released. There are few ways to make sure that data pages are deallocated at deletion of this scenario. First way is, using TABLOCK hint with DELETE statement.

   1: DELETE TestTable WITH (TABLOCK)

   2:  

This changes the way of placing locks, SQL Server places shared locks on the table instead of rows or pages, hence pages are deallocated.

Second way is, use TRUNCATE instead of DELETE. This deallocates pages too.

Another way is, adding a clustered index on the table and doing the deletion, which might time consuming.

Anyway, most of the tables we design have clustered indexes, if you have tables without clustered indexes, make sure you use one of above methods when deleting.

Just noticed a nice alert from Gmail.

If my composed mail has a sentence like “… find the attached …”, click on send button shows below:

SQL Server ANY and ALL Operators

When a scalar value has to be compared with a single-column set of values, we usually use IN or JOINs. In addition to that, we can extend the comparison by using ANY and ALL operators which have rarely been used. These two operators work differently, understanding them would be beneficial to all of us, avoiding complexity of queries.

ANY operator returns true when the scalar value matches with any value in the single-column set of values. ALL operator returns true when the scalar value matches with all value in the single-column set of values. The comparison can be extended with <, >, and <> operators too. Here are few sample queries to understand them:

   1: -- Lets create two tables and inser values

   2: USE tempdb

   3: GO

   4: CREATE TABLE Table1 (Id int)

   5: GO

   6: INSERT INTO Table1

   7: VALUES (1), (2), (3), (4), (5)

   8:  

   9: CREATE TABLE Table2 (Id int)

  10: GO

  11: INSERT INTO Table2 

  12: VALUES (1), (3), (5)

  13:  

  14: -- =ANY sees whether the scalar value is

  15: -- equal to any from Table2

  16: -- Eg. This checks: 

  17: -- (Table1:Id1 = Table2:Id1)

  18: -- OR (Table1:Id1 = Table2:Id3)

  19: -- OR (Table1:Id1 = Table2:Id5)

  20: SELECT Id FROM Table1 

  21: WHERE Id =ANY (SELECT Id FROM Table2)

  22: -- Finally, code returns 1, 3, and 5.

  23:  

  24: -- =ALL sees whether the scalar value is

  25: -- equal with all values in Table2.

  26: -- Eg. (Table1:Id1 = Table2:Id1)

  27: -- AND (Table1:Id1 = Table2:Id3)

  28: -- AND (Table1:Id1 = Table2:Id5)

  29: SELECT Id FROM Table1 

  30: WHERE Id =ALL (SELECT Id FROM Table2)

  31: -- Code returns nothing.

  32:  

  33: -- >=ANY sees whether the scalar value is

  34: -- greater than any value in Table2

  35: -- Eg. (Table1:Id1 > Table2:Id1)

  36: -- OR  (Table1:Id1 > Table2:Id3)

  37: -- OR (Table1:Id1 > Table2:Id5)

  38: -- Since the smallest one in Table2 is 1,

  39: -- As long as the Table1:Id is >= 1

  40: -- It is added to the resultset.

  41: SELECT Id FROM Table1 

  42: WHERE Id >=ANY (SELECT Id FROM Table2)

  43: -- Code returns 1, 2, 3, 4, and 5

  44:  

  45: -- >=ALL sees whether the scalar value is

  46: -- greater than all in Table2

  47: -- Eg. (Table1:Id1 > Table2:Id1)

  48: -- AND (Table1:Id1 > Table2:Id3)

  49: -- AND (Table1:Id1 > Table2:Id5)

  50: -- Since the highest number in Table2 is 5,

  51: -- As long as Table1:Id is >= 5, 

  52: -- It is added to the resultset.

  53: SELECT Id FROM Table1 

  54: WHERE Id >=ALL (SELECT Id FROM Table2)

  55: -- Code returns 5 only.

You should notice that =ANY is equal to IN. But the opposite of it, <>ANY and NOT IN are NOT equal. Remember, the equal one for NOT IN is, <>ALL.