The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My work

  • My badges

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming

    20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now

    20140424-Windows-7-free-disk-space

    More Photos
  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,349 other followers

SQL Server notes

Posted by jpluimers 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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 1,349 other followers

%d bloggers like this: