SQL Server System Report

Question

SQL Server System Report

Viewing 15 posts - 166 through 180 (of 189 total)

You must be logged in to reply to this topic. Login to reply

Sean Smith SSCertifiable Points: 5977 More actions · Answer 1

My pleasure. If there's anything else just let me know. 🙂

Joe Zonum SSCommitted Points: 1606 More actions · Answer 2

Joe Zonum (5/6/2014)
It didn't work..
It still sent an email but the email was blank because no jobs failed.
I hoping to avoid blank emails when no jobs fail.

Was this the only change you made?

I am trying to learn how to be a better tsql coder.

Sean Smith SSCertifiable Points: 5977 More actions · Answer 3

That and I moved this to the end:

Skip_Agent_Jobs:

IF OBJECT_ID (N'tempdb.dbo.#temp_sssr_agent_jobs', N'U') IS NOT NULL

BEGIN

DROP TABLE dbo.#temp_sssr_agent_jobs

END

GO

Here are two great books if you want:

http://www.amazon.com/Sams-Teach-Yourself-SQL-Minutes/dp/0672336073/ref=sr_1_1?s=books&ie=UTF8&qid=1399427202&sr=1-1&keywords=sam%27s+teach+yourself+sql

http://www.amazon.com/Microsoft-Server-Fundamentals-Developer-Reference/dp/0735658145/ref=sr_1_1?s=books&ie=UTF8&qid=1399427180&sr=1-1&keywords=t-sql+fundamentals

Sean

Joe Zonum SSCommitted Points: 1606 More actions · Answer 4

Thank you Old Hand... I'll take a look at the books.

Joe

Sean Smith SSCertifiable Points: 5977 More actions · Answer 5

They are really great. The Sam's one gave me my start many years ago, the other showed me a lot of great advanced stuff.

Sean Smith SSCertifiable Points: 5977 More actions · Answer 6

Latest version with a bunch of changes now available. 🙂

marcovomhau Mr or Mrs. 500 Points: 527 More actions · Answer 7

Hi,

thanks for sharing this procedure. I tried this on one of our servers and wondering about the reserved_mb metric in the fourth recordset about databases.

In this resultset there are several databases where the reserved_mb is greater than the total_size_mb? how can this be?

example:

total_size_mb: 8605

unallocated_mb: 0

reserved_mb: 12103

data_mb: 3692

index_mb: 1839

unused_mb: 6573

cached_mb: 1222

greetings

Marco

Today is a good day to sql

Sean Smith SSCertifiable Points: 5977 More actions · Answer 8

Unfortunately, without having access to your environment I cannot say for sure. What does it show if you run this on the database in question:

EXEC sp_spaceused

marcovomhau Mr or Mrs. 500 Points: 527 More actions · Answer 9

thanks for your quick reply.

here is the output of the sp_spaceused proc.

database_size unallocated space

------------------ ------------------

8605.13 MB 0.00 MB

reserved data index_size unused

------------------ ------------------ ------------------ ------------------

12400080 KB 3780312 KB 1888000 KB 6731768 KB

Today is a good day to sql

Lee Linares SSCrazy Points: 2727 More actions · Answer 10

Sounds weird. If you look at the BOL entry for sp_space_used in Remarks it states: "database_size will always be larger than the sum of reserved + unallocated space because it includes the size of log files, but reserved and unallocated_space consider only data pages."

Try running this satement in that database: EXEC sp_spaceused @updateusage = N'TRUE';

Lee

Sean Smith SSCertifiable Points: 5977 More actions · Answer 11

Sean Smith

SSCertifiable

Points: 5977

September 22, 2014 at 6:49 am

#1747049

Thanks Lee. 🙂

marcovomhau Mr or Mrs. 500 Points: 527 More actions · Answer 12

executed

EXEC sp_spaceused @updateusage = N'TRUE';

and now the result looks much better

database_size unallocated space

------------------ ------------------

8605.13 MB 199.59 MB

reserved data index_size unused

------------------ ------------------ ------------------ ------------------

6207264 KB 3789840 KB 1860992 KB 556432 KB

turns out to be a good day today, because I have learned something 🙂

thanks lee.

@sean: maybe it it makes sense to use the updateuseage option in your procedure...

Today is a good day to sql

Lee Linares SSCrazy Points: 2727 More actions · Answer 13

That looks much better. I would disagree though about including the update usage option in Sean's code. Again from BOL: "updateusage can take some time to run on large tables or databases. Use updateusage only when you suspect incorrect values are being returned and when the process will not have an adverse effect on other users or processes in the database. "

Lee

Sean Smith SSCertifiable Points: 5977 More actions · Answer 14

I would have to agree with Lee on this one, just because of pressure it could put on the server.

george25 Mr or Mrs. 500 Points: 538 More actions · Answer 15

Sean

I've been using your report quite happily for a while now on a SQL 2008R2 server but it just started failing with this error:

There is insufficient result space to convert a money value to varchar. [SQLSTATE 22003] (Error 234). The step failed.

Can you help?