DBA guide

Any good SQLServer DBA guides available online? Preferably
a downloadable pdf. Or online in HTML.

Thank you.Hi

The best resource is Books Online!
http://www.microsoft.com/sql/techin.../2000/books.asp

This threads may help:
http://tinyurl.com/2pn4h
http://tinyurl.com/yuebf

John
"C.O.Jones" <nospam.ple@.se> wrote in message
news:dpLBc.2436$xy4.21713054@.news-text.cableinet.net...
> Any good SQLServer DBA guides available online? Preferably
> a downloadable pdf. Or online in HTML.
> Thank you.

db_owner role

I am getting this error message when disabling a job. The user is not a SA.

TITLE: Microsoft.SqlServer.Smo

Alter failed for Job 'XYZ'.

ADDITIONAL INFORMATION:

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

EXECUTE permission denied on object 'sp_help_operator', database 'msdb', owner 'dbo'. (Microsoft SQL Server, Error: 229)

The user can diasble the job if i give db_owner permission on msdb.

Is there a way i can do this without making the user db_owner?

Thanks for any help

There is no specific db_role for job administation, but you can create one. Grant the appropiate permission to the role that you need and assign the user to that role. You sure can also only grant the specific user the rights for that, but the next time you want another user to do the job you will have to repeat your work for that.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks.

Could you describe the "Grant the appropriate permission to the role that you need" in as little more detail as to what has to be done?

Thanks for the help

|||

I don′t know the complete list of permissions you will need for your work to be done, so you would have to go the iterative way to get your work done, here are the steps to complete:

-Create a db Role in the Database msdb (as you want to administer the alerts and things related to the SQL Agent)
-Assign users to this role that should do the administrative work.
-Assign the appropiate permissions to that db role (where the users are currently in) Put in the execute right for that procedure you get an error for.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

DB restore error to new server

Hi,
We have just built a new Windows 2003 Std Ed, SP1 server with SQL
Server 2005 no SP.
We are attempting to restore the databases from an old server to this
new one, however after the restore process gets to 100% we recive the
following error:
SQL Server detected a logical consistency-based I/O error: incorrect
checksum (expected: 0x65f3e4dc; actual: 0x65f3e4de). It occurred during
a read of page (1:32625) in database ID 7 at offset 0x0000000fee2000 in
file 'D:\sqldata\filename.mdf'. Additional messages in the SQL Server
error log or system event log may provide more detail. This is a severe
error condition that threatens database integrity and must be corrected
immediately. Complete a full database consistency check (DBCC CHECKDB)
Does anybody know what might be the problem?
Regards
Paul RobertsBob wrote:
> Hi,
> We have just built a new Windows 2003 Std Ed, SP1 server with SQL
> Server 2005 no SP.
> We are attempting to restore the databases from an old server to this
> new one, however after the restore process gets to 100% we recive the
> following error:
> SQL Server detected a logical consistency-based I/O error: incorrect
> checksum (expected: 0x65f3e4dc; actual: 0x65f3e4de). It occurred during
> a read of page (1:32625) in database ID 7 at offset 0x0000000fee2000 in
> file 'D:\sqldata\filename.mdf'. Additional messages in the SQL Server
> error log or system event log may provide more detail. This is a severe
> error condition that threatens database integrity and must be corrected
> immediately. Complete a full database consistency check (DBCC CHECKDB)
> Does anybody know what might be the problem?
> Regards
> Paul Roberts
>
Sounds like a corrupt backup. That error is occurring as SQL tries to
"recover" the new database to bring it online. It's telling you that
database has an internal problem of some kind. I would run a new backup
of the original database to restore from. Barring that, use DBCC
CHECKDB to attempt a repair of the restored database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi,
We tried that too with no luck.
This database is from a replicated database being restored to a
non-replication system.|||Bob wrote:
> Hi,
> We tried that too with no luck.
> This database is from a replicated database being restored to a
> non-replication system.
>
Tried the new backup, or tried running DBCC? If the new backup gave the
same error, you might want to run DBCC CHECKDB against the original
database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||The RESTORE command has a CONTINUE_AFTER_ERROR option. That should at lest g
et the database there,
so you can do something with it (like last resort emergency mode). But you s
hould really CHECKDB the
source database. If that db is clean, you shouldn't get corrupted backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bob" <pmroberts@.talk21.com> wrote in message
news:1152811634.990074.129800@.p79g2000cwp.googlegroups.com...
> Hi,
> We tried that too with no luck.
> This database is from a replicated database being restored to a
> non-replication system.
>|||2 questions -
1. How are you copying over the .bak file between the 2 machines?
2. Is the file sie the same?
"Bob" wrote:

> Hi,
> We have just built a new Windows 2003 Std Ed, SP1 server with SQL
> Server 2005 no SP.
> We are attempting to restore the databases from an old server to this
> new one, however after the restore process gets to 100% we recive the
> following error:
> SQL Server detected a logical consistency-based I/O error: incorrect
> checksum (expected: 0x65f3e4dc; actual: 0x65f3e4de). It occurred during
> a read of page (1:32625) in database ID 7 at offset 0x0000000fee2000 in
> file 'D:\sqldata\filename.mdf'. Additional messages in the SQL Server
> error log or system event log may provide more detail. This is a severe
> error condition that threatens database integrity and must be corrected
> immediately. Complete a full database consistency check (DBCC CHECKDB)
> Does anybody know what might be the problem?
> Regards
> Paul Roberts
>|||Hi,
The files are being copied using windows drag and drop.
The file sizes are the same.
I am using SQL server management studio, I cannot see the RESTORE
command has a CONTINUE_AFTER_ERROR, I tried this option under the
backup option but with no luck.
I also tried new backups.
Is there a limit to the size of the backups?|||How do I use DBCC CHECKDB?|||Bob wrote:
> Hi,
> The files are being copied using windows drag and drop.
> The file sizes are the same.
> I am using SQL server management studio, I cannot see the RESTORE
> command has a CONTINUE_AFTER_ERROR, I tried this option under the
> backup option but with no luck.
> I also tried new backups.
> Is there a limit to the size of the backups?
>
You might need to do the restore with the sql command in order to
specify the CONTINUE AFTER ERROR option.
Try to look up RESTORE Database in Books On Line.
Also, have you tried to restore the files to a different drive? It might
not be the case, but you could have a bad spot on the disk and that's
why it fails (..I know it's a wild guess but it should be fairly easy to
try...).
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||Hi,
Problem solved.
The issue is due to the fact that they are replication databases.
Since I am restoring to a different server, without replication, the
SQL server is supposed to realise this and remove replication status,
however it does not and you have to remove this manually with the
command: sp_removedbreplication dbname
You can then bring the database online.
See http://msdn2.microsoft.com/en-us/library/ms151782(d=ide).aspx
for more information.

DB restore error

Hi
I'm passing the following SQL statement to my SQLServer:
RESTORE DATABASE myDB FROM DISK 'd:\mssql\backup2\myDB.bak' WITH MOVE 'myDB'
TO 'd:\mssql\data\myDB.mdf', MOVE 'myDB_log' TO
'd:\mssql\data\myDB_log.ldf'
However, when I run this, I get the following error:
Server: Msg 3234, Level 16, State2, Line 1
Logical file 'myDB' is not part of database 'myDB'. Use RESTORE
FILELISTONLY to list the logical file names.
I've had a look in the help and I'm not really any the wiser. Can someone
explain to me what is occuring here?
Thanks
GriffOkay - understood it now...the "data" was called "myDB_Data" and not "myDB".
Griff|||Hi
Looks like you specify incorrect path to your log file or you don't have
the path created on hard disk.
Did you run FILELISTONLY as it is suggested ?
"GriffithsJ" <GriffithsJ_520@.hotmail.com> wrote in message
news:exzIowrzDHA.1576@.TK2MSFTNGP11.phx.gbl...
> Hi
> I'm passing the following SQL statement to my SQLServer:
> RESTORE DATABASE myDB FROM DISK 'd:\mssql\backup2\myDB.bak' WITH MOVE
'myDB'
> TO 'd:\mssql\data\myDB.mdf', MOVE 'myDB_log' TO
> 'd:\mssql\data\myDB_log.ldf'
> However, when I run this, I get the following error:
> Server: Msg 3234, Level 16, State2, Line 1
> Logical file 'myDB' is not part of database 'myDB'. Use RESTORE
> FILELISTONLY to list the logical file names.
> I've had a look in the help and I'm not really any the wiser. Can someone
> explain to me what is occuring here?
> Thanks
> Griff
>

DB REORG

Is there a concept similar to DB reorganization in SQL
Server? The reason I am asking this is because batch
updates have doubled in it's turnaround time. Looking at
indexes, everything is fine. I thought that, perhaps,
data should be reorganized in appropriate pages and free
space.
If you have other suggestions, I would be interested in
them.
Thanks in advance
It depends of exactly what you mean with REORG. First, read in Books Online about below commands:
DBCC SHOWCONTIG
DBCC DBREINDEX
DBCC INDEXDEFRAG
Then (a must :-) ), read below great whitepaper on the subject:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RG" <anonymous@.discussions.microsoft.com> wrote in message
news:0caf01c4a580$58557910$a601280a@.phx.gbl...
> Is there a concept similar to DB reorganization in SQL
> Server? The reason I am asking this is because batch
> updates have doubled in it's turnaround time. Looking at
> indexes, everything is fine. I thought that, perhaps,
> data should be reorganized in appropriate pages and free
> space.
> If you have other suggestions, I would be interested in
> them.
> Thanks in advance
|||Thanks for your help.
You have mentioned index defragmation. What about data
table defragmation?

>--Original Message--
>It depends of exactly what you mean with REORG. First,
read in Books Online about below commands:
>DBCC SHOWCONTIG
>DBCC DBREINDEX
>DBCC INDEXDEFRAG
>Then (a must :-) ), read below great whitepaper on the
subject:
>http://www.microsoft.com/technet/pro.../sql/2000/main
tain/ss2kidbp.mspx
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"RG" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0caf01c4a580$58557910$a601280a@.phx.gbl...
at
>
>.
>
|||This is why I asked what you mean by fragmentation and reorganization. Are you referring to re-claim
free space in your pages? Or making the data more contiguous?
Also, please consider that a table without a clustered index is a heap table. There is no order to
speak about here.
And for a table with a clustered index, the heap pages of the clustered index are your data pages.
I.e., to reorg such, you reorg the clustered index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RG" <anonymous@.discussions.microsoft.com> wrote in message
news:02aa01c4a592$7efe9bc0$a401280a@.phx.gbl...[vbcol=seagreen]
> Thanks for your help.
> You have mentioned index defragmation. What about data
> table defragmation?
>
> read in Books Online about below commands:
> subject:
> tain/ss2kidbp.mspx
> message
> at
|||All of the tables in question have clustered index. I am
looking to have the data stored contiguously because they
are pivotal tables in batch processing. In addition, I
would like to make sure I have my 10% free space per page
as batch is performing updates to these tables.
Thanks again
>--Original Message--
>This is why I asked what you mean by fragmentation and
reorganization. Are you referring to re-claim
>free space in your pages? Or making the data more
contiguous?
>Also, please consider that a table without a clustered
index is a heap table. There is no order to
>speak about here.
>And for a table with a clustered index, the heap pages of
the clustered index are your data pages.
>I.e., to reorg such, you reorg the clustered index.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"RG" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:02aa01c4a592$7efe9bc0$a401280a@.phx.gbl...
in[vbcol=seagreen]
free[vbcol=seagreen]
in
>
>.
>
|||That is just fine. All the information you need is in the sections in Books Online for the commands
I mentioned in my first post, and also make sure you read the KB article I posted an URL to in tat
same first post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RG" <anonymous@.discussions.microsoft.com> wrote in message
news:031d01c4a597$ec30ea90$a401280a@.phx.gbl...
> All of the tables in question have clustered index. I am
> looking to have the data stored contiguously because they
> are pivotal tables in batch processing. In addition, I
> would like to make sure I have my 10% free space per page
> as batch is performing updates to these tables.
> Thanks again

DB IN LOADING STATE?

I had to recover from a point in time with this script...
RESTORE LOG IT_DATABASE FROM
DISK=N'\\sqlserver\E$\SQLSERVER_BACKUPS\IT_DATABAS E.BAK'
WITH RECOVERY,STOPAT='03/02/2005 13:35:00'
Now my DB is in a loading state and don't know how to get
it back operational...Any help is greatly appreciated.
Thanks,
Hi Bruce,
It looks like IT_DATABASE.BAK is not a transactional log backup file. You
need to use log backup file for point in time recovery.
Try issuing
RESTORE DATABASE YourDatabase
WITH RECOVERY
......to recover from the loading state. Let us know, if the problem
persists.
Thanks
Yogish

DB error...please help!

Hi
I have a DB running in SQLServer 2000 that has worked fine for ages.
However, a recent data load failed. We check the DB using 'dbcc checkdb'
and it reported errors. We fixed the DB using 'dbcc checkdb
(REPAIR_ALLOW_DATA_LOSS )' and this appeared to fix the problem. However,
the DB has since corrupted twice more within a week. CheckDB run on the
master DB shows no problems.
Error messages:
These have been fairly varied. The error we recieve when doing an update
is:
--
Error -2147467259 - I/O error (bad page ID) detected during read at offset
0x0000001f220000 in file 'd:\mssql\data\[myDBName].mdf'
(This is followed by a whole series of connection failure errors for each
connection attempted during the data upload.)
--
Other error messages include:
--
Server: Msg 3624, Level 20, State 1, Line 1
Location: S:\sql\ntdbms\storeng\drs\include\record.inl:1447
Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROW
SPID: 75
Process ID: 824
Connection Broken
--
When we correct the errors using CheckDB, we get the following example
messages (should you wish to read):
Table error: Object ID -1291825152, index ID 12336, page ID (1:74256). The
PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 33792, page ID (1:74258). The PageId in
the page header = (0:0).
The error has been repaired.
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object
ID -1291825152)' (object ID -1291825152).
CHECKDB fixed 0 allocation errors and 1 consistency errors in table '(Object
ID -1291825152)' (object ID -1291825152).
DBCC results for '[myDataBaseName]'.
The error has been repaired.
CHECKDB found 0 allocation errors and 1 consistency errors not associated
with any single object.
CHECKDB fixed 0 allocation errors and 1 consistency errors not associated
with any single object.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 606625204, index ID 0: Page (1:74256) could not be processed. See
other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 606625204, index ID 0: Page (1:74257) could not be processed. See
other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 606625204, index ID 0: Page (1:74258) could not be processed. See
other errors for details.
Server: Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 606625204, index ID 1. Page (1:74256) was not seen in
the scan although its parent (1:74322) and previous (1:74271) refer to it.
Check any previous errors.
Server: Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 606625204, index ID 1. Index node page (1:74322),
slot 32 refers to child page (1:74257) and previous child (1:74256), but
they were not encountered.
Server: Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 606625204, index ID 1. Index node page (1:74322)
DBCC results for 'accountProducts'.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
The error has been repaired.
Server: Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 606625204, index ID 1. Page (1:74259) is missing a
reference from previous page (1:74258). Possible chain linkage problem.
The error has been repaired.
The error has been repaired.
Repair: Page (1:74256) has been deallocated from object ID 606625204, index
ID 0.
Repair: Page (1:74257) has been deallocated from object ID 606625204, index
ID 0.
Repair: Page (1:74258) has been deallocated from object ID 606625204, index
ID 0.
There are 3930517 rows in 64635 pages for object 'accountProducts'.
CHECKDB found 0 allocation errors and 7 consistency errors in table
'accountProducts' (object ID 606625204).
CHECKDB fixed 0 allocation errors and 7 consistency errors in table
'accountProducts' (object ID 606625204).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 1312043077, index ID 12341, page ID (1:74257). The
PageId in the page header = (0:1205993552).
There are 7602 rows in 54 pages for object 'crossSellingProducts'.
The error has been repaired.
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object
ID 1312043077)' (object ID 1312043077).
CHECKDB fixed 0 allocation errors and 1 consistency errors in table '(Object
ID 1312043077)' (object ID 1312043077).
CHECKDB found 0 allocation errors and 10 consistency errors in database
'[myDBName]'.
CHECKDB fixed 0 allocation errors and 10 consistency errors in database
'[myDBName]'.
--
Any ideas what may be the problem? More importantly, what to do about it?
Many thanks in advance!
GriffHello,
After looking at your problem I think its somehow one or
more of your updates is currupting one of your clusters,
in turn its effecting your pages.
Personally I would examine the data closely and try the
50/50 approach, i.e. load in 50% of the data see if there
is an error of not 50% of the remaining data and so on.
I wish I could give you some more help, but please feel
free to email me on (in reverse order)
ku.oc.elcannip@.nalon.retep.
Peter
>--Original Message--
>Hi
>I have a DB running in SQLServer 2000 that has worked
fine for ages.
>However, a recent data load failed. We check the DB
using 'dbcc checkdb'
>and it reported errors. We fixed the DB using 'dbcc
checkdb
>(REPAIR_ALLOW_DATA_LOSS )' and this appeared to fix the
problem. However,
>the DB has since corrupted twice more within a week.
CheckDB run on the
>master DB shows no problems.
>Error messages:
>These have been fairly varied. The error we recieve when
doing an update
>is:
>--
>Error -2147467259 - I/O error (bad page ID) detected
during read at offset
>0x0000001f220000 in file 'd:\mssql\data\[myDBName].mdf'
>(This is followed by a whole series of connection failure
errors for each
>connection attempted during the data upload.)
>--
>Other error messages include:
>--
>Server: Msg 3624, Level 20, State 1, Line 1
>Location:
S:\sql\ntdbms\storeng\drs\include\record.inl:1447
>Expression: m_SizeRec > 0 && m_SizeRec <= MAXDATAROW
>SPID: 75
>Process ID: 824
>Connection Broken
>--
>When we correct the errors using CheckDB, we get the
following example
>messages (should you wish to read):
>Table error: Object ID -1291825152, index ID 12336, page
ID (1:74256). The
>PageId in the page header = (0:0).
>Server: Msg 8909, Level 16, State 1, Line 1
>Table error: Object ID 0, index ID 33792, page ID
(1:74258). The PageId in
>the page header = (0:0).
> The error has been repaired.
>CHECKDB found 0 allocation errors and 1 consistency
errors in table '(Object
>ID -1291825152)' (object ID -1291825152).
>CHECKDB fixed 0 allocation errors and 1 consistency
errors in table '(Object
>ID -1291825152)' (object ID -1291825152).
>DBCC results for '[myDataBaseName]'.
> The error has been repaired.
>CHECKDB found 0 allocation errors and 1 consistency
errors not associated
>with any single object.
>CHECKDB fixed 0 allocation errors and 1 consistency
errors not associated
>with any single object.
>Server: Msg 8928, Level 16, State 1, Line 1
>Object ID 606625204, index ID 0: Page (1:74256) could not
be processed. See
>other errors for details.
>Server: Msg 8928, Level 16, State 1, Line 1
>Object ID 606625204, index ID 0: Page (1:74257) could not
be processed. See
>other errors for details.
>Server: Msg 8928, Level 16, State 1, Line 1
>Object ID 606625204, index ID 0: Page (1:74258) could not
be processed. See
>other errors for details.
>Server: Msg 8976, Level 16, State 1, Line 1
>Table error: Object ID 606625204, index ID 1. Page
(1:74256) was not seen in
>the scan although its parent (1:74322) and previous
(1:74271) refer to it.
>Check any previous errors.
>Server: Msg 8980, Level 16, State 1, Line 1
>Table error: Object ID 606625204, index ID 1. Index node
page (1:74322),
>slot 32 refers to child page (1:74257) and previous child
(1:74256), but
>they were not encountered.
>Server: Msg 8980, Level 16, State 1, Line 1
>Table error: Object ID 606625204, index ID 1. Index node
page (1:74322)
>
>DBCC results for 'accountProducts'.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
> The error has been repaired.
>Server: Msg 8978, Level 16, State 1, Line 1
>Table error: Object ID 606625204, index ID 1. Page
(1:74259) is missing a
>reference from previous page (1:74258). Possible chain
linkage problem.
> The error has been repaired.
> The error has been repaired.
>Repair: Page (1:74256) has been deallocated from object
ID 606625204, index
>ID 0.
>Repair: Page (1:74257) has been deallocated from object
ID 606625204, index
>ID 0.
>Repair: Page (1:74258) has been deallocated from object
ID 606625204, index
>ID 0.
>
>There are 3930517 rows in 64635 pages for
object 'accountProducts'.
>CHECKDB found 0 allocation errors and 7 consistency
errors in table
>'accountProducts' (object ID 606625204).
>CHECKDB fixed 0 allocation errors and 7 consistency
errors in table
>'accountProducts' (object ID 606625204).
>Server: Msg 8909, Level 16, State 1, Line 1
>Table error: Object ID 1312043077, index ID 12341, page
ID (1:74257). The
>PageId in the page header = (0:1205993552).
>There are 7602 rows in 54 pages for
object 'crossSellingProducts'.
> The error has been repaired.
>CHECKDB found 0 allocation errors and 1 consistency
errors in table '(Object
>ID 1312043077)' (object ID 1312043077).
>CHECKDB fixed 0 allocation errors and 1 consistency
errors in table '(Object
>ID 1312043077)' (object ID 1312043077).
>CHECKDB found 0 allocation errors and 10 consistency
errors in database
>'[myDBName]'.
>CHECKDB fixed 0 allocation errors and 10 consistency
errors in database
>'[myDBName]'.
>--
>Any ideas what may be the problem? More importantly,
what to do about it?
>Many thanks in advance!
>Griff
>
>.
>