Script to display all Databases Sizes, Recovery Model, Free Space etc.,

DECLARE @DBInfo TABLE
( ServerName VARCHAR(100),
DatabaseName VARCHAR(100),
FileSizeMB INT,
LogicalFileName sysname,
PhysicalFileName NVARCHAR(520),
Status sysname,
Updateability sysname,
RecoveryMode sysname,
FreeSpaceMB INT,
FreeSpacePct VARCHAR(7),
FreeSpacePages INT,
PollDate datetime)

DECLARE @command VARCHAR(5000)

SELECT @command = 'Use [' + '?' + '] SELECT
@@servername as ServerName,
' + '''' + '?' + '''' + ' AS DatabaseName,
CAST(sysfiles.size/128.0 AS int) AS FileSize,
sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,
CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status,
CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability,
CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode,
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' +
'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name,
' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0))
AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + ' AS FreeSpacePct,
GETDATE() as PollDate FROM dbo.sysfiles'
INSERT INTO @DBInfo
(ServerName,
DatabaseName,
FileSizeMB,
LogicalFileName,
PhysicalFileName,
Status,
Updateability,
RecoveryMode,
FreeSpaceMB,
FreeSpacePct,
PollDate)
EXEC sp_MSForEachDB @command

SELECT
ServerName,
DatabaseName,
FileSizeMB,
LogicalFileName,
PhysicalFileName,
Status,
Updateability,
RecoveryMode,
FreeSpaceMB,
FreeSpacePct,
PollDate
FROM @DBInfo
ORDER BY
ServerName,
DatabaseName

To Find Free Space Along With Drive Space

USE MASTER
GO
CREATE TABLE #TMPFIXEDDRIVES (
DRIVE CHAR(1),
MBFREE INT)

INSERT INTO #TMPFIXEDDRIVES
EXEC xp_FIXEDDRIVES

CREATE TABLE #TMPSPACEUSED (
DBNAME VARCHAR(50),
FILENME VARCHAR(50),
SPACEUSED FLOAT)

INSERT INTO #TMPSPACEUSED
EXEC( 'sp_msforeachdb''use ?; Select ''''?'''' DBName, Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed from sysfiles''')

SELECT C.DRIVE,
CASE
WHEN (C.MBFREE) > 1000 THEN CAST(CAST(((C.MBFREE) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
ELSE CAST(CAST((C.MBFREE) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
END AS DISKSPACEFREE,
A.NAME AS DATABASENAME,
B.NAME AS FILENAME,
CASE B.TYPE
WHEN 0 THEN 'DATA'
ELSE TYPE_DESC
END AS FILETYPE,
CASE
WHEN (B.SIZE * 8 / 1024.0) > 1000 THEN CAST(CAST(((B.SIZE * 8 / 1024) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB'
ELSE CAST(CAST((B.SIZE * 8 / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB'
END AS FILESIZE,
CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) AS DECIMAL(15,2)) SPACEFREE,
B.PHYSICAL_NAME
FROM SYS.DATABASES A
JOIN SYS.MASTER_FILES B
ON A.DATABASE_ID = B.DATABASE_ID
JOIN #TMPFIXEDDRIVES C
ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE
JOIN #TMPSPACEUSED D
ON A.NAME = D.DBNAME
AND B.NAME = D.FILENME
ORDER BY DISKSPACEFREE,
SPACEFREE DESC

DROP TABLE #TMPFIXEDDRIVES

DROP TABLE #TMPSPACEUSED

Transfer Logins SQL2005 To SQL2005 / SQL2008

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length) BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLOOR(@tempint/16) SELECT @secondint = @tempint - (@firstint*16) SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SELECT @i = @i + 1 END SELECT @hexvalue = @charvalue GO IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GO CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS DECLARE @name sysname DECLARE @type varchar (1) DECLARE @hasaccess int DECLARE @denylogin int DECLARE @is_disabled int DECLARE @PWD_varbinary varbinary (256) DECLARE @PWD_string varchar (514) DECLARE @SID_varbinary varbinary (85) DECLARE @SID_string varchar (514) DECLARE @tmpstr varchar (1024) DECLARE @is_policy_checked varchar (3) DECLARE @is_expiration_checked varchar (3) DECLARE @defaultdb sysname IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR


SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

Msg 8941, Level 16, State 1, Line 1 Table error: Object ID 181575685, index ID 4, page (3:224866). Test (sorted [i].offset >= PAGEHEADSIZE) failed. Slot 159, offset 0x1 is invalid.Msg 8942, Level 16, State 1, Line 1 Table error: Object ID 181575685, index ID 4, page (3:224866). Test (sorted[i].offset >= max) failed. Slot 0, offset 0x9f overlaps with the prior row.

If corruption only in Non-Clustered Indexes then ....

If all the errors that checkDB returns refer to indexes with IDs of 2 or greater, then it indicates that all of the corruption is within the nonclustered indexes. Since the data in a nonclustered index is redundant these corruptions can be repaired without data loss.

If all of the errors that CheckDB picks up are in the nonclustered indexes, the recommended repair level will be Repair_Rebuild.

Msg 8941, Level 16, State 1, Line 1 Table error: Object ID 181575685, index ID 4, page (3:224866). Test (sorted [i].offset >= PAGEHEADSIZE) failed. Slot 159, offset 0x1 is invalid.Msg 8942, Level 16, State 1, Line 1 Table error: Object ID 181575685, index ID 4, page (3:224866). Test (sorted[i].offset >= max) failed. Slot 0, offset 0x9f overlaps with the prior row.
Those are just examples; there are many more possible errors.

In this case the corruption can be completely repaired by dropping the damaged nonclustered indexes and recreating them. Online index rebuilds (and some of the offline index rebuilds) read the old index to create the new and hence will encounter the corruption. Hence it's necessary to drop the old index completely and create a new one.

This is mostly what CheckDB with the repair_rebuild option will do, however the database must be in single user mode for the repair to be done. Hence it's usually better to manually rebuild the indexes as the database can remain online and in use while the affected indexes are recreated.

If there is insufficient time available to rebuild the affected index and there is a clean backup with an unbroken log chain, the damaged pages can be restored from backup.

Msg 2508, Level 16, State 3, Line 1 The In-row data RSVD page count for object "Broken1", index ID 0, partition ID 76911687695381, alloc unit ID 76911687695381 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.

This error indicates that the page has an incorrect value on it for the reserved space. In SQL 2000 it was possible for the row and page counts for a table or index to be incorrect, even negative. CheckDB did not pick this up. On SQL 2005, the counts should be correctly kept and CheckDB gives a warning when it finds this scenario

This is not a serious problem and it's trivial to fix. As the message says, run DBCC UPDATEUSAGE on the database in question and the warnings will disappear. This is common on databases upgraded from SQL 2000, for the reasons mentioned above, and should not occur on database created in SQL 2005/2008.

Some DMV's From Experts Site

1. Displays count of rows in each table

SELECT OBJECT_NAME(object_id) AS [ObjectName], object_id, index_id, row_count
FROM sys.dm_db_partition_stats;

2. Files Names And Paths of All Databases In Current Intance
sELECT DB_NAME([database_id])AS [Database Name], [file_id],
name, physical_name, type_desc
FROM sys.master_files
WHERE [database_id] > 4 AND [database_id] <> 32767
OR [database_id] = 2;


SELECT OBJECT_NAME(object_id) AS [ObjectName], *
FROM sys.dm_db_partition_stats;

SELECT *
FROM sys.dm_exec_connections;

SELECT *
FROM sys.dm_exec_requests;

SELECT *
FROM sys.dm_exec_sessions;

SELECT *
FROM sys.dm_tran_database_transactions;

SELECT *
FROM sys.dm_tran_active_transactions

Find Database File Size And Log File Size

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'AdventureWorks'

SQL Server Information In One Command

Exec Xp_Msver

To Know When Last Backup & Last Restore Completed

select user_name, backup_size, database_name, backup_start_date, backup_finish_date from msdb.dbo.backupset

msdb.dbo.restorehistory -- This will give complete information about the restoring databases and all.

Backup All Databases

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\Backup\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

To Know How Much Percentage Database Is Restored Through T-SQL

select percent_complete, status, start_time, command
from sys.dm_exec_requests where command = 'RESTORE DATABASE'

Missed Backups In Last 10 Days

SELECT sd.name,
bs.TYPE,
bs.database_name,
bs.backup_start_date as last_backup
FROM master..sysdatabases sd
Left outer join msdb..backupset bs on rtrim(bs.database_name) = rtrim(sd.name)
left outer JOIN msdb..backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE sd.name = 'DBA_Info' and bs.backup_start_date > getdate() - 10
Order by sd.name,last_backup

xp_cmdshell - Tip

It is not possible to execute any executable file using xp_cmdshell from SQL Server

Simple Command To Get SQL Server Version Details And Complete Information

Run the following command from Query Analyzer
xp_msver

All Table Space in a database

Select a.name "TABLE_NAME", b.rowcnt "ROWS",b.used*8/1024 "SPACE USED(MB)", b.reserved*8/1024 "ALLOCATED SPACE(MB)",a.crdate "DOB"
from sysobjects a, sysindexes b where a.xtype='U' and b.indid in (0,1) and a.id=b.id

Count each objects in the database

select case xtype
when 'U' then 'User Tables'
when 's' then 'System Tables'
when 'v' then 'Views'
when 'TR' then 'Triggers'
when 'p' then 'Stored procedures'
when 'pk' then 'Primary Keys'
when 'x' then 'Extended stored procedures'
when 'uq' then 'UNIQUE constraints'
when 'tf' then 'Table functions'
when 'if' then 'In-lined Table Functions'
when 'fn' then 'Scalar Functions '
when 'c' then 'CHECK Constraints'
when 'd' then 'Default'
when 'f' then 'FOREIGN KEY constraint'
when 'sq' then 'Service Broker Queues'
else 'Others' end as "OBJECT TYPE", count(*) as "OBJECT COUNT"
from sysobjects group by xtype

Statistics update date

SELECT o.name AS Table_Name,i.name AS Index_Name,STATS_DATE(o.id,i.indid)
AS Date_Updated FROM sysobjects o JOIN sysindexes i ON i.id = o.id
WHERE xtype = 'U' AND i.name IS NOT NULL ORDER BY o.name ASC,i.name ASC

Read SQL Services account by TSQL

DECLARE @serviceaccount varchar(100)
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER',
N'ObjectName',
@ServiceAccount OUTPUT,
N'no_output'
SELECT @Serviceaccount as SQLServer_ServiceAccount
set @Serviceaccount =''
EXECUTE master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT',
N'ObjectName',
@ServiceAccount OUTPUT,
N'no_output'

SELECT @Serviceaccount as SQLAgent_ServiceAccount