beware: SQL2005 management studio not 2000 compatible

using 2005 management studio i made changes to an 8 step job on SQL 2000
server. I inserted a new step into position 8 and then tried to save it.
SQL2005 management told me that some feature was not supported on SQL 2000
so i pressed cancel. at this point both SQL 2005 management studio and SQL
200) enterprise management both report a 4 step job.
It is going to take me hours to restore the 8 step job from a backup so that
it can be re-inserted into the live DB.
what a fiasco.
Leon
Leon McCalla (leonmccalla@.hotmail.com.junkfilter) writes:
> using 2005 management studio i made changes to an 8 step job on SQL 2000
> server. I inserted a new step into position 8 and then tried to save it.
> SQL2005 management told me that some feature was not supported on SQL 2000
> so i pressed cancel. at this point both SQL 2005 management studio and SQL
> 200) enterprise management both report a 4 step job.
> It is going to take me hours to restore the 8 step job from a backup so
> that it can be re-inserted into the live DB.
That's bad. That would indicate that there is no transaction handling. I
think this is quite a serious bug. Please go to http://connect.microsoft.com
and file a bug for this. Try to be as specific as possible. Best if you
can give a complete scenario which reproduces the problem. (A two-step job
should be sufficient, I guess.) I know that this will take even more time,
but if Microsoft does not get to know about the problem, they can't mend
their ways.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

better table management (partitions?)

Hi,

For my work I am now learning Sql server 2005 and I have been given a database that has been set up by someone else to work with. It is my job to get the database ready for use in reports.

My problem is that the current database has one huge table with almost 8GB of data. The table contains data from 2004 to present (and growing) from 14 different countries. The reports we use are mostly per country, but we also want to compare the 14 countries to eachother for say, whole 2006. At the moment the table is stored in one single file instead of using partitions.

I believe partitions can give a good performance boost when running the queries. But how do I do this? Currently the country codes are just plain text, can they be used for partitions?

Any advice would be welcome,

Thanks!

It sounds like you could benefit from partitioning. You can learn more here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5partition.asp

If this post answers your question, make sure you mark it "answered" so others can find it quickly.

Thanks -

Buck Woody
http://www.buckwoody.com

|||

Thanks for your reply.

The table has over 5 million records and is still growing, so partitioning seems nice.

However, they are using SQL 2005 standard edition, while only enterprise can use partitioning.

Are there any other ways to make the queries run faster?

|||

Use the Database Tuning Advisor in SQL Server 2005 to see if there are any changes you can make to help. There's a full article about that here:

http://msdn2.microsoft.com/en-us/library/ms173494.aspx

Buck Woody

best way to log out when running the job

Hi,
When I run the job, I am not sure whether it could be done at the table
level to log users out. I would not want to keep them out of the whole
database...only the tables I are updating. Also, once the job is running, I
would like to prevent any users from accessing the tables I am updating unti
l
the end of the job.
Is there anyway to do that?
Thanks
EdYou can take an Exclusive lock on the table and hold it until you are done.
That will not allow anyone to access the table as they will be blocked. Not
sure if they will like you for doing that though<g>.
Andrew J. Kelly SQL MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:933F98D6-BA8A-46D6-A136-35C59BE7F5D6@.microsoft.com...
> Hi,
> When I run the job, I am not sure whether it could be done at the table
> level to log users out. I would not want to keep them out of the whole
> database...only the tables I are updating. Also, once the job is running,
> I
> would like to prevent any users from accessing the tables I am updating
> until
> the end of the job.
> Is there anyway to do that?
> Thanks
> Ed|||Andrew,
Thanks for your answer. I would also like to know what is the best way to
log users out before the job is running. Again, I only need to log them out
of the tables that I will be updating not the whole database.
Thanks again
Ed
"Andrew J. Kelly" wrote:

> You can take an Exclusive lock on the table and hold it until you are done
.
> That will not allow anyone to access the table as they will be blocked. N
ot
> sure if they will like you for doing that though<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:933F98D6-BA8A-46D6-A136-35C59BE7F5D6@.microsoft.com...
>
>|||You can't "Log out" users from a table. They log in to the server and
potentially have access to one or more db's in that server. A user only
takes out locks while they are accessing the data. So any locks they may
have on any specific table are released once they are done with the query or
the transaction is committed or rolled back. Why do you care if they are in
the table or not? Maybe if you explain in more detail what you are
attempting to do we can suggest an appropriate action.
Andrew J. Kelly SQL MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:3937FA07-1F29-497C-99AA-608E89D0E807@.microsoft.com...
> Andrew,
> Thanks for your answer. I would also like to know what is the best way
> to
> log users out before the job is running. Again, I only need to log them
> out
> of the tables that I will be updating not the whole database.
> Thanks again
> Ed
> "Andrew J. Kelly" wrote:
>|||The major reason why I would like to "Log Out" users is because the job has
to be run in the daytime and the job has to be finished as quick as
possible. with so many users using the resources of the server (hugh data
warehouse to be accessed by so many people), I would like to "Log Out" all
users and block the access of all updating tables except the one running by
the job.
any suggestion
Thanks
Ed
"Andrew J. Kelly" wrote:

> You can't "Log out" users from a table. They log in to the server and
> potentially have access to one or more db's in that server. A user only
> takes out locks while they are accessing the data. So any locks they may
> have on any specific table are released once they are done with the query
or
> the transaction is committed or rolled back. Why do you care if they are
in
> the table or not? Maybe if you explain in more detail what you are
> attempting to do we can suggest an appropriate action.
> --
> Andrew J. Kelly SQL MVP
>
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:3937FA07-1F29-497C-99AA-608E89D0E807@.microsoft.com...
>
>|||Well you can KILL a connection that has a lock in the table you want but
that obviously ruins their day. Other than that there is no way to remove a
users locks on a table until that user has finished accessing it and
committed or rolled back their transaction if they had one. As I stated
earlier the only real way to prevent them from accessing the table is to
take out an exclusive lock. You may have to wait a while until all current
users in that table have finished before the lock takes hold. But no new
users can access it either. It almost sounds like you need to place the db
in a single user mode, do the work and return it as soon as possible. You
can specify a termination option to kick out the current users after a
period of time with the alter database command.
Andrew J. Kelly SQL MVP
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:5C6CCABD-739B-472F-892B-4F3B3F53DB31@.microsoft.com...
> The major reason why I would like to "Log Out" users is because the job
> has
> to be run in the daytime and the job has to be finished as quick as
> possible. with so many users using the resources of the server (hugh data
> warehouse to be accessed by so many people), I would like to "Log Out" all
> users and block the access of all updating tables except the one running
> by
> the job.
> any suggestion
> Thanks
> Ed
> "Andrew J. Kelly" wrote:
>

Best way to backup a database,

Hi,

Does anyone have a preference on how they back up their database?

Currently I do it via a SQL Server Agent Job, that runs some T-SQL to backup the system database, and backup the user databases and transaction logs (where appropriate).

I was thinking about moving this to a VBScript for the reason that it will allow me to (easily) write to a log and email the relevant people (i.e. if a backup succeeds or fails).

Question is, in SQL (T-SQL), is there an easy way to write to a text file, and send out an email? (does sendmail require outlook to be installed on the server?)

Thanks again!.You can set up your backups through Enterprise Manager. When you enable a schedule for the backup, the backup is added as a job. For that job you can setup notifications eg mail and adding results to the application log.
To send mails, sql server needs a mapi compliant mail program, which can be Outlook.|||I do my backups with a T-SQL script. You can send emails from the script using xp_sendmail. Why write a text file log? You can write to a database table instead, which can provide a lot more functionality for a logviewer GUI.

Instead of doing incremental backups of my larger databases (which get progressively larger), I write a complete backup everyday to a network disk that has seperate folders for each day. I also do a shrink and translog truncate before the backup runs. Here's my script for the backup step:

DECLARE @.day_of_week VARCHAR(15),
@.server_name VARCHAR(25),
@.db_location_string VARCHAR(128),
@.log_location_string VARCHAR(128),
@.database_name VARCHAR(128)

DECLARE database_cursor CURSOR FOR
SELECT [name] as DBNAME FROM sysdatabases
WHERE [name] NOT IN ('master', 'model', 'msdb', 'tempdb')

SET @.day_of_week=DATENAME(dw, GETDATE())
SET @.server_name=@.@.SERVERNAME

OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @.database_name

WHILE @.@.FETCH_STATUS=0
BEGIN
SET @.db_location_string='\\myServer\SQL Backups\' + @.day_of_week + '\SQL\' + @.server_name + '\' + @.database_name + '.bak'
SET @.log_location_string='\\myServer\SQL Backups\' + @.day_of_week + '\SQL\' + @.server_name + '\' + @.database_name + '_log.bak'
BACKUP DATABASE @.database_name TO DISK = @.db_location_string WITH NOINIT , NOUNLOAD , NAME = @.database_name, NOSKIP , STATS = 10, NOFORMAT
--BACKUP LOG @.database_name TO DISK = @.log_location_string WITH NOINIT , NOUNLOAD , NAME = @.database_name, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM database_cursor INTO @.database_name
END
CLOSE database_cursor
DEALLOCATE database_cursor|||Oh, if you don;t have your server set up for SQL Mail, which is, frankly, a pain, you can add a VBScript step to your back job that sends mail using the CDONTS object.

Configure your backup step to on failure, go to the send mail step, otherwise skip it.|||Thanks for the advice.

All suggestions taken on board.|||bpdWork:

Thank you so much for sharing your sql script. I might be able to use that in a new backup plan I am working on. One question, however, and I know this is asking a lot. Do you have another script that will restore all these databases?

Thanks
Tom|||nevermind that last question, it was too easy!

Thanks
Tommy