Get Last Tables Access or Structure Changed on SQL Server

There are two facts of table that DBA want to check

1. For Tables structural change

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

DROP TABLE #lastaccess1;

CREATE TABLE #lastaccess1(

    [DBName] sysname,

    [TableNames] NVARCHAR(256),

    [create_date] datetime,

    [modify_date] datetime,

   )

DECLARE @Statement1 VARCHAR(4000)

SET @Statement1 = 'USE [?]

SELECT  ''?'' as [DBNAME],  [TableName] = name,

create_date, modify_date

FROM    sys.tables

ORDER BY modify_date DESC'

INSERT INTO #lastaccess1([DBName] ,[TableNames] ,[create_date], [modify_date] )

EXEC sp_MSforeachdb @Statement1;

SELECT * FROM #lastaccess1 WHERE [DBNAME] not in ('master', 'msdb','model','tempdb');

DROP TABLE #lastaccess1;

[T-SQL] Recursive T-SQL to do “DENY IMPERSONATE ON LOGIN”

The situation depends on what your SQL environment are? For my case, I need to “DENY IMPERSONATE ON LOGIN::” to most of DBA logins and general Logins except real necessary DBAs. But I don’t like to COPY/PASTE command for lots users (if your server has over 20 accounts), so I created a script to do this.

It’s an inefficient T-SQL script due to my desperate boss wants to see the results for all SQL instances… if you can modify it for me, I will be very appreciated.

SQL Login User Dropped Failed

Situation:

The User1 is no longer in the company and only "Public" server role. When I type command:
"Drop Login [xxx\User1]", it showed the errors below
Login 'xxx\User1' has granted one or more permission(s). Revoke the permission(s) before dropping the login.

[SNMP TRAP: 15006 in CPQCLUS.MIB]

Today I've tried failover the server to the other one. It's been failed all the time but not the disks or network adapter.
Just SQL engine and SQL agent..... I cannot find any answers online.......
So I was disappointed and want to reinstall it. When I did that , I found one thing was very interesting..
The DB that our junior DBA installed was not installed as the Failover node but a stand-alone...
OMG..

Windows 7 Resource Monitor Not Working (No data shown)

1. Make sure your "Theme" service is running
2. Then "System Properties" -> "Advanced" -> "Performance"

3. Make Sure "Use Visual Styles on windows and buttons" is checked.

If failed, repeat to 1-3 or restart. It should be fixed

xp_cmdshell executed BCP statement error

Here is the error:
Msg 214, Level 16, State 201, Procedure xp_cmdshell, Line 1
Procedure expects parameter 'command_string' of type 'varchar'.
WHY?
Because xp_cmdshell CANNOT use varchar(max) with xp_cmdshell

Customize the Size of the Windows 7 Taskbar Icons

I dislike when I click the icons on the Windows taskbar then the icon will become large.
It's fine when you just need click one icon at a time.
But if you want to click two and more icons quickly at a short time?
Sometimes you will click the software twice or more especially Outlook

Database State is staying at Restoring status

It's one of solutions...

--Check DB state
select state_desc,name,user_access_desc from sys.databases where name='xxxxx'

Monitoring Table Size Growth in SQL Server

refer to http://www.sql-server-performance.com/2014/monitor-data-growth/
By Yaniv Etrogi

Msg 1807 Could not obtain exclusive lock on database 'model'. Retry the operation later.

Errors like this:

Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'model'. Retry the operation later.
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.