SQL Server - reduce the size of sql server with out affecting the data

Asked By muthuraman alexander on 09-Jan-12 03:43 AM

hi all
can any body advise me that how to reduce the size of the sql server database

i had sql SB for my site it was increasing its size day by day
i dont know how to reduce the size of it....
can any body suggest me that how to reduce the size of sql sever

thanks in advance
MUTHU

dipa ahuja replied to muthuraman alexander on 09-Jan-12 04:14 AM

Size will increase as the number of records are inserted into your database, you cannot decrease the size when records are being inserted.

But you can delete tables which are not useful from your database

Suchit shah replied to muthuraman alexander on 09-Jan-12 06:06 AM

To reduce the size of your database:
1.Check the number of users registered for the database, and delete users if needed. The acceptable number of users varies, depending on the performance of the server and the network.
2.Remove any temporary files
3.If your projects have outdated history information, remove it.
4.Delete any unused files and projects in the database.
5.If there are projects that are not interrelated, move them as needed to separate databases.

To shrink a file after it is allocated, you need to use the SHRINKFILE command:

USE [DatabaseName]
GO
DBCC SHRINKFILE (N'FileName' , size in KB)
GO

http://msdn.microsoft.com/en-us/library/ms189493.aspx

More info check : http://msdn.microsoft.com/en-us/library/bb508963(v=SQL.90).aspx