Script to Prepare Database Server Inventory

Posted on December 14, 2010 by uday arumilli

Script to Prepare Database Server Inventory

It’s really been a long time. Yah recently we @ our organization are requested to automate the process of building server and database inventory. We came up with the script which can capture sever / Instance / Database details from all the servers that are existed in a domain.  Since we are strictly restricted to do not use the linked servers looked for the next option “Accessing System Registry values”. No luck we miss either 64-bit instances or 32-bit instances or if the installation is not done properly. Finally we could find the way to build the inventory using “XP_CMDSHELL”.

For the future purpose and to create essential reports as well we are going to store the inventory information in relational objects. Here is the object structure.

 

 

 

Script for creating Tables

CREATE TABLE Environment (
ID TINYINT IDENTITY PRIMARY KEY,
Name VARCHAR(30) NOT NULL,
Deleted BIT NOT NULL DEFAULT(0))

CREATE TABLE Servers (
ID SMALLINT IDENTITY PRIMARY KEY,
Server_Name VARCHAR(100) NOT NULL,
OS_Version VARCHAR(100),
EnvironmentID TINYINT REFERENCES Environment(ID),
IPAddress VARCHAR(20),
Deleted BIT NOT NULL DEFAULT(0))

CREATE TABLE InstanceInventory (
ID SMALLINT IDENTITY PRIMARY KEY,
ServerID SMALLINT REFERENCES Servers (ID) NOT NULL,
InstanceName VARCHAR(100)NOT NULL,
Version VARCHAR(100) NULL,
Edition VARCHAR(100) NULL,
Deleted BIT NOT NULL DEFAULT(0))

CREATE TABLE DBInventory(
ID SMALLINT IDENTITY,
InstanceInventoryID SMALLINT REFERENCES InstanceInventory (ID)NOT NULL,
DBName VARCHAR(100) NOT NULL,
CompatabilityLevel TINYINT NULL,
Deleted BIT NOT NULL DEFAULT(0))

Script to Creating Stored procedures
Now we are going to create two stored procedures. “usp_Inventory_Instances” and “usp_Inventory_Databases”
———————————————-
———————————————-
GETTING INSTANCE DETAILS FOR ENVIRONMENT MAP———————————————-
———————————————-

 

CREATE PROC usp_Inventory_Instances(@ServerName VARCHAR(50))
AS
BEGIN
SET NOCOUNT ON
DECLARE @Instances TABLE(ServerName VARCHAR(100))
DECLARE @MachedInstances TABLE (ID INT IDENTITY,ServerName VARCHAR(50))
DECLARE @Counter SMALLINT
DECLARE @I SMALLINT
DECLARE @InstanceName VARCHAR(50)
DECLARE @ServerID SMALLINT
DECLARE @InstanceID SMALLINT
SET @ServerID=0
SELECT @ServerID=ID FROM Servers WHERE Server_Name=@ServerName
IF (@ServerID=0)
BEGIN
RETURN 0
END

INSERT INTO @Instances EXEC XP_CMDSHELL ‘SQLCMD -L’
INSERT INTO @MachedInstances (ServerName) SELECT LTRIM(RTRIM(ServerName)) FROM @Instances WHERE ServerName LIKE ‘%’+@ServerName+’%’
–SELECT * FROM @MachedInstances
SELECT @Counter=COUNT(1) FROM @MachedInstances
SET @i=1
BEGIN TRAN
BEGIN TRY
WHILE(@Counter>=@i)
BEGIN
SELECT @InstanceName=ServerName FROM @MachedInstances WHERE ID=@i
IF NOT EXISTS(SELECT 1 FROM InstanceInventory WHERE ServerID=@ServerID AND InstanceName=@InstanceName)
BEGIN
INSERT INTO InstanceInventory (ServerID,InstanceName) VALUES(@ServerID,@InstanceName)
SET @InstanceID=SCOPE_IDENTITY()
EXEC usp_Inventory_Databases @InstanceID
END
ELSE
BEGIN
SELECT ‘Instance Details are already exists in our database’
END
SET @i=@i+1
END
UPDATE INSTANCEINVENTORY SET DELETED=1 WHERE ID NOT IN(SELECT INSTANCEINVENTORYID FROM DBINVENTORY)
PRINT ‘All instances and respective database details Inserted Successfully’
END TRY
BEGIN CATCH
ROLLBACK TRAN
RETURN 0
END CATCH
COMMIT
END

———————————————-
———————————————-
GETTING DATABASE DETAILS FOR ENVIRONMENT MAP———————————————-
———————————————-
CREATE PROC usp_Inventory_Databases(@InstanceID SMALLINT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Databases TABLE(Name VARCHAR(1000))
DECLARE @Databases1 TABLE(ID INT IDENTITY,Name VARCHAR(100))
DECLARE @String VARCHAR(200)
DECLARE @InstanceName VARCHAR(100)
DECLARE @Count TINYINT
DECLARE @i TINYINT
DECLARE @DBName VARCHAR(1000)
SELECT @InstanceName=InstanceName FROM InstanceInventory WHERE ID=@InstanceID

SET @String=’SQLCMD -S ‘+@InstanceName+’ -q “SET NOCOUNT ON;USE MASTER;SELECT Name FROM SYSDATABASES”‘

INSERT INTO @Databases EXEC XP_CMDSHELL @String

IF EXISTS(SELECT 1 FROM @Databases WHERE Name LIKE’%Sqlcmd:%Error%’ OR Name LIKE ‘%HResult%Level%’ OR Name LIKE ‘%Login failed%’)
BEGIN
RETURN 0
END

DELETE @Databases WHERE Name=’Name’ OR Name LIKE ‘%——-%’ OR Name IS NULL OR Name IN(‘master’,’tempdb’,’model’,’msdb’) OR Name LIKE ‘%Changed database context%’
UPDATE @Databases SET Name=LTRIM(RTRIM(Name))

INSERT INTO @Databases1 (Name) SELECT  Name FROM @Databases
SET @i=1
SELECT @Count=COUNT(1) FROM @Databases1
WHILE(@Count>=@i)
BEGIN
SELECT @DBName=Name FROM @Databases1 WHERE ID=@i
INSERT INTO DBInventory (InstanceInventoryID,DBName) VALUES(@InstanceID,@DBName)
SET @i=@i+1
END
END

Executing Stored Procs:
Now run the below statement.EXEC usp_Inventory_Instances ‘NameofTheServer’
Ex:  EXEC usp_Inventory_Instances ‘MSSQLSERVER2005’
Now you will be able to find the existing instance details from the table “InstanceInventory” and the corresponding databases details stored on “DBInventory”
Posted in SQL Scripts, SQL Server DBA | Tagged , , , , , | 2 Comments

SQL Server Connectivity Issues

Posted on July 1, 2010 by uday arumilli

SQL Server Connectivity Issues

 

Title

SQL Connectivity Issues with Resolutions

Issue/Problem Description

Customer sends in a request to troubleshoot a connectivity issue follow the below steps

Action/Resolution Information

Error Message “53”:

[SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [53].
[SQL Native Client]Login timeout expired
[SQL Native Client] An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

-OR-

 

HResult 0x35, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [53].
Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..

Error: Microsoft SQL Native Client : Login timeout expired.

 

This indicates that your target server can not be accessed or does not exist. Try to use “ping ” , ” ping ” , “ping -a “, If either of the pings time out, fail, or do not return the correct values, then either the DNS lookup is not working properly or there is some other networking or routing issue that you will need to resolve.

 

Error Message “2”:

[SQL Native Client]Named Pipes Provider: Could not open a connection to SQL

Server [2].

[SQL Native Client]Login timeout expired

[SQL Native Client]An error has occurred while establishing a connection to

the server. When connecting to SQL Server 2005, this failure may be caused by

the fact that under the default settings SQL Server does not allow remote connections.

-OR-

HResult 0x2, Level 16, State 1

Named Pipes Provider: Could not open a connection to SQL Server [2].

Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..

Error: Microsoft SQL Native Client : Login timeout expired.

This error message means that the server was not found or not running or cannot make Named Pipe connection through the pipe name that client specified. To verify this:

 

1.  From the command line, do “sc query mssqlserver” or “sc query mssql$” to check whether sql instance present.  Then open sql server configuration manager -> check the state of the sql service, if it is not running, start it. If server started fail, check event log or server error log, see what happened there.

2.  If you are sure the service is running and shared memory/Named Pipe enabled, please try connection if it is local default instance “osql /Snp:\\.\pipe\sql\query”; or try connection “osql /Snp:\\.\pipe\mssql$\sql\query” if it is local named instance. if you still get error 2, then go to step 3).

3.  Double check the server is started and listening on named pipe if you enabled Named Pipe. One way is that see the ERRORLOG of the server, search follow keywords: Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ] or [\\.\pipe\mssql$\sql\query] Notice that “sql\query” is the default pipe name, so you need to know server is listening on which pipe name. eg: if you specify server pipe name is “sql\query1”, then you would see in the errorlog that server listening on [ \\.\pipe\sql\query1 ].

4.  See your connection string, whether you explicitly specify the pipe name and does it match the pipe that server is listening on? Or whether you just specify server name( like “.”,”(local)”, etc), but you specify the wrong pipe name on client side Named Pipe configuration.eg, go to SQL Server Configuration Manager, click client Named Pipe properties, see whether the pipe name is same with the one server listening on.

Note: For remote connection, you need to verify step 2) and 3).

 

Error Message “233”:

 

[SQL Native Client]Named Pipes Provider: Could not open a connection to SQL

Server [233].

[SQL Native Client]Login timeout expired

[SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

 

-OR-

 

HResult 0xE9, Level 16, State 1

Named Pipes Provider: Could not open a connection to SQL Server [233].

Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..

Error: Microsoft SQL Native Client : Login timeout expired.

 

Note: the difference between Message 2 and Message 1 is “Could not open a connection to SQL Server [233].” – error state.

 

You might specify the server name as FQDN/127.0.0.1/ IP Address and NP was disabled on the server. To resolve this, first way is to replace server name as the machine name or “.” or”(local)” or “” and you should be able to connect as long as server listening on Shared Memory; second way is to enable named pipe from sql configuration manager and restart server.

 

Message “No process is on the other end of the pipe”:

 

[SQL Native Client]Named Pipes Provider: No process is on the other end of the pipe.

[SQL Native Client]Communication link failure

[SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

 

-OR-

 

HResult 0xE9, Level 16, State 1

Named Pipes Provider: No process is on the other end of the pipe.

Error: Microsoft SQL Native Client : Communication link failure.

Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..

 

1  Local connection: You might specify the server name as FQDN/127.0.0.1/ IP Address in the connection string and speculate connection through Named Pipe provider. To resolve this, either change server name to as long as the server is listening on Shared Memory or enabled NP.

2  Remote connection: the server is not listening on Name Pipe. To resolve this, enable name pipe on the remote server and restart the server.

 

Message “[xFFFFFFFF]”:

 

[SQL Native Client]SQL Network Interfaces: Server doesn’t support requested protocol [xFFFFFFFF].

[SQL Native Client]Login timeout expired

[SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

 

-OR-

 

HResult 0xFFFFFFFF, Level 16, State 1

SQL Network Interfaces: Server doesn’t support requested protocol [xFFFFFFFF].

Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..

Error: Microsoft SQL Native Client : Login timeout expired.

 

1  Local connection: You might explicitly specify protocol prefix “np:” and connect to a named instance and NP was disabled.

2  Remote connection: You might connect to remote named instance and remote server is not listening on name pipe.

To resolve this, enable NP if you only want to use name pipe protocol or you can remove “np:” prefix to let connection over shared memory locally.

 

Message “[0xFFFFFFFF]”:

 

[SQL Native Client]SQL Network Interfaces: Error Locating Server/Instance Specified [0xFFFFFFFF].

[SQL Native Client]Login timeout expired

[SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

-OR-

HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not

allow remote connections..

Error: Microsoft SQL Native Client : Login timeout expired.

You might connect to local named instance and explicitly specify protocol prefix “tcp:” or “np:” in the connection string, however, SqlBrowser service was not running.

To resolve this, you should enable Sqlbrowser service on the server

1  Use net start or go to sql configuration manager(SSCM), check whether sqlbrowser service is running, if not, start it.

2  You still need to make sure SqlBrowser is active. Go to SSCM, click properties of sqlbrowser service -> Advanced-> Active “Yes” or “No”, if sqlbrowser is running but is not active, the service would not serve you correct pipe name and Tcp port info on which your connection depends.

 

Error Message “Shared Memory provider error“:

 

HResult 0x2, Level 16, State 1

Shared Memory Provider: Could not open a connection to SQL Server [2].

Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..

Error: Microsoft SQL Native Client : Login timeout expired.

 

-OR-

 

[SQL Native Client]Shared Memory Provider: Could not open a connection to SQLServer [2].

[SQL Native Client]Login timeout expired

[SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

The error indicate you speculated shared memory as connection protocol and server is not listening on shared memory, plus you probably specified “.”/”(local)”/ /localhost as the server name in the connection string. To resolve this, enable shared memory protocol and restart the server.

Error Message “87”:

[SQL Native Client]SQL Network Interfaces: Cannot open a Shared Memory connection to a remote SQL server [87].

[SQL Native Client]Login timeout expired

[SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

-OR-

HResult 0x57, Level 16, State 1

SQL Network Interfaces: Cannot open a Shared Memory connection to a remote SQL server [87].

Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not  allow remote connections..

Error: Microsoft SQL Native Client : Login timeout expired.

The reason is same as the one of Message 6, just you might specify FQDN/127.0.0.1/IP Address as server name in the connection string.

Error Message “TCP specific”:

[SQL Native Client]TCP Provider: No connection could be made because the target machine actively refused it.

[SQL Native Client]Login timeout expired

[SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

-OR-

HResult 0x274D, Level 16, State 1

TCP Provider: No connection could be made because the target machine actively refused it.

Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not  allow remote connections..

Error: Microsoft SQL Native Client : Login timeout expired.

The reason is server is not listening on TCP probably TCP protocol was not enabled.

Error Message:

[SQL Native Client]Unable to complete login process due to delay in opening server connection.

Reason:

1)     There are spaces after Instance name in the connection string eg. osql /S”\Instance  ” /E, to resolve this, you need to remove the trailing space.

2)     Connect through 127.0.01.

3)     Remote connection and WINS was disabled on the client machine and you connect using FQDN as server name. To resolve this, One way, turn on “File and Printer Sharing” and explicitly use name pipe protocol. Another is enlarge the connect timeout to around 30 seconds.

Error Message “ Firewall specific”:

HResult 0x274C, Level 16, State 1
An error has occurred while establishing a connection to the server. When connectiong to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.(provider:TCP Provider, errror:0-A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)

-OR-

Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
Error: Microsoft SQL Native Client : Login timeout expired.

This is because connection blocked by firewall. To resolve this, take follow steps:

Ø  Enable SqlBrowser, see the info in Message 4. Plus, add sqlbrowser.exe into Firewall exception list: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\DomainProfile\AuthorizedApplications\List

Ø   Add Tcp port to Firewall exception list. (eg, Name-1433:TCP, Value-1433:TCP:*:Enabled:Tcp 1433). HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SharedAccess\Parameters\FirewallPolicy\DomainProfile\GloballyOpenPorts\List

Posted in SQL Server DBA | Tagged , , , , , , , | Leave a comment

SQL Server Production Issues

Posted on May 20, 2010 by uday arumilli

SQL Server Production Issues

Prod Problems and Solutions:

     Below are the common problems from production environment.

  •  SQL Server Log full alert 
  • SQL Server Blocking Alert
  • Low Disk Space/Disk Full
      Now here are the solutions for these issues…………….

 

 SQL Server Log full alert
________________________________________

Title

SQL Server log full

 Issue/Problem Description

Log is full on TempDB or a user database. This is most often caused by a process has run longer than expected, has hung or performed a rollback because there was insufficient space in the database to proceed.

Action/Resolution Information

For User Databases

1. Verify drive space is not causing the problem.
2. Check SQL Agent jobs to see is one is causing the problem.

3. Locate running process verify if:
a. Are still running
b. Have completed already.
c. Are rolling back or have rolled back.

4. Check DB settings for caps on the files.
a. If a max size is set, expand the max size by a small amount to allow the process to finish running. Make sure that other files on the drive are not impacted.
b. If a max is not set and the drive is running out of space notify the customer of the findings and have them consider the following:
** Expand to another location. (Make sure to cap the file as well before expanding to
another location to prevent the file from taking all of the disk space left.)
** Grow the drive if it is on Storage Utility
5. If the following hold true attempt to shrink the log:
a. The process has completed or rolled back
b. There is free space on the Disk
c. There is free space to be gained in the log

For TempDB

1. Identify if TempDB is setup with multiple files.

2. Verify DB settings.

a. If there is a maxcap on the tempdb file and there is adequate, disk space increase it up a small amount to allow the process to complete.

IMPORTANT – If this TempDB is set up with several TempDB files you need to add a little space to each file equally to maintain the correct algorithm.

b. If there’s no cap set on the database.

** Verify drive space If there is a lot of space available than the process may have

already rolled back.

** Identify process using tempdb and what is driving them.

** Locate alternate location to expand files

(Use extreme caution when expanding to another drive. It will affect the other databases on the server. Try to expand only long enough to get the process completed and only to a drive other than E-H.)

As a last resort and only when SQL is negatively impacted by the condition consider requesting permission from the customer to restart SQL.

 

Blocking Alert
________________________________________

Title

SQL Server Blocking

Issue/Problem Description

On occasion SQL server will throw alerts for blockage. A lot of times this blockage will pass on its own. In those cases let the customer know the blockage occurred and that it has passed. If it continues however this could indicate a larger problem.

Action/Resolution Information

Blocking often occurs because:

1. A SPID holds locks on a set of resources for an extended period of time before releasing them. This type of blocking resolves itself over time, but can cause performance degradation.(This type will often resolve itself however can cause a moving target of sorts as the blocking moves from one resource to another making this harder to troubleshoot overall)

2. A SPID holds locks on a set of resources and never releases them. This type of blocking does not resolve itself and prevents access to the affected resources indefinitely.(This type is fairly consistent and easy to troubleshoot normally)

Running SQLStat will verify blockage is still occurring after you receive the alert:

CONNECTING TO SQL SERVER:

SERVER_NAME 9.00.1355

TOTAL PROCESSES: 26

BLOCKED PROCESSES: 2

Once you have verified that the blockage is still occurring you need to identify the following:

1. The SPID causing the blocking.

2. The command being executed by the SPID.

3. If the SPID is still active.

4. What process is controlling the SPID.

 

You can either view things using Query Analyzer or Enterprise Manager. Since some tempDB blocking can prevent you from viewing the process’ through Enterprise Manager it is good to be aware of which query to run to get the same information.

 

 

From Query Analyzer you can run any of the following to get SPID information:

 

 

** SP_WHO2 – BlkBy column

** SP_LOCK – SPID Column

** SELECT * FROM MASTER..SYSPROCESSES

** SELECT * FROM MASTER..SYSLOCKINFO

** SELECT * FROM SYS.DM_TRAN_LOCKS – SQL 2005 only-

Use WHERE resource_database_id = clause for specific database.

 

 

A SPID value of -2 indicates an orphaned transaction.

 

 

Take these blockers and run the following to get the detail of what they are running:

 

 

DBCC INPUTBUFFER(spid)

 

 

Identify I/O consumption on long running tasks to verify still active:

 

 

SELECT SPID, PHYSICAL_IO FROM MASTER..SYSPROCESSES

 

 

OR

 

 

Connect via Enterprise manager and view the following

1. Expand Group

2. Choose Server

3. Expand Management

For SQL 2000

** Expand Locks/Process ID

** Click Process Info

** Double Click the blocking Spid to capture the last command executed.

For SQL 2005

•Double click on Activity Monitor

** Scroll to the right and locate the “Blocked By” columns. These are your head

blockers.

** Double click on Process ID to make note of what activity is causing the blocking.

** Note Process ID number for the head of the blocking chain.

 

 

Once you have identified the running command and verified it is still active look at some of the following:

 

 

If a SQL Agent job is spawning the command to run.

 

 

1. If the job is running over, notify customer of situation and to check

2. If the job is not running over, notify customer and monitor till expected job end

3. If this is a daily occurrence you may suggest they choose a different time to run their job when the server is under fewer loads and it will not cause blocking.

 

 

Not a SQL job but ad-hoc query – Notify customer and person running query of the problem and request they

 

 

1. Break any large, complex queries into simpler queries

2. Run the query during off hours or on a separate computer

 

 

Remember that certain SPIDs should not be killed or cannot be killed.

 

 

**Your own process – Cannot be killed

 

 

Those labeled with the following should not be killed:

 

 

** AWAITING COMMAND

** CHECKPOINT SLEEP

** LAZY WRITER

** LOCK MONITOR

** SIGNAL HANDLER

 

 

If blocking is persistent and an attempt to kill the SPID has left it hung you may need to either:

1. Restart SQL

or

2. Reboot Server

 

 

Run the following from a Query Analyzer to get an idea of how long a SPID may take to rollback:

 

 

KILL spid WITH STATUSONLY

 

 

In order to clear the issue. Make sure and discuss this with the customer first.

 

 

 

 

Disk Space Alert

________________________________________

 

 

Title

 

 

Low Disk Space/Disk Full

 

 

Issue/Problem Description

 

 

Disk threshold alerts

 

 

There are two disk threshold alerts we are currently trapping:

1. Below 400mb (Sends email to MSE every 15 minutes)

2. Below 5% (Sends email to MSE every 15 minutes)

 

 

Drive Notations as Per  General Production Standards

 

 

C Drive – All OS Files present.

D Drive – All the System Database files (log & data files) present in D:\MSSQL\Data folder.

E Drive – All the Database Backup files (Full & Differential) present in E:\MSSQL\BAK folder.

F Drive – All the Transaction Log Backup files present in F:\MSSQL\TRAN folder.

T Drive – All the data files of Tempdb database present in T:\MSSQL\DATA

H Drive – All the database files (only Data) present in H:\MSSQL\DATA.

O Drive – All the Log files present in O:\MSSQL\DATA. .

 

 

Action/Resolution Information

 

 

Identify the server type

 

 

Since we monitor several different types of servers, each with its own SKU, it is important to identify the type of server throwing the error to make sure you understand what each drive’s purpose is.

 

 

Common:

 

 

** File servers

** SQL Servers

** IIS Servers

** Servers on SANs or SU’s

 

 

Common causes

 

 

** Look for the obvious files first:

1. *.tmp

2. *.dmp

3. *.log

** Look for failed jobs within SQL or Task Scheduler that may be causing the drive to fill.

** Check the Print Spooler if this server does a lot of printing. (A large print job that hung can cause the C$ drive to fill quickly. Commonly this folder is located

C:\$systemroot$\system32\spool\PRINTERS.)

** Check for SMS Cache that has grown large.
Actions you can consider

For C Drive alerts:

** Contact DCOps for resolution.

For other drives:

**Contact the customer and ask them to remove any unnecessary files/folders.

** Compressing old files and/or the folder.

** Special Tape archive and delete.

a. Create a share where the data is located.

b. Create a Change Request for the Backup team to backup the newly created share to

tape and send offsite.

c. Verify successful tape save with Backups.

d. Delete data off server.

** Shrinking the files if they are from a SQL database.

a. Identify if there is available space on the database to shrink it down a bit.

** Delete completely. (Mostly refers to C$)

a. Old dump files from Windows and DrWatson.

b. Old Install bits from updates and upgrades.

** Move Print Spool to another drive.

** If this is on a Storage Utility, you may consider adding more drive space.

 

 

!! Always confirm!!

 

 

Always confirm with the customer before moving, removing or altering ANY of their data from a server.

 

Will be back with More probs and Sols…………………..!!!!!.

Posted in SQL Server DBA | Tagged , , , , , , , , , , | 3 Comments