adjusting size of templog does not remain

A bit bizarre I could be overlooking something since tempdb is not like a user database.
Our log size for tempdb (templog) is 15mb, I need to increase this to 20mb.
I do so through the SQL Enterprise Manager. Adjust the size, hit apply and the size is adjusted!
When I run a Financial apps that uses tempdb, I receive the message that the templog files are full backup and ...
When I go back to tempdb the templog file is back at 15mb.
Jeff
--
Message posted via http://www.sqlmonster.comPerhaps you restarted SQL Server in between. Size for tempdb at startup you set using ALTER
DATABASE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jeffrey Sheldon via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:c83bbdcd541d46a4bb16394dcf882151@.SQLMonster.com...
>A bit bizarre I could be overlooking something since tempdb is not like a user database.
> Our log size for tempdb (templog) is 15mb, I need to increase this to 20mb.
> I do so through the SQL Enterprise Manager. Adjust the size, hit apply and the size is adjusted!
> When I run a Financial apps that uses tempdb, I receive the message that the templog files are
> full backup and ...
> When I go back to tempdb the templog file is back at 15mb.
> Jeff
> --
> Message posted via http://www.sqlmonster.com|||actually i thought the same..but when i change it on one of my local machine
using EM and restart sql server i see size of tempdb after the modification...
"Tibor Karaszi" wrote:
> Perhaps you restarted SQL Server in between. Size for tempdb at startup you set using ALTER
> DATABASE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Jeffrey Sheldon via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
> news:c83bbdcd541d46a4bb16394dcf882151@.SQLMonster.com...
> >A bit bizarre I could be overlooking something since tempdb is not like a user database.
> >
> > Our log size for tempdb (templog) is 15mb, I need to increase this to 20mb.
> >
> > I do so through the SQL Enterprise Manager. Adjust the size, hit apply and the size is adjusted!
> >
> > When I run a Financial apps that uses tempdb, I receive the message that the templog files are
> > full backup and ...
> >
> > When I go back to tempdb the templog file is back at 15mb.
> >
> > Jeff
> >
> > --
> > Message posted via http://www.sqlmonster.com
>
>|||Ahh yes, the lightbulb went off. tempdb needs the ALTERDATABASE command to adjust its data/log files.
Thanks
Jeff
--
Message posted via http://www.sqlmonster.com|||Ok now I am stumped.
I did
use master
ALTER DATABASE tempdb
MODIFY FILE
(name = templog,
size = 20MB)
go
The templog is then adjusted to 20mb.
I run my financial stored procedure and receive the message templog ran out of space blah blah...
So I go back to review and increase so more, but lo and behold templog is Back at 15mb!!!!
A stored procedure that simply taking data from one database (less than 2000 records) and using temp tables to do some calculations make the log file shift back to 15mb.
Any ideas?
Jeff
--
Message posted via http://www.sqlmonster.com|||What size does master..sysaltfiles specify for the file? Also, perhaps you have autoshrink turned on
for the tempdb database?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jeffrey Sheldon via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:29ce9a8218c945548d497982ae85ee55@.SQLMonster.com...
> Ok now I am stumped.
> I did
> use master
> ALTER DATABASE tempdb
> MODIFY FILE
> (name = templog,
> size = 20MB)
> go
> The templog is then adjusted to 20mb.
> I run my financial stored procedure and receive the message templog ran out of space blah blah...
> So I go back to review and increase so more, but lo and behold templog is Back at 15mb!!!!
> A stored procedure that simply taking data from one database (less than 2000 records) and using
> temp tables to do some calculations make the log file shift back to 15mb.
> Any ideas?
> Jeff
> --
> Message posted via http://www.sqlmonster.com

Adding bit fields

I tried to add 7 bit filelds together but it failed. I have 7 bit fields to
indicate days of w that person works, e.g. Mon, Tue,....Sun and want to
add them together to come up with # days worked in a w. I was trying
[Mon]+[Tue]+[Wed]......+[Sun] but it failed. Is there any easier way than
multiple IF statements? Thanks.
DavidTry CAST([Mon] AS INTEGER) + CAST([Tue] AS INTEGER) + ...
Are you sure you want to use a bit field there? Personally I find they're
more trouble than they're worth.
Thx,
Mike C.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:OHNnCXcHFHA.2620@.tk2msftngp13.phx.gbl...
>I tried to add 7 bit filelds together but it failed. I have 7 bit fields
>to indicate days of w that person works, e.g. Mon, Tue,....Sun and want
>to add them together to come up with # days worked in a w. I was trying
>[Mon]+[Tue]+[Wed]......+[Sun] but it failed. Is there any easier way than
>multiple IF statements? Thanks.
> David
>|||How about a change of design. Much easier to write queries against
something like this:
CREATE TABLE WorkingTime (ssn VARCHAR(10) NOT NULL REFERENCES Employees
(ssn), work_dt DATETIME NOT NULL, PRIMARY KEY (ssn,work_dt))
For example:
SELECT ssn, COUNT(*)
FROM Timesheet
WHERE work_dt >= '20050228'
AND work_dt < '20050307'
GROUP BY ssn
As you are discovering, repeating groups of BIT columns create all
sorts of problems. It is a cardinal error to represent data in column
names.
If you can't change it then use CASE:
SELECT
CASE mon WHEN 1 THEN 1 ELSE 0 END +
CASE tue WHEN 1 THEN 1 ELSE 0 END +
CASE wed WHEN 1 THEN 1 ELSE 0 END +
..
David Portas
SQL Server MVP
--|||I do want the bit fields because they work great with checkboxes in
Access forms.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||That doesn't make them a good design from a database perspective. You
probably only have to design one Access form but might have to develop
many queries against the data so it's more important to have a logical
design that works well for data manipulation and reporting.
David Portas
SQL Server MVP
--|||You may be right. This was an uploaded Access database and in Access
the reporting and querying worked fine. Now that it is on SQL maybe we
will change it. It just seemed odd that I could total up yes/no field
in Access, but not on something as powerful as SQL Server.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hi David,
SQL tends to make you re-think things the 'right' way. For instance, Access
might have let you total Yes/No fields, but that concept really doesn't make
much sense. After all, what is YES + YES + YES equal to? An "Emphatic
YES!!"? In SQL you would most likely use a COUNT() function in a SELECT
statement to determine how many YES's you had, which is what it sounds like
you're really after here. If you want to stay with the bits, David Portas
gave you the CASE statement that will do the trick, or you can do the CAST
conversion; I would recommend normalizing your DB and getting away from bits
though.
Thanks,
Mike C.
"David" <daman@.biteme.com> wrote in message
news:e96y3TeHFHA.3484@.TK2MSFTNGP12.phx.gbl...
> You may be right. This was an uploaded Access database and in Access
> the reporting and querying worked fine. Now that it is on SQL maybe we
> will change it. It just seemed odd that I could total up yes/no field
> in Access, but not on something as powerful as SQL Server.
> David
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!