A Tech Log

April 6, 2009

Turn Auto Shrink off on all SQL Databases

Filed under: Development — adallow @ 11:21 am
Tags:

DECLARE @name varchar(500)
DECLARE @sql varchar(8000)
SET @sql = ”
DECLARE Database_Cursor CURSOR READ_ONLY FOR
SELECT Name
FROM sysdatabases
WHERE DBID > 4
OPEN Database_Cursor
FETCH NEXT FROM Database_Cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + ‘ALTER DATABASE [‘ + @name + ‘] SET AUTO_SHRINK OFF’ + CHAR(10)

FETCH NEXT FROM Database_Cursor INTO @name
END
CLOSE Database_Cursor
DEALLOCATE Database_Cursor

print @sql

EXEC(@sql)

April 1, 2009

TSQL to Shrink All Databases on a Server

Filed under: Development — adallow @ 3:44 pm
Tags: ,
DECLARE @name varchar(500)
DECLARE @sql varchar(8000)
SET @sql = ”
DECLARE Database_Cursor CURSOR READ_ONLY FOR
SELECT Name
FROM sysdatabases
WHERE DBID > 4
OPEN Database_Cursor
FETCH NEXT FROM Database_Cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + ‘backup log [‘ + @name + ‘] with truncate_only’ + CHAR(10)
SET @sql = @sql +  ‘dbcc SHRINKDATABASE ( [‘ + @name + ‘] )’ + CHAR(10)

FETCH NEXT FROM Database_Cursor INTO @name

END
CLOSE Database_Cursor
DEALLOCATE Database_Cursor


print @sql

EXEC(@sql)

December 5, 2008

Visual Studio 2008: Visual Studio Team System Database Edition

Filed under: Development — adallow @ 1:47 pm
Tags: ,

VS Database Edition is Part of Team edition as well. check out the features here via video:

http://www.microsoft.com/web/content.aspx?id=visual-studio-2008-database

November 13, 2008

Migrating data from SQL Server 2000 to SQL Server 2005

Filed under: Development — adallow @ 11:28 pm
Tags:

An article by Microsoft, covers some the high-level information on migrating from SQL 2000 to 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlupgrd.mspx

In particular the following section was useful for me:

The database engine upgrade is the easiest upgrade and will result in immediate return on investment in the areas of management, performance, and high availability. Again, the two main options for the database engine upgrade are side-by-side migration (in which you install the SQL Server 2005 engine as a secondary instance on the same server as your SQL Server 2000 or 7.0 server or on a completely separate server) and an in-place upgrade (in which you upgrade an instance of SQL Server 2000 or 7.0 through the install process and databases and other objects are upgraded “in place”).

With a side-by-side migration, the most common upgrade path is a simple database detach and re-attach on the SQL Server 2005 instance or a database backup and restore from the older version to the new version. If you keep an up-to-date version of your metadata scripts, you can also create the objects on the SQL Server 2005 server and use BCP to export and import your data. The other option is an in-place upgrade, in which you upgrade and adapt the databases, settings, and extended features to the SQL Server 2005 engine during the install process. When you run the setup process on a server that has a SQL Server 2000 or 7.0 instance, you’ll see an option to upgrade the selected instance to SQL Server 2005.

Note that for the database engine upgrade, all your existing Microsoft Data Access Components (MDAC) and ADO.NET applications will continue to function as when they were running against SQL Server 2000 or 7.0. In fact, SQL Server 2005 doesn’t come with a newer release of MDAC. But new to the platform is the SQL Native Client, combining an updated SQL ODBC driver and SQL OLEDB provider with network libraries in a single DLL. The SQL Native Client lets you leverage SQL Server 2005’s new client-access features, such as Multiple Active Result Sets (MARS), the XML data type, and user-defined types (UDTs). SQL Server comes with tight integration with the .NET Framework 2.0, which includes the latest ADO.NET version.

The in-place server upgrade will be the easiest. Although this approach requires a more thorough fallback plan and testing, you reap the rewards through seamless connectivity. By performing an in-place upgrade, logins and users remain in-sync, database connections remain the same for applications, and SQL Agent jobs and other functionality is concurrently upgraded during the installation. Note that several features, such as log shipping, replication, and cluster environments, have special upgrade considerations.

As noted earlier, for the database engine, the upgrade sets the compatibility mode to 8.0. You might benefit from leaving this setting at 8.0 under certain circumstances, such as for T-SQL references that are no longer supported in SQL Server 2005. I’ve run across some query hints that require rewriting, for example, as well as some legacy ANSI join syntax that’s being phased out (the *= left outer join syntax in particular). The analysis phase of your upgrade will uncover situations where using a lower compatibility setting might apply. However, I recommend that you fix any syntax that requires a lower compatibility level than 9.0 (SQL Server 2005) during the upgrade process. By reworking the syntax, your developers will have immediate access to all the new programming enhancements and features in the release. One quick trick to isolating these type issues and other syntax that might cause upgrade trouble is to script out the objects and procedures from the earlier platform version and attempt to run the scripts within SQL Server 2005. A simple attach or restore might suppress these issues. Also remember that some SQL logic can be embedded in your application. For data validation, running DBCC checkdb on your attached or restored database will confirm the integrity of the migrated data.

One caution: You probably have developers who are adept at leveraging system objects to make their lives easier. Microsoft has always told us to use Information_Schema views to get various metadata instead of querying the system tables directly because Microsoft can’t guarantee that the underlying object structure will persist in new platforms. With the new release, Microsoft has changed SQL Server’s underlying object structure. Also note that SQL Server 2005 catalog views and Dynamic Management Views (DMVs) have restricted permissions. PUBLIC users no longer have permissions to view catalog views, and users with GUEST/PUBLIC permissions can’t select from DMVs.

SQL Server Locking : NOLOCK and ROWLOCK

Filed under: Development — adallow @ 11:14 pm
Tags:

SQL Server requires (READ) locks for reading from the database (select queries).
For Updates (WRITE) locks are used.
SQL Server starts with row-level locks, but often escalates these to page and table locks, causing deadlocks.

Using NOLOCK tells SQL Server to ignore locks and read directly from the tables. This means you don’t have to worry about locks, however you may read data that is not yet committed (i.e. in the Process of being updated, and not yet committed, or worse yet was int he process of being updated, but then the transaction doing the writing rolls back, so you read data that was never there). Obviously only use NOLOCK when you can deal with these consequences (e.g. it might not be a good idea in an Accouting system, but may be fine for a CMS style site).

Using ROWLOCK, means that the lock type won’t esclated up to page or table locks (which would have effected more records than just those being updated). Instead locks will always be at the ROW level.

Examples:
SELECT Name
FROM Person WITH (NOLOCK)
WHERE Name LIKE ‘Smith’

and

UPDATE Person WITH (ROWLOCK)
SET Name = ‘Albert’ WHERE Name= ‘Bert’

Blog at WordPress.com.