Archive

Archive for the ‘SQL 2008’ Category

SQL Server 2008 SP4 and SQL Server 2008 R2 SP3 Released!

September 29, 2014 2 comments

SQL Server 2008 SP4 (link) and SQL Server 2008 R2 SP3 (link) have been released.  Please note these are the final service packs for these products is now in Extended Support cycle of Product Support Life Cycles.

Update Sept. 30, 2014: Sorry for mistake, for SQL Server 2008 it would be SP4.

Online Index Rebuild can Cause Fragmentation

June 9, 2014 Leave a comment

As Microsoft SQL Server PFE, I am always playing with SQL Server.  I generally spend most of my time on the engine side of world; so I am somewhat of a newbie when it comes to BI.  So I understand when I see something in BI world and am a bit lost.  However when I ran into this, rebuilding index online caused fragmentation.  I was some what baffled. You can try it if you wish, I tested it on SQL 2008, SQL 2008 R2, and SQL 2012.

-- Create table for testing.
CREATE TABLE Test (Col1 INT IDENTITY(1,1), Col2 CHAR(50), Col3 CHAR(50), Col4 Char(50)
CONSTRAINT pkTest PRIMARY KEY
(
        [Col1]
) WITH (ALLOW_PAGE_LOCKS  = OFF)
)

-- Insert test data.
INSERT INTO dbo.Test (Col2, Col3, Col4)
SELECT TOP 1000000 REPLICATE('A',50),REPLICATE('A',50),REPLICATE('A',50)
FROM sys.all_objects O1
CROSS JOIN sys.all_objects O2

-- Check fragmentation.
SELECT * FROM sys.dm_db_index_physical_stats(db_id(),null,null,null,null)

-- Rebuild Index online using MAXDOP
ALTER INDEX pkTest ON Test
REBUILD
WITH (Online=ON, MAXDOP=8)

-- Check fragmentation.
SELECT * FROM sys.dm_db_index_physical_stats(db_id(),null,null,null,null)

Some of you might noticed an interesting property.  ALLOW_PAGE_LOCK.

So turns out this is by design.  If ALLOW_PAGE_LOCK is turned off, we are building a index using parallelism, and building it online; it can cause fragmentation (Ref 1 & Ref 2).

Well lesson learned for future and no, it will not be last one.

SQL Server 2008 SP3 CU8 Released

November 20, 2012 Leave a comment

Multiple components of SQL Server were fixed in this.  Fixes for SSRS, Replication, Locking Hints, Security issues, etc.

Few interesting ones for me…

  1. Added support for varchar(max), nvarchar(max), and varbinary(max) data types in OLD DB Providers.
  2. This is interesting one, I always say no memory leak in SQL Server.  This shows SQL is not beyond that, “FIX: Memory leak if you enable the AUTO_UPDATE_STATISTICS_ASYNC”.  Although its rare I see customers using this, but please note this issue happened after SP3 (as per KB2778088)  But it is an important issue identified and fixed.

Download the patch from here.

Microsoft Support policy for SQL Server in Windows 8 and Windows 2012

November 9, 2012 Leave a comment

Now that Windows 8 (which btw ROCKS!) and Windows 2012 is out, Microsoft has released a KB highlighting the support for SQL Server in respect to these operating systems.

In KB2681562, lists the support requirements, issues with install, etc. So a one-stop-shop or so… 🙂

CREATE FILE encountered operating system error 5(Access is denied.)

April 23, 2010 5 comments

When trying to attach the database to SQL Server with an account other then yours you might get following error.

TITLE: Microsoft SQL Server Management Studio
——————————

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

——————————
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

——————————

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file ‘D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\DBName.mdf’. (Microsoft SQL Server, Error: 5123)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=5123&LinkId=20476

——————————
BUTTONS:

OK
——————————

If you follow the link it explains nothing; a little confused I did search on the Google (yeeh?).  With no answers as to why this is happening, I made sure SQL Server had proper permissions to the MSSQL Data and Log folders.  I decided to run Process Monitor to see what service account is trying to access the file when I am attaching it; it was as expected my SQL Server Service account was trying to access the file.  So why the permission denied?  File was located in same folder as rest of mdf/ldf files…

Looking into it I saw the file had a small lock icon on it like “Lock Icon“. Looking into the permissions on the file I see it was only accessible by my user name.  I don’t know why this was the case (actually I found why, I’ll post on it tomorrow; need to do a bit more research on that topic).  I edited the permission properties on the file to inhert form parent folder.

Reattached the database with no issues.

Issue: Can’t attach mdf and ldf file to the SQL Server, keeps getting permissions deined even though file is in default data/log directory and SQL Server has access to it.
Solution: Check file to make sure it is inheriting permissions from parent and is not owned by single user.

Actual value was -1. (Microsoft.SqlServer.GridControl) Error when attaching database.

April 23, 2010 2 comments

When attaching a database in SQL Server 2008, a user might get the following error.

 TITLE: Microsoft SQL Server Management Studio
——————————
Cannot show requested dialog.
——————————
ADDITIONAL INFORMATION:

Parameter name: nColIndex
Actual value was -1. (Microsoft.SqlServer.GridControl)

——————————
BUTTONS:

OK
——————————

The default solution that is listed on the sites [1] & [2] is use a user a user with sysadmin.  It does resolve the issue; but why?

Digging into it a bit, I found when attaching a database SQL Server executes few extended stored procedures.  These stored procedures can only be executed by sysadmin role, therefore when someone with just dbcreator permissions try to attach the database it fails. The actual error is this …

Msg 22001, Level 16, State 1, Line 0
xp_regread() returned error 5, ‘Access is denied.’

So I decided to dive into books online to confirm I am not getting forgetful, Books Online state for sp_attach_db “sysadmin and dbcreator fixed server roles can execute this procedure’.So I decided to execute following command:

EXEC sp_attach_db    @dbname = 'DBName',
                     @filename1='D:\MSSQL10.MSSQLServer\MSSQL\Data\DBName.mdf',
                     @filename2='E:\MSSQL10.MSSQLServer\MSSQL\LOG\DBName_log.ldf'

It attached successfully, so it seems like an issue in GUI only.

I have tried to look for KB article to see if there is a fix for this; also on Microsoft connect with no luck.  So I have a Microsoft Connect article here.

References
[1] How to change SQL Server login default database through SQL Script. Jeff Widmer’s Blog. Link.
[2] Unable to attach database using Management Studio. MSDN Forums Online. Link.

Get listing of Databases user has access to …

March 30, 2009 Leave a comment

You can right click on each user to find out what database he or she has access to; which can be very time consuming. I have wrote the script below that does similar thing for one user or multiple users.

Please note this procedure will take a while to complete depending on the number of databases in your system. You can submit three types of searches:

-- Check Single User [DEFAULT]
EXEC up_CheckUsersAccessInDB 'Test'
GO

-- Check Multiple Users
EXEC up_CheckUsersAccessInDB 'Test1,Test2', 1
GO

-- Check All Users
EXEC up_CheckUsersAccessInDB '%', 2
GO

Stored Procedure: Link.

If any errors or issues please feel free to mail me, mohitkgupta at msn dot com.

Categories: Security, SQL 2008

Surface Area Configuration Manager *R.I.P.*

March 25, 2009 Leave a comment

All who liked the SACM raise your hands …

Right that’s what I thought no one heh; okay it wasn’t that bad. But non the less it was discontinued in SQL 2008.

So how do you lower the Surface Area?

Answer: Use SQL Server Configuration Manager to manage services and you can use Facets to configure Surface Area Configuration.

Right Click on Server -> Click on Facets -> Click on Drop down list select Surface Area Configuration

Change settings you need and click OK.

Following settings can be adjusted here:

  • AdHoc Remote Queries
  • CLR Integration
  • Database Mail
  • OLE Automation
  • Remote DAC
  • Service Broker Endpoint
  • Soap Endpoints
  • SQL Mail
  • Web Assistant
  • XP CmdShell

Ref #1: http://msdn.microsoft.com/en-us/library/cc281850.aspx
Ref #2: http://msdn.microsoft.com/en-us/library/ms161956.aspx

Categories: SQL 2008

"New Maintenance Plan…" doesn’t work

March 24, 2009 Leave a comment

For some reason Microsoft decided not to enable that function in some editions of RTM in SQL server 2008 (annoying but meh). So looking around I found Microsoft Connect article, link. According to that it was suppose to be for the IA64 only but I was only running developer edition on x86 Windows XP Sp3…

What made it more annoying was you don’t get any error messages or nothing indicating it is not going to work. But I was able to use the wizard to create the maintenance plan; heh guess they over looked that.

So anyhow I decided to install CU4 and I got my Maintenance Plans back again…

CU4 download link; note you have to specially request it.

Default Backup Path in SQL 2008

March 18, 2009 Leave a comment

In the SQL Server 2008 setup you can now set the default backup location but in case you set it wrong or need to change it at later time the SSMS interface does not provide you with any options to do that. You can do the same thing as in 2005 …

Go to following key in Registry and put the new path in …

First we need to determine the instance Name; go to
[HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL\]

Note down the value for the default instance usually MSSQL10.MSSQLSERVER (note they changed the naming convention of instance names from MSSQL.Instance# to MSSQL10.InstanceID which you enter in at install time).

Now go to …
[HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\]

And change the value for BackupDirectory to the new value.

Now if you modify a Maintenance Plan it will grab the new value. You don’t need to restart server or services.

Categories: SQL 2008
%d bloggers like this: