SQLMint
T-SQL
select
name, page_verify_option_desc, log_reuse_wait_desc
FROM
sys.databases
WHERE
log_reuse_wait_desc <>’NOTHING’
and
page_verify_option_desc<>’CHECKSUM’
ORDER BY
name,
page_verify_option_desc,
log_reuse_wait_desc asc
Other useful commands:
Short script to list all SQL login accounts [non windows NT accounts]
select
name
from
master..syslogins
where
isntname = 0
and
name not like ‘##%’
order by
loginname;
use master
go
select
cntr_value
from
sysperfinfo
where
counter_name = ‘Number of Deadlocks/sec’
order by
cntr_value
Related error log messages might show up like that shown below:
Source spid29s
Message Failure to send an event notification instance of type ‘DEADLOCK_GRAPH’ on conversation handle ‘{E96F2738-83CF-E111-8D19-00237D585B2C}’. Error Code = ‘8429’.
Below is an output from a sql login session, the sql login account has select permissions, and attempts to truncate [quickly delete rows without logging each transaction] the table which has over 10,000 rows of data.
SQL Code
truncate table [AdventureWorks2012].[dbo].[MyList]
Result ==========
Msg 1088, Level 16, State 7, Line 1 Cannot find the object “MyList” because it does not exist or you do not have permissions.
Solution
use [AdventureWorks2012]
GO
GRANT CONTROL ON [dbo].[MyList] TO [myuid]
GO
Result
In the above example, myuid is the name of the sql login account
This is a quick way to copy data from one table to the other. The other table will be created.
See example below, using the AdventureWorks2012 sample database from Microsoft
SELECT
[AddressID] ,[AddressLine1] ,[AddressLine2] ,[City] ,[StateProvinceID] ,[PostalCode] ,[SpatialLocation] ,[rowguid] ,[ModifiedDate]
into MyList
FROM [AdventureWorks2012].[Person].[Address]
Result (19614 row(s) affected)