After hearing great feedback about my last UG report, I am thrilled and excited to write report about recent Bangalore SQL Server User Group Meeting on 19th Jan 2013. [apologies for delay as it took time to get the pictures of event). This UG meet was focused on SQL Server Excel based visualizations. Peoples were expected to get some great content that was less known to do inside Excel. It was held at Microsoft Signature building, 4th floor. I came to the event when room was empty and here is what it was looking like when we started rolling our camera…

….and in no time, room was full!

Excitement was set to start at 10:30 AM and first session was started as per schedule and delivered by Vinod (B|T). His topic was “Tour of PowerPivot with Excel” and he continued with his last session’s excel trick about FlashFill feature of excel 2013.

Well received session and ended with claps of 70+ folks.

Next session taken by S Anand (B|T) on “Advanced Excel Visualizations”, who showed is the real power of visualization. He demonstrated analysis done for various classes results, hotel eating pattern, child birth pattern. In below picture, he was explaining why some hotels replaced their 1 liters bottle to 200 milliliters bottle.

This session was truly an eye opener and shown that how easy it is to read the data if its presented properly. This was truly one of the mostly discussed session after delivery.

Later, Selva (Blog|FB) from SQL Server Escalation Services team, who was called as hidden Gem by Vinod. Session title for Selva was – Introduction to PowerView with Excel. It was truly amazing session and was a good start point for them who wanted to learn PowerView. As per his demo “we might overtake china in population in next few years”. Sorry Selva, no picture.

Finally, most awaited surprise session from Pinal Dave (b|t) started and in a short session of 15 minutes, people learned about Auto Update Statistics and Auto Create Statistics setting of a database. Pinal was missing the cameraman and suddenly god shown his grace and Manas (b|t), appeared and clicked some pictures.

In his own unique style of presentation he showed one of the addictive game “Angry Bird” screenshot and asked which level it is. As usual there were many fun elements in the session and no one can forget those 15 minutes. One of the participants , Onkar, who kept him on toes received his famous book SQL Server Interview Questions and Answers (Amazon | Kindle | Flipkart | Indiaplaza) as a reward.

Pinal also shared a secret that why he checks in to all new places via foursquare (don’t worry Pinal, I will not write here). Session was full of infotainment followed by closure picture. Below are those folks who stayed late to have a photo session.

Few more folks were nice enough to take a picture with me as well I tried my best to hide my tummy by “cheese and inhale” trick but it didn’t work and got worst of my pic ever.

Below picture of all back-benchers clicked by another back bencher Manas (b|t). Left to Right – Selva (Blog|FB), Govind (B|T) Sudeepta (b|t), Kamlesh (B|T), , Vinod, Balmukund, Deepthi

That’s all for now. I personally had a great time in organizing, executing and becoming reporter of this event along with Vinod.

Feeling free to join the excitement at Facebook.

This is Balmukund, with no cameraman, reporting on wordpress about SQLBangUG#3.

Cheers,
Balmukund

I was contacted by colleague of mine and he informed that he has seen 19 GB database size on SQL Express 2008 R2. He read that limit was 10 GB and asked is there any workaround to elevate the limit? 

So, as a follow-up to Guru’s post I did further research and found interesting information.

Size limitation mentioned in his post are actually limit for data file(s) only. Which means I can have a database for total size more than specified in his post’s table. Actually this makes sense to me because log file is for recovery purpose not to store user data.

Also, this limit does not include filestream data (this is tricky to track) because user can store many images, more than 10 GB and still stay within the limit.

Happy learning!

Cheers,
Balmukund Lakhani
Twitter @blakhani

Sounds strange? Yeah and that’s the reason I am blogging about this. And recently I saw this question in forum as well. Have you ever come across this scenario?

Here is the repro of the issue and explanation. Below is the screenshot of my windows explorer space usage before I fired the query.

And here is the “bad” query. I know its the stupid query but it serves purpose of the blog  

Select    * 
from    sys.objects a1,
        sys.objects a2,
        sys.objects a3,
        sys.objects a4,
        sys.objects a5,
        sys.objects a6,
        sys.objects a7,
        sys.objects a8,
        sys.objects a9,
        sys.objects a0

Space would not be taken up as soon as you run the query. Once its start producing “HUGE” result set. I left the query running for around 6 min and here is the “proof”

Empty space in C drive dropped from 21.9 GB to 20.9 GB (Yeah you read is correct, it’s 1 GB) I have run process monitor and filtered results for ssms.exe.

Opened file Start > Run > %temp%\FileName  (I don’t know why i didn’t see file in folder)

If you look closely and compare above tmp file with management studio result window, it would be evident that ssms is writing the result in tmp file.

Moral of the Story:

• Don’t run query which has HUGE result set on server directly.
• If you have such need, redirect the output to “file” rather than shown in management studio result window. This can be done by two ways.

OR

Another workaround would be to change the TEMP/TMP path in environment variables as shown below.
My Computer > Right Click > Properies

Hope this would help someone in the world!

Cheers,
Balmukund Lakhani
Twitter @blakhani

Recently I worked with a customer where they wanted to run xp_fixeddrives without sysadmin permissions. This procedure was running from an application which was using non-sysadmin account. The major challenge in helping on such cases is that xp_fixeddrives is “undocumented” and hence “unsupported”

I love my customers and hate to say “No” and send them back empty handed. So, I put a little efforts to help them and found way to do it using proxy account. Here are the steps.

• Enable xp_cmdshell if its not enabled already.

sp_configure 'show advanced options', 1
go
reconfigure with override
go
sp_configure 'xp_cmdshell', 1
go
reconfigure with override

• Create below stored procedure which would provide the details about disk space. This has taken from internet and I am not author of this. I have done little modification to original code to get same output as xp_fixeddrives.

CREATE PROC Usp_fixeddrives 
WITH EXECUTE AS OWNER 
AS 
  BEGIN 
    set nocount on
      DECLARE @Drive TINYINT, 
              @SQL   VARCHAR(100) 

      SET @Drive = 97 

      DECLARE @Drives TABLE ( 
        drive CHAR(1), 
        info  VARCHAR(80)) 

      WHILE @Drive <= 122 
        BEGIN 
            SET @SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR( 
                       @Drive) 
                       + 
                       ':''' 

            INSERT @Drives 
                   (info) 
            EXEC (@SQL) 

            UPDATE @Drives 
            SET    drive = CHAR(@Drive) 
            WHERE  drive IS NULL 

            SET @Drive = @Drive + 1 
        END 

      -- Show the expected output 
      SELECT upper(drive) AS drive, 
             cast (SUM(CASE 
                   WHEN info LIKE 'Total # of free bytes        : %' THEN CAST( 
                   REPLACE( 
                   Substring(info, 32, 48), 
                   CHAR(13), '') AS BIGINT) 
                   ELSE CAST(0 AS BIGINT) 
                 END)/1024.0/1024.0 as bigint) AS 'MB free'
             FROM   (SELECT drive, 
                     info 
              FROM   @Drives 
              WHERE  info LIKE 'Total # of %') AS d 
      GROUP  BY drive 
      ORDER  BY drive 
  END  

• Since we are not going to give permission to SQL login on xp_cmdshell so we need to use proxy account. Windows account would be a Admin account.

EXEC sp_xp_cmdshell_proxy_account  'Domain\User', 'Password_For_User';
GO

• To test this we need to create a new SQL login, user and grant execute permission on the stored proc created.

-- Create login
USE [master]
GO
CREATE LOGIN [AppAdmin] WITH PASSWORD=N'P@ssw0rd1', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

-- Create User
USE [master]
GO
CREATE USER [AppAdmin] FOR LOGIN [AppAdmin]
GO

-- Grant execute on the stored procedure.
Grant exec on Usp_fixeddrives to [AppAdmin]
GO

• To test the functionality you need to login with AppAdmin account and run procedure as below.
• – Execute procedure
  exec Usp_fixeddrives

     /*————————
     execute Usp_fixeddrives
      ————————*/
      drive MB free
      —– ——————–
      C     23523
      D     196197

• Here is the output from sysadmin account and they are matching!!!

      /*————————
      execute xp_fixeddrives
      ————————*/
      drive MB free
      —– ———–
      C     23523
      D     196197

Hope this would help someone in the world!

Cheers,
Balmukund Lakhani
Twitter @blakhani

This is one of the common questions I have seen on forums, most common request from administrators who have been given task to create multi-tenant SQL Server. This essentially means various department of company or various client of the hosting company would access same SQL Server Instance. Business owners demand that they want clients to see only those databases which are relevant for them. I have worked with a company where the database name was name of the client. Inability to hide name of databases would essentially mean exposing information about their other clients.

In simple words, if Contoso is a database hosting company who has “Northwind traders” and “TailSpin Toys” as their client. If TailSpin logs in to the SQL Server, they should not see “Northwind” database and vice versa.

SQL Server Enterprise Manager calls the sp_MSdbuseraccess stored procedure to determine the accessibility of each database. The sp_MSdbuseraccess stored procedure then runs against every database in SQL Server to determine the user’s permission level in that database. So this can be achieved by altering the stored procedure as documented in KB http://support.microsoft.com/?id=889696 (You may experience a slow response from SQL Server Enterprise Manager when many databases exist in an instance of SQL Server)

In SQL 2005 onwards we fire queries to get the information in SQL Server Management Studio (a.k.a. SSMS) . Now, SQL Server 2005 onwards, we also have limited metadata visibility which I am going to use as “trick” to achieve our goal. As per http://msdn.microsoft.com/en-us/library/ms178534.aspx
{

If the caller of sys.databases is not the owner of the database and the database is not master or tempdb, the minimum permissions required to see the corresponding row are ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission, or CREATE DATABASE permission in the master database. The database to which the caller is connected can always be viewed in sys.databases.
}

So, here is the quick script which can be used to demo this. If you love SSMS then I have also given instruction.

• Create Two databases.

Create Database [NorthWindDB]
go
Create Database [TailSpinDB]
go

• Create Logins (Not mapped to any Database)

CREATE LOGIN [NorthWindLogin] WITH
PASSWORD=‘E@syP@ssw0rd’
go
CREATE LOGIN [TailSpinLogin] WITH
PASSWORD=‘E@syP@ssw0rd’
go

• Right Click on the Root section of the SSMS Tree (SQL Server Name)>Properties>Permissions>Click on the account, and select Deny to view databases. OR use below T-SQL

REVOKE VIEW ANY DATABASE TO [NorthWindLogin]
go
REVOKE VIEW ANY DATABASE TO [TailSpinLogin]
go

• Right Click on the newly created databases > Properties > Files, and change the Owner to the newly created accounts. OR use below T-SQL

use [NorthWindDB]
go
sp_changedbowner 'NorthWindLogin'
go
use [TailSpinDB]
go
sp_changedbowner 'TailSpinLogin'
go

Here is the final out come…

NorthWindLogin can see only NorthWindDB database and TailSpinLogin can see only his database.

Erland Sommarskog, SQL MVP, has filed request “Need VIEW DEFINITION permissions per database”. If you feel this is useful then please vote here!

Hope this would help someone in the world!

Cheers,
Balmukund Lakhani
Twitter @blakhani

While working on a case few days ago, I faced this question by customer and i found it interesting to blog about. (Today I found this in forum so finalizing and posting it). Here is the question.

I have created two node windows cluster. All disk are available in “storage” but I don’t see any option to do manual failover unless they are part of some group. How can I test if failover is working fine for disks without shutting down the node or adding them to a group.

Here is the screenshot to explain the problem better. As you can see that U and W are not part of any group defined under “Services and Applications”. So by default they are the part of “Available Storage” group.

And indeed there is no option in UI to do failover of those disks.

I spend sometime with windows team and I was able to find the answer. so here it goes.
This can only be done by command line. We need to use cluster.exe command to achieve this.

Cluster Group “Available Storage” /Move:NodeName

In above command NodeName is optional. If you have two nodes, it would move storage automatically to other node.

Hope this would help someone in the world!

Cheers,
Balmukund Lakhani
Twitter @blakhani

Sometime I have been asked that how to find out who dropped/altered/created table/stored procedure. Well, SQL 2005 has out-of-the-box solution to this problem. This works in SQL 2008 as well.

Schema Change History is the report which would give details. This report takes data from default trace which is enabled by default in SQL Server 2005 default installation. Below command will help you in finding current status.

USE master;
EXEC sp_configure ‘show advanced option’, ’1′;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure ‘default trace’;
GO

If its enabled you would see run_value as 1.

More about default trace can be read here

If you want to see who dropped a database, you can right click on Server Icon and go to Reports > Standard Reports > Schema Changed History. This Reports output would changed based on where you right clicked it (on server icon or database name)

Since default trace will be maximum 100 MB in size you may not get the data which is pretty old. Every restart also creates new file. So, if your SQL Server has restarted five times after someone dropped the object, you will not be able to see it here.

SQL Server 2005 has many other “Standard Reports” which can be found in SQL Server Management Studio.

Enjoy!!!

<Tags> Who dropped my database, when database was dropped, someone deleted database objects, tables, procedures, list of dropped objects in SQL Server 2005, Schema Change history Report, see who dropped databases, how to find who dropped the database in SQL server 2005 objects dropped</Tags>

Cheers!
Balmukund