Monthly Archives: January 2008

Finding Used Databases

Posted on January 31, 2008 by Clay Lenhart

It can be difficult to determine which databases you are administering are actually being used. Below is a query that determines the last time an index was used since the last time SQL Server started to help narrow down these … Continue reading →

Posted in SQL Server Administration | 7 Comments

SQL Server RAM Usage

Posted on January 30, 2008 by Clay Lenhart

SQL Server has difficultly managing memory for systems with a large amount of RAM (say 8+ GB), typically found in 64-bit installations. You might see log messages such as: A significant part of sql server process memory has been paged … Continue reading →

Posted in SQL Server Administration | 5 Comments

Defragging Indexes in SQL Server 2005

Posted on January 29, 2008 by Clay Lenhart

There are two ways to optimize your indexes in SQL Server 2005: ALTER INDEX … REORGANIZE ALTER INDEX … REBUILD Which one should you choose? According to BOL, it depends on how fragmented they are. The avg_fragmentation_in_percent column from the … Continue reading →

Posted in SQL Server Administration | 9 Comments

Listing checked-out files in Team System (TFS)

Posted on January 28, 2008 by Clay Lenhart

You can get a complete list of checked out files by using the tf.exe command line tool found in: C:\Program Files\Microsoft Visual Studio 8\Common7\IDE All checked out files in $/MyProject: tf.exe status $/MyProject /user:* /s:http://MyTfsServer:8080 /recursive All files checked out … Continue reading →

Posted in Source Control | 25 Comments

Backing up to a NTFS compressed folder

Posted on January 28, 2008 by Clay Lenhart

It seems like a good idea to backup SQL Server 2005 databases to a compressed folder, however if your database is larger than roughly 30 GB, you will get the following error: BackupMedium::ReportIoError: write failure on backup device ‘G:\Backups\200501220402.BAK’. Operating … Continue reading →

Posted in SQL Server Administration | 32 Comments

The sp_getapplock secret

Posted on January 28, 2008 by Clay Lenhart

sp_getapplock is not very well advertised in SQL Server 2005, however it is a good way to synchronize code in a stored procedure. Before finding out about sp_getapplock, I would SELECT from a table with an exclusive lock, like so: … Continue reading →

Posted in SQL Server Development | 22 Comments

SQL Server Security with EXECUTE AS OWNER

Posted on January 24, 2008 by Clay Lenhart

EXECUTE AS OWNER is a great way to limit the permissions of a SQL Server Login. The general idea is to create your stored procedure with the EXECUTE AS OWNER modifier. Any user who has the permissions to execute the … Continue reading →

Posted in SQL Server Development | 56 Comments

Version Control – Part 4: Distributed Version Control

Posted on January 23, 2008 by Clay Lenhart

There are many reasons to create branches in the Update/Commit model and Distributed Version Control really excels in an environment with many branches. Take the examples in the previous post: In Distributed Version Control, each of these would be “repository/working … Continue reading →

Posted in Source Control | 3 Comments

Version Control – Part 3: Branching/Merging

Posted on January 11, 2008 by Clay Lenhart

Branch and Merge Many people who implement Update/Commit typically organize the server into the Trunk and Branches folders: This allows you work on long term, scheduled features in Trunk, while making unexpected bug fixes in the latest branch: v2.0. The … Continue reading →

Posted in Source Control | 4 Comments

Version Control – Part 2: Update/Commit

Posted on January 8, 2008 by Clay Lenhart

The problem with Checkin/Checkout Generally speaking, Checkin/Checkout interrupts developers. You want to finish your task, but you can’t continue on a portion of it, which then interrupts your thought process. The more developers you have with the Checkin/Checkout model, the … Continue reading →

Posted in Source Control | 15 Comments