Hocke_sweden
asked on
Decrease database file SQL server 2008
My database is very big, is it possible shrink it if I for example delete alot of rows in it?
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
30gb is the datafile and the log file is 1,5gb, I delete the log file every night!
>I delete the log file every night!
you should NOT do that, because if it is 1.5GB, it is that big for a reason.
so:
* is the db in full recovery mode, or in simple recovery mode?
* if it's in full recovery mode: do you have a regular (hourly or even more) transaction log backup in place and working?
you should NOT do that, because if it is 1.5GB, it is that big for a reason.
so:
* is the db in full recovery mode, or in simple recovery mode?
* if it's in full recovery mode: do you have a regular (hourly or even more) transaction log backup in place and working?
note: instead of having 1 single big data file, you should consider adding filegroups and data files, and move (nonclustered) indexes to dedicated filegroups, as well as "hot" table(s) to dedicated filegroup(s)
this shall enhance your overall I/O and performance ...
this shall enhance your overall I/O and performance ...
ASKER
Its in full recovery mode
>do you have a regular (hourly or even more) transaction log backup in place and working?
No
Why should I have that?
>do you have a regular (hourly or even more) transaction log backup in place and working?
No
Why should I have that?
30gb is not very big. If you're running short on disk space, I would seriously consider getting a bigger disk.
As for why you should back up transaction logs... If you don't and the database fails you will only be able to go back to the last valid backup. Anything after that stored in the transaction logs will be lost. If you back the db up every night, then potentially you could lose a days worth of data as the transaction logs will contain that days database transactions. Backing those up allows you to restore almost to the point of failure.
Lee
As for why you should back up transaction logs... If you don't and the database fails you will only be able to go back to the last valid backup. Anything after that stored in the transaction logs will be lost. If you back the db up every night, then potentially you could lose a days worth of data as the transaction logs will contain that days database transactions. Backing those up allows you to restore almost to the point of failure.
Lee
>Why should I have that?
if you have full recovery mode, you can restore with the full+transaction log backups to any point in time since the full backup until the transaction log backup.
if you don't need that feature: change to simple recovery mode, but note:
IMPORTANT: you cannot change from simple to full "after the ooups", and expect to backup + restore to before your "ooups"
ooups can be "drop table", delete table, udpate all rows to the same value eetc ...
if you have full recovery mode, you can restore with the full+transaction log backups to any point in time since the full backup until the transaction log backup.
if you don't need that feature: change to simple recovery mode, but note:
IMPORTANT: you cannot change from simple to full "after the ooups", and expect to backup + restore to before your "ooups"
ooups can be "drop table", delete table, udpate all rows to the same value eetc ...
ASKER
We never use the full recovery mode feature, is it better to change to simple mode to better perfermance?
I have I big disk now, but Im thinking of maybe change to a SSD disk, and they are very expensive if I want I big one. Then I consider of shrink the databases (I have more 1 one, but the 30gb is the biggest, the other are 10gb, 6gb and 1gb!
The performance have getting worse the the latest time and I want to increase it!
I have I big disk now, but Im thinking of maybe change to a SSD disk, and they are very expensive if I want I big one. Then I consider of shrink the databases (I have more 1 one, but the 30gb is the biggest, the other are 10gb, 6gb and 1gb!
The performance have getting worse the the latest time and I want to increase it!
If performance is a problem, then you may consider looking at reindexing the database.
ASKER
Yes its a performance problem
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>Why should I have that?<<
Because you care about your data and/or your job and you wish to preserve one or both. In other words by doing this "I delete the log file every night!" you risk corrupting your database. Would you consider deleting the data file? I did not think so. In case you are not aware the Transaction Log file is an integral part of your database.
Because you care about your data and/or your job and you wish to preserve one or both. In other words by doing this "I delete the log file every night!" you risk corrupting your database. Would you consider deleting the data file? I did not think so. In case you are not aware the Transaction Log file is an integral part of your database.
ASKER
Thanks for the help!
Lee