Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot christmas Logo

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

SQL Server Admin Hacks

From Wiki

Jump to: navigation, search

Below is a collection of tips, hacks and tips related to SQL Server administration

Setup

Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2000
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
Fixing Cannot add, update, or delete a job that originated from an MSX Server Error after renaming a server
Using the Command Line to manage SQL Server services
How to enable database mail on SQL Server 2005 and up
How to create a local SQL Server linked server

Security and Permissioning

Grant Execute/SELECT Permissions For All User Defined Functions To A User
Grant Execute Permissions For All Stored Procedures To A User
Find Out Server Roles For a SQL Server Login
Test SQL Server Login Permissions With SETUSER
How to enable xp_cmdshell on SQL Server 2005 and higher
How to enable OPENROWSET/Ad Hoc Distributed Queries on SQL Server 2005 and higher
How to enable the CLR on SQL Server 2005 and higher
List all tables and columns that you have update and select permissions for on SQL Server 2005 and higher

Meta Data and Object Properties

Find Primary Keys and Columns Used in SQL Server
Get The Domain Name Of Your SQL Server Machine With T-SQL
Check If Auto Update Statistics Is Enabled By Using DATABASEPROPERTY
Three Way To List All Databases On Your Server
Generate A List Of Object Types By Using OBJECTPROPERTY
How to find all the tables and views in a database
Which Service Pack Is Installed On My SQL Server
Find All Tables Without Triggers In SQL Server
Find All Tables With Triggers In SQL Server
How To Get The Database Name For The Current User Process
How To Find Out Which Columns Have Defaults And What Those Default Values Are
How To Find Out If READ_COMMITTED_SNAPSHOT Has Been Enabled For Your Database?
Return all the columns in the database which allow NULLS
List all the statistics with their last updated date in a database
Looking at a stored SSIS package in T-SQL
List All Tables Without An Index In Your SQL Server Database
List All Tables That Have An Index In Your SQL Server Database
Find Out If An Index Is Clustered Or Non Clustered By Using T-SQL
List The Count Of All Objects In Your SQL Server Database Grouped By Type
Find All The Columns In Your SQL Server Database That Are Varchar(max) Or Nvarchar(max)
List The Source Code For All DML Triggers In Your SQL Server Database
List The Source Code For All DDL Triggers In Your SQL Server Database
List The Source Code For All Functions In Your SQL Server Database
List all empty tables in your SQL Server database
List all SQL Agent jobs and the owner of those jobs
List all SSIS packages and the owner of those SSIS packages
List the value of @@DBTS in all your databases
List all the databases and what state those databases are in

Maintenance and Automation

Compare Tables With Tablediff
Create Stored Procedures That Run At SQL Server Startup
Cycle The SQL Server Error Log
Find Recently Modified Stored Procedures
Finding Fragmentation Of An Index And Fixing It
Clear out the procedure cache
Finding Forwarded Records SQL Server 2008
Restore DB and Replication will not start
Deleting files with VBS
Starting and Stopping SQL Agent Jobs with T-SQL
Enabling and Disabling SQL Agent Jobs with T-SQL
Write to file in SQL Server 2005+
Store More SQL Agent Job History Than The Default

Importing and Exporting Data

Using bcp to export data into a file in SQL Server

Manipulating Tables, Indexes, Views, Functions and Procedures

SQL Compare Without The Price Tag
Use OBJECT_DEFINITION To Track SQL Server Stored Procedure Changes
Move A Table To A Different Schema
How to prevent a trigger from firing when doing an insert into a table in SQL Server
Freeing up space from a table after you drop columns by using DBCC Cleantable

Backup and Recovery

Find Out The Recovery Model For Your Database
Fix Orphaned Database Users
Bring database out of Standby / Read-Only
Get restore dates for all databases
Get last backup dates for all databases
How to make backup compression the default on SQL Server 2008
What Percent Of The Log Is Being Used For Each Database In SQL Server 2005 and up
What Is The Size Of The Log In Kilobytes For Each Database In SQL Server 2005 and up
Backing up databases from the command line with SQLCMD

Profiling and Monitoring

Kill All Active Connections To A Database
SQL Server 2008: When Was The Server Last Started?
How to read sql server error messages
Find Out Who Is Connected To The Database
Getting The Database State For All The Databases In SQL Server
Find Out What The Active Node Of A SQL Active-Passive Failover Cluster Is
Display the 50 most used stored procedures in SQL Server
Display stored procedures with the highest average CPU time in SQL Server
Getting your Buffer cache hit ratio in SQL Server 2005 and up
Getting your Page Life Expectancy in SQL Server 2005 and up
Getting the amount of free space for each drive with T-SQL
How Many CPUs Is SQL Server Able To Use
Read the error log with T-SQL
Read the SQL Agent log with T-SQL
Get a rough estimate of how many lines of code you have in your SQL Server database
When did DBCC CHECKDB last run to completion?
How long will the database restore take?
How long will the database backup take?
What does a DBA need to check for daily, weekly, monthly, quarterly and yearly

328 Rating: 3.3/5 (78 votes cast)