Consistent Formatting

If I create are report using a wizard, there are some color choices
available, how can I pick from this same list when I create a report with
the wizard?
How can I easily apply the same color theme to multiple reports?
TIA
DeanOn Jun 13, 2:34 pm, "Dean" <deanl...@.hotmail.com.nospam> wrote:
> If I create are report using a wizard, there are some color choices
> available, how can I pick from this same list when I create a report with
> the wizard?
> How can I easily apply the same color theme to multiple reports?
> TIA
> Dean
One option would be to physically copy the report and then modify the
controls, parameters, datasets, etc in it. Something else worth
looking into is if you can tie a report to a style sheet (css). This
option I don't have experience w/but is worth a shot. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultantsqlsql

Consistent Deadlock, need to identify the rogue PAGE lock

I'm having trouble tracking down a deadlock in a large multi-threaded application. I have been able to gather some information, but not quite enough to identify the two queries at fault.

I collected the following by turning on the -T1204 and -T3605 parameters:

Deadlock encountered .... Printing deadlock information
2006-04-13 09:13:48.70 spid3
2006-04-13 09:13:48.70 spid3 Wait-for graph
2006-04-13 09:13:48.70 spid3
2006-04-13 09:13:48.70 spid3 Node:1
2006-04-13 09:13:48.70 spid3 KEY: 10:1993058136:1 (aa0069f30857) CleanCnt:3 Mode: U Flags: 0x0
2006-04-13 09:13:48.70 spid3 Grant List 1::
2006-04-13 09:13:48.71 spid3 Owner:0x1a0d5b80 Mode: U Flg:0x0 Ref:1 Life:00000000 SPID:61 ECID:0
2006-04-13 09:13:48.71 spid3 SPID: 61 ECID: 0 Statement Type: UPDATE Line #: 1
2006-04-13 09:13:48.71 spid3 Input Buf: RPC Event: sp_execute;1
2006-04-13 09:13:48.71 spid3 Requested By:
2006-04-13 09:13:48.71 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:53 ECID:0 Ec:(0x1B4E54F0) Value:0x1a0d5ae0 Cost:(0/BA0)
2006-04-13 09:13:48.71 spid3
2006-04-13 09:13:48.71 spid3 Node:2
2006-04-13 09:13:48.71 spid3 PAG: 10:4:14898 CleanCnt:2 Mode: U Flags: 0x2
2006-04-13 09:13:48.71 spid3 Grant List 0::
2006-04-13 09:13:48.71 spid3 Owner:0x1a0c6bc0 Mode: U Flg:0x0 Ref:1 Life:00000000 SPID:58 ECID:0
2006-04-13 09:13:48.71 spid3 SPID: 58 ECID: 0 Statement Type: UPDATE Line #: 1
2006-04-13 09:13:48.71 spid3 Input Buf: RPC Event: sp_execute;1
2006-04-13 09:13:48.71 spid3 Requested By:
2006-04-13 09:13:48.71 spid3 ResType:LockOwner Stype:'OR' Mode: IU SPID:61 ECID:0 Ec:(0x1C2B54F0) Value:0x1a0d5440 Cost:(0/B98)
2006-04-13 09:13:48.71 spid3
2006-04-13 09:13:48.71 spid3 Node:3
2006-04-13 09:13:48.71 spid3 PAG: 10:4:14899 CleanCnt:2 Mode: IX Flags: 0x2
2006-04-13 09:13:48.71 spid3 Grant List 1::
2006-04-13 09:13:48.71 spid3 Owner:0x1a0c6e80 Mode: IX Flg:0x0 Ref:2 Life:02000000 SPID:55 ECID:0
2006-04-13 09:13:48.71 spid3 SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 1
2006-04-13 09:13:48.71 spid3 Input Buf: RPC Event: sp_execute;1
2006-04-13 09:13:48.71 spid3 Requested By:
2006-04-13 09:13:48.71 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:58 ECID:0 Ec:(0x1C0114F0) Value:0x1a0d5a20 Cost:(0/B98)
2006-04-13 09:13:48.71 spid3
2006-04-13 09:13:48.71 spid3 Node:4
2006-04-13 09:13:48.71 spid3 KEY: 10:1993058136:1 (aa0069f30857) CleanCnt:3 Mode: U Flags: 0x0
2006-04-13 09:13:48.71 spid3 Wait List:
2006-04-13 09:13:48.71 spid3 Owner:0x1a0d5ae0 Mode: U Flg:0x0 Ref:1 Life:00000000 SPID:53 ECID:0
2006-04-13 09:13:48.71 spid3 SPID: 53 ECID: 0 Statement Type: UPDATE Line #: 1
2006-04-13 09:13:48.71 spid3 Input Buf: RPC Event: sp_execute;1
2006-04-13 09:13:48.71 spid3 Requested By:
2006-04-13 09:13:48.71 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:55 ECID:0 Ec:(0x1BFB94F0) Value:0x1a0c6fc0 Cost:(0/BA0)
2006-04-13 09:13:48.71 spid3 Victim Resource Owner:
2006-04-13 09:13:48.71 spid3 ResType:LockOwner Stype:'OR' Mode: U SPID:58 ECID:0 Ec:(0x1C0114F0) Value:0x1a0d5a20 Cost:(0/B98)

I then ran the following queries: -

> select name from sysobjects where id=1993058136;
result: ACCOUNTARRANGEMENTSCHEDULES

> select name from sysindexes where indid=1 and id=1993058136;
result: PK_ACCOUNTARRANGEMENTSCHEDULES

> sp_help PK_ACCOUNTARRANGEMENTSCHEDULES
result: PK_ACCOUNTARRANGEMENTSCHEDULES , dbo , primary key cns , 2005-11-01 13:51:01.650

It looks like there is sql server selected a row lock for the ACCOUNTARRANGEMENTSCHEDULES table (sounds good), but for the UPDATE being done by nodes 2 and 3 a page lock is being used. Is there any way I can get more info on what the statement is or what table it is updating?
I found some more useful info, using the following command: -

dbcc traceon (3604)
dbcc page(10, 4, 14898,2)
dbcc traceoff (3604)

I received the following (amongst other things): -
m_objId = 1993058136

Which would indicate the page lock is on the same table (ACCOUNTARRANGEMENTSCHEDULES).|||

The page that the deadlock occurs on has little to do with what led to the deadlock.

This is the deadlock scenario:

Spid 61 -> owns KEY: 10:1993058136:1 (aa0069f30857)
-> requesting 10:4:14898
Spid 58 -> owns 10:4:14898
-> Requesting 10:4:14899
Spid 55 -> Owns 10:4:14899
-> requesting 10:1993058136:1 (aa0069f30857)

So spid 61 used an index to find a row it was interested in and the needed additional data off page 10:4:14898.

Spid 55 appears to have found a row it intends to update on page 10:4:14899, and now needs a key lock to perform the necessary index maintenance.

Spid 58 appears to be scanning as it is requesting the page with the next ID, which likely just happens to be the next page in the list.

All 3 of these spids are exectuting some kind of prepared statement that does an update, as evidenced by the "sp_execute" statement in the input buffer.

To get to the bottom of this you're going to need to look at the exectution plan of these statements. Use profiler and gather the showplan all, sp:stmtstarting, sp:stmtcompleted, rpc starting and rpc completed events. If the plan shows (as I suspect it will) something like CLUSTERED INDEX SCAN, then you need to think about proper indexes to support these statements.

|||Unfortunately, whenever I use the profiler (even if I only turn on Deadlock and Deadlock Chain events) it seems to change the timings enough that the deadlock is not produced.

I don't know how I can change the indexes. The table has a primary key, which is a composite of two columns. Since a primary key exists, why is a page lock being used? The only thing that I can think that could be causing it, is the fact that an updateable jdbc ResultSet is being used by one of the queries. So it's not strictly an UPDATE, it's a SELECT (that selects only 1 row) then an updateRow.

This deadlock only appeared when introducing sp4. With sp3, no deadlocks occur. Has the lock selection algorithm changed?
|||The lock selection algorithm has not changed. It's possible that you have a different plan now. Even if you don't hit the deadlock the plans themselves can be instructive. If you see Scan's in the plan you want to understand why. Of course a Scan is going to cause each page in an index to be read and locked. The fewer pages that are locked the fewer chances there are of encountering a blocking, or deadlocking scenario.

Consistent Backup Failures - Please Help

I am not quite a DBA, and this issue has me at the end of my rope.
We have a large (150 GB) database on Win2K3 Standard Ed w/ SQL Server 2000.
This backup takes over 8 hours to complete. Each night toward the end, our
backup job fails with the following error:
BackupMedium::ReportIoError: write failure on backup device
'F:\ourbackupfile.BAK'. Operating system error 33(The process cannot access
the file because another process has locked a portion of the file.).
For the life of me, I cannot determine what process is locking the file. I
have tried executing the backup with the database in Single User mode, and
it still fails.
Are there any utilities that I can use to spy on the access to the file, so
I can turn them off?
ANY help is really appreciated. Thanks.
Ive discovered the drives are all compressed (and the accompanying KBs that
say its bad to backup to compressed drives)...
"codejockey" <cj@.hotmail.com> wrote in message
news:#nAnWiISEHA.2468@.TK2MSFTNGP11.phx.gbl...
> I am not quite a DBA, and this issue has me at the end of my rope.
> We have a large (150 GB) database on Win2K3 Standard Ed w/ SQL Server
2000.
> This backup takes over 8 hours to complete. Each night toward the end, our
> backup job fails with the following error:
> BackupMedium::ReportIoError: write failure on backup device
> 'F:\ourbackupfile.BAK'. Operating system error 33(The process cannot
access
> the file because another process has locked a portion of the file.).
> For the life of me, I cannot determine what process is locking the file. I
> have tried executing the backup with the database in Single User mode, and
> it still fails.
> Are there any utilities that I can use to spy on the access to the file,
so
> I can turn them off?
> ANY help is really appreciated. Thanks.
>
|||You might try FileMon from sysinternals (http://www.sysinternals.com).
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"codejockey" <cj@.hotmail.com> wrote in message
news:%23nAnWiISEHA.2468@.TK2MSFTNGP11.phx.gbl...
> I am not quite a DBA, and this issue has me at the end of my rope.
> We have a large (150 GB) database on Win2K3 Standard Ed w/ SQL Server
2000.
> This backup takes over 8 hours to complete. Each night toward the end, our
> backup job fails with the following error:
> BackupMedium::ReportIoError: write failure on backup device
> 'F:\ourbackupfile.BAK'. Operating system error 33(The process cannot
access
> the file because another process has locked a portion of the file.).
> For the life of me, I cannot determine what process is locking the file. I
> have tried executing the backup with the database in Single User mode, and
> it still fails.
> Are there any utilities that I can use to spy on the access to the file,
so
> I can turn them off?
> ANY help is really appreciated. Thanks.
>
|||codejockey,
Annoying isn't it? I have had this a lot and 99% of the time it has been
a tape backup job that has the file locked copying it to tape. Quite
often the tape backup program is awaiting someone to change the tape as
the tape is full.
sysinternals.com is a great resource as Peter mentioned. FileMon should
highlight the culprit.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
codejockey wrote:
> I am not quite a DBA, and this issue has me at the end of my rope.
> We have a large (150 GB) database on Win2K3 Standard Ed w/ SQL Server 2000.
> This backup takes over 8 hours to complete. Each night toward the end, our
> backup job fails with the following error:
> BackupMedium::ReportIoError: write failure on backup device
> 'F:\ourbackupfile.BAK'. Operating system error 33(The process cannot access
> the file because another process has locked a portion of the file.).
> For the life of me, I cannot determine what process is locking the file. I
> have tried executing the backup with the database in Single User mode, and
> it still fails.
> Are there any utilities that I can use to spy on the access to the file, so
> I can turn them off?
> ANY help is really appreciated. Thanks.
>

Consistent Backup Failures - Please Help

I am not quite a DBA, and this issue has me at the end of my rope.
We have a large (150 GB) database on Win2K3 Standard Ed w/ SQL Server 2000.
This backup takes over 8 hours to complete. Each night toward the end, our
backup job fails with the following error:
BackupMedium::ReportIoError: write failure on backup device
'F:\ourbackupfile.BAK'. Operating system error 33(The process cannot access
the file because another process has locked a portion of the file.).
For the life of me, I cannot determine what process is locking the file. I
have tried executing the backup with the database in Single User mode, and
it still fails.
Are there any utilities that I can use to spy on the access to the file, so
I can turn them off?
ANY help is really appreciated. Thanks.Ive discovered the drives are all compressed (and the accompanying KBs that
say its bad to backup to compressed drives)...
"codejockey" <cj@.hotmail.com> wrote in message
news:#nAnWiISEHA.2468@.TK2MSFTNGP11.phx.gbl...
> I am not quite a DBA, and this issue has me at the end of my rope.
> We have a large (150 GB) database on Win2K3 Standard Ed w/ SQL Server
2000.
> This backup takes over 8 hours to complete. Each night toward the end, our
> backup job fails with the following error:
> BackupMedium::ReportIoError: write failure on backup device
> 'F:\ourbackupfile.BAK'. Operating system error 33(The process cannot
access
> the file because another process has locked a portion of the file.).
> For the life of me, I cannot determine what process is locking the file. I
> have tried executing the backup with the database in Single User mode, and
> it still fails.
> Are there any utilities that I can use to spy on the access to the file,
so
> I can turn them off?
> ANY help is really appreciated. Thanks.
>|||You might try FileMon from sysinternals (http://www.sysinternals.com).
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"codejockey" <cj@.hotmail.com> wrote in message
news:%23nAnWiISEHA.2468@.TK2MSFTNGP11.phx.gbl...
> I am not quite a DBA, and this issue has me at the end of my rope.
> We have a large (150 GB) database on Win2K3 Standard Ed w/ SQL Server
2000.
> This backup takes over 8 hours to complete. Each night toward the end, our
> backup job fails with the following error:
> BackupMedium::ReportIoError: write failure on backup device
> 'F:\ourbackupfile.BAK'. Operating system error 33(The process cannot
access
> the file because another process has locked a portion of the file.).
> For the life of me, I cannot determine what process is locking the file. I
> have tried executing the backup with the database in Single User mode, and
> it still fails.
> Are there any utilities that I can use to spy on the access to the file,
so
> I can turn them off?
> ANY help is really appreciated. Thanks.
>|||codejockey,
Annoying isn't it? I have had this a lot and 99% of the time it has been
a tape backup job that has the file locked copying it to tape. Quite
often the tape backup program is awaiting someone to change the tape as
the tape is full.
sysinternals.com is a great resource as Peter mentioned. FileMon should
highlight the culprit.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
codejockey wrote:
> I am not quite a DBA, and this issue has me at the end of my rope.
> We have a large (150 GB) database on Win2K3 Standard Ed w/ SQL Server 2000
.
> This backup takes over 8 hours to complete. Each night toward the end, our
> backup job fails with the following error:
> BackupMedium::ReportIoError: write failure on backup device
> 'F:\ourbackupfile.BAK'. Operating system error 33(The process cannot acces
s
> the file because another process has locked a portion of the file.).
> For the life of me, I cannot determine what process is locking the file. I
> have tried executing the backup with the database in Single User mode, and
> it still fails.
> Are there any utilities that I can use to spy on the access to the file, s
o
> I can turn them off?
> ANY help is really appreciated. Thanks.
>

Consistent Backup Failures - Please Help

I am not quite a DBA, and this issue has me at the end of my rope.
We have a large (150 GB) database on Win2K3 Standard Ed w/ SQL Server 2000.
This backup takes over 8 hours to complete. Each night toward the end, our
backup job fails with the following error:
BackupMedium::ReportIoError: write failure on backup device
'F:\ourbackupfile.BAK'. Operating system error 33(The process cannot access
the file because another process has locked a portion of the file.).
For the life of me, I cannot determine what process is locking the file. I
have tried executing the backup with the database in Single User mode, and
it still fails.
Are there any utilities that I can use to spy on the access to the file, so
I can turn them off?
ANY help is really appreciated. Thanks.Ive discovered the drives are all compressed (and the accompanying KBs that
say its bad to backup to compressed drives)...
"codejockey" <cj@.hotmail.com> wrote in message
news:#nAnWiISEHA.2468@.TK2MSFTNGP11.phx.gbl...
> I am not quite a DBA, and this issue has me at the end of my rope.
> We have a large (150 GB) database on Win2K3 Standard Ed w/ SQL Server
2000.
> This backup takes over 8 hours to complete. Each night toward the end, our
> backup job fails with the following error:
> BackupMedium::ReportIoError: write failure on backup device
> 'F:\ourbackupfile.BAK'. Operating system error 33(The process cannot
access
> the file because another process has locked a portion of the file.).
> For the life of me, I cannot determine what process is locking the file. I
> have tried executing the backup with the database in Single User mode, and
> it still fails.
> Are there any utilities that I can use to spy on the access to the file,
so
> I can turn them off?
> ANY help is really appreciated. Thanks.
>|||You might try FileMon from sysinternals (http://www.sysinternals.com).
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"codejockey" <cj@.hotmail.com> wrote in message
news:%23nAnWiISEHA.2468@.TK2MSFTNGP11.phx.gbl...
> I am not quite a DBA, and this issue has me at the end of my rope.
> We have a large (150 GB) database on Win2K3 Standard Ed w/ SQL Server
2000.
> This backup takes over 8 hours to complete. Each night toward the end, our
> backup job fails with the following error:
> BackupMedium::ReportIoError: write failure on backup device
> 'F:\ourbackupfile.BAK'. Operating system error 33(The process cannot
access
> the file because another process has locked a portion of the file.).
> For the life of me, I cannot determine what process is locking the file. I
> have tried executing the backup with the database in Single User mode, and
> it still fails.
> Are there any utilities that I can use to spy on the access to the file,
so
> I can turn them off?
> ANY help is really appreciated. Thanks.
>|||codejockey,
Annoying isn't it? I have had this a lot and 99% of the time it has been
a tape backup job that has the file locked copying it to tape. Quite
often the tape backup program is awaiting someone to change the tape as
the tape is full.
sysinternals.com is a great resource as Peter mentioned. FileMon should
highlight the culprit.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
codejockey wrote:
> I am not quite a DBA, and this issue has me at the end of my rope.
> We have a large (150 GB) database on Win2K3 Standard Ed w/ SQL Server 2000.
> This backup takes over 8 hours to complete. Each night toward the end, our
> backup job fails with the following error:
> BackupMedium::ReportIoError: write failure on backup device
> 'F:\ourbackupfile.BAK'. Operating system error 33(The process cannot access
> the file because another process has locked a portion of the file.).
> For the life of me, I cannot determine what process is locking the file. I
> have tried executing the backup with the database in Single User mode, and
> it still fails.
> Are there any utilities that I can use to spy on the access to the file, so
> I can turn them off?
> ANY help is really appreciated. Thanks.
>