Apr 04

Sort or Search in sp_who2

Posted on April 4, 2013 by Geoff Pasley

Here is a simple script that allows you to sort and search for items in the sp_who2 stored procedure. Works with SQL 2005 and up:

CREATE TABLE #sp_who2 (
	SPID INT, 
	Status VARCHAR(1000) NULL, 
	Login SYSNAME NULL, 
	HostName SYSNAME NULL, 
	BlkBy SYSNAME NULL, 
	DBName SYSNAME NULL, 
	Command VARCHAR(1000) NULL, 
	CPUTime INT NULL, 
	DiskIO INT NULL, 
	LastBatch VARCHAR(1000) NULL, 
	ProgramName VARCHAR(1000) NULL, 
	SPID2 INT,
	REQUESTID INT
)

INSERT INTO #sp_who2 EXEC sp_who2

SELECT *
FROM #sp_who2
--WHERE DBName = 'databasename'

DROP TABLE #sp_who2

CREATE TABLE #sp_who2 (

SPID INT,

Status VARCHAR(1000) NULL,

HostName SYSNAME NULL,

BlkBy SYSNAME NULL,

DBName SYSNAME NULL,

Command VARCHAR(1000) NULL,

CPUTime INT NULL,

DiskIO INT NULL,

LastBatch VARCHAR(1000) NULL,

ProgramName VARCHAR(1000) NULL,

SPID2 INT,

REQUESTID INT

)

INSERT INTO #sp_who2 EXEC sp_who2

SELECT *

FROM #sp_who2

--WHERE DBName = 'databasename'

DROP TABLE #sp_who2

Mar 14

Cannot alter user dbo

Posted on March 14, 2013 by Geoff Pasley

I was recently faced with the task to alter a SQL user’s database account; more specifically speaking a Login name’s database user name. I ran into this error:

Cannot alter the user dbo. It seems as if SQL server is upset if you attempt to alter the account of the database owner (dbo). To get around this you will need to change the dbo to another account to modify the original user. Use the following code to change the database owner:

Use [database_name]
go
sp_changedbowner 'sa'

Use [database_name]

sp_changedbowner 'sa'

Feb 27

Definition of Transaction (ACID)

Posted on February 27, 2013 by Geoff Pasley

Just a quick bit of knowledge:

In the context of a transactional database, regardless of type (MSSQL, Oracle, MySQL, etc.), the following rules must apply to any transaction:

Atomic: the entire transaction must be committed. If a single part of the transaction fails the entire transaction must fail.
Consistent: each transaction that is committed to the database must not violate any keys, relations or constraints. In other words, a transaction in not allowed to leave a database in an inconsistent state.
Isolation: a transaction must be isolated from any other transaction running at that time. Transactions running concurrently must be executed as if they were sequential. If two transactions can/need to run concurrently they will need to operate isolated from the data that the other is manipulating.
Durable: once a transaction is committed it must become a permanent part of the database. It must be stored on a medium that can be powered off and the data will remain (i.e. Hard Disk, SSD, SAN, etc.). Durable also means that a confirmation of the committed transaction is provided.

Feb 22

Backup Detected Log Corruption

Posted on February 22, 2013 by Geoff Pasley

I’ve been dealing with several data corruption issues for the last few days. It all started with a loss of a clustered disk and when it was brought back online there were a few Suspect and Recovery Pending databases. Here is how I was able to solve that issue: Database in Suspect or Recovery Pending State

For a few other database CHECKDB returns no error for and FULL backups complete successfully; but when performing transaction log backups we receive this error:

Along with these error in the SQL Server Logs:

No need to stress…Take a deep breath. There is a simple solution for this issue, and it turns out there is no data loss occurring on the database. Here are the steps:

Continue reading →

Feb 21

Remove Mirroring and Setting Secondary Database Online

Posted on February 21, 2013 by Geoff Pasley

Scenario: The primary server participating in a database mirroring configuration has gone offline. It has been decided that there not enough time to troubleshoot bringing the primary server online and the mirror server will now assume the responsibility of the primary server.

Use this command to manually remove database mirroring and make the secondary database available for use.

Note: this will break mirroring, essentially creating 2 independent databases.

ALTER DATABASE [database_name] SET PARTNER OFF
GO
RESTORE DATABASE [database_name] WITH RECOVERY
GO

ALTER DATABASE [database_name] SET PARTNER OFF

RESTORE DATABASE [database_name] WITH RECOVERY

Feb 20

Sizes of ALL Database on an Instance

Posted on February 20, 2013 by Geoff Pasley

Revised 8/15/14:

Created a script that combined both syntax for SQL 2000 and 2005+ as well as showing used space and free space:

SET NOCOUNT ON
/*
 * Update usage statistics. Not a necessary step
 * but will provide more accurate results
 */
DBCC UPDATEUSAGE(0)
DECLARE @database_id_start INT
SET @database_id_start = 0 

/* Uncomment the line below to exclude master, model, msdb, temp */
SET @database_id_start = 4

CREATE TABLE #files (
	[database_id] [smallint] NOT NULL,
	[size_MB] [float] NULL,
	[used_mb] [float] NULL,
	[free_mb] [float] NULL
)

-- SQL 2000
IF (SELECT LEFT(CAST(SERVERPROPERTY('productversion') AS NVARCHAR(128)) , 1)) = '8'
BEGIN
	EXEC sp_msForEachDB 'Use [?]; INSERT INTO #files SELECT 
	DB_ID()
	,CAST(ROUND(([size]*8.0)/(1024),2) AS FLOAT) AS [size_MB]
	,CAST(ROUND((FILEPROPERTY([name],''SpaceUsed'')*8.0)/(1024),2) AS FLOAT) AS [used_mb]
	,CAST(ROUND((([size]-FILEPROPERTY([name],''SpaceUsed''))*8.0)/(1024),2) AS FLOAT) AS [free_mb]
	FROM dbo.sysfiles df'
END

-- SQL 2005+
ELSE
BEGIN
	EXEC sp_msForEachDB 'Use [?]; INSERT INTO #files SELECT 
	DB_ID()
	,CAST(ROUND(([size]*8.0)/(1024),2) AS FLOAT) AS [size_MB]
	,CAST(ROUND((FILEPROPERTY([name],''SpaceUsed'')*8.0)/(1024),2) AS FLOAT) AS [used_mb]
	,CAST(ROUND((([size]-FILEPROPERTY([name],''SpaceUsed''))*8.0)/(1024),2) AS FLOAT) AS [free_mb]
	FROM sys.database_files df'
END

/* Show individual databse sizes */
SELECT @@SERVERNAME AS [Server Name], DB_NAME([database_id]) AS [Database Name],
CAST(SUM([size_MB]/1024) AS NUMERIC(12,3)) AS [Size (in GB)],
CAST(SUM([used_MB]/1024) AS NUMERIC(12,3)) AS [Used (in GB)],
CAST(SUM([free_MB]/1024) AS NUMERIC(12,3)) AS [Free (in GB)]
FROM #files
WHERE database_id > @database_id_start
GROUP BY [database_id] ORDER BY [Database Name]

/* Show total for all databases on instance */
SELECT CAST(SUM([size_MB]/1024) AS NUMERIC(12,3)) AS [Total Size (in GB)],
CAST(SUM([used_MB]/1024) AS NUMERIC(12,3)) AS [Total Used (in GB)],
CAST(SUM([free_MB]/1024) AS NUMERIC(12,3)) AS [Total Free (in GB)]
FROM #files
WHERE database_id > @database_id_start

DROP TABLE #files

SET NOCOUNT ON

* Update usage statistics. Not a necessary step

* but will provide more accurate results

DBCC UPDATEUSAGE(0)

DECLARE @database_id_start INT

SET @database_id_start = 0

/* Uncomment the line below to exclude master, model, msdb, temp */

SET @database_id_start = 4

CREATE TABLE #files (

[database_id] [smallint] NOT NULL,

[size_MB] [float] NULL,

[used_mb] [float] NULL,

[free_mb] [float] NULL

)

-- SQL 2000

IF (SELECT LEFT(CAST(SERVERPROPERTY('productversion') AS NVARCHAR(128)) , 1)) = '8'

BEGIN

EXEC sp_msForEachDB 'Use [?]; INSERT INTO #files SELECT

DB_ID()

,CAST(ROUND(([size]*8.0)/(1024),2) AS FLOAT) AS [size_MB]

,CAST(ROUND((FILEPROPERTY([name],''SpaceUsed'')*8.0)/(1024),2) AS FLOAT) AS [used_mb]

,CAST(ROUND((([size]-FILEPROPERTY([name],''SpaceUsed''))*8.0)/(1024),2) AS FLOAT) AS [free_mb]

FROM dbo.sysfiles df'

END

-- SQL 2005+

ELSE

BEGIN

EXEC sp_msForEachDB 'Use [?]; INSERT INTO #files SELECT

DB_ID()

,CAST(ROUND(([size]*8.0)/(1024),2) AS FLOAT) AS [size_MB]

,CAST(ROUND((FILEPROPERTY([name],''SpaceUsed'')*8.0)/(1024),2) AS FLOAT) AS [used_mb]

,CAST(ROUND((([size]-FILEPROPERTY([name],''SpaceUsed''))*8.0)/(1024),2) AS FLOAT) AS [free_mb]

FROM sys.database_files df'

END

/* Show individual databse sizes */

SELECT @@SERVERNAME AS [Server Name], DB_NAME([database_id]) AS [Database Name],

CAST(SUM([size_MB]/1024) AS NUMERIC(12,3)) AS [Size (in GB)],

CAST(SUM([used_MB]/1024) AS NUMERIC(12,3)) AS [Used (in GB)],

CAST(SUM([free_MB]/1024) AS NUMERIC(12,3)) AS [Free (in GB)]

FROM #files

WHERE database_id > @database_id_start

GROUP BY [database_id] ORDER BY [Database Name]

/* Show total for all databases on instance */

SELECT CAST(SUM([size_MB]/1024) AS NUMERIC(12,3)) AS [Total Size (in GB)],

CAST(SUM([used_MB]/1024) AS NUMERIC(12,3)) AS [Total Used (in GB)],

CAST(SUM([free_MB]/1024) AS NUMERIC(12,3)) AS [Total Free (in GB)]

FROM #files

WHERE database_id > @database_id_start

DROP TABLE #files

Older post published 2/20/13:

————————————————————————————————————————–

I needed a simple way to show the sizes of each database on a given instance, as well as their total size. This was the solution I came up with:

SET NOCOUNT ON
/*
 * Update usage statistics. Not a necessary step
 * but will provide more accurate results
 */
DBCC UPDATEUSAGE(0)

DECLARE @start as int = 0 

/* Uncomment the line below to exclude master, model, msdb, temp */
--SET @start = 4

/* Show individual databse sizes */
SELECT @@SERVERNAME AS 'Server Name', DB_NAME(database_id) AS 'Database Name',
CAST(SUM(size*8./1024)/1024 as numeric(12,3)) as 'Size (in GB)'
FROM sys.master_files
WHERE database_id > @start
GROUP BY database_id ORDER BY [Database Name]

/* Show total for all databases on instance */
SELECT cast(SUM(size*8./1024)/1024 as numeric(12,3)) as 'Total Size (in GB)' 
FROM sys.master_files 
WHERE database_id > @start

SET NOCOUNT ON

* Update usage statistics. Not a necessary step

* but will provide more accurate results

DBCC UPDATEUSAGE(0)

DECLARE @start as int = 0

/* Uncomment the line below to exclude master, model, msdb, temp */

--SET @start = 4

/* Show individual databse sizes */

SELECT @@SERVERNAME AS 'Server Name', DB_NAME(database_id) AS 'Database Name',

CAST(SUM(size*8./1024)/1024 as numeric(12,3)) as 'Size (in GB)'

FROM sys.master_files

WHERE database_id > @start

GROUP BY database_id ORDER BY [Database Name]

/* Show total for all databases on instance */

SELECT cast(SUM(size*8./1024)/1024 as numeric(12,3)) as 'Total Size (in GB)'

FROM sys.master_files

WHERE database_id > @start

SQL 2000 Syntax: Continue reading →

Feb 19

SP_Send_Mail Common Settings

Posted on February 19, 2013 by Geoff Pasley

There are some common setting that I find myself using when sending mail via the sp_send_dbmail command. I like these two features the best:

@blind_copy_recipients
@from_address, which you can use to change the sender’s display name
- This feature (variable) is only supported in 2008 and above.

I’ve included an example of what I usually include in my send_mail calls:

EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'SQL DBA Group',
  @recipients = 'email(@)domain.com', 
  @blind_copy_recipients = 'email(@)domain.com', 
  @from_address = 'Display Name <no-reply(@)domain.com>',
  @body = 'Email Body.',
  @subject = 'Subject' 
GO

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'SQL DBA Group',

@recipients = 'email(@)domain.com',

@blind_copy_recipients = 'email(@)domain.com',

@from_address = 'Display Name <no-reply(@)domain.com>',

@body = 'Email Body.',

@subject = 'Subject'

Feb 18

Cannot Connect to WMI Provider

Posted on February 18, 2013 by Geoff Pasley

Today a client came to me with this error:

When running SQL Server Configuration Manager he received this error: “Cannot Connect to WMI Provider. You do not have permission or the server is unreachable.”

I’ve handled this type of case before and a simple restart for the SQL server instance solved the issue, but that wasn’t the case today.

It’s important to note that this machine was 64-bit and the solution was to use the x86 (32-bit) shared components of configuration manager.

Solution:

Open a CMD prompt, and Run as Administrator.
Navigate to the shared components directory of the instance.

cd "C:\Program Files\Microsoft SQL Server\100\Shared"

1

cd "C:\Program Files\Microsoft SQL Server\100\Shared"
- May vary based on you configuration and version.
- UPDATE: If the mofcomp.exe file does not exist in the shared directory see instructions below.
Run the following command (This will vary depending on your version on SQL server):

mofcomp.exe "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

1

mofcomp.exe "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"
- May vary based on you configuration and version.

You should see a message similar to the following:

UPDATE:

If the mofcomp.exe file does not exist in the SQL shared directory server perform a search for the file in the following directory: C:\Windows\winsxs

The result will be something similar to this: C:\Windows\winsxs\wow64_microsoft-windows-wmi-core.resources_31bf3856ad364e35_6.1.7600.16385_en-us_3b112a3415ba87ce

Replace the location for Step # 2 with the results of the search. Example:

cd "C:\Windows\winsxs\wow64_microsoft-windows-wmi-core.resources_31bf3856ad364e35_6.1.7600.16385_en-us_3b112a3415ba87ce"

1	cd "C:\Windows\winsxs\wow64_microsoft-windows-wmi-core.resources_31bf3856ad364e35_6.1.7600.16385_en-us_3b112a3415ba87ce"

Feb 15

SQL 2012 F5 Keyboard Shortcut Refresh

Posted on February 15, 2013 by Geoff Pasley

For some unknown reason, Microsoft decided to change the global refresh key (F5) in SQL 2012 to debug. This has caused me countless headaches when trying to refresh a database in the Object Explorer. Here is how I went about changing F5 from debug to refresh:

In SQL Server Management Studio, click Tools, then click Options
Under Environment, expand Keyboard then select Keyboard nested underneath:
Search from View.Refresh and highlight.
Under Press shortcut keys: press F5:
Click Assign, the screen should look like this:
Click OK and you’re done!

Stupid debug

Feb 14

Check SQL Version

Posted on February 14, 2013 by Geoff Pasley

My preferred way of getting a SQL version with an easy to read output:

SELECT SERVERPROPERTY ('edition') as  'Edition', 
SERVERPROPERTY ('productlevel') as 'Service Pack', 
SERVERPROPERTY('productversion') as 'Version Number'

SELECT SERVERPROPERTY ('edition') as 'Edition',

SERVERPROPERTY ('productlevel') as 'Service Pack',

SERVERPROPERTY('productversion') as 'Version Number'

Page 3 of 51 234 5

SQL Professional

SQL Server with Geoff Pasley