PHISSUG

Hi guyz, how will i reduce the size of our database? any steps and idea?

 

thanks

hisouka:

Hi guyz, how will i reduce the size of our database? any steps and idea?

thanks

what kind of reduction do you intent to do? data reduction? transaction log bloat? indexes?

both data and trans log.. thanks

hisouka:

data

more accurately, for database, there is really no straightforward asnwer to this. if your db structure is currently of top quality and highly optimized, I dont see some ways for you to be able to reduce the size of your data. however, if there is room for improvement, I can think of several ways:

• use of proper data types. for example, 
• use VAR* when necessary. Using VARCHAR(500) vs CHAR(500) will probably allow you to save on space. But of course there is minimal trade off against fragmentation and probably performance.
• use TINYINT instead of INT if you are sure that your data will be in the range of TINYINT
• NON-UNICODE vs UNICODE. Do take note that each unicode character takes up 2 bytes.
• use of bitmapping
• normalize when necessary to avoid several instances of the same information in your database

hisouka:

trans log

I typically set a maintenance routine for my databases to have its transaction log backup every 30 minutes and have the database AUTO SHRINK property set to ON. This will continously shred off xlog and it wont grow that much. Use this settings with precaution as they might impact your DB's performance.

If you dont benefit from xlogs, you can turn its Recovery Model to Simple.

Or you can simple use the SHRINK command.

Hope this helps!

the fastest and most efficient way of shrinking the database is .....

DROP DATABASE 'DatabaseName'. 

hehehe, joke lang.

You can also consider archiving your old data. All the data that you dont need anymore, move them to CD/Archive media and delete them.

Arthur Jr:

the fastest and most efficient way of shrinking the database is .....

DROP DATABASE 'DatabaseName'. 

hehehe, joke lang.

heheh patay tayo dyan. baka may maniwala lagot ka hehe.

There is hope with SQL Server 2008. There is that new data and backup compression feature. I also have read that filtered indexes. All these meant that we can save on storage space.

Release would be very near I suppose :)

Bossing,

An autoshrink is a killer in SQL Server. This causes serious index fragmentation problems as well as file-level fragmentation on your disk.  Check out this issue of TechNet Magazine for August 2008 with one article written by Paul Randall.  Also, shrinking the transaction log after a log backup that truncates the log is also a killer. This is because of the existence of VLFs (virtual log files) inside the transaction log.  You can use the DBCC LOGINFO command to verify the existence of inactive VLFs. The proper approach to this is to shrink the log to the smallest size possible after a log backup to truncate the log.  You need to do this during your maintenance window.  Then, resize the transaction log to an appropriate size to minimize autogrow.  Check this article from Kimberly Tripp's blog 

---

Check out SQL Server tips at http://www.mssqltips.com
Check out SQL Server 2008 videos at
http://blogcastrepository.com

bass_player:

Bossing,

An autoshrink is a killer in SQL Server. This causes serious index fragmentation problems as well as file-level fragmentation on your disk.  Check out this issue of TechNet Magazine for August 2008 with one article written by Paul Randall.  Also, shrinking the transaction log after a log backup that truncates the log is also a killer. This is because of the existence of VLFs (virtual log files) inside the transaction log.  You can use the DBCC LOGINFO command to verify the existence of inactive VLFs. The proper approach to this is to shrink the log to the smallest size possible after a log backup to truncate the log.  You need to do this during your maintenance window.  Then, resize the transaction log to an appropriate size to minimize autogrow.  Check this article from Kimberly Tripp's blog 

true. :D that is why I have warned to use this with precaution as it would affect DB performance.

pero teka .. July 2008 pa lang a :P

galing. me August 2008 magazine n kaagad.

you can also remove unwanted indexes, this unwanted indexes will greatly reduce the size of database, just make sure, that index is not in use or not very often in use :) 

have you tried DBCC SHRINKFILE.... is it advisable?

---

MCP.MCAD.MCDBA
MCTS:SQL Server 2005
MCTS: Business Intelligence
MCITP:SQL Server 2005 Database Administrator
MCITP: Business Intelligence Applications Developer
Microsoft Certified Trainer
Database Administrator: Trend Micro

ggsubscribe:

There is hope with SQL Server 2008. There is that new data and backup compression feature. I also have read that filtered indexes. All these meant that we can save on storage space.

Release would be very near I suppose :)

yah SQL 2008 really rocks in terms of data compression and uncompression its around 200% more faster than any compression utilities plus they have AUDIT features and password security for backup mdf and ldf files...so meanwhile lets just utilize what is available compression tools we have like .rar or .zip thing that is if we don't have yet SQL 2008