Wednesday, March 28, 2012

how can i reduce the size of the database?

I am using SQL server 2000 and its size is increasing like a crazy.. is there any way to reduce the size by deleting any temp data or logs?

Have a look at the database files, which one are gwoing extremly, the data files or the log files ? if the logfiles are getting bigger you should consider shrking them from time to time.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

What is growing, your data files or your transaction log file? Are you running in Full Recovery mode and not doing transaction log backups?

If your data files are growing, you can try defragmenting or rebuilding your indexes. If you have a lot of index fragmentation, this can free up a lot of space. You can also try shrinking your data and log files like this:

DBCC SHRINKFILE (N'YourFileName_dat' , 0, TRUNCATEONLY)

http://glennberrysqlperformance.spaces.live.com

|||

Read the below mentioned links... You need to change your backup policy... take the transaction log backup regularly... Do not shirnk database just like that... every database requires some extra space for daily activities... Yes you can shrink Transaction log provided the first action after the shrinking should be Full Backup of your database. Which you truncate and shrink the Transaction Log the Backup chain breaks... so be aware that you should take full backup as soon as possible...

How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server 2005
http://support.microsoft.com/kb/907511

How to stop the transaction log of a SQL Server database from growing unexpectedly
http://support.microsoft.com/kb/873235

http://support.microsoft.com/kb/907511

How to stop the transaction log of a SQL Server database from growing unexpectedly
http://support.microsoft.com/kb/873235

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=19637

Madhu

|||

review ur backup policy...truncate transactional logs when u take a full backup....

i wont recommend -- regular -- shrinking of data files...its only goin to make it slower and wont be of significant help mostly...

No comments:

Post a Comment