How to Shrink a SQLExpress 2008 Database

A SQLExpress 2008 database usually comprises of a database and a log file with the extension *.MDF and *.LDF respectively. As time goes by, the LDF can grow dramatically and I have seen the LDF file being more than triple the size of the MDF !

So, how does one shrink the size of MDF and LDF ? It is really simple to do so using the Management Studio for SQL Express 2008

1. Launch Management Studio
2. Select the target database
3. Right-click and select All Tasks

4. Select Shrink Database

5. Check Reorganise files... as below :-

and click OK, That's it !

Installing SQL Express 2008 on a Windows 2003R2 Server

Recently, I had the opportunity to instal MS SQL Express 2008 on a Windows 2003R2 Server for a client for wanted to check it ou befire upgrading to SQL Server 2008.

Here are the steps that I took :-

1) Download SQL Express 2008 with Advanced Services

2)

Ran it and discovered that I had to download and install the following components

ASP.Net 3.5SP1
Windows Power Shell 1.0
Windows Installer 4.5 and above

3) Took a while to download 50 Plus MB of ASP.Net 3.5SP1 and installed it without issue

4) Installed Windows Power Shell with no issue

5) Installed Windows Installer 4.5 again with no issue

6) Ran SQL Express 2008 with Advanced Services again and passed all requirements

Tips


a) Use NT Authority/SYSTEM
b) Use Mixed Mode for authentication



More to come...

What are the limitations of the FREE SQL Express 2008 ? [Updated 26th October 2009]

SQL Server Express is a free, easy to use, redistributable version of SQL Server 2005 designed for building simple data-driven applications. SQL Server Express has most of the features offered by SQL Server full version, but with certain limitations. This makes SQL Server Express a good choice for small and medium scale applications.

Most of the limitations of SQL Server Express edition will not affect the small/medium scale applications.

Limitations of SQL Server Express Edition

1. Number of CPUs supported

SQL Server Express uses only one CPU at a time. It can be installed on a server with multiple CPUs, but it will use only one CPU at a time.

2. Maximum memory used

SQL Server Express uses a maximum of 1 GB memory for it's data buffer. So, if your server has severaql GB memeory, SQL Server Express cannot take advantage of it.

4. Database size limit

Maximum database size is limited to 4 GB (log file size is not counted)

5. Profiler in SQL Server Express

Profiler tool is not included with SQL Server Express editions.

6. Job Scheduler

Job Scheduling service is not available with SQL Server Express.

7. Import/Export

Data import and export feature is not available with SQL Server Express.

A complete feature comparison of various editions of SQL Server can be found at the Microsoft web site - http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx


SQL Express 2008

Physical Limitations

SQL Express 2008 has the same physical limitations that existed in SQL Express 2005:

    * 1 CPU.
    * 1 GB Memory
    * 4 GB of user data per database

Since people always ask - you can run SQL Express on machines that exceed these limits, SQL Express just ignores any addition CPUs or memory. SQL Express will take advantage of multiple cores on the single CPU by starting a scheduler for each core.

Feature Support

The same features available in SQL Express 2005 are still available in SQL Express 2008, if you need a refresher on those you can find them in the Feature Comparison chart in Books Online. This post only calls out the features in SQL Express that are new in 2008.

More at http://blogs.msdn.com/sqlexpress/archive/2008/06/10/sql-server-2008-express-rc0.aspx

Update on 26Th October 2009

CCH: Based on the latest download of SQL Express 2008 with Advanced Services, Import & Export of Data is now available. In addition, you must use Mixed Mode ratther than Windows authentication for smooth connectivity from your Client software.

What is Leto db Server ?

CCH : Information about Leto Db Server is pretty sketchy but it has been discussed extensively in the Harbour and HMG Extended Forums.

From http://sourceforge.net/projects/letodb/

Leto is a multi-platform database server or a database management system, chiefly intended for client programs, written on Harbour, be able to work with dbf/cdx files, located on a remote server.

Select Statement : Common Usage

Opening all columns from a Table sorted by specified Column

SELECT * FROM TargetTable ORDER BY ColumnName

How to copy a table ?

Make a copy of an existing table

SELECT * INTO TargetTable FROM SourceTable


Make a copy of the structure of an existing table

SELECT * INTO TargetTable FROM SourceTable WHERE 1=0