Find databases without any owners

SELECT @@SERVERNAME AS ServerName
, [name] as DBName
, SUSER_SNAME(sid) as CurrentDBOwner
FROM sysdatabases
WHERE SUSER_SNAME(sid) is null
OR SUSER_SNAME(sid) = ''

Find where DB Collation is different to Server Collation

SELECT @@SERVERNAME AS ServerName
, [name] as DBName
, SERVERPROPERTY('Collation') AS SvrCollation
, DATABASEPROPERTYEX([name],'Collation') AS DBCollation
FROM sysdatabases
WHERE DATABASEPROPERTYEX([name],'Collation') <> SERVERPROPERTY('Collation')

I found a blog very interesting on www.sqlskills.com which talks about migrating databases among various versions of SQL Server. It’s very common these days for an organisation to run multiple versions of SQL Server to lower down costs on licences. Moving databases to lower versions can be a painful task if any version specific functionality or features are not supported.

Thanks to Microsoft to provide a DMV ‘sys.dm_db_persisted_sku_features ‘ to tell features belonging to specific version.

Source: http://www.sqlskills.com/blogs/paul/post/SQL-Server-2008-Does-my-database-contain-Enterprise-only-features.aspx

Find SQL Server instances on local network

on command prompt: run

c:\ > osql -L


another way is to run from powershell, open "powershell ise" and run as administrator

now run command

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

it will give information as following

ServerName InstanceName IsClustered Version
---------- ------------ ----------- -------

Count of each table in database

the query below will generate sql statements to count number of rows in each table on the database.

SELECT 'select '''+ name + ''', count(*) from [' + name + '] union'
FROM Sysobjects
WHERE Type='U'
ORDER BY 1;

Instant Initialisation

In Windows 2008
gpedit.msc --> computer configuration --> windows settings --> security settings --> local policies --> user rights assignments --> "lock pages in memory"
give sql server service account here.

align primary disk partitions

How to set primary partition of disk drives on a server
cmd --> diskpart
list disk
select disk
create partition primary align = 1024

now goto computer management --> storage
format disk with specified block (64k for compellent and 4k for ramsan)
assign drive letter and label the drive

Note: if any error message comes as like disk is write protected, then
goto disk management --> brign it online --> initialise disk

Set DB to Single/Multi User Mode

-- setting database to single user mode
ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

-- setting database to multi user mode
ALTER DATABASE db_name SET MULTI_USER

SQL Server Configuration Manager - SQL Server Services - Exception

When I opened SQL Server 2008 R2 – SQL Server Configuration Manager, under "SQL Server Services" in the right hand pane had the following error "The server threw an exception. [0x80010105]"

Solution to this problem i found after doing research on internet:

Restart the "Windows Management Instrumentation" service on machine where SQL Configuration Manager is installed.

Start -> Run -> "Services.msc", scroll down to "Windows Management Instrumentation", right-click and "Restart"

Note: You may have to check with system administrator as there may be few other services dependent on this service and may be restarted as well.

Close and reopen SQL Server Configuration Manager and hopefully you'll see all SQL Server Services.

SSIS - Delete list of projects or files from Recent Projects

Close Visual Studio (if its open)

GOTO > Start > Run > RegEdit

GOTO > HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\\ProjectMRUList
remove unnecessary items from list.

Similarly repeat the steps for FileMRuList
GOTO > HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\\FileMRUList
remove unnecessary items from list.

To Clear the Find and Replace List
GOTO > HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\\Find
remove unnecessary items from list.