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

additional logg for DTS_DataPump Task inDTS Package

hi
any one knows how can log in a dts package othere than the defalt log .

i can work it out using DTSPackageLog.WriteStringToLog using a seperate activex task in my package .

the problem is that in my package there are more than 15 dts datapump tasks ,so to make log for each task i have to use 15 such activex task.......

so any one know how we can use DTSPackageLog.WriteStringToLog
or any thing similar in dts datapump task so that we can know the beginning and ending of individual task

abhiCheck this link (http://vyaskn.tripod.com/sql_server_dts_error_file.htm) from Vyas.

Adding Users to MSDE

I am having a authentication problem.. users log into my website... I authenticate them against Active Directory.. and then I try to query a MSDE database... my connection string is as follows:

 Dim connectionString As String = "server='srv_sql'; user id='sa'; password='MyPassword'; Database='MyDB'"
 Dim dbConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)

However since I have

<identity impersonate=true>

in my web.config file... it tries to login to the MSDE database as the user.. not as the user SA.

I cannot change the web.config file, since I need that for the active directory authentication to work...

in SQL Server you can add users to a database through the enterprise manager.. how can I do a similar thing using MSDE ?

Is it even possible? or do I have to upgrade to a full SQL instance?

any help would be appreciatedTo answer your question, use the osql command line utility. See this KB article for more info:HOW TO: Manage the SQL Server Desktop Engine (MSDE 2000) by Using the Osql Utility.

what you need to do is use the sp_grantlogin system sp to add the login:

EXEC sp_grantlogin 'Corporate\Test'

Then give it access like this:

EXEC sp_grantdbaccess 'Corporate\BobJ', 'Bob'

You'll also need to give it permissions.

That said, there may need to be adjustments for use with Active Directory.

BUT, you should never, ever, NEVER use the sa login for database access. Not for any app and certainly not for an ASP.NET app. You're opening yourself up to a whole lot of hurt doing it this way. Instead, create a login that has only the specific permissions needed to run the app. No more.

It's more work, but you'll have made the app far more secure.

Don|||...for sure don't use SA on the page itself. What about permissioning the database with Windows authenticated logins since you are using AD...if you haven't already. If you do then you can set windows authentication via Internet Services Manager on the directory hosting the page (be sure to get rid of anonymous users)

Adding users to log-shipped DB

Hallo!
After I configure log shipping between databases on primary server A and
secondary server B, is it possible to add new users or groups and give them
access to log-shipped DB on server B only? Since the DB on server B would be
in read-only mode I'm not sure if that would allow me to add users to that
DB.
I would like a group of users to have read access to log shipped DB on
server B so they can run reports,
but they should not have any access to that DB on server A.
Thanks
Tom
You can make NO changes on Server B or it will break the log shipping..After
having thought about this for a few minutes, I don;t think you can do what
you wish... You must add the users to server A, that will get shipped to
server B. If you delete the users or change permissions on Server A, that
will also go to Server B...
It is a great problem to think about, and it seems that you shouldn't be
stuck, but I think you may be... ( Maybe Ron Talmage is watching and will
have a suggestion... He is very good at log shipping.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:%23xEFcUQxEHA.2316@.TK2MSFTNGP15.phx.gbl...
> Hallo!
> After I configure log shipping between databases on primary server A and
> secondary server B, is it possible to add new users or groups and give
them
> access to log-shipped DB on server B only? Since the DB on server B would
be
> in read-only mode I'm not sure if that would allow me to add users to that
> DB.
> I would like a group of users to have read access to log shipped DB on
> server B so they can run reports,
> but they should not have any access to that DB on server A.
> Thanks
> Tom
>

Adding users to log-shipped DB

Hallo!
After I configure log shipping between databases on primary server A and
secondary server B, is it possible to add new users or groups and give them
access to log-shipped DB on server B only? Since the DB on server B would be
in read-only mode I'm not sure if that would allow me to add users to that
DB.
I would like a group of users to have read access to log shipped DB on
server B so they can run reports,
but they should not have any access to that DB on server A.
Thanks
TomYou can make NO changes on Server B or it will break the log shipping..After
having thought about this for a few minutes, I don;t think you can do what
you wish... You must add the users to server A, that will get shipped to
server B. If you delete the users or change permissions on Server A, that
will also go to Server B...
It is a great problem to think about, and it seems that you shouldn't be
stuck, but I think you may be... ( Maybe Ron Talmage is watching and will
have a suggestion... He is very good at log shipping.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:%23xEFcUQxEHA.2316@.TK2MSFTNGP15.phx.gbl...
> Hallo!
> After I configure log shipping between databases on primary server A and
> secondary server B, is it possible to add new users or groups and give
them
> access to log-shipped DB on server B only? Since the DB on server B would
be
> in read-only mode I'm not sure if that would allow me to add users to that
> DB.
> I would like a group of users to have read access to log shipped DB on
> server B so they can run reports,
> but they should not have any access to that DB on server A.
> Thanks
> Tom
>

Adding users to log-shipped DB

Hallo!
After I configure log shipping between databases on primary server A and
secondary server B, is it possible to add new users or groups and give them
access to log-shipped DB on server B only? Since the DB on server B would be
in read-only mode I'm not sure if that would allow me to add users to that
DB.
I would like a group of users to have read access to log shipped DB on
server B so they can run reports,
but they should not have any access to that DB on server A.
Thanks
TomYou can make NO changes on Server B or it will break the log shipping..After
having thought about this for a few minutes, I don;t think you can do what
you wish... You must add the users to server A, that will get shipped to
server B. If you delete the users or change permissions on Server A, that
will also go to Server B...
It is a great problem to think about, and it seems that you shouldn't be
stuck, but I think you may be... ( Maybe Ron Talmage is watching and will
have a suggestion... He is very good at log shipping.)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:%23xEFcUQxEHA.2316@.TK2MSFTNGP15.phx.gbl...
> Hallo!
> After I configure log shipping between databases on primary server A and
> secondary server B, is it possible to add new users or groups and give
them
> access to log-shipped DB on server B only? Since the DB on server B would
be
> in read-only mode I'm not sure if that would allow me to add users to that
> DB.
> I would like a group of users to have read access to log shipped DB on
> server B so they can run reports,
> but they should not have any access to that DB on server A.
> Thanks
> Tom
>

Adding datafile breaks my log shipping process

Hi All,

I am on sql server 2005. I have a production database that i log ship to another server and keep a standby copy of that database. Transaction logs are backed up every 15 minutes on the production database then copied to the standby server and then applied in order to the read-only standby database.

Every month we add a new partition and datafile to the production database. This causes the log shipping process to break because the read-only standby database doesn't have the new datafile present. I had hoped that the alter database command to create the datafile would be logshipped. It forces me to do a full db restore every month which is a major pain.

Has anyone encountered a similiar scenario? How can I 'log ship' the addition of a datafile every month and avoid doing a full restore of my standby db?

I should add that this is a home grown log ship process, we aren't using the sql server built-in log shipping. Here is a typical backup transaction log script that i'm using:

-- using sql litespeed
exec master..xp_backup_log @.database='dbname,
@.filename='d:\dbbackups\dbname_txlog_<uniqueidentifier>.bak', @.init=1

Any help would be greatly appreciated.
<!--[endif]--> Log shipping cannot handle database file operation, eg. adding new database files. Same operation must be done manually in standby database. <!--[endif]-->

In database mirroring these operations will be handled automatically.