The error happens when your disk space is full on TempDB.Check the space on the Drive where the TempDb is located .
Solutions
a.Review the Query and check if it can be better optimised. Some of the Queries’ intermediate heavy datasets are stored in tempdb before returning the results.
b.The most easiest way I found is to restart the SQL Server service. Every Time the SQL server is restarted the tempdb gets flushed thereby reducing all the disk space.
Here is a script that gives you the list of operators name who receive notifications when a job fails
SELECT sysoperators.name AS JobName, sysjobs.name AS OperatorName, sysjobs.enabled, sysoperators.id FROM sysjobs INNER JOIN sysoperators ON sysjobs.notify_email_operator_id = sysoperators.id
You need to change the sysjobs.notify_email_operator_id to change the Notifications of the Operator.
Yesterday Afternoon me and my friend were doing some serious Sql manipulation on some heavy datasets that needs to go inside the Cube.We have written some dynamic SQL statement that is going to insert some 40000 rows into a Control table for a Dimension.
Weird Enough my friend accidently closed the Query window and for a second we thought we lost it.
But Thanks to Dynamic Management views(DMV) from Sql server 2005.we were able to get the query back from the Cache..The query we used is as follows
select qs.usecounts, cacheobjtype, objtype, qt.text
from sys.dm_exec_cached_plans qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) as qt
–where qt.text like ‘%%’
order by qt.text
and thats it we were able to get back the query…quite cool way of doing things..isnt it?
You can identify the SQL server environments using various settings from the Sql server Management Studio 2008.
a. Open Management studio
b. Press Ctrl + Alt+ G (List of Registered Servers)
c. Right Click on one of the Registered Servers –> Properties
Server Properties
e. Once the settings are changed you can see the changes on the status bar as shown below
When Doing database Restore sometimes we may need to kill the spid’s in the database so that the connections are not open. Going through the spids in sp_who or sp_who2 can sometimes be a tedious task if there are more connections to the database. The following TSQL should generate the list of kill statements for a particular database with the spids.
DECLARE @spid VARCHAR(3000)
DECLARE @Databasename VARCHAR(100)
SET @spid = ”
SET @Databasename =’AdventureWorks’
SELECT @spid = + @spid + ‘Kill ‘+ CONVERT(VARCHAR(10),spid) +’;’ FROM MASTER..sysprocesses WHERE DB_NAME(dbid) = @Databasename
PRINT @spid
I have 20 Databases in my production Server and the tables in some database are used by the Views , functions and procedures of some Other Databases. Now I want to find out the Dependencies of the Tables and their Database Location. I can use the following Dynamic SQL to get the SQL statements and then execute them.
Eg:
If I want to find out the List of Procedures and Views on all the Databases that are using the table called Col1
Dependencies Across Databases
Recently I was involved in a task of splitting the URL’s and generating some reference based on them. I started looking around on google and found this function Split from http://www.otecode.com. This Table Valued function returns a list of substring based on the literal you specify as parameter
create function [fn_Split] ( @text varchar(8000) ,@Delimiter varchar(20) = ' ' ) returns @Strings Table ( position int identity primary key ,value varchar(8000) ) as Begin Declare @index int set @index = -1 while ( Len(@text) > 0 ) Begin set @Index = charIndex(@delimiter,@text) if (@index = 0) and (len(@text) > 0) Begin Insert into @Strings values(@text) Break End if (@Index > 1) Begin Insert into @Strings values (Left (@text , @index - 1)) set @text = right (@text , (len(@text) - @index)) End Else set @text = Right(@text , (len(@text) - @index)) End Return end
So this is how i used the Split function
Using Function Split
The Results
The Results after Splitting
How to get the Disk Space in SQL Server?
There are 2 ways
a. xp_fixeddrives — The Easy way
b. Ftsutil(xp_cmdshell) — If I want to know the percentage of free space in harddisk use FTSUTIL . The only thing you need to enable is the xp_cmdshell.
Click here to read how to enable XP_cmdshell
Here is the proc
CREATE PROCEDURE [dbo].[usp_GetDiskSpace]
– Add the parameters for the stored procedure here
AS
BEGIN
– SET NOCOUNT ON added to prevent extra result sets from
– interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Drive TINYINT,
@SQL VARCHAR(100)
SET @Drive = 97
– Setup Staging Area
DECLARE @Drives TABLE
(
Drive CHAR(1),
Info VARCHAR(80)
)
TRUNCATE TABLE Freediskspace
WHILE @Drive < = 122
BEGIN
SET @SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''
INSERT @Drives
(
Info
)
EXEC (@SQL)
UPDATE @Drives
SET Drive = CHAR(@Drive)
WHERE Drive IS NULL
SET @Drive = @Drive + 1
END
-- Show the expected output
INSERT INTO FreeDiskSpace
(
Drive
,TotalGB
,FreeGB
)
SELECT Drive,
SUM(CASE WHEN Info LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS FLOAT) ELSE CAST(0 AS BIGINT) END)/1073741824 AS TotalGB,
SUM(CASE WHEN Info LIKE 'Total # of free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS floaT) ELSE CAST(0 AS BIGINT) END)/1073741824 AS FreeGB
FROM (
SELECT Drive,
Info
FROM @Drives
WHERE Info LIKE 'Total # of %'
) AS d
GROUP BY Drive
ORDER BY Drive
UPDATE FreeDiskSpace
SET PercentageGB = CONVERT(DECIMAL(5,2),ROUND(CONVERT(DECIMAL(7,2),ROUND(FreeGB,2)) * 100 / CONVERT(DECIMAL(7,2),ROUND(TotalGB,2)),2))
WHERE PercentageGB IS null
SELECT
drive
,CONVERT(DECIMAL(7,2),ROUND(TotalGB,2)) AS TotalDiskSpace
,CONVERT(DECIMAL(7,2),ROUND(FreeGB,2)) AS FreeSpace
,PercentageGB
FROM FreeDiskSpace
END
GO
In Sql server 2008 the Surface area configuration no longer exists. you can enable the Xp_cmdshell either by the Policy based management or by using the sp_configure
Enabling Xp_cmdshell in Sql Server 2008
Here is how you create a stored procedures with OutPut Parameter
The Execution is as follows
My Twitter Feed
