About Trusted Foreign Key Contraints
I will post some of my findings about trusted foreign keys and constants here since this has become a minor issue at work.…
Read MoreCategory: Administration
SQL Server Audit: Resources
I am doing some research on how to implement auditing of schema and security changes and will use this post to track notes…
Read More
Log won’t shrink in SIMPLE Recovery mode
I recently had a situation where a database that was set to SIMPLE recovery mode had an ever-growing log file. I had tried…
Read More
Finding Suspect Pages
Here is where SQL Server stores any pages it has identified as suspect:
|
1 2 |
SELECT * FROM msdb.dbo.suspect_pages sp |
Views – 2217
Read More
Database Availability State Statuses
The possible values in the State and State_Desc columns of sys.databases are: 0 = ONLINE 1 = RESTORING 2 = RECOVERING 3 =…
Read More
How to identify IDENTITY columns tables in a database
Here is a nifty query that will display all the tables and columns set as IDENTITY in a given database:
|
1 2 3 4 5 6 7 8 9 10 11 |
USE AdventureWorks2012 GO SELECT OBJECT_NAME(object_id) AS 'table_name', name AS 'column_name', seed_value, increment_value, last_value, is_not_for_replication FROM sys.identity_columns ORDER BY 1 |
Views –…
Read More
Reseeding and Updating the INDENT_CURRENT Value in SQL Server
This code will do the following: 1. Display the current INDENT_CURRENT value for the identity column for the specified table. This value should…
Read More
Syncing SQL logins and database user accounts across servers with SIDs
Here is a good article on syncing SQL logins across servers with the same SIDS to log-shipped databases will propagate the correct access…
Read More
How to take a database offline
Here is one way to take a database offline. This will prevent any reads, writes or even a connection to it (including any…
Read More
DBCC command list
DBCC activecursors [(spid)] DBCC addextendedproc (function_name, dll_name) DBCC addinstance (objectname, instancename) DBCC adduserobject (name) DBCC auditevent (eventclass, eventsubclass, success, loginname, rolename, dbusername, loginid)…
Read More