Adding a Linked server can be done by either using the GUI interface or the sp_addlinkedserver command. Adding a linked Server using the GUI There are two ways to add another SQL Server as a linked server. Using the first method, you need to specify the actual server name as the “linked server name”. What [...]
Archive of posts filed under the DBA category.
How to Move TempDB
In order to move TempDB, use the alter database command with “modify file” and specify a new path. use master go Alter database tempdb modify file (name = tempdev, filename = ‘e:\tempdb\tempdb.mdf’) go Alter database tempdb modify file (name = templog, filename = ‘e:\tempdb\templog.ldf’) go Make sure the folder path already exist otherwise you will [...]
Configuring Parallelism
Parallelism is a powerful feature in SQL Server designed to allow greater bandwidth for high impact queries. Most of the time however, I’ve seen too many queries qualifying for parallelism and essentially bringing the SQL Server box to it’s knees. To understand parallelism, we need to understand SPIDs (Server Process ID’s). These SPID’s act essentially [...]
Using SQLDiag and SQL Nexus to Troubleshoot Slowness
Luckily, two of the internal Microsoft tools used in order to diagnose slowdowns is also available for the publics use. These tools are SQLDiag (previously PSSDiag), and SQL Nexus. SQLDiag (short for SQL Diagnostics) monitors all aspects of the system that SQL Server is running on and generates all output needed in order to determine [...]
Using ALTER INDEX
Alter index is a powerful new command that replaces both the DBCC INDEXDEFRAG and the DBCC DBREINDEX, and at the same time enhances them both. The following example encapsulates the most common settings used when rebuilding an index. This example is the same as using the DBCC DBREINDEX on an entire table. The settings below [...]
Alter Procedure VS Drop Create
I had to go back through this article and break it out in terms of Pro’s and Con’s. Pro’s of using Alter Procedure Permissions stay intact ObjectId’s of procedures stay the same Altering a procedure is transactional, dropping is not The create date of the procedure remains as original, and modified date gets updated Pro’s [...]
Using DBCC DBReindex
Increasingly, you can count on most DBCC commands as being deprecated in future versions of SQL. This is one of the ones that will be removed later. It still servers a purpose. However it can be replaced using the ALTER INDEX command. The great thing about the DBCC DBReindex command is that it will completely [...]
Using DBCC FREEPROCCACHE
The DBCC FREEPROCCACHE command clears the procedure cache for all the procedures on the server. This command should be used with caution, however in most circumstances it will not bring a server to its knees. In order to execute in SQL 2005+, you need to have the server-level permissions of ALTER SERVER STATE. To use [...]
SQL Server SPID – What is it?
A SPID in SQL Server is a Server Process ID. These process ID’s are essentially sessions in SQL Server. Everytime an application connects to SQL Server, a new connection (or SPID) is created. This connection has a defined scope and memory space and cannot interact with other SPIDs. The term SPID is synonymous with Connection, [...]
List All Databases
To list all databases in a SQL Server instance, run the following: SELECT * FROM sys.sysdatabases This also outputs metadata about each database including createdate, compatibility level, and the MDF filename. Popular search terms:sql Server 2008 list all databasesList all databases ownerstsql list of all databases in a sql instancesql server 2008 list databases with [...]
