Mark Breen
marklbreen at gmail.com
Fri Jan 6 04:19:19 CST 2012
Hello Gustav and John, Funny, I was discussing John's log file with my wife last night at 11:30 pm :) I said the same thing, unless you need to step back to a point in time, just detach the db, copy the MDF only, zip and and download it and then reattach only the mdf and allow SSMS to automatically recreate an empty log file. I rarely leave databases in Full recovery mode, unless the client is capable of restoring to a point in time. re Databases - I sometimes feel like the poor cousin because I am more into databases than programming? Am I just a script kiddie or can I hold my head proudly? Mark On 6 January 2012 07:11, Gustav Brock <Gustav at cactus.dk> wrote: > Hi John > > > It just looks to me like they are not spending much time on the > database side of things. > > They probably don't know how. Such stories always remind me about a > presentation I once attended where the project leader explained about a > large web project. One thing he had noted was, that among his otherwise > skilled developers were some that "were afraid of the database". It struck > me that I had never looked at it that way and that it could explain why so > much database work you meet out there is crap. But think about it; if you > were not very well educated in database design what would you do when you > meet such a monster? For us it is maybe not a non-issue but at least > nothing to be afraid of. But if you know very little about SQL, it is > scary. It can also - to some degree - explain the popularity of NonSQL > databases. > > What the project leader did was to create and present views to such > developers with exactly what they needed and, of course, as read-only if > that would do. > > As to your task, couldn't the BI parts and tools of Visual Studio and SQL > Server be what you are looking for? I haven't worked with these though. > > Also, as this effectively is a write-only database, I guess you could > easily clean up the log file to a fraction of the current size. > > /gustav > > > >>> jwcolby at colbyconsulting.com 05-01-2012 23:43 >>> > Guys, > > My client has a web page hosted on a RackSpace (the name of the hosting > provider) server. It runs > Windows 2008 Standard, appears to be a physical (as opposed to virtual) > server, has 12 gigs and 4 > cores. It runs SQL Server 2008 Standard. the C: drive is 300 gigs of > which about 109 gigs are > used. The client does meail ad campaigns for things like Aleve pain > reliever etc. There is a SQL > Server database which stores information about what happens on the web > page, hits, clicks moves > between pages etc. > > The specific database that stores all this info says that it has a size of > 60.33 gigs. Of that the > data file is about 5.56 gigs with 67% free and the log file is 54.77 gigs > with 16% free. > > So... > > I just got involved with this stuff. I didn't build the database nor the > web site. I was asked to > go find out why a specific statistic (time on site) was "out of whack". > My question really is that > the statistics are coming from stored procedures, mostly out of one > specific table EVNT_Events. > That table has no indexes on it. It does have an autoincrement field but > has no PK per se and of > course is a heap structure. > > What I know so far is that events are logged on a session id, that session > id is apparently a unique > identifier for a "session" or a specific browser logged into that web > site. An event record is > logged every 10 seconds, but perhaps more because there are "event types" > such as clicks, > backbutton, etc. The following is a count of events for this campaign > between 11/25 and 1/5, > roughly 2.4 million events over 40 days, which is an average of about an > event every 100 seconds. > Not exactly a high traffic campaign. Which is not to say the next one > won't be but let's be real, > click throughs from an email campaign isn't a high volume business. > > 2372576 2011-11-25 00:01:50.770 2012-01-05 14:10:08.037 > > > The statistics report that runs takes awhile to run. I would bet that > some judicious indexing would > speed things up. At the very least making a clustered index on the > autoincrement EVNT_ID. Of > course reporting is not the primary concern here. Still... > > So assuming I get permission to do anything, what do I do? I don't want > to make any changes without > first backing up the database. I would highly doubt that anything has > ever been backed up though I > will be asking these questions. > > 1) This appears to be a standard SQL Server setup, i.e max memory is > insane, all cores assigned to > SQL Server, compression is not turned on and so forty. Is there a reason > not to compress the backup? > 2) The log file contains a lot of "stuff". > 3) Performing a backup to the same physical drive is going to take awhile > and use a lot of disk > space, even if compressed. > 4) Once backed up what happens to that log file? Does it automatically > "empty"? > 5) Would creating a clustered index on that "PK" help make things faster? > 6) Many of the statistics group by the session ID. Obviously indexing > that and a few other key > fields would speed up reporting but slow down creating these log records > but would it slow it down > enough to make any difference? It seems unlikely to me but I don't have > much experience in a > situation like this. What I do know is that once created these event > records are apparently never > modified. > > I am just trying to get some "feel" for what will work and what to > recommend. I am to a large > extent the tech support for the client. The web site and the campaigns > are created by a third party > and that will continue. It just looks to me like they are not spending > much time on the database > side of things. > > -- > John W. Colby > Colby Consulting > > Reality is what refuses to go away > when you do not believe in it > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >