Showing posts with label below. Show all posts
Showing posts with label below. Show all posts

Tuesday, March 20, 2012

can't access MSDE database after setup

I can't log in with osql. My setup config file is as below.
[Options]
INSTANCENAME=NetSDK
SECURITYMODE=SQL
SAPWD=mypassword
It seems to install and run (green arrow in service bar after giving
it
myservername\NetSDK). Then I try this:
osql -U sa
Password: 'I give mypassword here
[Shared Memory]SQL Server does not exists or access denied.
[Shared Memory]ConnectionOpen (Connect()).
What does this mean? Am I missing something basic? I would really like
to use MSDE, but it seems broken or I just don't have enough
information.
Please cc to jhestess@.yahoo.com. Thanks in advance.
John Estess
El Paso, Texas
This may be a problem with ports or other things. Try these:
"Potential causes of the "SQL Server Does Not Exist or Access Denied" error message"
http://support.microsoft.com/default...06&Product=sql
http://support.microsoft.com/?id=287932
http://support.microsoft.com/default.aspx?kbid=814130
Mark Ferguson
"John Estess" wrote:

> I can't log in with osql. My setup config file is as below.
> [Options]
> INSTANCENAME=NetSDK
> SECURITYMODE=SQL
> SAPWD=mypassword
> It seems to install and run (green arrow in service bar after giving
> it
> myservername\NetSDK). Then I try this:
> osql -U sa
> Password: 'I give mypassword here
> [Shared Memory]SQL Server does not exists or access denied.
> [Shared Memory]ConnectionOpen (Connect()).
> What does this mean? Am I missing something basic? I would really like
> to use MSDE, but it seems broken or I just don't have enough
> information.
> Please cc to jhestess@.yahoo.com. Thanks in advance.
> John Estess
> El Paso, Texas
>
|||hi John,
"John Estess" <jhestess@.yahoo.com> ha scritto nel messaggio
news:136cf350.0406250942.1417a8a7@.posting.google.c om...
> I can't log in with osql. My setup config file is as below.
> [Options]
> INSTANCENAME=NetSDK
> SECURITYMODE=SQL
> SAPWD=mypassword
> It seems to install and run (green arrow in service bar after giving
> it
> myservername\NetSDK). Then I try this:
> osql -U sa
> Password: 'I give mypassword here
> [Shared Memory]SQL Server does not exists or access denied.
> [Shared Memory]ConnectionOpen (Connect()).
> What does this mean? Am I missing something basic? I would really like
> to use MSDE, but it seems broken or I just don't have enough
> information.
> Please cc to jhestess@.yahoo.com. Thanks in advance.
> John Estess
> El Paso, Texas
try
c:\>osql -Smyservername\NetSDK -Usa -Pyour_pwd
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

Can't access database

Please see threads below for sp_dboption.
I can't connect to the database via Query Analyser.
When I try to open the Databases folder in Enterprise
manager, it seems to hang.
The database affected is for testing only, so I am not
worried about that. I am worried about the server tho, as
it is a production server.
Hi,
Can you check the SQL Server error logs in SQL Server program folder\logs
folder for any errors.
See like there is some issues in your test database.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:28d3c01c4642d$e3781460$a301280a@.phx.gbl...
> Please see threads below for sp_dboption.
> I can't connect to the database via Query Analyser.
> When I try to open the Databases folder in Enterprise
> manager, it seems to hang.
> The database affected is for testing only, so I am not
> worried about that. I am worried about the server tho, as
> it is a production server.

Can't access database

Please see threads below for sp_dboption.
I can't connect to the database via Query Analyser.
When I try to open the Databases folder in Enterprise
manager, it seems to hang.
The database affected is for testing only, so I am not
worried about that. I am worried about the server tho, as
it is a production server.Hi,
Can you check the SQL Server error logs in SQL Server program folder\logs
folder for any errors.
See like there is some issues in your test database.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:28d3c01c4642d$e3781460$a301280a@.phx
.gbl...
> Please see threads below for sp_dboption.
> I can't connect to the database via Query Analyser.
> When I try to open the Databases folder in Enterprise
> manager, it seems to hang.
> The database affected is for testing only, so I am not
> worried about that. I am worried about the server tho, as
> it is a production server.

Can't access database

Please see threads below for sp_dboption.
I can't connect to the database via Query Analyser.
When I try to open the Databases folder in Enterprise
manager, it seems to hang.
The database affected is for testing only, so I am not
worried about that. I am worried about the server tho, as
it is a production server.Hi,
Can you check the SQL Server error logs in SQL Server program folder\logs
folder for any errors.
See like there is some issues in your test database.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:28d3c01c4642d$e3781460$a301280a@.phx.gbl...
> Please see threads below for sp_dboption.
> I can't connect to the database via Query Analyser.
> When I try to open the Databases folder in Enterprise
> manager, it seems to hang.
> The database affected is for testing only, so I am not
> worried about that. I am worried about the server tho, as
> it is a production server.

Wednesday, March 7, 2012

Cannot start your application. The workgroup information file is missing or opened exclusively b

hye everyone,
when i browse my web application the eror as below display..
what should i do..
any suggestion or tips...

"Cannot start your application. The workgroup information file is missing or opened exclusively by another user.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Cannot start your application. The workgroup information file is missing or opened exclusively by another user. "

thanks in advance..
thank you very much..

That is not a SQL Server problem as you aure using obviously MS Access, you better post your question to one of the Access forums in the public newgroup network. But, as far as I can remember from my old Access days, you will have to delete the lock file which is located in the same folder as the workgroup file which contains the security informa′tion.

Jens K. Suessmeyer

http://www.sqlserver2005.de

CANNOT START SQL SERVER Service - 18052 NEED HELP here !

Hi ,
This is refering to my previous mail " moving master db to another location"
and really need advise as i messed up the SQL Server !!
below is the series of steps & checks i have made from my earlier postings
1) +++++++++++++++++
Hi ,
I was trying to move both msdb & model using the following steps :
1. addede the parameter -T3608
2a. use master
go
sp_detach_db 'msdb'
go
2b. use master
go
sp_detach_db 'model'
go
3. stop & restart SQL SERVER
4. i could not restart the SQL SERVER Service from the SQL Server Service
Manager as it keeps restarting & stopping by itself
any way i can get it to work now ?
tks & rdgs
2) ++++++++++++++++=
Hi ,
I got the 18502 error (looked into past posting seems no solution)
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 5/29/2006
Time: 6:11:53 PM
User: N/A
Computer: ALTIRISSERVER
Description:
18052 :
Error: 823, Severity: 24, State: 2.
and
from the SQL Error Log i got the followings :
2006-05-29 18:11:51.41 server Microsoft SQL Server 2000 - 8.00.760 (Intel
X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
2006-05-29 18:11:51.42 server Copyright (C) 1988-2002 Microsoft
Corporation.
2006-05-29 18:11:51.42 server All rights reserved.
2006-05-29 18:11:51.42 server Server Process ID is 2268.
2006-05-29 18:11:51.42 server Logging SQL Server messages in file 'd:\ms
sqlserver\MSSQL\log\ERRORLOG'.
2006-05-29 18:11:51.42 server SQL Server is starting at priority class
'high'(2 CPUs detected).
2006-05-29 18:11:51.44 server Performance monitor shared memory setup
failed: -1
2006-05-29 18:11:51.53 server SQL Server configured for thread mode
processing.
2006-05-29 18:11:51.53 server Using dynamic lock allocation. [2500] Lock
Blocks, [5000] Lock Owner Blocks.
2006-05-29 18:11:51.57 server Attempting to initialize Distributed
Transaction Coordinator.
2006-05-29 18:11:52.60 spid4 Starting up database 'master'.
2006-05-29 18:11:53.85 spid4 Error: 823, Severity: 24, State: 2.
2006-05-29 18:11:53.85 spid4 Cannot recover the master database. Exiting.
Any solutions besidess re-install ?
tks & rdgs
3 +++++++++++++++
Hi ,
further info :
I have checked the registry for the Master db the path is as per the
startup parameters before i moved the 'model' and 'msdb' database
really appreciate all advise
tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1> 3 +++++++++++++++
> Hi ,
> further info :
> I have checked the registry for the Master db the path is as per the
> startup parameters before i moved the 'model' and 'msdb' database
The startup parameters must be changed to reflect the new master file
locations in order for SQL Server to start.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:60f93fb49965d@.uwe...
> Hi ,
> This is refering to my previous mail " moving master db to another
> location"
> and really need advise as i messed up the SQL Server !!
> below is the series of steps & checks i have made from my earlier postings
> 1) +++++++++++++++++
> Hi ,
> I was trying to move both msdb & model using the following steps :
> 1. addede the parameter -T3608
> 2a. use master
> go
> sp_detach_db 'msdb'
> go
> 2b. use master
> go
> sp_detach_db 'model'
> go
> 3. stop & restart SQL SERVER
> 4. i could not restart the SQL SERVER Service from the SQL Server Service
> Manager as it keeps restarting & stopping by itself
> any way i can get it to work now ?
> tks & rdgs
>
>
> 2) ++++++++++++++++=> Hi ,
> I got the 18502 error (looked into past posting seems no solution)
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17055
> Date: 5/29/2006
> Time: 6:11:53 PM
> User: N/A
> Computer: ALTIRISSERVER
> Description:
> 18052 :
> Error: 823, Severity: 24, State: 2.
> and
> from the SQL Error Log i got the followings :
> 2006-05-29 18:11:51.41 server Microsoft SQL Server 2000 - 8.00.760
> (Intel
> X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> 2006-05-29 18:11:51.42 server Copyright (C) 1988-2002 Microsoft
> Corporation.
> 2006-05-29 18:11:51.42 server All rights reserved.
> 2006-05-29 18:11:51.42 server Server Process ID is 2268.
> 2006-05-29 18:11:51.42 server Logging SQL Server messages in file
> 'd:\ms
> sqlserver\MSSQL\log\ERRORLOG'.
> 2006-05-29 18:11:51.42 server SQL Server is starting at priority class
> 'high'(2 CPUs detected).
> 2006-05-29 18:11:51.44 server Performance monitor shared memory setup
> failed: -1
> 2006-05-29 18:11:51.53 server SQL Server configured for thread mode
> processing.
> 2006-05-29 18:11:51.53 server Using dynamic lock allocation. [2500]
> Lock
> Blocks, [5000] Lock Owner Blocks.
> 2006-05-29 18:11:51.57 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2006-05-29 18:11:52.60 spid4 Starting up database 'master'.
> 2006-05-29 18:11:53.85 spid4 Error: 823, Severity: 24, State: 2.
> 2006-05-29 18:11:53.85 spid4 Cannot recover the master database.
> Exiting.
> Any solutions besidess re-install ?
> tks & rdgs
>
>
> 3 +++++++++++++++
> Hi ,
> further info :
> I have checked the registry for the Master db the path is as per the
> startup parameters before i moved the 'model' and 'msdb' database
> really appreciate all advise
> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Hi ,
But i did not move the Master file i moved only 'model' and 'msdb' . will
this affect the master db's parameter as well ?
tks & rdgs
Dan Guzman wrote:
>> 3 +++++++++++++++
>[quoted text clipped - 4 lines]
>> I have checked the registry for the Master db the path is as per the
>> startup parameters before i moved the 'model' and 'msdb' database
>The startup parameters must be changed to reflect the new master file
>locations in order for SQL Server to start.
>> Hi ,
>[quoted text clipped - 95 lines]
>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Moving model and msdb files will not affect the master database file
locations but you mentioned moving master in your other thread and it looks
to me like this problem may be related.
Check to ensure the master database files are at the locations specified by
the registry startup parameters.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:60f9d688bc36d@.uwe...
> Hi ,
> But i did not move the Master file i moved only 'model' and 'msdb' . will
> this affect the master db's parameter as well ?
>
> tks & rdgs
> Dan Guzman wrote:
>> 3 +++++++++++++++
>>[quoted text clipped - 4 lines]
>> I have checked the registry for the Master db the path is as per the
>> startup parameters before i moved the 'model' and 'msdb' database
>>The startup parameters must be changed to reflect the new master file
>>locations in order for SQL Server to start.
>> Hi ,
>>[quoted text clipped - 95 lines]
>> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Hi,
That was my intention to move master to another location but before i could
do that i wasn't able to start SQL server service after moving both the model
& msdb db to a new location at the same time
tks & rdgs
Dan Guzman wrote:
>Moving model and msdb files will not affect the master database file
>locations but you mentioned moving master in your other thread and it looks
>to me like this problem may be related.
>Check to ensure the master database files are at the locations specified by
>the registry startup parameters.
>> Hi ,
>[quoted text clipped - 17 lines]
>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Hi ,
This is the latest details from the Error Log :
2006-05-29 22:18:11.46 server Microsoft SQL Server 2000 - 8.00.760 (Intel
X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
2006-05-29 22:18:11.46 server Copyright (C) 1988-2002 Microsoft
Corporation.
2006-05-29 22:18:11.46 server All rights reserved.
2006-05-29 22:18:11.46 server Server Process ID is 1672.
2006-05-29 22:18:11.46 server Logging SQL Server messages in file 'd:\ms
sqlserver\MSSQL\log\ERRORLOG'.
2006-05-29 22:18:11.47 server SQL Server is starting at priority class
'high'(2 CPUs detected).
2006-05-29 22:18:11.61 server SQL Server configured for thread mode
processing.
2006-05-29 22:18:11.61 server Using dynamic lock allocation. [2500] Lock
Blocks, [5000] Lock Owner Blocks.
2006-05-29 22:18:11.64 server Attempting to initialize Distributed
Transaction Coordinator.
2006-05-29 22:18:11.67 server Failed to obtain
TransactionDispenserInterface: Result Code = 0x8004d01b
2006-05-29 22:18:11.67 spid3 Starting up database 'master'.
2006-05-29 22:18:11.83 server Using 'SSNETLIB.DLL' version '8.0.766'.
2006-05-29 22:18:11.85 server SQL server listening on 10.224.1.9: 1433.
2006-05-29 22:18:11.85 server SQL server listening on 127.0.0.1: 1433.
2006-05-29 22:18:11.85 spid3 Server name is 'ALTIRISSERVER'.
2006-05-29 22:18:11.85 spid8 Starting up database 'Altiris'.
2006-05-29 22:18:11.86 spid10 Starting up database 'Altiris_Incidents'.
2006-05-29 22:18:11.86 spid9 Starting up database 'SidataSQL_DC'.
2006-05-29 22:18:11.86 spid11 Starting up database 'ScalaDB_SGO_Restore'.
2006-05-29 22:18:11.86 spid12 Starting up database 'ScalaDB_SGO_TEST'.
2006-05-29 22:18:11.86 spid5 Could not find database ID 3. Database may
not be activated yet or may be in transition.
which database does ID 3 refers to ?
tks & rdgs
maxzsim wrote:
>Hi,
> That was my intention to move master to another location but before i could
>do that i wasn't able to start SQL server service after moving both the model
>& msdb db to a new location at the same time
>tks & rdgs
>>Moving model and msdb files will not affect the master database file
>>locations but you mentioned moving master in your other thread and it looks
>[quoted text clipped - 8 lines]
>> tks & rdgs
--
Message posted via http://www.sqlmonster.com|||Error 823 is usually preceded by a detailed message about the cause of the
I/O error. Do you have any other related messages in the event log? Are
you moving these database files because a disk is going bad?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:60fa3a34e6e1e@.uwe...
> Hi,
> That was my intention to move master to another location but before i
> could
> do that i wasn't able to start SQL server service after moving both the
> model
> & msdb db to a new location at the same time
> tks & rdgs
> Dan Guzman wrote:
>>Moving model and msdb files will not affect the master database file
>>locations but you mentioned moving master in your other thread and it
>>looks
>>to me like this problem may be related.
>>Check to ensure the master database files are at the locations specified
>>by
>>the registry startup parameters.
>> Hi ,
>>[quoted text clipped - 17 lines]
>> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||The model database must be assigned dbid 3. Perhaps dbid 3 was not assigned
correctly either because of the other problems or because you forgot to
remove the 3608 trace flag.
Start SQL Server in minimal configuration mode (-f parameter) and run the
following query
SELECT dbid
FROM master..sysdatabases
WHERE name = 'model'
If model reports a dbid other than 3, repeat the model database move steps
in minimal configuration mode.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:60fa4c757d28d@.uwe...
> Hi ,
> This is the latest details from the Error Log :
> 2006-05-29 22:18:11.46 server Microsoft SQL Server 2000 - 8.00.760
> (Intel
> X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> 2006-05-29 22:18:11.46 server Copyright (C) 1988-2002 Microsoft
> Corporation.
> 2006-05-29 22:18:11.46 server All rights reserved.
> 2006-05-29 22:18:11.46 server Server Process ID is 1672.
> 2006-05-29 22:18:11.46 server Logging SQL Server messages in file
> 'd:\ms
> sqlserver\MSSQL\log\ERRORLOG'.
> 2006-05-29 22:18:11.47 server SQL Server is starting at priority class
> 'high'(2 CPUs detected).
> 2006-05-29 22:18:11.61 server SQL Server configured for thread mode
> processing.
> 2006-05-29 22:18:11.61 server Using dynamic lock allocation. [2500]
> Lock
> Blocks, [5000] Lock Owner Blocks.
> 2006-05-29 22:18:11.64 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2006-05-29 22:18:11.67 server Failed to obtain
> TransactionDispenserInterface: Result Code = 0x8004d01b
> 2006-05-29 22:18:11.67 spid3 Starting up database 'master'.
> 2006-05-29 22:18:11.83 server Using 'SSNETLIB.DLL' version '8.0.766'.
> 2006-05-29 22:18:11.85 server SQL server listening on 10.224.1.9: 1433.
> 2006-05-29 22:18:11.85 server SQL server listening on 127.0.0.1: 1433.
> 2006-05-29 22:18:11.85 spid3 Server name is 'ALTIRISSERVER'.
> 2006-05-29 22:18:11.85 spid8 Starting up database 'Altiris'.
> 2006-05-29 22:18:11.86 spid10 Starting up database 'Altiris_Incidents'.
> 2006-05-29 22:18:11.86 spid9 Starting up database 'SidataSQL_DC'.
> 2006-05-29 22:18:11.86 spid11 Starting up database
> 'ScalaDB_SGO_Restore'.
> 2006-05-29 22:18:11.86 spid12 Starting up database 'ScalaDB_SGO_TEST'.
> 2006-05-29 22:18:11.86 spid5 Could not find database ID 3. Database
> may
> not be activated yet or may be in transition.
>
> which database does ID 3 refers to ?
> tks & rdgs
> maxzsim wrote:
>>Hi,
>> That was my intention to move master to another location but before i
>> could
>>do that i wasn't able to start SQL server service after moving both the
>>model
>>& msdb db to a new location at the same time
>>tks & rdgs
>>Moving model and msdb files will not affect the master database file
>>locations but you mentioned moving master in your other thread and it
>>looks
>>[quoted text clipped - 8 lines]
>> tks & rdgs
> --
> Message posted via http://www.sqlmonster.com|||Hi ,
Yes i am moving the databases because the harddisk is failing and i did the
following steps below
1. >sqlservr.exe /T3608
2. Then from SQL Query Analyzer I tried to attach both MODEL and MSDB
databases, and they were attached.
step 1 & 2 is from a posting by VM
3. but i cannot "see" model and when i tried to attached from EM it says
successfully but i still cannot see "model" db or when i tried to force
restore or WITH REPLACE it says RESTORE failed abnormally
what shall i do now ?
tks & rdgs
Dan Guzman wrote:
>Error 823 is usually preceded by a detailed message about the cause of the
>I/O error. Do you have any other related messages in the event log? Are
>you moving these database files because a disk is going bad?
>> Hi,
>[quoted text clipped - 20 lines]
>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Hello ,
Have you taken a copy of Master/Model/MSDB Database files before Performing
the above Steps?
Vishal|||I suggest you repeat the model database attach steps as described in
http://support.microsoft.com/kb/224071/en-us. Don't attach the model
database from EM - run the SQL scripts from Query Analyzer or OSQL.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:60fae5663e141@.uwe...
> Hi ,
> Yes i am moving the databases because the harddisk is failing and i did
> the
> following steps below
> 1. >sqlservr.exe /T3608
> 2. Then from SQL Query Analyzer I tried to attach both MODEL and MSDB
> databases, and they were attached.
> step 1 & 2 is from a posting by VM
> 3. but i cannot "see" model and when i tried to attached from EM it says
> successfully but i still cannot see "model" db or when i tried to force
> restore or WITH REPLACE it says RESTORE failed abnormally
>
> what shall i do now ?
> tks & rdgs
>
> Dan Guzman wrote:
>>Error 823 is usually preceded by a detailed message about the cause of the
>>I/O error. Do you have any other related messages in the event log? Are
>>you moving these database files because a disk is going bad?
>> Hi,
>>[quoted text clipped - 20 lines]
>>>
>>> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||Hi ,
I have made a backup copies of all the system db but i am not able to
restore from the model.bak
tks & rdgs
Vishal Gandhi wrote:
>Hello ,
>Have you taken a copy of Master/Model/MSDB Database files before Performing
>the above Steps?
>Vishal
--
Message posted via http://www.sqlmonster.com|||Hi ,
using QA to attach the msdb & model db was done after i ran sqlserv.exe
/T3608
it simply says attached successfully but i could not see the "model" db and
btw ID 3 refers to the "msdb" db
in the worst case scenario if i need to re-install SQL Server can i restore
the master , model & msdb over the existing ones ?
tks & rdgs
Dan Guzman wrote:
>I suggest you repeat the model database attach steps as described in
>http://support.microsoft.com/kb/224071/en-us. Don't attach the model
>database from EM - run the SQL scripts from Query Analyzer or OSQL.
>> Hi ,
>[quoted text clipped - 26 lines]
>>>
>>> tks & rdgs
--
Message posted via http://www.sqlmonster.com|||> it simply says attached successfully but i could not see the "model" db
> and
> btw ID 3 refers to the "msdb" db
It looks like msdb got assigned dbid 3 instead of model.
Start sql server in minimal configuration mode and detaching both model and
msdb. Attach model first and verify all is expected:
USE model
EXEC sp_helpfile
EXEC sp_helpdb 'model' --make sure dbid is 3
GO
Then attach msdb and verify all is good:
USE msdb
EXEC sp_helpfile
EXEC sp_helpdb 'msdb' --make sure dbid is > 3
GO
Then restart SQL Server normally.
> in the worst case scenario if i need to re-install SQL Server can i
> restore
> the master , model & msdb over the existing ones ?
Yes, you could restore following a reinstall (or REBUILDM) but I'd try the
reattach first. You are almost there.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:60fb4a3f43fa3@.uwe...
> Hi ,
> using QA to attach the msdb & model db was done after i ran sqlserv.exe
> /T3608
> it simply says attached successfully but i could not see the "model" db
> and
> btw ID 3 refers to the "msdb" db
> in the worst case scenario if i need to re-install SQL Server can i
> restore
> the master , model & msdb over the existing ones ?
> tks & rdgs
> Dan Guzman wrote:
>>I suggest you repeat the model database attach steps as described in
>>http://support.microsoft.com/kb/224071/en-us. Don't attach the model
>>database from EM - run the SQL scripts from Query Analyzer or OSQL.
>> Hi ,
>>[quoted text clipped - 26 lines]
>>>
>>> tks & rdgs
> --
> Message posted via http://www.sqlmonster.com|||tks for all the help provided but unfortunately the hdd failed on me before i
could do anything further
now i'll need to rebuild or re-install but luckily i got all the system &
user DBs backed up
tks & rdgs
Dan Guzman wrote:
>> it simply says attached successfully but i could not see the "model" db
>> and
>> btw ID 3 refers to the "msdb" db
>It looks like msdb got assigned dbid 3 instead of model.
>Start sql server in minimal configuration mode and detaching both model and
>msdb. Attach model first and verify all is expected:
>USE model
>EXEC sp_helpfile
>EXEC sp_helpdb 'model' --make sure dbid is 3
>GO
>Then attach msdb and verify all is good:
>USE msdb
>EXEC sp_helpfile
>EXEC sp_helpdb 'msdb' --make sure dbid is > 3
>GO
>Then restart SQL Server normally.
>> in the worst case scenario if i need to re-install SQL Server can i
>> restore
>> the master , model & msdb over the existing ones ?
>Yes, you could restore following a reinstall (or REBUILDM) but I'd try the
>reattach first. You are almost there.
>> Hi ,
>[quoted text clipped - 19 lines]
>>>>
>>>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1

CANNOT START SQL SERVER Service - 18052 NEED HELP here !

Hi ,
This is refering to my previous mail " moving master db to another location"
and really need advise as i messed up the SQL Server !!
below is the series of steps & checks i have made from my earlier postings
1) +++++++++++++++++
Hi ,
I was trying to move both msdb & model using the following steps :
1. addede the parameter -T3608
2a. use master
go
sp_detach_db 'msdb'
go
2b. use master
go
sp_detach_db 'model'
go
3. stop & restart SQL SERVER
4. i could not restart the SQL SERVER Service from the SQL Server Service
Manager as it keeps restarting & stopping by itself
any way i can get it to work now ?
tks & rdgs
2) ++++++++++++++++=
Hi ,
I got the 18502 error (looked into past posting seems no solution)
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 5/29/2006
Time: 6:11:53 PM
User: N/A
Computer: ALTIRISSERVER
Description:
18052 :
Error: 823, Severity: 24, State: 2.
and
from the SQL Error Log i got the followings :
2006-05-29 18:11:51.41 server Microsoft SQL Server 2000 - 8.00.760 (Inte
l
X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
2006-05-29 18:11:51.42 server Copyright (C) 1988-2002 Microsoft
Corporation.
2006-05-29 18:11:51.42 server All rights reserved.
2006-05-29 18:11:51.42 server Server Process ID is 2268.
2006-05-29 18:11:51.42 server Logging SQL Server messages in file 'd:\ms
sqlserver\MSSQL\log\ERRORLOG'.
2006-05-29 18:11:51.42 server SQL Server is starting at priority class
'high'(2 CPUs detected).
2006-05-29 18:11:51.44 server Performance monitor shared memory setup
failed: -1
2006-05-29 18:11:51.53 server SQL Server configured for thread mode
processing.
2006-05-29 18:11:51.53 server Using dynamic lock allocation. [2500] L
ock
Blocks, [5000] Lock Owner Blocks.
2006-05-29 18:11:51.57 server Attempting to initialize Distributed
Transaction Coordinator.
2006-05-29 18:11:52.60 spid4 Starting up database 'master'.
2006-05-29 18:11:53.85 spid4 Error: 823, Severity: 24, State: 2.
2006-05-29 18:11:53.85 spid4 Cannot recover the master database. Exiting
.
Any solutions besidess re-install ?
tks & rdgs
3 +++++++++++++++
Hi ,
further info :
I have checked the registry for the Master db the path is as per the
startup parameters before i moved the 'model' and 'msdb' database
really appreciate all advise
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1> 3 +++++++++++++++
> Hi ,
> further info :
> I have checked the registry for the Master db the path is as per the
> startup parameters before i moved the 'model' and 'msdb' database
The startup parameters must be changed to reflect the new master file
locations in order for SQL Server to start.
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:60f93fb49965d@.uwe...
> Hi ,
> This is refering to my previous mail " moving master db to another
> location"
> and really need advise as i messed up the SQL Server !!
> below is the series of steps & checks i have made from my earlier postings
> 1) +++++++++++++++++
> Hi ,
> I was trying to move both msdb & model using the following steps :
> 1. addede the parameter -T3608
> 2a. use master
> go
> sp_detach_db 'msdb'
> go
> 2b. use master
> go
> sp_detach_db 'model'
> go
> 3. stop & restart SQL SERVER
> 4. i could not restart the SQL SERVER Service from the SQL Server Service
> Manager as it keeps restarting & stopping by itself
> any way i can get it to work now ?
> tks & rdgs
>
>
> 2) ++++++++++++++++=
> Hi ,
> I got the 18502 error (looked into past posting seems no solution)
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17055
> Date: 5/29/2006
> Time: 6:11:53 PM
> User: N/A
> Computer: ALTIRISSERVER
> Description:
> 18052 :
> Error: 823, Severity: 24, State: 2.
> and
> from the SQL Error Log i got the followings :
> 2006-05-29 18:11:51.41 server Microsoft SQL Server 2000 - 8.00.760
> (Intel
> X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> 2006-05-29 18:11:51.42 server Copyright (C) 1988-2002 Microsoft
> Corporation.
> 2006-05-29 18:11:51.42 server All rights reserved.
> 2006-05-29 18:11:51.42 server Server Process ID is 2268.
> 2006-05-29 18:11:51.42 server Logging SQL Server messages in file
> 'd:\ms
> sqlserver\MSSQL\log\ERRORLOG'.
> 2006-05-29 18:11:51.42 server SQL Server is starting at priority class
> 'high'(2 CPUs detected).
> 2006-05-29 18:11:51.44 server Performance monitor shared memory setup
> failed: -1
> 2006-05-29 18:11:51.53 server SQL Server configured for thread mode
> processing.
> 2006-05-29 18:11:51.53 server Using dynamic lock allocation. [2500]
> Lock
> Blocks, [5000] Lock Owner Blocks.
> 2006-05-29 18:11:51.57 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2006-05-29 18:11:52.60 spid4 Starting up database 'master'.
> 2006-05-29 18:11:53.85 spid4 Error: 823, Severity: 24, State: 2.
> 2006-05-29 18:11:53.85 spid4 Cannot recover the master database.
> Exiting.
> Any solutions besidess re-install ?
> tks & rdgs
>
>
> 3 +++++++++++++++
> Hi ,
> further info :
> I have checked the registry for the Master db the path is as per the
> startup parameters before i moved the 'model' and 'msdb' database
> really appreciate all advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200605/1|||Hi ,
But i did not move the Master file i moved only 'model' and 'msdb' . will
this affect the master db's parameter as well ?
tks & rdgs
Dan Guzman wrote:[vbcol=seagreen]
>[quoted text clipped - 4 lines]
>The startup parameters must be changed to reflect the new master file
>locations in order for SQL Server to start.
>
>[quoted text clipped - 95 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1|||Moving model and msdb files will not affect the master database file
locations but you mentioned moving master in your other thread and it looks
to me like this problem may be related.
Check to ensure the master database files are at the locations specified by
the registry startup parameters.
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:60f9d688bc36d@.uwe...
> Hi ,
> But i did not move the Master file i moved only 'model' and 'msdb' . will
> this affect the master db's parameter as well ?
>
> tks & rdgs
> Dan Guzman wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200605/1|||Hi,
That was my intention to move master to another location but before i could
do that i wasn't able to start SQL server service after moving both the mode
l
& msdb db to a new location at the same time
tks & rdgs
Dan Guzman wrote:[vbcol=seagreen]
>Moving model and msdb files will not affect the master database file
>locations but you mentioned moving master in your other thread and it looks
>to me like this problem may be related.
>Check to ensure the master database files are at the locations specified by
>the registry startup parameters.
>
>[quoted text clipped - 17 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1|||Hi ,
This is the latest details from the Error Log :
2006-05-29 22:18:11.46 server Microsoft SQL Server 2000 - 8.00.760 (Inte
l
X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
2006-05-29 22:18:11.46 server Copyright (C) 1988-2002 Microsoft
Corporation.
2006-05-29 22:18:11.46 server All rights reserved.
2006-05-29 22:18:11.46 server Server Process ID is 1672.
2006-05-29 22:18:11.46 server Logging SQL Server messages in file 'd:\ms
sqlserver\MSSQL\log\ERRORLOG'.
2006-05-29 22:18:11.47 server SQL Server is starting at priority class
'high'(2 CPUs detected).
2006-05-29 22:18:11.61 server SQL Server configured for thread mode
processing.
2006-05-29 22:18:11.61 server Using dynamic lock allocation. [2500] L
ock
Blocks, [5000] Lock Owner Blocks.
2006-05-29 22:18:11.64 server Attempting to initialize Distributed
Transaction Coordinator.
2006-05-29 22:18:11.67 server Failed to obtain
TransactionDispenserInterface: Result Code = 0x8004d01b
2006-05-29 22:18:11.67 spid3 Starting up database 'master'.
2006-05-29 22:18:11.83 server Using 'SSNETLIB.DLL' version '8.0.766'.
2006-05-29 22:18:11.85 server SQL server listening on 10.224.1.9: 1433.
2006-05-29 22:18:11.85 server SQL server listening on 127.0.0.1: 1433.
2006-05-29 22:18:11.85 spid3 Server name is 'ALTIRISSERVER'.
2006-05-29 22:18:11.85 spid8 Starting up database 'Altiris'.
2006-05-29 22:18:11.86 spid10 Starting up database 'Altiris_Incidents'.
2006-05-29 22:18:11.86 spid9 Starting up database 'SidataSQL_DC'.
2006-05-29 22:18:11.86 spid11 Starting up database 'ScalaDB_SGO_Restore'.
2006-05-29 22:18:11.86 spid12 Starting up database 'ScalaDB_SGO_TEST'.
2006-05-29 22:18:11.86 spid5 Could not find database ID 3. Database may
not be activated yet or may be in transition.
which database does ID 3 refers to ?
tks & rdgs
maxzsim wrote:[vbcol=seagreen]
>Hi,
> That was my intention to move master to another location but before i coul
d
>do that i wasn't able to start SQL server service after moving both the mod
el
>& msdb db to a new location at the same time
>tks & rdgs
>
>[quoted text clipped - 8 lines]
Message posted via http://www.droptable.com|||Error 823 is usually preceded by a detailed message about the cause of the
I/O error. Do you have any other related messages in the event log? Are
you moving these database files because a disk is going bad?
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:60fa3a34e6e1e@.uwe...
> Hi,
> That was my intention to move master to another location but before i
> could
> do that i wasn't able to start SQL server service after moving both the
> model
> & msdb db to a new location at the same time
> tks & rdgs
> Dan Guzman wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200605/1|||The model database must be assigned dbid 3. Perhaps dbid 3 was not assigned
correctly either because of the other problems or because you forgot to
remove the 3608 trace flag.
Start SQL Server in minimal configuration mode (-f parameter) and run the
following query
SELECT dbid
FROM master..sysdatabases
WHERE name = 'model'
If model reports a dbid other than 3, repeat the model database move steps
in minimal configuration mode.
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:60fa4c757d28d@.uwe...
> Hi ,
> This is the latest details from the Error Log :
> 2006-05-29 22:18:11.46 server Microsoft SQL Server 2000 - 8.00.760
> (Intel
> X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> 2006-05-29 22:18:11.46 server Copyright (C) 1988-2002 Microsoft
> Corporation.
> 2006-05-29 22:18:11.46 server All rights reserved.
> 2006-05-29 22:18:11.46 server Server Process ID is 1672.
> 2006-05-29 22:18:11.46 server Logging SQL Server messages in file
> 'd:\ms
> sqlserver\MSSQL\log\ERRORLOG'.
> 2006-05-29 22:18:11.47 server SQL Server is starting at priority class
> 'high'(2 CPUs detected).
> 2006-05-29 22:18:11.61 server SQL Server configured for thread mode
> processing.
> 2006-05-29 22:18:11.61 server Using dynamic lock allocation. [2500]
> Lock
> Blocks, [5000] Lock Owner Blocks.
> 2006-05-29 22:18:11.64 server Attempting to initialize Distributed
> Transaction Coordinator.
> 2006-05-29 22:18:11.67 server Failed to obtain
> TransactionDispenserInterface: Result Code = 0x8004d01b
> 2006-05-29 22:18:11.67 spid3 Starting up database 'master'.
> 2006-05-29 22:18:11.83 server Using 'SSNETLIB.DLL' version '8.0.766'.
> 2006-05-29 22:18:11.85 server SQL server listening on 10.224.1.9: 1433.
> 2006-05-29 22:18:11.85 server SQL server listening on 127.0.0.1: 1433.
> 2006-05-29 22:18:11.85 spid3 Server name is 'ALTIRISSERVER'.
> 2006-05-29 22:18:11.85 spid8 Starting up database 'Altiris'.
> 2006-05-29 22:18:11.86 spid10 Starting up database 'Altiris_Incidents'.
> 2006-05-29 22:18:11.86 spid9 Starting up database 'SidataSQL_DC'.
> 2006-05-29 22:18:11.86 spid11 Starting up database
> 'ScalaDB_SGO_Restore'.
> 2006-05-29 22:18:11.86 spid12 Starting up database 'ScalaDB_SGO_TEST'.
> 2006-05-29 22:18:11.86 spid5 Could not find database ID 3. Database
> may
> not be activated yet or may be in transition.
>
> which database does ID 3 refers to ?
> tks & rdgs
> maxzsim wrote:
> --
> Message posted via http://www.droptable.com|||Hi ,
Yes i am moving the databases because the harddisk is failing and i did the
following steps below
1. >sqlservr.exe /T3608
2. Then from SQL Query Analyzer I tried to attach both MODEL and MSDB
databases, and they were attached.
step 1 & 2 is from a posting by VM
3. but i cannot "see" model and when i tried to attached from EM it says
successfully but i still cannot see "model" db or when i tried to force
restore or WITH REPLACE it says RESTORE failed abnormally
what shall i do now ?
tks & rdgs
Dan Guzman wrote:[vbcol=seagreen]
>Error 823 is usually preceded by a detailed message about the cause of the
>I/O error. Do you have any other related messages in the event log? Are
>you moving these database files because a disk is going bad?
>
>[quoted text clipped - 20 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1|||> it simply says attached successfully but i could not see the "model" db
> and
> btw ID 3 refers to the "msdb" db
It looks like msdb got assigned dbid 3 instead of model.
Start sql server in minimal configuration mode and detaching both model and
msdb. Attach model first and verify all is expected:
USE model
EXEC sp_helpfile
EXEC sp_helpdb 'model' --make sure dbid is 3
GO
Then attach msdb and verify all is good:
USE msdb
EXEC sp_helpfile
EXEC sp_helpdb 'msdb' --make sure dbid is > 3
GO
Then restart SQL Server normally.

> in the worst case scenario if i need to re-install SQL Server can i
> restore
> the master , model & msdb over the existing ones ?
Yes, you could restore following a reinstall (or REBUILDM) but I'd try the
reattach first. You are almost there.
Hope this helps.
Dan Guzman
SQL Server MVP
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:60fb4a3f43fa3@.uwe...
> Hi ,
> using QA to attach the msdb & model db was done after i ran sqlserv.exe
> /T3608
> it simply says attached successfully but i could not see the "model" db
> and
> btw ID 3 refers to the "msdb" db
> in the worst case scenario if i need to re-install SQL Server can i
> restore
> the master , model & msdb over the existing ones ?
> tks & rdgs
> Dan Guzman wrote:
> --
> Message posted via http://www.droptable.com

Friday, February 24, 2012

Cannot Shrink Log File

I have a Db w no open transactions, whose log file is 23GB and I cannot
shrink it below its initial size. Perhaps I need to know how to reduce the
initial size of the log. I am trying everything, including DBCC SHRINKFILE
(MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size in
Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
2005 SP2 x64 running in 64-bit mode). Thanks.Try doing a
backup log <db_name>
with Truncate_only
and then try shrinking the file.
--
MG
"MarkGsch" wrote:
> I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce the
> initial size of the log. I am trying everything, including DBCC SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.|||Do you mean that you created it at 23GB to begin with. How many VLF's are in
it? You may want to have a look at this section of BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2eb39bb3-f889-40be-9ca7-daf7d5107c46.htm
Andrew J. Kelly SQL MVP
"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce
> the
> initial size of the log. I am trying everything, including DBCC
> SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size
> in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.|||"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce
> the
> initial size of the log. I am trying everything, including DBCC
> SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size
> in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.
Sounds like you're in FULL RECOVERY mode and no transaction log backups are
being made.
I'd try to make a transaction log backup first.
barring that, do a BACKUP LOG <dbname> with TRUNCATE_ONLY
Then immediately do a FULL backup and then start doing your transaction log
backups.
(if you don't care about transaction logs, you can avoid this problem in the
future by setting the database to SIMPLE RECOVERY.)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||You are at the mercy of the next break point of the virtual log file you are
currently writing to. Try adding in a bunch of data, then doing the backup
log ... with truncate thing then trying to shrink. I can take a LOT data to
get this to work.
Also, if it is a single file database, can you detach and reattach it, thus
getting the default tlog created? If so, make sure you immediately size the
tlog correctly, and set an appropriate growth rate.
--
TheSQLGuru
President
Indicium Resources, Inc.
"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce
> the
> initial size of the log. I am trying everything, including DBCC
> SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size
> in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.|||In addition to the other posts:
Some additional details here: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce the
> initial size of the log. I am trying everything, including DBCC SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.|||Hi MarkGsch,
Just check with you to see if you need further assistance on this issue.
To reduce the size of the log, you can run:
BACKUP LOG ... WITH TRUNCATE_ONLY
and then run DBCC SHRINKFILE to shrink the file to the size that you want.
If you have any other questions or concerns, please feel free to post back.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Cannot Shrink Log File

I have a Db w no open transactions, whose log file is 23GB and I cannot
shrink it below its initial size. Perhaps I need to know how to reduce the
initial size of the log. I am trying everything, including DBCC SHRINKFILE
(MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size in
Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
2005 SP2 x64 running in 64-bit mode). Thanks.
Try doing a
backup log <db_name>
with Truncate_only
and then try shrinking the file.
MG
"MarkGsch" wrote:

> I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce the
> initial size of the log. I am trying everything, including DBCC SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.
|||Do you mean that you created it at 23GB to begin with. How many VLF's are in
it? You may want to have a look at this section of BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2eb39bb3-f889-40be-9ca7-daf7d5107c46.htm
Andrew J. Kelly SQL MVP
"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce
> the
> initial size of the log. I am trying everything, including DBCC
> SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size
> in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.
|||"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce
> the
> initial size of the log. I am trying everything, including DBCC
> SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size
> in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.
Sounds like you're in FULL RECOVERY mode and no transaction log backups are
being made.
I'd try to make a transaction log backup first.
barring that, do a BACKUP LOG <dbname> with TRUNCATE_ONLY
Then immediately do a FULL backup and then start doing your transaction log
backups.
(if you don't care about transaction logs, you can avoid this problem in the
future by setting the database to SIMPLE RECOVERY.)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||You are at the mercy of the next break point of the virtual log file you are
currently writing to. Try adding in a bunch of data, then doing the backup
log ... with truncate thing then trying to shrink. I can take a LOT data to
get this to work.
Also, if it is a single file database, can you detach and reattach it, thus
getting the default tlog created? If so, make sure you immediately size the
tlog correctly, and set an appropriate growth rate.
TheSQLGuru
President
Indicium Resources, Inc.
"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce
> the
> initial size of the log. I am trying everything, including DBCC
> SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size
> in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.
|||In addition to the other posts:
Some additional details here: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce the
> initial size of the log. I am trying everything, including DBCC SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.
|||Hi MarkGsch,
Just check with you to see if you need further assistance on this issue.
To reduce the size of the log, you can run:
BACKUP LOG ... WITH TRUNCATE_ONLY
and then run DBCC SHRINKFILE to shrink the file to the size that you want.
If you have any other questions or concerns, please feel free to post back.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Cannot Shrink Log File

I have a Db w no open transactions, whose log file is 23GB and I cannot
shrink it below its initial size. Perhaps I need to know how to reduce the
initial size of the log. I am trying everything, including DBCC SHRINKFILE
(MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size i
n
Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
2005 SP2 x64 running in 64-bit mode). Thanks.Try doing a
backup log <db_name>
with Truncate_only
and then try shrinking the file.
--
MG
"MarkGsch" wrote:

> I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce th
e
> initial size of the log. I am trying everything, including DBCC SHRINKFIL
E
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size
in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.|||Do you mean that you created it at 23GB to begin with. How many VLF's are in
it? You may want to have a look at this section of BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2eb39bb3-f889-40be-9ca7-daf7
d5107c46.htm
Andrew J. Kelly SQL MVP
"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce
> the
> initial size of the log. I am trying everything, including DBCC
> SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size
> in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.|||"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce
> the
> initial size of the log. I am trying everything, including DBCC
> SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size
> in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.
Sounds like you're in FULL RECOVERY mode and no transaction log backups are
being made.
I'd try to make a transaction log backup first.
barring that, do a BACKUP LOG <dbname> with TRUNCATE_ONLY
Then immediately do a FULL backup and then start doing your transaction log
backups.
(if you don't care about transaction logs, you can avoid this problem in the
future by setting the database to SIMPLE RECOVERY.)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||You are at the mercy of the next break point of the virtual log file you are
currently writing to. Try adding in a bunch of data, then doing the backup
log ... with truncate thing then trying to shrink. I can take a LOT data to
get this to work.
Also, if it is a single file database, can you detach and reattach it, thus
getting the default tlog created? If so, make sure you immediately size the
tlog correctly, and set an appropriate growth rate.
TheSQLGuru
President
Indicium Resources, Inc.
"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce
> the
> initial size of the log. I am trying everything, including DBCC
> SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size
> in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.|||In addition to the other posts:
Some additional details here: http://www.karaszi.com/SQLServer/in...ink.as
p
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce th
e
> initial size of the log. I am trying everything, including DBCC SHRINKFIL
E
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size
in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.|||Hi MarkGsch,
Just check with you to see if you need further assistance on this issue.
To reduce the size of the log, you can run:
BACKUP LOG ... WITH TRUNCATE_ONLY
and then run DBCC SHRINKFILE to shrink the file to the size that you want.
If you have any other questions or concerns, please feel free to post back.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

Sunday, February 12, 2012

Cannot run job created by SMO

I've create a SQL Agent job using C# SMO to process an Analysis Service Database (see code below). When I tried to start job from Management Studio, I get the following error message...any ideas?

TITLE: Microsoft.SqlServer.Smo

Start failed for Job 'Schedule Job OLAPProj'.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1314.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Start+Job&LinkId=20476


ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot start the job "Schedule Job OLAPProj" (ID 089BEE15-B060-466E-B59E-7F7DAB1EB8DB) because it does not have any job server or servers defined. Associate the job with a job server by calling sp_add_jobserver. (Microsoft SQL Server, Error: 14256)



Common.ServerConnection l_oServerConn = new Common.ServerConnection();

l_oServerConn.ServerInstance = txtServer.Text;

l_oServerConn.LoginSecure = true;

l_oServerConn.Connect();

Smo.Server l_oServer = new Smo.Server(l_oServerConn);

Agent.Job l_oJob = new Agent.Job(l_oServer.JobServer, "Schedule Job " + cboDB.Text);

l_oJob.IsEnabled = true;

l_oJob.Description = "Schedule Job Processing OLAP Cube: " + cboDB.Text;

l_oJob.OwnerLoginName = @."NT AUTHORITY\SYSTEM";

l_oJob.Create();

Agent.JobStep l_oJobStep = new Agent.JobStep(l_oJob, "Process " + cboDB.Text);

l_oJobStep.SubSystem = Agent.AgentSubSystem.AnalysisCommand;

string l_sCmd = @."<Process xmlns=""http://schemas.microsoft.com/analysisservices/2003/engine"">";

l_sCmd = l_sCmd + "<Object><DatabaseID>" + cboDB.Text + "</DatabaseID></Object><Type>ProcessFull</Type>";

l_sCmd = l_sCmd + "<WriteBackTableCreation>UseExisting</WriteBackTableCreation></Process>";

l_oJobStep.Command = l_sCmd;

l_oJobStep.Server = txtServer.Text;

l_oJobStep.DatabaseName = @."master";

l_oJobStep.JobStepFlags = Agent.JobStepFlags.AppendToJobHistory;

l_oJobStep.OnSuccessAction = Agent.StepCompletionAction.QuitWithSuccess;

l_oJobStep.OnFailAction = Agent.StepCompletionAction.QuitWithFailure;

l_oJobStep.Create();


I have posted a sample here, which should clarify this. Please let me know if you have further issues after reading the sample.

|||Based on your sample, I've added the following which seemed to do the trick:

l_oJob.ApplyToTargetServer("(local)");

Initially I use "localhost\yukon" which is name of my instance but that failed and "(local)" worked. Thanks.