CODEWITHSUNDEEP
sqlsql-serversql-server-2005
monocular
monocular

Reputation: 323

Microsoft SQL Server backup file very heavy

I have 2.159.000 records now in my table. The table contain:

ID -- int
Username -- nvarchar(50)
Password -- nvarchar(50)
Address -- nvarchar(100)
Email -- nvarchar(250)
Note -- nvarchar(250)
Linking - nvarchar(max)
IsBusiness bit
IsActive bit
Delivered - bit
Something1 bit
Something2 bit
Something3 int
Songthing4 int

And it's the only one table in my database. But when i run backup using Microsoft SQL server management studio. The output file is up to 6.6 GB. My friend have a database of 9.000.000 records but when he run backup it's only 2.2 GB in same SQL Server 2005. I don't know what could make my database becoming so heavy and i really what to decrease it as soon as possible for save my disk.

Upvotes: 2

Views: 88

Answers (2)

Laurence Frost
Laurence Frost

Reputation: 2993

Here's an idea for you. Duplicate the database so that you have a 2nd copy, drop the linking column out of the table, and then try a backup the new DB. Everything else is fixed-length, so you'll be able to work out if the MAX column contains lots of data.

If it's still huge, then you can look at shrinking the database in case there is some stuck data or space in there. It's generally not required, but can be useful for situations like this.

DBCC SHRINKDATABASE MyDatabaseName;

Here is more info on how to shrink the DB: http://technet.microsoft.com/en-us/library/ms190488(v=sql.105).aspx

Upvotes: 1

Gareth
Gareth

Reputation: 2791

The number of rows is almost irrelevant - it's the size of the data within those rows that counts. Your friend might have every row containing half the amount of data in the Linking column, for example.

Upvotes: 1

Related Questions