Sql Auto grow: manager

Showing posts with label manager. Show all posts

Tuesday, March 27, 2012

Automating backups with TSM

I was wondering if anyone can share the procedure(s) used in setting up an automated MS SQL database backup through tivoli Storage Manager
( 5.2.7 )........?I'd settle for getting Tivoli to back up files 50% or more of the time. I'd consider getting Tivoli to do reliable, repeatable backups to be a miracle.

-PatPsql

Thursday, March 22, 2012

Automatical Import from MySQL-Table?

Hi All!
is it possible to create a job in the SQL Enterprise Manager that regularly
imports the data from a table of a MySQL-database?
how could I achieve this aim without having to program a lot?
any hints are highly appreciated!
cheers, jens
Take a look at OPENROWSET if you don't want to create a linked server
or OPENQUERY if you do create a linked server
http://sqlservercode.blogspot.com/
|||Hi SQL,
for me a so called "linked server" sounds better, how could I create a
linked server in the MS SQL ENterprise Manager?
Cheers, Jens
"SQL" <denis.gobo@.gmail.com> schrieb im Newsbeitrag
news:1134485484.301414.309720@.g43g2000cwa.googlegr oups.com...
> Take a look at OPENROWSET if you don't want to create a linked server
> or OPENQUERY if you do create a linked server
> http://sqlservercode.blogspot.com/
>
|||Go to security/Linked Servers
Right Click on Linked Servers--> New Linked Server
Use Microsoft OLE DB Provider for ODBC Drivers
Use the Connection String
DRIVER={MySQL ODBC 3.51
Driver};SERVER=myserver.com;DATABASE=database;USER
=user;PASSWORD=password;OPTION=3
And in Provider Options select:
level zero only
Non-transacted updates (something)
Allow InProcess
the linked thing will work as
mylinkedserver...tablename
http://sqlservercode.blogspot.com/
|||Hey SQL, that worked perfectly, thanx a lot!
now I see the tables of "DatabaseXYZ" the "MyLinkedMySQLServer". But how can
I now create a job that imports all data from "Table1" of "DatabaseXYZ"
into "DatabaseABC" of my MSSQLServer?
Cheers, Jens
"SQL" <denis.gobo@.gmail.com> schrieb im Newsbeitrag
news:1134491324.820321.274410@.z14g2000cwz.googlegr oups.com...
> Go to security/Linked Servers
> Right Click on Linked Servers--> New Linked Server
> Use Microsoft OLE DB Provider for ODBC Drivers
> Use the Connection String
> DRIVER={MySQL ODBC 3.51
> Driver};SERVER=myserver.com;DATABASE=database;USER
> =user;PASSWORD=password;OPTION=3
> And in Provider Options select:
> level zero only
> Non-transacted updates (something)
> Allow InProcess
> the linked thing will work as
> mylinkedserver...tablename
>
> http://sqlservercode.blogspot.com/
>
|||Create a job and copy this code (obviously you will have to change it,
for example i don't know what your join condition is)
Then create a schedule and that should do it
insert into DatabaseABC.dbo.table1
select * from DatabaseABC.dbo.table1 ms left join
MyLinkedMySQLServer.DatabaseXYZ.Owner.Table1 my
on ms.id =my.id
where my.id is null
To Create a job go to Managment-->Sql server Agent-->Jobs
Right click --> New Job add a step paste the code
add the schedule and that should do it
http://sqlservercode.blogspot.com/

Automatical Import from MySQL-Table?

Hi All!
is it possible to create a job in the SQL Enterprise Manager that regularly
imports the data from a table of a MySQL-database?
how could I achieve this aim without having to program a lot?
any hints are highly appreciated!
cheers, jensTake a look at OPENROWSET if you don't want to create a linked server
or OPENQUERY if you do create a linked server
http://sqlservercode.blogspot.com/|||Hi SQL,
for me a so called "linked server" sounds better, how could I create a
linked server in the MS SQL ENterprise Manager?
Cheers, Jens
"SQL" <denis.gobo@.gmail.com> schrieb im Newsbeitrag
news:1134485484.301414.309720@.g43g2000cwa.googlegroups.com...
> Take a look at OPENROWSET if you don't want to create a linked server
> or OPENQUERY if you do create a linked server
> http://sqlservercode.blogspot.com/
>|||Go to security/Linked Servers
Right Click on Linked Servers--> New Linked Server
Use Microsoft OLE DB Provider for ODBC Drivers
Use the Connection String
DRIVER={MySQL ODBC 3.51
Driver};SERVER=myserver.com;DATABASE=database;USER
=user;PASSWORD=password;OPTION=3
And in Provider Options select:
level zero only
Non-transacted updates (something)
Allow InProcess
the linked thing will work as
mylinkedserver...tablename
http://sqlservercode.blogspot.com/|||Hey SQL, that worked perfectly, thanx a lot!
now I see the tables of "DatabaseXYZ" the "MyLinkedMySQLServer". But how can
I now create a job that imports all data from "Table1" of "DatabaseXYZ"
into "DatabaseABC" of my MSSQLServer?
Cheers, Jens
"SQL" <denis.gobo@.gmail.com> schrieb im Newsbeitrag
news:1134491324.820321.274410@.z14g2000cwz.googlegroups.com...
> Go to security/Linked Servers
> Right Click on Linked Servers--> New Linked Server
> Use Microsoft OLE DB Provider for ODBC Drivers
> Use the Connection String
> DRIVER={MySQL ODBC 3.51
> Driver};SERVER=myserver.com;DATABASE=database;USER
> =user;PASSWORD=password;OPTION=3
> And in Provider Options select:
> level zero only
> Non-transacted updates (something)
> Allow InProcess
> the linked thing will work as
> mylinkedserver...tablename
>
> http://sqlservercode.blogspot.com/
>|||Create a job and copy this code (obviously you will have to change it,
for example i don't know what your join condition is)
Then create a schedule and that should do it
insert into DatabaseABC.dbo.table1
select * from DatabaseABC.dbo.table1 ms left join
MyLinkedMySQLServer.DatabaseXYZ.Owner.Table1 my
on ms.id =my.id
where my.id is null
To Create a job go to Managment-->Sql server Agent-->Jobs
Right click --> New Job add a step paste the code
add the schedule and that should do it
http://sqlservercode.blogspot.com/

Automatical Import from MySQL-Table?

Hi All!
is it possible to create a job in the SQL Enterprise Manager that regularly
imports the data from a table of a MySQL-database?
how could I achieve this aim without having to program a lot?
any hints are highly appreciated!
cheers, jensTake a look at OPENROWSET if you don't want to create a linked server
or OPENQUERY if you do create a linked server
http://sqlservercode.blogspot.com/|||Hi SQL,
for me a so called "linked server" sounds better, how could I create a
linked server in the MS SQL ENterprise Manager?
Cheers, Jens
"SQL" <denis.gobo@.gmail.com> schrieb im Newsbeitrag
news:1134485484.301414.309720@.g43g2000cwa.googlegroups.com...
> Take a look at OPENROWSET if you don't want to create a linked server
> or OPENQUERY if you do create a linked server
> http://sqlservercode.blogspot.com/
>|||Go to security/Linked Servers
Right Click on Linked Servers--> New Linked Server
Use Microsoft OLE DB Provider for ODBC Drivers
Use the Connection String
DRIVER={MySQL ODBC 3.51
Driver};SERVER=myserver.com;DATABASE=database;USER
=user;PASSWORD=password;OPTION=3
And in Provider Options select:
level zero only
Non-transacted updates (something)
Allow InProcess
the linked thing will work as
mylinkedserver...tablename
http://sqlservercode.blogspot.com/|||Hey SQL, that worked perfectly, thanx a lot!
now I see the tables of "DatabaseXYZ" the "MyLinkedMySQLServer". But how can
I now create a job that imports all data from "Table1" of "DatabaseXYZ"
into "DatabaseABC" of my MSSQLServer?
Cheers, Jens
"SQL" <denis.gobo@.gmail.com> schrieb im Newsbeitrag
news:1134491324.820321.274410@.z14g2000cwz.googlegroups.com...
> Go to security/Linked Servers
> Right Click on Linked Servers--> New Linked Server
> Use Microsoft OLE DB Provider for ODBC Drivers
> Use the Connection String
> DRIVER={MySQL ODBC 3.51
> Driver};SERVER=myserver.com;DATABASE=database;USER
> =user;PASSWORD=password;OPTION=3
> And in Provider Options select:
> level zero only
> Non-transacted updates (something)
> Allow InProcess
> the linked thing will work as
> mylinkedserver...tablename
>
> http://sqlservercode.blogspot.com/
>|||Create a job and copy this code (obviously you will have to change it,
for example i don't know what your join condition is)
Then create a schedule and that should do it
insert into DatabaseABC.dbo.table1
select * from DatabaseABC.dbo.table1 ms left join
MyLinkedMySQLServer.DatabaseXYZ.Owner.Table1 my
on ms.id =my.id
where my.id is null
To Create a job go to Managment-->Sql server Agent-->Jobs
Right click --> New Job add a step paste the code
add the schedule and that should do it
http://sqlservercode.blogspot.com/

Tuesday, March 20, 2012

automatic sql server backup to networked machine

Hi,
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV
1. Make sure the SQL Server service is running on a domain account.
2. use UNC name for the designation file, eg:
\\MYSERVER\MYSHARE\MyFolder\MyFile.bak.
3. Make sure the account in #1 has read/write privileges on the share.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
Hi,
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV
|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV
|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV
|||Just so happens there is a community article on that very subject.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?id=555128
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.co m...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV

automatic sql server backup to networked machine

Hi,
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV1. Make sure the SQL Server service is running on a domain account.
2. use UNC name for the designation file, eg:
\\MYSERVER\MYSHARE\MyFolder\MyFile.bak.
3. Make sure the account in #1 has read/write privileges on the share.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
Hi,
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV|||Just so happens there is a community article on that very subject.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?id=555128
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV

automatic sql server backup to networked machine

Hi,
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV1. Make sure the SQL Server service is running on a domain account.
2. use UNC name for the designation file, eg:
\\MYSERVER\MYSHARE\MyFolder\MyFile.bak.
3. Make sure the account in #1 has read/write privileges on the share.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
Hi,
I am currently backing up to my machine on my localbox of the database
I want with enterprise manager.
I would like to do an automatic backup of my database running on a
schedule on a networked shared/remote machine?
How do I do that?
Thanks,
:DHRUV|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV|||Hi,
Enterprise manager will only show the local disks not the mapped drives.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak' with
init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
How to schedule
--
Enterprise Manager - Management -- SQL Agent -- Jobs -- Right click and
create new job. Give a name to the Job and in Job step mention this above
backup script and schedule the job to be executed based on requirement
Thanks
Hari
MCDBA
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV|||Just so happens there is a community article on that very subject.
HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?id=555128
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Dhruv" <dmalhotr2001@.yahoo.com> wrote in message
news:b6d0b0b.0407090855.43690994@.posting.google.com...
> Hi,
> I am currently backing up to my machine on my localbox of the database
> I want with enterprise manager.
> I would like to do an automatic backup of my database running on a
> schedule on a networked shared/remote machine?
> How do I do that?
> Thanks,
> :DHRUV

automatic script generation

Hi,
Is it possible to automate the 'All tasks - generate
sqlscripts' for tables and stored procs thru SQL
enterprise manager. I want the scripts to be automatically
generated monthly instead of doing manually - pls help.
regards,
bharathbharath,
Make and application that leverages the SQL-DMO libraries.
There are classes which can be used to generate scripts.
You may also use sp_OA* procs instead of a little app,
but I have done so, and I will advise you that
it is an exercise in tedium. Better to make a little app.
See "SQL-DMO" in Books Online.
James Hokes
"bharath" <anonymous@.discussions.microsoft.com> wrote in message
news:62ce01c3e625$7ecfec80$a001280a@.phx.gbl...
> Hi,
> Is it possible to automate the 'All tasks - generate
> sqlscripts' for tables and stored procs thru SQL
> enterprise manager. I want the scripts to be automatically
> generated monthly instead of doing manually - pls help.
> regards,
> bharath|||Hi,
JAmes.. thanks a lot.. can u give me an example so that i
understand better (regarding the apps)...
regards,
bharath
>--Original Message--
>bharath,
>Make and application that leverages the SQL-DMO libraries.
>There are classes which can be used to generate scripts.
>You may also use sp_OA* procs instead of a little app,
>but I have done so, and I will advise you that
>it is an exercise in tedium. Better to make a little app.
>See "SQL-DMO" in Books Online.
>James Hokes
>"bharath" <anonymous@.discussions.microsoft.com> wrote in
message
>news:62ce01c3e625$7ecfec80$a001280a@.phx.gbl...
>> Hi,
>> Is it possible to automate the 'All tasks - generate
>> sqlscripts' for tables and stored procs thru SQL
>> enterprise manager. I want the scripts to be
automatically
>> generated monthly instead of doing manually - pls help.
>> regards,
>> bharath
>
>.
>|||For sp_OA* variant, you can find a nice script at
http://support.microsoft.com/default.aspx?scid=kb;en-us;233392.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"bharath" <anonymous@.discussions.microsoft.com> wrote in message
news:658a01c3e629$6bd589b0$a401280a@.phx.gbl...
> Hi,
> JAmes.. thanks a lot.. can u give me an example so that i
> understand better (regarding the apps)...
> regards,
> bharath
> >--Original Message--
> >bharath,
> >
> >Make and application that leverages the SQL-DMO libraries.
> >There are classes which can be used to generate scripts.
> >
> >You may also use sp_OA* procs instead of a little app,
> >but I have done so, and I will advise you that
> >it is an exercise in tedium. Better to make a little app.
> >
> >See "SQL-DMO" in Books Online.
> >
> >James Hokes
> >
> >"bharath" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:62ce01c3e625$7ecfec80$a001280a@.phx.gbl...
> >> Hi,
> >>
> >> Is it possible to automate the 'All tasks - generate
> >> sqlscripts' for tables and stored procs thru SQL
> >> enterprise manager. I want the scripts to be
> automatically
> >> generated monthly instead of doing manually - pls help.
> >>
> >> regards,
> >> bharath
> >
> >
> >.
> >|||There is also a nice program to do this that is included in Ken Hendersons
new book "SQL Server Architecture"
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"bharath" <anonymous@.discussions.microsoft.com> wrote in message
news:62ce01c3e625$7ecfec80$a001280a@.phx.gbl...
> Hi,
> Is it possible to automate the 'All tasks - generate
> sqlscripts' for tables and stored procs thru SQL
> enterprise manager. I want the scripts to be automatically
> generated monthly instead of doing manually - pls help.
> regards,
> bharath|||bharath,
Well, off the top of my head, there's an SQL Server class, and first you
create an instance of that.
Then there's the databases collection off of the Server class, and you drill
down through the object hierarchy to get at the .Script method of the actual
object you're interested in.
There are separate collections for Tables, Views, StoredProcedures, etc.
Anyhow, there are a bizzillion flags you can set, such as whether or not to
include permissions, indexes, triggers, etc.
As I said, all the documentation you'll ever need is in Books Online, so I
can't really give you 'sample code'. I don't even know what language you're
planning to write it in.
James Hokes
"bharath" <anonymous@.discussions.microsoft.com> wrote in message
news:658a01c3e629$6bd589b0$a401280a@.phx.gbl...
> Hi,
> JAmes.. thanks a lot.. can u give me an example so that i
> understand better (regarding the apps)...
> regards,
> bharath
> >--Original Message--
> >bharath,
> >
> >Make and application that leverages the SQL-DMO libraries.
> >There are classes which can be used to generate scripts.
> >
> >You may also use sp_OA* procs instead of a little app,
> >but I have done so, and I will advise you that
> >it is an exercise in tedium. Better to make a little app.
> >
> >See "SQL-DMO" in Books Online.
> >
> >James Hokes
> >
> >"bharath" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:62ce01c3e625$7ecfec80$a001280a@.phx.gbl...
> >> Hi,
> >>
> >> Is it possible to automate the 'All tasks - generate
> >> sqlscripts' for tables and stored procs thru SQL
> >> enterprise manager. I want the scripts to be
> automatically
> >> generated monthly instead of doing manually - pls help.
> >>
> >> regards,
> >> bharath
> >
> >
> >.
> >|||Dejan,
That articles does not mention the sp_OA* variation on this concept.
Looks more like VBA in Microsoft Access.
James Hokes
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:#5FRhuj5DHA.488@.TK2MSFTNGP12.phx.gbl...
> For sp_OA* variant, you can find a nice script at
> http://support.microsoft.com/default.aspx?scid=kb;en-us;233392.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "bharath" <anonymous@.discussions.microsoft.com> wrote in message
> news:658a01c3e629$6bd589b0$a401280a@.phx.gbl...
> > Hi,
> >
> > JAmes.. thanks a lot.. can u give me an example so that i
> > understand better (regarding the apps)...
> >
> > regards,
> > bharath
> >
> > >--Original Message--
> > >bharath,
> > >
> > >Make and application that leverages the SQL-DMO libraries.
> > >There are classes which can be used to generate scripts.
> > >
> > >You may also use sp_OA* procs instead of a little app,
> > >but I have done so, and I will advise you that
> > >it is an exercise in tedium. Better to make a little app.
> > >
> > >See "SQL-DMO" in Books Online.
> > >
> > >James Hokes
> > >
> > >"bharath" <anonymous@.discussions.microsoft.com> wrote in
> > message
> > >news:62ce01c3e625$7ecfec80$a001280a@.phx.gbl...
> > >> Hi,
> > >>
> > >> Is it possible to automate the 'All tasks - generate
> > >> sqlscripts' for tables and stored procs thru SQL
> > >> enterprise manager. I want the scripts to be
> > automatically
> > >> generated monthly instead of doing manually - pls help.
> > >>
> > >> regards,
> > >> bharath
> > >
> > >
> > >.
> > >
>sql

automatic script generation

quote:

> Hi,
> Is it possible to automate the 'All tasks - generate
> sqlscripts' for tables and stored procs thru SQL
> enterprise manager. I want the scripts to be automatically
> generated monthly instead of doing manually - pls help.
> regards,
> bharath

|||Hi,
JAmes.. thanks a lot.. can u give me an example so that i
understand better (regarding the apps)...
regards,
bharath

quote:

>--Original Message--
>bharath,
>Make and application that leverages the SQL-DMO libraries.
>There are classes which can be used to generate scripts.
>You may also use sp_OA* procs instead of a little app,
>but I have done so, and I will advise you that
>it is an exercise in tedium. Better to make a little app.
>See "SQL-DMO" in Books Online.
>James Hokes
>"bharath" <anonymous@.discussions.microsoft.com> wrote in

message

quote:

>news:62ce01c3e625$7ecfec80$a001280a@.phx.gbl...
automatically[QUOTE]
>
>.
>

|||For sp_OA* variant, you can find a nice script at
http://support.microsoft.com/defaul...b;en-us;233392.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"bharath" <anonymous@.discussions.microsoft.com> wrote in message
news:658a01c3e629$6bd589b0$a401280a@.phx.gbl...[QUOTE]
> Hi,
> JAmes.. thanks a lot.. can u give me an example so that i
> understand better (regarding the apps)...
> regards,
> bharath
>
> message
> automatically|||There is also a nice program to do this that is included in Ken Hendersons
new book "SQL Server Architecture"
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"bharath" <anonymous@.discussions.microsoft.com> wrote in message
news:62ce01c3e625$7ecfec80$a001280a@.phx.gbl...

quote:

> Hi,
> Is it possible to automate the 'All tasks - generate
> sqlscripts' for tables and stored procs thru SQL
> enterprise manager. I want the scripts to be automatically
> generated monthly instead of doing manually - pls help.
> regards,
> bharath

|||bharath,
Well, off the top of my head, there's an SQL Server class, and first you
create an instance of that.
Then there's the databases collection off of the Server class, and you drill
down through the object hierarchy to get at the .Script method of the actual
object you're interested in.
There are separate collections for Tables, Views, StoredProcedures, etc.
Anyhow, there are a bizzillion flags you can set, such as whether or not to
include permissions, indexes, triggers, etc.
As I said, all the documentation you'll ever need is in Books Online, so I
can't really give you 'sample code'. I don't even know what language you're
planning to write it in.
James Hokes
"bharath" <anonymous@.discussions.microsoft.com> wrote in message
news:658a01c3e629$6bd589b0$a401280a@.phx.gbl...[QUOTE]
> Hi,
> JAmes.. thanks a lot.. can u give me an example so that i
> understand better (regarding the apps)...
> regards,
> bharath
>
> message
> automatically|||Dejan,
That articles does not mention the sp_OA* variation on this concept.
Looks more like VBA in Microsoft Access.
James Hokes
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:#5FRhuj5DHA.488@.TK2MSFTNGP12.phx.gbl...

quote:

> For sp_OA* variant, you can find a nice script at
> http://support.microsoft.com/defaul...b;en-us;233392.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "bharath" <anonymous@.discussions.microsoft.com> wrote in message
> news:658a01c3e629$6bd589b0$a401280a@.phx.gbl...
>

Automatic Recovery of SQL database?

Hi
I have problems writing to a SQL Server database called Event.
I get a lot of Timeout has expired.
In SQL Server Enterprise Manager, reading the log I can boserve the
following messages:
2005-02-26 15:48:03.79 spid11 Analysis of database 'Event' (7) is 41%
complete (approximately 6 more seconds)
2005-02-26 15:48:08.85 spid11 Analysis of database 'Event' (7) is 86%
complete (approximately 1 more seconds)
2005-02-26 15:48:09.06 spid11 Analysis of database 'Event' (7) is 100%
complete (approximately 0 more seconds)
2005-02-26 15:48:09.07 spid11 Recovery of database 'Event' (7) is 0%
complete (approximately 213 more seconds) (Phase 2 of 3).
2005-02-26 15:48:11.07 spid11 Recovery of database 'Event' (7) is 2%
complete (approximately 83 more seconds) (Phase 2 of 3).
2005-02-26 15:48:14.95 spid11 Recovery of database 'Event' (7) is 8%
complete (approximately 65 more seconds) (Phase 2 of 3).
2005-02-26 15:48:19.20 spid11 Recovery of database 'Event' (7) is 15%
complete (approximately 56 more seconds) (Phase 2 of 3).
2005-02-26 15:48:23.54 spid11 Recovery of database 'Event' (7) is 22%
complete (approximately 49 more seconds) (Phase 2 of 3).
2005-02-26 15:48:28.25 spid11 Recovery of database 'Event' (7) is 30%
complete (approximately 43 more seconds) (Phase 2 of 3).
2005-02-26 15:48:33.10 spid11 Recovery of database 'Event' (7) is 38%
complete (approximately 38 more seconds) (Phase 2 of 3).
2005-02-26 15:48:37.87 spid11 Recovery of database 'Event' (7) is 46%
complete (approximately 32 more seconds) (Phase 2 of 3).
2005-02-26 15:48:42.90 spid11 Recovery of database 'Event' (7) is 54%
complete (approximately 28 more seconds) (Phase 2 of 3).
2005-02-26 15:48:48.03 spid11 Recovery of database 'Event' (7) is 62%
complete (approximately 23 more seconds) (Phase 2 of 3).
2005-02-26 15:48:53.29 spid11 Recovery of database 'Event' (7) is 70%
complete (approximately 18 more seconds) (Phase 2 of 3).
2005-02-26 15:48:58.18 spid11 Recovery of database 'Event' (7) is 78%
complete (approximately 13 more seconds) (Phase 2 of 3).
2005-02-26 15:49:02.45 spid11 Recovery of database 'Event' (7) is 86%
complete (approximately 8 more seconds) (Phase 2 of 3).
2005-02-26 15:49:06.62 spid11 Recovery of database 'Event' (7) is 95%
complete (approximately 2 more seconds) (Phase 2 of 3).
2005-02-26 15:49:09.32 spid11 Recovery of database 'Event' (7) is 99%
complete (approximately 0 more seconds) (Phase 2 of 3).
2005-02-26 15:49:09.32 spid11 1108 transactions rolled forward in
database 'Event' (7).
2005-02-26 15:49:09.39 spid11 Recovery of database 'Event' (7) is 99%
complete (approximately 0 more seconds) (Phase 3 of 3).
2005-02-26 15:49:09.42 spid11 Recovery of database 'Event' (7) is 100%
complete (approximately 0 more seconds) (Phase 3 of 3).
2005-02-26 15:49:09.42 spid11 1 transactions rolled back in database
'Event' (7).
2005-02-26 15:49:09.42 spid11 Recovery is checkpointing database 'Event'
(7)
2005-02-26 15:49:09.70 spid3 Recovery complete.
2005-02-26 15:49:09.70 spid3 SQL global counter collection task is
created.
What does it mean? Is it something wrong with the database?
Also
2005-02-27 16:02:48.78 spid52 Error: 1204, Severity: 19, State: 1
2005-02-27 16:02:48.78 spid52 The SQL Server cannot obtain a LOCK
resource at this time. Rerun your statement when there are fewer active
What does this mean?
Does anyone now if it could be a serious problem with the database?
The database is 30 GB at the moment.
I appreciate comments and suggestions.
Kjell Arne JohansenMost probably you took down the server while there's a lot of activity on the machine, so SQL Server
had a lotto do when you startup SQL Server. Read more about it in Books Online "automatic recovery".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
news:39078C36-8B58-47A0-B920-8CEF2A031282@.microsoft.com...
> Hi
> I have problems writing to a SQL Server database called Event.
> I get a lot of Timeout has expired.
> In SQL Server Enterprise Manager, reading the log I can boserve the
> following messages:
> 2005-02-26 15:48:03.79 spid11 Analysis of database 'Event' (7) is 41%
> complete (approximately 6 more seconds)
> 2005-02-26 15:48:08.85 spid11 Analysis of database 'Event' (7) is 86%
> complete (approximately 1 more seconds)
> 2005-02-26 15:48:09.06 spid11 Analysis of database 'Event' (7) is 100%
> complete (approximately 0 more seconds)
> 2005-02-26 15:48:09.07 spid11 Recovery of database 'Event' (7) is 0%
> complete (approximately 213 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:11.07 spid11 Recovery of database 'Event' (7) is 2%
> complete (approximately 83 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:14.95 spid11 Recovery of database 'Event' (7) is 8%
> complete (approximately 65 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:19.20 spid11 Recovery of database 'Event' (7) is 15%
> complete (approximately 56 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:23.54 spid11 Recovery of database 'Event' (7) is 22%
> complete (approximately 49 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:28.25 spid11 Recovery of database 'Event' (7) is 30%
> complete (approximately 43 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:33.10 spid11 Recovery of database 'Event' (7) is 38%
> complete (approximately 38 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:37.87 spid11 Recovery of database 'Event' (7) is 46%
> complete (approximately 32 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:42.90 spid11 Recovery of database 'Event' (7) is 54%
> complete (approximately 28 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:48.03 spid11 Recovery of database 'Event' (7) is 62%
> complete (approximately 23 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:53.29 spid11 Recovery of database 'Event' (7) is 70%
> complete (approximately 18 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:58.18 spid11 Recovery of database 'Event' (7) is 78%
> complete (approximately 13 more seconds) (Phase 2 of 3).
> 2005-02-26 15:49:02.45 spid11 Recovery of database 'Event' (7) is 86%
> complete (approximately 8 more seconds) (Phase 2 of 3).
> 2005-02-26 15:49:06.62 spid11 Recovery of database 'Event' (7) is 95%
> complete (approximately 2 more seconds) (Phase 2 of 3).
> 2005-02-26 15:49:09.32 spid11 Recovery of database 'Event' (7) is 99%
> complete (approximately 0 more seconds) (Phase 2 of 3).
> 2005-02-26 15:49:09.32 spid11 1108 transactions rolled forward in
> database 'Event' (7).
> 2005-02-26 15:49:09.39 spid11 Recovery of database 'Event' (7) is 99%
> complete (approximately 0 more seconds) (Phase 3 of 3).
> 2005-02-26 15:49:09.42 spid11 Recovery of database 'Event' (7) is 100%
> complete (approximately 0 more seconds) (Phase 3 of 3).
> 2005-02-26 15:49:09.42 spid11 1 transactions rolled back in database
> 'Event' (7).
> 2005-02-26 15:49:09.42 spid11 Recovery is checkpointing database 'Event'
> (7)
> 2005-02-26 15:49:09.70 spid3 Recovery complete.
> 2005-02-26 15:49:09.70 spid3 SQL global counter collection task is
> created.
> What does it mean? Is it something wrong with the database?
> Also
> 2005-02-27 16:02:48.78 spid52 Error: 1204, Severity: 19, State: 1
> 2005-02-27 16:02:48.78 spid52 The SQL Server cannot obtain a LOCK
> resource at this time. Rerun your statement when there are fewer active
> What does this mean?
> Does anyone now if it could be a serious problem with the database?
> The database is 30 GB at the moment.
> I appreciate comments and suggestions.
> Kjell Arne Johansen
>|||Unless you've implemented a startup trace flag to block it, EVERY DATABASE
IS RECOVERED, automatically, UPON SQL SERVER STARTUP. That's part of the
ACID properties. If it didn't and you had a power outage as the cause of
the last reboot, all of your databases would be hosed. No, don't be
worried. Why you see this for this particular databases is because it is a
little larger than the others, which recover much more quickly.
Now, the last error indicates you may have too little memory on your server
for the activity that is been given it or you have throttled back SQL
Server's access to RAM.
Sincerely,
Anthony Thomas
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:39078C36-8B58-47A0-B920-8CEF2A031282@.microsoft.com...
Hi
I have problems writing to a SQL Server database called Event.
I get a lot of Timeout has expired.
In SQL Server Enterprise Manager, reading the log I can boserve the
following messages:
2005-02-26 15:48:03.79 spid11 Analysis of database 'Event' (7) is 41%
complete (approximately 6 more seconds)
2005-02-26 15:48:08.85 spid11 Analysis of database 'Event' (7) is 86%
complete (approximately 1 more seconds)
2005-02-26 15:48:09.06 spid11 Analysis of database 'Event' (7) is 100%
complete (approximately 0 more seconds)
2005-02-26 15:48:09.07 spid11 Recovery of database 'Event' (7) is 0%
complete (approximately 213 more seconds) (Phase 2 of 3).
2005-02-26 15:48:11.07 spid11 Recovery of database 'Event' (7) is 2%
complete (approximately 83 more seconds) (Phase 2 of 3).
2005-02-26 15:48:14.95 spid11 Recovery of database 'Event' (7) is 8%
complete (approximately 65 more seconds) (Phase 2 of 3).
2005-02-26 15:48:19.20 spid11 Recovery of database 'Event' (7) is 15%
complete (approximately 56 more seconds) (Phase 2 of 3).
2005-02-26 15:48:23.54 spid11 Recovery of database 'Event' (7) is 22%
complete (approximately 49 more seconds) (Phase 2 of 3).
2005-02-26 15:48:28.25 spid11 Recovery of database 'Event' (7) is 30%
complete (approximately 43 more seconds) (Phase 2 of 3).
2005-02-26 15:48:33.10 spid11 Recovery of database 'Event' (7) is 38%
complete (approximately 38 more seconds) (Phase 2 of 3).
2005-02-26 15:48:37.87 spid11 Recovery of database 'Event' (7) is 46%
complete (approximately 32 more seconds) (Phase 2 of 3).
2005-02-26 15:48:42.90 spid11 Recovery of database 'Event' (7) is 54%
complete (approximately 28 more seconds) (Phase 2 of 3).
2005-02-26 15:48:48.03 spid11 Recovery of database 'Event' (7) is 62%
complete (approximately 23 more seconds) (Phase 2 of 3).
2005-02-26 15:48:53.29 spid11 Recovery of database 'Event' (7) is 70%
complete (approximately 18 more seconds) (Phase 2 of 3).
2005-02-26 15:48:58.18 spid11 Recovery of database 'Event' (7) is 78%
complete (approximately 13 more seconds) (Phase 2 of 3).
2005-02-26 15:49:02.45 spid11 Recovery of database 'Event' (7) is 86%
complete (approximately 8 more seconds) (Phase 2 of 3).
2005-02-26 15:49:06.62 spid11 Recovery of database 'Event' (7) is 95%
complete (approximately 2 more seconds) (Phase 2 of 3).
2005-02-26 15:49:09.32 spid11 Recovery of database 'Event' (7) is 99%
complete (approximately 0 more seconds) (Phase 2 of 3).
2005-02-26 15:49:09.32 spid11 1108 transactions rolled forward in
database 'Event' (7).
2005-02-26 15:49:09.39 spid11 Recovery of database 'Event' (7) is 99%
complete (approximately 0 more seconds) (Phase 3 of 3).
2005-02-26 15:49:09.42 spid11 Recovery of database 'Event' (7) is 100%
complete (approximately 0 more seconds) (Phase 3 of 3).
2005-02-26 15:49:09.42 spid11 1 transactions rolled back in database
'Event' (7).
2005-02-26 15:49:09.42 spid11 Recovery is checkpointing database 'Event'
(7)
2005-02-26 15:49:09.70 spid3 Recovery complete.
2005-02-26 15:49:09.70 spid3 SQL global counter collection task is
created.
What does it mean? Is it something wrong with the database?
Also
2005-02-27 16:02:48.78 spid52 Error: 1204, Severity: 19, State: 1
2005-02-27 16:02:48.78 spid52 The SQL Server cannot obtain a LOCK
resource at this time. Rerun your statement when there are fewer active
What does this mean?
Does anyone now if it could be a serious problem with the database?
The database is 30 GB at the moment.
I appreciate comments and suggestions.
Kjell Arne Johansen|||Thank You.
Does it exist rules for how much memory that should be be available for SQL
Server?
This is an alarms and events database and at the moment there is a lot of
events from the system. About 2000 - 3000 records to the database each
minute with text, date and time. I will assume that each event is about 200
byte before making the SQL.
The SQL Server and database is on the same machine but the application
writing to the database is on another machine. (Process network and
Administrative network).
When the system is finished and tuned there will not be more than 50 events
-and far less alarms- each minute.
Regards
Kjell Arne Johansen
"Anthony Thomas" wrote:
> Unless you've implemented a startup trace flag to block it, EVERY DATABASE
> IS RECOVERED, automatically, UPON SQL SERVER STARTUP. That's part of the
> ACID properties. If it didn't and you had a power outage as the cause of
> the last reboot, all of your databases would be hosed. No, don't be
> worried. Why you see this for this particular databases is because it is a
> little larger than the others, which recover much more quickly.
> Now, the last error indicates you may have too little memory on your server
> for the activity that is been given it or you have throttled back SQL
> Server's access to RAM.
> Sincerely,
>
> Anthony Thomas
>|||Well it all depends on how much activity there is on your server, by default
SQL Server will use memory dynamically. That means that it will allocate and
deallocate memory on the fly based on the needs.
I would suggest that you monitor the memory by using performance monitor.
Start by looking at the following counters:
SQLServer:Buffer Manager Buffer cache hit ratio
SQLServer:Buffer Manager Free pages
SQLServer:Buffer Manager Page life expectancy
SQLServer:Buffer Manager Target pages
There is more information about how you can monitor the memory usage of your
SQL Server in the book called:
Microsoft SQL Server 2000
Performance Tuning
Technical Reference
ISBN: 0-7356-1270-6
Simon
This posting is provided "as is" with no warranties and confers no rights.
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:B3B453C9-7964-4DF4-A0FC-C61CF116F9FD@.microsoft.com...
> Thank You.
> Does it exist rules for how much memory that should be be available for
> SQL
> Server?
> This is an alarms and events database and at the moment there is a lot of
> events from the system. About 2000 - 3000 records to the database each
> minute with text, date and time. I will assume that each event is about
> 200
> byte before making the SQL.
> The SQL Server and database is on the same machine but the application
> writing to the database is on another machine. (Process network and
> Administrative network).
> When the system is finished and tuned there will not be more than 50
> events
> -and far less alarms- each minute.
> Regards
> Kjell Arne Johansen
> "Anthony Thomas" wrote:
>> Unless you've implemented a startup trace flag to block it, EVERY
>> DATABASE
>> IS RECOVERED, automatically, UPON SQL SERVER STARTUP. That's part of the
>> ACID properties. If it didn't and you had a power outage as the cause of
>> the last reboot, all of your databases would be hosed. No, don't be
>> worried. Why you see this for this particular databases is because it is
>> a
>> little larger than the others, which recover much more quickly.
>> Now, the last error indicates you may have too little memory on your
>> server
>> for the activity that is been given it or you have throttled back SQL
>> Server's access to RAM.
>> Sincerely,
>>
>> Anthony Thomas
>>
>

Automatic Recovery of SQL database?

Hi
I have problems writing to a SQL Server database called Event.
I get a lot of Timeout has expired.
In SQL Server Enterprise Manager, reading the log I can boserve the
following messages:
2005-02-26 15:48:03.79 spid11 Analysis of database 'Event' (7) is 41%
complete (approximately 6 more seconds)
2005-02-26 15:48:08.85 spid11 Analysis of database 'Event' (7) is 86%
complete (approximately 1 more seconds)
2005-02-26 15:48:09.06 spid11 Analysis of database 'Event' (7) is 100%
complete (approximately 0 more seconds)
2005-02-26 15:48:09.07 spid11 Recovery of database 'Event' (7) is 0%
complete (approximately 213 more seconds) (Phase 2 of 3).
2005-02-26 15:48:11.07 spid11 Recovery of database 'Event' (7) is 2%
complete (approximately 83 more seconds) (Phase 2 of 3).
2005-02-26 15:48:14.95 spid11 Recovery of database 'Event' (7) is 8%
complete (approximately 65 more seconds) (Phase 2 of 3).
2005-02-26 15:48:19.20 spid11 Recovery of database 'Event' (7) is 15%
complete (approximately 56 more seconds) (Phase 2 of 3).
2005-02-26 15:48:23.54 spid11 Recovery of database 'Event' (7) is 22%
complete (approximately 49 more seconds) (Phase 2 of 3).
2005-02-26 15:48:28.25 spid11 Recovery of database 'Event' (7) is 30%
complete (approximately 43 more seconds) (Phase 2 of 3).
2005-02-26 15:48:33.10 spid11 Recovery of database 'Event' (7) is 38%
complete (approximately 38 more seconds) (Phase 2 of 3).
2005-02-26 15:48:37.87 spid11 Recovery of database 'Event' (7) is 46%
complete (approximately 32 more seconds) (Phase 2 of 3).
2005-02-26 15:48:42.90 spid11 Recovery of database 'Event' (7) is 54%
complete (approximately 28 more seconds) (Phase 2 of 3).
2005-02-26 15:48:48.03 spid11 Recovery of database 'Event' (7) is 62%
complete (approximately 23 more seconds) (Phase 2 of 3).
2005-02-26 15:48:53.29 spid11 Recovery of database 'Event' (7) is 70%
complete (approximately 18 more seconds) (Phase 2 of 3).
2005-02-26 15:48:58.18 spid11 Recovery of database 'Event' (7) is 78%
complete (approximately 13 more seconds) (Phase 2 of 3).
2005-02-26 15:49:02.45 spid11 Recovery of database 'Event' (7) is 86%
complete (approximately 8 more seconds) (Phase 2 of 3).
2005-02-26 15:49:06.62 spid11 Recovery of database 'Event' (7) is 95%
complete (approximately 2 more seconds) (Phase 2 of 3).
2005-02-26 15:49:09.32 spid11 Recovery of database 'Event' (7) is 99%
complete (approximately 0 more seconds) (Phase 2 of 3).
2005-02-26 15:49:09.32 spid11 1108 transactions rolled forward in
database 'Event' (7).
2005-02-26 15:49:09.39 spid11 Recovery of database 'Event' (7) is 99%
complete (approximately 0 more seconds) (Phase 3 of 3).
2005-02-26 15:49:09.42 spid11 Recovery of database 'Event' (7) is 100%
complete (approximately 0 more seconds) (Phase 3 of 3).
2005-02-26 15:49:09.42 spid11 1 transactions rolled back in database
'Event' (7).
2005-02-26 15:49:09.42 spid11 Recovery is checkpointing database 'Event'
(7)
2005-02-26 15:49:09.70 spid3 Recovery complete.
2005-02-26 15:49:09.70 spid3 SQL global counter collection task is
created.
What does it mean? Is it something wrong with the database?
Also
2005-02-27 16:02:48.78 spid52 Error: 1204, Severity: 19, State: 1
2005-02-27 16:02:48.78 spid52 The SQL Server cannot obtain a LOCK
resource at this time. Rerun your statement when there are fewer active
What does this mean?
Does anyone now if it could be a serious problem with the database?
The database is 30 GB at the moment.
I appreciate comments and suggestions.
Kjell Arne Johansen
Most probably you took down the server while there's a lot of activity on the machine, so SQL Server
had a lotto do when you startup SQL Server. Read more about it in Books Online "automatic recovery".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in message
news:39078C36-8B58-47A0-B920-8CEF2A031282@.microsoft.com...
> Hi
> I have problems writing to a SQL Server database called Event.
> I get a lot of Timeout has expired.
> In SQL Server Enterprise Manager, reading the log I can boserve the
> following messages:
> 2005-02-26 15:48:03.79 spid11 Analysis of database 'Event' (7) is 41%
> complete (approximately 6 more seconds)
> 2005-02-26 15:48:08.85 spid11 Analysis of database 'Event' (7) is 86%
> complete (approximately 1 more seconds)
> 2005-02-26 15:48:09.06 spid11 Analysis of database 'Event' (7) is 100%
> complete (approximately 0 more seconds)
> 2005-02-26 15:48:09.07 spid11 Recovery of database 'Event' (7) is 0%
> complete (approximately 213 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:11.07 spid11 Recovery of database 'Event' (7) is 2%
> complete (approximately 83 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:14.95 spid11 Recovery of database 'Event' (7) is 8%
> complete (approximately 65 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:19.20 spid11 Recovery of database 'Event' (7) is 15%
> complete (approximately 56 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:23.54 spid11 Recovery of database 'Event' (7) is 22%
> complete (approximately 49 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:28.25 spid11 Recovery of database 'Event' (7) is 30%
> complete (approximately 43 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:33.10 spid11 Recovery of database 'Event' (7) is 38%
> complete (approximately 38 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:37.87 spid11 Recovery of database 'Event' (7) is 46%
> complete (approximately 32 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:42.90 spid11 Recovery of database 'Event' (7) is 54%
> complete (approximately 28 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:48.03 spid11 Recovery of database 'Event' (7) is 62%
> complete (approximately 23 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:53.29 spid11 Recovery of database 'Event' (7) is 70%
> complete (approximately 18 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:58.18 spid11 Recovery of database 'Event' (7) is 78%
> complete (approximately 13 more seconds) (Phase 2 of 3).
> 2005-02-26 15:49:02.45 spid11 Recovery of database 'Event' (7) is 86%
> complete (approximately 8 more seconds) (Phase 2 of 3).
> 2005-02-26 15:49:06.62 spid11 Recovery of database 'Event' (7) is 95%
> complete (approximately 2 more seconds) (Phase 2 of 3).
> 2005-02-26 15:49:09.32 spid11 Recovery of database 'Event' (7) is 99%
> complete (approximately 0 more seconds) (Phase 2 of 3).
> 2005-02-26 15:49:09.32 spid11 1108 transactions rolled forward in
> database 'Event' (7).
> 2005-02-26 15:49:09.39 spid11 Recovery of database 'Event' (7) is 99%
> complete (approximately 0 more seconds) (Phase 3 of 3).
> 2005-02-26 15:49:09.42 spid11 Recovery of database 'Event' (7) is 100%
> complete (approximately 0 more seconds) (Phase 3 of 3).
> 2005-02-26 15:49:09.42 spid11 1 transactions rolled back in database
> 'Event' (7).
> 2005-02-26 15:49:09.42 spid11 Recovery is checkpointing database 'Event'
> (7)
> 2005-02-26 15:49:09.70 spid3 Recovery complete.
> 2005-02-26 15:49:09.70 spid3 SQL global counter collection task is
> created.
> What does it mean? Is it something wrong with the database?
> Also
> 2005-02-27 16:02:48.78 spid52 Error: 1204, Severity: 19, State: 1
> 2005-02-27 16:02:48.78 spid52 The SQL Server cannot obtain a LOCK
> resource at this time. Rerun your statement when there are fewer active
> What does this mean?
> Does anyone now if it could be a serious problem with the database?
> The database is 30 GB at the moment.
> I appreciate comments and suggestions.
> Kjell Arne Johansen
>
|||Unless you've implemented a startup trace flag to block it, EVERY DATABASE
IS RECOVERED, automatically, UPON SQL SERVER STARTUP. That's part of the
ACID properties. If it didn't and you had a power outage as the cause of
the last reboot, all of your databases would be hosed. No, don't be
worried. Why you see this for this particular databases is because it is a
little larger than the others, which recover much more quickly.
Now, the last error indicates you may have too little memory on your server
for the activity that is been given it or you have throttled back SQL
Server's access to RAM.
Sincerely,
Anthony Thomas

"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:39078C36-8B58-47A0-B920-8CEF2A031282@.microsoft.com...
Hi
I have problems writing to a SQL Server database called Event.
I get a lot of Timeout has expired.
In SQL Server Enterprise Manager, reading the log I can boserve the
following messages:
2005-02-26 15:48:03.79 spid11 Analysis of database 'Event' (7) is 41%
complete (approximately 6 more seconds)
2005-02-26 15:48:08.85 spid11 Analysis of database 'Event' (7) is 86%
complete (approximately 1 more seconds)
2005-02-26 15:48:09.06 spid11 Analysis of database 'Event' (7) is 100%
complete (approximately 0 more seconds)
2005-02-26 15:48:09.07 spid11 Recovery of database 'Event' (7) is 0%
complete (approximately 213 more seconds) (Phase 2 of 3).
2005-02-26 15:48:11.07 spid11 Recovery of database 'Event' (7) is 2%
complete (approximately 83 more seconds) (Phase 2 of 3).
2005-02-26 15:48:14.95 spid11 Recovery of database 'Event' (7) is 8%
complete (approximately 65 more seconds) (Phase 2 of 3).
2005-02-26 15:48:19.20 spid11 Recovery of database 'Event' (7) is 15%
complete (approximately 56 more seconds) (Phase 2 of 3).
2005-02-26 15:48:23.54 spid11 Recovery of database 'Event' (7) is 22%
complete (approximately 49 more seconds) (Phase 2 of 3).
2005-02-26 15:48:28.25 spid11 Recovery of database 'Event' (7) is 30%
complete (approximately 43 more seconds) (Phase 2 of 3).
2005-02-26 15:48:33.10 spid11 Recovery of database 'Event' (7) is 38%
complete (approximately 38 more seconds) (Phase 2 of 3).
2005-02-26 15:48:37.87 spid11 Recovery of database 'Event' (7) is 46%
complete (approximately 32 more seconds) (Phase 2 of 3).
2005-02-26 15:48:42.90 spid11 Recovery of database 'Event' (7) is 54%
complete (approximately 28 more seconds) (Phase 2 of 3).
2005-02-26 15:48:48.03 spid11 Recovery of database 'Event' (7) is 62%
complete (approximately 23 more seconds) (Phase 2 of 3).
2005-02-26 15:48:53.29 spid11 Recovery of database 'Event' (7) is 70%
complete (approximately 18 more seconds) (Phase 2 of 3).
2005-02-26 15:48:58.18 spid11 Recovery of database 'Event' (7) is 78%
complete (approximately 13 more seconds) (Phase 2 of 3).
2005-02-26 15:49:02.45 spid11 Recovery of database 'Event' (7) is 86%
complete (approximately 8 more seconds) (Phase 2 of 3).
2005-02-26 15:49:06.62 spid11 Recovery of database 'Event' (7) is 95%
complete (approximately 2 more seconds) (Phase 2 of 3).
2005-02-26 15:49:09.32 spid11 Recovery of database 'Event' (7) is 99%
complete (approximately 0 more seconds) (Phase 2 of 3).
2005-02-26 15:49:09.32 spid11 1108 transactions rolled forward in
database 'Event' (7).
2005-02-26 15:49:09.39 spid11 Recovery of database 'Event' (7) is 99%
complete (approximately 0 more seconds) (Phase 3 of 3).
2005-02-26 15:49:09.42 spid11 Recovery of database 'Event' (7) is 100%
complete (approximately 0 more seconds) (Phase 3 of 3).
2005-02-26 15:49:09.42 spid11 1 transactions rolled back in database
'Event' (7).
2005-02-26 15:49:09.42 spid11 Recovery is checkpointing database 'Event'
(7)
2005-02-26 15:49:09.70 spid3 Recovery complete.
2005-02-26 15:49:09.70 spid3 SQL global counter collection task is
created.
What does it mean? Is it something wrong with the database?
Also
2005-02-27 16:02:48.78 spid52 Error: 1204, Severity: 19, State: 1
2005-02-27 16:02:48.78 spid52 The SQL Server cannot obtain a LOCK
resource at this time. Rerun your statement when there are fewer active
What does this mean?
Does anyone now if it could be a serious problem with the database?
The database is 30 GB at the moment.
I appreciate comments and suggestions.
Kjell Arne Johansen
|||Thank You.
Does it exist rules for how much memory that should be be available for SQL
Server?
This is an alarms and events database and at the moment there is a lot of
events from the system. About 2000 - 3000 records to the database each
minute with text, date and time. I will assume that each event is about 200
byte before making the SQL.
The SQL Server and database is on the same machine but the application
writing to the database is on another machine. (Process network and
Administrative network).
When the system is finished and tuned there will not be more than 50 events
-and far less alarms- each minute.
Regards
Kjell Arne Johansen
"Anthony Thomas" wrote:

> Unless you've implemented a startup trace flag to block it, EVERY DATABASE
> IS RECOVERED, automatically, UPON SQL SERVER STARTUP. That's part of the
> ACID properties. If it didn't and you had a power outage as the cause of
> the last reboot, all of your databases would be hosed. No, don't be
> worried. Why you see this for this particular databases is because it is a
> little larger than the others, which recover much more quickly.
> Now, the last error indicates you may have too little memory on your server
> for the activity that is been given it or you have throttled back SQL
> Server's access to RAM.
> Sincerely,
>
> Anthony Thomas
>
|||Well it all depends on how much activity there is on your server, by default
SQL Server will use memory dynamically. That means that it will allocate and
deallocate memory on the fly based on the needs.
I would suggest that you monitor the memory by using performance monitor.
Start by looking at the following counters:
SQLServer:Buffer Manager Buffer cache hit ratio
SQLServer:Buffer Manager Free pages
SQLServer:Buffer Manager Page life expectancy
SQLServer:Buffer Manager Target pages
There is more information about how you can monitor the memory usage of your
SQL Server in the book called:
Microsoft SQL Server 2000
Performance Tuning
Technical Reference
ISBN: 0-7356-1270-6
Simon
This posting is provided "as is" with no warranties and confers no rights.
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:B3B453C9-7964-4DF4-A0FC-C61CF116F9FD@.microsoft.com...
> Thank You.
> Does it exist rules for how much memory that should be be available for
> SQL
> Server?
> This is an alarms and events database and at the moment there is a lot of
> events from the system. About 2000 - 3000 records to the database each
> minute with text, date and time. I will assume that each event is about
> 200
> byte before making the SQL.
> The SQL Server and database is on the same machine but the application
> writing to the database is on another machine. (Process network and
> Administrative network).
> When the system is finished and tuned there will not be more than 50
> events
> -and far less alarms- each minute.
> Regards
> Kjell Arne Johansen
> "Anthony Thomas" wrote:
>

Automatic Recovery of SQL database?

Hi
I have problems writing to a SQL Server database called Event.
I get a lot of Timeout has expired.
In SQL Server Enterprise Manager, reading the log I can boserve the
following messages:
2005-02-26 15:48:03.79 spid11 Analysis of database 'Event' (7) is 41%
complete (approximately 6 more seconds)
2005-02-26 15:48:08.85 spid11 Analysis of database 'Event' (7) is 86%
complete (approximately 1 more seconds)
2005-02-26 15:48:09.06 spid11 Analysis of database 'Event' (7) is 100%
complete (approximately 0 more seconds)
2005-02-26 15:48:09.07 spid11 Recovery of database 'Event' (7) is 0%
complete (approximately 213 more seconds) (Phase 2 of 3).
2005-02-26 15:48:11.07 spid11 Recovery of database 'Event' (7) is 2%
complete (approximately 83 more seconds) (Phase 2 of 3).
2005-02-26 15:48:14.95 spid11 Recovery of database 'Event' (7) is 8%
complete (approximately 65 more seconds) (Phase 2 of 3).
2005-02-26 15:48:19.20 spid11 Recovery of database 'Event' (7) is 15%
complete (approximately 56 more seconds) (Phase 2 of 3).
2005-02-26 15:48:23.54 spid11 Recovery of database 'Event' (7) is 22%
complete (approximately 49 more seconds) (Phase 2 of 3).
2005-02-26 15:48:28.25 spid11 Recovery of database 'Event' (7) is 30%
complete (approximately 43 more seconds) (Phase 2 of 3).
2005-02-26 15:48:33.10 spid11 Recovery of database 'Event' (7) is 38%
complete (approximately 38 more seconds) (Phase 2 of 3).
2005-02-26 15:48:37.87 spid11 Recovery of database 'Event' (7) is 46%
complete (approximately 32 more seconds) (Phase 2 of 3).
2005-02-26 15:48:42.90 spid11 Recovery of database 'Event' (7) is 54%
complete (approximately 28 more seconds) (Phase 2 of 3).
2005-02-26 15:48:48.03 spid11 Recovery of database 'Event' (7) is 62%
complete (approximately 23 more seconds) (Phase 2 of 3).
2005-02-26 15:48:53.29 spid11 Recovery of database 'Event' (7) is 70%
complete (approximately 18 more seconds) (Phase 2 of 3).
2005-02-26 15:48:58.18 spid11 Recovery of database 'Event' (7) is 78%
complete (approximately 13 more seconds) (Phase 2 of 3).
2005-02-26 15:49:02.45 spid11 Recovery of database 'Event' (7) is 86%
complete (approximately 8 more seconds) (Phase 2 of 3).
2005-02-26 15:49:06.62 spid11 Recovery of database 'Event' (7) is 95%
complete (approximately 2 more seconds) (Phase 2 of 3).
2005-02-26 15:49:09.32 spid11 Recovery of database 'Event' (7) is 99%
complete (approximately 0 more seconds) (Phase 2 of 3).
2005-02-26 15:49:09.32 spid11 1108 transactions rolled forward in
database 'Event' (7).
2005-02-26 15:49:09.39 spid11 Recovery of database 'Event' (7) is 99%
complete (approximately 0 more seconds) (Phase 3 of 3).
2005-02-26 15:49:09.42 spid11 Recovery of database 'Event' (7) is 100%
complete (approximately 0 more seconds) (Phase 3 of 3).
2005-02-26 15:49:09.42 spid11 1 transactions rolled back in database
'Event' (7).
2005-02-26 15:49:09.42 spid11 Recovery is checkpointing database 'Event'
(7)
2005-02-26 15:49:09.70 spid3 Recovery complete.
2005-02-26 15:49:09.70 spid3 SQL global counter collection task is
created.
What does it mean? Is it something wrong with the database?
Also
2005-02-27 16:02:48.78 spid52 Error: 1204, Severity: 19, State: 1
2005-02-27 16:02:48.78 spid52 The SQL Server cannot obtain a LOCK
resource at this time. Rerun your statement when there are fewer active
What does this mean?
Does anyone now if it could be a serious problem with the database?
The database is 30 GB at the moment.
I appreciate comments and suggestions.
Kjell Arne JohansenMost probably you took down the server while there's a lot of activity on th
e machine, so SQL Server
had a lotto do when you startup SQL Server. Read more about it in Books Onli
ne "automatic recovery".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message
news:39078C36-8B58-47A0-B920-8CEF2A031282@.microsoft.com...
> Hi
> I have problems writing to a SQL Server database called Event.
> I get a lot of Timeout has expired.
> In SQL Server Enterprise Manager, reading the log I can boserve the
> following messages:
> 2005-02-26 15:48:03.79 spid11 Analysis of database 'Event' (7) is 41%
> complete (approximately 6 more seconds)
> 2005-02-26 15:48:08.85 spid11 Analysis of database 'Event' (7) is 86%
> complete (approximately 1 more seconds)
> 2005-02-26 15:48:09.06 spid11 Analysis of database 'Event' (7) is 100%
> complete (approximately 0 more seconds)
> 2005-02-26 15:48:09.07 spid11 Recovery of database 'Event' (7) is 0%
> complete (approximately 213 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:11.07 spid11 Recovery of database 'Event' (7) is 2%
> complete (approximately 83 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:14.95 spid11 Recovery of database 'Event' (7) is 8%
> complete (approximately 65 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:19.20 spid11 Recovery of database 'Event' (7) is 15%
> complete (approximately 56 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:23.54 spid11 Recovery of database 'Event' (7) is 22%
> complete (approximately 49 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:28.25 spid11 Recovery of database 'Event' (7) is 30%
> complete (approximately 43 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:33.10 spid11 Recovery of database 'Event' (7) is 38%
> complete (approximately 38 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:37.87 spid11 Recovery of database 'Event' (7) is 46%
> complete (approximately 32 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:42.90 spid11 Recovery of database 'Event' (7) is 54%
> complete (approximately 28 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:48.03 spid11 Recovery of database 'Event' (7) is 62%
> complete (approximately 23 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:53.29 spid11 Recovery of database 'Event' (7) is 70%
> complete (approximately 18 more seconds) (Phase 2 of 3).
> 2005-02-26 15:48:58.18 spid11 Recovery of database 'Event' (7) is 78%
> complete (approximately 13 more seconds) (Phase 2 of 3).
> 2005-02-26 15:49:02.45 spid11 Recovery of database 'Event' (7) is 86%
> complete (approximately 8 more seconds) (Phase 2 of 3).
> 2005-02-26 15:49:06.62 spid11 Recovery of database 'Event' (7) is 95%
> complete (approximately 2 more seconds) (Phase 2 of 3).
> 2005-02-26 15:49:09.32 spid11 Recovery of database 'Event' (7) is 99%
> complete (approximately 0 more seconds) (Phase 2 of 3).
> 2005-02-26 15:49:09.32 spid11 1108 transactions rolled forward in
> database 'Event' (7).
> 2005-02-26 15:49:09.39 spid11 Recovery of database 'Event' (7) is 99%
> complete (approximately 0 more seconds) (Phase 3 of 3).
> 2005-02-26 15:49:09.42 spid11 Recovery of database 'Event' (7) is 100%
> complete (approximately 0 more seconds) (Phase 3 of 3).
> 2005-02-26 15:49:09.42 spid11 1 transactions rolled back in database
> 'Event' (7).
> 2005-02-26 15:49:09.42 spid11 Recovery is checkpointing database 'Event
'
> (7)
> 2005-02-26 15:49:09.70 spid3 Recovery complete.
> 2005-02-26 15:49:09.70 spid3 SQL global counter collection task is
> created.
> What does it mean? Is it something wrong with the database?
> Also
> 2005-02-27 16:02:48.78 spid52 Error: 1204, Severity: 19, State: 1
> 2005-02-27 16:02:48.78 spid52 The SQL Server cannot obtain a LOCK
> resource at this time. Rerun your statement when there are fewer active
> What does this mean?
> Does anyone now if it could be a serious problem with the database?
> The database is 30 GB at the moment.
> I appreciate comments and suggestions.
> Kjell Arne Johansen
>|||Unless you've implemented a startup trace flag to block it, EVERY DATABASE
IS RECOVERED, automatically, UPON SQL SERVER STARTUP. That's part of the
ACID properties. If it didn't and you had a power outage as the cause of
the last reboot, all of your databases would be hosed. No, don't be
worried. Why you see this for this particular databases is because it is a
little larger than the others, which recover much more quickly.
Now, the last error indicates you may have too little memory on your server
for the activity that is been given it or you have throttled back SQL
Server's access to RAM.
Sincerely,
Anthony Thomas
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:39078C36-8B58-47A0-B920-8CEF2A031282@.microsoft.com...
Hi
I have problems writing to a SQL Server database called Event.
I get a lot of Timeout has expired.
In SQL Server Enterprise Manager, reading the log I can boserve the
following messages:
2005-02-26 15:48:03.79 spid11 Analysis of database 'Event' (7) is 41%
complete (approximately 6 more seconds)
2005-02-26 15:48:08.85 spid11 Analysis of database 'Event' (7) is 86%
complete (approximately 1 more seconds)
2005-02-26 15:48:09.06 spid11 Analysis of database 'Event' (7) is 100%
complete (approximately 0 more seconds)
2005-02-26 15:48:09.07 spid11 Recovery of database 'Event' (7) is 0%
complete (approximately 213 more seconds) (Phase 2 of 3).
2005-02-26 15:48:11.07 spid11 Recovery of database 'Event' (7) is 2%
complete (approximately 83 more seconds) (Phase 2 of 3).
2005-02-26 15:48:14.95 spid11 Recovery of database 'Event' (7) is 8%
complete (approximately 65 more seconds) (Phase 2 of 3).
2005-02-26 15:48:19.20 spid11 Recovery of database 'Event' (7) is 15%
complete (approximately 56 more seconds) (Phase 2 of 3).
2005-02-26 15:48:23.54 spid11 Recovery of database 'Event' (7) is 22%
complete (approximately 49 more seconds) (Phase 2 of 3).
2005-02-26 15:48:28.25 spid11 Recovery of database 'Event' (7) is 30%
complete (approximately 43 more seconds) (Phase 2 of 3).
2005-02-26 15:48:33.10 spid11 Recovery of database 'Event' (7) is 38%
complete (approximately 38 more seconds) (Phase 2 of 3).
2005-02-26 15:48:37.87 spid11 Recovery of database 'Event' (7) is 46%
complete (approximately 32 more seconds) (Phase 2 of 3).
2005-02-26 15:48:42.90 spid11 Recovery of database 'Event' (7) is 54%
complete (approximately 28 more seconds) (Phase 2 of 3).
2005-02-26 15:48:48.03 spid11 Recovery of database 'Event' (7) is 62%
complete (approximately 23 more seconds) (Phase 2 of 3).
2005-02-26 15:48:53.29 spid11 Recovery of database 'Event' (7) is 70%
complete (approximately 18 more seconds) (Phase 2 of 3).
2005-02-26 15:48:58.18 spid11 Recovery of database 'Event' (7) is 78%
complete (approximately 13 more seconds) (Phase 2 of 3).
2005-02-26 15:49:02.45 spid11 Recovery of database 'Event' (7) is 86%
complete (approximately 8 more seconds) (Phase 2 of 3).
2005-02-26 15:49:06.62 spid11 Recovery of database 'Event' (7) is 95%
complete (approximately 2 more seconds) (Phase 2 of 3).
2005-02-26 15:49:09.32 spid11 Recovery of database 'Event' (7) is 99%
complete (approximately 0 more seconds) (Phase 2 of 3).
2005-02-26 15:49:09.32 spid11 1108 transactions rolled forward in
database 'Event' (7).
2005-02-26 15:49:09.39 spid11 Recovery of database 'Event' (7) is 99%
complete (approximately 0 more seconds) (Phase 3 of 3).
2005-02-26 15:49:09.42 spid11 Recovery of database 'Event' (7) is 100%
complete (approximately 0 more seconds) (Phase 3 of 3).
2005-02-26 15:49:09.42 spid11 1 transactions rolled back in database
'Event' (7).
2005-02-26 15:49:09.42 spid11 Recovery is checkpointing database 'Event'
(7)
2005-02-26 15:49:09.70 spid3 Recovery complete.
2005-02-26 15:49:09.70 spid3 SQL global counter collection task is
created.
What does it mean? Is it something wrong with the database?
Also
2005-02-27 16:02:48.78 spid52 Error: 1204, Severity: 19, State: 1
2005-02-27 16:02:48.78 spid52 The SQL Server cannot obtain a LOCK
resource at this time. Rerun your statement when there are fewer active
What does this mean?
Does anyone now if it could be a serious problem with the database?
The database is 30 GB at the moment.
I appreciate comments and suggestions.
Kjell Arne Johansen|||Thank You.
Does it exist rules for how much memory that should be be available for SQL
Server?
This is an alarms and events database and at the moment there is a lot of
events from the system. About 2000 - 3000 records to the database each
minute with text, date and time. I will assume that each event is about 200
byte before making the SQL.
The SQL Server and database is on the same machine but the application
writing to the database is on another machine. (Process network and
Administrative network).
When the system is finished and tuned there will not be more than 50 events
-and far less alarms- each minute.
Regards
Kjell Arne Johansen
"Anthony Thomas" wrote:

> Unless you've implemented a startup trace flag to block it, EVERY DATABASE
> IS RECOVERED, automatically, UPON SQL SERVER STARTUP. That's part of the
> ACID properties. If it didn't and you had a power outage as the cause of
> the last reboot, all of your databases would be hosed. No, don't be
> worried. Why you see this for this particular databases is because it is
a
> little larger than the others, which recover much more quickly.
> Now, the last error indicates you may have too little memory on your serve
r
> for the activity that is been given it or you have throttled back SQL
> Server's access to RAM.
> Sincerely,
>
> Anthony Thomas
>|||Well it all depends on how much activity there is on your server, by default
SQL Server will use memory dynamically. That means that it will allocate and
deallocate memory on the fly based on the needs.
I would suggest that you monitor the memory by using performance monitor.
Start by looking at the following counters:
SQLServer:Buffer Manager Buffer cache hit ratio
SQLServer:Buffer Manager Free pages
SQLServer:Buffer Manager Page life expectancy
SQLServer:Buffer Manager Target pages
There is more information about how you can monitor the memory usage of your
SQL Server in the book called:
Microsoft SQL Server 2000
Performance Tuning
Technical Reference
ISBN: 0-7356-1270-6
Simon
This posting is provided "as is" with no warranties and confers no rights.
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:B3B453C9-7964-4DF4-A0FC-C61CF116F9FD@.microsoft.com...
> Thank You.
> Does it exist rules for how much memory that should be be available for
> SQL
> Server?
> This is an alarms and events database and at the moment there is a lot of
> events from the system. About 2000 - 3000 records to the database each
> minute with text, date and time. I will assume that each event is about
> 200
> byte before making the SQL.
> The SQL Server and database is on the same machine but the application
> writing to the database is on another machine. (Process network and
> Administrative network).
> When the system is finished and tuned there will not be more than 50
> events
> -and far less alarms- each minute.
> Regards
> Kjell Arne Johansen
> "Anthony Thomas" wrote:
>
>

Monday, March 19, 2012

Automatic Job Monitoring

I'm a SQL Server 7/2000 DBA and manages about 40 servers in different networks. Every morning I check through the Enterprise Manager if all Jobs (backup, maintenance, etc.) have run successfully. This check costs me 1 hour per day.

Because of a reorganization I've got some new college's and lost some college's. My new college's think this is to much work, so it should be automated. They only want the failed jobs to report an error on a website or something like that, and don't want to check 40 servers. I don't agree in this, because I'm affraid I'm going to miss some errors.

How do you do your checks every morning?

Thanks,

MarcoYou can look up master SQL Server Agent job in SQL BOL. I personally have never used it except for a long time ago when I was experimenting with it early on.

You can also use SQL Agent Mail to flag when a job has failed and send out an alert. SQL Agent Mail is a pain to set up (have to install Outlook), is not fully supported by MS in an clustered environment (Outlook is not cluster aware) and when it goes South in can really go south in a bad way.

I personally manage about a dozen SQL servers. I use a combination of SQL Agent mail on a "management" SQL server (to which all the other servers forward their alerts) and a web site that pulls from a database on the "management" database. This database is in turn populated by a DTS package that pulls from the sysjobhistory table in the other servers. It was a fair amount of work to set up, but it's been running for almost three years now with very few changes.

Regards,

hmscott|||u can build query on system tables for job history in msdb database like : sysjobs, sysjobhistory, sysjobschedules. to make it automated, just schedule the query, then send a notification email, or store to a table, or however you want.
to make a complete check, notice the schedule, coz only already run steps (succeeded, failed, or cancelled) are logged in sysjobhistory.

Thursday, February 16, 2012

Auto Shutdown Service Manager

Plz Help me.
Auto shutting down of my sql service manager. what i will start it,
but the server is shutdown after 30 seconds. that is why i can't used any
services. Please to help me, what about to start of my sql service manager
and matained of sql server 2000 (enterprice)? Please answer and good thing.
Plz faster and faster moment.
Thank of all and member.
Saw Oo(Myanmar)
Hi,
For Stopping and starting the SQL Server Service please the Control Panel --
Services option.
Thanks
Hari
SQL Server MVP
"SawOo.ygn" <SawOo.ygn@.discussions.microsoft.com> wrote in message
news:AD9546B7-87BB-4386-BB61-F064D200410A@.microsoft.com...
> Plz Help me.
> Auto shutting down of my sql service manager. what i will start it,
> but the server is shutdown after 30 seconds. that is why i can't used any
> services. Please to help me, what about to start of my sql service manager
> and matained of sql server 2000 (enterprice)? Please answer and good
> thing.
> Plz faster and faster moment.
> Thank of all and member.
> Saw Oo(Myanmar)
>

Auto run report

Hello

Anyone know if any of the following are possible:

Change the icon for a report in the report manager. I want a report to have a folder icon, so it therefore looks like a folder. When a user opens a folder, have a report automatically run and display. Have a report automatically open when the user opens the report manager.

Cheers

**Bump** - Anyone?

Monday, February 13, 2012

Auto Render as .pdf

Is there a way to force the output of a manually run report using
Report Manager to output as .pdf rather than html and having to export
from there?
I would like to enter a shipment number as a prompt, then go straight
to creating a shippping document as a .pdf
Thanks
BobHi Bob,
This should answer your query
http://msdn2.microsoft.com/en-us/library/ms152835.aspx
"Bob" wrote:
> Is there a way to force the output of a manually run report using
> Report Manager to output as .pdf rather than html and having to export
> from there?
> I would like to enter a shipment number as a prompt, then go straight
> to creating a shippping document as a .pdf
> Thanks
> Bob
>

Friday, February 10, 2012

auto generated indexes

when viewing a database in the taskpad view from
enterprise manager, i notice there are a lot of indexes on
tables that have been automatically generated. they are
named like _WA_Sys_TransactionID_479C827A (where
TransactionID is a column in the table). Does someone
know where they come from and what they are used for?
There are more than one on some tables.
ThanksMaria
These are not indexes, they are just statistics on columns that are
auto-generated when the optimizer is trying to come up with a plan involving
the columns. Knowing the data distribution in the column can help the
optimizer come up with a better plan. By default, they are created on any
unindexed column used in a query. This is controlled by a database option
'auto create statistics'.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Maria" <mariac@.rcspf.co.za> wrote in message
news:2334101c38c21$f4de13b0$a601280a@.phx.gbl...
> when viewing a database in the taskpad view from
> enterprise manager, i notice there are a lot of indexes on
> tables that have been automatically generated. they are
> named like _WA_Sys_TransactionID_479C827A (where
> TransactionID is a column in the table). Does someone
> know where they come from and what they are used for?
> There are more than one on some tables.
> Thanks

Tuesday, March 27, 2012

Automating backups with TSM

Thursday, March 22, 2012

Automatical Import from MySQL-Table?

Automatical Import from MySQL-Table?

Automatical Import from MySQL-Table?

Tuesday, March 20, 2012

automatic sql server backup to networked machine

automatic sql server backup to networked machine

automatic sql server backup to networked machine

automatic sql server backup to networked machine

automatic script generation

automatic script generation

Automatic Recovery of SQL database?

Automatic Recovery of SQL database?

Automatic Recovery of SQL database?

Monday, March 19, 2012

Automatic Job Monitoring

Thursday, February 16, 2012

Auto Shutdown Service Manager

Auto run report

Monday, February 13, 2012

Auto Render as .pdf

Friday, February 10, 2012

auto generated indexes

Sql Auto grow

Blog Archive

About Me