Find that big database in SQL server

by Damiaan Peeters 10. January 2013 20:08

We have several SQL databases.  Each for each client website.  We also have a lot of servers running SQL server.  When you need to clean up space, you sometimes know that there is just one big database . 

sp_helpdb to the rescue! 

image

And when you add a database name to sp_help ‘my_db_name’
you get all the files too…

image

Tags:

SQL | Sysadmin

(Clustered) Indexing on SQL

by Damiaan Peeters 6. August 2012 13:45

Published inititially in this forum post on sql Azure.

Use always the primary key + clustered index with integer with Identity.  Use an ever increasing value.  If you use a Guid use the NEWSEQUENTIALID function.

A few additional thoughts:

  • Try to create a clustered index on the most frequently used column to retrieve data.
  • created a (clustered) index on as few columns as possible
  • Clustered index columns shouldn't have duplicate values
  • Put clustered indexes on columns which are not updated

There is a very good write up on best practices on SQL Indexing (not azure specific) here: http://blogs.technet.com/b/josebda/archive/2009/03/17/indexing-best-practices-for-sql-server-2008.aspx

EDIT: Concerning an INSERT: i don't think it's always the same whether you have an (clustered) index or not. A Clustered index creates an index in which the logical order of the key values determines the physical order of the corresponding rows in a table. Depending on your choice of clustered index this may affect insert performance.  Read the introduction to clustered and non-clustered index data structures at sql-server-performance.com.

Create azure sql user

by Damiaan Peeters 11. June 2012 14:22

Just a small note for myself on creating sql user using the SQL statements.

Connect to the MASTER and create a login.

create login myNewLogin with password = 'myPassword'

The connect to the database itself and give the new login access

create user myNewUser FOR LOGIN myNewLogin

EXEC sp_addrolemember N'db_datareader', N'myNewUser'
EXEC sp_addrolemember N'db_datawriter', N'myNewUser'

 

There is a lot to read on MSDN

Delete duplicate relations in Umbraco using SQL

by Damiaan Peeters 21. September 2011 09:29

Somehow I got duplicate relations in my Umbraco Relations table (about 120 k).  That’s impossible to clean them up manually, so I decided to remove the duplicates using an SQL statement directly on the database.  

Apparently deleting duplicates is not so hard on newer SQL servers when using Common Table Expressions.  CTE’s are a kind of temporary result sets that can be used within a single statement.  The difference with a temporary table is that it is not stored as an object.

WITH Dublicates_CTE(parentId, childId, Id)
AS
(
SELECT parentId, childId , Min(Id) Id
from umbracoRelation
group by  parentId, childId
having COUNT(*) >1
)

DELETE FROM umbracoRelation
WHERE Id IN (
    SELECT umbracoRelation.Id
    FROM umbracoRelation
    INNER JOIN Dublicates_CTE
    ON umbracoRelation.parentId= Dublicates_CTE.parentId
    AND umbracoRelation.childId= Dublicates_CTE.childId
    AND umbracoRelation.id <> Dublicates_CTE.Id
)

I found the idea of removing duplicates on this blogpost.

Regain administrator access to your SQL EXPRESS 2008 (R2)

by Damiaan Peeters 27. March 2011 23:32

As I described earlier with a plain SQL server loosing access to your own SQL server or database is a very annoying problem.

The Problem

When starting the Azure Compute Emulator I got this error:

Creating database DevelopmentStorageDb20090919...
Cannot create database 'DevelopmentStorageDb20090919' : CREATE DATABASE permission denied in database 'master'.

One or more initialization actions have failed. Resolve these errors before attempting to run the storage emulator again. These errors can occur if SQL Server was installed by someone other than the current user. Please refer to http://go.microsoft.com/fwlink/?LinkID=205140 for more details.

What this error means is:

  • I was using the useDevelopmentStorage=true parameter, so the emulator connects to the default sqlexpress instance (MACHINE\sqlexpress) on the pc. 
  • Once connected, he tried to create a new database

If SQL Express is not installed by the user your are working with.  The current user is NOT a sysadmin and can’t create any databases).  Adding yourself is neither a solution or enabling the SA user as you are not granted access to do so.

The Solution

Launch the “add self to SQL SysAdmin” script created by ward Beattie.

It saves you days of work and searching.

I highly encourage you to DOWNLOAD the script from the original location.  But just for reference and in case the script got deleted or moved, I added the complete script below.

 


@echo off
rem
rem ****************************************************************************
rem
rem    Copyright (c) Microsoft Corporation. All rights reserved.
rem    This code is licensed under the Microsoft Public License.
rem    THIS CODE IS PROVIDED *AS IS* WITHOUT WARRANTY OF
rem    ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING ANY
rem    IMPLIED WARRANTIES OF FITNESS FOR A PARTICULAR
rem    PURPOSE, MERCHANTABILITY, OR NON-INFRINGEMENT.
rem
rem ****************************************************************************
rem
rem CMD script to add a user to the SQL Server sysadmin role
rem
rem Input:  %1 specifies the instance name to be modified. Defaults to SQLEXPRESS.
rem         %2 specifies the principal identity to be added (in the form "<domain>\<user>").
rem            If omitted, the script will request elevation and add the current user (pre-elevation) to the sysadmin role.
rem            If provided explicitly, the script is assumed to be running elevated already.
rem
rem Method: 1) restart the SQL service with the '-m' option, which allows a single connection from a box admin
rem            (the box admin is temporarily added to the sysadmin role with this start option)
rem         2) connect to the SQL instance and add the user to the sysadmin role
rem         3) restart the SQL service for normal connections
rem
rem Output: Messages indicating success/failure.
rem         Note that if elevation is done by this script, a new command process window is created: the output of this
rem         window is not directly accessible to the caller.
rem
rem
setlocal
set sqlresult=N/A
if .%1 == . (set /P sqlinstance=Enter SQL instance name, or default to SQLEXPRESS: ) else (set sqlinstance=%1)
if .%sqlinstance% == . (set sqlinstance=SQLEXPRESS)
if /I %sqlinstance% == MSSQLSERVER (set sqlservice=MSSQLSERVER) else (set sqlservice=MSSQL$%sqlinstance%)
if .%2 == . (set sqllogin="%USERDOMAIN%\%USERNAME%") else (set sqllogin=%2)
rem remove enclosing quotes
for %%i in (%sqllogin%) do set sqllogin=%%~i
@echo Adding '%sqllogin%' to the 'sysadmin' role on SQL Server instance '%sqlinstance%'.
@echo Verify the '%sqlservice%' service exists ...
set srvstate=0
for /F "usebackq tokens=1,3" %%i in (`sc query %sqlservice%`) do if .%%i == .STATE set srvstate=%%j
if .%srvstate% == .0 goto existerror
rem
rem elevate if <domain/user> was defaulted
rem
if NOT .%2 == . goto continue
echo new ActiveXObject("Shell.Application").ShellExecute("cmd.exe", "/D /Q /C pushd \""+WScript.Arguments(0)+"\" & \""+WScript.Arguments(1)+"\" %sqlinstance% \""+WScript.Arguments(2)+"\"", "", "runas"); >"%TEMP%\addsysadmin{7FC2CAE2-2E9E-47a0-ADE5-C43582022EA8}.js"
call "%TEMP%\addsysadmin{7FC2CAE2-2E9E-47a0-ADE5-C43582022EA8}.js" "%cd%" %0 "%sqllogin%"
del "%TEMP%\addsysadmin{7FC2CAE2-2E9E-47a0-ADE5-C43582022EA8}.js"
goto :EOF
:continue
rem
rem determine if the SQL service is running
rem
set srvstarted=0
set srvstate=0
for /F "usebackq tokens=1,3" %%i in (`sc query %sqlservice%`) do if .%%i == .STATE set srvstate=%%j
if .%srvstate% == .0 goto queryerror
rem
rem if required, stop the SQL service
rem
if .%srvstate% == .1 goto startm
set srvstarted=1
@echo Stop the '%sqlservice%' service ...
net stop %sqlservice%
if errorlevel 1 goto stoperror
:startm
rem
rem start the SQL service with the '-m' option (single admin connection) and wait until its STATE is '4' (STARTED)
rem also use trace flags as follows:
rem     3659 - log all errors to errorlog
rem     4010 - enable shared memory only (lpc:)
rem     4022 - do not start autoprocs
rem
@echo Start the '%sqlservice%' service in maintenance mode ...
sc start %sqlservice% -m -T3659 -T4010 -T4022 >nul
if errorlevel 1 goto startmerror
:checkstate1
set srvstate=0
for /F "usebackq tokens=1,3" %%i in (`sc query %sqlservice%`) do if .%%i == .STATE set srvstate=%%j
if .%srvstate% == .0 goto queryerror
if .%srvstate% == .1 goto startmerror
if NOT .%srvstate% == .4 goto checkstate1
rem
rem add the specified user to the sysadmin role
rem access tempdb to avoid a misleading shutdown error
rem
@echo Add '%sqllogin%' to the 'sysadmin' role ...
for /F "usebackq tokens=1,3" %%i in (`sqlcmd -S np:\\.\pipe\SQLLocal\%sqlinstance% -E -Q "create table #foo (bar int); declare @rc int; execute @rc = sp_addsrvrolemember '$(sqllogin)', 'sysadmin'; print 'RETURN_CODE : '+CAST(@rc as char)"`) do if .%%i == .RETURN_CODE set sqlresult=%%j
rem
rem stop the SQL service
rem
@echo Stop the '%sqlservice%' service ...
net stop %sqlservice%
if errorlevel 1 goto stoperror
if .%srvstarted% == .0 goto exit
rem
rem start the SQL service for normal connections
rem
net start %sqlservice%
if errorlevel 1 goto starterror
goto exit
rem
rem handle unexpected errors
rem
:existerror
sc query %sqlservice%
@echo '%sqlservice%' service is invalid
goto exit
:queryerror
@echo 'sc query %sqlservice%' failed
goto exit
:stoperror
@echo 'net stop %sqlservice%' failed
goto exit
:startmerror
@echo 'sc start %sqlservice% -m' failed
goto exit
:starterror
@echo 'net start %sqlservice%' failed
goto exit
:exit
if .%sqlresult% == .0 (@echo '%sqllogin%' was successfully added to the 'sysadmin' role.) else (@echo '%sqllogin%' was NOT added to the 'sysadmin' role: SQL return code is %sqlresult%.)
endlocal
pause

EXECUTE permission denied on object 'aspnet_CheckSchemaVersion', database 'myDB', schema 'dbo'.

by Damiaan Peeters 7. April 2009 18:27

Problem

Today I launched the aspnet_regsql.exe.  Unfortunately I received the following error:

EXECUTE permission denied on object 'aspnet_CheckSchemaVersion', database 'myDB', schema 'dbo'.

Solution

Take the properties of the “NT Autority\Network Service” user.  Select the myDB database and add all the necessary Roles like aspnet_Membership_FullAccess to the user. 

image

In the screenshot are all the databaseroles added.

Tags: ,

Asp.Net | SQL

Update Query versus Updatable cursor

by Damiaan Peeters 28. February 2009 02:02

Tonight someone gave me passed me an awfull SQL Query.  After a small few adaptions some the tablescans where eliminated.  I still believed it would be quicker using a cursor.  So, i started to create a SQL script with a cursor.

Create and populate test data

This was the Table to work against:

CREATE TABLE temptbl   
(    
  ProductId INT primary key identity(1,1),   
  Period INT,   
  Qty INT,   
  BFQty INT,   
  CFQty INT  
)   

 

To populate the table, you can write a million of bytes or create a small loop

-- Populate the table with some data
INSERT INTO temptbl VALUES(200801,1,0,0)   
INSERT INTO temptbl VALUES(200802,2,0,0)   
INSERT INTO temptbl VALUES(200803,3,0,0)   

DECLARE @RowCount INT, @Random INT, @Upper INT, @Lower INT, @periode INT

SET @Lower = 0
SET @Upper = 12
SET @RowCount = 0

WHILE @RowCount < 10000
BEGIN
    SET @RowString = CAST(@RowCount AS VARCHAR(10))
    SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
    INSERT INTO temptbl VALUES(200801+@random,@random,0,0)   
    SET @RowCount = @RowCount + 1
END
Select count(*) from temptbl

 

The solution with one single query

This was the – not sooo awful solution to fill the brought forward and carry forward fields

DECLARE @Value INT
UPDATE temptbl SET  
     @Value = (SELECT ISNULL(SUM(Qty),0) FROM temptbl AS InnerT WHERE InnerT.ProductId=temptbl.ProductId AND InnerT.Period<temptbl.Period),
     BFQty = @Value,CFQty = @Value + Qty   

Solution with (updatable) SQL cursor

The trick to let it run fast, is – if i understood it well – using the updateble cursor.  Normally you should avoid using this type of cursor.  But now we need to run through the whole table anyway and update each field using the data of the previous record.  I highlighted the 2 important parts.

begin transaction
DECLARE MY_CURSOR Cursor DYNAMIC
FOR SELECT ProductId, qty FROM temptbl order by productId, period
FOR UPDATE  OF BFQty, CFQty

Open My_Cursor
DECLARE @Value int, @Qty int, @prodId int, @OldProdId int

Fetch NEXT FROM MY_Cursor INTO @ProdId, @Qty
SET @OldProdId = @ProdId
SET @Value = 0
While (@@FETCH_STATUS = 0)
BEGIN

    SET @OldProdId = @ProdId

   UPDATE temptbl
    set BFQty = @Value, CFQty = @Value + @Qty
    WHERE  CURRENT OF My_Cursor

    SET @Value = @Value + @Qty

    Fetch NEXT FROM MY_Cursor INTO @ProdId, @Qty
    IF @OldProdId <> @ProdId
    BEGIN
        set @VALUE =0
    END
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
commit transaction

Conclusion

Without the transaction it took 2 seconds for the cursor solution, and less than 1 second for the sql statement.  Adding The transaction dropped the cursor solution also below a second.

BUT, it took me 1 hour and a half to create (and learn about updateble) cursor solutions.  It’s 2:04 am, the other guy is in bed next to his girlfriend for some very reasonable time.  And I even haven’t fully tested if the cursor solution has the correct return (i even doubt it).

 

Normal

In transaction

Human work time

Total

Statement

< 1 sec

n.a.

10 minutes

winner

Cursor

2 seconds

< 1 sec

1,5 hour

loser

Next time, i might write a sql query right away.  The optimizer of MSSQL does it’s work anyway.

ASP.Net Membership API Cannot resolve collation conflict for equal to operation

by Damiaan Peeters 31. December 2008 09:36

Error

I was using the ASP.NET SQL Server Setup Wizard (aspnet_regsql.exe) to deploy te database to the production SQL Server 2005.

image

Creation or change of the SQL Server database failed.

Setup failed.

Exception:
An error occurred during the execution of the SQL file 'InstallRoles.sql'. The SQL error number is 468 and the SqlException message is: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
[cut]

SQL file loaded:
InstallRoles.sql

Commands failed:

[cut] …  the failed SQL code

SQL Exception:
System.Data.SqlClient.SqlException: Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
[cut
]…
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Web.Management.SqlServices.ExecuteFile(String file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionState, Boolean isInstall, SessionStateType sessionStatetype)

Cause

Apparently this error happend when creating the asp.net membership stored procedures: aspnet_UsersInRoles_AddUsersToRoles or aspnet_UsersInRoles_RemoveUsersFromRoles.

The reason is pretty straithforward.

The aspnet_Roles table uses collation “latin1_General_CI_AS”.

image

While my database has a SQL_Latin1_General_CP1_CI_AS collation by default (just like my SQL server collation).

image

Solution

Use the SQL Profiler to get the Code of the of the Stored procedure which need to be created.  The SQL scripts of the ASP.Net Membership can also be found under: C:\Windows\Microsoft.NET\Framework\v2.0.50727  (InstallRoles.sql)

Change the line(s) in the scripts:

look for this line: DECLARE @tbNames table(Name nvarchar(256) NOT NULL PRIMARY KEY) and add the collation like specified in the “aspnet_Roles.RoleName” column.  Your new line should look like this:

DECLARE @tbNames table(Name nvarchar(256) COLLATE Latin1_General_CI_AS NOT NULL PRIMARY KEY)

You need to do this for the 2 stored procedures.

MSSQL error message: "Property Owner is not available for Database [databaseName]"

by Damiaan Peeters 22. June 2008 10:43

Problem

When i clicked on the properties of the Database, I got this error on 2 of my Microsoft SQL Server 2005 databases.

image

TITLE: Microsoft SQL Server Management Studio

Cannot show requested dialog.

ADDITIONAL INFORMATION:

Cannot show requested dialog. (SqlMgmt)

Property Owner is not available for Database '[myDatabaseName]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=Owner&LinkId=20476

Cause

I have restored this databases in the past from other server's backup, and my original login is apparerently not available on target server.

image

Solution

You can fix it with 'sp_changedbowner sa'.

update:
I removed '2005' the subject of this post because you can have this error message on the Newer SQL Server 2008 also.

update 2:
The syntax to use in SQL Server Management Studio is:

use myDBName
EXEC sp_changedbowner 'sa'

Searching in all tables and columns of a database

by Damiaan Peeters 26. May 2008 12:57

There are a lot of people struggling with finding data.  What about these two solutions... Google gave me these solutions...

When you know little about a database or can't remember it is often nice to be able to do global type searches on that database. This scenario can be thought of in two ways.

  • Search database schema
  • Search database data

Below I describe how to do both on a MS SQL database.

Search MS SQL Server for any text | Just Geeks 

 

And what do you think of this problem...  I guess you know the problem...

I was forced recently to do some maintenance and bug fixing on an aging .NET-based CMS.

Most of the problems were in the SQL Server database, with lots of corrupted entries. The most frustrating thing however was that at times I didn’t even know where to find the entries. Try finding some specific strings in a database with a hundred tables, each with many columns (poorly named, obviously) and tens of thousands of records – it’s like finding the proverbial needle in the haystack.

Searching in all tables and columns of a database | RichNetApps Developers' blog

Who.I.am

Certified Umbraco, .net and Azure developer, seo lover. Magician in my spare time.

Month List