Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Riaan777
Starting Member
3 Posts |
 Posted - 2008-11-27 : 09:36:52
|
| Hello,We are dynamically creating tables in a SQL 2005 database to store log data - a new table for each day. The problem is that it works fine for about 8 days to 2 weeks and then just creates the log table for the new day, but doesn't write any records to the table. When I drop the oldest log table from the database it again starts writing to the newest table.I've verified that autogrow is turned on for the database & that there is no size limit set. The funny thing is it does actually autogrow, but only to a certain point (there is more than enough hard drive space available). It is on various machines on various sites we are getting this behavior, so it can't be the SQL installation.Has anyone dealt with something like this before?Thanks in advance,Riaan du Plessis |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-27 : 09:54:54
|
| What is the purpose of dynamically creating tables to store log data? Can you explain your requirements so we can further assist? |
 |
|
|
Riaan777
Starting Member
3 Posts |
Posted - 2008-11-28 : 01:03:43
|
| Well, basically I'm working on a time & attendance system which stores clock in and clock out (log) data for individuals. Each day a new table is created in a our database to store all the clock in/out transactions for that particular day. I am not sure why it was initially decided to go this route as I only later joined the project. |
|
|
|
Riaan777
Starting Member
3 Posts |
Posted - 2008-11-28 : 09:20:36
|
| After scrutinizing some code we discovered that the application attempts to create table indexes in the new tables, but they have duplicate names of older table indexes - hence failing to create the new tables properly.Thanks for your willingness to assist. |
|
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2008-11-28 : 09:44:29
|
| Why would you want to create a new table for each day, wouldn't it be more sensible to have a large table hold this information and then hav queries/table-functions designed to retrieve the require data.Sounds like having a new table for every day is bad bad planning and going to be a nightmare to administrate |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-28 : 13:01:05
|
quote:
Originally posted by Riaan777
Well, basically I'm working on a time & attendance system which stores clock in and clock out (log) data for individuals. Each day a new table is created in a our database to store all the clock in/out transactions for that particular day. I am not sure why it was initially decided to go this route as I only later joined the project.
First of all, You need to fire that guy who designed this system. what if someone wants to see data before 2 weeks? You need to sit down and design properly rather than going with the flow. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-11-29 : 08:36:48
|
| Very true. You should definately put your data into one table. If you run into issues with applications needing the data in the old way then you can always dynamically create a view to select for a given week instead of the new table. It's not nice but it gives you a way of improving the system while maintaining a consistent historic view of things. |
|
|
|
|
|
|
SQL 2005 data insertion issue