We don't use the default port for SQL Server for security reasons, so we usually have to explicitly tell the client how to connect to SQL Server by creating an alias on the client machine.
Read more →
Windows Server 2003 Service Pack 2 has been released. Check this out for details.
Legacy Comments
vinayajk
2007-03-25
re: Windows Server 2003 Service Pack 2 has been released khsdhsadhksahdkhas
Read more →
UPDATE: This stored procedure has been updated.
This stored procedure defragments indexes in SQL Server 2005. It utilizes sys.dm_db_index_physical_stats, a dynamic management function, to retrieve the fragmentation levels. If you plan to use DETAILED for @statsMode, you may want to consider running the query that builds the #FragIndex table on a database snapshot.
Read more →
SQL Server 2005 service pack 2 was recently released and already Microsoft has a critical update for it. *Sigh*
Service pack 2 was updated to include this, so you only need to install the critical update if you downloaded service pack 2 prior to March 5, 2007.
Read more →
To defragment your indexes, we now use ALTER INDEX in SQL Server 2005 rather than DBCC DBREINDEX and DBCC INDEXDEFRAG, which have both been deprecated in 2005.
The REBUILD option of ALTER INDEX is equivalent to the old DBCC DBREINDEX; the REORGANIZE option of ALTER INDEX is equivalent to the old DBCC INDEXDEFRAG.
Read more →
EDIT: A new version of this stored procedure is available here.
I've completely modified my isp_Backup stored procedure. The new version supports both SQL Server 2000 and 2005. It also gets rid of the need to use my other backup stored procedures, such as isp_Backup_TLog and isp_Backup_LS.
Read more →
In our production environment, we have two 4-node clusters. One cluster runs at the primary site; the other cluster runs at our disaster recovery site. Each cluster is running 11 SQL Server 2005 instances.
Read more →
Our disaster recovery and database availability solution involves clustering and Database Mirroring in SQL Server 2005. At our primary site and our disaster recovery site, we have a 4 node cluster with several SQL Server 2005 instances on it.
Read more →
To quickly disconnect all non admins from a database, I use this:
ALTER DATABASE Database1 SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE Database2 SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
One of the development teams recently requested that their production databases be copied to a development server on a weekly basis.
Read more →
I've updated my isp_DBCC_CHECKDB stored procedure as I was getting errors on a SQL Server 2005 server when one of the databases was the mirror in a database mirroring scenario or when one of the databases was in a loading state (NORECOVERY option of RESTORE command).
Read more →
I've been trying to determine what new cell phone I want to get since my current personal one is pretty old. One of the most important features that I am considering is its size.
Read more →
We use MOM 2005 to monitor our production environment. We hadn't been receiving any alerts from MOM when a SQL Server 2005 job failed, so I was tasked with tracking the problem down.
Read more →
Check out this article for white papers, scripts, and tools on SQL Server 2005 best practices.
Legacy Comments
Jesse
2006-12-13
re: SQL Server 2005 - Best Practices good resource.
Read more →
I deleted a few hundred spam comments today from my posts. I hadn't kept up with deleting them, so I had quite a bit of work to do today. Due to the amount of spam my posts have received, I've decided to close comments after 90 days so that the amount of spam is reduced.
Read more →
Do you ever need to connect to the console of a server? Are you too lazy to go to the server to do it? If so, then check this out from a cmd window:
Read more →
We've been having performance problems on a new 64-bit SQL Server 2005 cluster in our test environment. We opened a ticket with Microsoft to assist us with this. After modifying several things, the only thing that was slow was Management Studio.
Read more →
For those of you who have been using SQL Profiler 2005 for a while, you probably are already aware of this. We've been using SQL Server 2005 for a few months now, but I hadn't touched SQL Profiler 2005 very much yet.
Read more →
In SQL Server 2000, you could generate a SQL script easily using the Generate SQL Script wizard. You could get to it from pretty much anywhere from within Enterprise Manager. For instance, you can get to it by right clicking on any object in the database, then selecting All Tasks, and then selecting Generate SQL Script.
Read more →
By default, xp_cmdshell is disabled in SQL Server 2005 for security reasons. I am currently using xp_cmdshell in my backup stored procedure to delete files, so I must enable xp_cmdshell on my SQL Server 2005 instances.
Read more →
In Query Analyzer, we could easily comment blocks of code using Ctrl+Shift+C and uncomment blocks of code using Ctrl+Shift+R.
These keyboard shortcuts do not work in Management Studio unless you switch the keyboard scheme to SQL Server 2000.
Read more →