Currently SQL2005 is on SP3 for a while now ! You should evaluate it and upgrade if you can !

The logon trigger (available with sp2) is only validated...

Tara-1044200 (11/4/2009)

---

I have database with 100's of partitions ( a partition for a month) , how can i add or delete partitions on 2TB database.

example:2TB of databases containing 20 yrs...

If you have SQL2005 SP2 or higher have a look at http://www.simple-talk.com/sql/sql-server-2005/logon-triggers/

- please post in the correct forum ! ( this is a sql2005 forum )

- the lack of SQLAgent manager role is one of the weak spots of SQL2000 ...

HTH

Keep in mind that if you are using sqlauthentication and didn't migrate the sqluserid including the SID, you must resync the user ids using:

-- to be executed per sqluserid !

exec...

What issue did you encounter to validate the need to uninstall the sp ?

Robert Frasca (10/30/2009)

---

Just to clarify. If you're using 64-bit SQL Server it is recommend that Lock Pages in Memory be enabled and that the AWE enabled switch be set....

it isn't mandatory, but it helps for sure :-P:-D

I posted my SQL2005 script, because you posted in the SQL2005 forum !

for sql2000 you can use:

SET NOCOUNT ON

SET ROWCOUNT 1000

WHILE 1 = 1

BEGIN

DELETE TableName WHERE <Your Condition Here>

...

You can also produce the backup to a remote location, provided you grant...

That's an eye opener indeed !

In many cases we only see the beauty of life, once we have to mis part of it.

For Belgium these links may get donation started:

Flemish...

If both sqlservers can "see" eachother and the destination service account has read auth on the source servers backup folder, you can restore using the source server bak files

restore database...

This is one way to work with batches:

If you don't switch to simple recovery, you log files may get filled up and even grow !

If you switch to simple recovery,...

or maybe someone just dropped the database that had been set for default database of the service account.

Solution:

ALTER LOGIN [ictlan\MSSQLSERVICE] WITH DEFAULT_DATABASE=[master]

Thank you both for clarifying how to change that behaviour at windows level;

I hadn't been looking in that direction, because in many cases, when you encounter this behaviour, it points...