Showing posts with label huge. Show all posts
Showing posts with label huge. Show all posts

Wednesday, March 28, 2012

Manage huge amount of data

Requirement:
1 billion records to be inserted every day
180 days data to be maintained
180 * 1 billion records = approx no of records = defines the size of the
database
Need to design the database/process to maintain so huge data?
- RHi Rakesh,
Need more info like an idea of the number of tables, size of rows, querying
which will reflect what indexes are required etc...
You will need some good kit though, lots of disks; what sort of fault
tolerance are you looking at because backups are going to be a problem.
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:CE1870BB-68DB-47B8-A1B8-EFE8C30FB333@.microsoft.com...
> Requirement:
> 1 billion records to be inserted every day
> 180 days data to be maintained
> 180 * 1 billion records = approx no of records = defines the size of the
> database
> Need to design the database/process to maintain so huge data?
>
> - R|||> 1 billion records to be inserted every day
> 180 days data to be maintained
> 180 * 1 billion records = approx no of records = defines the size of the
> database
With an average row size of 100 bytes, this calculates to about 18 TB of
usable space not including index overhead. You'll also need to sustain a
rate of over 10 thousand inserts per second 24x7.
Very large tables are often partitioned for manageability reasons and to
address backup issues like Tony mentioned. Attention to detail is very
important. Unless you have experience working with very large databases, I
suggest you engage consultants with VLDB experience to help you out. We can
help you with specific questions but a project of such magnitude requires
dedicated resources with specialized experience.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:CE1870BB-68DB-47B8-A1B8-EFE8C30FB333@.microsoft.com...
> Requirement:
> 1 billion records to be inserted every day
> 180 days data to be maintained
> 180 * 1 billion records = approx no of records = defines the size of the
> database
> Need to design the database/process to maintain so huge data?
>
> - R

Making the Log file small

I'm using ss2005 and Management Studio...
I have this huge log file and I want it to be small. I read that if I do a
transaction backup that it will make it small. That did give it 90% free
space. So then I did a shrink (all three types) but that did not shrink it
at all. I did a database shrink and that did nothing.
Then I read that if I detach it, delete the log file (I just moved it), and
reattach it that a new small log file will be built. But, the reattach
would not work saying that the log file is missing. So I had to move it
back and now I still have this huge log file.
How can I make it small?
Thanks,
Tbackups shrink the logical space, not physical...
try backup log with the no_log clause, then shrink the file
Tina wrote:
> I'm using ss2005 and Management Studio...
> I have this huge log file and I want it to be small. I read that if I do a
> transaction backup that it will make it small. That did give it 90% free
> space. So then I did a shrink (all three types) but that did not shrink it
> at all. I did a database shrink and that did nothing.
> Then I read that if I detach it, delete the log file (I just moved it), and
> reattach it that a new small log file will be built. But, the reattach
> would not work saying that the log file is missing. So I had to move it
> back and now I still have this huge log file.
> How can I make it small?
> Thanks,
> T|||After you backup your log file, you can shrink the file with DBCC
SHRINKFILE. For example:
DBCC SHRINKFILE('MyDatabase_Log', 100)
See the SQL Server Books Online for more info on DBCC SHRINKFILE.
The most common cause of out-of-control log files is failure to implement a
backup/recovery plan. If you choose to run the database in the
FULL/BULK_LOGGED recovery model, you will need to do regular transaction log
backups to keep the log size reasonable and provide the means to minimize
data loss after a failure. You can also run the database in the SIMPLE
recovery model so that you don't need to bother with log backups. However,
your only recovery recourse in the SIMPLE model is to restore from the last
full backup.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:Oj5uSqvmGHA.4772@.TK2MSFTNGP04.phx.gbl...
> I'm using ss2005 and Management Studio...
> I have this huge log file and I want it to be small. I read that if I do
> a transaction backup that it will make it small. That did give it 90%
> free space. So then I did a shrink (all three types) but that did not
> shrink it at all. I did a database shrink and that did nothing.
> Then I read that if I detach it, delete the log file (I just moved it),
> and reattach it that a new small log file will be built. But, the
> reattach would not work saying that the log file is missing. So I had to
> move it back and now I still have this huge log file.
> How can I make it small?
> Thanks,
> T
>|||http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:Oj5uSqvmGHA.4772@.TK2MSFTNGP04.phx.gbl...
> I'm using ss2005 and Management Studio...
> I have this huge log file and I want it to be small. I read that if I do a transaction backup
> that it will make it small. That did give it 90% free space. So then I did a shrink (all three
> types) but that did not shrink it at all. I did a database shrink and that did nothing.
> Then I read that if I detach it, delete the log file (I just moved it), and reattach it that a new
> small log file will be built. But, the reattach would not work saying that the log file is
> missing. So I had to move it back and now I still have this huge log file.
> How can I make it small?
> Thanks,
> T
>

Friday, March 9, 2012

Major flaw with Reporting Services!

There seems to be a major flaw with Reporting Services that will give huge problems. RS can't support subreports when it is generated in Excel. As you know all reporting, almost all of them are dumped in Excel format. I really hope someone fix this huge flaw to this software. I wouldn't recommend RS to anyone if I have known about this problem earlier. This problem is huge, for me at least 75% of the problem.

Do you know when patch or fix or whatever will be available so people can generate subreports in excel? I would really hope Microsoft put out just a patch, instead of a release so that it will be available soon to everyone. This is crazy!

Enkh.

Sub reports have a limititation in Excel of not working in a Table or Matrix. However, they will work in a list or a non data region.|||

I would really hope that you guys fix this problem, because this problem is huge since a lot of people usually generate reports in excel and fix the problem and send it when they don't have a lot of time. You can't do that with PDF reports.

Just to give you a little suggestion and information and I think you can bring this up in the needed RFE to RS next version when you guys go over RS in your usual meetings..

1. Make sub-reports function flawlesly in Excel whether it is in table or matrix or whatever.

2. I think event management is really needed in Reporting Services. Active Reports has events like "on report start", "on report end", "on fetch data" etc that users can write codes and those get executed depending on the function and event that is in. This is really useful when manipulating data greatly. This is very very very needed.

3. There seems to be a huge problem with how layout of the report is handled. For example when a table is not getting rendered on a certain page, it's header is not getting displayed on that page. I think there should be absolute setting of headers on all pages that doesn't depend on a whether table or anything is on the page.

Let me know if you have questions, I would be glad to help you guys about this RS feature enhancement. I create fairly complicated reports in RS.

Let me know if you have any questions,

Enkh.

|||I fail to see the significance of exporting reports to Excel. Report consumers frequently ask for the ability to export to excel - as excel is the tool that allows them to feel they are still in charge. However after years I have yet to see anyone do anything intelligent with the report output once they get it to excel.

Do your users do something intelligent with Excel Output?|||I have created several reports that have many subreports in them. Everything exports to Excel fine for me.|||

Yes it will export fine for reports with subreports not in a table or matrix. Once you put a subreport in a Table or Matrix, the export to Excel doesn't work saying it is ignoring the cell in the subreports. I don't think it knows how to create cells in Excel when subreport is in a Table. My main use of subreports this way is that one information in the main report needs to have multiple information handled in a subreport. I can't really do that all the time through just one query, so I have to create a second query to get a bunch of info. for the main record, and the advantage of Table or Matrix is that it can loop through the dataset, so the subreport has to be in the detail section so that it can loop with the main database record.

I hope I made it clear. Let me know if you have questions and we probably we can exchange ideas.

Enkh.

|||

Yes definitely, they need an instant edit access to the report so that when there is no time to correct the information in the database on the data (find the bug), they will have to change the values in the report themselves once.

Enkh.

|||

Enkht,

Can you give me an example of when you would want to have a report that does not reflect the actual state of the data? While I understand pen and ink changes, I think that this capability could become a real Pandora's box of trouble.

R