NetDevHelp

Shrink SQL Server Database

2010-11-20T20:29:00.000+01:00

Background

Database tend to grow in size the more you use them even if the amount of data stored stays the same. This is mainly due to transaction log size constantly increasing. Also database engine tends to reserve more space for a database than it is in the given moment necessary. All this extra storage space can be retrieved which is especially useful in the event of sending the database files to some external device for backup purposes or such.

Solution

The commands to shrink the database that I've found most useful are:

BACKUP LOG database_name WITH TRUNCATE_ONLY

DBCC SHRINKDATABASE (database_name)

The database_name in the above commands needs to be replaced with the actual database name.

You can also try the shrink database commands available in the SQL Server Management studio UI. However they not always succeed to free as much space as it is technically possible.

Insert values into identity column in SQL Server

2010-11-20T19:21:00.001+01:00

Background

Identity column with auto increment in SQL Server data table does not allow for overriding its value. While in most cases it is the desired behavior sometimes there is a necessity to do things differently. For example while copying data from your development database to the production you may want to make sure all the IDs are exactly the same in both sets.

Solution

To enable overriding identity column it is required to run following command before executing the overriding script:

SET IDENTITY_INSERT table_name ON

You will need to replace table_name with the actual table name you want the identity insert enabled on.

After executing your command you may want to switch back to original settings, which you can achieve running:

SET IDENTITY_INSERT table_name OFF

To summarize, the whole SQL query overriding identity column should have following structure:

SET IDENTITY_INSERT table_name ON

UPDATE table_name SET ... --here goes your query

SET IDENTITY_INSERT table_name OFF

Reset identity column index in SQL Server

2010-11-20T18:57:00.000+01:00

Background
When you set a column in your SQL table to auto increment every inserted record will have a value of this column one number higher than the previous one - which is what we want in general. However even after you delete some or all of the records in the table the next inserted record will not have the next-higher-free-ID. It will be one higher than the last inserted one. Reseting this index is fortunately very easy and requires firing single command of a format:

Solution

DBCC CHECKIDENT ('table_name', RESEED, 0)

Where table_name is the name of the table you want to reset the auto increment ID on. The above command will reset the seed to the original value '0'. So the next inserted record will have the ID equal 1. Though you can replace the last parameter with a different number if you wish the numbering to start from a different position.