SQL Server - Kill process attached to database

If you are trying to run a command

And you can't get access to the database because database might be in use by the single user and is in the single user mode.

There is a work around.

Run This command

SP_Who will show you processes attached to your database. See DBNAME Column and find your database in it. Then note down SPID. For example 72 is the SPID of the process you want to kill.
Then run this command to kill the process.

Kill command will kill the process. And then you will be able to run Set multi user mode command again and it should work.

Shrink database and check its progress

Claiming space from a SQL Server database is not like deleting all rows from a table or truncate it. Please follow the instructions if you want to reclaim your disk space.

1. Run Sql Command:
   DBCC SHRINKDATABASE (MyDBName, 10 )
   Here MyDBName is a valid database name and 10 is the percentage of reserved free space.
2. Run following command to check percentage done:
   select percent_complete, total_elapsed_time, estimated_completion_time, * from sys.dm_exec_requests where command = 'DbccFilesCompact'

SQL Server force identity insert for autoseed pk

  use InspectionManager
  GO
  set identity_insert dbo.report ON 
  go
  insert into Report(ReportID, InspectionID, Name, DateCreated) values(124842,161815, 'wa report', '2013-07-01 20:59:09.297')
  set identity_insert report OFF
  go

MS SQL Shrink DB Log File

USE [master]
GO
ALTER DATABASE YourDBName SET RECOVERY SIMPLE WITH NO_WAIT

USE YourDBName;
GO
DBCC SHRINKFILE (YourDBName_Log_File_Logial_Name, 1024);
GO

USE [master]
GO
ALTER DATABASE YourDBName SET RECOVERY FULL WITH NO_WAIT
GO

SQL Server - Release database connections

use master
ALTER DATABASE YourDBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--do you stuff here

ALTER DATABASE YourDBName SET MULTI_USER

Add Firewall Exception for Sql Sever Connection over Network

1. On the Start menu, click Run, type WF.msc, and then click OK.
2. In the Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then click New Rule in the action pane.
3. In the Rule Type dialog box, select Port, and then click Next.
4. In the Protocol and Ports dialog box, select TCP. Select Specific local ports, and then type the port number of the instance of the Database Engine, such as 1433 for the default instance. Click Next.
5. In the Action dialog box, select Allow the connection, and then click Next.
6. In the Profile dialog box, select any profiles that describe the computer connection environment when you want to connect to the Database Engine, and then click Next.
7. In the Name dialog box, type a name and description for this rule, and then click Finish.

Cascading and Bulk Delete through one stored procedure

Create PROCEDURE CascadeDelete
@table varchar(100), -- Table name
@column varchar(100), -- Primary key column name
@value varchar(max) -- Must be comma separated list of primary keys 
AS

Print 'Need to delete from ' + @table + ' where ' + @column 
+ ' IN (' + convert(varchar, @value) + ')'

DECLARE @refrencingTable varchar(100)
DECLARE @refrencingColumn varchar(100)
DECLARE @refrencingTablesPrimaryKeyColumn varchar(100)

DECLARE @sql varchar(4000)
DECLARE @keyval bigint

BEGIN TRY
SET @sql = 'delete from ' + @table + ' where ' + @column 
+ ' IN (' + convert(varchar, @value) + ')'
EXEC (@sql)
PRINT 'Bulk delete worked' 
-- Deleting was successful becuase the table does not seem to refer anything
END TRY
BEGIN CATCH
-- first, find all the objects which refer to this object
DECLARE ref CURSOR LOCAL FOR -- ref is a cursor to find references
SELECT DISTINCT
OBJECT_NAME(f.parent_object_id) AS table_name
, COL_NAME(fc.parent_object_id, fc.parent_column_id) AS constraint_column_name
, sc.name as table_key
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.object_id = fc.constraint_object_id
JOIN sys.columns as sc
ON f.parent_object_id = sc.object_id
JOIN sys.indexes as i
on f.parent_object_id = i.object_id
AND i.is_primary_key = 1
JOIN sys.index_columns as ic
on i.index_id = ic.index_id
AND i.object_id = ic.object_id
AND i.is_primary_key = 1
AND sc.column_id = ic.column_id
WHERE f.referenced_object_id = OBJECT_ID(@table);

-- loop over the referring objects
OPEN ref
FETCH NEXT FROM ref INTO @refrencingTable, @refrencingColumn, 
@refrencingTablesPrimaryKeyColumn
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC cascadeDelete @refrencingTable, @refrencingColumn, @value 
-- Self call to delete references
print @table + ' is referenced by ' + @refrencingTable + '.' + @refrencingColumn

-- get all the id values for all the referring records and put them into a temp table
SET @sql = 'SELECT ' + @refrencingTablesPrimaryKeyColumn 
+ ' as keyval FROM ' + @refrencingTable + ' WHERE ' + @refrencingColumn 
+ ' IN (' + CONVERT(varchar, @value) + ')'

CREATE TABLE #temp (
keyval int
)

INSERT INTO #temp
EXEC (@sql)

-- loop over the table and for each row, use cascase delete to delete it.
DECLARE del CURSOR LOCAL FOR
SELECT keyval FROM #temp

OPEN del
FETCH NEXT FROM del INTO @keyval
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC cascadeDelete @refrencingTable, @refrencingTablesPrimaryKeyColumn, @keyval

FETCH NEXT FROM del INTO @keyval
END
CLOSE del
DEALLOCATE del

DROP TABLE #temp

FETCH NEXT FROM ref INTO @refrencingTable, @refrencingColumn, 
@refrencingTablesPrimaryKeyColumn
END
CLOSE ref
DEALLOCATE ref

SET @sql = 'DELETE FROM ' + @table + ' WHERE ' + @column 
+ ' IN (' + CONVERT(varchar, @value) + ')'
PRINT @sql
EXEC (@sql)
END CATCH

MSDN Developer Samples: Learn Through Code

Click on this link .....

Truncate db

-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- delete data in all tables
EXEC sp_MSForEachTable "DELETE FROM ?"

-- enable all constraints
exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Drop all objects from a sql server database

-------------START---------------------------------
declare @n char(1)
set @n = char(10)
declare @stmt nvarchar(max)

-- procedures
select @stmt = isnull( @stmt + @n, '' ) +
    'drop procedure [' + name + ']'
from sys.procedures

-- check constraints
select @stmt = isnull( @stmt + @n, '' ) +
    'alter table [' + object_name( parent_object_id ) + '] drop constraint [' + name + ']'
from sys.check_constraints

-- functions
select @stmt = isnull( @stmt + @n, '' ) +
    'drop function [' + name + ']'
from sys.objects
where type in ( 'FN', 'IF', 'TF' )

-- views
select @stmt = isnull( @stmt + @n, '' ) +
    'drop view [' + name + ']'
from sys.views

-- foreign keys
select @stmt = isnull( @stmt + @n, '' ) +
    'alter table [' + object_name( parent_object_id ) + '] drop constraint [' + name + ']'
from sys.foreign_keys

-- tables
select @stmt = isnull( @stmt + @n, '' ) +
    'drop table [' + name + ']'
from sys.tables

-- user defined types
select @stmt = isnull( @stmt + @n, '' ) +
    'drop type [' + name + ']'
from sys.types
where is_user_defined = 1

exec sp_executesql @stmt
--------------------------END---------------------

Sql Server DB Diagram Permissions Workaround

EXEC sp_dbcmptlevel 'YOURDBNAME', '90';
go
ALTER AUTHORIZATION ON DATABASE::YOURDBNAME TO "sa"
go
use [YOURDBNAME]
go
EXECUTE AS USER = N'dbo' REVERT
go