Check version of sql you are running on server:

Step 1:By using SQL Server Management Studio right clicking on properties of instance name. Here you can see the “Product version” or “Version”.

Step 2:Using T-SQL command.
SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’),SERVERPROPERTY (‘edition’)
GO
OR
select @@version

Step 3:Using configuration manager
Open configuration manager expand sql server services right click on instance name. Go to “Advanced Tab” go to to “Stock Keeping Unit Name” and “Version”
First Digit of version:

8.0 for SQL Server 2000
9.0 for SQL Server 2005
10.0 for SQL Server 2008
10.5 for SQL Server 2008 R2
11.0 for SQL Server 2012
12.0 for SQL Server 2014
13.0 for SQL Server 2016

Find owner of maintenance plan

To view the owner of maintenance plan execute the following select statement.

–Above 2008
select [ownersid] , SUSER_SNAME([ownersid]) from msdb.dbo.sysssispackages
where [name] like ‘<mainatenanceplanname>’

To update owner of maintenance plan execute the below command:
update msdb.dbo.sysssispackages
set [ownersid] = suser_sid(‘<loginname>’)
where [name] like ‘<mainatenanceplanname>’

–For 2005
select name,owner from dbo.sysmaintplan_plans

UPDATE msdb.dbo.sysdtspackages90
SET OWNERSID = SUSER_SID(‘<loginname>’)
WHERE [Name] like ‘<mainatenanceplanname>’

–For 2000
select plan_name,owner from dbo.sysdbmaintplans

update dbo.sysdbmaintplans set owner =’LoginName’ where owner =’LoginName’

To change job owner expand SQL Server Agent, expand Jobs, right-click the job, and then click Properties.In the Owner list, select a login.
You must be a system administrator to change the owner of a job.

Get DB Size of all dbs

 

–Get size of all databases.

SELECT
database_name = DB_NAME(database_id)
, log_size_mb = CAST(SUM(CASE WHEN type_desc = ‘LOG’ THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, row_size_mb = CAST(SUM(CASE WHEN type_desc = ‘ROWS’ THEN size END) * 8. / 1024 AS DECIMAL(8,2))
, total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2))
FROM sys.master_files WITH(NOWAIT)
WHERE database_id = DB_ID() — for current db
GROUP BY database_id

How can I get the name of all columns of a table in SQL SERVER 2008?

SELECT *
FROM Databasename.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N’table name

Can be made over all these DB objects:
– CHECK_CONSTRAINTS
– COLUMN_DOMAIN_USAGE – COLUMN_PRIVILEGES
– COLUMNS
– CONSTRAINT_COLUMN_USAGE
– CONSTRAINT_TABLE_USAGE
– DOMAIN_CONSTRAINTS
– DOMAINS
– KEY_COLUMN_USAGE
– PARAMETERS
– REFERENTIAL_CONSTRAINTS
– ROUTINES
– ROUTINE_COLUMNS
– SCHEMATA
– TABLE_CONSTRAINTS
– TABLE_PRIVILEGES
– TABLES
– VIEW_COLUMN_USAGE
– VIEW_TABLE_USAGE
– VIEWS

Determine users connected to sqlserver database

There are three ways to find who is connected to a database in sqlserver.

First one:

Use the SQL SERVER Management Studio — Management — Activity Monitor
This gives a list of users, database names, status, command, transactions, application with which they are using the database, CPU, IO and Memory usage, Login time etc.

Second One:

Use the built in stored procedure called sp_who2
Run the command exec sp_who2
This gives a list of users, database names, status, command, program with which they are using the database, CPU time, Login time etc.

Third One:

Use the script
select spid, status, loginame, hostname, blocked, db_name(dbid), cmd from master..sysprocesses where db_name(dbid) = ‘databasename’

User this also
select * from sys.dm_exec_sessions

This gives the status, login name and host name for the database.

How to repair a Suspect Database in SQL Server?

Suspect state of SQL Server database is a state when you are unable to connect to the database.
In this state you cannot do anything with your database: no opening, no backup and no restore.

Possible cause for this problem can be one of the following:

1) Database is corrupted
2) Database files are being “opened” or held by some process (operating system, other program(s)…)
3) Not enough disk space for SQL Server
4) Insufficient memory (RAM) for SQL Server
5) Unexpected SQL Server shutdown caused by power failure etc…

SOLUTION for this problem as follows:

Connect to your database server using Microsoft SQL Server Management Studio
Execute the following SQL script:
NOTE: replace [DatabaseName] with your database name

— sp_resetstatus turns off the “suspect” flag on a database
EXEC sp_resetstatus [DatabaseName]

— Marking database READ_ONLY, disable logging,
— and limiting access only to members of the sysadmin fixed server role
ALTER DATABASE [DatabaseName] SET EMERGENCY

— Checks the logical and physical integrity of all the objects in the specified database
DBCC checkdb([DatabaseName])

— This query will rollback any transaction which is running on that database
— and bring SQL Server database in a “single user” mode
ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ([DatabaseName], REPAIR_ALLOW_DATA_LOSS)

— Set database accessibility to it’s original state, allowing all logins
ALTER DATABASE [DatabaseName] SET MULTI_USER

Your database should now be accessible and no longer marked as “suspect” database.