Sitting in on Maciej Pilecki’s BASTA! 2009 session on “Advanced SQL Server Troubleshooting” is fun.
First of all it gives a good overview of many topics for which information usually is spreaded.
And there are many new things, for instance some of them about login failures:
Understanding “login failed” – the “state” part is important!
State=16 is undocumented, it can mean that the Administrator Studio is not being run with “Administrative Privileges” (UAC!) under Vista or Windows 7, or you are not allowed to login to the particular database.
Some other useful links
How to use Kerberos authentication in SQL Server (KB 319723).
Cannot create SSPI context (KB 811889).
DMV: Dynamic Management Views.
Sample:
select *
from sysobjects
where name like 'dm_%'
order by name
select *
from sys.dm_os_sys_info
When a LOG file is missing, state_desc in the below query will show ‘RECOVERY_PENDING’
select *
from sys.databases
If you have a database backup and all the log files since that instant in time, you can restore your data.
Last resort: You can try open a database in EMERGENCY mode now (but your DB might be inconsistent because some of the transactions might be open):
ALTER DATABASE Northwind SET EMERGENCY
DBCC CHECKDB('Northwind') -- now (wrongly!) indicates no errors
Trick to loose your SQL SERVER log file. The two most important commands there:
SET DATABASE DemoSuspect SET EMERGENCY
GO
SET DATABASE DemoSuspect SET SINGLE_USER
GO
DBCC CHECKDB ('DemoSuspect', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
SET DATABASE DemoSuspect SET MULTI_USER
GO
Note that because of the repair, you lost ‘transactional consistency’!
Memory usage:
Page life expectancy should not fall below 300 seconds for a longer period of time.
When in doubt: Reboot!
Manually geneate a BSOD in order to be able to debug a hung system.
It can now also be done with USB keyboards in Windows Server 2003 SP2 and up, and Windows 7.
See also thishttp://support.microsoft.com/kb/972110.
Some more interesting links:
– Encryption Hierarchy / Encryption Hierarchy
– Book: SQL Server 2005 Practical Troubleshooting (by the late Ken Henderson)
– Security in SQL Server 2005
– SQL Server 2005 Data Encryption and data length limitations
–jeroen
The Wiert Corner - irregular stream of stuff 