Useful Database Queries

Repair Database syntax
DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS)

Alter user mode syntax
alter database [database_name] set multi_user


reset the login’s default database.
alter login sa with default_database = master


This is the command to reset user password:
sp_password @new = ‘new_password’, @loginame = ‘user’

Restore DB:

USE master
RESTORE DATABASE dbname
FROM DISK = ‘c:\db\test\test.bak’
WITH MOVE ‘test_data’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test_data.mdf’,
MOVE ‘lts_log’ TO ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\test_log.ldf’,
STATAS = 1, REPLACE
GO

Show database connections
Sp_who2 active

Show sleeping connections
USE master
SELECT * FROM dbo.sysprocesses
where dbid = (select db_id(‘Contaxo’))
and status = ‘sleeping’
GO

Here’s another one

Select count(*) as num, db_Name(dbID)
from master..SysProcesses
where status=’sleeping’
and spId <> @@SpId
and dbID <> 0 group by db_Name(dbID) order by num desc

Kill DB sleeping connection
SELECT ‘KILL ‘ + CONVERT (char(3),spid) FROM dbo.sysprocesses
where dbid = (select db_id(‘DBName’))
and status = ‘sleeping’
GO


Use the following to kill all connections

– To kill all process

USE master
go

DECLARE @dbname sysname

SET @dbname = ‘HSRA’ — name of database you want to drop connections from

DECLARE @spid int
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
WHILE @spid IS NOT NULL
BEGIN
EXECUTE (‘KILL ‘ + @spid)
SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
END

Query to showup the top 20 DBs using highest CPU

use master
go
select top 20 sum(cpu) as c , databasename from "dec16th" group by databasename order by c desc

Set database as read-only:
USE master;
GO
ALTER DATABASE Test
SET READ_ONLY WITH NO_WAIT
GO

Use this to set it as writable:
USE master;
GO
ALTER DATABASE Test
SET READ_WRITE WITH NO_WAIT
GO


Use this to check DB status (OFFLINE, ONLINE, READ_ONLY, READ_WRITE, SINGLE_USER, RESTRICTED_USER, MULTI_USER):
SELECT DATABASEPROPERTYEX(‘TEST’, ‘Updateability’);


Change our SQL Server Compatibility Level command:
Exec sp_dbcmptlevel ‘dbname’, ’90′
Referring url:
http://codebetter.com/blogs/raymond.lewallen/archive/2005/03/23/60497.aspx


Attach SQL express to SQL server command:
CREATE DATABASE dbname
ON PRIMARY
(FILENAME = ‘filepath to sql express file’)
FOR ATTACH

To disable show all db list from ssms regular login:
use master
Revoke View any database from public
go
 
can test it with the following:
Create Login newuser with password = ’12%E56WW’
go
Create Database newuserdb
go

use newuserdb
Exec Sp_changedbowner ‘newuser’
To rename a database, do the following to make sure everything correct:
 

USE master
GO
EXEC sp_dboption old_name, ‘Single User’, True
GO
EXEC sp_renamedb ‘old_name’, ‘new_name’
GO
EXEC sp_dboption new_name, ‘Single User’, False
GO
this query is good to check db high cpu:

Select  sum(cpu) as c, db_Name(dbID)
                        from master..SysProcesses
                        where  spId <> @@SpId
                        and dbID <> 0 group by db_Name(dbID) order by c desc

Assumming the db and phisical mdf file name are different, You can find out by running the following query in server:

use master
go
select name, filename from master.dbo.sysdatabases
the query will show up all dbname with correspond file names, you can run the following to check the specific one:

use master
go
select name, filename from master.dbo.sysdatabases
where filename=’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\NAME.MDF’


MSSQL turn on full textt search

exec sp_fulltext_database ‘enable’


Mssql change collate

ALTER DATABASE database_name COLLATE collation_name

Share

- has written 121 posts.


Leave a Reply

You must be logged in to post a comment.