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

Advertisements

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.

Structured Query Language (SQL)

SQL is a standard language for accessing and manipulating databases.
According to ANSI (American National Standards Institute), it is the standard language for relational database management systems.
SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database.

The standard SQL commands are such as “Select”, “Insert”, “Update”, “Delete”, “Create”, and “Drop”.The data in RDBMS is stored in database objects called tables.

A table is a collections of related data entries and it consists of columns and rows.

SQL can be divided into:
The Data Manipulation Language (DML) and
the Data Definition Language (DDL) ,
Data Control Language (DCL) and
Transactional Control Language (TCL) .

1) The query and update commands form the DML part of SQL.
SELECT – extracts data from a Table.
UPDATE – updates data in a Table.
DELETE – deletes data from a Table.
INSERT INTO – inserts new data into a Table.

2) The DDL part of SQL permits database tables to be created or deleted. The most important DDL statements in SQL are:
CREATE DATABASE – creates a new database.
ALTER DATABASE – modifies a database.
CREATE TABLE – creates a new table.
ALTER TABLE – modifies a table.
DROP TABLE – deletes a table.
CREATE INDEX – creates an index (search key).

3) DCL: DCL stands for Data Control Language. DCL is used to create roles, grant and revoke permissions, establish referential integrity etc.
Examples: GRANT, REVOKE statements

4) TCL: TCL stands for Transactional Control Language. TCL is used to manage transactions within a database.
Examples: COMMIT, ROLLBACK statements

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.