Home > Uncategorized > Shrinking a 6 GB database

Shrinking a 6 GB database

After spending the guts of a month compiling a 6 million row database, containing the name, address and phone number of every unique companiy in europe (Well, UK, Germany, Belgium, France, Italy, ang Luxemborg). – This exludes branches of the same company, which ammounts to 130 Million records – Which I didn’t store.

The resultant database was 2.3 GB MDF and 3.9GB LDF (log). stored on my local PC. In order to transfer this to my server, realistically, it needs to be below 1GB, or else it would take days to upload via 256K ADSL.

The first step is to shrink the log file. using this:

backup log dinfo with truncate_only

dbcc shrinkfile(dinfo, 100)

Unfortunately, I found that this did not work, instead, I logged into Enterpise mangager, selected the database from the list and selected Shrink Database. Selected 0% and "move pages to beginning of file". This reduced the database fragmentation, and thus shrunk the transaction log down to 500Kb, and the main MDF down to just over 2.2 Gb.

After Zipping and uploading the database, I then attempted to attach it with

EXEC sp_attach_db @dbname = ‘dinfo’,
     @filename1 = ‘C:Program FilesMicrosoft SQL ServerMSSQLDatadinfo.mdf’,
     @filename2 = ‘C:Program FilesMicrosoft SQL ServerMSSQLDatadinfo_log.ldf’

Which gave the error:

Server: Msg 1827, Level 16, State 2, Line 1
CREATE/ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 2048 MB per database.

However, I also have SQL server 2005 (Yukon) installed on my server, which doesn’t have the size limit of MSDE (yet it is still beta). So I ran the following command

C:Documents and SettingsAdministrator>sqlcmd -S.SQLEXPRESS -E
1> use generalpurpose
2> go
Changed database context to ‘generalPurpose’.
1> exec sp_attach_db @dbname=’dinfo’,@filename1 = ‘C:Program FilesMicrosoft SQ
L ServerMSSQL.1MSSQLDatadinfo.mdf’,@filename2 = ‘C:Program FilesMicrosoft
SQL ServerMSSQL.1MSSQLDatadinfo_log.ldf’
2> go
Converting database ‘dinfo’ from version 539 to the current version 587.
Database ‘dinfo’ running the upgrade step from version 539 to version 551.
Database ‘dinfo’ running the upgrade step from version 551 to version 552.
Database ‘dinfo’ running the upgrade step from version 552 to version 553.
Database ‘dinfo’ running the upgrade step from version 553 to version 554.
Database ‘dinfo’ running the upgrade step from version 554 to version 586.
Database ‘dinfo’ running the upgrade step from version 586 to version 587.

Et viola,  my 6GB database is running off SQL server 2005!

 

 

 

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: