Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

Archive for July, 2011

Did you Know – Size restriction in SQLExpress applies to only data file(s)?

Posted by blakhani on July 19, 2011


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

Advertisements

Posted in Uncategorized | Leave a Comment »

Help : Select query filling up my disk space!!!

Posted by blakhani on July 18, 2011


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.

image

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

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”

image

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.

image 

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

Tmp File

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.

Results to File

OR
Results to File_1

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

Temp Path Change

Hope this would help someone in the world!

Cheers,
Balmukund Lakhani
Twitter @blakhani

Posted in Uncategorized | 9 Comments »

Help : Getting drive free space details without sysadmin permission

Posted by blakhani on July 15, 2011


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

Posted in Uncategorized | 3 Comments »