Calculation of 10 G database

Question

Post reply

Calculation of 10 G database

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

NJ-DBA SSChampion Points: 13834 More actions · Answer 1

Looks like 1 table using most of the space:

dbo.EventLog U 561681 5726.102 - 994.203 == 4731.898 = 56.813 + 4675.086

almost 6GB there

Performance issues... no, this is just a select. Once it's done, it's done.

Krasavita SSC-Insane Points: 23138 More actions · Answer 2

I can go to the prpertes now, that was something else

Krasavita SSC-Insane Points: 23138 More actions · Answer 3

Why his table is taking so much space?

I am deleting records from this table and should have about 200000 rows left, should db get smaller?

NJ-DBA SSChampion Points: 13834 More actions · Answer 4

I can't tell you why the table take so much space- what is in it? How is the table defined (what datatypes are the columns)?

The datafile will not automatically get smaller just by deleting the records, but there will be more available space in the datafile. After such a large delete, you may want to defrag or rebuilt the clustered index on the datafile. That will leave free space at the end of the file which you could reclaim with a shrink file if you wanted to...

but if you are going to continue to add data, why do that at all? Just leave the free space there as is and use it up as you add more data.

10GB is not a large database by most standards.

Krasavita SSC-Insane Points: 23138 More actions · Answer 5

This table captures eror logs, which we don't need to keep, I keep it for 1 month.The problem is we make a back up of this db 2 times a day because developers have releises an it takes too long. the same db in staging is 28M, much much smaller and I wanted to know why, everything is the same accept this table, that why I need to make this db smaller if it possible.Thank you

NJ-DBA SSChampion Points: 13834 More actions · Answer 6

If your concern is recoverability, full backups 2Xs daily might not be the best solution.

Why not operate in full recovery mode- you could do a full backup daily and then periodic transaction log backups. That would ensure point in time recoverability.

or example, you take a Full backup at midnight, then TLOG backups every 2 hours. A Developer applies some release at 9am and you decide you need to roll it back. Best option would be to require the developer to have some "rollback" or "undo" script prior to implementation- that way database restore is just your "all else fails" option.

Assuming the "rollback" script doesnt do the job, you take another Transaction log backup at 9:01am and then you just restore the database using the full backup and all transaction log backups since the full backup. You specify point in time recovery to a moment before the release was applied.

BOL has a full description of this type of recovery: http://msdn.microsoft.com/en-US/library/ms187495%28v=SQL.90%29.aspx

Krasavita SSC-Insane Points: 23138 More actions · Answer 7

Thank you for this information, but i STILL WOULD LIKE TO KNOW how to reduce Data, when I change and save it goes back

NJ-DBA SSChampion Points: 13834 More actions · Answer 8

YOU DONT HAVE TO YELL.. nah, just kidding.

I don't understand what you mean by "when I change and save it it goes back"...

Once you delete records, you have deleted data, thereby creating more free space in the datafile, but the datafile will not get smaller automatically.

If you MUST give the space back to the OS for some reason..try DBCC SHRINKFILE: http://technet.microsoft.com/en-us/library/ms189493.aspx

Krasavita SSC-Insane Points: 23138 More actions · Answer 9

Thank you so much, I need a data to get smaller, so I need to use shrinkfile or shrink db?

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 10

Yes, but why shrink it? 10GB isn't very large and be keeping the database this size you won't have to grow the database or it won't "auto grow" for some time (depends on growth rate).