To Shrink Tempdb

When Tempdb Folder is full follow this steps Below.......

1. Check any open transaction are running by below query on master

               DBCC opentran()

     If there is any transactions running if required, kill them or wait until they complete.

2. Use Tempdb Check how many data files are there by right-click on Tempdb > Properties > Files

3. Keep 1024 Mb as Size in data Files and log files and Script it out > run them

If this doesn't work follow below Steps

1.  Check any open transaction are running by below query on master

               DBCC opentran()

     If there is any transactions running if required, kill them or wait until they complete.

2. Use tempdb and run the below query

          Use [tempdb]

           go

          Checkpoint

           go

           DBCC DROPCLEANBUFFERS

           go

           DBCC FREESYSTEMCACHE ('ALL');

           go

           DBCC FREESESSIONCACHE;

            go

To Script out Database user level permissions

 

DECLARE

    @sql VARCHAR(2048)

    ,@sort INT

 DECLARE tmp CURSOR FOR

/*********************************************/

/*********   DB CONTEXT STATEMENT    *********/

/*********************************************/

SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --],

        1 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT  'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --],

        1 AS [-- RESULT ORDER HOLDER --]

 UNION

 SELECT '' AS [-- SQL STATEMENTS --],

        2 AS [-- RESULT ORDER HOLDER --]

 UNION

 /*********************************************/

/*********     DB USER CREATION      *********/

/*********************************************/

 SELECT '-- [-- DB USERS --] --' AS [-- SQL STATEMENTS --],

        3 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT  'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' FOR LOGIN ' + QUOTENAME([name]) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + 'END; ' AS [-- SQL STATEMENTS --],

        4 AS [-- RESULT ORDER HOLDER --]

FROM    sys.database_principals AS rm

WHERE [type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups

 UNION

 /*********************************************/

/*********    DB ROLE PERMISSIONS    *********/

/*********************************************/

SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],

        5 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT  'EXEC sp_addrolemember @rolename ='

    + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS [-- SQL STATEMENTS --],

        6 AS [-- RESULT ORDER HOLDER --]

FROM    sys.database_role_members AS rm

WHERE   USER_NAME(rm.member_principal_id) IN ( 

                                                --get user names on the database

                                                SELECT [name]

                                                FROM sys.database_principals

                                                WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas

                                                and [type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group

                                              )

--ORDER BY rm.role_principal_id ASC

 UNION

 SELECT '' AS [-- SQL STATEMENTS --],

        7 AS [-- RESULT ORDER HOLDER --]

 UNION

 /*********************************************/

/*********  OBJECT LEVEL PERMISSIONS *********/

/*********************************************/

SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],

        8 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT  CASE

            WHEN perm.state <> 'W' THEN perm.state_desc

            ELSE 'GRANT'

        END

        + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) --select, execute, etc on specific objects

        + CASE

                WHEN cl.column_id IS NULL THEN SPACE(0)

                ELSE '(' + QUOTENAME(cl.name) + ')'

          END

        + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default

        + CASE

                WHEN perm.state <> 'W' THEN SPACE(0)

                ELSE SPACE(1) + 'WITH GRANT OPTION'

          END

            AS [-- SQL STATEMENTS --],

        9 AS [-- RESULT ORDER HOLDER --]

FROM   

    sys.database_permissions AS perm

        INNER JOIN

    sys.objects AS obj

            ON perm.major_id = obj.[object_id]

        INNER JOIN

    sys.database_principals AS usr

            ON perm.grantee_principal_id = usr.principal_id

        LEFT JOIN

    sys.columns AS cl

            ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

--WHERE usr.name = @OldUser

--ORDER BY perm.permission_name ASC, perm.state_desc ASC

UNION

SELECT '' AS [-- SQL STATEMENTS --],

    10 AS [-- RESULT ORDER HOLDER --]

 UNION

 /*********************************************/

/*********    DB LEVEL PERMISSIONS   *********/

/*********************************************/

SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],

        11 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT  CASE

            WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option

            ELSE 'GRANT'

        END

    + SPACE(1) + perm.permission_name --CONNECT, etc

    + SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO <user name>

    + CASE

            WHEN perm.state <> 'W' THEN SPACE(0)

            ELSE SPACE(1) + 'WITH GRANT OPTION'

      END

        AS [-- SQL STATEMENTS --],

        12 AS [-- RESULT ORDER HOLDER --]

FROM    sys.database_permissions AS perm

    INNER JOIN

    sys.database_principals AS usr

    ON perm.grantee_principal_id = usr.principal_id

--WHERE usr.name = @OldUser

 

WHERE   [perm].[major_id] = 0

    AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas

    AND [usr].[type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group

 UNION

 SELECT '' AS [-- SQL STATEMENTS --],

        13 AS [-- RESULT ORDER HOLDER --]

 UNION

 SELECT '-- [--DB LEVEL SCHEMA PERMISSIONS --] --' AS [-- SQL STATEMENTS --],

        14 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT  CASE

            WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option

            ELSE 'GRANT'

            END

                + SPACE(1) + perm.permission_name --CONNECT, etc

                + SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>

                + QUOTENAME(SCHEMA_NAME(major_id))

                + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default

                + CASE

                    WHEN perm.state <> 'W' THEN SPACE(0)

                    ELSE SPACE(1) + 'WITH GRANT OPTION'

                    END

            AS [-- SQL STATEMENTS --],

        15 AS [-- RESULT ORDER HOLDER --]

from sys.database_permissions AS perm

    inner join sys.schemas s

        on perm.major_id = s.schema_id

    inner join sys.database_principals dbprin

        on perm.grantee_principal_id = dbprin.principal_id

WHERE class = 3 --class 3 = schema

 ORDER BY [-- RESULT ORDER HOLDER --]

 OPEN tmp

FETCH NEXT FROM tmp INTO @sql, @sort

WHILE @@FETCH_STATUS = 0

BEGIN

        PRINT @sql

        FETCH NEXT FROM tmp INTO @sql, @sort   

END

 CLOSE tmp

DEALLOCATE tmp

 

To check progress of SQL Server Backup or Restore process

SELECT

   r.session_id

 , r.command

 , CONVERT(NUMERIC(6,2), r.percent_complete) AS [Percent Complete]

 , CONVERT(VARCHAR(20), DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time]

 , CONVERT(NUMERIC(10,2), r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min]

 , CONVERT(NUMERIC(10,2), r.estimated_completion_time/1000.0/60.0) AS [ETA Min]

 , CONVERT(NUMERIC(10,2), r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours]

 , CONVERT(VARCHAR(1000),

      (SELECT SUBSTRING(text,r.statement_start_offset/2, CASE WHEN r.statement_end_offset = -1

                                                             THEN 1000

                                                             ELSE (r.statement_end_offset-r.statement_start_offset)/2

                                                        END)

        FROM sys.dm_exec_sql_text(sql_handle)

       )

   ) AS [SQL]

  FROM sys.dm_exec_requests r

 WHERE command IN ('RESTORE DATABASE', 'BACKUP DATABASE')

   

To check Server level configure settings

SP_configure

--------- To check Advance Server level configuration settings

SP_configure 'show advanced options' ,1
reconfigure

To find all databases data files size and free space

  

IF OBJECT_ID('tempdb..#DBFiles') IS NOT NULL

    DROP TABLE #DBFiles

CREATE TABLE #DBFiles

(

[id] INT IDENTITY(1,1), 

[DBName] VARCHAR(200),

[Recovery] VARCHAR(200) NULL,

[File_Name] VARCHAR(200) NULL,

[Type_Desc] VARCHAR(20) NULL,

[physical_name] VARCHAR(MAX) NULL,

[FileSize_MB] DECIMAL(10,2) NULL,

[UsedSpace_MB] DECIMAL(10,2) NULL,

[FreeSpace_MB] DECIMAL(10,2) NULL,

[FreeSpace_%] DECIMAL(10,2) NULL,

[State_Desc] VARCHAR(20) NULL,

[AutoGrow] VARCHAR(200) NULL

)

INSERT INTO #DBFiles

SELECT 

db.[name] AS [DBName],

db.recovery_model_desc,

files.[name] AS [File_Name],

files.[type_desc],

files.physical_name,

CONVERT(DECIMAL(10,2), files.SIZE/128.0) AS [FILESIZE_MB],

NULL AS [UsedSpace_MB],

NULL AS [FreeSpace_MB],

NULL AS [FreeSpace_%],

files.state_desc,

CASE is_percent_growth 

WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -'

        WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -'

ELSE '' END 

        + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted'

ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB'

END 

        + CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting! - dbtales.com]'

ELSE ''

END AS [AutoGrow]

FROM sys.master_files AS files

INNER JOIN sys.databases AS db

ON files.database_id = db.database_id

WHERE db.[name] NOT IN ('model','tempdb','msdb','master');

EXEC sp_msforeachdb '

USE [?];

UPDATE #DBFiles

SET [USEDSPACE_MB] = CONVERT(DECIMAL(10,2), SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(dbf.[name], ''SpaceUsed'') AS INT)/128.0)),

[FREESPACE_MB] = CONVERT(DECIMAL(10,2), dbf.SIZE/128.0 - CAST(FILEPROPERTY(dbf.NAME, ''SpaceUsed'') AS INT)/128.0),

[FREESPACE_%] = CONVERT(DECIMAL(10,2), ((dbf.SIZE/128.0 - CAST(FILEPROPERTY(dbf.NAME, ''SpaceUsed'') AS INT)/128.0)/(dbf.SIZE/128.0))*100)

FROM #DBFiles AS temp

INNER JOIN sys.database_files AS dbf 

ON temp.[physical_name] = dbf.[physical_name] COLLATE DATABASE_DEFAULT '

SELECT * FROM #DBFiles ORDER BY DBName

To find all replication tables

-- create table with only the names of databases that are published

SELECT name as [DatabaseName]

INTO #tmpPubDatabases

FROM sys.databases

WHERE database_id > 4

AND is_published = 1; 

-- create table to hold the table info (name, schema,row count, space used)

CREATE TABLE #tmpTableSizes(

DBName VARCHAR(256),

SchemaName VARCHAR(256),

TableName VARCHAR(256),

RowCounts INT,

TotalSpaceMB DECIMAL(18,2)

);

DECLARE @command VARCHAR(MAX);

-- run this in all the databases that have publications 

SET @command = '

USE [?]

IF DB_NAME() IN (SELECT DatabaseName FROM #tmpPubDatabases)

BEGIN

INSERT #tmpTableSizes

SELECT 

db_Name(),

s.Name AS SchemaName,

t.NAME AS TableName,

p.rows AS RowCounts,

(SUM(a.total_pages) * 8)/1024.0 AS TotalSpaceMB

FROM 

sys.tables t

INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id

WHERE 

t.NAME NOT LIKE ''dt%'' 

AND t.is_ms_shipped = 0

AND i.OBJECT_ID > 255

--AND t.Name IN ()

GROUP BY 

t.Name, s.Name, p.Rows

ORDER BY 

t.Name

END';

-- run for all affected databases

EXEC sp_MSforeachdb @command

-- this will match the publications to the tables and give the you row count and sizes

-- run this in the distribution database

SELECT

     P.[publication]   AS [PublicationName]

    ,A.[publisher_db]  AS [DatabaseName]

    ,A.[article]       AS [ArticleName]

    ,A.[source_owner]  AS [Schema]

    ,A.[source_object] AS [Table]

,T.RowCounts

,T.TotalSpaceMB

FROM

    [distribution].[dbo].[MSarticles] AS A

    INNER JOIN [distribution].[dbo].[MSpublications] AS P ON (A.[publication_id] = P.[publication_id])

LEFT JOIN #tmpTableSizes T ON A.[publisher_db] = T.DBName AND A.[source_owner] = T.SchemaName AND A.[source_object] = T.TableName

ORDER BY

    P.[publication], A.[article];

-- clean up

DROP TABLE #tmpTableSizes;

DROP TABLE #tmpPubDatabases;

How to change all DB's compatibility level at a time

USE [master]

GO

select 'ALTER DATABASE ['+name+'] SET COMPATIBILITY_LEVEL = 130' from sys.databases where database_id > 4

Change all Database owners at a time

USE [master]

GO

select 'ALTER AUTHORIZATION ON DATABASE::' + name + ' TO sa' from sys.databases 

To find list of the job names and step associated with a particular table

Use MSDB

go

SELECT 

    [sJOB].[job_id] AS [JobID]

    , [sJOB].[name] AS [JobName]

    ,step.step_name

    ,step.command

FROM

    [msdb].[dbo].[sysjobs] AS [sJOB]

    LEFT JOIN [msdb].dbo.sysjobsteps step ON sJOB.job_id = step.job_id

WHERE step.command LIKE '%MYTABLENAME%'

ORDER BY [JobName]