SQL SERVER Find and Kill User Session

31. August 2012 13:09 by Mrojas in SQL Server  //  Tags: , ,   //   Comments (0)

I found this excellent post from Aasim adbullah

http://connectsql.blogspot.com/2010/12/sql-server-find-and-kill-specific-user.html

SELECT DISTINCT
        name AS database_name,
        session_id,
        host_name,
        login_time,
        login_name,
        reads,
        writes
FROM    sys.dm_exec_sessions
        LEFT OUTER JOIN sys.dm_tran_locks ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id
        INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id
WHERE   resource_type <> 'DATABASE'
--AND name ='YourDatabaseNameHere'
ORDER BY name
 
To find out sessions which acquired EXCLUSIVE locks, modify above mentioned query as follow
SELECT DISTINCT
        name AS database_name,
        session_id,
        host_name,
        login_time,
        login_name,
        reads,
        writes
FROM    sys.dm_exec_sessions
        LEFT OUTER JOIN sys.dm_tran_locks ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id
        INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id
WHERE   resource_type <> 'DATABASE'
AND request_mode LIKE '%X%'
--AND name ='YourDatabaseNameHere'
ORDER BY name

Migrating Access to SQL Server

10. February 2012 17:51 by Mrojas in Access, SQL Server  //  Tags: , , , , ,   //   Comments (0)

If during a VB6 migration you decide o take a step forward and move your databases to MS SQL

don't worry. The SQL Server team has a tool just for that.

I just don't know why it is so hard to find it in google. But the appropiate link is:

http://blogs.msdn.com/b/ssma/p/access.aspx

Also a recent article in Teched goes into all the gory details: http://technet.microsoft.com/en-us/magazine/hh334645.aspx

Good Luck and don't hesitate to contact us for any doubts.

Windows Azure Migration: Database Migration, Post 1

2. April 2011 18:14 by Mrojas in Azure  //  Tags: , , , , , , , , , ,   //   Comments (0)

WheWhen you are doing an azure migration, one of the first thing you must do is
collect all the information you can about your database.
Also at some point in your migration process you might consider between migration to
SQL Azure or Azure Storage or Azure Tables.

Do do all the appropriate decisions you need to collect at least basic data like:

  • Database Size
  • Table Size
  • Row Size
  • User Defined Types or any other code that depends on the CLR
  • Extended Properties

Database Size

You can use a script like this to collect some general information:

create table #spaceused(
databasename varchar(255),
size varchar(255),
owner varchar(255),
dbid int,
created varchar(255),
status varchar(255),
level int)

insert #spaceused (databasename , size,owner,dbid,created,status, level)  exec sp_helpdb

select * from #spaceused for xml raw
drop table  #spaceused
 

When you run this script you will get an XML like:

<row databasename="master" 
size=" 33.69 MB" 
owner="sa" 
dbid="1" 
created="Apr 8 2003" 
status="Status=ONLINE, ..." 
level="90"/>
<row databasename="msdb" 
size=" 50.50 MB" 
owner="sa" 
dbid="4" 
created="Oct 14 2005" 
status="Status=ONLINE, ..." 
level="90"/>
<row databasename="mycooldb" 
size=" 180.94 MB" 
owner="sa" 
dbid="89" 
created="Apr 22 2010" 
status="Status=ONLINE, ..." 
level="90"/>
<row databasename="cooldb" 
size=" 10.49 MB" 
owner="sa" 
dbid="53" 
created="Jul 22 2010" 
status="Status=ONLINE, ..." 
level="90"/>
<row databasename="tempdb" 
size=" 398.44 MB" 
owner="sa" dbid="2" 
created="Feb 16 2011" 
status="Status=ONLINE, ..." 
level="90"/>

And yes I know there are several other scripts that can give you more detailed information about your database
but this one answers simple questions like

Does my database fits in SQL Azure?
Which is an appropriate SQL Azure DB Size?

Also remember that SQL Azure is based on SQL Server 2008 (level 100).

80 = SQL Server 2000

90 = SQL Server 2005

100 = SQL Server 2008


If you are migrating from an older database (level 80 or 90) it might be necessary to upgrade first.

This post might be helpful: http://blog.scalabilityexperts.com/2008/01/28/upgrade-sql-server-2000-to-2005-or-2008/

Table Size

Table size is also important.There great script for that:

http://vyaskn.tripod.com/sp_show_biggest_tables.htm

If you plan to migrate to Azure Storage there are certain constraints. For example consider looking at the number of columns:

You can use these scripts: http://www.novicksoftware.com/udfofweek/vol2/t-sql-udf-vol-2-num-27-udf_tbl_colcounttab.htm (I just had to change the alter for create)

Row Size

I found this on a forum (thanks to Lee Dice and Michael Lee)

DECLARE @sql        VARCHAR (8000)
        , @tablename  VARCHAR (255)
        , @delim      VARCHAR (3)
        , @q          CHAR (1)

  SELECT @tablename = '{table name}'
       , @q         = CHAR (39)

  SELECT @delim = ''
       , @sql   = 'SELECT '

  SELECT @sql   = @sql
                + @delim
                + 'ISNULL(DATALENGTH ([' + name + ']),0)'
       , @delim = ' + '
  FROM   syscolumns
  WHERE  id = OBJECT_ID (@tablename)
  ORDER BY colid

  SELECT @sql = @sql + ' rowlength'
              + ' FROM [' + @tablename + ']'
       , @sql =  'SELECT MAX (rowlength)'
              + ' FROM (' + @sql + ') rowlengths'
  PRINT @sql
  EXEC (@sql)

Remember to change the {table name} for the name of the table you need

User Defined Types or any other code that depends on the CLR

Just look at your db scripts at determine if there are any CREATE TYPE statements with the assembly keyword.
Also determine if CLR is enabled with a query like:

select * from sys.configurations where name = 'clr enabled'

If this query has a column value = 1 then it is enabled.

Extended Properties

Look for calls to sp_addextendedproperty dropextendedproperty OBJECTPROPERTY and sys.extended_properties  in your scripts.

Create a Database in SQL Azure

26. October 2010 06:28 by Mrojas in General  //  Tags: , , , , , ,   //   Comments (0)

One of the first steps to put your database in Windows Azure is to put your data on the cloud.

1. The first step is to log in to Azure

1.a) Go to https://windows.azure.com/Cloud/Provisioning/Default.aspx 

1.b)Type your user name and password.

1.c) When your are logged in go to SQL Azure option on the left hand menu.

1.d) You will see a list of your projects. Click on the project.

image

2. You must accept Azure Terms of Use

image

3. Now create a server. You must indicate an administrator username and password. Azure will set the server name.
Press the Create Server button and continue.

image 

4. Now let’s create a new database.Press the Create Database Button

image

5. Just type your database name, edition and size. Editions can be Web or Business.
At this moment Size for Web Edition is 1GB or 5GB and for Business is 10GB, 20GB, 30GB, 40GB and 50GB.
Prices varies according to the options that you select.
For my purposes I will select a Web Edition of 1GB

image

6. You should be able to see your new database in the Databases list.

image

7. Configure Firewall settings so you can connect to the new database.

image

7.1 Press the Allow Microsoft Services access to this server checkbox.
That will add a Rule Name that allows access from 0.0.0.0 to 0.0.0.0.
Select the rule and press Edit. You must can type something like 0.0.0.0 to 255.255.255.255 to allow access to all IP Address.

Moving your data to SQL Azure

The easiest way to upload a SQL Server database to SQL Azure is to use
the SQL Azure Migration Wizard this is a great tool that you can download from
CodePlex http://sqlazuremw.codeplex.com/

1. Download the tool. In my case I installed it in D:\SQLAzureTools. Run SQLAzureMW.exe

image

2. Select SQL Database and press Next

image

3. Select your database and press connect. That will add your database to a list. Click on your database name and press Next

image

4. Select the tables and other object that you will move to your SQL Azure database

image

5. Press Next a Summary is presented with all options

image

 

 

 

6. The wizard will generate a BCP file and a script for your tables.

image

You can connect to run your scripts with the following command:

> sqlcmd -S tcp:<servername>.database.windows.net -U <username>@<servername> -P <password> –d <database>

Depending on your configuration you might have problems with BCP. In general you need something like:

To export data:

bcp PhoneBook out c:\temp\Phonebook-c.dat –S <Server> –T –c

-T  means a trusted connection and native format

To import data

bcp Phonebook.dbo.Phonebook in c:\temp\Phonebook-c.dat -c -U <username>@<servername> -P <Password> -S tcp:<servername>.database.windows.net -c

 

 

 

 

 

 

After importing your data, you are set to keep on developing your applications on Azure

NOTE:

If you dont know the schema name connect to your database and run something like:

SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']' AS SchemaTable FROM sys.tables