SQL Server notes
Posted by Jeroen Pluimers on 2009/09/25
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
DMV: Dynamic Management Views.
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’!
Page life expectancy should not fall below 300 seconds for a longer period of time.
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: