Please help ~~~

Please help , my company's SQL server database grow so quick , a few day grow 20G, does any tools or server can compress the database ?

Please help me ~~~~

silver
e-mail : silvershi@.gmail.com

Hi Silver,

did you mean the *.mdf File (Datafile) or the *.ldf (Transaction-Logfile)?

|||

try shrink that db

DBCC SHRINKDATABASE ( 'database_name' | database_id | 0 [ ,target_percent ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ) [ WITH NO_INFOMSGS ] if the log growed unexpectedly the command can be BACKUP LOG { database_name | @.database_name_var } WITH { NO_LOG | TRUNCATE_ONLY } [;] this truncate the logAnyway, all depends by many factors as recovery model, the activity on your server, if there is bulk insert of data etc.|||

If I were to guess, you are using full recover model and what is growing is probably your transaction log. The way to deal with that is to backup your transaction log. If you aren't in production, don't need point in time recovery and can afford to lose data, you may want to explore simple recovery model.

That's the simple answer if that is your configuration. A lot of other issues come into play in deciding your recovery model, frequency of backups, etc.

-Sue

|||I means the *.ldf( Transaction-LogFile)

what's can I do ? nowaday the transaction-LogFile is 120G , it so large my sql server just have 60G free now ....
|||thanks for all , I have find a doc. to do this .....

http://support.microsoft.com/?scid=kb%3Ben-us%3B272318&x=10&y=8

thank for your help ~~

|||

This is a very common prob in SQL Server. The growth of Transaction Log(TL) is depends on couple of things... (a) the Recovery Model (b) TL Backup freqency.

(a) first check whether you need Full recovery model , if not change it to Simple

(b) WHat is the Backup policy. if you are taking the TL backup then increase the frequency of the TL backup

(c) If you want to shrink the file then you will have to truncate and shrink it. After truncating and shrinking the first step should be a full backup. otherwise the backup chain will break and u will not be able to restore from TL bakcup.

Refer :

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

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1542414&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1630021&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1623857&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1447193&SiteID=1

Madhu

|||thanks for you help , but I have a question ...
if I shrink the log file , is it have any effect about my data in the database ?

thanks

|||

Hi Sivershi,

normaly no!

But you have to think about Madhu K Nair questions - backup policy, recovery model!

|||thanks a lot ....
if no more affect , I can shrink the log file ...
because I have write another log file program to logging the entry in my program

|||

But if you find you have to shrink the log file again, you have likely missed the point. If the log file is huge, the last thought should be just going and shrinking the log file and be done with the issue. You first need to address your recovery model and backup plan. When you shrink files, you use a lot of expensive disk resource. It also introduces fragmentation at the file, OS level. Shrinking databases or database files should not be something that needs to be done regularly. If you shrink files, and then the grow again, and then you shrink them, you are just going in circles and wasting resources.

-Sue